Back to Study Material Java

Java Database Connectivity

Java Database Connectivity, or JDBC, is the standard API that lets Java programs talk to relational databases. It sits between your Java code and any SQL d...

Java Database Connectivity

What is JDBC and How It Works

Java Database Connectivity, or JDBC, is the standard API that lets Java programs talk to relational databases. It sits between your Java code and any SQL database — MySQL, PostgreSQL, Oracle, SQLite — providing a uniform set of interfaces so your application code stays largely the same regardless of which database engine runs underneath.

The JDBC architecture has two main layers. The JDBC API layer is what you write against: classes like Connection, Statement, PreparedStatement, ResultSet, and DriverManager. The JDBC Driver layer is vendor-supplied code that translates your API calls into the specific wire protocol each database expects. Drivers come in four types, with Type 4 (thin, pure-Java, direct-socket) being the most common in modern projects.

A basic JDBC workflow follows four steps. First, load the driver — in modern Java this happens automatically through the ServiceLoader mechanism when the JAR is on the classpath, so you rarely need Class.forName() anymore. Second, obtain a Connection via DriverManager.getConnection("jdbc:mysql://localhost:3306/mydb", user, pass). Third, create a Statement or PreparedStatement, execute your SQL, and iterate over the ResultSet. Fourth, close every resource — ResultSet, Statement, Connection — ideally with try-with-resources so the compiler guarantees cleanup even if an exception fires.

PreparedStatement is almost always preferable to plain Statement for queries that accept user input. It separates SQL structure from data values, preventing SQL injection attacks and also allowing the database to cache the query execution plan for better performance. Example: PreparedStatement ps = conn.prepareStatement("SELECT * FROM users WHERE email = ?"); ps.setString(1, email);

Connection pooling is critical in production. Opening a raw JDBC connection is expensive — it involves a TCP handshake, authentication, and session setup. Libraries like HikariCP or Apache DBCP maintain a pool of pre-opened connections that your code borrows and returns, cutting overhead dramatically. Most Spring Boot projects configure HikariCP automatically through application.properties.

Working with Transactions and Batch Updates

A transaction is a group of SQL statements that must all succeed or all fail together, preserving database consistency. JDBC exposes transaction control through the Connection object. By default, connections run in auto-commit mode, meaning every statement commits immediately. For multi-step operations — transferring money between accounts, creating an order and its line items — you switch this off with conn.setAutoCommit(false), run your statements, then call conn.commit() on success or conn.rollback() if anything goes wrong.

Savepoints let you roll back to a midpoint within a transaction rather than all the way to the start. conn.setSavepoint("afterInsert") marks a position. If a later step fails, conn.rollback(savepoint) undoes only the work after that marker, keeping earlier work intact. This is useful in complex workflows where partial progress has value.

Batch updates dramatically improve throughput when you need to insert or update many rows. Instead of sending one INSERT per row — each requiring a round trip — you add statements to a batch and flush them all at once. With PreparedStatement: call ps.addBatch() after setting parameters for each row, then call ps.executeBatch() when ready. A batch of 1,000 inserts over a local network can be 10–50 times faster than 1,000 individual inserts because you pay the network round-trip cost once rather than a thousand times.

ResultSet has three types worth knowing. TYPE_FORWARD_ONLY (the default) lets you move forward row by row. TYPE_SCROLL_INSENSITIVE lets you move in any direction; the data is a snapshot taken at query time. TYPE_SCROLL_SENSITIVE reflects live changes. For most reporting and data-extraction work, forward-only is sufficient and most efficient.

One tip: always set an appropriate fetch size with rs.setFetchSize(100) when reading large result sets. Without this, some drivers load the entire result into memory at once, causing OutOfMemoryError on tables with millions of rows.

JDBC Best Practices for Production Code

Writing production-quality JDBC code goes beyond making queries work. The first rule is to never construct SQL by concatenating user-supplied strings. SQL injection remains one of the most exploited vulnerabilities in web applications and is entirely preventable with PreparedStatement. Even innocuous-looking inputs can contain quote characters or semicolons that break query logic or expose data.

Connection management deserves careful attention. Failing to close a Connection returns it to the pool in a dirty state — or, worse, leaks it entirely, eventually exhausting the pool and causing the application to hang. The Java try-with-resources statement, available since Java 7, is the cleanest solution: try (Connection conn = ds.getConnection(); PreparedStatement ps = conn.prepareStatement(sql)) { ... }. The compiler inserts the close calls in a finally block automatically.

Error handling with SQLException requires care because SQLException wraps database-specific error codes. Modern JDBC defines a hierarchy of subclasses — SQLIntegrityConstraintViolationException for duplicate-key errors, SQLTimeoutException for queries that exceed their allowed time — which lets you catch specific conditions without inspecting raw vendor codes.

Logging SQL statements and their execution times is invaluable during development and troubleshooting. Libraries like P6Spy or datasource-proxy wrap your DataSource and log every query with parameters and duration, making it easy to spot slow queries or unexpected N+1 patterns where your code fires one query per row of a parent result set.

For new projects, consider whether raw JDBC or a higher-level tool fits better. Frameworks like JPA/Hibernate, jOOQ, or Spring JDBC Template reduce boilerplate significantly. Spring JDBC Template, for instance, handles connection acquisition, exception translation, and resource cleanup while still letting you write plain SQL — a middle ground between raw JDBC and a full ORM. Raw JDBC remains valuable when you need precise control over SQL or work with legacy schemas that map poorly to ORM conventions.