Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- /*
- * 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();
- }
- }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement