JDBC &Transactions

                        
                            CREATE DATABASE IF NOT EXISTS
                            demo;
USE
                            demo;

DROP TABLE IF EXISTS
                            employees;
CREATE TABLE
                            employees (
  id          INT
                            PRIMARY KEY AUTO_INCREMENT
                            ,   -- auto id; isAutoIncrement()=true
                            name        VARCHAR
                            (100
                            ) NOT NULL
                            ,            -- maps to Java String
                            department  VARCHAR
                            (50
                            ),
  salary      INT
                            ,                            -- maps to Java int
                            hire_date   DATE
                            -- maps to java.sql.Date
                            );

INSERT INTO
                            employees (name, department, salary, hire_date) VALUES
                            ('Asha Rao'
                            ,    'Legal'
                            ,       92000
                            , '2021-03-01'
                            ),
  ('Ben Cole'
                            ,    'Engineering'
                            , 78000
                            , '2022-07-15'
                            ),
  ('Cara Diaz'
                            ,   'Legal'
                            ,       85000
                            , '2020-11-20'
                            ),
  ('Dev Khan'
                            ,    'Engineering'
                            , 99000
                            , '2019-01-05'
                            );
                        
                    

                        
                            import
                            java.sql.*;                          // Connection, Statement, ResultSet, SQLException
                            public class
                            JDBCDemo
                            {
  public static void
                            main
                            (String
                            [] args) {
    String
                            url  = "jdbc:mysql://localhost:3306/demo"
                            ;  // protocol:host:port/db
                            String
                            user = "student"
                            , pwd = "student"
                            ;

    // try-with-resources: con, stmt, rs all auto-close (Step 6 for free) [+]
                            try
                            (Connection
                            con = DriverManager
                            .getConnection
                            (url, user, pwd);  // Steps 2-3
                            Statement
                            stmt = con.createStatement
                            ();
         ResultSet
                            rs = stmt.executeQuery
                            (                          // Step 4: SELECT
                            "SELECT id, name, salary FROM employees ORDER BY id"
                            )) {

      while
                            (rs.next
                            ()) {                // Step 5: next() advances cursor, false at end
                            int
                            id      = rs.getInt
                            ("id"
                            );       // read by column NAME
                            String
                            name = rs.getString
                            (2
                            );       // read by 1-based INDEX (col 2 = name)
                            int
                            salary  = rs.getInt
                            ("salary"
                            );
        System
                            .out
                            .printf
                            ("%d  %-10s %d%n"
                            , id, name, salary);
      }
    } catch
                            (SQLException
                            e) {        // bad url/creds, SQL error, etc.
                            e.printStackTrace
                            ();
    }
  }
}
                        
                    

                        
                            import
                            java.sql.*;

public class
                            JDBCInsertDemo
                            {
  public static void
                            main
                            (String
                            [] args) {
    String
                            url = "jdbc:mysql://localhost:3306/demo"
                            ;
    String
                            sql = "INSERT INTO employees(name, department, salary, hire_date) "
                            + "VALUES ('Eli Frost', 'Sales', 71000, '2023-02-10')"
                            ;

    try
                            (Connection
                            con = DriverManager
                            .getConnection
                            (url, "student"
                            , "student"
                            );
         Statement
                            stmt = con.createStatement
                            ()) {

      int
                            rows = stmt.executeUpdate
                            (sql);   // executeUpdate → int rows affected
                            System
                            .out
                            .println
                            ("Rows inserted: "
                            + rows);  // 1 = success
                            } catch
                            (SQLException
                            e) {
      e.printStackTrace
                            ();
    }
  }
}
                        
                    

                        
                            import
                            java.sql.*;

public class
                            JDBCUpdateDemo
                            {
  public static void
                            main
                            (String
                            [] args) throws
                            SQLException
                            {
    String
                            url = "jdbc:mysql://localhost:3306/demo"
                            ;
    // give everyone in Legal a 10% raise
                            String
                            sql = "UPDATE employees SET salary = salary * 1.10 "
                            + "WHERE department = 'Legal'"
                            ;

    try
                            (Connection
                            con = DriverManager
                            .getConnection
                            (url, "student"
                            , "student"
                            );
         Statement
                            stmt = con.createStatement
                            ()) {
      int
                            rows = stmt.executeUpdate
                            (sql);  // rows matched by WHERE
                            System
                            .out
                            .println
                            ("Rows updated: "
                            + rows);
    }
  }
}
                        
                    

                        
                            import
                            java.sql.*;

public class
                            JDBCDeleteDemo
                            {
  public static void
                            main
                            (String
                            [] args) throws
                            SQLException
                            {
    String
                            url = "jdbc:mysql://localhost:3306/demo"
                            ;
    String
                            sql = "DELETE FROM employees WHERE name = 'Eli Frost'"
                            ;

    try
                            (Connection
                            con = DriverManager
                            .getConnection
                            (url, "student"
                            , "student"
                            );
         Statement
                            stmt = con.createStatement
                            ()) {
      int
                            rows = stmt.executeUpdate
                            (sql);
      System
                            .out
                            .println
                            ("Rows deleted: "
                            + rows);
    }
  }
}
                        
                    

                        
                            import
                            java.sql.*;

public class
                            JDBCPreparedStatementDemo
                            {
  public static void
                            main
                            (String
                            [] args) throws
                            SQLException
                            {
    String
                            url = "jdbc:mysql://localhost:3306/demo"
                            ;
    // ? placeholders — values bound separately, so input can't be SQL
                            String
                            sql = "SELECT name, salary FROM employees "
                            + "WHERE salary >? AND department = ?"
                            ;

    try
                            (Connection
                            con = DriverManager
                            .getConnection
                            (url, "student"
                            , "student"
                            );
         PreparedStatement
                            ps = con.prepareStatement
                            (sql)) {  // precompiled once
                            ps.setInt
                            (1
                            , 80000
                            );          // bind ?#1 (1-based) — typed setter
                            ps.setString
                            (2
                            , "Legal"
                            );     // bind ?#2
                            try
                            (ResultSet
                            rs = ps.executeQuery
                            ()) {  // no SQL string passed here
                            while
                            (rs.next
                            ()) {
          System
                            .out
                            .printf
                            ("%-10s %d%n"
                            ,
              rs.getString
                            ("name"
                            ), rs.getInt
                            ("salary"
                            ));
        }
      }

      // --- Setting a SQL NULL the right way [+] ---
                            String
                            ins = "INSERT INTO employees(name, department, salary, hire_date) "
                            + "VALUES (?, ?, ?, ?)"
                            ;
      try
                            (PreparedStatement
                            p2 = con.prepareStatement
                            (ins)) {
        p2.setString
                            (1
                            , "Mara Vex"
                            );
        p2.setNull
                            (2
                            , Types
                            .VARCHAR);  // department = SQL NULL (not Java null!)
                            p2.setInt
                            (3
                            , 60000
                            );
        p2.setNull
                            (4
                            , Types
                            .DATE);
        System
                            .out
                            .println
                            ("NULL-insert rows: "
                            + p2.executeUpdate
                            ());
      }
    }
  }
}
                        
                    

                        
                            # key=value config — no quotes, no recompiling to switch envs
                            db.url=jdbc:mysql://localhost:3306/demo
db.user=student
db.password=student
                        
                    

                        
                            import
                            java.sql.*;
import
                            java.io.*;
import
                            java.util.Properties;          // standard config reader
                            public class
                            JDBCPropertiesDemo
                            {
  public static void
                            main
                            (String
                            [] args) throws
                            Exception
                            {
    Properties
                            props = new
                            Properties
                            ();
    try
                            (FileInputStream
                            in = new
                            FileInputStream
                            ("db.properties"
                            )) {
      props.load
                            (in);              // parse key=value pairs at startup
                            }

    String
                            url  = props.getProperty
                            ("db.url"
                            );
    String
                            user = props.getProperty
                            ("db.user"
                            );
    String
                            pwd  = props.getProperty
                            ("db.password"
                            );

    try
                            (Connection
                            con = DriverManager
                            .getConnection
                            (url, user, pwd)) {
      System
                            .out
                            .println
                            ("Connected to: "
                            + con.getCatalog
                            ());
    }
  }
}
                        
                    

                        
                            import
                            java.sql.*;

public class
                            JDBCResultSetMetaDataDemo
                            {
  public static void
                            main
                            (String
                            [] args) throws
                            SQLException
                            {
    String
                            url = "jdbc:mysql://localhost:3306/demo"
                            ;

    try
                            (Connection
                            con = DriverManager
                            .getConnection
                            (url, "student"
                            , "student"
                            );
         Statement
                            stmt = con.createStatement
                            ();
         ResultSet
                            rs = stmt.executeQuery
                            ("SELECT id, name FROM employees"
                            )) {

      // DatabaseMetaData — about the DB itself
                            DatabaseMetaData
                            db = con.getMetaData
                            ();
      System
                            .out
                            .println
                            ("DB: "
                            + db.getDatabaseProductName
                            ());

      // ResultSetMetaData — about the query's columns (schema unknown ahead of time)
                            ResultSetMetaData
                            md = rs.getMetaData
                            ();
      int
                            cols = md.getColumnCount
                            ();        // build headers dynamically
                            for
                            (int
                            i = 1
                            ; i <= cols; i++)      // columns are 1-based
                            System
                            .out
                            .print
                            (md.getColumnName
                            (i) + "\t"
                            );
      System
                            .out
                            .println
                            ();
    }
  }
}
                        
                    

                        
                            USE
                            demo;
SET
                            autocommit = 0
                            ;                -- turn off auto-commit so we control the txn
                            START TRANSACTION
                            ;
UPDATE
                            employees SET
                            salary = salary + 1000
                            WHERE
                            name = 'Ben Cole'
                            ;
SAVEPOINT
                            sp1;                     -- checkpoint AFTER Ben's raise
                            UPDATE
                            employees SET
                            salary = salary + 5000
                            WHERE
                            name = 'Dev Khan'
                            ;
ROLLBACK TO
                            sp1;                   -- undo ONLY Dev's raise; Ben's stays pending
                            COMMIT
                            ;                            -- finalize: Ben +1000 sticks, Dev unchanged
                            SELECT
                            name, salary FROM
                            employees WHERE
                            name IN
                            ('Ben Cole'
                            , 'Dev Khan'
                            );
                        
                    

                        
                            import
                            java.sql.*;

public class
                            JDBCTransactionDemo
                            {
  public static void
                            main
                            (String
                            [] args) {
    String
                            url = "jdbc:mysql://localhost:3306/demo"
                            ;

    Connection
                            con = null
                            ;
    try
                            {
      con = DriverManager
                            .getConnection
                            (url, "student"
                            , "student"
                            );
      con.setAutoCommit
                            (false
                            );     // BEGIN: group the next statements [+] (note 19)
                            // move $5 from Asha (A) to Ben (B) — treat 'salary' as a balance
                            PreparedStatement
                            debit = con.prepareStatement
                            (
          "UPDATE employees SET salary = salary - 5 WHERE name = 'Asha Rao'"
                            );
      PreparedStatement
                            credit = con.prepareStatement
                            (
          "UPDATE employees SET salary = salary + 5 WHERE name = 'Ben Cole'"
                            );

      debit.executeUpdate
                            ();          // step: A - 5
                            credit.executeUpdate
                            ();         // step: B + 5
                            con.commit
                            ();                 // both succeeded → finalize atomically
                            System
                            .out
                            .println
                            ("COMMIT: transfer complete"
                            );

    } catch
                            (SQLException
                            e) {
      try
                            {
        if
                            (con != null
                            ) con.rollback
                            ();  // any failure → undo BOTH updates
                            System
                            .out
                            .println
                            ("ROLLBACK: nothing changed"
                            );
      } catch
                            (SQLException
                            ex) { ex.printStackTrace
                            (); }
    } finally
                            {
      try
                            { if
                            (con != null
                            ) con.close
                            (); }   // Step 6: always release
                            catch
                            (SQLException
                            ignored) {}
    }
  }
}