Lesson 34 • Advanced
JDBC – Database Connectivity
Almost every production Java app talks to a database. JDBC is the standard API for this — and even if you use JPA/Hibernate, understanding JDBC is essential because that's what runs underneath. Get this right, and your data layer will be fast, safe, and reliable.
Before You Start
You should be comfortable with Exception Handling (try-with-resources for auto-closing) and IO & NIO (stream concepts). Basic SQL knowledge (SQL Course) is essential.
What You'll Learn
- ✅ JDBC architecture: Driver → Connection → Statement → ResultSet
- ✅ DriverManager vs DataSource connections
- ✅ PreparedStatement to prevent SQL injection
- ✅ Transaction management with commit/rollback
- ✅ Connection pooling with HikariCP
- ✅ Batch operations for bulk inserts
1️⃣ The JDBC Flow
Analogy: JDBC is like making a phone call to a database. You dial (get a Connection), speak (execute queries via Statement), listen (read ResultSet), and hang up (close resources). PreparedStatement is like using a template — you fill in the blanks safely.
| Component | Role | Key Insight |
|---|---|---|
| Driver | Translates Java ↔ DB protocol | Auto-loaded since Java 6 |
| Connection | Session with database | Expensive — use connection pools! |
| PreparedStatement | Parameterized query | ALWAYS use this, never concatenate SQL |
| ResultSet | Query results cursor | Forward-only, call next() to advance |
Try It: JDBC CRUD Operations
// 💡 Try modifying this code and see what happens!
// Simulating JDBC CRUD operations with an in-memory database
console.log("=== JDBC CRUD Operations ===\n");
// Simulated database
class Database {
constructor() { this.tables = { users: [] }; this.autoId = 1; }
prepareStatement(sql) {
return { sql, params: [], setString(i, v) { this.params[i] = v; }, setInt(i, v) { this.params[i] = v; } };
}
executeQuery(ps) {
if (ps.sql.includes("SELECT * FROM users WHERE id")) {
...2️⃣ SQL Injection Prevention
SQL injection is the #1 database vulnerability worldwide. It happens when user input is concatenated directly into SQL strings. PreparedStatement eliminates this entirely by treating parameters as data, never as executable code.
❌ Vulnerable: "SELECT * FROM users WHERE name='" + input + "'"
→ Attacker sends: ' OR 1=1 -- → Gets ALL data!
✅ Safe: "SELECT * FROM users WHERE name = ?" + ps.setString(1, input)
Try It: SQL Injection Attack & Defense
// 💡 Try modifying this code and see what happens!
// SQL Injection — Attack vs Defense demonstration
console.log("=== SQL Injection: Attack vs Defense ===\n");
// Simulated database
let users = [
{ id: 1, name: "Alice", password: "secret123", role: "user" },
{ id: 2, name: "Bob", password: "pass456", role: "admin" },
{ id: 3, name: "Charlie", password: "qwerty", role: "user" },
];
// ❌ VULNERABLE: String concatenation
function unsafeQuery(userInput) {
let sql = "SELECT * FROM users
...Common Mistakes
- ⚠️ SQL Injection:
"SELECT * FROM users WHERE name='" + input + "'"— NEVER do this - ⚠️ Not closing resources: Leaked connections exhaust the pool. Always try-with-resources
- ⚠️ Hardcoded credentials: Never put DB passwords in source code — use environment variables
- ⚠️ No connection pooling: Creating a new connection per request is 100-1000x slower than pooling
Pro Tips
- 💡 HikariCP is the fastest connection pool — Spring Boot uses it by default
- 💡 Use batch operations (
addBatch() / executeBatch()) for bulk inserts — 10-100x faster - 💡 Set fetch size:
stmt.setFetchSize(100)prevents loading millions of rows into memory - 💡 In real projects, use JPA/Hibernate for CRUD and drop to raw JDBC for complex queries
Try It: Transactions & Connection Pooling
// 💡 Try modifying this code and see what happens!
// Transactions and Connection Pooling simulation
console.log("=== Transactions & Connection Pooling ===\n");
// 1. Transaction — Bank Transfer (all-or-nothing)
console.log("1. TRANSACTION — Bank Transfer:");
let accounts = { Alice: 1000, Bob: 500 };
console.log(" Before: Alice=$" + accounts.Alice + ", Bob=$" + accounts.Bob);
function transfer(from, to, amount) {
console.log(" Transferring $" + amount + " from " + from + " to " + to);
...📋 Quick Reference
| Operation | API | Returns |
|---|---|---|
| Connect | DriverManager.getConnection() | Connection |
| Query | ps.executeQuery() | ResultSet |
| Update | ps.executeUpdate() | int (rows affected) |
| Batch | ps.addBatch() / executeBatch() | int[] |
| Transaction | commit() / rollback() | void |
🎉 Lesson Complete!
You've mastered JDBC — the foundation of all Java database access! Next: REST APIs — building web services with Spring Boot.
Sign up for free to track which lessons you've completed and get learning reminders.