JDBC with MySQL (Java Tutorial)

Almost every business application stores its data in a database: students and marks, orders and payments, train bookings. JDBC (Java Database Connectivity) is the standard Java API for talking to relational databases. Your code uses the interfaces in java.sql, and a driver for your specific database does the actual communication.

In this lesson you will connect to MySQL, run queries safely with PreparedStatement, read results, insert rows and get their new IDs, organise database code into a small DAO class, and use transactions so that a money transfer either happens completely or not at all. You should know basic SQL; the SQL tutorial covers it.

Setting up the database and driver

First, create a database and a table in MySQL (for example with the mysql command-line client or MySQL Workbench):

SQL

CREATE DATABASE college CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
USE college;

CREATE TABLE students (
    roll_no INT AUTO_INCREMENT PRIMARY KEY,
    name    VARCHAR(50) NOT NULL,
    city    VARCHAR(50) NOT NULL,
    marks   INT NOT NULL
);

INSERT INTO students (name, city, marks) VALUES
    ('Ananya', 'Lucknow', 91), ('Farhan', 'Pune', 78),
    ('Isha', 'Kochi', 95), ('Rohan', 'Rohtak', 58);

Next, add MySQL's official JDBC driver, Connector/J, to your project. With Maven (see Maven & Gradle):

XML

<dependency>
    <groupId>com.mysql</groupId>
    <artifactId>mysql-connector-j</artifactId>
    <version>26.10.0</version>
    <scope>runtime</scope>
</dependency>

Connector/J now uses calendar-style version numbers (26.10 means October 2026) and supports MySQL 8.4 and later. Your code never refers to the driver's classes directly; JDBC finds the driver on the class path automatically.

Connecting and running a query

A JDBC URL tells DriverManager which database to open: jdbc:mysql://host:port/database. Never write passwords into source code; read them from environment variables or a configuration file outside your project. Connection, PreparedStatement and ResultSet all hold database resources, so open them in try-with-resources.

Java

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class ListStudents {
    static final String URL = "jdbc:mysql://localhost:3306/college";

    public static void main(String[] args) {
        String user = System.getenv().getOrDefault("DB_USER", "root");
        String password = System.getenv().getOrDefault("DB_PASSWORD", "");

        String sql = "SELECT roll_no, name, city, marks FROM students WHERE marks >= ? ORDER BY marks DESC";
        try (Connection conn = DriverManager.getConnection(URL, user, password);
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setInt(1, 75);
            try (ResultSet rs = stmt.executeQuery()) {
                while (rs.next()) {
                    System.out.printf("%d  %-7s %-8s %d%n",
                            rs.getInt("roll_no"), rs.getString("name"),
                            rs.getString("city"), rs.getInt("marks"));
                }
            }
        } catch (SQLException e) {
            System.out.println("Database error: " + e.getMessage());
        }
    }
}

Output

3  Isha    Kochi    95
1  Ananya  Lucknow  91
2  Farhan  Pune     78

The ? is a parameter placeholder. setInt(1, 75) fills the first ? (placeholders are numbered from 1, not 0).
executeQuery() runs a SELECT and returns a ResultSet, a cursor over the result rows. rs.next() moves to the next row and returns false when there are no more.
getInt, getString, getBigDecimal, getDate and friends read a column by name (or by number, from 1).
Every JDBC method can throw the checked SQLException, for example when the password is wrong or the table does not exist.

Never build SQL by joining strings

Imagine a search box where the user types a name, and the code glues it into the SQL:

Java

public class Injection {
    public static void main(String[] args) {
        String typed = "x' OR '1'='1";
        String sql = "SELECT * FROM students WHERE name = '" + typed + "'";
        System.out.println(sql);
    }
}

Output

SELECT * FROM students WHERE name = 'x' OR '1'='1'

The user's quote character ended the string early, and OR '1'='1' is always true, so this query returns every student. With a DELETE or a login check, the damage is far worse. This attack is called SQL injection, and it is still one of the most common security holes in real applications.

PreparedStatement prevents it completely: the SQL is sent to the database with ? placeholders, and the values are sent separately, so the database never treats them as SQL. Always use PreparedStatement with ? for any value that comes from outside your program.

A DAO with records

Spreading SQL across your whole program makes it hard to maintain. A common pattern is a DAO (Data Access Object): one class that holds all the SQL for one table and converts rows into Java objects, here a record.

Java

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Optional;

public class StudentDao {
    public record Student(int rollNo, String name, String city, int marks) {}

    private final Connection conn;

    public StudentDao(Connection conn) {
        this.conn = conn;
    }

    public int add(String name, String city, int marks) throws SQLException {
        String sql = "INSERT INTO students (name, city, marks) VALUES (?, ?, ?)";
        try (PreparedStatement stmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
            stmt.setString(1, name);
            stmt.setString(2, city);
            stmt.setInt(3, marks);
            stmt.executeUpdate();
            try (ResultSet keys = stmt.getGeneratedKeys()) {
                keys.next();
                return keys.getInt(1);                 // the new roll_no
            }
        }
    }

    public Optional<Student> findByRoll(int rollNo) throws SQLException {
        String sql = "SELECT roll_no, name, city, marks FROM students WHERE roll_no = ?";
        try (PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setInt(1, rollNo);
            try (ResultSet rs = stmt.executeQuery()) {
                if (rs.next()) {
                    return Optional.of(toStudent(rs));
                }
                return Optional.empty();
            }
        }
    }

    public List<Student> findByCity(String city) throws SQLException {
        String sql = "SELECT roll_no, name, city, marks FROM students WHERE city = ? ORDER BY name";
        List<Student> result = new ArrayList<>();
        try (PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setString(1, city);
            try (ResultSet rs = stmt.executeQuery()) {
                while (rs.next()) {
                    result.add(toStudent(rs));
                }
            }
        }
        return result;
    }

    public int updateMarks(int rollNo, int marks) throws SQLException {
        String sql = "UPDATE students SET marks = ? WHERE roll_no = ?";
        try (PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setInt(1, marks);
            stmt.setInt(2, rollNo);
            return stmt.executeUpdate();               // number of rows changed
        }
    }

    public int delete(int rollNo) throws SQLException {
        try (PreparedStatement stmt = conn.prepareStatement("DELETE FROM students WHERE roll_no = ?")) {
            stmt.setInt(1, rollNo);
            return stmt.executeUpdate();
        }
    }

    private static Student toStudent(ResultSet rs) throws SQLException {
        return new Student(rs.getInt("roll_no"), rs.getString("name"),
                           rs.getString("city"), rs.getInt("marks"));
    }
}

Using it reads almost like plain English:

Java

try (Connection conn = DriverManager.getConnection(URL, user, password)) {
    StudentDao dao = new StudentDao(conn);

    int roll = dao.add("Meera", "Kochi", 84);
    System.out.println("Added Meera with roll number " + roll);
    System.out.println(dao.findByCity("Kochi"));

    System.out.println("Rows updated: " + dao.updateMarks(roll, 88));
    System.out.println(dao.findByRoll(roll).orElseThrow());
    System.out.println("Rows deleted: " + dao.delete(roll));
    System.out.println("Found after delete? " + dao.findByRoll(roll).isPresent());
}

Output

Added Meera with roll number 5
[Student[rollNo=3, name=Isha, city=Kochi, marks=95], Student[rollNo=5, name=Meera, city=Kochi, marks=84]]
Rows updated: 1
Student[rollNo=5, name=Meera, city=Kochi, marks=88]
Rows deleted: 1
Found after delete? false

executeUpdate() runs INSERT, UPDATE or DELETE and returns how many rows changed, which tells you whether the row existed. RETURN_GENERATED_KEYS gives back the AUTO_INCREMENT value MySQL assigned. A search that may find nothing returns Optional, as you learned in Streams & Optional.

Transactions: all or nothing

Moving money from Meera's wallet to Kabir's needs two UPDATEs. If the program crashes after the first, money disappears. A transaction groups statements so they either all succeed (commit) or all are undone (rollback). By default JDBC commits after every statement (auto-commit); switch it off to group statements.

Java

static void transfer(Connection conn, String from, String to, BigDecimal amount) throws SQLException {
    String debit = "UPDATE wallets SET balance = balance - ? WHERE owner = ? AND balance >= ?";
    String credit = "UPDATE wallets SET balance = balance + ? WHERE owner = ?";

    conn.setAutoCommit(false);                       // start a transaction
    try (PreparedStatement d = conn.prepareStatement(debit);
         PreparedStatement c = conn.prepareStatement(credit)) {
        d.setBigDecimal(1, amount);
        d.setString(2, from);
        d.setBigDecimal(3, amount);
        if (d.executeUpdate() != 1) {
            throw new SQLException("Not enough balance in " + from + "'s wallet");
        }
        c.setBigDecimal(1, amount);
        c.setString(2, to);
        if (c.executeUpdate() != 1) {
            throw new SQLException("No wallet for " + to);
        }
        conn.commit();                               // both changes become permanent
        System.out.println("Moved Rs " + amount + " from " + from + " to " + to);
    } catch (SQLException e) {
        conn.rollback();                             // undo everything since setAutoCommit(false)
        System.out.println("Cancelled: " + e.getMessage());
    } finally {
        conn.setAutoCommit(true);
    }
}

With a wallets table (owner VARCHAR(50) PRIMARY KEY, balance DECIMAL(10,2)) holding Meera Rs 5000.00 and Kabir Rs 1200.00, calling transfer three times (Meera to Kabir 1500, Kabir to Meera 9000, Meera to Priya 100) and then listing the wallets prints:

Output

Moved Rs 1500.00 from Meera to Kabir
Cancelled: Not enough balance in Kabir's wallet
Cancelled: No wallet for Priya
  Kabir: Rs 2700.00
  Meera: Rs 3500.00

The third transfer had already taken Rs 100 from Meera when it discovered that Priya has no wallet. The rollback put the money back, so Meera still has Rs 3500.00. Money columns use DECIMAL in SQL and BigDecimal in Java, never double.

Common mistakes

Concatenating user input into SQL. Always use ? placeholders with PreparedStatement.
Forgetting to close resources. Use try-with-resources for Connection, PreparedStatement and ResultSet.
Reading before rs.next(). A ResultSet starts before the first row; call next() first.
Numbering parameters from 0. JDBC placeholders and column numbers start at 1.
Hard-coding passwords. Read credentials from environment variables or configuration outside the code.
Old driver code. Class.forName("com.mysql.jdbc.Driver") is from very old tutorials; modern drivers register themselves, and that class name belongs to the outdated 5.x driver.

Key points

JDBC (java.sql) is the standard API; a driver such as MySQL Connector/J (com.mysql:mysql-connector-j) connects to the database.
DriverManager.getConnection("jdbc:mysql://localhost:3306/college", user, password) opens a connection.
PreparedStatement with ? placeholders prevents SQL injection; executeQuery returns a ResultSet, executeUpdate returns a row count.
RETURN_GENERATED_KEYS retrieves AUTO_INCREMENT IDs.
A DAO class keeps SQL in one place and maps rows to records.
setAutoCommit(false), commit() and rollback() make several statements succeed or fail together.

Exercise

Add a method List<Student> toppers(int minMarks, int limit) to StudentDao that returns students with at least minMarks, highest first, at most limit rows (use LIMIT ?). Then add double averageMarks() that runs SELECT AVG(marks) FROM students. Using the four starting students, print toppers(75, 2) and the average.

Show solution

Java

public List<Student> toppers(int minMarks, int limit) throws SQLException {
    String sql = "SELECT roll_no, name, city, marks FROM students "
               + "WHERE marks >= ? ORDER BY marks DESC LIMIT ?";
    List<Student> result = new ArrayList<>();
    try (PreparedStatement stmt = conn.prepareStatement(sql)) {
        stmt.setInt(1, minMarks);
        stmt.setInt(2, limit);
        try (ResultSet rs = stmt.executeQuery()) {
            while (rs.next()) {
                result.add(toStudent(rs));
            }
        }
    }
    return result;
}

public double averageMarks() throws SQLException {
    try (PreparedStatement stmt = conn.prepareStatement("SELECT AVG(marks) FROM students");
         ResultSet rs = stmt.executeQuery()) {
        rs.next();
        return rs.getDouble(1);
    }
}

Java

System.out.println(dao.toppers(75, 2));
System.out.println("Average: " + dao.averageMarks());

Output

[Student[rollNo=3, name=Isha, city=Kochi, marks=95], Student[rollNo=1, name=Ananya, city=Lucknow, marks=91]]
Average: 80.5

LIMIT ? is filled like any other parameter. An aggregate query always returns exactly one row, so one call to rs.next() is enough, and getDouble(1) reads the first column.

Quick Quiz

1. Why should you use PreparedStatement with ? placeholders?

2. Where does a ResultSet cursor start?

rs.next() moves to the first row and returns false when no rows are left.

3. What does executeUpdate() return?

4. A transfer fails after the first of two UPDATEs. What makes the first UPDATE disappear?

In a transaction, rollback() undoes every change since auto-commit was switched off.

Web Development

Programming Languages

Databases

Data Science & AI

Cloud & DevOps

Security & Systems

Office & Productivity

JDBC with MySQL

Setting up the database and driver

Connecting and running a query

Never build SQL by joining strings

A DAO with records

Transactions: all or nothing

Common mistakes

Key points

Exercise

Quick Quiz

Learn Java with a mentor