package testtt;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;


public class CustomerHandler {

       // JDBC driver name and database URL
       static final String JDBC_DRIVER = "org.h2.Driver";
       static final String DB_URL = "jdbc:h2:~/test";

       //  Database credentials
       static final String USER = "admin";
       static final String PASS = "admin";

          static Connection conn = null;
          static Statement stmt = null;

       public static void connect() {

             try {
                Class.forName(JDBC_DRIVER);
                System.out.println("Connecting to database...");
                conn = DriverManager.getConnection(DB_URL,USER,PASS);

            } catch (ClassNotFoundException |SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }


       }

       public static void close() {

            try {
               //stmt.close();
               conn.close();
               System.out.println("Connection closed...");
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
       }

       public static void addCustomer(String id, String name, String surname, String birthdate,String adress) {

             try {

                 PreparedStatement insertPreparedStatement = null;
                 String InsertQuery = "INSERT INTO CUSTOMERS" + "(id, name, surname, birthdate, address,list) values" + "(?,?,?,?,?,?)";

                ArrayList list1 = new ArrayList<Account>();

                Account acc = new Account("44","dspname");
                list1.add(acc);

                 insertPreparedStatement = conn.prepareStatement(InsertQuery);
                 insertPreparedStatement.setString(1, id);
                 insertPreparedStatement.setString(2, name);
                 insertPreparedStatement.setString(3, surname);
                 insertPreparedStatement.setString(4, birthdate);
                 insertPreparedStatement.setString(5, adress);
                 insertPreparedStatement.setObject(6, list1);
                 insertPreparedStatement.executeUpdate();
                 insertPreparedStatement.close();


            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }


       }

       public static void getCustomerById(int input_id) {

           try {

             stmt = conn.createStatement();
             String sql = "SELECT * FROM CUSTOMERS WHERE ID = " + input_id;
             ResultSet rs = stmt.executeQuery(sql);

             while(rs.next()) {
                // Retrieve by column name
                String id  = rs.getString("id");
                String name = rs.getString("name");
                String surname = rs.getString("surname");
                String birthdate = rs.getString("birthdate");
                String adress = rs.getString("address");
                Object[] colArray=  (Object[]) rs.getArray("list").getArray();

                // Display values
                System.out.print("ID: " + id);
                System.out.print(", Name: " + name);
                System.out.print(", Surname: " + surname);
                System.out.print(", Date: " + birthdate);
                System.out.print(", Adress: "+ adress);
               // System.out.println("Accoutn detail: " + colArray.getClass().);
             }

                rs.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }

       }

       public static void main(String [] args) {

        connect();

        getCustomerById(3);

        close();
    }


}