JDBC architecture & Driver Types

The Java Database Connectivity is a standard Java API specifying interfaces for connectivity between the Java applications and a wide range of databases. The JDBC contains methods to connect to a database server, send queries to create a table, update, delete, insert records in a database,  retrieve and process the results obtained from a database. Nowadays, there are a lot of frameworks that are built for easier work with databases. But they contain the JDBC under the hood.

Java Database Connectivity supports two-tier and three-tier architectures to access a database. They are also called as processing models for database access. Let’s look closer at them.

Two-tier Architecture

In this kind of architecture, a java application is directly communicating with a database. It requires one of the specific Java Database Connectivity drivers to connect to a specific database. All queries and requests are sending by the user to the database and results are receiving back by the user. The database can be running on the same machine. Also, it can be on a remote machine and to be connected via a network. This approach is called a client-server architecture.

Three-tier Architecture

In the three-tier model, there is no direct communication. First of all, all requests and queries are sent to the middle tier. A middle tier can be a browser with a web page or desktop application, that sends a request to the java application. After that request is sent to the database. The database processes the request and sends the result back to the middle tier. And the middle tier then communicates with the user. This model has better performance and simplifies application deployment.

All of these different executables are able to access a database with the use of a JDBC driver as a middle tier: Java  Desktop Applications, Java Applets, Java Servlets, Java Server Pages (JSPs), Enterprise JavaBeans (EJBs).

For the two-tier model and the three-tier model, JDBC has two main layers:

JDBC API (an application-to-JDBC Manager connection), JDBC Driver API (this supports the JDBC Manager-to-Driver Connection).

 The JDBC driver manager and database-specific drivers provide the possibility to connect to heterogeneous databases. The function of the JDBC driver manager is to ensure that the correct driver is used to access each database. The driver manager is capable to support more than one driver. And the drivers can be concurrently connected to multiple different data sources. 

For example, your application can connect to the Oracle database and MySQL database at the same time. The connections will be created by the use of different drivers. You can read data from the Oracle database and insert it into MySQL and vise versa.

JDBC Drivers Types

There are different JDBC driver implementations, because of the wide variety of operating systems and hardware platforms. There are 4 driver types:

• JDBC-ODBC Bridge Driver
• JDBC-Native API
• JDBC-Net pure Java
• 100% Pure Java

Usually, type 3 is the preferred driver. Type 2 is used in situations, where a type 3 or type 4 driver is not available yet for your database.

The type 1 driver is typically used for development and testing purposes only, not considered for deployment.

JDBC API interfaces and classes

Let’s look at JDBC API interfaces and classes, that are located in the package java.sql and javax.sql:

The DriverManager is the basic service class for managing a set of database drivers. During initialization, the DriverManager class loads the driver classes from the “jdbc.drivers” system property. This allows you to customize the drivers used by your application in a properties file.

The most useful methods of the DriverManager class are the getConnection and the getDrivers. The getConnection method has many versions, but they all are about the ways to pass database URL, user, password and connection properties. This method attempts to create a connection to the given database URL with given credentials and connection properties. The getDrivers() method retrieves an Enumeration with all of the currently loaded JDBC drivers that it can access.

From JDBC 4.0 Drivers must include the file META-INF/services/java.sql.Driver, which contains the name of the JDBC drivers implementation of java.sql.Driver.

Applications already do not need to explicitly load JDBC drivers using the method Class.forName(). When we call the method getConnection, the DriverManager attempts to locate a suitable driver from loaded at initialization and explicitly using the same classloader as the current application.

The Driver interface is an interface that every Driver has to implement.

The Java application can have multiple database drivers.

The DriverManager is trying to load as many drivers as it can find. For any given connection request, the DriverManager will ask each driver before trying to connect to the database URL.

Each Driver class is recommended to be small and standalone.

When a Driver class is loaded, it creates an instance of itself and registers within the DriverManager.

The Connection interface is an interface that contains all the methods for work with a connected database. All actions under a database can be done just with the Connection object, because it represents the communication context. We can get the Connection object with the DriverManager method getConnection.

Let’s look at the basic methods. The information about our database can be obtained with the getMetaData method. It provides information about the database tables, supported SQL grammar, stored procedures, etc. 

To perform database SQL queries and SQL requests, developers are using the createStatement() method.  This method creates a Statement object for sending SQL statements to the database. There is also another method prepareStatement(String SQL), that creates a PreparedStatement object for sending parameterized SQL statements to the database. The difference between these two methods is that the query or request from the second method can be called as many times as we need.

There is a possibility to change the transaction isolation level for the Connection object with the method setTransactionIsolation(int level). Levels of isolations are provided as constants (TRANSACTION_NONE, TRANSACTION_READ_COMMITTED, TRANSACTION_READ_UNCOMMITTED, TRANSACTION_REPEATABLE_READ, TRANSACTION_SERIALIZABLE). 

 This information about the database can be obtained with the getMetaData method. One more valuable method is setAutoCommit(boolean autoCommit), that sets this connection auto-commit mode to the given state. By default, when you create a Connection object, it is in auto-commit mode. The auto-commit mode means that it automatically commits changes after executing a statement. The disabled auto-commit mode means that the commit method must be called explicitly. Otherwise, database changes will not be saved.

The Statement interface is aimed to create an object used for executing a static SQL statement and returning the results that it produces. We open just one ResultSet object per Statement at the same time. The ResultSets have to be generated by different Statement objects.The most used method of the Statement is the method executeQuery(String sql). This method performs SQL query and returns a result in the ResultSet object. This method can throw SQLException or SQLTimeoutException. The method cannot return null, the ResultSet always returns.

There is one more similar method executeUpdate. This method used to perform the given SQL statement, which may be an INSERT, UPDATE, or DELETE statement. It also can perform an SQL statement that returns nothing, such as an SQL DDL statement.

The PreparedStatement class is a class that extends the Statement interface. A PreparedStatement object represents a precompiled SQL statement. After, that statement is stored in a PreparedStatement object. This object can be used to execute this statement multiple times. It has a similar interface with the Statment.

The ResultSet object contains the data retrieved from a database after the execution of an SQL query. The ResultSet can be also called a table. It works similarly to an iterator. There is the method next that moves the cursor to the next row. This method returns false when there are no more rows in the ResultSet object. The ResultSet can be used in a while loop.

A ResultSet object is not updatable by default. It has a cursor that moves forward only. You can iterate through it only once from the first row to the last row. You can make a RestultSet updatable:

 Statement stmt = con.createStatement(
                                      ResultSet.TYPE_SCROLL_INSENSITIVE,
                                      ResultSet.CONCUR_UPDATABLE);
       ResultSet rs = stmt.executeQuery(“SELECT a, b FROM MY_TABLE”);

To retrieve column values from the current row, the ResultSet interface provides getter methods (getBoolean, getLong, etc). You can retrieve values using the index number of the column or the name of the column. Using the column index is more efficient. Columns are numbers are starting from 1. Result set columns within each row are read from left-to-right, and each column is read-only once.

For the getter methods, the underlying data are attempting to convert to the Java type specified in the getter method. If you use getter methods with a column name, you need to know that they are case insensitive. 

If you have several columns with the same name, the value of the first matching column will be returned. It is designed to use the column name option when column names are used in the SQL query that generated the result set. 

The SQLException class handles almost all errors that occur in an application that works with a database. You can understand the problem by calling the method getMessage. This method provides a string describing the error. You can also use the method getErrorCode(). That method retrieves the vendor-specific exception code for this SQLException object.

The SQLTimeoutException class extends SQLTransientException class.

It is thrown when the timeout specified by the Statement has expired.  This is usually determined by the database driver. The timeout occurs when the value set by the method setQueryTimeout has been exceeded.