JDBC Explained: Drivers, Statements and Best Practices

The first time I had to save data from a Java program into a database, I was sure there would be a different library for every database. One for MySQL, one for Oracle, one for PostgreSQL, each with its own commands to learn.

It turns out Java solved this problem a long time ago, with one standard called JDBC. You learn it once, and the same code works with almost any relational database. You mostly just swap one small jar file and one line of text.

In this post, we'll learn what JDBC is, what a JDBC driver does (and which one to use for each database), the steps of every JDBC program, a complete working example with an explanation of each line, the different execute methods, the three kinds of statements and when to use each, and the best practices that keep your code safe and fast. The example uses an in-memory database called H2, so you don't need to install anything to try it.

Table of contents

• What is JDBC?
  • The problem JDBC solves
  • The main pieces of JDBC
• JDBC drivers
  • The four driver types
  • Drivers for popular databases
  • Adding a driver with Maven
  • Anatomy of a JDBC URL
• Steps for working with JDBC
• Your first JDBC program
  • The complete code
  • Explaining the code
  • What happens behind the scenes
• Types of execute methods
  • executeQuery
  • executeUpdate
  • execute
  • executeBatch
• Types of statements
  • Statement
  • PreparedStatement
  • CallableStatement
  • SQL injection and why it matters
  • Choosing the right statement
• Working with ResultSet
• Transactions
• Best practices
  • Close resources with try-with-resources
  • Use PreparedStatement for every query with input
  • Use a connection pool
  • Handle SQLException properly
  • Keep credentials out of your code
  • Other good habits
• Which one should I use?
• Common mistakes
• Quick recap
• Try it yourself

What is JDBC?

JDBC stands for Java Database Connectivity. It's a standard API (a set of classes and interfaces) that lets a Java program connect to a relational database, send SQL commands and read the results. It's part of Java itself, in the packages java.sql and javax.sql.

The problem JDBC solves

Every database has its own way of talking over the network. MySQL speaks one protocol, Oracle another, PostgreSQL a third. Without a standard, you'd have to learn each one and rewrite your code to switch databases.

JDBC fixes this with a middle layer. Your code talks only to JDBC's standard interfaces. A small library from the database vendor, called a driver, translates those calls into the database's own language.

   YOUR JAVA CODE
        |   uses standard interfaces: Connection, Statement, ResultSet
        v
   JDBC API   (java.sql and javax.sql, part of Java SE)
        |
        v
   JDBC DRIVER   (a jar file from the database vendor)
        |   speaks the database's own protocol over the network
        v
   DATABASE   (MySQL, PostgreSQL, Oracle, SQL Server, H2 ...)

Because your code depends only on the standard interfaces, switching databases usually means changing the driver jar and the connection URL. (Your SQL itself may still need small changes, since each database has its own dialect for some features.)

The main pieces of JDBC

You'll meet these names again and again:

• DriverManager: finds the right driver for a URL and opens a connection.
• DataSource: a more modern way to get connections. It's what connection pools implement, and it's preferred in real applications.
• Connection: your session with the database. You use it to create statements and to control transactions.
• Statement, PreparedStatement, CallableStatement: objects that send SQL to the database.
• ResultSet: the rows that come back from a query.
• SQLException: the exception thrown when anything goes wrong with the database.

All of these (except the driver itself) are interfaces. The driver provides the real classes behind them.

JDBC drivers

A JDBC driver is a library that implements the JDBC interfaces for one specific database. It knows how to open a network connection to that database, log in, send SQL and read the results. You add it to your project like any other library.

The four driver types

JDBC drivers are traditionally grouped into four types:

• Type 1, JDBC-ODBC bridge: used the old ODBC standard on your computer to reach a database. It was removed from Java in version 8, so you won't use it.
• Type 2, native-API driver: partly Java and partly native code, and it needs the database's client software installed on the machine. It's used in a few special cases (for example, Oracle's OCI driver).
• Type 3, network-protocol driver: talks to a middleware server, which then talks to the database. It's rare today.
• Type 4, thin driver (pure Java): written entirely in Java and talks to the database directly over the network. No extra software to install. This is the type you'll use almost every time, and all the drivers in the next section are Type 4.

Drivers for popular databases

Here are the common ones, with the Maven dependency (groupId:artifactId), the driver class name and an example URL:

• MySQL
  • Dependency: com.mysql:mysql-connector-j
  • Driver class: com.mysql.cj.jdbc.Driver
  • URL: jdbc:mysql://localhost:3306/school
• MariaDB
  • Dependency: org.mariadb.jdbc:mariadb-java-client
  • Driver class: org.mariadb.jdbc.Driver
  • URL: jdbc:mariadb://localhost:3306/school
• PostgreSQL
  • Dependency: org.postgresql:postgresql
  • Driver class: org.postgresql.Driver
  • URL: jdbc:postgresql://localhost:5432/school
• Oracle
  • Dependency: com.oracle.database.jdbc:ojdbc11 (use ojdbc8 for Java 8)
  • Driver class: oracle.jdbc.OracleDriver
  • URL: jdbc:oracle:thin:@//localhost:1521/XEPDB1
• SQL Server
  • Dependency: com.microsoft.sqlserver:mssql-jdbc
  • Driver class: com.microsoft.sqlserver.jdbc.SQLServerDriver
  • URL: jdbc:sqlserver://localhost:1433;databaseName=school
• H2 (a small, pure-Java database, great for learning and tests)
  • Dependency: com.h2database:h2
  • Driver class: org.h2.Driver
  • URL: jdbc:h2:mem:testdb (in memory) or jdbc:h2:~/school (a file)
• SQLite
  • Dependency: org.xerial:sqlite-jdbc
  • Driver class: org.sqlite.JDBC
  • URL: jdbc:sqlite:school.db

A few notes:

• The driver class names are only needed in old-style code (we'll see why in a moment). It's still useful to recognize them in error messages.
• Always check the vendor's documentation for the current version and options. For example, newer SQL Server drivers encrypt the connection by default, so local development often needs extra URL settings such as trustServerCertificate=true (use that only for development, never in production).

Adding a driver with Maven

If you use Maven (see my Maven post), add the dependency in your pom.xml. We'll use H2 for this post's examples:

<dependencies>
  <dependency>
    <groupId>com.h2database</groupId>
    <artifactId>h2</artifactId>
    <version>2.4.240</version>
    <scope>runtime</scope>
  </dependency>
</dependencies>

Two things to notice:

• Use the newest version you can find on Maven Central. 2.4.240 is just the version I used.
• The scope is runtime, because your code uses only the standard java.sql interfaces. The driver is needed to run the program, but not to compile it. That's the kind of "runtime" scope we saw in the Maven post.

For another database, swap in its dependency from the list above.

Anatomy of a JDBC URL

A JDBC URL tells DriverManager which database to reach and where it lives:

   jdbc:mysql://localhost:3306/school?useSSL=true
    |     |        |        |     |        |
    |     |        |        |     |        +-- optional settings (key=value)
    |     |        |        |     +----------- database (schema) name
    |     |        |        +----------------- port
    |     |        +-------------------------- host (the machine running the database)
    |     +----------------------------------- sub-protocol: which database/driver
    +----------------------------------------- always "jdbc"

The exact format changes slightly from database to database (see the list above), but the idea is always jdbc: + the database type + the location.

Steps for working with JDBC

Every JDBC program follows the same steps:

   1. Add the driver          put the driver jar on the classpath (Maven dependency)
          |
          v
   2. Register the driver     happens automatically in modern JDBC
          |
          v
   3. Open a Connection       DriverManager.getConnection(url, user, password)
          |
          v
   4. Create a statement      createStatement() or prepareStatement(sql)
          |
          v
   5. Execute the SQL         executeQuery(), executeUpdate() or execute()
          |
          v
   6. Process the results     loop over the ResultSet, or read the update count
          |
          v
   7. Handle exceptions       catch SQLException
          |
          v
   8. Close everything        ResultSet, then Statement, then Connection

A quick note on step 2. In older tutorials, you'll see this line before connecting:

Class.forName("com.mysql.cj.jdbc.Driver");

You don't need it anymore. Since JDBC 4.0 (Java 6), every driver jar announces itself in a file, and Java finds and registers it automatically as soon as it's on the classpath. Just add the jar, and DriverManager will find it.

Your first JDBC program

We'll build a program that connects to an in-memory H2 database, creates a table, inserts two rows and reads them back. You need nothing installed except the H2 dependency above.

The complete code

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

public class JdbcDemo {

    private static final String URL = "jdbc:h2:mem:testdb";
    private static final String USER = "sa";
    private static final String PASSWORD = "";

    public static void main(String[] args) {
        try (Connection conn = DriverManager.getConnection(URL, USER, PASSWORD)) {

            createTable(conn);
            insertStudent(conn, 1, "Asha", 88);
            insertStudent(conn, 2, "Ravi", 72);
            printStudents(conn);

        } catch (SQLException e) {
            System.err.println("Database error: " + e.getMessage());
        }
    }

    private static void createTable(Connection conn) throws SQLException {
        String sql = "CREATE TABLE student (id INT PRIMARY KEY, name VARCHAR(50), marks INT)";
        try (Statement stmt = conn.createStatement()) {
            stmt.executeUpdate(sql);
        }
    }

    private static void insertStudent(Connection conn, int id, String name, int marks)
            throws SQLException {
        String sql = "INSERT INTO student (id, name, marks) VALUES (?, ?, ?)";
        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, id);
            ps.setString(2, name);
            ps.setInt(3, marks);
            int rows = ps.executeUpdate();
            System.out.println("Inserted " + rows + " row(s): " + name);
        }
    }

    private static void printStudents(Connection conn) throws SQLException {
        String sql = "SELECT id, name, marks FROM student ORDER BY id";
        try (Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {
            while (rs.next()) {
                int id = rs.getInt("id");
                String name = rs.getString("name");
                int marks = rs.getInt("marks");
                System.out.println(id + " | " + name + " | " + marks);
            }
        }
    }
}

Output:

Inserted 1 row(s): Asha
Inserted 1 row(s): Ravi
1 | Asha | 88
2 | Ravi | 72

Explaining the code

Let's go through it piece by piece.

The three constants. URL, USER and PASSWORD describe where the database is and how to log in. jdbc:h2:mem:testdb means "an H2 database that lives in memory, named testdb." sa is H2's default user, with an empty password. (For a real database, you'd never write the password in your code. More on that in the best practices.)

DriverManager.getConnection(URL, USER, PASSWORD). This asks Java to find a driver that understands the URL, then opens a connection to the database. It returns a Connection, your session with the database. It throws SQLException if the database can't be reached, the login fails or no driver matches.

try (Connection conn = ...). This is try-with-resources. Anything declared in the parentheses is closed automatically when the block ends, even if an exception happens. So we never need to write conn.close() ourselves. (An in-memory H2 database disappears when its last connection closes. Since we use one connection for the whole program, our data stays.)

conn.createStatement(). This creates a Statement object, a messenger that carries plain SQL text to the database.

stmt.executeUpdate(sql). This sends SQL that changes something (here, a CREATE TABLE). It returns the number of rows affected, which is 0 for this kind of command.

conn.prepareStatement(sql) with the ? marks. This creates a PreparedStatement. The ? characters are placeholders for values we'll supply later. Then we fill them in with setInt and setString. The numbers start at 1, not 0: 1 is the first ?, 2 is the second, and so on. executeUpdate() sends the command and returns how many rows were inserted (1).

stmt.executeQuery(sql) and ResultSet. This sends a SELECT and returns a ResultSet, the rows that came back. The loop while (rs.next()) moves to the next row each time, and returns false when there are no more rows. Inside the loop, rs.getInt("id") and rs.getString("name") read the columns of the current row by name.

catch (SQLException e). Almost every JDBC call can throw this checked exception, so the compiler makes us deal with it. Here we simply print the message.

The small helper methods. Putting each job in its own method keeps main short and makes each step easy to read and reuse.

What happens behind the scenes

When you call DriverManager.getConnection(...), a lot happens in a few milliseconds:

   getConnection("jdbc:h2:mem:testdb", "sa", "")
        |
        v
   DriverManager asks each registered driver: "Do you accept this URL?"
        |        (drivers register themselves when their jar is on the classpath)
        v
   The matching driver opens a network connection (TCP) to the database,
   sends the user name and password, and waits for the database to accept them
        |
        v
   You get back a Connection object  (a real class from the driver,
   used through the standard java.sql.Connection interface)

Then, when you run a query:

   stmt.executeQuery("SELECT ...")
        |  the driver sends the SQL text over the network
        v
   DATABASE: parses the SQL, plans how to run it, runs it
        |  sends the rows back (often in chunks)
        v
   ResultSet: a cursor that points at one row at a time; rs.next() moves it

Opening a connection is expensive: it needs a network round trip, a login and some setup. Sending SQL is much cheaper. That's why connection pools (covered in the best practices) are so important.

Types of execute methods

A statement can send its SQL in four main ways. The right one depends on what kind of SQL you're sending:

   METHOD           USE IT FOR                          RETURNS
   executeQuery     SELECT                              ResultSet
   executeUpdate    INSERT, UPDATE, DELETE, and DDL     int (rows affected)
   execute          unknown SQL, or several results     boolean (true = a ResultSet)
   executeBatch     many statements at once             int[] (one count for each)

(DDL means commands that change the structure, like CREATE TABLE.)

executeQuery

Use it for a SELECT. It returns a ResultSet and can never return an update count.

try (Statement stmt = conn.createStatement();
     ResultSet rs = stmt.executeQuery("SELECT name FROM student ORDER BY id")) {
    while (rs.next()) {
        System.out.println(rs.getString("name"));
    }
}

Asha
Ravi

If you pass something that isn't a query (like an INSERT) to executeQuery, you get an SQLException.

executeUpdate

Use it for INSERT, UPDATE, DELETE, and for DDL commands like CREATE TABLE. It returns an int: the number of rows affected.

try (Statement stmt = conn.createStatement()) {
    int rows = stmt.executeUpdate("UPDATE student SET marks = marks + 5 WHERE marks < 80");
    System.out.println("Rows updated: " + rows);
}

Rows updated: 1

Only Ravi (72) matched, so one row changed. For DDL, it returns 0. If you ever expect more than about two billion affected rows, there's also executeLargeUpdate, which returns a long.

execute

Use it when you don't know in advance what the SQL does, or when it might produce several results (some stored procedures do). It returns a boolean:

• true means the first result is a ResultSet. Get it with getResultSet().
• false means the first result is an update count. Get it with getUpdateCount().

static void run(Connection conn, String sql) throws SQLException {
    try (Statement stmt = conn.createStatement()) {
        boolean hasResultSet = stmt.execute(sql);

        if (hasResultSet) {
            try (ResultSet rs = stmt.getResultSet()) {
                int count = 0;
                while (rs.next()) {
                    count++;
                }
                System.out.println("Query returned " + count + " row(s)");
            }
        } else {
            System.out.println("Update count: " + stmt.getUpdateCount());
        }
    }
}

run(conn, "SELECT * FROM student");
run(conn, "DELETE FROM student WHERE id = 99");

Query returned 2 row(s)
Update count: 0

The same method handled both a query and an update, because execute told us which kind of result we got. In everyday code, executeQuery and executeUpdate are clearer, so reach for execute only when you need its flexibility.

executeBatch

Use it to send many statements in one trip to the database. Sending 1000 inserts one by one means 1000 network round trips. A batch sends them together, which is much faster. You add statements with addBatch(), then send them all with executeBatch():

String sql = "INSERT INTO student (id, name, marks) VALUES (?, ?, ?)";
try (PreparedStatement ps = conn.prepareStatement(sql)) {
    ps.setInt(1, 3);  ps.setString(2, "Meera"); ps.setInt(3, 95); ps.addBatch();
    ps.setInt(1, 4);  ps.setString(2, "Kabir"); ps.setInt(3, 64); ps.addBatch();
    ps.setInt(1, 5);  ps.setString(2, "Zoya");  ps.setInt(3, 81); ps.addBatch();

    int[] counts = ps.executeBatch();
    System.out.println(Arrays.toString(counts));
}

[1, 1, 1]

(This needs import java.util.Arrays;.) The returned array holds one count for each statement. Some drivers return the special value Statement.SUCCESS_NO_INFO (which is -2) instead, meaning "it worked, but I can't tell you how many rows." For very big batches, send them in chunks of a few hundred or thousand rows.

From here on, our table has five students: Asha, Ravi, Meera, Kabir and Zoya.

Types of statements

JDBC has three kinds of statement objects. They differ in how they build and send SQL.

   Statement            plain SQL text, no parameters
        ^
        |  extends
   PreparedStatement    SQL with ? placeholders, precompiled, safe with user input
        ^
        |  extends
   CallableStatement    calls stored procedures and functions

Statement

A Statement sends a complete SQL string exactly as you wrote it. It has no placeholders.

try (Statement stmt = conn.createStatement()) {
    stmt.executeUpdate("CREATE TABLE course (id INT PRIMARY KEY, title VARCHAR(50))");
}

• Good for: fixed SQL that has no input from users, like creating tables, running a fixed report or a one-off admin command.
• Watch out: every time you run it, the database has to read and plan the SQL again. And if you build the SQL by joining in user input, you open the door to SQL injection (next).

PreparedStatement

A PreparedStatement takes SQL with ? placeholders. The database compiles it once, and you supply the values separately with setInt, setString, setObject and similar methods.

String sql = "SELECT name, marks FROM student WHERE marks >= ?";
try (PreparedStatement ps = conn.prepareStatement(sql)) {
    ps.setInt(1, 80);
    try (ResultSet rs = ps.executeQuery()) {
        while (rs.next()) {
            System.out.println(rs.getString("name") + " " + rs.getInt("marks"));
        }
    }
}

Asha 88
Meera 95
Zoya 81

(Ravi is at 77 after our earlier update, and Kabir at 64, so neither matches.)

Why it's better for most work:

• Safe from SQL injection, because values are never mixed into the SQL text.
• Faster when repeated, since the database (or driver) can reuse the prepared plan.
• Handles types and special characters for you. A name like O'Brien just works, with no manual escaping.
• Supports batches easily, as we saw.

One limit: placeholders can only stand for values. You can't use ? for a table name, a column name or an SQL keyword.

CallableStatement

A CallableStatement calls a stored procedure or function, which is a piece of code saved inside the database itself. It uses a special call syntax that JDBC translates for each database:

try (CallableStatement cs = conn.prepareCall("{call get_student_count(?)}")) {
    cs.registerOutParameter(1, Types.INTEGER);
    cs.execute();
    int count = cs.getInt(1);
    System.out.println("Students: " + count);
}

This assumes your database already has a procedure named get_student_count with one output parameter. Notes:

• registerOutParameter declares a parameter that the procedure fills in (an OUT parameter). Parameters you send in are IN parameters, set with setXxx as usual. A parameter can also be both (INOUT).
• For a function that returns a value, the syntax is {? = call my_function(?)}.
• CallableStatement extends PreparedStatement, so everything you know about placeholders still applies.
• How you write procedures differs a lot between databases, but the Java side stays the same.

SQL injection and why it matters

SQL injection happens when user input becomes part of the SQL itself. Watch what happens with a nasty input:

String userInput = "x' OR '1'='1";

// UNSAFE: Statement with the input joined into the SQL
String unsafeSql = "SELECT * FROM student WHERE name = '" + userInput + "'";
System.out.println(unsafeSql);
try (Statement stmt = conn.createStatement();
     ResultSet rs = stmt.executeQuery(unsafeSql)) {
    int count = 0;
    while (rs.next()) {
        count++;
    }
    System.out.println("Statement returned " + count + " row(s)");
}

// SAFE: PreparedStatement with a placeholder
try (PreparedStatement ps = conn.prepareStatement("SELECT * FROM student WHERE name = ?")) {
    ps.setString(1, userInput);
    try (ResultSet rs = ps.executeQuery()) {
        int count = 0;
        while (rs.next()) {
            count++;
        }
        System.out.println("PreparedStatement returned " + count + " row(s)");
    }
}

SELECT * FROM student WHERE name = 'x' OR '1'='1'
Statement returned 5 row(s)
PreparedStatement returned 0 row(s)

The input was meant to be a name. But with the plain Statement, the quote characters changed the meaning of the SQL. '1'='1' is always true, so the query returned every row. In a login form, the same trick lets someone in without a password. With the PreparedStatement, the whole input is treated as one plain value to compare to a name, so nothing matched.

   Statement (joined text):      [ SQL + user input ]  ---->  database parses it ALL as SQL
   PreparedStatement:            [ SQL with ? ]  ---->  database parses and plans it
                                 [ value ]       ---->  value is handled as DATA only

Many drivers don't allow several commands in one string by default, so the ; DROP TABLE trick is often blocked. But data leaks and login bypasses still work, so don't count on that. The rule is simple: never build SQL by joining in user input.

Choosing the right statement

• Statement: fixed SQL with no outside input (DDL, a fixed report, quick scripts).
• PreparedStatement: the default choice. Use it for any SQL that has values, anything with user input, anything you run repeatedly and any batch.
• CallableStatement: when you need to call a stored procedure or function.

If you're ever unsure, use PreparedStatement. It's almost never the wrong answer.

Working with ResultSet

A ResultSet is like a cursor that starts before the first row. Each call to next() moves it down one row and returns true, or false when you run out:

   before first row
        |
        v   rs.next()  -->  row 1     (true)
        |   rs.next()  -->  row 2     (true)
        |   rs.next()  -->  row 3     (true)
        v   rs.next()  -->  after last (false)

So you must call next() before you read anything. If you skip it, you'll get an error like "before start of result set."

Reading columns:

• By name: rs.getString("name"). This is clearer and survives column reordering.
• By position: rs.getString(2). Column numbers start at 1.
• Pick the getter that matches the column: getInt, getLong, getDouble, getString, getBoolean, getBigDecimal (best for money), and so on.
• For dates and times, use the modern java.time types: rs.getObject("birth_date", LocalDate.class).

A subtle trap is NULL. getInt returns 0 for a NULL column, which is indistinguishable from a real zero. To keep a NULL as a NULL, read it as an object:

Integer marks = rs.getObject("marks", Integer.class);   // stays null if the column is NULL

Or call rs.wasNull() right after reading.

You can also inspect the shape of a result with metadata:

try (Statement stmt = conn.createStatement();
     ResultSet rs = stmt.executeQuery("SELECT id, name, marks FROM student")) {
    ResultSetMetaData meta = rs.getMetaData();
    for (int i = 1; i <= meta.getColumnCount(); i++) {
        System.out.println(meta.getColumnName(i) + " : " + meta.getColumnTypeName(i));
    }
}

ID : INTEGER
NAME : CHARACTER VARYING
MARKS : INTEGER

(The type names depend on the database. H2 also shows column names in uppercase.)

By default, a ResultSet is forward-only and read-only: you move down once, and you can't edit rows through it. You can ask for scrollable or updatable result sets with createStatement(type, concurrency), but that's rarely needed. A ResultSet is also tied to its statement: closing the statement closes the result set.

Transactions

A transaction is a group of SQL commands that must succeed together or not at all. The classic example is moving money: subtract from one account, add to another. If the first step works and the second fails, you must undo the first.

By default, JDBC runs in auto-commit mode: every statement is committed (made permanent) the moment it runs. For a multi-step job, turn it off and commit yourself:

conn.setAutoCommit(false);
try {
    debit(conn, 1, 500);     // UPDATE account SET balance = balance - 500 WHERE id = 1
    credit(conn, 2, 500);    // UPDATE account SET balance = balance + 500 WHERE id = 2
    conn.commit();           // both changes become permanent together
} catch (SQLException e) {
    conn.rollback();         // undo everything since the last commit
    throw e;
} finally {
    conn.setAutoCommit(true);
}

   setAutoCommit(false)
          |
     step 1 ---- step 2 ---- step 3
          |                    |
       all OK?              something failed?
          |                    |
       commit()            rollback()
     (permanent)         (as if nothing happened)

Transactions give you the ACID guarantees: Atomic (all or nothing), Consistent (rules stay true), Isolated (other users don't see half-finished work) and Durable (committed data survives a crash). If you forget commit() after turning off auto-commit, your changes are lost when the connection closes.

Best practices

Close resources with try-with-resources

Connection, Statement and ResultSet all hold real resources (network sockets, memory on the database server). If you forget to close them, they leak, and eventually the database runs out of connections. Always use try-with-resources, which closes everything for you in reverse order, even when exceptions happen:

try (Connection conn = dataSource.getConnection();
     PreparedStatement ps = conn.prepareStatement(sql);
     ResultSet rs = ps.executeQuery()) {
    // use rs
}   // closed automatically: rs, then ps, then conn

Avoid the old style with finally blocks, null checks and nested try/catch. It's long and easy to get wrong.

Use PreparedStatement for every query with input

Never build SQL by joining text with user input. Always use ? placeholders, and set the values with the right setXxx method. We saw why in the SQL injection section. It also gives you speed and correct handling of quotes and types for free.

Use a connection pool

Opening a new connection for every request is slow. A connection pool opens a fixed set of connections once and lends them out. When you call close() on a pooled connection, it isn't really closed. It goes back to the pool for the next user.

Popular pools include HikariCP. You create the pool once, when the application starts, and share it:

HikariConfig config = new HikariConfig();
config.setJdbcUrl(System.getenv("DB_URL"));
config.setUsername(System.getenv("DB_USER"));
config.setPassword(System.getenv("DB_PASSWORD"));
config.setMaximumPoolSize(10);

DataSource dataSource = new HikariDataSource(config);   // create once, reuse everywhere

try (Connection conn = dataSource.getConnection()) {    // borrowed from the pool
    // use conn
}                                                        // returned to the pool

Notice the code uses the standard DataSource interface. That's why frameworks and servers prefer it to DriverManager: you can swap in a pool without changing your database code.

Handle SQLException properly

Don't swallow it with an empty catch. SQLException carries more than a message:

try (PreparedStatement ps = conn.prepareStatement("INSERT INTO student VALUES (?, ?, ?)")) {
    ps.setInt(1, 1);                  // id 1 already exists
    ps.setString(2, "Duplicate");
    ps.setInt(3, 50);
    ps.executeUpdate();
} catch (SQLException e) {
    System.err.println("Message : " + e.getMessage());
    System.err.println("SQLState: " + e.getSQLState());
    System.err.println("Code    : " + e.getErrorCode());
}

Message : Unique index or primary key violation: ...
SQLState: 23505
Code    : 23505

(H2 shown. Other databases word the message differently.)

• getSQLState() is a standard 5-character code. 23505 means a duplicate key, and the first two digits show the category (23 is "integrity constraint violation"). This is the safest value to write logic against.
• getErrorCode() is a number specific to the database vendor.
• Log the full exception for developers, but show users a friendly message. Raw database errors can reveal table names and other details an attacker could use.

Keep credentials out of your code

Never hard-code passwords in source files, and never commit them to Git. Read them from environment variables, a config file outside the repository or a secrets manager:

String url = System.getenv("DB_URL");
String user = System.getenv("DB_USER");
String password = System.getenv("DB_PASSWORD");

Also give the application's database user only the permissions it needs (for example, no permission to drop tables), and use an encrypted connection (TLS) when the database is on another machine.

Other good habits

• Select only what you need. Name the columns instead of SELECT *, and add WHERE and limits, so you don't pull huge amounts of data.
• Use batches for bulk inserts and updates, in chunks.
• Use transactions for anything that must succeed as a unit, and keep them short.
• Use java.time types (LocalDate, LocalDateTime) with setObject and getObject, instead of the older java.util.Date and java.sql.Date.
• Separate database code from business logic. Put SQL in its own classes (often called DAOs or repositories), so the rest of the program doesn't depend on JDBC details.
• Set timeouts. stmt.setQueryTimeout(30) stops a stuck query from hanging forever.
• Don't use Class.forName for modern drivers. Just keep the driver jar on the classpath.
• Use column names in ResultSet reads for clarity, unless performance on huge result sets proves otherwise.

Which one should I use?

• Statement or PreparedStatement? PreparedStatement whenever there's any input value or any repetition. Statement only for fixed, input-free SQL like DDL.
• CallableStatement? Only to call stored procedures or functions.
• executeQuery, executeUpdate or execute? executeQuery for SELECT, executeUpdate for everything that changes data or structure, and execute only when the kind of result is unknown or there may be several. Use executeBatch for bulk work.
• DriverManager or DataSource? DriverManager is fine for learning and small scripts. Use a pooled DataSource in real applications.
• Get columns by name or by index? Name, for readability. Index is a little faster and fine for tiny queries.
• JDBC directly, or a framework? Plain JDBC is perfect for learning, small tools and cases where you want full control. For bigger applications, tools built on top of JDBC (like Spring's JdbcTemplate, jOOQ or JPA with Hibernate) remove repetitive code. They all use JDBC underneath, so understanding it makes every one of them easier.
• Auto-commit or manual transactions? Auto-commit for single statements. Manual commit and rollback whenever several statements belong together.

Common mistakes

1. "No suitable driver found." The driver jar isn't on the classpath, or the URL is wrong. Check the dependency (and its scope) and the exact URL format for your database.
2. Joining user input into SQL. "... WHERE name = '" + name + "'" is an SQL injection bug waiting to happen. Use PreparedStatement with ?.
3. Not closing resources. Leaked connections, statements and result sets slowly exhaust the database. Use try-with-resources.
4. Off-by-one and cursor mistakes. Parameter and column positions start at 1, and a ResultSet must be advanced with next() before you read it.
5. Using the wrong execute method. Calling executeQuery with an INSERT, or executeUpdate with a SELECT, throws an SQLException. Match the method to the SQL, or use execute.
6. Transaction surprises. With auto-commit on, a half-finished multi-step job leaves half the changes saved. With auto-commit off, forgetting commit() throws all the changes away.

Quick recap

• JDBC is Java's standard API (java.sql and javax.sql) for talking to relational databases. Your code uses the interfaces, and a vendor's driver does the translation.
• Drivers come in four types, but you'll use Type 4 (pure Java): mysql-connector-j, postgresql, ojdbc11, mssql-jdbc, h2, sqlite-jdbc and others. Add the right one with Maven, usually in runtime scope.
• A JDBC URL looks like jdbc:<database>://host:port/name. Since JDBC 4, drivers register themselves, so Class.forName is no longer needed.
• The steps: add the driver, open a Connection, create a statement, execute the SQL, process the results, handle SQLException and close everything.
• Execute methods: executeQuery (SELECT, returns a ResultSet), executeUpdate (changes data, returns a row count), execute (unknown or multiple results, returns a boolean) and executeBatch (many statements at once).
• Statements: Statement (plain, fixed SQL), PreparedStatement (placeholders, safe, faster, the default) and CallableStatement (stored procedures).
• Joining user input into SQL causes SQL injection. PreparedStatement prevents it.
• A ResultSet is a cursor: call next() first, and columns start at 1. Watch out for NULL values.
• Use transactions (setAutoCommit(false), commit, rollback) when several statements must succeed together.
• Best practices: try-with-resources, PreparedStatement for all input, a connection pool, careful SQLException handling, credentials outside the code and small, focused queries.

Try it yourself

Here's a small practice idea: build a StudentDao class with five methods: insert, findById, findAll, updateMarks and delete, all using PreparedStatement and try-with-resources, against the H2 table from this post. Then add a insertAll(List<Student>) method that uses a batch, and a method that moves marks from one student to another inside a transaction, rolling back if either update fails.

For a bonus experiment, switch to a real database. Install MySQL or PostgreSQL (or run one in Docker), swap the H2 dependency for the right driver, and change only the URL, user and password. The rest of your code should run unchanged, and that's the whole point of JDBC. Then put your connection settings in environment variables, and add a HikariCP pool.

If this helped, or if you spotted something I should fix, tell me in the comments. I'm still learning too.