Java JDBC Basics
Java JDBC (Java Database Connectivity) is the standard API that lets a Java program talk to a relational database such as MySQL, PostgreSQL, Oracle or H2. You write the same Java code for every database, and a small database-specific library called a driver translates your calls into that database's own protocol.
Almost every Java application that stores data uses JDBC, either directly or through tools built on top of it such as JPA, Hibernate and Spring Data. In this lesson you will learn how JDBC is organised, connect to a database, create a table, insert and read rows, choose the right execute method, handle SQLException, and finish with a small inventory report.
What Is Java JDBC?
JDBC is a set of interfaces in the java.sql package, shipped with every JDK. The interfaces describe what you can do (connect, run SQL, read results). Each database vendor provides a driver JAR that contains the classes that actually do it.
This design gives your code two layers:
| Layer | Written by | Example |
|---|---|---|
JDBC API (java.sql) | The Java platform | Connection, Statement, ResultSet |
| JDBC driver | The database vendor | MySQL Connector/J, PostgreSQL JDBC, H2 |
| Database | The database vendor | MySQL server, PostgreSQL server, H2 engine |
Because your code depends only on the interfaces, switching from H2 to MySQL usually means changing the connection URL and the driver JAR, not your Java code.
Your Java code
│ calls java.sql interfaces
▼
DriverManager ── picks the driver that understands the URL
│
▼
JDBC driver (JAR) ── speaks the database's network protocol
│
▼
Database (MySQL, PostgreSQL, H2, ...)
Note: All examples in this tutorial use H2, a small database written in Java. It can run completely in memory, so you do not need to install a database server. Setting up the H2 and MySQL drivers is covered in Java JDBC Setup.
The Core JDBC Interfaces
You will use the same handful of types in every JDBC program. Each one has its own lesson later in this tutorial.
| Type | Package | Role |
|---|---|---|
DriverManager | java.sql | Finds a driver and opens a connection from a URL |
Connection | java.sql | An open session with the database |
Statement | java.sql | Runs a fixed SQL string |
PreparedStatement | java.sql | Runs SQL with ? parameters (safe and fast) |
ResultSet | java.sql | The rows returned by a query, read one row at a time |
SQLException | java.sql | The checked exception thrown when something goes wrong |
DataSource | javax.sql | A connection factory used by servers and connection pools |
JDBC Driver Types
You will see four "types" of drivers mentioned in older books. Today almost every driver is Type 4.
| Type | Name | How it works | Used today? |
|---|---|---|---|
| 1 | JDBC-ODBC bridge | Calls an ODBC driver | No (removed in Java 8) |
| 2 | Native-API | Calls a native C library | Rarely |
| 3 | Network protocol | Talks to a middleware server | Rarely |
| 4 | Thin / pure Java | Speaks the database protocol directly | Yes, the standard |
The JDBC Connection URL
A JDBC URL tells DriverManager which driver to use and where the database is. It always starts with jdbc:, followed by a sub-protocol that names the driver.
jdbc:<subprotocol>:<database-specific part>
| Database | Example URL |
|---|---|
| H2 (in memory) | jdbc:h2:mem:shop |
| H2 (file) | jdbc:h2:./data/shop |
| MySQL | jdbc:mysql://localhost:3306/shop |
| PostgreSQL | jdbc:postgresql://localhost:5432/shop |
| SQLite | jdbc:sqlite:shop.db |
The full list of URL formats and options is in Java Database Connection.
The Steps of Every JDBC Program
Every JDBC program follows the same five steps:
- Open a connection with
DriverManager.getConnection(url, user, password). - Create a statement from the connection.
- Execute SQL with the statement.
- Process the results (read a
ResultSetor an update count). - Close the result set, statement and connection, best done with try-with-resources.
Note: Old tutorials start with
Class.forName("com.mysql.jdbc.Driver"). Since JDBC 4.0 (Java 6) drivers register themselves automatically when their JAR is on the classpath, so this line is no longer needed.
Your First JDBC Program: Connect to a Database
This program opens a connection to an in-memory H2 database and prints information about it. It needs the H2 driver JAR on the classpath.
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.SQLException;
public class FirstConnection {
public static void main(String[] args) {
String url = "jdbc:h2:mem:shop"; // in-memory database named "shop"
String user = "sa";
String password = "";
// try-with-resources closes the connection automatically
try (Connection connection = DriverManager.getConnection(url, user, password)) {
DatabaseMetaData meta = connection.getMetaData();
System.out.println("Connected!");
System.out.println("Database: " + meta.getDatabaseProductName());
System.out.println("Driver: " + meta.getDriverName());
System.out.println("URL: " + meta.getURL());
System.out.println("Open? " + !connection.isClosed());
} catch (SQLException e) {
System.out.println("Could not connect: " + e.getMessage());
}
}
}
Output:
Connected!
Database: H2
Driver: H2 JDBC Driver
URL: jdbc:h2:mem:shop
Open? true
How it works:
DriverManager.getConnection()asks every registered driver whether it understands the URL. The H2 driver acceptsjdbc:h2:...and returns aConnection.getMetaData()returns aDatabaseMetaDataobject that describes the database and the driver.- The connection is declared inside
try (...), so Java closes it at the end of the block, even if an exception is thrown. SQLExceptionis a checked exception, so the code must catch it or declare it withthrows.
Create a Table and Insert Rows with JDBC
To change the database you use Statement.executeUpdate(). It runs CREATE, INSERT, UPDATE and DELETE statements and returns the number of rows affected.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class CreateAndInsert {
public static void main(String[] args) throws SQLException {
try (Connection connection = DriverManager.getConnection("jdbc:h2:mem:shop", "sa", "");
Statement statement = connection.createStatement()) {
int created = statement.executeUpdate("""
CREATE TABLE products (
id INT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
price DECIMAL(10, 2) NOT NULL,
stock INT NOT NULL
)""");
System.out.println("CREATE TABLE returned: " + created);
int inserted = statement.executeUpdate("""
INSERT INTO products (id, name, price, stock) VALUES
(1, 'Laptop', 58999.00, 12),
(2, 'Mouse', 499.00, 150),
(3, 'Keyboard', 1299.00, 0)""");
System.out.println("Rows inserted: " + inserted);
int updated = statement.executeUpdate("UPDATE products SET stock = stock + 20 WHERE id = 3");
System.out.println("Rows updated: " + updated);
}
}
}
Output:
CREATE TABLE returned: 0
Rows inserted: 3
Rows updated: 1
How it works:
connection.createStatement()creates aStatement, the simplest way to send SQL to the database.CREATE TABLEchanges no rows, soexecuteUpdate()returns0.- One
INSERTwith three value lists adds three rows, so it returns3. - The
UPDATEmatches one row (id = 3), so it returns1. - Java text blocks (
""") keep longer SQL readable. You can also use normal strings.
Read Rows with executeQuery() and ResultSet
To read data you use executeQuery(), which returns a ResultSet. A ResultSet works like a cursor: it starts before the first row, and each call to next() moves to the next row and returns false when there are no more rows.
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class ReadRows {
public static void main(String[] args) throws SQLException {
try (Connection connection = DriverManager.getConnection("jdbc:h2:mem:shop", "sa", "");
Statement statement = connection.createStatement()) {
statement.executeUpdate("CREATE TABLE products (id INT PRIMARY KEY, name VARCHAR(100), price DECIMAL(10, 2))");
statement.executeUpdate("INSERT INTO products VALUES (1, 'Laptop', 58999.00), (2, 'Mouse', 499.00), (3, 'Keyboard', 1299.00)");
try (ResultSet rows = statement.executeQuery("SELECT id, name, price FROM products ORDER BY price")) {
while (rows.next()) { // move to the next row
int id = rows.getInt("id"); // read by column name
String name = rows.getString("name");
BigDecimal price = rows.getBigDecimal("price");
System.out.println(id + " | " + name + " | " + price);
}
}
}
}
}
Output:
2 | Mouse | 499.00
3 | Keyboard | 1299.00
1 | Laptop | 58999.00
How it works:
executeQuery()runs theSELECTand returns the result rows.- The
while (rows.next())loop visits each row once. Inside the loop, the getter methods read columns from the current row. - Columns can be read by name (
"price") or by position (1,2,3, starting at 1, not 0). - Money is read with
getBigDecimal(), which keeps exact decimal values.getDouble()could introduce rounding errors.
Mapping SQL Types to Java Types
Choose the getter that matches the column's SQL type:
| SQL type | ResultSet getter | Java type |
|---|---|---|
INT, INTEGER | getInt() | int |
BIGINT | getLong() | long |
DECIMAL, NUMERIC | getBigDecimal() | BigDecimal |
DOUBLE, FLOAT | getDouble() | double |
VARCHAR, CHAR, TEXT | getString() | String |
BOOLEAN | getBoolean() | boolean |
DATE | getObject(col, LocalDate.class) | LocalDate |
TIMESTAMP | getObject(col, LocalDateTime.class) | LocalDateTime |
Note:
getInt()returns0for an SQLNULL. Callrows.wasNull()right after the getter, or usegetObject(col, Integer.class), when a column can beNULL.
executeQuery vs executeUpdate vs execute
A Statement has three main ways to run SQL. Picking the right one makes your intention clear and avoids exceptions.
| Method | Use for | Returns |
|---|---|---|
executeQuery(sql) | SELECT | ResultSet |
executeUpdate(sql) | INSERT, UPDATE, DELETE, CREATE, DROP | int (rows affected) |
execute(sql) | SQL whose type you do not know in advance | boolean (true if a ResultSet is available) |
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class ExecuteMethods {
public static void main(String[] args) throws SQLException {
try (Connection connection = DriverManager.getConnection("jdbc:h2:mem:shop", "sa", "");
Statement statement = connection.createStatement()) {
statement.executeUpdate("CREATE TABLE tags (name VARCHAR(30))");
String[] commands = {
"INSERT INTO tags VALUES ('java'), ('sql'), ('jdbc')",
"SELECT COUNT(*) FROM tags",
"DELETE FROM tags WHERE name = 'sql'"
};
for (String sql : commands) {
boolean hasResultSet = statement.execute(sql);
if (hasResultSet) {
try (ResultSet rows = statement.getResultSet()) {
rows.next();
System.out.println("Query result: " + rows.getInt(1));
}
} else {
System.out.println("Rows changed: " + statement.getUpdateCount());
}
}
}
}
}
Output:
Rows changed: 3
Query result: 3
Rows changed: 1
How it works:
execute()returnstrueonly for theSELECT, because that is the only command that produces rows.- When it returns
true,getResultSet()gives you the rows. When it returnsfalse,getUpdateCount()gives you the number of changed rows. rows.getInt(1)reads the first column by position, which is handy for unnamed columns such asCOUNT(*).
The Java JDBC Statement lesson covers these methods in more depth.
Handling SQLException in JDBC
Every JDBC method can throw SQLException. Besides the message, it carries two useful codes:
| Method | Meaning |
|---|---|
getMessage() | A human-readable description |
getSQLState() | A standard five-character code; the first two characters are the class (08 = connection error, 42 = syntax or access error, 23 = constraint violation) |
getErrorCode() | The vendor's own error number |
getNextException() | The next exception in the chain, if the driver reported several |
This example uses a URL that no driver understands, so it runs even without any driver JAR.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class HandleSqlException {
public static void main(String[] args) {
String badUrl = "jdbc:unknowndb://localhost/shop";
try (Connection connection = DriverManager.getConnection(badUrl, "app", "secret")) {
System.out.println("Connected");
} catch (SQLException e) {
System.out.println("Message: " + e.getMessage());
System.out.println("SQLState: " + e.getSQLState());
System.out.println("Is a connection error? " + e.getSQLState().startsWith("08"));
}
}
}
Output:
Message: No suitable driver found for jdbc:unknowndb://localhost/shop
SQLState: 08001
Is a connection error? true
How it works:
DriverManagerasks each registered driver about the URL. None acceptsjdbc:unknowndb:, so it throwsSQLExceptionwith the message "No suitable driver found".- SQLState
08001means "unable to establish a connection". The08class tells you the problem is the connection, not your SQL. - You see this same error when the driver JAR is missing from the classpath, which is the most common first problem with JDBC.
Note: Never show raw
SQLExceptionmessages to end users. They can reveal table names and server details. Log them, and show a friendly message instead.
Closing JDBC Resources with try-with-resources
Connection, Statement and ResultSet all hold resources on the database server and in the driver. All three implement AutoCloseable, so the cleanest way to close them is try-with-resources. Resources are closed in the reverse order of their declaration.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class CloseResources {
public static void main(String[] args) throws SQLException {
Connection keptConnection;
Statement keptStatement;
try (Connection connection = DriverManager.getConnection("jdbc:h2:mem:shop", "sa", "");
Statement statement = connection.createStatement();
ResultSet rows = statement.executeQuery("SELECT 1 + 1")) {
rows.next();
System.out.println("1 + 1 = " + rows.getInt(1));
keptConnection = connection; // keep references only to inspect them later
keptStatement = statement;
}
System.out.println("Statement closed? " + keptStatement.isClosed());
System.out.println("Connection closed? " + keptConnection.isClosed());
}
}
Output:
1 + 1 = 2
Statement closed? true
Connection closed? true
How it works:
- The three resources are declared in one
try (...)header, separated by semicolons. - At the end of the block Java closes
rows, thenstatement, thenconnection. isClosed()confirms both the statement and the connection were closed automatically. Forgetting to close connections is a classic cause of "too many connections" errors in production.
Real-World Example: A Product Inventory Report
This program puts all the basics together. It creates an inventory table, loads sample data, runs a summary query and prints a formatted stock report that flags products that need reordering.
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class InventoryReport {
static final String URL = "jdbc:h2:mem:inventory";
static final int REORDER_LEVEL = 10;
static void createSchema(Statement statement) throws SQLException {
statement.executeUpdate("""
CREATE TABLE products (
id INT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
category VARCHAR(50) NOT NULL,
price DECIMAL(10, 2) NOT NULL,
stock INT NOT NULL
)""");
}
static int loadSampleData(Statement statement) throws SQLException {
return statement.executeUpdate("""
INSERT INTO products VALUES
(1, 'Laptop', 'Electronics', 58999.00, 12),
(2, 'Mouse', 'Electronics', 499.00, 150),
(3, 'Keyboard', 'Electronics', 1299.00, 4),
(4, 'Notebook', 'Stationery', 60.00, 300),
(5, 'Pen Pack', 'Stationery', 120.00, 8)""");
}
static void printStockReport(Statement statement) throws SQLException {
String sql = "SELECT name, category, price, stock FROM products ORDER BY category, name";
System.out.printf("%-10s %-12s %10s %6s %s%n", "Product", "Category", "Price", "Stock", "Status");
try (ResultSet rows = statement.executeQuery(sql)) {
while (rows.next()) {
int stock = rows.getInt("stock");
String status = stock < REORDER_LEVEL ? "REORDER" : "OK";
System.out.printf("%-10s %-12s %10.2f %6d %s%n",
rows.getString("name"), rows.getString("category"),
rows.getBigDecimal("price"), stock, status);
}
}
}
static void printTotalValue(Statement statement) throws SQLException {
try (ResultSet rows = statement.executeQuery("SELECT SUM(price * stock) FROM products")) {
rows.next();
BigDecimal total = rows.getBigDecimal(1);
System.out.printf("Total stock value: %.2f%n", total);
}
}
public static void main(String[] args) {
try (Connection connection = DriverManager.getConnection(URL, "sa", "");
Statement statement = connection.createStatement()) {
createSchema(statement);
System.out.println("Loaded " + loadSampleData(statement) + " products\n");
printStockReport(statement);
System.out.println();
printTotalValue(statement);
} catch (SQLException e) {
System.out.println("Database error (" + e.getSQLState() + "): " + e.getMessage());
}
}
}
Output:
Loaded 5 products
Product Category Price Stock Status
Keyboard Electronics 1299.00 4 REORDER
Laptop Electronics 58999.00 12 OK
Mouse Electronics 499.00 150 OK
Notebook Stationery 60.00 300 OK
Pen Pack Stationery 120.00 8 REORDER
Total stock value: 806994.00
How it works:
- One connection, one statement.
main()opens the connection and aStatementonce and passes the statement to small helper methods. Each method does one job. - Schema and data.
createSchema()usesexecuteUpdate()forCREATE TABLE, andloadSampleData()returns the insert count, whichmain()prints. - Reading rows.
printStockReport()loops over theResultSet, reads each column with the matching getter and decides the status in Java using theREORDER_LEVELconstant. - Letting the database calculate.
printTotalValue()asks the database forSUM(price * stock)instead of adding the numbers in Java. Databases are built for this kind of work, and only one number travels back. - Exact money values. Prices and the total are
BigDecimal, so 58999.00 × 12 + ... is exact. - One error handler. Every helper declares
throws SQLException, andmain()catches it in one place, printing the SQLState for easier debugging.
The next lesson, Java JDBC Setup, shows how to add the H2 and MySQL drivers to a project so you can run these programs yourself.