JDBC and databases — Java

In this lesson you will learn

Connect to a database with JDBC, send queries and read a ResultSet
Prevent SQL injection with PreparedStatement
Manage transactions and explain the role of a connection pool and of JPA

When a program closes, every object in memory disappears. Students' grades, orders and user accounts, however, must be kept for years — they are stored in a database. In the SQL course you wrote queries by hand; now the Java program itself will connect to the database, send SQL and turn the results into objects. The foundation for this is JDBC.

Definition

JDBC

Java Database Connectivity — the standard API in the java.sql package. You write code against the Connection, PreparedStatement and ResultSet interfaces, and the driver of a particular database (PostgreSQL, MySQL, SQLite, H2) implements them. When you switch databases, usually only the driver and the connection string change.

Connecting and the first query

The driver is added to the project like any other dependency (in Maven usually with runtime scope). DriverManager.getConnection(url, user, password) opens a connection; the URL gives the type and address of the database. Statement has three main methods: execute runs any SQL, executeUpdate returns the number of changed rows, and executeQuery returns a result table — a ResultSet. rs.next() moves the cursor to the next row and returns false when the rows run out. It is important to close every resource with try-with-resources.

Database	Example URL	Driver (Maven)
PostgreSQL	`jdbc:postgresql://localhost:5432/school`	`org.postgresql:postgresql`
MySQL	`jdbc:mysql://localhost:3306/school`	`com.mysql:mysql-connector-j`
SQLite	`jdbc:sqlite:school.db`	`org.xerial:sqlite-jdbc`
H2 (in memory)	`jdbc:h2:mem:school`	`com.h2database:h2`

Java

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

public class Main {
    public static void main(String[] args) throws SQLException {
        String url = "jdbc:h2:mem:school";   // the H2 driver must be on the classpath
        try (Connection conn = DriverManager.getConnection(url, "sa", "");
             Statement st = conn.createStatement()) {
            st.execute("CREATE TABLE students (id INT AUTO_INCREMENT PRIMARY KEY, "
                    + "first_name VARCHAR(50), city VARCHAR(50), score INT)");
            int rows = st.executeUpdate("INSERT INTO students (first_name, city, score) VALUES "
                    + "('Aysel', 'Baku', 95), ('Murad', 'Ganja', 78), ('Leyla', 'Baku', 88)");
            System.out.println("Inserted rows: " + rows);

            try (ResultSet rs = st.executeQuery("SELECT first_name, score FROM students ORDER BY score DESC")) {
                while (rs.next()) {
                    System.out.println(rs.getString("first_name") + " " + rs.getInt("score"));
                }
            }
        }
    }
}

Expected output

Inserted rows: 3
Aysel 95
Leyla 88
Murad 78

Sample output with the H2 driver (without a driver the program fails with No suitable driver found)

PreparedStatement and SQL injection

Gluing a value from the user into an SQL string is one of the most dangerous mistakes. If someone types ' OR '1'='1 into a login field, the condition is always true and the query returns every user — this is SQL injection. A PreparedStatement prepares the SQL in advance with ? placeholders and sends the values separately: the database always treats them as data, never as commands. As a bonus, the same prepared query can be faster when it is reused.

Dangerous: string concatenation

String sql = "SELECT * FROM users WHERE login = '" + login + "'";
ResultSet rs = st.executeQuery(sql);
// login = ' OR '1'='1   ->   WHERE login = '' OR '1'='1'   ->   every user!

Safe: `?` parameters

String sql = "SELECT * FROM users WHERE login = ?";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setString(1, login);   // sent separately, always treated as data
ResultSet rs = ps.executeQuery();

Java

static List<String> findByCity(Connection conn, String city, int minScore) throws SQLException {
    String sql = "SELECT first_name FROM students WHERE city = ? AND score >= ? ORDER BY first_name";
    List<String> names = new ArrayList<>();
    try (PreparedStatement ps = conn.prepareStatement(sql)) {
        ps.setString(1, city);        // parameters are numbered from 1
        ps.setInt(2, minScore);
        try (ResultSet rs = ps.executeQuery()) {
            while (rs.next()) {
                names.add(rs.getString("first_name"));
            }
        }
    }
    return names;
}
// findByCity(conn, "Baku", 80)  ->  [Aysel, Leyla]

A typical read method: parameters, a ResultSet and collecting the result into a list

In a real program each row is usually turned into an object: new Student(rs.getLong("id"), rs.getString("first_name"), rs.getInt("score")). All the read and write methods for one table are gathered in one class — called a DAO (Data Access Object) or a repository. This way the SQL stays in one place, and the rest of the program works only with Student objects and does not know which database is behind them.

Transactions

Aysel transfers 50 manat to Murad's account: the money must leave one account and arrive in the other. If the server goes down after the first UPDATE, the money is “lost”. A transaction turns several operations into one indivisible unit: either all of them are saved (commit) or none of them (rollback). In JDBC a connection is in auto-commit mode by default — every statement is saved immediately. setAutoCommit(false) switches this off so you can control the transaction yourself.

Java

static void transfer(Connection conn, int fromId, int toId, int amount) throws SQLException {
    String sql = "UPDATE accounts SET balance = balance + ? WHERE id = ?";
    conn.setAutoCommit(false);                    // start a transaction
    try (PreparedStatement ps = conn.prepareStatement(sql)) {
        ps.setInt(1, -amount);
        ps.setInt(2, fromId);
        ps.executeUpdate();
        ps.setInt(1, amount);
        ps.setInt(2, toId);
        ps.executeUpdate();
        conn.commit();                            // both changes are saved together...
    } catch (SQLException e) {
        conn.rollback();                          // ...or neither of them
        throw e;
    } finally {
        conn.setAutoCommit(true);
    }
}

Both UPDATEs either succeed together or are undone together

Connection pools

Opening a new connection is expensive: the network, the password check, memory on the database side. A web app that does this for every request collapses under load. A connection pool opens several connections in advance and “lends” them out: getConnection() takes a ready connection, and close() does not close it but returns it to the pool. The most popular pool in Java is HikariCP; Spring Boot uses it by default.

Java

HikariConfig config = new HikariConfig();
config.setJdbcUrl("jdbc:postgresql://localhost:5432/school");
config.setUsername("school_app");
config.setPassword(System.getenv("DB_PASSWORD"));
config.setMaximumPoolSize(10);

try (HikariDataSource pool = new HikariDataSource(config)) {    // create once per application
    try (Connection conn = pool.getConnection()) {              // borrowed from the pool
        System.out.println(findByCity(conn, "Baku", 80));
    }                                                           // close() returns it to the pool
}

HikariCP (com.zaxxer:HikariCP); never hard-code the password — read it from an environment variable

JPA and Hibernate: objects without SQL

With JDBC you have to copy every column into an object by hand. ORM (Object-Relational Mapping) automates this: a class is mapped to a table, a field to a column and an object to a row. The ORM standard in Java is JPA (Jakarta Persistence, package jakarta.persistence), and its most popular implementation is Hibernate. You mark the class with @Entity; @Id marks the primary key and @Column sets the column name and constraints.

Java

import jakarta.persistence.*;

@Entity
@Table(name = "students")
public class Student {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(name = "first_name", nullable = false)
    private String firstName;
    private String city;
    private int score;

    protected Student() {}                        // JPA needs a no-argument constructor

    public Student(String firstName, String city, int score) {
        this.firstName = firstName;
        this.city = city;
        this.score = score;
    }

    public String getFirstName() { return firstName; }
    public int getScore() { return score; }
}

A table described as a Java class

Java

EntityManager em = emf.createEntityManager();

em.getTransaction().begin();
em.persist(new Student("Nigar", "Sumgait", 91));        // Hibernate generates the INSERT
em.getTransaction().commit();

Student first = em.find(Student.class, 1L);             // SELECT ... WHERE id = 1

List<Student> best = em.createQuery(
        "SELECT s FROM Student s WHERE s.score >= :min ORDER BY s.score DESC", Student.class)
    .setParameter("min", 90)
    .getResultList();

The EntityManager generates the SQL itself; queries are written in JPQL using class and field names

In JPQL you write the Student class instead of the students table and the firstName field instead of the first_name column. In the next lesson you will see that Spring Data JPA shortens this code even further: writing interface StudentRepository extends JpaRepository<Student, Long> is enough, and Spring generates save, findById and findAll itself. Under the ORM, however, JDBC is still at work, so knowing JDBC helps you understand problems.

Key points

JDBC: Connection → PreparedStatement → ResultSet; the driver is chosen by the URL, and resources are closed with try-with-resources.
Never glue user input into SQL; use ? parameters, numbered from 1.
executeQuery returns a ResultSet, executeUpdate the number of changed rows; rs.next() moves the cursor forward.
A transaction: setAutoCommit(false) → commit() or rollback() — all or nothing.
A connection pool (HikariCP) reuses connections; JPA/Hibernate maps classes to tables with @Entity and @Id.

Check yourself

10 questions. Every correct answer earns XP.

1 / 10

What does executeUpdate return?