Java JDBC Introduction
In this page:
Connection connection = DriverManager.getConnection(url, user, password);
Statement statement = connection.createStatement();
ResultSet result = statement.executeQuery("SELECT * FROM table_name");JDBC Driver Registration
Before connecting to a database, a Java application needs the database's JDBC driver on its classpath. Modern JDBC (4.0+) loads the driver automatically via service discovery, but understanding the older Class.forName() manual-loading pattern still helps when maintaining legacy configurations that predate that mechanism.
Example: JDBC Driver Registration
public class Main {
public static void main(String[] args) {
try {
Class.forName("org.sqlite.JDBC"); // legacy manual-loading pattern
} catch (ClassNotFoundException e) {
System.out.println("Modern JDBC 4.0+ loads drivers automatically instead");
}
}
}Login to try C/C++/Java/PHP code in the editor
Creating a Database Connection
A Connection object represents an open session with the database, and obtaining one is typically the most expensive step in a database operation (network handshake, authentication, session setup). We can simulate connection-related operations cleanly using standard interfaces and dynamic proxy handlers when writing tests, avoiding the need for a real database.
Example: Creating a Database Connection
// Import java.lang.reflect.* so it can be used by its short name
import java.lang.reflect.*;
// Import java.sql.Connection so it can be used by its short name
import java.sql.Connection;
// Define the class Main
public class Main {
// Program entry point: the JVM starts running here
public static void main(String[] args) {
Connection fakeConnection = (Connection) Proxy.newProxyInstance(
Main.class.getClassLoader(),
new Class[]{Connection.class},
(proxy, method, methodArgs) -> {
if (method.getName().equals("isClosed")) return false;
// Send null back to the caller
return null;
}
);
// Print a line to the console
System.out.println("Simulated connection created without a real database");
}
}Login to try C/C++/Java/PHP code in the editor
Retrieving ResultSet
A ResultSet object represents a cursor over the tabular data returned by a query. You call its typed getter methods (getString, getInt, and so on) to extract each row's columns, advancing to the next row with next() until it returns false.
Example: Retrieving ResultSet
// Import java.lang.reflect.* so it can be used by its short name
import java.lang.reflect.*;
// Import java.sql.ResultSet so it can be used by its short name
import java.sql.ResultSet;
import java.util.*;
// Define the class Main
public class Main {
// Program entry point: the JVM starts running here
public static void main(String[] args) throws Exception {
Iterator<String> rows = List.of("Riya", "Aman").iterator();
ResultSet fakeResultSet = (ResultSet) Proxy.newProxyInstance(
Main.class.getClassLoader(),
new Class[]{ResultSet.class},
(proxy, method, methodArgs) -> switch (method.getName()) {
case "next" -> rows.hasNext();
case "getString" -> rows.next();
default -> null;
}
);
// Repeat while fakeResultSet.next() is true
while (fakeResultSet.next()) {
// Print a line to the console
System.out.println(fakeResultSet.getString("name"));
}
}
}Login to try C/C++/Java/PHP code in the editor
Executing SQL Statements
JDBC gives you two main ways to execute SQL: the basic Statement interface, and PreparedStatement, which precompiles the query and safely binds parameters. PreparedStatement is strongly preferred whenever user input is involved, because it separates SQL structure from data and prevents SQL injection.
Example: Executing SQL Statements
public class Main {
public static void main(String[] args) {
String userInput = "O'Reilly";
String unsafe = "SELECT * FROM books WHERE author = '" + userInput + "'"; // vulnerable to SQL injection
String safe = "SELECT * FROM books WHERE author = ?"; // PreparedStatement binds userInput separately
System.out.println(unsafe);
System.out.println(safe + " -- bound param: " + userInput);
}
}Login to try C/C++/Java/PHP code in the editor
Closing JDBC Resources
Connections, statements, and result sets all hold onto native database resources and must be explicitly closed when you're finished with them. Java's try-with-resources syntax closes them automatically -- even if an exception is thrown mid-operation -- which is the easiest way to avoid silently leaking open connections over time.
Example: Closing JDBC Resources
public class Main {
static class FakeResource implements AutoCloseable {
public void close() { System.out.println("Resource closed automatically"); }
}
public static void main(String[] args) {
try (FakeResource conn = new FakeResource(); FakeResource stmt = new FakeResource()) {
System.out.println("Using resources");
} // both closed automatically, even if an exception was thrown
}
}Login to try C/C++/Java/PHP code in the editor
- Reading from a
ResultSetwithout callingnext()first, since the cursor starts before the first row and this throws an exception. - Using column index
0, when JDBC column and parameter indexes start at1. - Not closing the
Connection,StatementandResultSet(for example with try-with-resources), which leaks database resources.
Chapter Quiz — Complete all 4 topics to unlock
0/4 topics done
Complete these topics first: