package com.ism.scs;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Scanner;

public class SQLInject {

	public static void main(String args[]) {
		Connection c = null;
		Statement stmt = null;

		try {
			Class.forName("org.sqlite.JDBC");
			c = DriverManager.getConnection("jdbc:sqlite:test.db");
			System.out.println("Opened database successfully");
			String sql;

			stmt = c.createStatement();
			stmt.executeUpdate("drop table product");

			stmt = c.createStatement();
			sql = "CREATE TABLE PRODUCT " + "(ID INT PRIMARY KEY NOT NULL," + " NAME TEXT NOT NULL, "
					+ " QUANTITY INT NOT NULL, " + " PRICE REAL," + " deleted int not null)";
			stmt.executeUpdate(sql);

			stmt = c.createStatement();
			sql = "insert into product(id, name, quantity, price, deleted)  values(1, 'laptop', 10, 123, 0)";
			stmt.executeUpdate(sql);
			sql = "insert into product(id, name, quantity, price, deleted)  values(2, 'watch', 50, 50, 0)";
			stmt.executeUpdate(sql);
			sql = "insert into product(id, name, quantity, price, deleted)  values(3, 'TV Set', 5, 300, 0)";
			stmt.executeUpdate(sql);

			Scanner scanner = new Scanner(System.in);
			boolean exit = false;

			while (!exit) {
				System.out.println("Search product by name or id: ");
				String input = scanner.nextLine();
				String params[] = input.split("\\s");
				switch (params[0]) {
				case "exit":
					exit = true;
					break;
				case "find":

					PreparedStatement pstmt = c
							.prepareStatement("select * from product where id=? or name=? and deleted=0");
					pstmt.setString(1, params[1]);
					pstmt.setString(2, params[1]);

					ResultSet rs = pstmt.executeQuery();

					while (rs.next()) {
						int id = rs.getInt("id");
						String name = rs.getString("name");
						int q = rs.getInt("quantity");
						float price = rs.getFloat("price");

						System.out.println("ID = " + id);
						System.out.println("NAME = " + name);
						System.out.println("Quantity = " + q);
						System.out.println("Price = " + price);
						System.out.println();
					}
					rs.close();
					pstmt.close();

					break;
				case "add":
					pstmt = c.prepareStatement(
							"insert into product(id, name, quantity, price, deleted)  values(?, ?, ?, ?, 0)");
					pstmt.setString(1, params[1]);
					pstmt.setString(2, params[2]);
					pstmt.setString(3, params[3]);
					pstmt.setString(4, params[4]);
					pstmt.executeUpdate();
					break;

				case "delete":
					pstmt = c.prepareStatement("update product set deleted=1 where id=?");
					pstmt.setInt(1, Integer.parseInt(params[1]));
					pstmt.executeUpdate();

				case "show":
					pstmt = c.prepareStatement("select * from product where  deleted=0");

					rs = pstmt.executeQuery();

					while (rs.next()) {
						int id = rs.getInt("id");
						String name = rs.getString("name");
						int q = rs.getInt("quantity");
						float price = rs.getFloat("price");

						System.out.println("ID = " + id);
						System.out.println("NAME = " + name);
						System.out.println("Quantity = " + q);
						System.out.println("Price = " + price);
						System.out.println();
					}
					rs.close();
					break;
				}

			}

			scanner.close();
			stmt.close();
			c.close();

		} catch (Exception e) {
			e.printStackTrace();
			System.err.println(e.getClass().getName() + ": " + e.getMessage());
			System.exit(0);
		}
	}
}

// bogdan.iancu@ie.ase.ro