package pl.edu.pw.elka.bd.lab6;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;

import oracle.jdbc.pool.OracleDataSource;


/**
 * BD.A
 * lab 6
 * JDBC Showcase.
 *
 * @author B.Twardowski <B.Twardowski@ii.pw.edu.pl>
 *
 */
public class JDBCShowcase {

	//const's
	private static String JDBC_URL = "jdbc:oracle:thin:mjarzebs/mjarzebs@ora3.elka.pw.edu.pl:1521:ora3inf";


	private OracleDataSource ods;
	private Connection conn;
	private Scanner inputScanner = new Scanner(System.in);



	/**
	 * Initializing DB connection.
	 * @throws SQLException
	 */
	private void init() throws SQLException {
		ods = new OracleDataSource();
		ods.setURL(JDBC_URL);
		conn = ods.getConnection();
		DatabaseMetaData meta = conn.getMetaData();
		System.out.println("Successfully connected to DB. JDBC driver version is " + meta.getDriverVersion());

	}

	/**
	 * Closing DB connection.
	 * @throws SQLException
	 */
	private void close() throws SQLException {
		conn.close();
		System.out.println("Database connection successfully closed.");
	}


	/**
	 * Showcase.
	 * @throws SQLException
	 */
	public void doShowcase() throws SQLException {
		init();
		int i=5;
		while(i!=0)
		{
			System.out.println("0- Exit 1- Create 2- Read 3-Update 4-Delete");
			i= inputScanner.nextInt();
			inputScanner.nextLine();
			System.out.println("tst");

			if(i==1)
			{
				Create();
			}
			if(i==2)
			{
				Read();
			}
			if(i==3)
			{
				Update();
			}
			if(i==4)
			{
				Delete();
			}

		}





		/*simpleTest();
		preparedStatementShowcace();
		transactionShowcace();*/
		close();
	}



	private void Delete() throws SQLException {
		System.out.println("Podaj nip do usuniecia");
		PreparedStatement preparedStatement = conn.prepareStatement("delete from firmy where nip=?");
		preparedStatement.setString(1, inputScanner.nextLine() );
		ResultSet rs = preparedStatement.executeQuery();

	    /*int j = 1;
	    while (rs.next()) {
	       System.out.println("[" + j + "]:" + rs.getString(1));
	       j++;
	    }*/

	    // close the result set, the statement and connect
	    rs.close();
	    preparedStatement.close();
	}




	private void Update() throws SQLException {
		System.out.println("Podaj nazwe firmy");
		PreparedStatement preparedStatement = conn.prepareStatement("update firmy set nip=? where nazwa=?");
		preparedStatement.setString(2, inputScanner.nextLine() );
		System.out.println("Podaj nowy nip firmy");
		preparedStatement.setString(1, inputScanner.nextLine() );
		ResultSet rs = preparedStatement.executeQuery();

	    /*int j = 1;
	    while (rs.next()) {
	       System.out.println("[" + j + "]:" + rs.getString(1));
	       j++;
	    }*/

	    // close the result set, the statement and connect
	    rs.close();
	    preparedStatement.close();
	}



	private void Create() throws SQLException {
		System.out.println("Podaj nazwe firmy");
		PreparedStatement preparedStatement = conn.prepareStatement("insert into firmy values (?,?)");
		preparedStatement.setString(1, inputScanner.nextLine() );
		System.out.println("Podaj nip firmy");
		preparedStatement.setString(2, inputScanner.nextLine() );
		ResultSet rs = preparedStatement.executeQuery();

	    /*int j = 1;
	    while (rs.next()) {
	       System.out.println("[" + j + "]:" + rs.getString(1));
	       j++;
	    }*/

	    // close the result set, the statement and connect
	    rs.close();
	    preparedStatement.close();
	}




	private void Read() throws SQLException {
		System.out.println("Podaj nazwe firmy ktorej nip chcesz odczytac");
		PreparedStatement preparedStatement = conn.prepareStatement("select nip from firmy where nazwa like ?");
		preparedStatement.setString(1, inputScanner.nextLine() );
		ResultSet rs = preparedStatement.executeQuery();

	    int j = 1;
	    while (rs.next()) {
	       System.out.println("[" + j + "]:" + rs.getString(1));
	       j++;
	    }

	    // close the result set, the statement and connect
	    rs.close();
	    preparedStatement.close();
	}



	/**
	 * Simple select statement.
	 * @throws SQLException
	 */
	private void simpleTest() throws SQLException {

		System.out.println("Simple query statement test...");

	    // Create a statement
	    Statement stmt = conn.createStatement();

	    // Execute SQL
	    ResultSet rset = stmt.executeQuery("select pesel from kierowcy");

	    System.out.println("Query result: ");

	    int i = 1;
	    while (rset.next()) {
	       System.out.println("[" + i + "]:" + rset.getString(1));
	       //System.out.println("[" + i + "]:" + rset.getString(2));
	       i++;
	    }

	    // close the result set, the statement and connect
	    rset.close();
	    stmt.close();

	}


	/**
	 * Prepared statement showcase.
	 * @throws SQLException
	 */
	private void preparedStatementShowcace() throws SQLException {

		System.out.println("Prepared statement showcase...");

		//TODO: To modify!!!

		PreparedStatement preparedStatement = conn.prepareStatement("select pesel from kierowcy where imie like ?");

		//get params from console
		System.out.println("Type employee name :" );
		preparedStatement.setString(1, inputScanner.nextLine() );

		ResultSet rs = preparedStatement.executeQuery();

	    int i = 1;
	    while (rs.next()) {
	       System.out.println("[" + i + "]:" + rs.getString(1));
	       i++;
	    }

	    // close the result set, the statement and connect
	    rs.close();
	    preparedStatement.close();

	}



	/**
	 * Transaction showcase.
	 * @throws SQLException
	 */
	private void transactionShowcace() throws SQLException {

		System.out.println("Transactions statement showcase...");

		//TODO: To fill...

		conn.setAutoCommit(false);

		//operations - selects/updates/inserts with user interactions

		//if something wrong
		//conn.rollback();


		//if no error
		//conn.commit();

	}


	/**
	 * Run showcase.
	 *
	 * @param args
	 */
	public static void main(String[] args) throws SQLException {
		JDBCShowcase showcase = new JDBCShowcase();
		showcase.doShowcase();
	}

}