- Understand what JDBC is and describe the role of JDBC and JDBC drivers in enabling database connectivity
- Configure JDBC to connect to any database and execute SQL queries using the JDBC API
- Implement complete CRUD operations (Create, Read, Update, Delete) on database tables
- Manage database resources effectively using try-with-resources and proper exception handling
- Work with ResultSet to process query results and access any kind of tabular data
Every non-trivial Java application eventually needs to persist data. You can serialize objects to files, but when you need to query, update, or manage relationships between entities, a relational database becomes essential. JDBC—Java Database Connectivity—is the standard API designed for Java programmers to interact with databases. It's vendor-neutral, which means you write your code once and it works with MySQL, PostgreSQL, Oracle, SQL Server, or any other JDBC-compliant database.
This JDBC tutorial walks you through how to use the JDBC API from establishing connections to performing full CRUD operations. You'll see how JDBC drivers work behind the scenes, how to execute SQL queries safely, and how to handle the inevitable SQLException that shows up when things go wrong. We're using H2, an excellent in-memory database that requires zero installation and is perfect for learning and testing. The patterns you learn here apply to any relational database you'll encounter in production.
The beauty of JDBC is its simplicity. You get a connection, create a statement, execute SQL, and process results. The API is straightforward once you understand the core interfaces—Connection, Statement, PreparedStatement, and ResultSet. These are the building blocks of every database interaction in Java.
JDBC provides a uniform interface for accessing different relational database systems. Your Java application talks to JDBC interfaces, and JDBC drivers translate those calls into database-specific protocols. This abstraction layer means your code doesn't need to know whether it's talking to MySQL or PostgreSQL—the driver handles those details.
The role of JDBC drivers is crucial. A JDBC driver is a set of classes that implements the JDBC interfaces for a specific database. When you add a database driver JAR to your classpath (like H2, MySQL Connector/J, or PostgreSQL JDBC), you're providing the implementation that knows how to communicate with that particular database. Modern JDBC 4.0+ drivers auto-register themselves when they're on the classpath, so you don't need the old Class.forName("com.mysql.jdbc.Driver") pattern anymore—that's legacy code.
JDBC can access any kind of tabular data. If it's organized in rows and columns with a SQL interface, JDBC can work with it. This universality makes JDBC the foundation for most Java persistence frameworks. Even when you use JPA or Hibernate, they're using JDBC under the hood to execute the actual SQL.
The architecture follows a clear separation of concerns. Your application depends on JDBC interfaces from java.sql package, which is part of the JDK. The driver JAR provides the concrete implementations. This design pattern—programming to interfaces rather than implementations—is fundamental to writing maintainable Java applications.
H2 is an embedded, in-memory database written entirely in Java. It's fast, lightweight, and perfect for development and testing. Since it runs in the same JVM as your application, there's no separate database server to install or configure. When you create an in-memory H2 database, it exists only while your application runs and disappears when the JVM shuts down.
First, add the H2 dependency. If you're using Maven:
<dependency>
<groupId>com.h2database</groupId>
<artifactId>h2</artifactId>
<version>2.2.224</version>
</dependency>For a Gradle project:
implementation 'com.h2database:h2:2.2.224'H2's JDBC URL format is simple: jdbc:h2:mem:testdb creates an in-memory database named "testdb". The mem part tells H2 to keep everything in RAM. You can also use jdbc:h2:~/testdb to persist data to disk in your home directory, but we'll stick with in-memory for this tutorial.
Every JDBC operation starts with a Connection object. This represents an active session with your database. You obtain connections through DriverManager, which manages registered drivers and selects the appropriate one based on your JDBC URL.
package academy.javapro;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class H2ConnectionDemo {
private static final String DB_URL = "jdbc:h2:mem:testdb";
private static final String USER = "sa";
private static final String PASSWORD = "";
public static void main(String[] args) {
try (Connection conn = DriverManager.getConnection(DB_URL, USER, PASSWORD)) {
System.out.println("Connected to H2 database successfully!");
System.out.println("Database product: " + conn.getMetaData().getDatabaseProductName());
System.out.println("Database version: " + conn.getMetaData().getDatabaseProductVersion());
// Create our students table
createStudentsTable(conn);
} catch (SQLException e) {
System.err.println("Database error: " + e.getMessage());
e.printStackTrace();
}
}
private static void createStudentsTable(Connection conn) throws SQLException {
String createTableSQL = """
CREATE TABLE IF NOT EXISTS students (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(100) UNIQUE NOT NULL,
gpa DECIMAL(3, 2),
enrollment_date DATE
)
""";
try (Statement stmt = conn.createStatement()) {
stmt.execute(createTableSQL);
System.out.println("Students table created successfully!");
}
}
}Notice the try-with-resources syntax. Connection implements AutoCloseable, so it automatically closes when the try block exits. This is critical. Database connections are expensive resources—they consume memory, hold network sockets, and occupy slots in the database's connection pool. Leaked connections are one of the most common causes of production issues in Java applications.
H2's default username is "sa" (system administrator) with an empty password. This is fine for development, but never use default credentials in production. The connection URL, username, and password should come from configuration files or environment variables, not be hardcoded.
The metadata API (conn.getMetaData()) provides information about the database itself. You can query supported features, get table schemas, discover primary keys, and more. This is useful when you're writing database-agnostic code or building tools that need to inspect database structure at runtime.
Once you have a connection and a table, you can query data. SELECT queries return a ResultSet, which is a cursor pointing to the query results. You iterate through the ResultSet row by row, extracting column values as you go.
package academy.javapro;
import java.sql.*;
public class ReadStudentsExample {
private static final String DB_URL = "jdbc:h2:mem:testdb";
private static final String USER = "sa";
private static final String PASSWORD = "";
public static void main(String[] args) {
try (Connection conn = DriverManager.getConnection(DB_URL, USER, PASSWORD)) {
setupDatabase(conn);
insertSampleData(conn);
readAllStudents(conn);
} catch (SQLException e) {
System.err.println("Database error: " + e.getMessage());
e.printStackTrace();
}
}
private static void setupDatabase(Connection conn) throws SQLException {
String createTableSQL = """
CREATE TABLE IF NOT EXISTS students (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(100) UNIQUE NOT NULL,
gpa DECIMAL(3, 2),
enrollment_date DATE
)
""";
try (Statement stmt = conn.createStatement()) {
stmt.execute(createTableSQL);
}
}
private static void insertSampleData(Connection conn) throws SQLException {
String insertSQL = """
INSERT INTO students (name, email, gpa, enrollment_date) VALUES
('Alice Johnson', 'alice@university.edu', 3.85, '2023-09-01'),
('Bob Smith', 'bob@university.edu', 3.42, '2023-09-01'),
('Carol White', 'carol@university.edu', 3.91, '2022-09-01'),
('David Brown', 'david@university.edu', 2.78, '2024-01-15')
""";
try (Statement stmt = conn.createStatement()) {
int rowsInserted = stmt.executeUpdate(insertSQL);
System.out.println(rowsInserted + " students inserted.\n");
}
}
private static void readAllStudents(Connection conn) throws SQLException {
String query = "SELECT id, name, email, gpa, enrollment_date FROM students ORDER BY gpa DESC";
try (Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(query)) {
System.out.println("Students in Database:");
System.out.println("-".repeat(80));
while (rs.next()) {
int id = rs.getInt("id");
String name = rs.getString("name");
String email = rs.getString("email");
double gpa = rs.getDouble("gpa");
Date enrollmentDate = rs.getDate("enrollment_date");
System.out.printf("ID: %-3d | Name: %-20s | Email: %-25s | GPA: %.2f | Enrolled: %s%n",
id, name, email, gpa, enrollmentDate);
}
}
}
}The ResultSet cursor starts before the first row. Calling rs.next() moves the cursor forward and returns true if there's data, false when you've exhausted the results. This pattern—the while loop with rs.next()—is fundamental to JDBC programming.
You extract column values using type-specific getter methods: getInt(), getString(), getDouble(), getDate(). You can reference columns by name (more readable) or by index (slightly faster, but fragile if your SELECT list changes). Column indices start at 1, not 0—this trips up developers who expect zero-based indexing like arrays.
Statement is suitable for static SQL queries, but it has a critical weakness: SQL injection vulnerabilities. If you concatenate user input into SQL strings, an attacker can inject malicious SQL. Always use PreparedStatement for queries with parameters.
Inserting data means executing an INSERT statement. You use executeUpdate() instead of executeQuery()—it returns the number of affected rows rather than a ResultSet. When you need the generated primary key after an insert, you use special methods to retrieve it.
package academy.javapro;
import java.sql.*;
public class InsertStudentExample {
private static final String DB_URL = "jdbc:h2:mem:testdb";
private static final String USER = "sa";
private static final String PASSWORD = "";
public static void main(String[] args) {
try (Connection conn = DriverManager.getConnection(DB_URL, USER, PASSWORD)) {
setupDatabase(conn);
// Insert with PreparedStatement
int newId = insertStudent(conn, "Emma Wilson", "emma@university.edu", 3.67, "2024-01-15");
System.out.println("Inserted student with ID: " + newId);
// Insert another student
int anotherId = insertStudent(conn, "Frank Martinez", "frank@university.edu", 3.55, "2023-09-01");
System.out.println("Inserted student with ID: " + anotherId);
// Show all students
displayAllStudents(conn);
} catch (SQLException e) {
System.err.println("Database error: " + e.getMessage());
e.printStackTrace();
}
}
private static void setupDatabase(Connection conn) throws SQLException {
String createTableSQL = """
CREATE TABLE IF NOT EXISTS students (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(100) UNIQUE NOT NULL,
gpa DECIMAL(3, 2),
enrollment_date DATE
)
""";
try (Statement stmt = conn.createStatement()) {
stmt.execute(createTableSQL);
}
}
private static int insertStudent(Connection conn, String name, String email,
double gpa, String enrollmentDate) throws SQLException {
String insertSQL = "INSERT INTO students (name, email, gpa, enrollment_date) VALUES (?, ?, ?, ?)";
try (PreparedStatement pstmt = conn.prepareStatement(insertSQL, Statement.RETURN_GENERATED_KEYS)) {
pstmt.setString(1, name);
pstmt.setString(2, email);
pstmt.setDouble(3, gpa);
pstmt.setDate(4, Date.valueOf(enrollmentDate));
int rowsAffected = pstmt.executeUpdate();
if (rowsAffected > 0) {
try (ResultSet generatedKeys = pstmt.getGeneratedKeys()) {
if (generatedKeys.next()) {
return generatedKeys.getInt(1);
}
}
}
throw new SQLException("Insert failed, no ID obtained.");
}
}
private static void displayAllStudents(Connection conn) throws SQLException {
String query = "SELECT id, name, email, gpa FROM students";
try (Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(query)) {
System.out.println("\nAll Students:");
System.out.println("-".repeat(70));
while (rs.next()) {
System.out.printf("ID: %d | %s | %s | GPA: %.2f%n",
rs.getInt("id"),
rs.getString("name"),
rs.getString("email"),
rs.getDouble("gpa"));
}
}
}
}PreparedStatement is safer and often faster than Statement. The question marks (?) are parameter placeholders. You bind values using setter methods—setString(), setInt(), setDouble(), setDate(). The JDBC driver handles proper escaping and type conversion, eliminating SQL injection risks.
The Statement.RETURN_GENERATED_KEYS flag tells the driver you want access to auto-generated primary keys. After executing the insert, you call getGeneratedKeys() to retrieve a ResultSet containing those keys. For tables with auto-increment IDs, this is how you discover what ID the database assigned to your new row.
Date handling deserves attention. JDBC has three date/time types: java.sql.Date for dates, java.sql.Time for times, and java.sql.Timestamp for timestamps. These are separate from java.util.Date and the newer java.time API. You typically convert between them using static methods like Date.valueOf() or by using PreparedStatement methods that accept java.time.LocalDate directly in JDBC 4.2+.
UPDATE operations modify existing rows. Like INSERT, you use executeUpdate() and get back the count of modified rows. PreparedStatement is essential here—you're almost always updating based on some condition that includes variable data.
package academy.javapro;
import java.sql.*;
public class UpdateStudentExample {
private static final String DB_URL = "jdbc:h2:mem:testdb";
private static final String USER = "sa";
private static final String PASSWORD = "";
public static void main(String[] args) {
try (Connection conn = DriverManager.getConnection(DB_URL, USER, PASSWORD)) {
setupDatabase(conn);
insertInitialData(conn);
System.out.println("Before update:");
displayStudent(conn, 1);
// Update student's GPA
updateStudentGPA(conn, 1, 3.95);
System.out.println("\nAfter update:");
displayStudent(conn, 1);
// Update email
updateStudentEmail(conn, 1, "alice.j@university.edu");
System.out.println("\nAfter email update:");
displayStudent(conn, 1);
} catch (SQLException e) {
System.err.println("Database error: " + e.getMessage());
e.printStackTrace();
}
}
private static void setupDatabase(Connection conn) throws SQLException {
String createTableSQL = """
CREATE TABLE IF NOT EXISTS students (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(100) UNIQUE NOT NULL,
gpa DECIMAL(3, 2),
enrollment_date DATE
)
""";
try (Statement stmt = conn.createStatement()) {
stmt.execute(createTableSQL);
}
}
private static void insertInitialData(Connection conn) throws SQLException {
String insertSQL = """
INSERT INTO students (name, email, gpa, enrollment_date) VALUES
('Alice Johnson', 'alice@university.edu', 3.85, '2023-09-01'),
('Bob Smith', 'bob@university.edu', 3.42, '2023-09-01')
""";
try (Statement stmt = conn.createStatement()) {
stmt.executeUpdate(insertSQL);
}
}
private static void updateStudentGPA(Connection conn, int studentId, double newGPA) throws SQLException {
String updateSQL = "UPDATE students SET gpa = ? WHERE id = ?";
try (PreparedStatement pstmt = conn.prepareStatement(updateSQL)) {
pstmt.setDouble(1, newGPA);
pstmt.setInt(2, studentId);
int rowsUpdated = pstmt.executeUpdate();
if (rowsUpdated > 0) {
System.out.println("Updated GPA for student ID " + studentId);
} else {
System.out.println("No student found with ID " + studentId);
}
}
}
private static void updateStudentEmail(Connection conn, int studentId, String newEmail) throws SQLException {
String updateSQL = "UPDATE students SET email = ? WHERE id = ?";
try (PreparedStatement pstmt = conn.prepareStatement(updateSQL)) {
pstmt.setString(1, newEmail);
pstmt.setInt(2, studentId);
int rowsUpdated = pstmt.executeUpdate();
System.out.println("Updated email for " + rowsUpdated + " student(s)");
}
}
private static void displayStudent(Connection conn, int studentId) throws SQLException {
String query = "SELECT id, name, email, gpa, enrollment_date FROM students WHERE id = ?";
try (PreparedStatement pstmt = conn.prepareStatement(query)) {
pstmt.setInt(1, studentId);
try (ResultSet rs = pstmt.executeQuery()) {
if (rs.next()) {
System.out.printf("ID: %d | Name: %s | Email: %s | GPA: %.2f | Enrolled: %s%n",
rs.getInt("id"),
rs.getString("name"),
rs.getString("email"),
rs.getDouble("gpa"),
rs.getDate("enrollment_date"));
} else {
System.out.println("Student not found");
}
}
}
}
}The return value from executeUpdate() tells you how many rows were affected. If you update a student by ID and get 0 back, either that ID doesn't exist or the update didn't actually change anything (updating a GPA to its current value returns 0 in some databases). This is useful for detecting issues or confirming operations succeeded.
You can update multiple columns in one statement by expanding your SQL: UPDATE students SET gpa = ?, email = ? WHERE id = ?. Just remember to bind parameters in the order they appear in your SQL string. Parameter indices start at 1, matching column positions in your SQL.
DELETE operations remove rows permanently. The pattern is identical to INSERT and UPDATE—use PreparedStatement with a WHERE clause to specify which rows to delete. Without a WHERE clause, you delete everything, which is almost never what you want.
package academy.javapro;
import java.sql.*;
public class DeleteStudentExample {
private static final String DB_URL = "jdbc:h2:mem:testdb";
private static final String USER = "sa";
private static final String PASSWORD = "";
public static void main(String[] args) {
try (Connection conn = DriverManager.getConnection(DB_URL, USER, PASSWORD)) {
setupDatabase(conn);
insertSampleData(conn);
System.out.println("Before deletion:");
displayAllStudents(conn);
// Delete a specific student
deleteStudent(conn, 2);
System.out.println("\nAfter deleting student ID 2:");
displayAllStudents(conn);
// Delete students with low GPA
deleteStudentsByGPA(conn, 3.0);
System.out.println("\nAfter deleting students with GPA < 3.0:");
displayAllStudents(conn);
} catch (SQLException e) {
System.err.println("Database error: " + e.getMessage());
e.printStackTrace();
}
}
private static void setupDatabase(Connection conn) throws SQLException {
String createTableSQL = """
CREATE TABLE IF NOT EXISTS students (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(100) UNIQUE NOT NULL,
gpa DECIMAL(3, 2),
enrollment_date DATE
)
""";
try (Statement stmt = conn.createStatement()) {
stmt.execute(createTableSQL);
}
}
private static void insertSampleData(Connection conn) throws SQLException {
String insertSQL = """
INSERT INTO students (name, email, gpa, enrollment_date) VALUES
('Alice Johnson', 'alice@university.edu', 3.85, '2023-09-01'),
('Bob Smith', 'bob@university.edu', 2.78, '2023-09-01'),
('Carol White', 'carol@university.edu', 3.91, '2022-09-01'),
('David Brown', 'david@university.edu', 2.45, '2024-01-15')
""";
try (Statement stmt = conn.createStatement()) {
stmt.executeUpdate(insertSQL);
}
}
private static void deleteStudent(Connection conn, int studentId) throws SQLException {
String deleteSQL = "DELETE FROM students WHERE id = ?";
try (PreparedStatement pstmt = conn.prepareStatement(deleteSQL)) {
pstmt.setInt(1, studentId);
int rowsDeleted = pstmt.executeUpdate();
if (rowsDeleted > 0) {
System.out.println("Deleted student with ID: " + studentId);
} else {
System.out.println("No student found with ID: " + studentId);
}
}
}
private static void deleteStudentsByGPA(Connection conn, double thresholdGPA) throws SQLException {
String deleteSQL = "DELETE FROM students WHERE gpa < ?";
try (PreparedStatement pstmt = conn.prepareStatement(deleteSQL)) {
pstmt.setDouble(1, thresholdGPA);
int rowsDeleted = pstmt.executeUpdate();
System.out.println("Deleted " + rowsDeleted + " student(s) with GPA below " + thresholdGPA);
}
}
private static void displayAllStudents(Connection conn) throws SQLException {
String query = "SELECT id, name, email, gpa FROM students ORDER BY id";
try (Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(query)) {
System.out.println("-".repeat(70));
while (rs.next()) {
System.out.printf("ID: %d | %s | GPA: %.2f%n",
rs.getInt("id"),
rs.getString("name"),
rs.getDouble("gpa"));
}
System.out.println("-".repeat(70));
}
}
}Be extremely careful with DELETE operations. There's no undo. In production systems, many teams prefer "soft deletes"—adding a deleted flag or deleted_at timestamp column and marking records as deleted rather than physically removing them. This preserves data for auditing and allows recovery if someone deletes something by mistake.
The pattern of checking the return value applies here too. If executeUpdate() returns 0 on a DELETE, nothing was deleted. This might be fine (the record was already gone), or it might indicate a logic error (you're trying to delete something that doesn't exist).
Now that you've seen each operation individually, here's a complete example that ties everything together. This demonstrates a typical pattern: a database utility class that encapsulates all CRUD operations for a specific entity.
package academy.javapro;
import java.sql.*;
import java.time.LocalDate;
import java.util.ArrayList;
import java.util.List;
public class StudentDAO {
private static final String DB_URL = "jdbc:h2:mem:testdb";
private static final String USER = "sa";
private static final String PASSWORD = "";
static {
try (Connection conn = DriverManager.getConnection(DB_URL, USER, PASSWORD)) {
initializeDatabase(conn);
} catch (SQLException e) {
throw new RuntimeException("Failed to initialize database", e);
}
}
private static void initializeDatabase(Connection conn) throws SQLException {
String createTableSQL = """
CREATE TABLE IF NOT EXISTS students (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(100) UNIQUE NOT NULL,
gpa DECIMAL(3, 2),
enrollment_date DATE
)
""";
try (Statement stmt = conn.createStatement()) {
stmt.execute(createTableSQL);
}
}
private static Connection getConnection() throws SQLException {
return DriverManager.getConnection(DB_URL, USER, PASSWORD);
}
// CREATE
public static int createStudent(String name, String email, double gpa, LocalDate enrollmentDate)
throws SQLException {
String insertSQL = "INSERT INTO students (name, email, gpa, enrollment_date) VALUES (?, ?, ?, ?)";
try (Connection conn = getConnection();
PreparedStatement pstmt = conn.prepareStatement(insertSQL, Statement.RETURN_GENERATED_KEYS)) {
pstmt.setString(1, name);
pstmt.setString(2, email);
pstmt.setDouble(3, gpa);
pstmt.setDate(4, Date.valueOf(enrollmentDate));
pstmt.executeUpdate();
try (ResultSet keys = pstmt.getGeneratedKeys()) {
if (keys.next()) {
return keys.getInt(1);
}
throw new SQLException("Failed to retrieve generated ID");
}
}
}
// READ - get single student by ID
public static Student getStudentById(int id) throws SQLException {
String query = "SELECT id, name, email, gpa, enrollment_date FROM students WHERE id = ?";
try (Connection conn = getConnection();
PreparedStatement pstmt = conn.prepareStatement(query)) {
pstmt.setInt(1, id);
try (ResultSet rs = pstmt.executeQuery()) {
if (rs.next()) {
return extractStudentFromResultSet(rs);
}
return null;
}
}
}
// READ - get all students
public static List<Student> getAllStudents() throws SQLException {
String query = "SELECT id, name, email, gpa, enrollment_date FROM students ORDER BY name";
List<Student> students = new ArrayList<>();
try (Connection conn = getConnection();
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(query)) {
while (rs.next()) {
students.add(extractStudentFromResultSet(rs));
}
}
return students;
}
// UPDATE
public static boolean updateStudent(int id, String name, String email, double gpa) throws SQLException {
String updateSQL = "UPDATE students SET name = ?, email = ?, gpa = ? WHERE id = ?";
try (Connection conn = getConnection();
PreparedStatement pstmt = conn.prepareStatement(updateSQL)) {
pstmt.setString(1, name);
pstmt.setString(2, email);
pstmt.setDouble(3, gpa);
pstmt.setInt(4, id);
int rowsUpdated = pstmt.executeUpdate();
return rowsUpdated > 0;
}
}
// DELETE
public static boolean deleteStudent(int id) throws SQLException {
String deleteSQL = "DELETE FROM students WHERE id = ?";
try (Connection conn = getConnection();
PreparedStatement pstmt = conn.prepareStatement(deleteSQL)) {
pstmt.setInt(1, id);
int rowsDeleted = pstmt.executeUpdate();
return rowsDeleted > 0;
}
}
private static Student extractStudentFromResultSet(ResultSet rs) throws SQLException {
return new Student(
rs.getInt("id"),
rs.getString("name"),
rs.getString("email"),
rs.getDouble("gpa"),
rs.getDate("enrollment_date").toLocalDate()
);
}
// Simple Student class
static class Student {
private final int id;
private final String name;
private final String email;
private final double gpa;
private final LocalDate enrollmentDate;
public Student(int id, String name, String email, double gpa, LocalDate enrollmentDate) {
this.id = id;
this.name = name;
this.email = email;
this.gpa = gpa;
this.enrollmentDate = enrollmentDate;
}
@Override
public String toString() {
return String.format("Student[id=%d, name=%s, email=%s, gpa=%.2f, enrolled=%s]",
id, name, email, gpa, enrollmentDate);
}
}
public static void main(String[] args) {
try {
// CREATE
System.out.println("Creating students...");
int id1 = createStudent("Alice Johnson", "alice@university.edu", 3.85, LocalDate.of(2023, 9, 1));
int id2 = createStudent("Bob Smith", "bob@university.edu", 3.42, LocalDate.of(2023, 9, 1));
int id3 = createStudent("Carol White", "carol@university.edu", 3.91, LocalDate.of(2022, 9, 1));
System.out.println("Created students with IDs: " + id1 + ", " + id2 + ", " + id3);
// READ
System.out.println("\nReading all students:");
List<Student> allStudents = getAllStudents();
allStudents.forEach(System.out::println);
// READ by ID
System.out.println("\nReading student with ID " + id1 + ":");
Student alice = getStudentById(id1);
System.out.println(alice);
// UPDATE
System.out.println("\nUpdating student " + id2 + "...");
boolean updated = updateStudent(id2, "Robert Smith", "robert@university.edu", 3.65);
System.out.println("Update " + (updated ? "successful" : "failed"));
System.out.println("\nAfter update:");
System.out.println(getStudentById(id2));
// DELETE
System.out.println("\nDeleting student " + id3 + "...");
boolean deleted = deleteStudent(id3);
System.out.println("Delete " + (deleted ? "successful" : "failed"));
System.out.println("\nRemaining students:");
getAllStudents().forEach(System.out::println);
} catch (SQLException e) {
System.err.println("Database operation failed: " + e.getMessage());
e.printStackTrace();
}
}
}This Data Access Object (DAO) pattern is common in Java applications. You encapsulate all database operations for a specific entity in one class. Each method handles a single responsibility: create, read, update, or delete. The rest of your application doesn't need to know about SQL or JDBC—it just calls methods like createStudent() or getAllStudents().
Notice how the Student class is immutable—all fields are final. This is good practice. Immutable objects are thread-safe and easier to reason about. When you need to "update" a student, you're actually just executing an UPDATE statement; you're not modifying the Student object itself.
The static initializer block creates the table when the class loads. In a real application, you'd use database migration tools like Flyway or Liquibase instead of embedding DDL in your Java code. But for learning and simple applications, this approach works fine.
SQLException is a checked exception, which means you must either catch it or declare that your method throws it. JDBC operations can fail in numerous ways: network issues, constraint violations, syntax errors in SQL, authentication problems, and more. The SQLException gives you error codes and SQL states that help diagnose problems.
Resource management is critical in JDBC. You must close Connections, Statements, PreparedStatements, and ResultSets. If you don't, you leak resources. Try-with-resources is your friend—it automatically calls close() on any AutoCloseable resource when the try block exits, whether normally or via exception.
package academy.javapro;
import java.sql.*;
public class ExceptionHandlingExample {
private static final String DB_URL = "jdbc:h2:mem:testdb";
private static final String USER = "sa";
private static final String PASSWORD = "";
public static void main(String[] args) {
setupDatabase();
// Example 1: Handling constraint violations
try {
insertDuplicateEmail();
} catch (SQLException e) {
System.out.println("Caught constraint violation:");
System.out.println(" Error code: " + e.getErrorCode());
System.out.println(" SQL state: " + e.getSQLState());
System.out.println(" Message: " + e.getMessage());
}
// Example 2: Handling SQL syntax errors
try {
executeBadSQL();
} catch (SQLException e) {
System.out.println("\nCaught SQL syntax error:");
System.out.println(" Message: " + e.getMessage());
}
// Example 3: Demonstrating proper resource cleanup
demonstrateResourceCleanup();
}
private static void setupDatabase() {
try (Connection conn = DriverManager.getConnection(DB_URL, USER, PASSWORD);
Statement stmt = conn.createStatement()) {
stmt.execute("""
CREATE TABLE IF NOT EXISTS students (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(100) UNIQUE NOT NULL,
gpa DECIMAL(3, 2)
)
""");
stmt.executeUpdate("""
INSERT INTO students (name, email, gpa) VALUES
('Alice Johnson', 'alice@university.edu', 3.85)
""");
} catch (SQLException e) {
e.printStackTrace();
}
}
private static void insertDuplicateEmail() throws SQLException {
try (Connection conn = DriverManager.getConnection(DB_URL, USER, PASSWORD);
PreparedStatement pstmt = conn.prepareStatement(
"INSERT INTO students (name, email, gpa) VALUES (?, ?, ?)")) {
pstmt.setString(1, "Another Alice");
pstmt.setString(2, "alice@university.edu"); // Duplicate!
pstmt.setDouble(3, 3.50);
pstmt.executeUpdate();
}
}
private static void executeBadSQL() throws SQLException {
try (Connection conn = DriverManager.getConnection(DB_URL, USER, PASSWORD);
Statement stmt = conn.createStatement()) {
// Intentionally malformed SQL
stmt.executeQuery("SELECT * FORM students"); // FORM instead of FROM
}
}
private static void demonstrateResourceCleanup() {
System.out.println("\nDemonstrating proper resource cleanup:");
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
try {
conn = DriverManager.getConnection(DB_URL, USER, PASSWORD);
stmt = conn.createStatement();
rs = stmt.executeQuery("SELECT COUNT(*) as count FROM students");
if (rs.next()) {
System.out.println("Student count: " + rs.getInt("count"));
}
} catch (SQLException e) {
System.err.println("Error: " + e.getMessage());
} finally {
// Manual cleanup (try-with-resources is better!)
try {
if (rs != null) rs.close();
if (stmt != null) stmt.close();
if (conn != null) conn.close();
System.out.println("Resources cleaned up manually in finally block");
} catch (SQLException e) {
System.err.println("Error closing resources: " + e.getMessage());
}
}
}
}The finally block in that last example shows the old way of ensuring resources get closed. You have to check for null (because initialization might have failed partway through) and wrap the close calls in a try-catch (because close() itself can throw SQLException). This is verbose and error-prone. Try-with-resources is much cleaner and has been the standard since Java 7.
SQL states are standardized codes that indicate the category of error. A state starting with "23" indicates a constraint violation. States starting with "42" are syntax errors. You can use these codes to handle different error types programmatically rather than parsing error messages.
JDBC is the foundation of database access in Java. It provides a vendor-neutral API designed for Java programmers to connect to any database and execute SQL queries. Understanding how to use the JDBC API effectively is essential for building data-driven applications, whether you're working directly with JDBC or using higher-level frameworks that wrap it.
The role of JDBC drivers is to translate standard JDBC calls into database-specific protocols. You add a driver JAR to your classpath, and the driver registers itself automatically in modern JDBC. This abstraction lets you write portable code that works across different database systems. You can access any kind of tabular data through JDBC—from traditional relational databases to newer SQL-compliant data stores.
This JDBC tutorial covered the complete lifecycle of database operations. You've seen how to establish connections, execute queries with Statement and PreparedStatement, process results with ResultSet, and perform full CRUD operations. The patterns are consistent across all operations: obtain a connection, create a statement, execute SQL, handle results or update counts, and close resources. PreparedStatement is your primary tool for anything involving parameters—it's safer, cleaner, and often faster than concatenating SQL strings.
Resource management can't be stressed enough. Always use try-with-resources for Connections, Statements, and ResultSets. Leaked connections are one of the most common causes of production failures. The database has a limited number of concurrent connections it can handle, and if your application doesn't return them to the pool, you'll eventually exhaust the available connections and grind to a halt.
JDBC is straightforward once you understand these core patterns. Modern applications often use frameworks like Spring JDBC, JPA, or Hibernate that abstract away some of the boilerplate, but those frameworks are all built on top of JDBC. Understanding JDBC gives you insight into how database access actually works in Java, and that knowledge makes you a better developer regardless of what abstractions you're using.
Introduction to JDBC. Last updated January 20, 2026.
Join our Java Bootcamp to master enterprise-level development, or start with our free Core Java course to build your foundation.