library
intermediateJDBC Basics & PreparedStatement
Connect to databases, execute queries safely with PreparedStatements, and manage transactions.
JDBC (Java Database Connectivity) is the standard API for connecting to relational databases from Java.
JDBC = a phone call to a database. Connection = the phone line. PreparedStatement = a form letter with blanks (parameters). Statement = dictating the letter freeform (risky if caller inserts malicious text).
Key Concepts
1
Core workflow:
1. Load driver (automatic since JDBC 4.0 via ServiceLoader)
2. Get connection: DriverManager.getConnection(url, user, pass)
3. Create statement: connection.prepareStatement(sql)
4. Execute: executeQuery() for SELECT, executeUpdate() for INSERT/UPDATE/DELETE
5. Process results: ResultSet iteration
6. Close resources: try-with-resources
2
PreparedStatement vs Statement:
- PreparedStatement: parameterized queries (?). Prevents SQL injection. Pre-compiled by the DB.
- Statement: raw SQL string concatenation. NEVER use for user input — SQL injection vulnerability.
3
Transactions:
- Default: auto-commit (each statement is its own transaction)
- Manual: connection.setAutoCommit(false), then commit() or rollback()
- Isolation levels: READ_UNCOMMITTED, READ_COMMITTED, REPEATABLE_READ, SERIALIZABLE
4
Connection pooling: in production, never use DriverManager directly. Use HikariCP, Apache DBCP, or c3p0 for connection pooling. Pools maintain pre-opened connections, drastically reducing latency.
5
Batch operations: addBatch() + executeBatch() for bulk inserts (much faster than individual inserts).