Java JDBC Basics

Su Suriya Ravichandran Updated 18 Nov 2026
13 min read ·Lesson 211 of 306

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:

LayerWritten byExample
JDBC API (java.sql)The Java platformConnection, Statement, ResultSet
JDBC driverThe database vendorMySQL Connector/J, PostgreSQL JDBC, H2
DatabaseThe database vendorMySQL 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.

TypePackageRole
DriverManagerjava.sqlFinds a driver and opens a connection from a URL
Connectionjava.sqlAn open session with the database
Statementjava.sqlRuns a fixed SQL string
PreparedStatementjava.sqlRuns SQL with ? parameters (safe and fast)
ResultSetjava.sqlThe rows returned by a query, read one row at a time
SQLExceptionjava.sqlThe checked exception thrown when something goes wrong
DataSourcejavax.sqlA 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.

TypeNameHow it worksUsed today?
1JDBC-ODBC bridgeCalls an ODBC driverNo (removed in Java 8)
2Native-APICalls a native C libraryRarely
3Network protocolTalks to a middleware serverRarely
4Thin / pure JavaSpeaks the database protocol directlyYes, 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>
DatabaseExample URL
H2 (in memory)jdbc:h2:mem:shop
H2 (file)jdbc:h2:./data/shop
MySQLjdbc:mysql://localhost:3306/shop
PostgreSQLjdbc:postgresql://localhost:5432/shop
SQLitejdbc: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:

  1. Open a connection with DriverManager.getConnection(url, user, password).
  2. Create a statement from the connection.
  3. Execute SQL with the statement.
  4. Process the results (read a ResultSet or an update count).
  5. 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 accepts jdbc:h2:... and returns a Connection.
  • getMetaData() returns a DatabaseMetaData object 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.
  • SQLException is a checked exception, so the code must catch it or declare it with throws.

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 a Statement, the simplest way to send SQL to the database.
  • CREATE TABLE changes no rows, so executeUpdate() returns 0.
  • One INSERT with three value lists adds three rows, so it returns 3.
  • The UPDATE matches one row (id = 3), so it returns 1.
  • 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 the SELECT and 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 typeResultSet getterJava type
INT, INTEGERgetInt()int
BIGINTgetLong()long
DECIMAL, NUMERICgetBigDecimal()BigDecimal
DOUBLE, FLOATgetDouble()double
VARCHAR, CHAR, TEXTgetString()String
BOOLEANgetBoolean()boolean
DATEgetObject(col, LocalDate.class)LocalDate
TIMESTAMPgetObject(col, LocalDateTime.class)LocalDateTime

Note: getInt() returns 0 for an SQL NULL. Call rows.wasNull() right after the getter, or use getObject(col, Integer.class), when a column can be NULL.


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.

MethodUse forReturns
executeQuery(sql)SELECTResultSet
executeUpdate(sql)INSERT, UPDATE, DELETE, CREATE, DROPint (rows affected)
execute(sql)SQL whose type you do not know in advanceboolean (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() returns true only for the SELECT, because that is the only command that produces rows.
  • When it returns true, getResultSet() gives you the rows. When it returns false, getUpdateCount() gives you the number of changed rows.
  • rows.getInt(1) reads the first column by position, which is handy for unnamed columns such as COUNT(*).

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:

MethodMeaning
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:

  • DriverManager asks each registered driver about the URL. None accepts jdbc:unknowndb:, so it throws SQLException with the message "No suitable driver found".
  • SQLState 08001 means "unable to establish a connection". The 08 class 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 SQLException messages 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, then statement, then connection.
  • 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:

  1. One connection, one statement. main() opens the connection and a Statement once and passes the statement to small helper methods. Each method does one job.
  2. Schema and data. createSchema() uses executeUpdate() for CREATE TABLE, and loadSampleData() returns the insert count, which main() prints.
  3. Reading rows. printStockReport() loops over the ResultSet, reads each column with the matching getter and decides the status in Java using the REORDER_LEVEL constant.
  4. Letting the database calculate. printTotalValue() asks the database for SUM(price * stock) instead of adding the numbers in Java. Databases are built for this kind of work, and only one number travels back.
  5. Exact money values. Prices and the total are BigDecimal, so 58999.00 × 12 + ... is exact.
  6. One error handler. Every helper declares throws SQLException, and main() 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.

0 Comments

Reviewed before they appear

No comments yet.

Java
Java Introduction Java Features Java Syntax Java Comments Java Statements Java Output Java Variables Java Data Types Java Type Casting Java Operators Java Strings Java Numbers Java Math Java Booleans Java User Input Java Conditions Java If Else Java Switch Java While Loop Java For Loop Java Break Java Continue Java Arrays Java Methods Java Parameters Java Return Values Java Scope Java Recursion Install Java Install JDK Java IDEs Java JDK Java JRE Java JVM Java Compiler Java File Compile Java Run Java JShell Basics Java Packages Java OOP Java Classes Java Objects Class Methods Constructors Constructor Overload this Keyword static Keyword final Keyword Encapsulation Inheritance Polymorphism Abstraction Access Modifiers super Keyword Method Overload Method Override Instanceof Operator Nested Classes Inner Classes Anonymous Classes Enums Records Array Basics Array Length Loop Arrays Multidimensional Arrays Array Copy Array Sorting Array Search ArrayList LinkedList HashSet TreeSet HashMap TreeMap Queue Interface Deque Interface Stack Class Iterator List Interface Set Interface Map Interface Collections Class Comparable Comparator Generics Basics Generic Classes Generic Methods Wildcards String Basics String Length String Methods String Concatenation String Compare String Search String Replace String Split String Format StringBuilder StringBuffer Text Blocks Regular Expressions Number Types Integer Class Float Class Double Class BigInteger BigDecimal Math Class Random Numbers Number Format Date Basics LocalDate LocalTime LocalDateTime ZonedDateTime Date Formatting Date Parsing Period Class Duration Class Java Instant Class Java Exception Basics Java try...catch Java Multiple Catch Blocks Java finally Block Java throw Keyword Java throws Keyword Java Checked Exceptions Java Unchecked Exceptions Java Custom Exceptions Java try-with-resources Java Exception Chaining Java File Basics Java Create Files Java Read Files Java Write Files Java Delete Files Java File Paths Java File Class Java BufferedReader Java BufferedWriter Java FileInputStream Java FileOutputStream Java Object Streams Java Directory Handling Java NIO Basics Java Files API Java Input Java Scanner Class Java Console Input Java PrintStream Java InputStream Java OutputStream Java Reader Class Java Writer Class Java Buffered Streams Java Data Streams Java Generics Introduction Java Generic Types Java Generic Methods Java Bounded Types Java Wildcards Java Type Erasure Java Generic Interfaces Java Generic Collections Java Lambda Basics Java Lambda Syntax Java Functional Interfaces Java Method References Java Stream API Java Stream Creation Java Stream Filter Java Stream Map Java Stream Reduce Java Stream Sort Java Stream Collect Java Collectors Class Java Optional Class Java Parallel Streams Java Threads: Thread Basics Create a Thread in Java Java Thread Class Java Runnable Interface Java Thread States Java Thread Sleep Java Thread Join Java Thread Priority Java Synchronization Java Thread Safety Java Race Conditions Java Deadlocks Java Executor Service Java Thread Pools Java Callable Interface Java Future Interface Java CompletableFuture Java Concurrent Collections Java Atomic Classes Java Virtual Threads Java Networking Basics Java URL Class Java URI Class Java HttpClient Java HTTP Requests Java HTTP Responses Java Socket Basics Java ServerSocket Java DatagramSocket Java WebSocket Basics Java JDBC Basics Java JDBC Setup Java Database Connection Java JDBC Connection Interface Java JDBC Statement Java PreparedStatement Java ResultSet Java JDBC CRUD Operations Java JDBC Transactions Java JDBC Batch Update Java Connection Pooling with HikariCP Java JPA Basics: Entities, EntityManager and JPQL Java Hibernate Basics: SessionFactory, Session and HQL Package in Java: Basics Explained with Examples Create Package in Java: Step-by-Step Guide with Examples Import Package in Java: The import Statement Explained Access Control in Java: Packages, Classes and Members JAR File in Java: Create, Run and Inspect JAR Files Java Modules: Introduction to the Java Module System module-info in Java: Every Module Directive Explained Java Module Path: How --module-path Finds Your Modules Java Classpath: How -cp and CLASSPATH Find Your Classes Java Annotations: Basics of @ Metadata in Java Java Built-in Annotations: @Override, @Deprecated and More Java Custom Annotations: Create Your Own @interface Java Annotation Targets: @Target and ElementType Explained Java Reflection API: Inspect and Use Classes at Runtime Java Class Metadata: Names, Modifiers, Hierarchy and Generic Java Dynamic Objects: Create Objects Dynamically With Reflec Java Dynamic Proxy: Intercept Method Calls With Proxy and In Java Security Java Secure Coding Java Password Hashing Java Input Validation Java Cryptography Basics Java KeyStore Basics Java TLS Basics Java SecureRandom Java JUnit Basics Java JUnit Test Methods JUnit Assertions JUnit Test Fixtures JUnit Parameterized Tests Mockito in Java Java Mock Objects Java Integration Testing Java Code Coverage Maven in Java Create a Maven Project in Java Maven Dependencies Maven Plugins Gradle in Java Gradle Build Gradle Dependencies Build Executable JAR in Java Build WAR File in Java HTTP Basics in Java Java Servlet Basics Servlet Lifecycle in Java HttpServletRequest in Java HttpServletResponse in Java JSP in Java JSTL in JSP REST API in Java JSON in Java Java Web Application Spring Framework Basics Spring Boot Tutorial Spring MVC Spring Data JPA Spring Security Spring Boot REST API Jakarta EE Basics Java Deployment JAR Deployment Java WAR Deployment Run Java as a Service Docker Java: Dockerize a Java Application Java Environment Variables and Configuration Java Logging Basics Java Production Setup Java JVM Options Java JVM Monitoring Java Hello World App Java Calculator App Java Number Guess Game Java Student Manager Java Library System Java Bank App Java Todo App Java File Manager Java Quiz App Java REST API Project Java Chat Server Java Employee Manager Java E-commerce API
Ask about this post
AI Ask about this post

Ask questions about Java JDBC Basics and get answers drawn from it.

Signed-in readers only.