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.

Source：https://dev.to/mayur_bodhare/jdbc-explained-drivers-statements-and-best-practices-3hbh
My favorites

Related posts

SOLID Principles Explained: 5 Essential Principles for Writing Clean, Maintainable Code

SOLID principles are a set of five fundamental object-oriented design principles that help developers build software tha

0dev.to Tutorial

How to Build a Professional Resume in 2026: Best Resume Formats, Templates, and ATS Tips

Creating a professional resume is one of the most important steps when looking for a new job. However, many job seekers

0dev.to Career

How to Explain Your Project in a Technical Interview

"Walk me through a project you worked on" is one of the most common questions in technical interviews. It is also where

0dev.to Career

Stop Calling It "Refactoring" When You Are Just Moving The Bugs Around

Let's talk about the ultimate corporate tech delusion: the quarterly "code cleanup" or what senior developers love to pr

0dev.to Career

PostgreSQL migrations that lock a busy table

Which schema changes in PostgreSQL block reads or writes on a live table, why each one does, and the safer sequence of s

0dev.to Testing

🧠 Do Prompt ao RAG: como fazer um LLM responder sem inventar (e sem processo do cliente)

E aí, dev que já viu uma IA inventar uma política de reembolso que não existe? Ou pior: prometer um recurso que o produt

0dev.to Java