PublishedBEG-055-L01

How Java talks to a database

Lesson 1 of 7
10 min read
Beginner

On This Lesson 11 topics

Lesson Overview
Learning Objectives
Written Explanation
Examples
Backend Insight
Common Mistakes
Exercises
Mini Challenge
Summary & Next Lesson
References
Practice

Lesson Overview

In the SQL modules you typed queries into psql and watched the answers appear. That works for you, but a real application cannot ask a person to type. Your Java program must send SQL to PostgreSQL by itself and read the answers back. The standard way to do that is JDBC.

We will use one picture through the whole module: a phone call to the librarian. You dial (connect), ask a question (statement), hear the answer (result), and hang up (close). This lesson draws the map of who is on the line. The next lessons make each step real.

This lesson builds on your SQL modules (BEG-050 and BEG-051), on interfaces (BEG-024), on exceptions and try-with-resources (BEG-029), and on Maven dependencies (BEG-054).

Learning Objectives

By the end of this lesson you will be able to:

Explain what JDBC is and why Java code uses it instead of talking to PostgreSQL directly.
Describe what a JDBC driver does and why one driver serves one database.
Name the three main types: Connection, PreparedStatement, and ResultSet.
Put the five-step flow in order: connect, prepare, execute, read, close.
Add the PostgreSQL driver to a Maven project.
Explain why java.sql types are interfaces and what that buys you.

Written Explanation

What JDBC is

JDBC (Java Database Connectivity) is the standard Java API for working with relational databases. It lets a Java program connect to a database, send SQL, and read the results. Oracle's Java tutorial describes it as covering connections, statements, result sets, and transactions, all in the java.sql package that ships with the JDK.

Notice what JDBC is not: it is not a database, and it does not know PostgreSQL's wire format. It is a set of types and method names. Someone still has to do the real talking.

The driver does the real talking

A driver is a library that implements the JDBC types for one particular database. It translates a call such as "run this SQL" into the network protocol that database understands. PostgreSQL has its own driver, the PostgreSQL JDBC Driver (often called pgjdbc). A MySQL driver speaks MySQL's protocol instead.

text

 Your Java code
      |   uses Connection, PreparedStatement, ResultSet   (JDBC API, java.sql)
      v
 PostgreSQL driver   (org.postgresql.Driver, a jar on your classpath)
      |   speaks PostgreSQL's network protocol
      v
 PostgreSQL server   (the library with the shelves)

The librarian analogy: JDBC is the shape of the conversation (dial, ask, listen, hang up). The driver is the phone line to one specific library. Change the library and you change the line, but you still dial, ask, listen, and hang up the same way.

The three types you will use all module

Type	Role	Analogy
`Connection`	An open session with the database	The phone call itself
`PreparedStatement`	One SQL command, with `?` slots for values	The question you ask
`ResultSet`	The rows the database sends back	The answer you hear

All three are interfaces in java.sql. Your code is written against the interfaces. The driver supplies the real classes behind them, so your code never names a PostgreSQL class (you will see that in Example 1). You met this idea in BEG-024: programming to an interface lets you swap the implementation.

The flow, in five steps

Almost every database call in Java follows the same order:

Connect: open a Connection (lesson 2).
Prepare: create a PreparedStatement from SQL text with ? placeholders (lesson 3).
Execute: run it. executeQuery() for a SELECT, executeUpdate() for an INSERT, UPDATE, or DELETE.
Read: walk the ResultSet row by row, or read the number of changed rows.
Close: release the ResultSet, statement, and connection. Try-with-resources from BEG-029 does this for you.

How the driver gets found

With a modern driver you do not have to register anything by hand. DriverManager, the JDK class that opens connections, loads every driver it finds on the classpath through Java's service-provider mechanism. Adding the driver jar is enough. The PostgreSQL JDBC documentation says that for Java 1.6 and later the driver loads automatically; explicit Class.forName("org.postgresql.Driver") lines in old tutorials are a leftover from older setups.

When you later call DriverManager.getConnection(url, ...), the URL starts with jdbc:postgresql: and tells DriverManager which driver should handle it.

Adding the driver with Maven

The driver is an ordinary library on Maven Central. You add it as a dependency, exactly as you learned in BEG-054:

xml

<dependency>
  <groupId>org.postgresql</groupId>
  <artifactId>postgresql</artifactId>
  <version>42.7.8</version>
</dependency>

The version above is the one used while writing this lesson. Check Maven Central for the current release when you start your own project, and keep the version in one place in your pom.xml.

Where JDBC sits in your career path

JDBC is the foundation under almost every Java persistence tool. Frameworks you will meet later (connection pools, Spring, JPA) call these same interfaces underneath. Learning raw JDBC now is like learning to drive a manual car: you will understand what the automatic one does for you. A later module in the roadmap, JNR-025, builds on this one with connection management and batching.

Examples

Example 1: what the driver adds (simple)

Create a Maven project with the dependency above and this class. It does not connect to anything yet; it only inspects the pieces.

java

package lib;

import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Collections;

public class DriverTour {
    public static void main(String[] args) {
        System.out.println("Connection is an interface: " + Connection.class.isInterface());
        System.out.println("PreparedStatement is an interface: " + PreparedStatement.class.isInterface());
        System.out.println("ResultSet is an interface: " + ResultSet.class.isInterface());
        for (Driver d : Collections.list(DriverManager.getDrivers())) {
            System.out.println("Registered driver: " + d.getClass().getName());
        }
    }
}

Run with the driver on the classpath:

text

Connection is an interface: true
PreparedStatement is an interface: true
ResultSet is an interface: true
Registered driver: org.postgresql.Driver

The three JDK types are interfaces. The one class that names PostgreSQL, org.postgresql.Driver, appeared by itself because the jar is on the classpath. Run the same program without the driver jar and the last line is missing: there is no phone line to any library.

Example 2: the five steps in order (practical)

This sketch is not meant to run yet (the connection details arrive in lesson 2). Read it as a map:

java

// 1. Connect
try (Connection conn = DriverManager.getConnection(url, user, password);
     // 2. Prepare
     PreparedStatement ps = conn.prepareStatement("SELECT title FROM books WHERE copies > ?")) {
    ps.setInt(1, 0);
    // 3. Execute
    try (ResultSet rs = ps.executeQuery()) {
        // 4. Read
        while (rs.next()) {
            System.out.println(rs.getString("title"));
        }
    }
} // 5. Close: all three resources are closed here, in reverse order
catch (java.sql.SQLException e) {
    System.out.println("Database problem: " + e.getMessage());
}

Every JDBC method that talks to the database can throw SQLException, a checked exception (BEG-029), so the compiler forces you to handle or declare it.

Example 3: one program, one library, one change (realistic)

A team writes a BookReader class that uses only Connection, PreparedStatement, and ResultSet. In development they run PostgreSQL. A client later asks for a different database engine. What changes?

The driver dependency in pom.xml and the URL (for example jdbc:postgresql://... becomes another vendor's prefix).
The SQL may need small edits, because databases differ in details beyond the common core.
BookReader's Java code, written against the java.sql interfaces, mostly stays as it is.

This is the promise of JDBC: the Java side is portable. It is not a promise that SQL is identical everywhere, so test against the real database you deploy to.

Backend Insight

Almost every backend service you will write is a thin layer between HTTP requests and a database: a request arrives, your code runs a query, and the rows become a response. JDBC is that layer's bottom floor.

Two habits start here and pay off for years:

Keep database code in one place. A class that owns all SQL for books (a repository, which you will build in lesson 6) means the rest of the program never touches Connection directly.
Treat every call as something that can fail. A database is a separate program, often on another machine. The network can drop, the password can be wrong, a constraint can reject your row. That is why JDBC throws SQLException at every turn.

Common Mistakes

Mistake 1: Thinking JDBC is the database

JDBC is an API. Without a running PostgreSQL server and the driver jar, nothing happens. Both must be present.

Mistake 2: Forgetting the driver dependency

If the jar is missing from the classpath, getConnection throws SQLException: No suitable driver found for jdbc:postgresql://... (the message we got when we tried it). The fix is the Maven dependency, not more Java code.

Mistake 3: Importing the wrong `Connection`

Both java.sql.Connection and several other Connection types exist in libraries. Use java.sql.Connection. Your IDE's auto-import may suggest others; check the package line.

Mistake 4: Using PostgreSQL classes in application code

Writing org.postgresql.PGConnection everywhere ties your whole program to one vendor. Stay with the java.sql interfaces unless you have a feature that truly needs the vendor type.

Exercises

Level A: Foundation

In one sentence each, say what JDBC and a JDBC driver are, and which one is specific to a single database.
Put these steps in order: execute, close, connect, read the rows, prepare the statement.
Name the JDBC type that matches each item: (a) the open session; (b) the SQL with ? slots; (c) the rows that came back.

Level B: Application

Create a Maven project (hedge: your IDE may do this with a wizard or you may use mvn archetype:generate; either is fine). Add the PostgreSQL driver, compile, and run DriverTour from Example 1. Copy the last line it prints.
Remove the dependency from pom.xml and run again. What changed in the output, and what does that tell you?
A teammate says "our code does not depend on PostgreSQL, because we use java.sql." Which part of that is true and which part is not?

Level C: Challenge

Extend DriverTour to print, for each registered driver, its getMajorVersion() and getMinorVersion(). Say why these numbers belong to the driver, not to the database.
Draw the stack for a program that uses PostgreSQL, then redraw it for a program that has two drivers on the classpath (PostgreSQL and another). Explain how DriverManager can tell which driver should handle a given URL.

Show solutions

A1. JDBC is the standard Java API (in java.sql) for connecting to relational databases, sending SQL, and reading results. A driver implements that API for one database and translates calls into its protocol. The driver is specific to one database.

A2. Connect, prepare the statement, execute, read the rows, close.

A3. (a) Connection. (b) PreparedStatement. (c) ResultSet.

B1. The last line is Registered driver: org.postgresql.Driver. Steps depend on your tooling; with Maven, mvn compile and then running the class with the dependency jars on the classpath (or from the IDE) works.

B2. The "Registered driver" line disappears while the three "is an interface" lines remain. The interfaces come from the JDK, but the driver is a separate library: without it there is nothing that can speak PostgreSQL's protocol.

B3. True: the code uses only JDK interfaces, so the source code names no vendor class. Not true: at run time the program still needs the PostgreSQL driver and server, and the SQL text may use PostgreSQL-specific features. So it depends on PostgreSQL in practice, just not in its imports.

C1. Add inside the loop: System.out.println(d.getMajorVersion() + "." + d.getMinorVersion());. They describe the driver library version (with driver 42.7.8 the program printed 42.7); the PostgreSQL server has its own, separate version number. The exact numbers you see depend on the version you added.

C2. The stack is code, JDBC API, then one driver, then the server. With two drivers, both sit side by side under the API. DriverManager looks at the URL prefix (jdbc:postgresql: versus another vendor's) and uses the driver that accepts that URL.

Mini Challenge

Make a one-page "phone call map" for the Library application. Draw four boxes (your Java code, the JDBC API, the PostgreSQL driver, the PostgreSQL server) and label each arrow with what travels along it (for example "SQL text with ?" and "rows"). Under the picture, write the five JDBC steps and, next to each, the librarian-call step it matches (dial, ask, hear, hang up).

Check your work: your Java code box touches only java.sql types, and the driver is the only box that mentions PostgreSQL's protocol.

Summary & Next Lesson

Key takeaways

JDBC is Java's standard API for relational databases; it lives in java.sql.
A driver implements JDBC for one database. For PostgreSQL it comes from the org.postgresql:postgresql Maven dependency.
The core types are Connection, PreparedStatement, and ResultSet, all interfaces.
The flow is connect, prepare, execute, read, close. Try-with-resources handles the closing.
JDBC methods throw the checked SQLException, because a database is a separate system that can fail.

Next lesson

The next lesson, "Connecting: driver, URL, credentials from environment variables", opens a real Connection: it explains the URL, and shows how to keep the password out of your source code and out of Git.

Self-check

What does the JDBC driver do?
Which JDBC type represents the rows returned by a SELECT?
Why are Connection and ResultSet interfaces rather than classes?
Which Maven coordinates add the PostgreSQL driver?
Which kind of exception do JDBC methods throw, and why must you handle it?

Show answers

It translates JDBC calls into the protocol of one specific database.
ResultSet.
So that the JDK defines the shape and each driver supplies its own implementation; your code stays independent of the vendor.
Group org.postgresql, artifact postgresql (plus a version).
SQLException, a checked exception, so the compiler requires you to catch it or declare it. It signals database problems such as a bad connection or a rejected statement.

References

Oracle, JDBC Basics (Java Tutorials). It lists what JDBC covers: connections, statements, result sets, and transactions. The tutorial was written for an older JDK.
Oracle, DriverManager (Java SE 21 API). It documents that drivers are loaded through the service-provider mechanism and that the URL has the form jdbc:subprotocol:subname.
PostgreSQL JDBC Driver, Initializing the Driver. It gives the driver class name org.postgresql.Driver and says it loads automatically on modern Java versions.
Maven Central coordinates and the latest version number were not verified in this lesson; version 42.7.8 is the one the examples were run with, so check the current one yourself. The librarian analogy is this course's own teaching device.

Practice

Apply this lesson with the module's lab, exercises and quiz.

Lab and exercises Quiz

Marking a lesson complete is optional and you can undo it any time. Your progress stays in this browser.

Continue to next lesson