Advanced JDBC features for building robust, efficient, and secure database applications.
1. Connection Pooling
Using HikariCP (Popular Connection Pool)
import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import java.sql.*;
public class HikariCPExample {
private static HikariDataSource dataSource;
static {
HikariConfig config = new HikariConfig();
config.setJdbcUrl("jdbc:mysql://localhost:3306/company");
config.setUsername("username");
config.setPassword("password");
config.setDriverClassName("com.mysql.cj.jdbc.Driver");
// Connection pool settings
config.setMaximumPoolSize(20);
config.setMinimumIdle(5);
config.setConnectionTimeout(30000);
config.setIdleTimeout(600000);
config.setMaxLifetime(1800000);
config.setLeakDetectionThreshold(2000);
// MySQL-specific optimizations
config.addDataSourceProperty("cachePrepStmts", "true");
config.addDataSourceProperty("prepStmtCacheSize", "250");
config.addDataSourceProperty("prepStmtCacheSqlLimit", "2048");
dataSource = new HikariDataSource(config);
}
public static Connection getConnection() throws SQLException {
return dataSource.getConnection();
}
public static void main(String[] args) {
try (Connection conn = getConnection();
PreparedStatement stmt = conn.prepareStatement("SELECT * FROM employees");
ResultSet rs = stmt.executeQuery()) {
while (rs.next()) {
System.out.println("Employee: " + rs.getString("name"));
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
2. Transaction Management
Manual Transaction Control
import java.sql.*;
public class TransactionManagement {
private Connection connection;
public TransactionManagement(Connection connection) {
this.connection = connection;
}
public void transferMoney(int fromAccount, int toAccount, double amount)
throws SQLException {
boolean autoCommit = connection.getAutoCommit();
try {
connection.setAutoCommit(false); // Start transaction
// Deduct from source account
deductFromAccount(fromAccount, amount);
// Add to destination account
addToAccount(toAccount, amount);
connection.commit(); // Commit transaction
System.out.println("Transfer successful");
} catch (SQLException e) {
connection.rollback(); // Rollback on error
System.out.println("Transfer failed: " + e.getMessage());
throw e;
} finally {
connection.setAutoCommit(autoCommit); // Restore original state
}
}
private void deductFromAccount(int accountId, double amount) throws SQLException {
String sql = "UPDATE accounts SET balance = balance - ? WHERE id = ? AND balance >= ?";
try (PreparedStatement stmt = connection.prepareStatement(sql)) {
stmt.setDouble(1, amount);
stmt.setInt(2, accountId);
stmt.setDouble(3, amount);
int rowsAffected = stmt.executeUpdate();
if (rowsAffected == 0) {
throw new SQLException("Insufficient funds or account not found");
}
}
}
private void addToAccount(int accountId, double amount) throws SQLException {
String sql = "UPDATE accounts SET balance = balance + ? WHERE id = ?";
try (PreparedStatement stmt = connection.prepareStatement(sql)) {
stmt.setDouble(1, amount);
stmt.setInt(2, accountId);
int rowsAffected = stmt.executeUpdate();
if (rowsAffected == 0) {
throw new SQLException("Destination account not found");
}
}
}
// Using savepoints for nested transactions
public void complexOperation() throws SQLException {
boolean autoCommit = connection.getAutoCommit();
Savepoint savepoint = null;
try {
connection.setAutoCommit(false);
// Operation 1
updateTable1();
savepoint = connection.setSavepoint("SAVEPOINT_1");
try {
// Operation 2 - might fail
updateTable2();
// Operation 3
updateTable3();
} catch (SQLException e) {
connection.rollback(savepoint); // Rollback to savepoint
System.out.println("Partial rollback performed");
}
connection.commit();
} catch (SQLException e) {
connection.rollback();
throw e;
} finally {
connection.setAutoCommit(autoCommit);
}
}
private void updateTable1() throws SQLException {
// Implementation
}
private void updateTable2() throws SQLException {
// Implementation
}
private void updateTable3() throws SQLException {
// Implementation
}
}
3. Batch Processing
Batch Insert/Update Operations
import java.sql.*;
import java.util.*;
public class BatchProcessing {
private Connection connection;
public BatchProcessing(Connection connection) {
this.connection = connection;
}
// Batch insert with PreparedStatement
public void batchInsertEmployees(List<Employee> employees) throws SQLException {
String sql = "INSERT INTO employees (name, email, department, salary) VALUES (?, ?, ?, ?)";
try (PreparedStatement stmt = connection.prepareStatement(sql)) {
connection.setAutoCommit(false);
for (Employee employee : employees) {
stmt.setString(1, employee.getName());
stmt.setString(2, employee.getEmail());
stmt.setString(3, employee.getDepartment());
stmt.setDouble(4, employee.getSalary());
stmt.addBatch();
// Execute batch in chunks to avoid memory issues
if (employees.indexOf(employee) % 1000 == 0) {
stmt.executeBatch();
}
}
// Execute remaining batch
int[] updateCounts = stmt.executeBatch();
connection.commit();
System.out.println("Inserted " + updateCounts.length + " employees");
} catch (SQLException e) {
connection.rollback();
throw e;
} finally {
connection.setAutoCommit(true);
}
}
// Batch update with different operations
public void batchMixedOperations(List<String> operations) throws SQLException {
try (Statement stmt = connection.createStatement()) {
connection.setAutoCommit(false);
for (String operation : operations) {
stmt.addBatch(operation);
}
int[] results = stmt.executeBatch();
connection.commit();
System.out.println("Batch operations completed. Results: " + Arrays.toString(results));
} catch (SQLException e) {
connection.rollback();
throw e;
} finally {
connection.setAutoCommit(true);
}
}
// Using BatchUpdateException for error handling
public void batchInsertWithErrorHandling(List<Employee> employees) {
String sql = "INSERT INTO employees (name, email, department, salary) VALUES (?, ?, ?, ?)";
try (PreparedStatement stmt = connection.prepareStatement(sql)) {
connection.setAutoCommit(false);
for (Employee employee : employees) {
stmt.setString(1, employee.getName());
stmt.setString(2, employee.getEmail());
stmt.setString(3, employee.getDepartment());
stmt.setDouble(4, employee.getSalary());
stmt.addBatch();
}
try {
int[] updateCounts = stmt.executeBatch();
connection.commit();
System.out.println("Successfully inserted batch");
} catch (BatchUpdateException bue) {
connection.rollback();
int[] updateCounts = bue.getUpdateCounts();
for (int i = 0; i < updateCounts.length; i++) {
if (updateCounts[i] == Statement.EXECUTE_FAILED) {
System.err.println("Failed to insert employee: " + employees.get(i));
}
}
throw bue;
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
connection.setAutoCommit(true);
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
class Employee {
private String name;
private String email;
private String department;
private double salary;
// Constructor, getters, setters
public Employee(String name, String email, String department, double salary) {
this.name = name;
this.email = email;
this.department = department;
this.salary = salary;
}
public String getName() { return name; }
public String getEmail() { return email; }
public String getDepartment() { return department; }
public double getSalary() { return salary; }
}
4. Advanced ResultSet Handling
Scrollable and Updatable ResultSets
import java.sql.*;
public class AdvancedResultSetExample {
private Connection connection;
public AdvancedResultSetExample(Connection connection) {
this.connection = connection;
}
// Scrollable ResultSet
public void scrollableResultSetDemo() throws SQLException {
String sql = "SELECT id, name, salary FROM employees ORDER BY salary DESC";
try (Statement stmt = connection.createStatement(
ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY);
ResultSet rs = stmt.executeQuery(sql)) {
// Move to last row
if (rs.last()) {
System.out.println("Highest paid employee: " + rs.getString("name"));
// Move to first row
rs.first();
System.out.println("First employee: " + rs.getString("name"));
// Get absolute position
rs.absolute(5);
System.out.println("5th employee: " + rs.getString("name"));
// Relative movement
rs.relative(-2);
System.out.println("3rd employee: " + rs.getString("name"));
// Check position
System.out.println("Current row: " + rs.getRow());
System.out.println("Total rows: " + getRowCount(rs));
}
}
}
// Updatable ResultSet
public void updatableResultSetDemo() throws SQLException {
String sql = "SELECT id, name, salary FROM employees WHERE department = ?";
try (PreparedStatement stmt = connection.prepareStatement(
sql,
ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_UPDATABLE)) {
stmt.setString(1, "Engineering");
ResultSet rs = stmt.executeQuery();
while (rs.next()) {
// Update salary
double currentSalary = rs.getDouble("salary");
double newSalary = currentSalary * 1.1; // 10% raise
rs.updateDouble("salary", newSalary);
rs.updateRow(); // Commit the update
System.out.println("Updated salary for: " + rs.getString("name"));
}
// Insert new row through ResultSet
rs.moveToInsertRow();
rs.updateString("name", "New Employee");
rs.updateDouble("salary", 50000.0);
rs.insertRow();
// Delete a row
rs.absolute(1); // Move to first row
rs.deleteRow();
} catch (SQLException e) {
e.printStackTrace();
}
}
// ResultSet metadata
public void printResultSetMetadata(String tableName) throws SQLException {
String sql = "SELECT * FROM " + tableName + " WHERE 1=0"; // No data, just metadata
try (Statement stmt = connection.createStatement();
ResultSet rs = stmt.executeQuery(sql)) {
ResultSetMetaData metaData = rs.getMetaData();
int columnCount = metaData.getColumnCount();
System.out.println("Table: " + tableName);
System.out.println("Columns: " + columnCount);
System.out.println("----------------------------------------");
for (int i = 1; i <= columnCount; i++) {
System.out.println("Column " + i + ":");
System.out.println(" Name: " + metaData.getColumnName(i));
System.out.println(" Type: " + metaData.getColumnTypeName(i));
System.out.println(" Size: " + metaData.getPrecision(i));
System.out.println(" Nullable: " +
(metaData.isNullable(i) == ResultSetMetaData.columnNullable ? "YES" : "NO"));
System.out.println(" Auto Increment: " + metaData.isAutoIncrement(i));
System.out.println();
}
}
}
private int getRowCount(ResultSet rs) throws SQLException {
int currentRow = rs.getRow();
rs.last();
int rowCount = rs.getRow();
if (currentRow == 0) {
rs.beforeFirst();
} else {
rs.absolute(currentRow);
}
return rowCount;
}
// Handling BLOB and CLOB data
public void handleLargeObjects() throws SQLException {
// Store file as BLOB
String insertSQL = "INSERT INTO documents (name, content, file_data) VALUES (?, ?, ?)";
try (PreparedStatement stmt = connection.prepareStatement(insertSQL)) {
stmt.setString(1, "report.pdf");
// CLOB for text content
String largeText = "This is a large text content...";
stmt.setCharacterStream(2, new java.io.StringReader(largeText), largeText.length());
// BLOB for binary data
byte[] fileData = "Simulated PDF content".getBytes();
stmt.setBytes(3, fileData); // Or use setBinaryStream for larger files
stmt.executeUpdate();
}
// Retrieve BLOB/CLOB data
String selectSQL = "SELECT name, content, file_data FROM documents WHERE name = ?";
try (PreparedStatement stmt = connection.prepareStatement(selectSQL)) {
stmt.setString(1, "report.pdf");
ResultSet rs = stmt.executeQuery();
if (rs.next()) {
String name = rs.getString("name");
// Read CLOB
Clob contentClob = rs.getClob("content");
String content = contentClob.getSubString(1, (int) contentClob.length());
// Read BLOB
Blob fileBlob = rs.getBlob("file_data");
byte[] fileData = fileBlob.getBytes(1, (int) fileBlob.length());
System.out.println("Retrieved document: " + name);
System.out.println("Content length: " + content.length());
System.out.println("File size: " + fileData.length + " bytes");
}
}
}
}
5. Database Metadata
import java.sql.*;
public class DatabaseMetadataExplorer {
private Connection connection;
public DatabaseMetadataExplorer(Connection connection) {
this.connection = connection;
}
public void exploreDatabase() throws SQLException {
DatabaseMetaData dbMetaData = connection.getMetaData();
// Database information
System.out.println("Database Product: " + dbMetaData.getDatabaseProductName());
System.out.println("Database Version: " + dbMetaData.getDatabaseProductVersion());
System.out.println("Driver Name: " + dbMetaData.getDriverName());
System.out.println("Driver Version: " + dbMetaData.getDriverVersion());
System.out.println("JDBC Version: " + dbMetaData.getJDBCMajorVersion() + "." +
dbMetaData.getJDBCMinorVersion());
// Supported features
System.out.println("\nSupported Features:");
System.out.println("Supports transactions: " + dbMetaData.supportsTransactions());
System.out.println("Supports batch updates: " + dbMetaData.supportsBatchUpdates());
System.out.println("Supports result set concurrency: " +
dbMetaData.supportsResultSetConcurrency(
ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE));
// List all tables
System.out.println("\nTables in database:");
try (ResultSet tables = dbMetaData.getTables(null, null, "%", new String[]{"TABLE"})) {
while (tables.next()) {
String tableName = tables.getString("TABLE_NAME");
String tableType = tables.getString("TABLE_TYPE");
System.out.println("Table: " + tableName + " (" + tableType + ")");
}
}
// List all schemas
System.out.println("\nSchemas in database:");
try (ResultSet schemas = dbMetaData.getSchemas()) {
while (schemas.next()) {
System.out.println("Schema: " + schemas.getString("TABLE_SCHEM"));
}
}
}
public void exploreTable(String tableName) throws SQLException {
DatabaseMetaData dbMetaData = connection.getMetaData();
System.out.println("\nColumns in table: " + tableName);
try (ResultSet columns = dbMetaData.getColumns(null, null, tableName, "%")) {
while (columns.next()) {
String columnName = columns.getString("COLUMN_NAME");
String dataType = columns.getString("TYPE_NAME");
int columnSize = columns.getInt("COLUMN_SIZE");
String isNullable = columns.getString("IS_NULLABLE");
String isAutoIncrement = columns.getString("IS_AUTOINCREMENT");
System.out.printf(" %-20s %-15s Size: %-5s Nullable: %-3s AutoInc: %s%n",
columnName, dataType, columnSize, isNullable, isAutoIncrement);
}
}
// Primary keys
System.out.println("\nPrimary keys for table: " + tableName);
try (ResultSet primaryKeys = dbMetaData.getPrimaryKeys(null, null, tableName)) {
while (primaryKeys.next()) {
String pkColumn = primaryKeys.getString("COLUMN_NAME");
short keySeq = primaryKeys.getShort("KEY_SEQ");
String pkName = primaryKeys.getString("PK_NAME");
System.out.println(" PK Column: " + pkColumn + " (Sequence: " + keySeq + ", Name: " + pkName + ")");
}
}
// Foreign keys
System.out.println("\nForeign keys for table: " + tableName);
try (ResultSet foreignKeys = dbMetaData.getImportedKeys(null, null, tableName)) {
while (foreignKeys.next()) {
String fkColumn = foreignKeys.getString("FKCOLUMN_NAME");
String pkTable = foreignKeys.getString("PKTABLE_NAME");
String pkColumn = foreignKeys.getString("PKCOLUMN_NAME");
System.out.println(" FK Column: " + fkColumn + " -> " + pkTable + "." + pkColumn);
}
}
// Indexes
System.out.println("\nIndexes for table: " + tableName);
try (ResultSet indexes = dbMetaData.getIndexInfo(null, null, tableName, false, false)) {
while (indexes.next()) {
String indexName = indexes.getString("INDEX_NAME");
String columnName = indexes.getString("COLUMN_NAME");
boolean nonUnique = indexes.getBoolean("NON_UNIQUE");
String indexType = getIndexType(indexes.getShort("TYPE"));
if (indexName != null) {
System.out.printf(" Index: %-20s Column: %-15s Unique: %-5s Type: %s%n",
indexName, columnName, !nonUnique, indexType);
}
}
}
}
private String getIndexType(short type) {
switch (type) {
case DatabaseMetaData.tableIndexStatistic: return "STATISTIC";
case DatabaseMetaData.tableIndexClustered: return "CLUSTERED";
case DatabaseMetaData.tableIndexHashed: return "HASHED";
case DatabaseMetaData.tableIndexOther: return "OTHER";
default: return "UNKNOWN";
}
}
}
6. RowSet Implementations
Using CachedRowSet for Disconnected Operations
import javax.sql.rowset.*;
import java.sql.*;
import java.util.*;
public class RowSetExamples {
private Connection connection;
public RowSetExamples(Connection connection) {
this.connection = connection;
}
// CachedRowSet - disconnected operation
public void cachedRowSetDemo() throws SQLException {
String sql = "SELECT id, name, email, department FROM employees";
try (CachedRowSet rowSet = RowSetProvider.newFactory().createCachedRowSet()) {
// Configure rowset
rowSet.setUrl("jdbc:mysql://localhost:3306/company");
rowSet.setUsername("username");
rowSet.setPassword("password");
rowSet.setCommand(sql);
rowSet.execute(); // Fetches data and disconnects
// Work offline
while (rowSet.next()) {
String name = rowSet.getString("name");
String department = rowSet.getString("department");
// Update data offline
if ("Engineering".equals(department)) {
rowSet.updateString("email", name.toLowerCase() + "@engineering.company.com");
rowSet.updateRow();
}
}
// Reconnect and sync changes
rowSet.acceptChanges();
System.out.println("Changes synchronized with database");
}
}
// WebRowSet - XML support
public void webRowSetDemo() throws SQLException {
String sql = "SELECT id, name, department FROM employees WHERE department = ?";
try (WebRowSet webRowSet = RowSetProvider.newFactory().createWebRowSet()) {
webRowSet.setUrl("jdbc:mysql://localhost:3306/company");
webRowSet.setUsername("username");
webRowSet.setPassword("password");
webRowSet.setCommand(sql);
webRowSet.setString(1, "Sales");
webRowSet.execute();
// Write to XML
java.io.StringWriter writer = new java.io.StringWriter();
webRowSet.writeXml(writer);
String xmlData = writer.toString();
System.out.println("XML Representation:");
System.out.println(xmlData);
// Read from XML
try (WebRowSet webRowSet2 = RowSetProvider.newFactory().createWebRowSet()) {
java.io.StringReader reader = new java.io.StringReader(xmlData);
webRowSet2.readXml(reader);
// Work with the data
webRowSet2.beforeFirst();
while (webRowSet2.next()) {
System.out.println("Employee: " + webRowSet2.getString("name"));
}
}
}
}
// FilteredRowSet - data filtering
public void filteredRowSetDemo() throws SQLException {
try (FilteredRowSet filteredRowSet = RowSetProvider.newFactory().createFilteredRowSet()) {
filteredRowSet.setUrl("jdbc:mysql://localhost:3306/company");
filteredRowSet.setUsername("username");
filteredRowSet.setPassword("password");
filteredRowSet.setCommand("SELECT id, name, department, salary FROM employees");
filteredRowSet.execute();
// Apply filter for high-salary employees
filteredRowSet.setFilter(new HighSalaryFilter(80000));
System.out.println("High salary employees:");
while (filteredRowSet.next()) {
System.out.printf("Name: %-15s Department: %-15s Salary: $%,.2f%n",
filteredRowSet.getString("name"),
filteredRowSet.getString("department"),
filteredRowSet.getDouble("salary"));
}
}
}
}
// Custom filter for RowSet
class HighSalaryFilter implements Predicate {
private double minSalary;
public HighSalaryFilter(double minSalary) {
this.minSalary = minSalary;
}
@Override
public boolean evaluate(RowSet rs) {
try {
if (rs == null) return false;
double salary = rs.getDouble("salary");
return salary >= minSalary;
} catch (SQLException e) {
return false;
}
}
@Override
public boolean evaluate(Object value, int column) throws SQLException {
return false; // Not used
}
@Override
public boolean evaluate(Object value, String columnName) throws SQLException {
if ("salary".equals(columnName) && value instanceof Number) {
return ((Number) value).doubleValue() >= minSalary;
}
return false;
}
}
7. Advanced SQL Features
Calling Stored Procedures
import java.sql.*;
public class StoredProcedureExample {
private Connection connection;
public StoredProcedureExample(Connection connection) {
this.connection = connection;
}
// Call stored procedure with IN parameters
public void callProcedureWithInParams(int employeeId) throws SQLException {
String sql = "{call get_employee_details(?)}";
try (CallableStatement stmt = connection.prepareCall(sql)) {
stmt.setInt(1, employeeId);
try (ResultSet rs = stmt.executeQuery()) {
while (rs.next()) {
System.out.println("Employee: " + rs.getString("name") +
", Department: " + rs.getString("department"));
}
}
}
}
// Call stored procedure with OUT parameters
public void callProcedureWithOutParams(int employeeId) throws SQLException {
String sql = "{call get_employee_salary(?, ?)}";
try (CallableStatement stmt = connection.prepareCall(sql)) {
stmt.setInt(1, employeeId);
stmt.registerOutParameter(2, Types.DECIMAL);
stmt.execute();
double salary = stmt.getDouble(2);
System.out.println("Employee salary: $" + salary);
}
}
// Call stored procedure with INOUT parameters
public void callProcedureWithInOutParams(int departmentId) throws SQLException {
String sql = "{call get_department_stats(?, ?, ?)}";
try (CallableStatement stmt = connection.prepareCall(sql)) {
stmt.setInt(1, departmentId);
stmt.registerOutParameter(2, Types.INTEGER); // employee_count
stmt.registerOutParameter(3, Types.DECIMAL); // avg_salary
stmt.execute();
int employeeCount = stmt.getInt(2);
double avgSalary = stmt.getDouble(3);
System.out.println("Department " + departmentId +
" has " + employeeCount + " employees with average salary: $" + avgSalary);
}
}
// Call function
public void callFunction() throws SQLException {
String sql = "{? = call calculate_annual_bonus(?)}";
try (CallableStatement stmt = connection.prepareCall(sql)) {
stmt.registerOutParameter(1, Types.DECIMAL);
stmt.setInt(2, 1001); // employee_id
stmt.execute();
double bonus = stmt.getDouble(1);
System.out.println("Annual bonus: $" + bonus);
}
}
// Complex stored procedure with multiple result sets
public void callProcedureWithMultipleResultSets(int companyId) throws SQLException {
String sql = "{call get_company_report(?)}";
try (CallableStatement stmt = connection.prepareCall(sql)) {
stmt.setInt(1, companyId);
boolean hasResults = stmt.execute();
int resultSetCount = 0;
do {
if (hasResults) {
try (ResultSet rs = stmt.getResultSet()) {
resultSetCount++;
System.out.println("\n--- Result Set " + resultSetCount + " ---");
ResultSetMetaData metaData = rs.getMetaData();
int columnCount = metaData.getColumnCount();
// Print column headers
for (int i = 1; i <= columnCount; i++) {
System.out.printf("%-20s", metaData.getColumnName(i));
}
System.out.println();
// Print data
while (rs.next()) {
for (int i = 1; i <= columnCount; i++) {
System.out.printf("%-20s", rs.getString(i));
}
System.out.println();
}
}
}
hasResults = stmt.getMoreResults();
} while (hasResults || stmt.getUpdateCount() != -1);
}
}
}
8. Performance Optimization
Fetch Size and ResultSet Optimization
import java.sql.*;
public class PerformanceOptimization {
private Connection connection;
public PerformanceOptimization(Connection connection) {
this.connection = connection;
}
public void optimizeLargeResultSet() throws SQLException {
String sql = "SELECT * FROM large_table";
try (Statement stmt = connection.createStatement()) {
// Set fetch size to reduce memory usage
stmt.setFetchSize(1000); // Fetch 1000 rows at a time
try (ResultSet rs = stmt.executeQuery(sql)) {
// Set fetch direction for optimization
rs.setFetchDirection(ResultSet.FETCH_FORWARD);
while (rs.next()) {
// Process each row
processRow(rs);
}
}
}
}
public void preparedStatementReuse() throws SQLException {
String sql = "INSERT INTO transactions (account_id, amount, type) VALUES (?, ?, ?)";
// Reuse PreparedStatement for multiple executions
try (PreparedStatement stmt = connection.prepareStatement(sql)) {
List<Transaction> transactions = getTransactions();
for (Transaction transaction : transactions) {
stmt.setInt(1, transaction.getAccountId());
stmt.setDouble(2, transaction.getAmount());
stmt.setString(3, transaction.getType());
stmt.executeUpdate();
}
}
}
public void connectionPoolMonitoring() throws SQLException {
if (connection instanceof com.zaxxer.hikari.HikariProxyConnection) {
com.zaxxer.hikari.HikariDataSource hikariDataSource =
(com.zaxxer.hikari.HikariDataSource) ((com.zaxxer.hikari.HikariProxyConnection) connection).getHikariDataSource();
System.out.println("Active connections: " + hikariDataSource.getHikariPoolMXBean().getActiveConnections());
System.out.println("Idle connections: " + hikariDataSource.getHikariPoolMXBean().getIdleConnections());
System.out.println("Total connections: " + hikariDataSource.getHikariPoolMXBean().getTotalConnections());
}
}
private void processRow(ResultSet rs) throws SQLException {
// Simulate row processing
}
private List<Transaction> getTransactions() {
// Return sample transactions
return Arrays.asList(
new Transaction(1, 100.0, "DEPOSIT"),
new Transaction(2, 50.0, "WITHDRAWAL"),
new Transaction(1, 25.0, "WITHDRAWAL")
);
}
}
class Transaction {
private int accountId;
private double amount;
private String type;
public Transaction(int accountId, double amount, String type) {
this.accountId = accountId;
this.amount = amount;
this.type = type;
}
// Getters
public int getAccountId() { return accountId; }
public double getAmount() { return amount; }
public String getType() { return type; }
}
Key Advanced JDBC Features Summary
- Connection Pooling - HikariCP for performance
- Transaction Management - ACID properties with savepoints
- Batch Processing - Efficient bulk operations
- Advanced ResultSets - Scrollable, updatable, sensitive
- Database Metadata - Schema exploration
- RowSets - Disconnected data manipulation
- Stored Procedures - Database-side logic
- Performance Optimization - Fetch size, statement reuse
These advanced features help build enterprise-grade applications with better performance, reliability, and maintainability.