How do I use savepoints in JDBC transactions?

By I Wayan Saryada in JDBC 0 Comment

Savepoints in JDBC provide fine-grained control over transactions by allowing you to roll back to a specific point within a transaction rather than undoing everything. This is particularly useful for handling optional operations or partial failures.

Key Steps to Use Savepoints

  1. Disable Auto-commit: Savepoints only work within a manual transaction.
  2. Set a Savepoint: Use connection.setSavepoint() to mark a logical point in your execution.
  3. Rollback to Savepoint: If an error occurs, use connection.rollback(savepoint).
  4. Release or Commit: Release the savepoint once it’s no longer necessary (though commit or a full rollback will also clear them).

Implementation Example

Here is how you can implement this in your project.

package org.kodejava.jdbc;

import java.sql.*;

public class SavepointExample {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/your_database";
        String user = "root";
        String password = "password";

        try (Connection conn = DriverManager.getConnection(url, user, password)) {
            // 1. Disable auto-commit
            conn.setAutoCommit(false);

            try (Statement stmt = conn.createStatement()) {
                // Execute a required operation
                stmt.executeUpdate("INSERT INTO orders (item, qty) VALUES ('Laptop', 1)");

                // 2. Set a savepoint before an "optional" or risky operation
                Savepoint savepoint1 = conn.setSavepoint("Savepoint1");

                try {
                    // Try an optional operation (e.g., updating a secondary table)
                    stmt.executeUpdate("INSERT INTO loyalty_points (user_id, points) VALUES (1, 100)");
                } catch (SQLException e) {
                    // 3. Roll back to the savepoint if the optional part fails
                    System.out.println("Optional operation failed, rolling back to savepoint.");
                    conn.rollback(savepoint1);
                }

                // 4. Commit the overall transaction
                conn.commit();
                System.out.println("Transaction committed successfully.");

            } catch (SQLException e) {
                // If the main operation fails, roll back everything
                conn.rollback();
                e.printStackTrace();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Things to Keep in Mind

  • Named vs. Unnamed Savepoints: conn.setSavepoint() returns an unnamed savepoint with a system-generated ID. conn.setSavepoint("Name") creates a named one which can be easier for debugging.
  • Release Savepoints: While not strictly mandatory in all drivers, calling connection.releaseSavepoint(savepoint) can help free up resources if you have many savepoints in a long-running transaction.
  • Driver Support: Most modern databases (MySQL, PostgreSQL, Oracle, SQL Server) support savepoints, but you can check programmatically using DatabaseMetaData.supportsSavepoints().
  • Transaction Scope: Once a transaction is committed or rolled back entirely, all associated savepoints are automatically released and become invalid.

How do I use transactions in JDBC?

By I Wayan Saryada in JDBC 0 Comment

Using transactions in JDBC is essential when you need to ensure that a group of SQL statements either all succeed or all fail together (maintaining Atomicity).

By default, a JDBC Connection is in auto-commit mode, meaning every single SQL statement is treated as its own transaction and committed immediately.

To manage transactions manually, follow these three main steps:

1. Disable Auto-Commit

The first step is to tell the connection not to commit automatically after every execution.

connection.setAutoCommit(false);

2. Perform Your Database Operations

Execute your SQL statements (inserts, updates, deletes). If any of these throw an exception, you should catch it to handle the failure.

3. Commit or Rollback

  • commit(): If everything went well, save the changes permanently.
  • rollback(): If an error occurred, undo all changes made since the last commit.

Basic Example

Here is a clean pattern using a try-with-resources block for the connection and a nested try-catch for the transaction logic:

try (Connection conn = DriverManager.getConnection(url, user, pass)) {
    // Step 1: Disable auto-commit
    conn.setAutoCommit(false);

    try (PreparedStatement pstmt1 = conn.prepareStatement("UPDATE accounts SET balance = balance - 100 WHERE id = 1");
         PreparedStatement pstmt2 = conn.prepareStatement("UPDATE accounts SET balance = balance + 100 WHERE id = 2")) {

        // Execute operations
        pstmt1.executeUpdate();
        pstmt2.executeUpdate();

        // Step 3a: Commit changes
        conn.commit();
        System.out.println("Transaction committed successfully!");

    } catch (SQLException e) {
        // Step 3b: Rollback changes if something goes wrong
        conn.rollback();
        System.err.println("Transaction rolled back due to error.");
        e.printStackTrace();
    }
} catch (SQLException e) {
    e.printStackTrace();
}

Important Tips:

  • Always use rollback() in the catch block: If you don’t roll back on failure, the connection might hold onto locks or leave the session in an inconsistent state.
  • Savepoints: If you have a very long transaction and want to roll back only a part of it, you can use conn.setSavepoint().
  • Transaction Isolation: You can control how “isolated” your transaction is from others using conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE), though the default is usually enough for standard applications.

How do I set fetch size for large queries?

By I Wayan Saryada in JDBC 0 Comment

To set the fetch size for large queries in Java using JDBC, you use the setFetchSize(int rows) method on a Statement or PreparedStatement object.

This gives the JDBC driver a hint as to the number of rows that should be fetched from the database when more rows are needed. This is particularly useful for large result sets to avoid loading everything into memory at once or to reduce the number of network round-trips.

Using JDBC Statement

Here is how you can apply it to a standard Statement:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

// ... existing code ...
try (Connection conn = DriverManager.getConnection(url, user, password);
     Statement stmt = conn.createStatement()) {

    // Set the fetch size to 100 rows
    stmt.setFetchSize(100);

    try (ResultSet rs = stmt.executeQuery("SELECT * FROM very_large_table")) {
        while (rs.next()) {
            // Process rows
        }
    }
} catch (Exception e) {
    e.printStackTrace();
}

Important Considerations

  1. Driver Support: setFetchSize is a hint. Not all JDBC drivers honor this value in the same way.
  2. MySQL Specifics: By default, the MySQL driver fetches all rows into memory. To stream results (fetch row-by-row), you must set the fetch size to Integer.MIN_VALUE and use a forward-only, read-only result set: 
    stmt.setFetchSize(Integer.MIN_VALUE);
  3. Oracle Specifics: Oracle has a default fetch size (usually 10). Increasing this to 100 or 500 can significantly improve performance for large queries.

  4. Memory vs. Network:
    • Small fetch size: Saves memory but increases network round-trips (slower).
    • Large fetch size: Reduces network round-trips (faster) but consumes more client-side memory.

Using Spring Data JPA / Jakarta EE

Since your project uses Spring Data JPA, you can also set the fetch size using the @QueryHints annotation on your repository methods:

import jakarta.persistence.QueryHint;
import org.springframework.data.jpa.repository.QueryHints;
import static org.hibernate.jpa.HibernateHints.HINT_FETCH_SIZE;

@QueryHints(value = @QueryHint(name = HINT_FETCH_SIZE, value = "100"))
List<User> findAllByStatus(String status);

How do I use auto-generated keys in JDBC?

By I Wayan Saryada in JDBC 0 Comment

To use auto-generated keys in JDBC (like an AUTO_INCREMENT primary key), you need to follow a three-step process: notify the statement you want the keys, execute the update, and then retrieve them from a special ResultSet.

Here is a practical example using PreparedStatement:

1. Prepare the Statement

When creating your PreparedStatement, you must pass the constant Statement.RETURN_GENERATED_KEYS to let the driver know you want the keys back.

String sql = "INSERT INTO users (username, email) VALUES (?, ?)";
try (PreparedStatement pstmt = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
    pstmt.setString(1, "john_doe");
    pstmt.setString(2, "[email protected]");

    // ...
}

2. Execute and Retrieve

After calling executeUpdate(), use getGeneratedKeys() to fetch the IDs. Even if you only inserted one row, the keys are returned as a ResultSet because some databases support multiple generated keys per row or batch inserts.

int affectedRows = pstmt.executeUpdate();

if (affectedRows > 0) {
    try (ResultSet generatedKeys = pstmt.getGeneratedKeys()) {
        if (generatedKeys.next()) {
            long id = generatedKeys.getLong(1);
            System.out.println("Inserted record ID: " + id);
        }
    }
}

Complete Example

Based on standard JDBC practices, here is how the implementation usually looks:

package org.kodejava.jdbc;

import java.sql.*;

public class GetGeneratedKey {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/your_db";
        String user = "root";
        String password = "password";

        String sql = "INSERT INTO authors (name) VALUES (?)";

        try (Connection conn = DriverManager.getConnection(url, user, password);
             PreparedStatement pstmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {

            pstmt.setString(1, "Herbert Schildt");
            pstmt.executeUpdate();

            // Retrieve the generated key
            try (ResultSet rs = pstmt.getGeneratedKeys()) {
                if (rs.next()) {
                    long generatedId = rs.getLong(1);
                    System.out.println("Generated ID: " + generatedId);
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Key Points to Remember:

  • Constant: Don’t forget Statement.RETURN_GENERATED_KEYS. Without it, getGeneratedKeys() will return an empty result set or throw an exception depending on the driver.
  • Column Index: Usually, the generated key is in the first column of the returned ResultSet, so rs.getLong(1) is standard.
  • Database Support: Most modern databases (MySQL, PostgreSQL, SQL Server, Oracle) support this, though the internal mechanism (Sequences vs. Identity columns) varies.

How do I batch insert data with JDBC?

By I Wayan Saryada in JDBC 0 Comment

To batch insert data with JDBC, you typically use the addBatch() and executeBatch() methods. This is much more efficient than executing individual INSERT statements because it reduces the number of round-trips between your application and the database.

The most common and secure way to do this is with a PreparedStatement.

Batch Insert with PreparedStatement

Using PreparedStatement allows you to define a template query and then add multiple sets of parameters to a single batch.

package org.kodejava.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class JDBCBatchInsert {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/your_db";
        String user = "user";
        String password = "password";

        String sql = "INSERT INTO employees (name, department) VALUES (?, ?)";

        try (Connection conn = DriverManager.getConnection(url, user, password)) {
            // 1. Disable auto-commit for better performance and transaction control
            conn.setAutoCommit(false);

            try (PreparedStatement pstmt = conn.prepareStatement(sql)) {

                // Add first record to batch
                pstmt.setString(1, "Alice");
                pstmt.setString(2, "Engineering");
                pstmt.addBatch();

                // Add second record to batch
                pstmt.setString(1, "Bob");
                pstmt.setString(2, "Marketing");
                pstmt.addBatch();

                // 2. Execute the batch
                int[] results = pstmt.executeBatch();

                // 3. Commit the transaction
                conn.commit();
                System.out.println("Batch executed. Rows affected: " + results.length);

            } catch (SQLException e) {
                // Rollback in case of error
                conn.rollback();
                e.printStackTrace();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Key Considerations

  1. setAutoCommit(false): By default, JDBC commits every statement individually. Turning this off allows the entire batch to be treated as a single transaction, which significantly boosts performance.
  2. addBatch(): Adds the current set of parameters to the internal list of commands.
  3. executeBatch(): Sends all the gathered commands to the database. It returns an int[] where each element represents the update count for the corresponding command in the batch.
  4. Batch Size: For very large datasets (e.g., thousands of rows), don’t add everything to a single batch. Instead, execute the batch every 500–1000 rows to avoid memory issues: 
    if (count % 1000 == 0) {
    pstmt.executeBatch();
    conn.commit(); // Optional: commit periodically
}

Using Statement

While possible, using Statement.addBatch(String sql) is generally discouraged for inserts involving variables because it is vulnerable to SQL injection and harder for the database to optimize. Use PreparedStatement whenever possible.