What is JDBC? Introduction to Java Database Connectivity

PreparedStatements

One easy way to increase the flexibility of your code is to replace the Statement class with PreparedStatement, as shown in Listing 6.

Listing 6. Using JDBC PreparedStatements

String prepState = "insert into albums values (?, ?);";

PreparedStatement  prepState  =
      connection.prepareStatement(sql);

prepState.setString(1, "Uprising");
prepState.setString(2, "Bob Marley and the Wailers  ");

int rowsAffected = preparedStatement.executeUpdate();

PreparedStatement replaces Statement's hard-coded values with question marks (?). Using PreparedStatements optimizes your code for reuse: a PreparedStatement is compiled only once and can be reused with a variety of parameters. As your code base grows, you simply insert new values into the statement, instead of hacking the string object itself.

Batch updates

Whenever an application has several updates to issue, doing them in batches can greatly benefit performance. The essence of batching is to take the multiple updates and collect them together, then issue them all at once. Listing 7 uses JDBC's batch methods to perform a batch update of several PreparedStatements.

Listing 7. Batching with PreparedStatement

prepState.setString(1, "Uprising");
prepState.setString(2, "Bob Marley and the Wailers");
preparedStatement.addBatch();

prepState.setString(1, "Wildflowers");
prepState.setString(2, "Tom Petty and the Heartbreakers");
preparedStatement.addBatch();

int[]  rowsAffected   = preparedStatement.executeBatch();

JDBC transactions

You can use transactions in relational databases to wrap a set of updates in an interaction that either succeeds or fails altogether. The basic process of using a transaction via JDBC is to tell the system to turn off auto-commit, and then manually tell the system to commit when you are done. By default, auto-commit is on, which means whenever an executeUpdate or executeInsert is run, the command is committed. Listing 8 shows a small slice of a JDBC transaction.

Listing 8. JDBC transactions

connection.setAutoCommit(false);
// Use executeUpdate multiple times
connection.commit();

When the system encounters a connection.commit(), it will attempt all the updates wrapped inside. If any one of the updates fails, all of them will be rolled back.

JDBC 4.3 has many more features worth exploring, including connection pooling with DataSource objects, which we'll look at next.

Connection pooling with JDBC

Connection pooling is a performance improvement that lets you reuse a collection of database connections based on certain parameters. Instead of generating, using, and discarding connections, you maintain them in a pool. This section shows you how to use a JDBC DataSource object for connection pooling.

Because SQLite is a simple file database, we don’t really need to pool its connections. But its driver does offer a pooling DataSource at org.sqlite.javax.SQLiteConnectionPoolDataSource. (Note that not all DataSource object implementations support connection pooling.)

There are several approaches to obtaining a connection pool, including using a separate pooling service obtained from the Java Naming and Directory Interface (JNDI). Often, you will use a pool that ships with your application server, such as the Tomcat JDBC connection pool. Another option is to use a third-party tool, like the C3PO project. We’ll use C3PO for our example.

C3PO for connection pooling

Using C3PO with SQLite is relatively simple. You can do it by wrapping a previously configured SQLite data source (if you need the configuration) or configuring the pool with your database specifications directly in C3PO. We’ll try the simpler option. Note that you will need to have the C3PO .jar on your classpath.

Listing 9. JDBC connection pooling with C3PO

import com.mchange.v2.c3p0.ComboPooledDataSource;
import org.sqlite.javax.SQLiteConnectionPoolDataSource;
import org.sqlite.SQLiteConfig;
import java.sql.Connection;
// …

Listing 9 shows that there are a variety of options for tuning how the pool works, such as how many connections to hold and what increments to acquire them in. This listing just gives you a sense of what’s possible. Notice that with the pooling store configured, the actual code use of the JDBC database is exactly as before. The only difference is in how you build the Connection object.

Conclusion

JDBC is one of Java's oldest APIs, providing an easy-to-use solution for one of the perennial needs of Java application development. Knowing just the few JDBC calls demonstrated in this article will get you started using JDBC to connect to virtually any database. Once you've got those commands down, you can begin to explore some of the more sophisticated options that are built into JDBC.

While JDBC is sufficient for simpler applications, most developers will eventually look to the Jakarta Persistence API (formerly the Java Persistence API) to develop a more formal data access layer. JPA requires more up-front work and a more sophisticated understanding of the application architecture, but it nets you a more consistent, isolated, and well-defined data access layer. See the companion to this article, What is JPA? Introduction to the Jakarta Persistence API for more about developing the data persistence layer for your Java applications.

This story, "What is JDBC? Introduction to Java Database Connectivity" was originally published by JavaWorld.