palak singla

Posted on Sep 1

Java JDBC Explained: How to Perform CRUD Operations with Database

#java #database #sql #tutorial

Imagine you’re building a Student Management System where you need to store and retrieve student records. If you only rely on arrays or collections, the moment your program ends, all data vanishes.

That’s why we use databases to persist information. But here comes the question:
👉 How does a Java program talk to a database like MySQL?

The answer is JDBC (Java Database Connectivity).

JDBC is a Java API that allows Java applications to interact with relational databases. It’s like a translator between Java and SQL — you write Java code, it converts it into SQL queries, sends them to the database, and fetches results back into Java objects.

JDBC Architecture Overview

At its core, JDBC provides a bridge between a Java application and a relational database (like MySQL, PostgreSQL, Oracle, etc.).

Components of JDBC Architecture

JDBC API (Java Application Layer) - The set of interfaces and classes provided by Java (java.sql package). Developers write code using the JDBC API without worrying about database-specific details.
Example: Connection, Statement, PreparedStatement, ResultSet.
JDBC Driver Manager - Manages a list of database drivers. Matches the connection request (like jdbc:mysql://...) with the appropriate driver.
Example: DriverManager.getConnection(...) loads the MySQL driver.
JDBC Driver (Vendor Specific) - The actual implementation provided by database vendors. Converts JDBC API calls into database-specific calls (like MySQL protocol, Oracle protocol).
Example: mysql-connector-java-x.x.jar for MySQL.
Database - The actual relational database (MySQL, Oracle, PostgreSQL, etc.). Executes the queries and returns results.

How JDBC Works (Flow)

Java app calls JDBC API methods.
DriverManager picks the right driver.
JDBC Driver translates the call into database-specific commands.
Database executes the command and returns results.
JDBC Driver translates results back into Java objects (ResultSet).

Database Setup (Before Running Code)

Before running JDBC code, you need:

Install MySQL (or use any other DB like PostgreSQL). Create a database

CREATE DATABASE testdb;
USE testdb;

Create a table

CREATE TABLE students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    age INT
);

Add MySQL JDBC Driver to Project

(i)If using Maven, add this dependency to pom.xml:

   <dependency>
       <groupId>mysql</groupId>
       <artifactId>mysql-connector-java</artifactId>
       <version>8.0.33</version>
   </dependency>

(ii)If not using Maven, download the MySQL Connector JAR and add it to your project’s classpath.

Steps to Connect Java with Database (The JDBC Workflow)

Every JDBC program follows these 6 steps:

Load the Driver (Not mandatory from Java 6 onwards, but still shown for clarity)

Class.forName("com.mysql.cj.jdbc.Driver");

Establish Connection

Connection con = DriverManager.getConnection(
    "jdbc:mysql://localhost:3306/testdb", "root", "password");

Create Statement

Statement stmt = con.createStatement();

Execute Query

ResultSet rs = stmt.executeQuery("SELECT * FROM students");

Process Results

while (rs.next()) {
    System.out.println(rs.getInt("id") + " - " + rs.getString("name"));
}

Close Resources

con.close();

Code Example

import java.sql.*;

public class JDBCDemo {
    public static void main(String[] args) {
        try {
            // Step 1: Connect to DB
            Connection con = DriverManager.getConnection(
                "jdbc:mysql://localhost:3306/testdb", "root", "password");

            // Step 2: Create Statement
            Statement stmt = con.createStatement();

            // Step 3: Execute Query
            ResultSet rs = stmt.executeQuery("SELECT * FROM students");

            // Step 4: Process Results
            while (rs.next()) {
                System.out.println(rs.getInt("id") + " - " + rs.getString("name"));
            }

            // Step 5: Close Connection
            con.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

This is the simplest JDBC program to fetch all student records.

PreparedStatement vs Statement

Statement – Executes raw SQL queries.
PreparedStatement – Precompiled query with placeholders (?).
✅ Why PreparedStatement is better:

1. Prevents SQL Injection
2. Faster performance for repeated queries
3. Cleaner syntax Example

String sql = "INSERT INTO students (name, age) VALUES (?, ?)";
PreparedStatement pstmt = con.prepareStatement(sql);
pstmt.setString(1, "Alice");
pstmt.setInt(2, 22);
pstmt.executeUpdate();

CRUD Operations Example (Mini Project)

Let’s build a small Student Database CRUD:
Insert Data

String query = "INSERT INTO students (name, age) VALUES (?, ?)";
PreparedStatement ps = con.prepareStatement(query);
ps.setString(1, "John");
ps.setInt(2, 21);
ps.executeUpdate();

Read Data

ResultSet rs = stmt.executeQuery("SELECT * FROM students");
while (rs.next()) {
    System.out.println(rs.getInt("id") + " " + rs.getString("name"));
}

Update Data

String query = "UPDATE students SET age=? WHERE name=?";
PreparedStatement ps = con.prepareStatement(query);
ps.setInt(1, 23);
ps.setString(2, "John");
ps.executeUpdate();

Delete Data

String query = "DELETE FROM students WHERE name=?";
PreparedStatement ps = con.prepareStatement(query);
ps.setString(1, "John");
ps.executeUpdate();

This covers all 4 CRUD operations (Create, Read, Update, Delete).

Best Practices

Always close Connection, Statement, and ResultSet → use try-with-resources
Prefer PreparedStatement over Statement
Use connection pooling in production (HikariCP, Apache DBCP)
Don’t hardcode DB credentials — use config files
Log errors properly instead of printStackTrace()

Key Takeaways

JDBC is the standard way to connect Java apps with databases.
Every JDBC program follows the 6-step workflow.
PreparedStatement is faster and safer than Statement.
CRUD operations are the foundation of database programming.
JDBC is the stepping stone to Hibernate and Spring Data JPA.

Conclusion

JDBC may look verbose, but it’s the backbone of Java database programming. Once you understand JDBC, moving to modern frameworks like Hibernate and Spring Boot becomes much easier.

👉 As your first project, try building a Student Management System where you can insert, update, delete, and view student records using JDBC. This will give you hands-on practice and strengthen your fundamentals.

DEV Community