JDBC & Transactions

Fast-recall cheat sheet. Compact monospace blocks meant for transcription into a physical notebook. Comparison concepts are in tables; doodle: lines describe diagrams to sketch.

═══ JDBC BASICS ═══
JDBC = Java DataBase Connectivity
  • standard API  → access SQL DBs from Java code
  • also a SPEC   → tells vendors how to write drivers
  • does 3 things : connect → send SQL → process results
  • vendor-neutral: swap DB, app logic stays the same

ARCHITECTURE (flow):
  App → JDBC API → DriverManager → Driver → DB
    DriverManager → routes by JDBC URL, picks the driver
    Driver        → vendor-supplied; JDBC calls → native DB calls
  doodle: [App]→[JDBC API]→[DriverManager]→[Driver]→(DB cylinder)
  aid: JDBC = universal power adapter ; driver = the plug
  ⚠ DriverManager ≠ Driver (mgr is std lib; driver is vendor jar)

═══ 6 CONNECTION STEPS ═══
1. load driver  → Class.forName("oracle.jdbc.driver.OracleDriver")
2. build URL    → jdbc:<protocol>:<details>
3. connect      → DriverManager.getConnection(url[,user,pwd]) → Connection
4. execute      → Statement: executeQuery OR executeUpdate
5. process      → walk ResultSet
6. close        → release! (else CONNECTION LEAK → pool dries up)

DRIVER CLASSES                       JDBC URLs
  com.mysql.cj.jdbc.Driver  → MySQL    MySQL  → jdbc:mysql://localhost:3306/demodb
  oracle.jdbc.driver.       → Oracle   Oracle → jdbc:oracle:thin@host:1521:demodb
    OracleDriver                       MSSQL  → jdbc:odbc:DemoDSN
  sun.jdbc.odbc.JdbcOdbc    → ODBC

ERRORS
  SQLException           → bad url / credentials
  ClassNotFoundException → driver jar not on classpath
  doodle: 6 stacked boxes, brackets {1-3}=setup {6}=cleanup

═══ EXECUTE & RESULTSET ═══
executeQuery()  → SELECT            → returns ResultSet
executeUpdate() → INSERT/UPDATE/DEL → returns int (rows affected, =1 ok)

ResultSet = rows/cols from SELECT ; cursor-based
  rs.next()        → move fwd 1 row ; true if more rows, false at end
  getXXX(colName)  → read by column name
  getXXX(colIndex) → 1-BASED (first col = 1, NOT 0!)
  ⚠ only 1 ResultSet open per Statement at a time

loop: while(rs.next()){ rs.getInt("id"); rs.getString("name"); }
  aid: ResultSet = cursor on a list ; next() steps down, stops at false
  doodle: vertical list of rows, ▸ arrow pointing at "current row"

CRUD ↔ method map

═══ PREPARED STATEMENT ═══
PreparedStatement = precompiled SQL with ? placeholders
  ...WHERE salary > ? AND department = ?
  → ps.setInt(1, 80000); ps.setString(2, "Legal");

BENEFITS
  • easier to set param values (typed setters)
  • PREVENTS SQL INJECTION   ★ key interview answer
  • may improve perf (compiled once, plan reused)
  works for INSERT / UPDATE / DELETE too, not just SELECT

NULL: ps.setNull(i, Types.X) to set ; rs.wasNull() to detect [+]
  aid: fill-in-the-blank form → blanks can't smuggle SQL
  doodle: a form with [____] blanks, values dropping into them

═══ CONFIG & METADATA ═══
CONFIG: don't hardcode creds → externalize
  java.util.Properties → read/write key=value config file
  db.url=... db.user=... → Properties.load() at startup

METADATA = "data about the data"
  DatabaseMetaData   ← connection.getMetaData()
    getDatabaseProductName / getDriverName / getTables / getColumns
  ResultSetMetaData  ← rs.getMetaData()
    getColumnCount / getColumnName / getColumnType
    isAutoIncrement / isNullable

API PACKAGES: java.sql + javax.sql
  javax.sql.DataSource → connection pooling (modern, preferred) [+]

═══ TRANSACTIONS ═══
Transaction = multiple ops as 1 logical unit → ALL or NOTHING
  = atomicity (the A in ACID: Atomicity Consistency Isolation Durability) [+]

EX: transfer $5  A → B
  begin → read A → A−5 → read B → B+5
  crash after A−5 but before B+5  → $5 VANISHES
  txn wraps all 5 → either all stick or none do
  doodle: 2 buckets A & B, arrow A→B, dotted box around all = 1 unit
END A TXN
  COMMIT   → finalize all stmts, permanent, visible to others
  ROLLBACK → abort, discard uncommitted, revert to original
  aid: COMMIT = Save-all ; ROLLBACK = Undo-all

TCL (Transaction Control Language)
  COMMIT    → make permanent
  ROLLBACK  → undo all
  SAVEPOINT → checkpoint ; ROLLBACK TO sp = partial undo [+]
  doodle: timeline ──•sp1──✗ , curved arrow back to sp1 = ROLLBACK TO
JDBC TXN MGMT
  ⚠ auto-commit = ON by default (each stmt commits itself!)
  con.setAutoCommit(false);          // begin
  try   { ...updates...; con.commit(); }   // all ok
  catch { con.rollback(); }                // failure → undo all

One numbered subsection per concept. Each opens with a one-line claim, then a from-scratch explanation, then callouts. [+] marks anything added or corrected beyond the core material. Each links to its matching code sample.

1. What JDBC is #

JDBC (Java Database Connectivity) is a standard Java API for talking to SQL databases from Java code.

It does three things: establishes a connection to a database, sends SQL statements to it, and processes the results that come back. JDBC is also a specification — a contract telling third-party database vendors (Oracle, MySQL, etc.) how to write their drivers so any Java program can talk to their specific database using the same standard API.

Real-world. Almost every Java backend that stores data — a banking app, an e-commerce inventory system, a payroll tool — sits on JDBC, either directly or under a higher-level framework (Spring's JdbcTemplate, or an ORM like Hibernate, which use JDBC internally). [+]

2. JDBC architecture #

JDBC sits in two layers: the JDBC API your code calls, and the driver layer that translates those calls for a specific database.

• JDBC API — the application-to-DriverManager interfaces your code programs against (Connection, Statement, ResultSet, …).
• DriverManager — picks and connects your app to the right driver based on the database connection string (the JDBC URL).
• JDBC Driver — provides the actual connection and converts generic JDBC calls into calls the specific database understands. Vendors supply the driver.

Described diagram. Java App → JDBC API → DriverManager → (chooses) → Vendor Driver → Database. The DriverManager is the switchboard in the middle; it routes you to the correct driver.

3. The six connection steps #

Connecting a Java app to a database follows a fixed six-step recipe — every JDBC program is a variation of it.

1. Instantiate the proper driver — load the vendor's driver class.
2. Open a connection — assemble the JDBC URL.
3. Establish the connection — call DriverManager.getConnection().
4. Query the database — run a SELECT, or an INSERT / UPDATE / DELETE.
5. Process the result — read rows from the ResultSet.
6. Close the connection — release resources.

→ See full runnable walkthrough: JDBCDemo.java

4. Step 1 — Instantiate the driver #

You load the driver class by name with Class.forName("<driver class>"). The class name is vendor-dependent.

Key points: Class.forName("oracle.jdbc.driver.OracleDriver");

5. Step 2 — Open the connection (JDBC URL) #

To connect you need a connection string in the form of a JDBC URL.

Basic syntax: jdbc:<driver protocol>:<driver connection details>

How to read a URL. Every JDBC URL starts with jdbc:, names the protocol/vendor, then gives host, port, and database. In the MySQL example: localhost is the host, 3306 is MySQL's default port, demodb is the database. [+]

6. Step 3 — Establish the connection #

Get a live connection with DriverManager.getConnection(...).

• DriverManager.getConnection(url)
• DriverManager.getConnection(url, user, password)

It returns a Connection object — your handle to the database for the rest of the program.

Failures throw exceptions:

• java.sql.SQLException: bad url or credentials — wrong URL, username, or password.
• java.lang.ClassNotFoundException: JDBC driver not in classpath — driver jar missing.

→ See full runnable walkthrough: JDBCDemo.java

7. Step 4 — Query / modify (Statement) #

You run SQL through a Statement object, using one of two methods depending on whether you're reading or writing.

executeUpdate() returns the count of rows changed (should be 1 for a successful single-row insert).

→ See: JDBCInsertDemo.java

8. Step 5 — Process the ResultSet #

A SELECT's rows and columns come back as a ResultSet, which you walk row-by-row using a cursor.

• ResultSet.next() — moves the cursor forward one row; returns true if there are more rows, false when you've run out. This is what you loop on.
• getXXX(columnName) — read a column by name (e.g. getString("name") ).
• getXXX(columnIndex) — read by position. Indexes are one-based (first column is 1, not 0).

Key facts. A ResultSet maintains a cursor pointing at its current row; next() advances it. Only one ResultSet per Statement can be open at a time — opening a second on the same Statement closes the first.

→ See: JDBCDemo.java

9. Step 6 — Close the connection #

Always close the connection (and ideally the Statement and ResultSet) when done.

This is called out as its own step to stress its importance.

10. Basic CRUD operations #

The four basic JDBC operations map to the four CRUD actions, each demonstrated by its own demo class.

The session demos these as JDBCDemo.java (query), JDBCInsertDemo.java, JDBCUpdateDemo.java, JDBCDeleteDemo.java, run against a sample employees /demo database created from an employees.sql script.

→ See: Insert, Update, Delete

11. Prepared Statements #

A PreparedStatement is a precompiled SQL statement with placeholders (? ) you fill in later, instead of hardcoding values into the SQL string.

Instead of concatenating values into ... WHERE salary >80000 AND department = 'Legal', you write placeholders ... WHERE salary >? AND department = ? and bind values: ps.setInt(1, 80000); ps.setString(2, "Legal"); [+]

Three benefits:

• Easier to set SQL parameter values — typed setters instead of fragile string building.
• Prevents SQL injection — parameters are sent separately from the SQL, so user input can't be interpreted as SQL. (A common slip is "SQL dependency injection"; the correct term is SQL injection. [+] )
• May improve performance — the database compiles the statement once and can reuse the execution plan.

Prepared statements work for INSERT, UPDATE, and DELETE too, not just SELECT.

NULL values. To set a parameter to SQL NULL use ps.setNull(index, java.sql.Types.XXX) rather than a Java null, and check rs.wasNull() after a getter to detect a NULL you just read. [+]

→ See: JDBCPreparedStatementDemo.java

12. Statement vs PreparedStatement #

Use Statement for one-off static SQL; use PreparedStatement whenever there are parameters or the query repeats. [+ table synthesizes the deck's points]

13. Database configuration #

Hardcoding connection details is fine for learning but bad practice; externalize them into a configuration file.

Note the app has connection info hardcoded (easy for beginners), but ideally this lives in a config file. Java provides java.util.Properties to read and write such files (demo: JDBCPropertiesDemo.java ).

→ See: JDBCPropertiesDemo.java

14. Metadata — Database & ResultSet #

Metadata is "data about the data" — JDBC lets you ask the database about its own structure, and ask a result set about its columns.

DatabaseMetaData — describes the database itself, from the Connection: DatabaseMetaData dbMetaData = connection.getMetaData(); Useful methods: getDatabaseProductName(), getDriverName(), getTables(), getColumns(). (Demo: JDBCDatabaseMetaDataDemo.java.)

ResultSetMetaData — describes the columns of a query result, from the ResultSet: ResultSetMetaData rsMetaData = rs.getMetaData(); Useful methods: getColumnCount(), getColumnName(), getColumnType(), isAutoIncrement(), isNullable(). (A common misspelling is "ResultMetaData"; the correct interface is ResultSetMetaData. [+] )

→ See: JDBCResultSetMetaDataDemo.java

15. JDBC API debrief — key classes #

The whole JDBC API lives in two packages and a small set of core types.

Packages: java.sql and javax.sql.

From javax.sql: DataSource — used for connection pooling. [+] A DataSource is the modern, preferred way to obtain connections in real applications (it reuses pooled connections instead of opening a fresh one each time, which is expensive).

16. What is a transaction? #

A transaction is a sequence of multiple database operations treated as a single logical unit of work — it happens wholly or not at all.

The classic example: transferring $5 from Account A to Account B.

1. Create a record to transfer $5 from A to B — the begin of the transaction.
2. Read the balance from Account A.
3. Subtract $5 from A's balance.
4. Read the balance from Account B.
5. Add $5 credit to Account B.

Why "all or nothing" matters. If the system crashes after step 3 (money removed from A) but before step 5 (money added to B), $5 has vanished. Wrapping all five steps in one transaction means either every step sticks, or none does — balances are never left in an inconsistent in-between state.

Real-world. Any operation touching multiple rows or tables that must agree: bank transfers, placing an order (decrement stock + create order + charge payment), booking a seat. [+]

17. Transactions in a database — COMMIT & ROLLBACK #

A transaction groups one or more SQL statements that execute together, and ends with either COMMIT or ROLLBACK.

• COMMIT — execute all the statements as one unit and finalize the updates. Changes become permanent and visible to others.
• ROLLBACK — abort the transaction; all uncommitted statements are discarded and the database reverts to its original state.

Example: updating several tables due to a customer purchase — all the updates commit together, or none should.

18. TCL — Transaction Control Language #

In SQL, the commands that manage transactions are called Transaction Control Language (TCL): COMMIT, ROLLBACK, and SAVEPOINT.

The session demonstrates these via a tcl.sql script. [+] A typical flow: do some work, SAVEPOINT sp1, do more work, then either ROLLBACK TO sp1 (undo the later part) or COMMIT (keep everything).

→ See: tcl.sql

19. Transaction management in JDBC #

By default a JDBC Connection is in auto-commit mode — every statement commits immediately. To use real transactions you must turn auto-commit off and control commit/rollback yourself.

• Default: auto-commit — each statement is its own transaction, committed as soon as it runs.
• You must explicitly turn off auto-commit to group statements.
• Then the developer controls commit or rollback.

→ See full runnable demo: JDBCTransactionDemo.java

★ Recap #

• JDBC = standard Java API + vendor-driver spec for SQL databases; vendor-neutral access.
• Architecture: App → JDBC API → DriverManager → vendor Driver → DB. DriverManager routes by JDBC URL.
• Six steps: load driver → build URL → getConnection → execute → process ResultSet → close.
• execute split: executeQuery() (SELECT → ResultSet) vs executeUpdate() (INSERT/UPDATE/DELETE → row count).
• ResultSet: cursor-based; loop on next(); getXXX by name or 1-based index; one open ResultSet per Statement.
• PreparedStatement: precompiled, ? placeholders → easier params, prevents SQL injection, faster on repeat.
• Config: externalize connection details with java.util.Properties.
• Metadata: DatabaseMetaData (about the DB) and ResultSetMetaData (about columns).
• Transaction: multiple ops as one all-or-nothing unit (atomicity / ACID).
• TCL: COMMIT (finalize), ROLLBACK (undo all), SAVEPOINT (partial rollback checkpoint).
• JDBC transactions: auto-commit is ON by default — call setAutoCommit(false), then commit/rollback manually.

Standard library only (java.sql / java.util ), targeting Java 8+. Every class the notes reference is written out in full. Each block has a filename header, a run command, inline comments, and an Expected-output box. You need the MySQL Connector/J jar on the classpath and the demo database from Part IV setup.

0. employees.sql — schema & seed data #

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');

1. JDBCDemo.java — query (Steps 1–6) ↔ notes 3,6,8 #

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();
    }
  }
}

2. JDBCInsertDemo.java — create ↔ notes 7,10 #

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();
    }
  }
}

3. JDBCUpdateDemo.java — update ↔ note 10 #

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);
    }
  }
}

4. JDBCDeleteDemo.java — delete ↔ note 10 #

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);
    }
  }
}

5. JDBCPreparedStatementDemo.java — prepared + NULL ↔ notes 11,12 #

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());}
    }
  }
}

6. JDBCPropertiesDemo.java + db.properties — externalized config ↔ note 13 #

# 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());
    }
  }
}

7. JDBCResultSetMetaDataDemo.java — metadata-driven printer ↔ note 14 #

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();
    }
  }
}

8. tcl.sql — TCL commands (SAVEPOINT demo) ↔ note 18 #

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');

9. JDBCTransactionDemo.java — commit/rollback (the $5 transfer) ↔ notes 16,19 #

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) {}
    }
  }
}

Six graded exercises, Warm-up → Core → Challenge. Each has a goal, numbered steps, a predict-then-verify prompt, and a collapsible solution. Do the one-time setup first.

import java.sql.*;

public class Ex1 {
  public static void main(String[] a) throws SQLException {
    try (Connection c = DriverManager.getConnection("jdbc:mysql://localhost:3306/demo", "student", "student");
        Statement s = c.createStatement();
        ResultSet rs = s.executeQuery("SELECT name, department FROM employees")) {
            while (rs.next()) System.out.println(rs.getString("name") + " — " + rs.getString("department"));
    }
  }
}

import java.sql.*;

public class Ex2 {
  public static void main(String[] a) throws SQLException {
    String sql = "INSERT INTO employees(name,department,salary,hire_date) " + "VALUES ('Nia Bose','Support',64000,'2024-05-01')";
    try (Connection c = DriverManager.getConnection("jdbc:mysql://localhost:3306/demo", "student", "student");
        Statement s = c.createStatement()) {
            System.out.println("rows = " + s.executeUpdate(sql));
    }
  }
}

import java.sql.*;

public class Ex3 {
  public static void main(String[] a) throws SQLException {
    String sql = "SELECT name,salary FROM employees WHERE salary > ? AND department = ?";
    try (Connection c = DriverManager.getConnection("jdbc:mysql://localhost:3306/demo", "student", "student");
        PreparedStatement ps = c.prepareStatement(sql)) {
            ps.setInt(1, 75000);
            ps.setString(2, "Engineering");
            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) System.out.println(rs.getString(1) + " " + rs.getInt(2));
            }
    }
  }
}

import java.sql.*;

public class Ex4 {
  public static void main(String[] a) throws SQLException {
    try (Connection c = DriverManager.getConnection("jdbc:mysql://localhost:3306/demo", "student", "student");
        Statement s = c.createStatement();
        ResultSet rs = s.executeQuery("SELECT * FROM employees")) {
            ResultSetMetaData md = rs.getMetaData();
            for (int i = 1; i <= md.getColumnCount(); i++) System.out.print(md.getColumnName(i) + "  ");
            System.out.println();
    }
  }
}

import java.sql.*;

public class Ex5 {
  public static void main(String[] a) throws SQLException {
    String url = "jdbc:mysql://localhost:3306/demo";
    Connection con = null;
    try {
            con = DriverManager.getConnection(url, "student", "student");
            con.setAutoCommit(false);                 // begin txn
            Statement s = con.createStatement();
            s.executeUpdate("UPDATE employees SET salary=salary-5 WHERE name='Asha Rao'");
            int credited = s.executeUpdate("UPDATE employees SET salary=salary+5 WHERE name='Ghost User'");
            if (credited == 0)                        // recipient doesn't exist
            throw new SQLException("recipient missing");
            con.commit();
            System.out.println("COMMIT");
    } catch (SQLException e) {
            if (con != null) con.rollback();          // undo the debit too
            System.out.println("ROLLBACK: " + e.getMessage());
    } finally {
            if (con != null) con.close();
    }
  }
}

import java.sql.*;
import java.io.*;
import java.util.Properties;

public class Ex6 {
  public static void main(String[] a) throws Exception {
    Properties p = new Properties();
    try (FileInputStream in = new FileInputStream("db.properties")) { p.load(in); }
    try (Connection c = DriverManager.getConnection(p.getProperty("db.url"), p.getProperty("db.user"), p.getProperty("db.password"))) {
            try (PreparedStatement ins = c.prepareStatement("INSERT INTO employees(name,department,salary,hire_date) VALUES (?,?,?,?)")) {
                ins.setString(1, "Oren Pax");
                ins.setNull(2, Types.VARCHAR);          // department = NULL
                ins.setInt(3, 50000);
                ins.setNull(4, Types.DATE);
                ins.executeUpdate();
            }
            try (PreparedStatement q = c.prepareStatement("SELECT department FROM employees WHERE name = ?")) {
                q.setString(1, "Oren Pax");
                try (ResultSet rs = q.executeQuery()) {
                    if (rs.next()) {
                        String d = rs.getString("department");
                        System.out.println("dept = " + (rs.wasNull() ? "(null)" : d));
                    }
                }
            }
    }
  }
}