{"id":59,"date":"2018-07-10T10:26:35","date_gmt":"2018-07-10T10:26:35","guid":{"rendered":"http:\/\/itp16.epgpbooks.inflibnet.ac.in\/?post_type=chapter&p=59"},"modified":"2019-05-15T07:11:22","modified_gmt":"2019-05-15T07:11:22","slug":"jdbc1","status":"publish","type":"chapter","link":"https:\/\/ebooks.inflibnet.ac.in\/itp16\/chapter\/jdbc1\/","title":{"rendered":"JDBC1"},"content":{"raw":"
\"epgp<\/a>\r\n<\/span><\/div>\r\n \r\n\r\nWhat is JDBC?<\/strong>\r\n\r\n \r\n

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 \u00a0acronym.<\/p>\r\n \r\n\r\nLet\u2019s not worry about that. ODBC and JDBC\r\n\r\n \r\n

What is ODBC?<\/strong><\/p>\r\n \r\n

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.<\/p>\r\n

\"\"\r\n Native Applications interacting with database via ODBC.<\/strong><\/p>\r\n \r\n

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.<\/p>\r\n \r\n

Java Applications interacting with database via JDBC.<\/strong><\/p>\r\n \r\n\r\n\"\"\r\n\r\n \r\n

JDBC Drivers<\/strong><\/p>\r\n \r\n

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).<\/p>\r\n

Types of JDBC Drivers<\/strong><\/p>\r\n \r\n

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.<\/p>\r\n \r\n

Types of JDBC Drivers<\/strong><\/p>\r\n\r\n\r\n\r\n\r\n\r\n\r\n
<\/td>\r\nIndirect<\/strong><\/td>\r\nDirect<\/strong><\/td>\r\n<\/tr>\r\n
Native<\/td>\r\n1<\/td>\r\n2<\/td>\r\n<\/tr>\r\n
Pure Java<\/td>\r\n3<\/td>\r\n4<\/td>\r\n<\/tr>\r\n<\/tbody>\r\n<\/table>\r\nType-1 (JDBC-ODBC bridge driver)<\/strong>\r\n\r\n \r\n

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.<\/p>\r\n \r\n\r\n\"\"\r\n

Java Applications interacting with database via Type-1 Driver.<\/strong><\/p>\r\n \r\n

Type-2 (Native driver)<\/strong><\/p>\r\n \r\n

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.<\/p>\r\n \r\n\r\n\"\"\r\n

Java Applications interacting with database via Type-2 Driver.<\/strong><\/p>\r\n \r\n

Type-3 (Net driver)<\/strong><\/p>\r\n \r\n

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).<\/p>\r\n\"\"\r\n

JavaApplicationsinteractingwithdatabaseviaType-4Driver.<\/strong><\/div>\r\n
\r\n\r\n \r\n

Using JDBC Drivers and the API<\/strong><\/p>\r\n \r\n

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 \u00a0database \u00a0is represented by an instance of a class implementing the Connection interface. The Driver interface provides methods which returns an instance of Connection.<\/p>\r\n \r\n

Overview of API related to connection and interaction with database<\/strong><\/p>\r\n \r\n

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.<\/p>\r\n \r\n\r\nThere are three common interactions, which are performed by an application with a database.\r\n\r\n \r\n\r\n\u2022 executing any kind of SQL\r\n\r\n\u2022\u00a0 executing pre-compiled SQL\r\n\r\n\u2022\u00a0 executing stored-procedures and functions\r\n\r\n \r\n

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,\u00a0 PreparedStatement \u00a0and the \u00a0CallableStatement.<\/p>\r\n \r\n

Connecting to a database<\/strong><\/p>\r\n \r\n

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.<\/p>\r\n \r\n

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:<\/p>\r\n \r\n\r\npublic static Class forName(String classname) throws ClassNotFoundException\r\n\r\n \r\n

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<\/p>\r\n \r\n

Class.forName(\"sun.jdbc.odbc.JdbcOdbcDriver\");<\/span><\/p>\r\n \r\n

Note: Use the appropriate class name in the above call, depending on the JDBC driver we are using.<\/span><\/p>\r\n \r\n

The second step is to establish a connection \u00a0to \u00a0the \u00a0database. \u00a0To \u00a0get \u00a0an \u00a0instance \u00a0of \u00a0Connection, \u00a0we use the static method getConnection() in the DriverManager class, as shown below:<\/span><\/p>\r\n\r\n<\/div>\r\n

\r\n\r\n \r\n\r\nConnection con = DriverManager.getConnection(\"jdbc:odbc:<dsn>\");\r\n\r\n \r\n

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.<\/p>\r\n7.3. Then the sequence would be something like:\r\n\r\n \r\n\r\nClass.forName(\"org.postgresql.Driver\") ;\r\n\r\nConnection con = DriverManager.\r\n\r\ngetConnection(\"jdbc:postgresql:\/\/<host>\/<database>\");\r\n\r\n \r\n

The static method getConnection() in DriverManager is overloaded and we may use it to separately supply \u00a0the \u00a0authentication \u00a0info.\u00a0 like<\/p>\r\n \r\n\r\npublic static Connection getConnection(String jdbcurl)\r\n\r\npublic static Connection getConnection(String jdbcurl, String user,\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 String password)\r\n\r\n \r\n

Interacting with the database<\/strong><\/p>\r\n \r\n

As mentioned in the above section there are mainly three \u00a0kinds \u00a0of \u00a0interactions. \u00a0Executing \u00a0SQL, executing pre-compiled SQL and executing stored-procedures and functions. \u00a0Let \u00a0us \u00a0look \u00a0at \u00a0how \u00a0we use the JDBC API to carry out each of these tasks.<\/p>\r\n \r\n

Executing SQL using Statement<\/strong><\/p>\r\n \r\n

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:<\/span><\/p>\r\n \r\n\r\nStatement createStatement()<\/span>\r\n\r\nStatement createStatement(int resultSetType, int resultSetConcurrency)<\/span>\r\n\r\n \r\n

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 \u00a0using \u00a0this \u00a0statement. \u00a0The valid values for resultSetType are:<\/span><\/p>\r\n \r\n\r\nResultSet.TYPE_FORWARD_ONLY ResultSet.TYPE_SCROLL_SENSITIVE<\/span>\r\n\r\nResultSet.TYPE_SCROLL_INSENSITIVE<\/span>\r\n\r\n \r\n\r\nThe valid values of resultSetConcurrency are:<\/span>\r\n\r\n \r\n\r\nResultSet.CONCUR_READ_ONLY ResultSet.CONCUR_UPDATABLE<\/span>\r\n\r\n \r\n

By default the ResultSet type is forward only and read only. Let \u00a0us \u00a0look \u00a0at \u00a0how \u00a0to \u00a0execute \u00a0an \u00a0SQL. Once we have an instance of\u00a0 Statement. The Statement interface\u00a0 has the following \u00a0methods\u00a0\u00a0 for executing a SQL.<\/span><\/p>\r\n \r\n\r\nBoolean execute(String sql) int executeUpdate(String sql)<\/span>\r\n\r\nResultSet executeQuery(String sql)<\/span>\r\n\r\n \r\n

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.<\/span><\/p>\r\n \r\n

The instance of Statement can be used for executing multiple SQL \u00a0statements \u00a0in \u00a0a \u00a0batch. \u00a0The Statement interface has methods to \u00a0manage \u00a0SQL \u00a0statements \u00a0into \u00a0a \u00a0batch, \u00a0and \u00a0a \u00a0method \u00a0to \u00a0execute all the SQL statements which have been added into the batch. The following \u00a0methods \u00a0are \u00a0used \u00a0for batch \u00a0execution.<\/span><\/p>\r\n \r\n

public void addBatch(String sql) public void clearBatch( )<\/span><\/p>\r\n

public int[] executeBatch( )<\/span><\/p>\r\n \r\n

The size of the int [] returned by executeBatch() method is the number of \u00a0SQL \u00a0statements \u00a0in \u00a0the 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.\u00a0<\/span><\/p>\r\n \r\n

Executing a precompiled SQL using PreparedStatement<\/strong><\/p>\r\n \r\n

An instance of Statement is used to execute any kind of SQL. There is a \u00a0sub-interface \u00a0of \u00a0Statement 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\u00a0\u00a0 interface:<\/span><\/p>\r\n \r\n

PreparedStatement prepareStatement(String sql)<\/span><\/p>\r\n

P<\/span>reparedStatement prepareStatement(String sql, int rstype, int rsconcur)<\/span><\/p>\r\n \r\n

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 \u2019 ?\u2019 character within the SQL string passed as parameter. These place holders are referred to as parameters for the PreparedStatement.<\/span><\/p>\r\n \r\n

PreparedStatement ps = con.prepareStatement(\"select * from account where acno=? and type = ?\");<\/span><\/p>\r\n

PreparedStatement ps = con.prepareStatement(\"insert into account values(?,?,?,?)\");<\/span><\/p>\r\n \r\n

The first \u2019 ?\u2019 in the SQL string is the first parameter of the PreparedStatement, and so on. So, \u00a0the number of parameters in a\u00a0 PreparedSTatement will be\u00a0 the number of \u2019 ?\u2019\u00a0 characters used in the SQL string. The parameters can only be used in place for values in the SQL statement. These \u00a0parameter values need to be setup before the precompiled SQL corresponding to the PreparedStatement can be executed.<\/span><\/p>\r\n \r\n

Now the PreparedStatement interface has additional methods for executing the SQL. These are \u00a0the same three methods as in Statement interface, \u00a0but \u00a0without \u00a0any \u00a0parameters. \u00a0In \u00a0Statement \u00a0interface the parameter\u00a0 to the\u00a0 three execute methods was\u00a0 the SQL to be\u00a0 executed. In case\u00a0 of PreparedStatement this has already been specified at the time of creating the instance using the parameter in \u00a0the \u00a0prepareStatement() \u00a0method.\u00a0 So,\u00a0 the \u00a0methods\u00a0 for \u00a0executing\u00a0 the \u00a0precompiled \u00a0SQL \u00a0in a PreparedStatement are:<\/span><\/p>\r\n \r\n

boolean execute() int executeUpdate()<\/span><\/p>\r\n

ResultSet executeQuery()<\/span><\/p>\r\n \r\n

Executing Stored Procedures and functions using CallableStatement<\/strong><\/p>\r\n \r\n

There is also\u00a0 a sub-interface\u00a0 of PreparedStatement\u00a0 called the CallableStatement, this\u00a0 is used to execute stored procedures and functions. The instance of CallableStatement is created by the method on Connection as<\/span><\/p>\r\n

CallableStatement prepareCall(String callstatement)<\/span><\/p>\r\n

CallableStatement prepareCall(String callstatement,int rstype,int rsconcur)<\/span><\/p>\r\n \r\n

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 \u00a0the syntax for the callstatement is:<\/span><\/p>\r\n \r\n

{ [?] = call <proc-name or function name> (?,...} }<\/span><\/p>\r\n

if it is a stored procedure which is to be invoked we create CallableStatement as<\/span><\/p>\r\n

CallableStatement cs = con.prepareCall(\"{ call myproc(?,?,?) }\");<\/span><\/p>\r\n \r\n

to execute a stored procedure or<\/span><\/p>\r\n \r\n

CallableStatement cs = con.prepareCall(\"{ ? = call myfunctino(?, ?, ?) }\");<\/span><\/p>\r\n \r\n

to execute a function, which returns some value.<\/span><\/p>\r\n\r\n<\/div>\r\n

In Summary:<\/strong><\/p>\r\n\r\n