8 JDBC1
Pravin Jain
What is JDBC?
JDBC is JDBC. It is not an acronym. Well, we do come across texts which describes JDBC as an acronym for Java Database Connectivity, and also there are other texts which would mention, that JDBC is not an acronym.
Let’s not worry about that. ODBC and JDBC
What is ODBC?
ODBC is Open Database Connectivity. ODBC is a standard set of calls, which is used for connecting and interacting with a database. ODBC defines a standard way of interaction with any kind of database. There are various kinds of databases, available from various vendors. These database vendors normally have a native driver for their database. This native driver is a collection of proprietary calls, which can be used by any application developer to interact with the database. These native drivers are normally accessedfrom applications written in C or other native programming languages. Applications written using the native drivers, are not portable to other kinds of databases. ODBC was a standard created to make applications independent of the database. ie. if an application developer, develops an application which uses only ODBC calls, for interaction with a database, then the application is independent of the kind of database being used. The database vendors, supply an ODBC driver along with the database. The ODBC driver is the implementation of the ODBC calls, specifically for interaction with a particular kind of database. So, if an application is using only ODBC calls, then the application would be able to interact with any kind of database for which an ODBC driver is available. This allows, developers to develop prototype using a smaller footprint databases, and that same code could be reused in a production environment. Also switching from one kind of database to another would not require any kind of porting efforts. The same application would work with other kinds of database, without recompilation.
Native Applications interacting with database via ODBC.
Like ODBC, JDBC is used by Java applications to connect and interact with a database. JDBC is a standard set of classes and interfaces, available as part of the standard java . sql package. A Java application can make use of these standard JDBC classes and interfaces in order to connect and interact with a database. Most of the API, in java . sql package is made up of interfaces.
Java Applications interacting with database via JDBC.
JDBC Drivers
The different JDBC drivers, for connection and interaction with different types of databases are the different implementations of these interfaces (available from java . sql package) and their additional supporting classes. A JDBC driver (the set of implementation classes) is normally packaged and available in a jar file. There are different JDBC drivers for different databases. Even for connecting to the same database, there are multiple vendors, supplying the JDBC Drivers. The JDBC drivers have been categorized into four types based on their implementations. The main concern, in this categorization is related to the use of native methods (dependence on native code).
Types of JDBC Drivers
There are four types of JDBC Drivers. The Type-1, and Type-2 drivers are the ones which make use of native methods, whereas the Type-3 and Type-4 are pure Java drivers, and do not make use of any native calls.
Types of JDBC Drivers
| Indirect | Direct | |
| Native | 1 | 2 |
| Pure Java | 3 | 4 |
Type-1 (JDBC-ODBC bridge driver)
The Type-1 driver is the JDBC-ODBC bridge driver. There is an implementation of the JDBC-ODBC bridge driver, included in the distribution of the standard Java Run time. This driver does not directly interact with a database. Instead, it interacts with the database, via an ODBC driver. It is dependent on an ODBC driver. The implementation of this driver makes use of the native methods, in order to make the standard ODBC calls.
Java Applications interacting with database via Type-1 Driver.
Type-2 (Native driver)
The Type-2 driver is an implementation of the JDBC API, which directly connects to the database. It is not dependent on any other driver or application like ODBC. The native drivers, normally make use of the native library supplied by the database vendor, They make use of the proprietary calls available in the native library supplied by the database vendor. The implementation of this driver makes use of native methods, in order to make the calls to the native driver.
Java Applications interacting with database via Type-2 Driver.
Type-3 (Net driver)
The Type-3 driver is an implementation of the JDBC API, which like the Type- 1 driver is dependent on some other network based application. These drivers interact with the database via some network based middleware. The implementation of this type of JDBC driver with the network based middleware does not make use of any native methods. So, these drivers are pure Java drivers. But they do depend on some middleware which is a native application (non-Java application).
Using JDBC Drivers and the API
As mentioned in above section , the JDBC Drivers are implementations of the interfaces from the java. sql package. In this package, we have lots of interfaces. One of the important interface, which is used for making a connection to the database is the Driver interface. A connection to a database is represented by an instance of a class implementing the Connection interface. The Driver interface provides methods which returns an instance of Connection.
Overview of API related to connection and interaction with database
A Java application, which would like to interact with any database, would have to first obtain an instance of Connection, before it can start interacting with the database. A single Java application may like to interact with different types of databases. ie. the application may have to manage multiple instances of the Driver. In {java. sql package, we have a class called DriverManager which manages various instances of Driver and helps the application to get a instance of Connection by using the appropriate instance of Driver from the various Driver instances which it is managing.
There are three common interactions, which are performed by an application with a database.
• executing any kind of SQL
• executing pre-compiled SQL
• executing stored-procedures and functions
The three kinds of interaction require instances of Statement for executing any kind of SQL, PreparedStatement for executing pre-compiled SQL and CallableStatement for executing stored- procedures and functions. We have methods in the Connection interface to obtain instances of the Statement, PreparedStatement and the CallableStatement.
Connecting to a database
There are two steps which are normally used to make a connection to a particular database. Step one, is to register the appropriate instance of Driver implementation class with the DriverManager. Second step is to use the DriverManager to obtain the Connection instance, which represents the connection to the database. The first step is normally done by simply loading the class which implements the Driver interface. The static block of all the Driver implementation classes, carry out the task of registering an instance of the class, with the DriverManager. The DriverManager class has a static method to register a Driver instance.
To load any class, we can use the static method forName() in the Class class. The method signature of the static method in the Class class is as given below:
public static Class forName(String classname) throws ClassNotFoundException
The forName method has only one parameter, which is used to pass the fully qualified name of the class to be loaded, and it returns the Class instance for the class which has been loaded. This method can throw a ClassNotFoundException. So, if we want to use the JDBC-ODBC driver class from the JDK. In this case the name of the driver class is “sun.jdbc.odbc.JdbcOdbcDriver”. If we are using some other JDBC driver, then we would normally have a jar file, containing the driver class. This jar file should be made available in the CLASSPATH. We should also know the name of the class implementing the java. sql .Driver inter- face. This would be available from the documentation, for the JDBC driver being used. So the first step in an application using JDBC for connecting to a database would appear something like
Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver”);
Note: Use the appropriate class name in the above call, depending on the JDBC driver we are using.
The second step is to establish a connection to the database. To get an instance of Connection, we use the static method getConnection() in the DriverManager class, as shown below:
Connection con = DriverManager.getConnection(“jdbc:odbc:<dsn>”);
The static method getConnection() takes as parameter a String which is the jdbc-url. The jdbc-url is of the form “jdbc:<sub-protocol>:…”. This string is dependent on the driver used. Different drivers would have different requirements. The jdbc-url syntax has lot of things which depend on the JDBC driver being used. When we call the getConnection() method, the DriverManager would check each of the registered drivers, if they support the specified jdbc- url. The driver instance which accepts such a url would return the Connection to the DriverManager, which is in turn returned to the application by the DriverManager. Let us say, we would like to connect to the PostgreSQL database, and we have downloaded the pg73jdbc3.jar file, which contains the jdbc driver for connecting to PostgreSQL ver.
7.3. Then the sequence would be something like:
Class.forName(“org.postgresql.Driver”) ;
Connection con = DriverManager.
getConnection(“jdbc:postgresql://<host>/<database>”);
The static method getConnection() in DriverManager is overloaded and we may use it to separately supply the authentication info. like
public static Connection getConnection(String jdbcurl)
public static Connection getConnection(String jdbcurl, String user, String password)
Interacting with the database
As mentioned in the above section there are mainly three kinds of interactions. Executing SQL, executing pre-compiled SQL and executing stored-procedures and functions. Let us look at how we use the JDBC API to carry out each of these tasks.
Executing SQL using Statement
Once we have the instance of Connection, and we want to execute a SQL, then we may use the createStatement() on the Connection instance to obtain the instance of Statement. The createStatement() method in Connection interface is overloaded as shown below:
Statement createStatement()
Statement createStatement(int resultSetType, int resultSetConcurrency)
We have the first method without parameters, and the second method takes two integer parameters. The int values for resultSetType and resultSetConcurrency are defined in the java.sql.ResultSet interface. When we use a select query to get results from a database, it would return an instance of ResultSet. The Statement instance is used for executing SQL. So the two parameters define the type of resultSets returned by this Statement when we execute any select query using this statement. The valid values for resultSetType are:
ResultSet.TYPE_FORWARD_ONLY ResultSet.TYPE_SCROLL_SENSITIVE
ResultSet.TYPE_SCROLL_INSENSITIVE
The valid values of resultSetConcurrency are:
ResultSet.CONCUR_READ_ONLY ResultSet.CONCUR_UPDATABLE
By default the ResultSet type is forward only and read only. Let us look at how to execute an SQL. Once we have an instance of Statement. The Statement interface has the following methods for executing a SQL.
Boolean execute(String sql) int executeUpdate(String sql)
ResultSet executeQuery(String sql)
All the three methods can be used for executing any kind of SQL. The SQL to be executed is passed as the parameter. All the methods have a different return type. Depending on the type of SQL to be executed, we choose the method with the appropriate return type. The execute() method is more commonly used for executing the DDL statements like CREATE and DROP. The executeUpdate() method is more commonly used for executing the DML statements like INSERT, UPDATE and DELETE. This method returns the number of rows affected by the DML statement. The executeQuery() method is more commonly used for executing the SELECT statement. It returns an instance of ResultSet, which contains the results of the SELECT query.
The instance of Statement can be used for executing multiple SQL statements in a batch. The Statement interface has methods to manage SQL statements into a batch, and a method to execute all the SQL statements which have been added into the batch. The following methods are used for batch execution.
public void addBatch(String sql) public void clearBatch( )
public int[] executeBatch( )
The size of the int [] returned by executeBatch() method is the number of SQL statements in the batch, and it contains an int value corresponding to each SQL statement executed from the batch. The int value is the number of rows affected by the DML statement, and in case of SELECT statement it contains the value of -1.
Executing a precompiled SQL using PreparedStatement
An instance of Statement is used to execute any kind of SQL. There is a sub-interface of Statement called PreparedStatement, which is used to execute precompiled SQL. An instance of PreparedStatement is also created from the instance of Connection by using the following methods of Connection interface:
PreparedStatement prepareStatement(String sql)
PreparedStatement prepareStatement(String sql, int rstype, int rsconcur)
Here unlike the Statement the SQL is specified at the time of creating the in- stance of PreparedStatement. The SQL, which is passed as parameter can have place holders which are introduced by using the ’ ?’ character within the SQL string passed as parameter. These place holders are referred to as parameters for the PreparedStatement.
PreparedStatement ps = con.prepareStatement(“select * from account where acno=? and type = ?”);
PreparedStatement ps = con.prepareStatement(“insert into account values(?,?,?,?)”);
The first ’ ?’ in the SQL string is the first parameter of the PreparedStatement, and so on. So, the number of parameters in a PreparedSTatement will be the number of ’ ?’ characters used in the SQL string. The parameters can only be used in place for values in the SQL statement. These parameter values need to be setup before the precompiled SQL corresponding to the PreparedStatement can be executed.
Now the PreparedStatement interface has additional methods for executing the SQL. These are the same three methods as in Statement interface, but without any parameters. In Statement interface the parameter to the three execute methods was the SQL to be executed. In case of PreparedStatement this has already been specified at the time of creating the instance using the parameter in the prepareStatement() method. So, the methods for executing the precompiled SQL in a PreparedStatement are:
boolean execute() int executeUpdate()
ResultSet executeQuery()
Executing Stored Procedures and functions using CallableStatement
There is also a sub-interface of PreparedStatement called the CallableStatement, this is used to execute stored procedures and functions. The instance of CallableStatement is created by the method on Connection as
CallableStatement prepareCall(String callstatement)
CallableStatement prepareCall(String callstatement,int rstype,int rsconcur)
The CallableStatement inherits methods from the PreparedStatement and some more additional methods. let us look at how to create an instance of CallableStatement. In the above methods the syntax for the callstatement is:
{ [?] = call <proc-name or function name> (?,…} }
if it is a stored procedure which is to be invoked we create CallableStatement as
CallableStatement cs = con.prepareCall(“{ call myproc(?,?,?) }”);
to execute a stored procedure or
CallableStatement cs = con.prepareCall(“{ ? = call myfunctino(?, ?, ?) }”);
to execute a function, which returns some value.
In Summary:
- Java applications use JDBC to access any kind of databases. The JDBC is a standard way of interaction with different databases, available from different vendors. The JDBC is defined using the set of interfaces and classes available in the java . sql package. There are various implementations of these standard for different types of database. These JDBC drivers are categorized into four types based on how they have been implemented.
- The Type-1 driver is the one which talks to the ODBC driver available for connected to a database. This driver makes use of native calls in order to make the ODBC calls. The type 2 driver is the one which directly talks to the database, but is not a pure Java driver, it makes use of native libraries available from the database vendor to make connection and talk to the databse. The type 3 driver is a pure Java driver, unlike the type 1 and type 2. but it does not directly talk to the database, instead it talks to some network absed service which in turn talks to the database. and type 4 driver is the one which is a pure Java driver directly communicating with the database.
- The first task in interacting with database for a Java application is to obtain an instance of Connection. This is then used for all other interactions with the database. The instance of a class implementing the Driver interface is used to obtain instance of Connection. The DriverManager class can manage various implementations of Driver, which are first required to be registered. Whenever any implementation of Driver is loaded, it would register with the DriverManager. The DriverManager has method getConnection(), which can be used to obtain a connection for a given url. The DriverManager will select the appropriate instance of Driver and use it to return the required Connection.
| you can view video on JDBC1 | |