1. INTRODUCTION
Many Java applications need access to databases. Java DataBase Connectivity (JDBC) allows us to access databases through Java programs. It provides Java classes and interfaces to fire SQL and PL/ SQL statements, process results (if any), and perform other operations common to databases. Since Java Server Pages can contain Java code embedded in them, it is also possible to access databases from Java Server Pages. The classes and interfaces for database connectivity are provided as a separate package, java.sqi.
2. JDBC DRIVERS
A Java application can access almost all types of databases such as relational, object, and object- relational. The access to a specific database is accomplished using a set of Java interfaces, each of which is implemented by different vendors differently. A Java class that provides interfaces to a specific database is called JDBC driver. Each database has its own set of JDBC drivers. Users need not bother about the implementation of those Java classes. They can concentrate on developing database applications. Those drivers are provided (generally freely) by database vendors. This way, JDBC hides the underlying database architecture. JDBC drivers provided by database vendors convert database access requests to database-specific APIs.
JDBC drivers are classified into four categories depending upon the way they work.
2.1. JDBC-ODBC Bridge (Type 1)
This is the Type 1 driver. This type of driver cannot talk to the database directly. It needs an intermediate ODBC (Open DataBase Connectivity) driver, with which it forms a kind of bridge. The driver translates JDBC function calls to ODBC method calls. ODBC makes use of native libraries of the operating system and is hence platform-dependent. For this mechanism to function correctly, the ODBC driver must be available in the client machine and must also be configured correctly, which is generally a long and tedious process. For this reason, the Type 1 driver is used for experimental purposes or when no other JDBC driver is available. Sun provides a Type 1 JDBC driver with JDK 1.1 or later.
2.2. Native-API, Partly Java (Type 2)
This is very similar to the Type 1 driver. However, it does not forward the JDBC call to the ODBC driver. Instead, it translates JDBC calls to database-specific native API calls. This driver is not a pure Java driver, as it interfaces with non-Java APIs that communicate with the database. This approach is a little bit faster than the earlier one, as it interfaces directly with the database through the native APIs. However, it has limitations similar to the previous one. This means that the client must have vendor-specific native APIs installed and configured in it.
2.3. Middleware, Pure Java (Type 3)
In this case, the JDBC driver forwards the JDBC calls to some middleware server [Figure 22.1: (ii)] using a database-independent network protocol. The middleware server acts as a gateway for multiple (possibly different) database servers and can use different database-specific protocols to connect to different database servers. This intermediate server sends each client request to a specific database. The results are then sent back to the intermediate server, which in turn sends the result back to the client. This approach hides the details of connections to the database servers and makes it possible to change the database servers without affecting the client.
2.4. Pure Java Driver (Type 4)
These types of drivers communicate with the database directly by making socket connections. It has distinct advantages over other mechanisms, in terms of performance and development time. Since, it talks with the database server directly, no other subsidiary driver is needed. In this book, we shall use only the Type 4 driver.
3. JDBC ARCHITECTURE
The JDBC architecture is sometimes classified as: two-tier and three-tier. Type 2 and 4 drivers use two-tier and Type 1 and 3 use three-tier architecture. Figure 22.1: (i) and (ii) show the JDBC two-tier and three-tier architectures.
4. JDBC CLASSES AND INTERFACES
Java provides an API for accessing and processing data stored in a data source (usually a relational database). A summary of JDBC classes and interfaces with a brief description is shown in Table 22.1:
5. BASIC STEPS
The following basic steps are followed to work with JDBC:
- Loading a Driver
- Making a connection
- Executing an SQL statement
6. LOADING A DRIVER
You have to first download an appropriate driver depending upon the database you want to connect. Sun provides a Type 1 driver bundled with the JDK 1.1 or later. Other types of drivers are database-specific and must be downloaded.
The latest version, Type 4 MySQL JDBC driver, can be downloaded from the following site:
http://dev.mysql.com/downloads/connector/j/#downloads
Download the .zip or .tar.gz file containing the jar (java archive) file mysql-connector-java- 5.1.26-bin.jar.
The latest version Type 4 JDBC driver for Oracle can be downloaded from the following site:
http://www.oracle.com/technology/software/tech/java/sqlj_jdbc/index.html
Download the appropriate driver depending upon the JDK and Oracle version you are using. For example, the binary driver file for Oracle 12c and JDK 1.7 or later is ojdbc7.jar.
Once you have downloaded the appropriate .jar file, put it in Tomcat’s lib directory and restart the web server.
If you are developing simple Java database applications, put this .jar file in the classpath environment variable.
So far, we have downloaded and installed the JDBC driver. For it to start functioning, an instance of the driver has to be created and registered with the DriverManager class so that it can translate the JDBC call to the appropriate database call. The JDBC class DriverManager is an important class in the java.sql package. It interfaces bet^veen the Java application and the J^)BC driver. This class manages the set of JDBC drivers installed on the system. It has many other useful methods, some of which will be discussed in Section 26.17.
One way to register a driver with the driver manager is to use the static with a driver class name as an argument. For example, the Type 1 driver provided by Sun can be instantiated and registered with the driver manager as follows:
Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver”);
The method forName() creates an instance of the class whose name is specified as an argument using its default constructor. The instance created in this fashion must register itself with the DriverManager class. The .jar file for MySQL contains two driver class files with the name Driver. class, one in the com.mysql.jdbc package and the other in the org.gjt.mm.mysql package. So, you may use any one of the following:
Class.forName(”com.mysql.jdbc.Driver”);
Class.forName(”org.gjt.mm.mysql.Driver”);
One can perform this registration procedure by explicitly creating an instance and passing it to the static registerDriver() method of the DriverManager class. The method registerDriver() in turn registers the driver with the driver manager. Some JDBC vendors such as Oracle recommend the latter mechanism.
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
A similar procedure can be followed for other drivers as well. The implementation of the MySQL driver file com.mysql.jdbc.Driver looks like this.
public class Driver extends NonRegisteringDriver implements java.sql.Driver {
static {
try {
JAVA DATABASE CONNECTIVITY (JDBC) 1529
java.sql.DriverManager.registerDriver(new DriverO);
} catch (SQLException E) {
throw new RuntimeException(”Can’t register driver!”);
}
}
public Driver() throws SQLException {}
}
Since the static block registers the driver with the driver manager automatically, only creating an instance is sufficient. So, one might use the following code as well:
new com.mysql.jdbc.Driver();
Now, the driver is ready to translate the JDBC call.
7. MAKING A CONNECTION
Once a driver is instantiated and registered with the driver manager, the connection to the database can be established using methods provided by the DriverManager class. For each connection created, DriverManager makes use of the appropriate driver registered to it. The following methods are available on the DriverManager class to establish a connection. All methods return a Connection object on successful creation of the connection.
public static Connection getConnection(String url, String login, String passwd)
public static Connection getConnection(String url)
public static Connection getConnection(String url, Properties)
The Connection object encapsulates the session/connection to a specific database. It is used to fire SQL statements as well as commit or roll back database transactions. It also allows us to collect useful information about the database dynamically and to write custom applications. Many connections can be established to a single database server or different database servers.
The primary argument that the getConnection() method takes is a database URL. This argument identifies a database uniquely. DriverManager uses this URL to find a suitable JDBC driver installed earlier, which recognizes the URL and uses this driver to connect to the corresponding database.
The URL always starts with jdbc:. The format of the rest of the JDBC URL varies widely for different databases. Some are mentioned in Table 22.2: The format of the MySQL JDBC URL is as follows:
jdbc:mysql://[host]:[port]/[database]
Here, host is the name (or IP address) of the machine running the database at the port number port and database is a name of a database. Suppose a MySQL database, test, is running in a machine, uroy, at port 3306, the corresponding URL will be
jdbc:mysql://uroy:3306/test
A database connection can be established using this URL as follows:
Connection con = DriverManager.getConnection(“jdbc:mysql://uroy:3306/test”, “root”, “nbuser”);
Similarly, the following code segment creates a database connection to the Oracle database mirora running in the machine miroracle at port 1521.
Connection con =
DriverManager.getConnection(“jdbc:oracle:thin:@miroracle:1521:mirora”, ”scott”, “tiger”);
The second overloaded version of the getConnection() method takes only a string argument. This argument must contain URL information, together with other parameters such as user name and password. The parameters are passed as a name-value pair separated by “&” using the same syntax as the HTTP URL. The general syntax of such a URL is as follows:
basicURL?param1=value1¶m2=value2…
The following is an example of such a string argument for the MySQL database.
jdbc:mysql://uroy:3306/test?user=root&password=nbuser
Alternatively, parameters can be put in a java.util.Properties object and the object can be passed to the getConnection() method. The following is an example using Properties.
String url = “jdbc:mysql://uroy:3306/test”;
java.util.Properties p = new java.util.Properties();
p.setProperty(“user”, “root”);
p.setProperty(“password”, “nbuser”);
Connection con = DriverManager.getConnection(url, p);
8. EXECUTE SQL STATEMENT
Once a connection to the database is established, we can interact with the database. The Connection interface provides methods for obtaining different statement objects that are used to fire SQL statements via the established connection. The Connection object can be used for other purposes such as gathering database information, and committing or rolling back a transaction. The following section describes the different types of statement objects and their functionality.
Source: Uttam Kumar Roy (2015), Advanced Java programming, Oxford University Press.