JDBC: connecting to MySQL

Connector/J, DriverManager, PreparedStatement, ResultSet, CRUD, transactions and SQL-injection safety.

Almost every business application stores its data in a relational database. JDBC (Java Database Connectivity, packages java.sql and javax.sql) is the standard API for talking to databases from Java. Frameworks like Spring Data JPA and Hibernate are built on top of it, so understanding JDBC makes you better at all of them. In this lesson we connect to MySQL 8, run queries safely, and use transactions.

Prerequisite: a running MySQL 8.x server. The MySQL course on Elephantoo shows how to install one. On Ubuntu/Debian, sudo apt install mysql-server (or mariadb-server, which works with the same driver for these examples); on Fedora, sudo dnf install community-mysql-server; or run docker run -p 3306:3306 -e MYSQL_ROOT_PASSWORD=root -d mysql:8.4.

How JDBC fits together#

Output

Your code  ──►  JDBC API (java.sql)  ──►  JDBC driver (MySQL Connector/J)  ──►  MySQL server
             Connection, PreparedStatement,          vendor-specific network protocol
             ResultSet, SQLException

You program against the standard interfaces; the driver JAR translates to the MySQL protocol. Switching to PostgreSQL mostly means swapping the driver and the connection URL.

Step 1: create a database and user#

In the mysql client (as root):

SQL

CREATE DATABASE shop;
CREATE USER 'shop_app'@'localhost' IDENTIFIED BY 'S3cret!pass';
GRANT ALL PRIVILEGES ON shop.* TO 'shop_app'@'localhost';

Give applications their own user with access only to their own database, never root.

Step 2: add the MySQL driver#

With Maven, add the dependency (see the build tools lesson):

XML

<dependency>
    <groupId>com.mysql</groupId>
    <artifactId>mysql-connector-j</artifactId>
    <version>26.7.0</version>
</dependency>

Or with Gradle: runtimeOnly("com.mysql:mysql-connector-j:26.7.0"). Any recent Connector/J version (9.x or newer) works with MySQL 8.x. For quick single-file experiments, download the JAR from Maven Central and put it on the classpath:

Terminal

java -cp mysql-connector-j-26.7.0.jar ConnectTest.java

Modern drivers register themselves automatically; the old Class.forName("com.mysql.cj.jdbc.Driver") line is no longer needed.

Step 3: connect#

A connection URL has the form jdbc:mysql://host:port/database?options:

ConnectTest.java

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.SQLException;

public class ConnectTest {
    static final String URL = System.getenv().getOrDefault("DB_URL", "jdbc:mysql://localhost:3306/shop");
    static final String USER = System.getenv().getOrDefault("DB_USER", "shop_app");
    static final String PASSWORD = System.getenv().getOrDefault("DB_PASSWORD", "S3cret!pass");

    public static void main(String[] args) {
        try (Connection conn = DriverManager.getConnection(URL, USER, PASSWORD)) {
            DatabaseMetaData meta = conn.getMetaData();
            System.out.println("Connected to " + meta.getDatabaseProductName() + " " + meta.getDatabaseProductVersion());
            System.out.println("Driver: " + meta.getDriverName());
        } catch (SQLException e) {
            System.out.println("Connection failed: " + e.getMessage());
        }
    }
}

Output

Connected to MySQL 8.4.9
Driver: MySQL Connector/J

Connection is AutoCloseable. Always open it in try-with-resources; leaked connections exhaust the server.
Read credentials from environment variables or configuration, never hard-code real passwords in source code.
If it fails, the message usually tells you why: Access denied for user (credentials), Communications link failure (server not running or wrong host/port), Unknown database (typo or missing CREATE DATABASE).

Step 4: create a table and run statements#

Statement runs fixed SQL. For DDL without user input it's fine:

Java

try (Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);
     Statement st = conn.createStatement()) {
    st.executeUpdate("""
        CREATE TABLE IF NOT EXISTS products (
            id    INT AUTO_INCREMENT PRIMARY KEY,
            name  VARCHAR(100) NOT NULL,
            price DECIMAL(10, 2) NOT NULL,
            stock INT NOT NULL DEFAULT 0
        )""");
}

Method	Use for	Returns
`executeQuery(sql)`	`SELECT`	a `ResultSet`
`executeUpdate(sql)`	`INSERT`, `UPDATE`, `DELETE`, DDL	number of affected rows
`execute(sql)`	anything	`true` if there's a `ResultSet`

`PreparedStatement`: parameters done right#

Whenever a value comes from outside (users, files, APIs), use a PreparedStatement with ? placeholders:

ProductDao.java

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.DriverManager;
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 ProductDao {
    static final String URL = System.getenv().getOrDefault("DB_URL", "jdbc:mysql://localhost:3306/shop");
    static final String USER = System.getenv().getOrDefault("DB_USER", "shop_app");
    static final String PASSWORD = System.getenv().getOrDefault("DB_PASSWORD", "S3cret!pass");

    record Product(int id, String name, BigDecimal price, int stock) { }

    private final Connection conn;

    ProductDao(Connection conn) { this.conn = conn; }

    void resetTable() throws SQLException {
        try (Statement st = conn.createStatement()) {
            st.executeUpdate("DROP TABLE IF EXISTS products");
            st.executeUpdate("""
                CREATE TABLE products (
                    id    INT AUTO_INCREMENT PRIMARY KEY,
                    name  VARCHAR(100) NOT NULL,
                    price DECIMAL(10, 2) NOT NULL,
                    stock INT NOT NULL DEFAULT 0
                )""");
        }
    }

    int insert(String name, BigDecimal price, int stock) throws SQLException {
        String sql = "INSERT INTO products (name, price, stock) VALUES (?, ?, ?)";
        try (PreparedStatement ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
            ps.setString(1, name);            // parameter indexes start at 1
            ps.setBigDecimal(2, price);
            ps.setInt(3, stock);
            ps.executeUpdate();
            try (ResultSet keys = ps.getGeneratedKeys()) {
                keys.next();
                return keys.getInt(1);        // the AUTO_INCREMENT id
            }
        }
    }

    Optional<Product> findById(int id) throws SQLException {
        try (PreparedStatement ps = conn.prepareStatement("SELECT id, name, price, stock FROM products WHERE id = ?")) {
            ps.setInt(1, id);
            try (ResultSet rs = ps.executeQuery()) {
                return rs.next() ? Optional.of(map(rs)) : Optional.empty();
            }
        }
    }

    List<Product> findCheaperThan(BigDecimal max) throws SQLException {
        String sql = "SELECT id, name, price, stock FROM products WHERE price < ? ORDER BY price";
        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setBigDecimal(1, max);
            try (ResultSet rs = ps.executeQuery()) {
                List<Product> out = new ArrayList<>();
                while (rs.next()) out.add(map(rs));      // move the cursor row by row
                return out;
            }
        }
    }

    int updatePrice(int id, BigDecimal newPrice) throws SQLException {
        try (PreparedStatement ps = conn.prepareStatement("UPDATE products SET price = ? WHERE id = ?")) {
            ps.setBigDecimal(1, newPrice);
            ps.setInt(2, id);
            return ps.executeUpdate();                   // rows affected
        }
    }

    int delete(int id) throws SQLException {
        try (PreparedStatement ps = conn.prepareStatement("DELETE FROM products WHERE id = ?")) {
            ps.setInt(1, id);
            return ps.executeUpdate();
        }
    }

    private static Product map(ResultSet rs) throws SQLException {
        return new Product(rs.getInt("id"), rs.getString("name"), rs.getBigDecimal("price"), rs.getInt("stock"));
    }

    public static void main(String[] args) throws SQLException {
        try (Connection conn = DriverManager.getConnection(URL, USER, PASSWORD)) {
            ProductDao dao = new ProductDao(conn);
            dao.resetTable();

            int pen = dao.insert("Pen", new BigDecimal("25.00"), 100);
            int book = dao.insert("Notebook", new BigDecimal("60.00"), 40);
            int bag = dao.insert("Backpack", new BigDecimal("1499.00"), 5);
            System.out.println("Inserted ids " + pen + ", " + book + ", " + bag);

            System.out.println(dao.findById(book).orElseThrow());
            System.out.println(dao.findById(99).isPresent());

            System.out.println("Updated rows: " + dao.updatePrice(pen, new BigDecimal("22.50")));
            for (Product p : dao.findCheaperThan(new BigDecimal("100"))) {
                System.out.println("  " + p.name() + " @ " + p.price());
            }

            System.out.println("Deleted rows: " + dao.delete(bag));
            System.out.println("Deleted rows: " + dao.delete(bag));
        }
    }
}

Output

Inserted ids 1, 2, 3
Product[id=2, name=Notebook, price=60.00, stock=40]
false
Updated rows: 1
  Pen @ 22.50
  Notebook @ 60.00
Deleted rows: 1
Deleted rows: 0

This DAO (Data Access Object) pattern keeps SQL in one class and exposes plain Java methods to the rest of the application.

Reading a `ResultSet`

A ResultSet is a cursor positioned before the first row. rs.next() moves to the next row and returns false when there are no more.
Read columns by label (rs.getString("name")) or 1-based index (rs.getString(1)). Labels are clearer.
Typed getters: getInt, getLong, getString, getBigDecimal, getBoolean, getObject(col, LocalDate.class)...
For nullable numeric columns, getInt returns 0 for SQL NULL. Check rs.wasNull() afterwards, or use rs.getObject("col", Integer.class), which returns null.

Java ↔ MySQL types

MySQL	Java
`INT`	`int` / `Integer`
`BIGINT`	`long` / `Long`
`DECIMAL(p,s)`	`BigDecimal` (always for money)
`VARCHAR`, `TEXT`	`String`
`BOOLEAN` / `TINYINT(1)`	`boolean`
`DATE`	`LocalDate` (`getObject(col, LocalDate.class)`)
`DATETIME`	`LocalDateTime`
`TIMESTAMP`	`Instant`, or `LocalDateTime` in the session time zone

SQL injection: why concatenation is dangerous#

Injection.java

public class Injection {
    public static void main(String[] args) {
        String userInput = "x' OR '1'='1";

        String unsafe = "SELECT * FROM users WHERE name = '" + userInput + "'";
        System.out.println(unsafe);
    }
}

Output

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

The attacker's input changed the meaning of the query: OR '1'='1' is always true, so it returns every user. Worse inputs can delete tables or read passwords. With PreparedStatement, the same input is just an odd-looking name that matches nothing:

Java

PreparedStatement ps = conn.prepareStatement("SELECT * FROM users WHERE name = ?");
ps.setString(1, userInput);   // sent as data, never interpreted as SQL

Rule: never build SQL by concatenating values. Placeholders can't be used for table or column names; if those must vary, choose them from a fixed allow-list in code.

Transactions#

By default each statement is committed immediately (auto-commit). A transaction groups statements so they succeed or fail together. The classic example is a money transfer:

Transfer.java

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class Transfer {
    static final String URL = System.getenv().getOrDefault("DB_URL", "jdbc:mysql://localhost:3306/shop");
    static final String USER = System.getenv().getOrDefault("DB_USER", "shop_app");
    static final String PASSWORD = System.getenv().getOrDefault("DB_PASSWORD", "S3cret!pass");

    static void transfer(Connection conn, int from, int to, BigDecimal amount) throws SQLException {
        boolean oldAutoCommit = conn.getAutoCommit();
        conn.setAutoCommit(false);                          // start a transaction
        try (PreparedStatement debit = conn.prepareStatement(
                 "UPDATE accounts SET balance = balance - ? WHERE id = ? AND balance >= ?");
             PreparedStatement credit = conn.prepareStatement(
                 "UPDATE accounts SET balance = balance + ? WHERE id = ?")) {

            debit.setBigDecimal(1, amount);
            debit.setInt(2, from);
            debit.setBigDecimal(3, amount);
            if (debit.executeUpdate() != 1) {
                throw new SQLException("Insufficient funds or unknown account " + from);
            }

            credit.setBigDecimal(1, amount);
            credit.setInt(2, to);
            if (credit.executeUpdate() != 1) {
                throw new SQLException("Unknown account " + to);
            }

            conn.commit();                                  // both succeeded: make permanent
        } catch (SQLException e) {
            conn.rollback();                                // undo everything since setAutoCommit(false)
            throw e;
        } finally {
            conn.setAutoCommit(oldAutoCommit);
        }
    }

    static void printBalances(Connection conn) throws SQLException {
        try (Statement st = conn.createStatement();
             ResultSet rs = st.executeQuery("SELECT id, owner, balance FROM accounts ORDER BY id")) {
            while (rs.next()) {
                System.out.println("  " + rs.getInt("id") + " " + rs.getString("owner") + ": " + rs.getBigDecimal("balance"));
            }
        }
    }

    public static void main(String[] args) throws SQLException {
        try (Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);
             Statement st = conn.createStatement()) {
            st.executeUpdate("DROP TABLE IF EXISTS accounts");
            st.executeUpdate("CREATE TABLE accounts (id INT PRIMARY KEY, owner VARCHAR(50), balance DECIMAL(10,2))");
            st.executeUpdate("INSERT INTO accounts VALUES (1, 'Asha', 1000.00), (2, 'Ben', 200.00)");

            transfer(conn, 1, 2, new BigDecimal("300.00"));
            System.out.println("After a successful transfer:");
            printBalances(conn);

            try {
                transfer(conn, 1, 99, new BigDecimal("100.00"));   // credit fails: no account 99
            } catch (SQLException e) {
                System.out.println("Rolled back: " + e.getMessage());
            }
            printBalances(conn);                                    // Asha was NOT debited
        }
    }
}

Output

After a successful transfer:
  1 Asha: 700.00
  2 Ben: 500.00
Rolled back: Unknown account 99
  1 Asha: 700.00
  2 Ben: 500.00

The failed transfer had already debited Asha when the credit failed; rollback() undid it. Transactions require a transactional storage engine, which MySQL's default InnoDB is.

Batch inserts#

Sending rows one at a time costs a network round trip each. Batching is far faster for bulk loads:

Java

String sql = "INSERT INTO products (name, price, stock) VALUES (?, ?, ?)";
conn.setAutoCommit(false);
try (PreparedStatement ps = conn.prepareStatement(sql)) {
    for (int i = 1; i <= 1000; i++) {
        ps.setString(1, "Item " + i);
        ps.setBigDecimal(2, BigDecimal.valueOf(i));
        ps.setInt(3, 10);
        ps.addBatch();
    }
    ps.executeBatch();
    conn.commit();
}

Add ?rewriteBatchedStatements=true to the MySQL URL so Connector/J combines the batch into multi-row INSERTs.

Connection pools#

Opening a database connection takes time (network handshake, authentication). Real applications use a connection pool that keeps connections open and lends them out. HikariCP is the standard (and Spring Boot's default):

Java

HikariConfig config = new HikariConfig();
config.setJdbcUrl("jdbc:mysql://localhost:3306/shop");
config.setUsername("shop_app");
config.setPassword(System.getenv("DB_PASSWORD"));
config.setMaximumPoolSize(10);
DataSource ds = new HikariDataSource(config);

try (Connection conn = ds.getConnection()) {   // borrowed; close() returns it to the pool
    // ...
}

Code written against DataSource works the same with or without a pool.

Beyond plain JDBC#

Raw JDBC is verbose. In practice you'll often use:

Spring's JdbcClient / JdbcTemplate: JDBC without the boilerplate.
JPA/Hibernate with Spring Data JPA: map classes to tables and write interface ProductRepository extends JpaRepository<Product, Long>.
jOOQ: type-safe SQL built in Java code.
Flyway or Liquibase: versioned schema migrations.

All of them use JDBC underneath, and every concept here (connections, prepared statements, transactions, pools) still applies.

Common mistakes#

Concatenating user input into SQL.
Not closing Connection, PreparedStatement and ResultSet. Use try-with-resources for all three.
Using double for money columns instead of DECIMAL + BigDecimal.
Forgetting rs.next() before reading the first row (Illegal operation on empty result set).
0-based indexes: JDBC parameters and columns start at 1.
Committing after a partial failure instead of rolling back.
Hard-coding passwords in source control.

What's next#

Juggling JARs and classpaths by hand, as we did with the driver, doesn't scale. Next, Maven and Gradle manage dependencies, builds and tests for you.

How JDBC fits together#

Step 1: create a database and user#

Step 2: add the MySQL driver#

Step 3: connect#

Step 4: create a table and run statements#

PreparedStatement: parameters done right#

Reading a ResultSet

Java ↔ MySQL types

SQL injection: why concatenation is dangerous#

Transactions#

Batch inserts#

Connection pools#

Beyond plain JDBC#

Common mistakes#

What's next#

Quick quiz

`PreparedStatement`: parameters done right#

Reading a `ResultSet`