Working with JDBC
1. Loading JDBC Drivers
| Era | Detail |
|---|
| Modern | SPI auto-load via META-INF/services |
| Legacy | Class.forName("com.mysql.cj.jdbc.Driver") |
2. Establishing Connections
Example: Connection
String url = "jdbc:postgresql://localhost:5432/mydb";
try (Connection conn = DriverManager.getConnection(url, "user", "pass")) {
conn.setAutoCommit(false);
// ... operations
conn.commit();
}
| URL Pattern | Database |
|---|
jdbc:postgresql://host:5432/db | PostgreSQL |
jdbc:mysql://host:3306/db | MySQL |
jdbc:oracle:thin:@host:1521:sid | Oracle |
jdbc:h2:mem:testdb | H2 in-memory |
3. Using Statement
| Method | Use |
|---|
executeQuery(sql) | SELECT → ResultSet |
executeUpdate(sql) | INSERT/UPDATE/DELETE → row count |
execute(sql) | Any (returns boolean) |
Warning: Never concatenate user input into SQL — use PreparedStatement to prevent SQL injection.
4. Using PreparedStatement
Example: PreparedStatement
String sql = "SELECT * FROM users WHERE email = ? AND active = ?";
try (PreparedStatement ps = conn.prepareStatement(sql)) {
ps.setString(1, email);
ps.setBoolean(2, true);
try (ResultSet rs = ps.executeQuery()) {
while (rs.next()) { ... }
}
}
| Setter | Type |
|---|
setString(idx, val) | String |
setInt/setLong/setDouble | Numeric |
setObject(idx, val, sqlType) | Generic |
setNull(idx, sqlType) | NULL |
5. Using CallableStatement
| Aspect | Detail |
|---|
| SQL | {call procedure(?, ?)} |
| OUT params | registerOutParameter(idx, type) |
| Get result | getXxx(idx) after execute |
6. Working with ResultSet
| Method | Use |
|---|
next() | Move to next row |
getString(col) / getInt(col) | By name or index |
wasNull() | Check if last value was SQL NULL |
getMetaData() | Column info |
7. Handling Transactions
| Method | Detail |
|---|
setAutoCommit(false) | Begin manual mode |
commit() / rollback() | Finalize |
setSavepoint() / rollback(sp) | Partial rollback |
setTransactionIsolation(level) | READ_COMMITTED, etc. |
8. Using Batch Processing
Example: Batch insert
try (PreparedStatement ps = conn.prepareStatement("INSERT INTO log VALUES (?, ?)")) {
for (LogEntry e : entries) {
ps.setString(1, e.message());
ps.setTimestamp(2, Timestamp.from(e.time()));
ps.addBatch();
}
int[] counts = ps.executeBatch();
}
| Method | Use |
|---|
addBatch() | Queue statement |
executeBatch() | Run all |
clearBatch() | Reset queue |
9. Handling SQL Exceptions
| Aspect | Detail |
|---|
SQLException | Base for SQL errors |
getSQLState() | 5-char SQLSTATE |
getErrorCode() | Vendor code |
getNextException() | Chained exceptions |
| Subclasses | SQLIntegrityConstraintViolationException, etc. |
10. Using Connection Pools
| Pool | Detail |
|---|
| HikariCP | Industry standard, fastest |
| Apache DBCP2 | Legacy |
DataSource | JDBC-standard interface for pools |
| Why pool? | Connection setup is expensive |
11. Closing Resources Properly
| Order | Reason |
|---|
| ResultSet → Statement → Connection | Reverse of opening |
| try-with-resources | Handles automatically |
| Pooled connection | close() returns to pool |
| Method | Returns |
|---|
conn.getMetaData() | DatabaseMetaData |
getTables / getColumns / getPrimaryKeys | Schema info |
getDriverVersion / getDatabaseProductName | Version info |