JDBC and MySQL

1JDBC is Java, a driver, and a database

JDBC is an API in java.sql. Your code talks to interfaces: Connection, Statement, ResultSet. A driver implements those interfaces for one database product.

MySQL Connector/J is that driver for MySQL. The desk does not speak MySQL's wire protocol; the driver does.

Figure. The application uses JDBC types. The driver talks to MySQL. Swap the driver, not the desk code, to change products — in principle.

How it works

1. JDBC is Java, a driver, and a databaseYour code uses java.sql. The driver is the MySQL-specific piece on the classpath.

2Put Connector/J on the classpath

Download MySQL Connector/J (a jar) and add it to the classpath the same way the packages lesson added a library. Modern Java loads the driver from that jar automatically.

The MySQL server must be running and must have a database, for example cinema. Creating that schema is SQL, not Java. This course does not ship a live password.

No diagram — the idea is carried by the prose or the Java listing.

3A Connection is a live session

DriverManager.getConnection(url, user, password) returns a Connection. The URL names the product, host, port and database: jdbc:mysql://localhost:3306/cinema

The password argument in listings is YOUR_PASSWORD. Read the real secret from the environment in any program that leaves your laptop.

Figure. DriverManager.getConnection reads jdbc:mysql://localhost:3306/cinema plus user cinema_app. The password in listings is YOUR_PASSWORD — a placeholder, not a secret to commit.

How it works

1. A Connection is a live sessionThe URL picks host and database. YOUR_PASSWORD is a placeholder, not a secret to commit.

URL, user, placeholder password

import java.sql.Connection;
import java.sql.DriverManager;
String url =
    "jdbc:mysql://localhost:3306/cinema";
String user = "cinema_app";
String password = "YOUR_PASSWORD";
Connection c = DriverManager.getConnection(
        url, user, password);

4A Statement runs SQL

connection.createStatement() gives a Statement. executeQuery runs SELECT and returns a ResultSet. executeUpdate runs INSERT, UPDATE or DELETE and returns how many rows changed.

A PreparedStatement with ? placeholders is the form that does not paste Meera's seat into the SQL string. Use it as soon as any part of the SQL comes from input.

Figure. PreparedStatement keeps ? in the SQL and binds A12 with setString. executeQuery then returns a ResultSet. Building SQL by joining input strings is the form this lesson refuses.

How it works

1. A Statement runs SQLStatement runs SQL. PreparedStatement binds values. Do not build SQL by joining input strings.

? is bound, not concatenated

var ps = c.prepareStatement(
    "SELECT seat, price FROM booking
     WHERE seat = ?");
ps.setString(1, "A12");
var rs = ps.executeQuery();

5ResultSet is a cursor over rows

A ResultSet starts before the first row. rs.next() moves to the next row and returns false when there are no more.

rs.getString("seat") and rs.getInt("price") read columns of the current row. Column names match the SELECT list (or aliases).

Figure. ResultSet starts before the first row. The first next() exposes A12 200, the second B7 200, the third returns false. getString("seat") and getInt("price") read the current row only.

next, then getters, until next is false

while (rs.next()) {
    String seat = rs.getString("seat");
    int price = rs.getInt("price");
    System.out.println(seat + " " + price);
}

6Close ResultSet, Statement, Connection

Each JDBC object holds a server resource. Close them. try-with-resources can list Connection, then Statement, then ResultSet — or let the Connection close close the children if you opened them in the same try.

Leaving a Connection open is how a student lab exhausts the database's connection limit.

Figure. ResultSet, Statement and Connection each hold a server resource. Open them in try (...) so all three close on the way out. A leaked Connection is how a lab exhausts the database limit.

How it works

1. Close ResultSet, Statement, ConnectionOpen JDBC objects in try (...). YOUR_PASSWORD stays a placeholder in the listing.

All three close on the way out

try (Connection c = DriverManager.getConnection(
        url, user, "YOUR_PASSWORD");
     var st = c.createStatement();
     var rs = st.executeQuery(
         "SELECT seat FROM booking")) {
    while (rs.next()) {
        System.out.println(rs.getString(1));
    }
}