Chapter 24: JDBC (Database Connectivity)
JDBC is the official Java API for connecting to relational databases. It’s been around since Java 1.1 (1997) and is still the foundation of almost every Java backend application in 2026 (Spring Boot, Hibernate, etc. all use JDBC underneath).
We’re going to go super slowly, step by step, like I’m sitting next to you in a quiet Mumbai café, showing you every line of code on my laptop. We’ll cover:
- Connecting to a database
- Statement vs PreparedStatement vs CallableStatement
- Working with ResultSet
- Complete runnable examples (with MySQL — most common in India)
Important note: To run these examples, you need:
- A running MySQL database (local or cloud — I’ll show both)
- MySQL Connector/J JAR (the JDBC driver)
0. Setup – One-Time Preparation (Very Important!)
Step 1: Download MySQL Connector/J
- Go to: https://dev.mysql.com/downloads/connector/j/
- Download Platform Independent (ZIP) → extract → find mysql-connector-j-9.x.x.jar (or latest 8.x/9.x version)
- In IntelliJ/Eclipse: Add this JAR to your project libraries (or use Maven — recommended for real projects)
Step 2: Create a Sample Database & Table
Run this SQL in MySQL Workbench or terminal:
SQL
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
CREATE DATABASE java_db; USE java_db; CREATE TABLE students ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(100) NOT NULL, age INT, city VARCHAR(50), created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); INSERT INTO students (name, age, city) VALUES ('Webliance', 25, 'Mumbai'), ('Amit', 22, 'Delhi'), ('Priya', 24, 'Pune'), ('Rahul', 21, 'Bangalore'); |
Now let’s write Java code!
1. Connecting to the Database (The Most Important Step)
JDBC URL format (very important!):
text
|
0 1 2 3 4 5 6 |
jdbc:mysql://hostname:port/database_name?key=value&key=value |
Common examples:
- Local MySQL: jdbc:mysql://localhost:3306/java_db
- With user/password in URL: jdbc:mysql://localhost:3306/java_db?user=root&password=yourpass
- Modern (recommended): use Properties or DataSource
Step-by-Step Connection Example (2025–2026 style)
Java
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 |
import java.sql.*; public class JdbcConnectDemo { public static void main(String[] args) { // 1. Database credentials String url = "jdbc:mysql://localhost:3306/java_db"; String username = "root"; String password = "your_password_here"; // CHANGE THIS! // 2. Connection object Connection conn = null; try { // 3. Load driver (optional in Java 8+ — auto-loaded) // Class.forName("com.mysql.cj.jdbc.Driver"); // Old way // 4. Establish connection conn = DriverManager.getConnection(url, username, password); System.out.println("Successfully connected to MySQL database!"); } catch (SQLException e) { System.out.println("Connection failed: " + e.getMessage()); e.printStackTrace(); } finally { // 5. Always close connection! if (conn != null) { try { conn.close(); System.out.println("Connection closed."); } catch (SQLException e) { e.printStackTrace(); } } } } } |
Modern & Recommended Way (try-with-resources – auto close!)
Java
|
0 1 2 3 4 5 6 7 8 9 10 11 |
try (Connection conn = DriverManager.getConnection(url, username, password)) { System.out.println("Connected successfully!"); // Use conn here... } catch (SQLException e) { System.out.println("Connection error: " + e.getMessage()); } |
2. Statement vs PreparedStatement vs CallableStatement
| Type | What it is | Security (SQL Injection) | Performance | Use Case |
|---|---|---|---|---|
| Statement | Simple SQL execution | Unsafe | OK | Static queries (rarely used) |
| PreparedStatement | Precompiled SQL + placeholders (?) | Safe | Best | Most common – dynamic queries |
| CallableStatement | Call stored procedures | Safe | Good | Call database procedures/functions |
Example 1: Using Statement (NOT recommended for user input!)
Java
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
try (Connection conn = DriverManager.getConnection(url, username, password); Statement stmt = conn.createStatement()) { String sql = "SELECT * FROM students"; ResultSet rs = stmt.executeQuery(sql); while (rs.next()) { System.out.println("ID: " + rs.getInt("id") + ", Name: " + rs.getString("name") + ", Age: " + rs.getInt("age") + ", City: " + rs.getString("city")); } } catch (SQLException e) { e.printStackTrace(); } |
Example 2: PreparedStatement (The Modern & Safe Way)
Java
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 |
try (Connection conn = DriverManager.getConnection(url, username, password)) { // Insert new student (safe from SQL injection) String insertSql = "INSERT INTO students (name, age, city) VALUES (?, ?, ?)"; try (PreparedStatement pstmt = conn.prepareStatement(insertSql)) { pstmt.setString(1, "Sachin"); pstmt.setInt(2, 23); pstmt.setString(3, "Mumbai"); int rowsAffected = pstmt.executeUpdate(); System.out.println(rowsAffected + " row(s) inserted."); } // Select with parameter String selectSql = "SELECT * FROM students WHERE city = ? AND age > ?"; try (PreparedStatement pstmt = conn.prepareStatement(selectSql)) { pstmt.setString(1, "Mumbai"); pstmt.setInt(2, 20); try (ResultSet rs = pstmt.executeQuery()) { while (rs.next()) { System.out.println(rs.getString("name") + " from " + rs.getString("city")); } } } } catch (SQLException e) { e.printStackTrace(); } |
Example 3: CallableStatement (Calling Stored Procedure)
First, create a stored procedure in MySQL:
SQL
|
0 1 2 3 4 5 6 7 8 9 10 11 |
DELIMITER // CREATE PROCEDURE getStudentCountByCity(IN cityName VARCHAR(50), OUT total INT) BEGIN SELECT COUNT(*) INTO total FROM students WHERE city = cityName; END // DELIMITER ; |
Java code:
Java
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
try (Connection conn = DriverManager.getConnection(url, username, password)) { String callSql = "{CALL getStudentCountByCity(?, ?)}"; try (CallableStatement cstmt = conn.prepareCall(callSql)) { cstmt.setString(1, "Mumbai"); cstmt.registerOutParameter(2, Types.INTEGER); cstmt.execute(); int count = cstmt.getInt(2); System.out.println("Number of students in Mumbai: " + count); } } catch (SQLException e) { e.printStackTrace(); } |
3. Working with ResultSet (Reading Query Results)
ResultSet is like a cursor pointing to rows returned by a SELECT query.
Important Methods:
| Method | What it does | Example |
|---|---|---|
| next() | Move to next row (returns false at end) | while (rs.next()) { … } |
| getInt(“column”) / getString(“column”) | Get value by column name | rs.getString(“name”) |
| getInt(1) / getString(1) | Get value by column index (1-based) | rs.getInt(1) |
| getDate(), getTimestamp() | Get date/time values | rs.getTimestamp(“created_at”) |
| wasNull() | Check if last column was SQL NULL | if (rs.wasNull()) … |
Best Practice: Use column names (not indices) — safer when table structure changes.
4. Quick Recap Table (Your Cheat Sheet)
| Task | Recommended Class / Method | Security / Performance Notes |
|---|---|---|
| Simple static query | Statement | Unsafe – avoid for user input |
| Dynamic query with parameters | PreparedStatement (with ?) | Safe from SQL injection + fastest |
| Call stored procedure | CallableStatement | For complex DB logic |
| Read results | ResultSet + next() | Always use try-with-resources |
| Auto-close resources | try-with-resources | Prevents leaks |
5. Homework for You (Very Practical!)
- Basic Write a program that inserts 3 new students into the students table using PreparedStatement.
- Medium Write a program that:
- Asks user for a city name
- Shows all students from that city
- Uses PreparedStatement to avoid SQL injection
- Advanced Create a method getStudentById(int id) that returns a Student object (make a simple Student class with getters/setters).
- Fun Write a program that reads all students and prints them in a nice formatted table (use printf).
- Challenge Handle the case when no rows are returned — print “No students found”.
You’re doing fantastic! JDBC is the backbone of almost every Java backend job — now you can connect Java to real databases like a pro.
