/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package jdbc;
import java.sql.*;
import java.util.Calendar;
import java.util.logging.Level;
import java.util.logging.Logger;
/**
 *
 * @author Yuki_Khiem
 */

public class JDBC {

 /**
  * @param args the command line arguments
  */
 String url = "jdbc:mysql://127.0.0.1";
 String port = "3306";
 String database = "sakila";
 String user = "root";
 String password = "";
 Statement stmt = null;
 ResultSet rs = null;
 Connection con = null;
 public JDBC() {

 }
 // create a connection to the database
 public Connection Connect() {

  System.out.println("Get connection ...");
  try {
   Class.forName("com.mysql.jdbc.Driver");
   con = DriverManager.getConnection(url + ":" + port + "/" + database, user, password);
   //info Driver and Product
   DatabaseMetaData dm = (DatabaseMetaData) con.getMetaData();
   System.out.println("Driver name: " + dm.getDriverName());
   System.out.println("Driver version: " + dm.getDriverVersion());
   System.out.println("Product name: " + dm.getDatabaseProductName());
   System.out.println("Product version: " + dm.getDatabaseProductVersion());
   System.out.println("---------------Done--------------" + "\n");
  } catch (SQLException sqle) {
   System.out.println("Connection Failed!");
   System.out.println("SQLException: " + sqle.getMessage() + "SQLState: " + sqle.getSQLState());
  } catch (ClassNotFoundException e) {
   System.out.println("Connection Failed!");
   System.out.println("ERROR: " + e.getMessage());
  }
  return con;
 }
 // query
 public String getQuery(String q) {
  return q;
 }
 // Result query
 public int ResultCau1(Connection con, String query) {
  try {
   stmt = con.createStatement();
   rs = stmt.executeQuery(query);
   System.out.println("Query 1: " + query + "\n");
   if (!rs.next()) {
    System.out.println("No data found in query!! \n");
    return 0;
   }
   do {
    int count = 1;
    String resultQuery = "";
    while (rs.getString(count) != null) {
     resultQuery += rs.getString(count) + " ";
     count++;
    }
    System.out.println(resultQuery);
   }
   while (rs.next());
   System.out.println("\n");
  } catch (SQLException sqle) {
   System.out.println("SQLException: " + sqle.getMessage() + " SQLState: " + sqle.getSQLState());
  } catch (Exception e) {
   System.out.println("Exception: " + e.getMessage());
  }
  return 0;
 }
 public int ResultCau2(Connection con, String query) {
  try {
   stmt = con.createStatement();
   con.setAutoCommit(false);
   System.out.println("Query 2: " + query + "\n");
   stmt.executeUpdate(query, Statement.RETURN_GENERATED_KEYS);
   rs = stmt.getGeneratedKeys();
   while (rs.next()) {
    System.out.println("Result: Automatically generated key value = " + rs.getInt(1) + "\n");
   }
   con.commit();
   System.out.println("Inserted records to table. \n");
  } catch (SQLException sqle) {
   System.out.println("SQLException: " + sqle.getMessage() + " SQLState: " + sqle.getSQLState());
  } catch (Exception e) {
   System.out.println("Exception: " + e.getMessage());
  }
  return 0;
 }
 public int ResultCau32(Connection con) {
  Calendar calendar = Calendar.getInstance();
  Timestamp currentTimestamp = new java.sql.Timestamp(calendar.getTime().getTime());
  String query = "INSERT INTO actor \n" + "VALUES (null, ?, ?, ?)";
  System.out.println("Query 32: " + query + "\n");
  try {
   PreparedStatement Pstmt = con.prepareStatement(query, Statement.RETURN_GENERATED_KEYS);
   Pstmt.setString(1, "Thanh");
   Pstmt.setString(2, "Thanh");
   Pstmt.setTimestamp(3, currentTimestamp);
   int i = Pstmt.executeUpdate();
   con.setAutoCommit(false);
   con.commit();
   rs = Pstmt.getGeneratedKeys();
   while (rs.next()) {
    System.out.println("Result: Automatically generated key value = " + rs.getInt(1));
   }
   System.out.println(i + " records inserted \n");
  } catch (SQLException sqle) {
   System.out.println("SQLException: " + sqle.getMessage() + "SQLState: " + sqle.getSQLState());
  } catch (Exception e) {
   System.out.println("Exception: " + e.getMessage());
  }
  return 0;
 }
 public int ResultCau31(Connection con) {
  String query = "SELECT * FROM film \n" +
"	JOIN film_actor ON film.film_id = film_actor.film_id \n" +
"	WHERE film_actor.actor_id = ?";
  try {
   PreparedStatement Pstmt = con.prepareStatement(query);
   Pstmt.setInt(1, 1);
   rs = Pstmt.executeQuery();
   System.out.println("Query 31: " + query + "\n");
   if (!rs.next()) {
    System.out.println("No data found in query!! \n");
    return 0;
   }
   do {
    int count = 1;
    String resultQuery = "";
    while (rs.getString(count) != null) {
     resultQuery += rs.getString(count) + " ";
     count++;
    }
    System.out.println(resultQuery);
   }
   while (rs.next());
   System.out.println("\n");
  } catch (SQLException sqle) {
   System.out.println("SQLException: " + sqle.getMessage() + " SQLState: " + sqle.getSQLState());
  } catch (Exception e) {
   System.out.println("Exception: " + e.getMessage());
  }
  return 0;
 }
 public int ResultCau4(Connection con, String query1, String query2) throws SQLException {
  try {
   stmt = con.createStatement();
   con.setAutoCommit(false);
   System.out.println("Query 4 add record 1: " + query1 + "\n");
   stmt.executeUpdate(query1);
   System.out.println("Inserted record 1 to table. \n");
   System.out.println("Query 4 add record 2: " + query2 + "\n");
   stmt.executeUpdate(query2);
   System.out.println("Inserted record 2 to table. \n");
   con.commit();
  } catch (SQLException sqle) {
   con.rollback();
   System.out.println("SQLException: " + sqle.getMessage() + " SQLState: " + sqle.getSQLState());
  } catch (Exception e) {
   System.out.println("Exception: " + e.getMessage());
  }
  return 0;
 }
 //Close connect
 public void Close() {
  if (con != null)
   try {
    con.close();
   } catch (SQLException sqle) {
    System.out.println("SQLException: " + sqle.getMessage() + " SQLState: " + sqle.getSQLState());
   }
 }
 public static void main(String[] args) throws SQLException {
  JDBC Gt1 = new JDBC();
  String cau1 = "SELECT * FROM film \n" +
   "	JOIN film_actor ON film.film_id = film_actor.film_id \n" +
   "	WHERE film_actor.actor_id = 1";
  String cau2 = "INSERT INTO actor \n" +
   "VALUES (NULL, 'Thanh', 'Xuan', CURRENT_TIMESTAMP)";
  String cau41 = "INSERT INTO rental VALUES (NULL, '2018-11-22 00:00:00', '367', '459', '2018-11-30 00:00:00', '1', CURRENT_TIMESTAMP)";
  String cau42 = "INSERT INTO rental VALUES (NULL, '2018-11-23 00:00:00', '389', '142', '2018-11-30 00:00:00', '1', CURRENT_TIMESTAMP)";
  Connection conn = Gt1.Connect();
  //Cau 1
    Gt1.ResultCau1(conn, Gt1.getQuery(cau1));
// Cau 2
    Gt1.ResultCau2(conn, Gt1.getQuery(cau2));
//cau 3
  Gt1.ResultCau31(conn);
  Gt1.ResultCau32(conn);
//cau 4
  Gt1.ResultCau4(conn, cau41, cau42);
  Gt1.Close();
 }
}