← Java Course17 / 21

Database Access with JDBC

Connect to a database, run queries safely with PreparedStatement, read results, and manage transactions using core JDBC.

Ad 728×90

What JDBC is

Why: JDBC (Java Database Connectivity) is the standard, built-in API for talking to relational databases. You add a driver for your database (PostgreSQL, MySQL, SQLite…) as a dependency; the JDBC code stays the same regardless of which one.

Add a driver to your build — for example the SQLite driver in Maven (pom.xml):

Connecting

Why: DriverManager.getConnection opens a connection from a URL that names the database. Open it in a try-with-resources so it always closes. The URL format depends on the database — here a local SQLite file.

import java.sql.Connection;
import java.sql.DriverManager;

String url = "jdbc:sqlite:app.db";   // creates app.db if missing

try (Connection conn = DriverManager.getConnection(url)) {
    System.out.println("connected: " + !conn.isClosed());
}

Creating a table & inserting

Why: a Statement runs plain SQL like creating a table. executeUpdate is for statements that change data (CREATE, INSERT, UPDATE, DELETE) and returns how many rows were affected.

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

try (Connection conn = DriverManager.getConnection("jdbc:sqlite:app.db");
     Statement stmt = conn.createStatement()) {

    stmt.executeUpdate("CREATE TABLE IF NOT EXISTS users " +
                       "(id INTEGER PRIMARY KEY, name TEXT)");
    stmt.executeUpdate("INSERT INTO users(name) VALUES ('Ada')");
}

PreparedStatement (avoid SQL injection)

Why: never glue user input into SQL with + — that opens you to SQL injection attacks. A PreparedStatement uses ? placeholders and setString/setInt to bind values safely, and it is faster when reused.

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

String name = "Grace";   // imagine this came from a user

try (Connection conn = DriverManager.getConnection("jdbc:sqlite:app.db");
     PreparedStatement ps =
         conn.prepareStatement("INSERT INTO users(name) VALUES (?)")) {

    ps.setString(1, name);   // safely bound to the first ?
    ps.executeUpdate();
}

Reading rows with a ResultSet

Why: a SELECT returns a ResultSet — a cursor you advance with next(). Each loop reads one row; pull columns by name with getString, getInt, and so on.

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

try (Connection conn = DriverManager.getConnection("jdbc:sqlite:app.db");
     PreparedStatement ps = conn.prepareStatement("SELECT id, name FROM users");
     ResultSet rs = ps.executeQuery()) {

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

Transactions

Why: a transaction groups several changes so they all succeed or all fail together. Turn off auto-commit, do the work, then commit — or rollback if something throws, leaving the database untouched.

import java.sql.Connection;
import java.sql.DriverManager;

try (Connection conn = DriverManager.getConnection("jdbc:sqlite:app.db")) {
    conn.setAutoCommit(false);
    try {
        // ... several executeUpdate calls ...
        conn.commit();          // make them permanent together
    } catch (Exception e) {
        conn.rollback();        // undo everything on failure
    }
}