24.2 Introduction to JDBC
The Java Database Connectivity (JDBC) protocol provides database interaction capabilities for Java programs. The JDBC specification is not database provider specific, allowing Java programs to interact with any database.
The JDBC API is defined in the java.sql package. It comprises a number of interfaces that Java programs use to represent database connections, SQL statements, query results, and much more.
The JDBC API is implemented by a number of database-specific JDBC drivers. The job of a JDBC driver is to provide database-specific, native protocol implementation of the JDBC API. For example, Oracle, MySQL, DerbyDB, and others provide JDBC drivers that all implement the same JDBC API, but in a different database-specific manner.
A Java application can dynamically load the JDBC driver as required and connect to different databases, provided that the JDBC driver is present in the class path or module path of the application.
Figure 24.1 illustrates the layered approach to database connectivity. Java developers write code that utilizes JDBC interfaces defined by the java.sql package. These interfaces are implemented by different JDBC drivers. Java developers should only write code that utilizes JDBC API interfaces and not use JDBC drivers directly, so that software portability is not compromised. This allows Java applications to maintain database-provider neutrality, and potentially switch JDBC drivers and database providers without modifying application code. This approach is designed to decouple the application code from specific database providers, potentially allowing the application to switch between different databases and even use different databases at the same time.
Figure 24.1 Layered Database Connectivity
In order to connect to a database, the Java application has to perform the following tasks:
- Ensure that the relevant JDBC driver is available in the class path or module path of the application.
- Load the JDBC driver to memory.
- Establish the database connection.
Once a database connection is established, a typical interaction scenario with the database proceeds as follows:
- Create SQL statements.
- Execute SQL statements.
- Process query results.
- Close the JDBC resources.
Closing JDBC Resources
It is important to ensure that all JDBC resources are properly closed once they are no longer needed.
All JDBC API methods can throw a java.sql.SQLException. A SQLException is a checked exception, which in addition to the usual Java error message, also wraps up database error information such as the SQL state and an error code.
The skeletal code below will always catch the SQLException and handle it:
try {
/* execute JDBC operations */
} catch (SQLException e) {
String state = e.getSQLState();
int code = e.getErrorCode();
}
The logic of the JDBC application can be generalized with the following skeletal code:
try {
/* establish database connection */
/* create and execute SQL statements */
/* process results */
} catch (SQLException e) {
/* handle any errors */
} finally {
/* close result sets */
/* close statements */
/* close connection */
}
It is important to remember that the closing order of these objects is significant: First close any result set objects, then close statements, and then close connections. No Java exceptions are actually produced if you try to close in a different order. However, this would prevent resources from being promptly released and thus can result in memory leaks.
JDBC interfaces that represent connection, statement, and result set objects all implement the AutoCloseable interface. Therefore, they can be used in the try-with-resources construct, in which case they are automatically closed in the implicit finally block inserted by the compiler, as shown below.
try (/* establish database connection */
/* create and execute SQL statements */)
{
/* process results */
} catch (SQLException e) {
/* handle any exceptions */
} /* implicit finally block closes resources*/
