Core Concepts

JDBC (Java Database Connectivity) is a standardized Java API for interacting with relational databases. Database vendors provide JDBC-compliant drivers—typically distributed as JAR files—that implement this interface. To connect to a specific database, the corresponding driver must be included in the application’s classpath.

Basic Workflow

1. Add Driver Dependency Place the appropriate JDBC driver JAR (e.g., mysql-connector-java-8.x.x.jar) in your project’s lib directory and configure it as a library in your IDE.

2. Establish Connection Modern JDBC drivers auto-register, so explicit Class.forName() calls are generally unnecessary. Use DriverManager.getConnection() with a valid JDBC URL:

   String dbUrl = "jdbc:mysql://localhost:3306/sample_db?useSSL=false&serverTimezone=UTC";
   String user = "root";
   String pass = "secure_password";
   Connection conn = DriverManager.getConnection(dbUrl, user, pass);
   

3. Prepare and Execute Statement

   String updateSql = "UPDATE accounts SET balance = ? WHERE account_id = ?";
   try (PreparedStatement pstmt = conn.prepareStatement(updateSql)) {
       pstmt.setDouble(1, 5200.0);
       pstmt.setInt(2, 101);
       int affectedRows = pstmt.executeUpdate();
       System.out.println("Updated rows: " + affectedRows);
   }
   

4. Resource Management Always close resources in reverse order of creation. Prefer try-with-resources to guarantee cleanup:

   try (Connection c = dataSource.getConnection();
        PreparedStatement ps = c.prepareStatement(query);
        ResultSet rs = ps.executeQuery()) {
       // process results
   }
   

Key JDBC Interfaces

Connection

Manages physical connections and transaction control:

• setAutoCommit(false) disables auto-commit mode, enabling manual transaction boundaries.
• commit() finalizes changes.
• rollback() reverts uncommitted changes.

Example with transaction safety:

conn.setAutoCommit(false);
try {
    executeTransfer(conn, "ACC001", "ACC002", 1000.0);
    executeFeeDeduction(conn, "ACC001", 5.0);
    conn.commit();
} catch (SQLException e) {
    conn.rollback();
    throw e;
}

Statement vs PreparedStatement

• Statement: Suitable for static SQL. Vulnerable to injection if string concatenation is used:

  // UNSAFE — do NOT use
  String unsafeSql = "SELECT * FROM users WHERE login = '" + userInput + "'";
  

• PreparedStatement: Uses parameterized queries, automatically escaping inputs:

  String safeSql = "SELECT * FROM users WHERE login = ? AND status = ?";
  try (PreparedStatement ps = conn.prepareStatement(safeSql)) {
      ps.setString(1, username);
      ps.setString(2, "active");
      ResultSet rs = ps.executeQuery();
  }
  

ResultSet

Ancapsulates query results. Iterate using next() and extract values by column index or name:

List<Account> accounts = new ArrayList<>();
while (rs.next()) {
    Account acc = new Account(
        rs.getInt("id"),
        rs.getString("holder_name"),
        rs.getBigDecimal("balance")
    );
    accounts.add(acc);
}

Connection Pooling with Druid

Repeatedly opening/closing connections is expensive. Connection pools reuse established connections.

Druid, an open-source high-performance pool from Alibaba, requires:

1. Configuration file (druid.properties):

   driverClassName=com.mysql.cj.jdbc.Driver
   url=jdbc:mysql://localhost:3306/sample_db?useSSL=false&serverTimezone=UTC
   username=root
   password=secure_password
   initialSize=5
   maxActive=20
   maxWait=3000
   

2. Initialization and usage:

   Properties props = new Properties();
   props.load(new FileInputStream("src/main/resources/druid.properties"));
   DataSource pool = DruidDataSourceFactory.createDataSource(props);
   
   try (Connection conn = pool.getConnection()) {
       // execute queries
   }
   

Practical CRUD Operations

Assume a table products(id INT PRIMARY KEY, title VARCHAR, price DECIMAL) and matching Product POJO.

Query All:

String sql = "SELECT id, title, price FROM products";
try (PreparedStatement ps = conn.prepareStatement(sql);
     ResultSet rs = ps.executeQuery()) {
    List<Product> list = new ArrayList<>();
    while (rs.next()) {
        list.add(new Product(rs.getInt(1), rs.getString(2), rs.getBigDecimal(3)));
    }
    return list;
}

Insert:

String sql = "INSERT INTO products(title, price) VALUES(?, ?)";
try (PreparedStatement ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
    ps.setString(1, "Wireless Headphones");
    ps.setBigDecimal(2, new BigDecimal("129.99"));
    int rows = ps.executeUpdate();
    if (rows > 0) {
        try (ResultSet keys = ps.getGeneratedKeys()) {
            if (keys.next()) {
                int newId = keys.getInt(1);
            }
        }
    }
}

Update:

String sql = "UPDATE products SET title = ?, price = ? WHERE id = ?";
try (PreparedStatement ps = conn.prepareStatement(sql)) {
    ps.setString(1, "Noise-Cancelling Headphones");
    ps.setBigDecimal(2, new BigDecimal("199.99"));
    ps.setInt(3, 101);
    ps.executeUpdate();
}

Delete:

String sql = "DELETE FROM products WHERE id = ?";
try (PreparedStatement ps = conn.prepareStatement(sql)) {
    ps.setInt(1, 101);
    ps.executeUpdate();
}