Java Data Base Connectivity

How to Interact with DB?

           Generally every DB vendor provides a User Interface through which we can easily execute SQL query’s and get the result (For example Oracle Query Manager for Oracle, and TOAD (www.quest.com) tool common to all the databases). And these tools will help DB developers to create database. But as a programmer we want to interact with the DB dynamically to execute some SQL queries from our application (Any application like C, C++, JAVA etc), and for this requirement DB vendors provide some Native Libraries (Vendor Specific) using this we can interact with the DB i.e. If you want to execute some queries on Oracle DB, oracle vendor provides an OCI (Oracle Call Interface) Libraries to perform the same.

About ODBC

What is ODBC

     ODBC (Open Database Connectivity) is an ISV (Independent software vendor product) composes of native API to connect to different databases through via a single API called ODBC.

Open Database Connectivity (ODBC) is an SQL oriented application programming interface developed by in collaboration with IBM and some other database vendors.

ODBC comes with Microsoft products and with all databases on Windows OS.

ODBC Architecture

Advantages

·         Single API (Protocol) is used to interact with any DB

·         Switching from one DB to another is easy

·         Doesn’t require any modifications in the Application when you want to shift from one DB to other.

What for JDBC?

        As we have studied about ODBC and is advantages and came to know that it provides a common API to interact with any DB which has an ODBC Service Provider’s Implementation written in Native API that can be used in your applications.

   If an application wants to interact with the DB then the options which have been explained up to now in this book are:

1. Using Native Libraries given by the DB vendor
2. Using ODBC API

And we have listed there Advantages and Disadvantages.

   But if the application is a JAVA application then the above given options are not recommended to be used due to the following reasons

1. Native Libraries given by DB vendor

a.    Application becomes vendor dependent and

b.    The application has to use JNI to interact with Native Lib which may cause serious problem for Platform Independency in our applications.

2. And the second option given was using ODBC API which can solve the 1.a problem but again this ODBC API is also a Native API, so we have to use JNI in our Java applications which lead to the 1.b described problem.

And the answer for these problems is JDBC (Java Data Base Connectivity) which provides a common Java API to interact with any DB.

What is JDBC

       As explained above JDBC standards for Java Data Base Connectivity. It is a specification given by Sun Microsystems and standards followed by X/Open SAG (SQL Access Group) CLI (Call Level Interface) to interact with the DB.

Java programing language methods. The JDBC API provides database-independent connectivity between the JAVA Applications and a wide range of tabular data bases. JDBC technology allows an application component provider to:

·         Perform connection and authentication to a database server

·         Manage transactions

·         Moves SQL statements to a database engine for preprocessing and execution

·         Executes stored procedures

·         Inspects and modifies the results from SELECT statements

JDBC API

JDBC API is divided into two parts

1. JDBC Core API
2. JDBC Extension or Optional API

JDBC Core API (java.sql package)

This part of API deals with the following futures

1.    Establish a connection to a DB

2.    Getting DB Details

3.    Getting Driver Details

4.    maintaining Local Transaction

5.    executing query’s

6.    getting result’s (ResultSet)

7.    preparing pre-compiled SQL query’s and executing

8.    executing procedures & functions

JDBC Ext OR Optional API (javax.sql package)

      This part of API deals with the following futures

1.    Resource Objects with Distributed Transaction Management support

2.    Connection Pooling.

These two parts of Specification are the part of J2SE and are inherited into J2EE i.e. this specification API can be used with all the component’s given under J2SE and J2EE.

JDBC Architecture:

                  In the above show archetecture diagram the JDBC Driver forms an abstraction layer between the JAVA Application and DB, and is implemented by 3^rd party vendors or a DB Vendor. But whoever may be the vendor and what ever may be the DB we need not to worry will just us JDCB API to give instructions to JDBC Driver and then it’s the responsibility of JDBC Driver Provider to convert the JDBC Call to the DB Specific Call.

And this 3^rd party vendor or DB vendor implemented Drivers are classified into 4-Types namely

Types Of Drivers :

1. Type-1 (JDBC ODBC-Bridge Driver) JDBC-ODBC Bridge Driver
2. Type-2 (Java-Native API Driver)     Native API Partly JAVA Driver (Thick Driver)
3. Type-3 (Java Net Protocol Driver)    Intermediate DataBase Access Server
4. Type-4 (Java Native Protocol driver) Pure JAVA Driver (Thin driver)

Type-1 : JDBC-ODBC Bridge Driver :

                              Since ODBC is written in C-language using pointers, so JAVA does’t support pointers, a java program can’t communate directly with the DataBase.  The JDBC-ODBC bridge drivertransulates JDBC API calls to ODBC API calls.

Architecture

This type of Driver is designed to convert the JDBC request call to ODBC call and ODBC response call to JDBC call.

        The JDBC uses this interface in order to communicate with the database, so neither the database nor the middle tier need to be Java compliant. However ODBC binary code must be installed on each client machine that uses this driver. This bridge driver uses a configured data source.

Advantages

• Simple to use because ODBC drivers comes with DB installation/Microsoft front/back office product installation
• JDBC ODBC Drivers comes with JDK software

Disadvantages

·         More number of layers between the application and DB. And more number of API conversions leads to the downfall of the performance.

·         Slower than type-2 driver

Where to use?

   This type of drivers are generaly used at the development time to test your application’s.

Because of the disadvantages listed above it is not used at production time. But if we are not available with any other type of driver implementations for a DB then we are forced to use this type of driver (for example Microsoft Access).

Examples of this type of drivers

JdbcOdbcDriver from sun

Sun’s JdbcOdbcDriver is one of type-1 drivers and comes along with sun j2sdk (JDK).

Setting environment to use this driver

1. Software
   ODBC libraries has to be installed.
2. classpath 
   No additional classpath settings are required apart from the runtime jar (c:\j2sdk1.4\jre\lib\rt.jar) which is defaultly configured.
3. Path
   No additional path configuration is required.

How to use this driver

1. Driver class name  à   sun.jdbc.odbc.JdbcOdbcDriver
2. Driver URL   à dbc:odbc:<DSN>
   here <DSN> (Data Source Name) is an ODBC datasource name which is used by ODBC driver to locate one of the ODBC Service Provider implementation API which can in-turn connect to DB.

Steps to create <DSN>

1. run ‘Data Sources (ODBC)’ from Control Panal\Administrative Tools\
   (for Windows 2000 server/2000 professional/XP)
   run ‘ODBC Data Sources’ from Control Panel\

2. click on Add button available on the above displayed screen. this opens a new window titled ‘Create New Data Source’ which displays all the available DB’s lable DB’s ODBC drivers currently installed on your system.

3. Select the suitable driver and click on Finish

4. Give the required info to the driver (like username, service id etc)

Type-2 :  Native API Partly JAVA Driver (Thick Driver) :

                                       JDBC Database calls are translated into Vendor-specific API calls. The database will process the request and send the results back through API to JDBC Driver – this will translate  the results to the JDBC standard and return them to the Java application.

     The Vendor specific language API must be installed on every client that runs the JAVA application.         

Architecture

This driver converts the JDBC call given by the Java application to a DB specific native call (i.e. to C or C++) using JNI (Java Native Interface).

Advantages :Faster than the other types of drivers due to native library participation in socket programing.     

Disadvantage : DB spcifiic native client library has to be installed in the client machine.

·         Preferablly work in local network environment because network service name must be configured in client system

Where to use?

   This type of drivers are suitable to be used in server side applications.

 Not recommended to use with the applications using two tire model (i.e. client and database layer’s) because in this type of model client used to interact with DB using the driver and in such a situation the client system sould have the DB native library.

Examples of this type of drivers

1. OCI 8 (Oracle Call Interface) for Oracle implemented by Oracle Corporation.

Setting environment to use this driver

·         Software: Oracle client software has to be installed in client machine

·         classpath             à       %ORACLE_HOME%\ora81\jdbc\lib\classes111.zip

·         path                      à       %ORACLE_HOME%\ora81\bin

How to use this driver

• Driver class name   à         oracle.jdbc.driver.OracleDriver

• Driver URL                à         jdbc:oracle:oci8:@TNSName

Note: TNS Names of Oracle is available in Oracle installed folder %ORACLE_HOME%\Ora81\network\admin\tnsnames.ora

2. Weblogic Jdriver for Oracle implemented by BEA Weblogic:

Setting environment to use this driver

·         Oracle client software has to be installed in client machine

·         weblogicoic dll’s has to be set in the path

·         classpath             à       d:\bea\weblogic700\server\lib\weblogic.jar

·         path                      à       %ORACLE_HOME%\ora81\bin;
 d:\bea\weblogic700\server\bin\<subfolder><sub folder> is

o    oci817_8 if you are using Oracle 8.1.x

o    oci901_8 for Oracle 9.0.x

o    oci920_8 for Oracle 9.2.x

How to use this driver

• Driver class name   à         weblogic.jdbc.oci.Driver

• Driver URL                à         jdbc:weblogic:oracle:HostName

Type-3 Intermediate DataBase Access Server :

                                 Type-3 Driver uses an Intermediate(middleware) database driver that has the ability to connect multiple JAVA clients to multiple database servers.

   Client connect to the Databse server via an Intermediate server component (such as listener) that acts as a gateway for multple database servers.

   Bea weblogic includes Type-3 Driver.

Architecture :

                This type of drivers responsibility is to convert JDBC call to Net protocol (Middleware listener dependent) format and redirect the client request to Middleware Listener and middleware listener inturn uses type-1, type-2 or type-4 driver to interact with DB.

Advantages:

• It allows the flexibility on the architecture of the application.
• In absence of DB vendor supplied driver we can use this driver
• Suitable for Applet clients to connect DB, because it uses Java libraries for communication between client and server.

Disadvantages:

• From client to server communication this driver uses Java libraries, but from server to DB connectivity this driver uses native libraries, hence number of API conversion and layer of interactions increases to perform operations that leads to performance deficit.
• Third party vendor dependent and this driver may not provide  suitable driver for all DBs

Where to use?

• Suitable for Applets when connecting to databases

Examples of this type of drivers:

1. IDS Server (Intersolv) driver available for most of the Databases

Setting environment to use this driver

·         Software: IDS software required to be downloaded from the following URL

[ http://www.idssoftware.com/idsserver.html -> Export Evaluation ]

·         classpath             à       C:\IDSServer\classes\jdk14drv.jar

·         path                      à      

How to use this driver

• Driver class name   à         ids.sql.IDSDriver

• Driver URL                à         jdbc:ids://localhost:12/conn?dsn='IDSExamples'

Note: DSN Name must be created in ServerDSN

Type-4  Pure JAVA Driver (Thin driver) :

                             Type-4 Driver  translates JDBC-API calls to direct network calls using vendor specific networking protocols by making direct server connections with the database.

Architecture

This type of driver converts the JDBC call to a DB defined native protocol.

Advantage

• Type-4 driver are simple to deploy since there is No client native libraries required to be installed in client machine
• Comes with most of the Databases

Disadvantages:

• Slower in execution compared with other JDBC Driver due to Java libraries are used in socket communication with the DB

Where to use?

• This type of drivers are sutable to be used with server side applications, client side application and Java Applets also.

Examples of this type of drivers

1) Thin driver for Oracle implemented by Oracle Corporation
Setting environment to use this driver

·         classpath             à       %ORACLE_HOME%\ora81\jdbc\lib\classes111.zip

How to use this driver

• Driver class name   à         oracle.jdbc.driver.OracleDriver

• Driver URL                à         jdbc:oracle:thin:@HostName:<port no>:<SID>

<port no>  à 1521

<SID> -> ORCL

2) MySQL Jconnector for MySQL database

Setting environment to use this driver

·         classpath             à       C:\mysql\mysql-connector-java-3.0.8-stable\mysql-connector-java-3.0.8-stable-bin.jar

How to use this driver

• Driver class name   à         com.mysql.jdbc.Driver

• Driver URL                à         jdbc:mysql:///test

Chapter 3 [JDBC Core API]

In this chapter we are going to discuss about 3 versions of JDBC: JDBC 1.0, 2.0 and 3.0

Q) How JDBC API is common to all the Databases and also to all drivers?

A) Fine! The answer is JDBC API uses Factory Method and Abstract Factory Design pattern implementations to make API common to all the Databases and Drivers. In fact most of the classes available in JDBC API are interfaces, where Driver vendors must provide implementation for the above said interfaces.

Q) Then how JDBC developer can remember or find out the syntaxes of vendor specific classes?

A) No! developer need not have to find out the syntaxes of vendor specific implementations why because DriverManager is one named class available in JDBC API into which if you register Driver class name, URL, user and password, DriverManager class in-turn brings us one Connection object.

Q) Why most of the classes given in JDBC API are interfaces?

A) Why abstract class and abstract methods are?

Abstract class forces all sub classes to implement common methods whichever are required implementations. Only abstract method and class can do this job. That’s’ why most part of the JDBC API is a formation of interfaces.

JDBC API comes in 2 packages

java.sql.*

javax.sql.*

First of all I want to discuss briefly about all the list of interfaces and classes available in java.sql. package

Interfaces index

Driver

Every JDBC Driver vendor must one sub class of this class for initial establishment of Connections. DriverManager class need to be first registered with this class before accepting URL and other information for getting DB connection.

Method index

• Connection connect(String url, Properties info)
  This method takes URL argument and user name & password info as Properties object
• boolean acceptURL(String url)
  This method returns boolean value true if the given URL is correct, false if any wrong in URL
• boolean jdbcComplaint()
  JDBC compliance requires full support for the JDBC API and full support for SQL 92 Entry Level. It is expected that JDBC compliant drivers will be available for all the major commercial databases.

Connection

       Connection is class in-turn holds the TCP/IP connection with DB. Functions available in this class are used to manage connection live-ness as long as JDBC application wants to connect with DB. The period for how long the connection exists is called as Session. This class also provides functions to execute various SQL statements on the DB. For instance the operations for DB are mainly divided into 3 types

• DDL (create, alter, and drop)
• DML (insert, select, update and delete)
• DCL (commit, rollback)  and also
• call function_name (or) call procedure_name

Method Index

• Statement createStatement()
• PreparedStatement prepareStatement(String preSqlOperation)
• CallableStatement prepareCall(String callToProc())

Statement

       Statement class is the super class in its hierarchy. Provides basic functions to execute query (select) and non-related (create, alter, drop, insert, update, delete) query operations.

 Method Index

• int executeUpdate(String sql)

This function accepts non-query based SQL operations; the return value int tells that how many number of rows effected/updated by the given SQL operation.

• ResultSet executeQuery(String sql)

This function accepts SQL statement SELECT and returns java buffer object which contains temporary instance of SQL structure maintaining all the records retrieved from the DB. This object exists as long as DB connection exist.

• boolean execute()

This function accepts all SQL operations including SELECT statement also.

PreparedStatement

       PreparedStatement class is sub classing from Statement class. While connection class prepareStatement function is creating one new instance this class, function takes one String argument that contains basic syntax of SQL operation represented with “?” for IN parameter representation. In the further stages of the JDBC program, programmer uses setXXX(int index, datatype identifier) to pass values into IN parameter and requests exdcute()/ exuecteUpdate() call.

Method Index

• setInt(int index, int value) – similar functions are provided for all other primitive parameters
• setString(int index, String value)
• setObject(int index, Object value)
• setBinaryStream(int index, InputStream is, int length)

CallableStatement

ResultSet              ResultSetMetaData                 DatabaseMetaData

BLOB                    CLOB                                     REF        

SavePoint              Struct

SQLInput               SQLOutput                           SQLData

 Class diagram required here

// TypeI DriverTest,java

package com.digitalbook.j2ee.jdbc;

import java.sql.*;

public class TypeIDriverTest

{

    Connection con;

    Statement stmt;

    ResultSet rs;

   public TypeIDriverTest ()

   {

      try {

// Load driver class into default ClassLoader

Class.forName ("sun.jdbc.odbc.JdbcOdbcDriver");

          // Obtain a connection with the loaded driver

con =DriverManager.getConnection ("jdbc:odbc:digitalbook","scott","tiger");

             URL String -   ("<protocol>:<subprotocol>:<subname>", "  ", "  " );  }

// create a statement   

st=con.createStatement();

//execute SQL query

rs =st.executeQuery ("select ename,sal from emp");

System.out.println ("Name                       Salary");

System.out.println ("--------------------------------");

while(rs.next())

{

System.out.println (rs.getString(1)+"                "+rs.getString(2));

}

rs.close ();

stmt.close ();     

con.close ();

}

catch(Exception e)

{

e.printStackTrace ();

}

          }

public static void main (String args[])

{

TypeIDriverTest demo=new TypeIDriverTest ();

}

 }

// TypeIIDriverTest,java

package com.digitalbook.j2ee.jdbc;

import java.sql.*;

public class TypeIIDriverTest

{

    Connection con;

    Statement stmt;

    ResultSet rs;

   public TypeIIDriverTest ()

   {

      try {

// Load driver class into default ClassLoader

Class.forName ("oracle.jdbc.driver.OracleDriver");

          // Obtain a connection with the loaded driver

con =DriverManager.getConnection ("jdbc:oracle:oci8:@digital","scott","tiger");

        // create a statement   

st=con.createStatement();

//execute SQL query

rs =st.executeQuery ("select ename,sal from emp");

System.out.println ("Name                       Salary");

System.out.println ("--------------------------------");

while(rs.next())

{

System.out.println (rs.getString(1)+"                "+rs.getString(2));

}

rs.close ();

stmt.close ();     

con.close ();

}

catch(Exception e)

{

e.printStackTrace ();

}

          }

public static void main (String args[])

{

TypeIIDriverTest demo=new TypeIIDriverTest ();

}

 }

Chapter 9  :       [javax.sql package]

This package supplements the java.sql package and is included as a part of JDK 1.4 version. This package mainly provides following features:

1. DataSource interface was introduced in substitution to DriverManager class for getting connection objects.
2. Connection Pooling

3.    Distributed TX management

4.    RowSets

    Applications can directly use DataSource and RowSet API but connection pooling and Distributed TX management APIs are used internally by the middle-tier infrastructure.

DataSource

        DataSource is an interface. Driver vendor will provide implementation for this interface (That means in case JDBC Driver Type II driver Oracle vendor for Oracle DB, Intersolv in case of IDSServer). This object is used to obtain connections into any type of JDBC program. Though DriverManager class is ideal for getting DB connection object, this class provides some extra features over DriverManager class:

·       Applications will obtain DB connection objects through via this factory class

·       DataSource object will be registered into JNDI, hence any application connected in the network can obtain this object by requesting JNDI API, DataSource class is having one method called getConnection() geives one Connection object

·       Application do not need to hard code a driver class

·       Changes can be made to a data source properties, which means that it is not necessary to make changes in application code when something about the data source or driver changes

·       Connection pooling and Distributed transactions are available through only the connection obtained from this object. Connection obtained through DriverManager class do not have this capability

DataSource interface is implemented by driver vendor. There are 3 types of implementations available:

1. Basic Implementation- Produces a standard connection object.
2. Connection Pooling Implementation- Produces a connection object that automatically participates in connection pooling. This implementation works with a middle-tier connection pooling manager.
3. Distributed transaction implementation- Produces a connection object that may be used for distributed transactions and almost always participates in connection pooling. This implementation works with a middle-tier transaction manager and almost always with a connection pool manager.

A driver that is accessed via a DataSource object does not register itself with the DriverManager. Rather, a DataSource object is retrieved though a lookup operation and then used to create a Connection object. With a basic implementation, the connection obtained through a DataSource object is identical to a connection obtained through the DriverManager facility.

Method Index

• Connection getConnection() – This function returns Connection object on demand of this method.
• Connection getConnection(String user, String pass) – This function returns Connection object on demand of this method by passing username and password.

Sub classes of this interface are

Type III Driver – IDSServer – Intersolv – ids.jdbc.IDSDataSource

Type III Driver – WebLogic – BEA – weblogic.jdbc.jta.DataSource – XA Support

Connection Pooling

          Connections made via a DataSource object that is implemented to work with a middle tier connection pool manager will participate in connection pooling. This can improve the performance dramatically because creating a new connection is very expensive.

Connection Pool provides following features:

• Substantial improvement in the performance of DB application can be accomplished by pre-caching the DB connection objects
• CPM supplied DB connections are remote enable
• CPM supplied DB connections are cluster aware
• CPM supplied DB connections supports DTM (distributed TXs)
• CPM supplied DB connections are not actual DB Connection objects, in turn they are remote object, hence even though client closes DB connection using con.close() the actual connection may not be closed instead RMI connection between client to CPM are closed
• CPM supplied DB connection objects are serializable, hence client from any where in the network can access DB connections

The classes and interfaces used for connection pooling are:

1.    ConnectionPoolDataSource

2.    PooledConnection

3.    ConnectionEvent

4.    ConnectionEventListener

       Connection Pool Manager resided on middle tier system uses these classes and interfaces behind the scenes. When the ConnectionPooledDataSource object is called on to create PooledConnection object, the connection pool manager will register as a ConnectionEventListener object with the new PooledConnection object. When the connection is closed or there is an error, the connection pool manager (being listener) gets a notification that includes a ConnectionEvent object.

Distributed Transactions

         As with pooled connections, connections made via data source object that is implemented to work with the middle tier infrastructure may participate in distributed transactions. This gives an application the ability to involve data sources on multiple servers in a single transaction.

The classes and interfaces used for distributed transactions are:

• XADataSource
• XAConnection

These interfaces are used by transaction manager; an application does not use them directly.

      The XAConnection interface is derived from the PooledConnection interface, so what applies to a pooled connection also applies to a connection that is part of distributed transaction. A transaction manager in the middle tier handles everything transparently. The only change in application code is that an application cannot do anything that would interfere with the transaction manager’s handling of the transaction. Specifically application cannot call the methods Connection.commit or Connection.rollback and it cannot set the connection to be in auto-commit mode.

      An application does not need to do anything special to participate in a distributed transaction. It simply creates connections to the data sources it wants to use via the DataSource.getConnection method, just as it normally does. The transaction manager manages the transaction behind the scenes. The XADataSource interface creates XAConnection objects, and each XAConnection object creates an XAResource object that the transaction manager uses to manage the connection.

Rowsets

     The RowSet interface works with various other classes and interfaces behind the scenes. These can be grouped into three categories.

1. Event Notification

o       RowSetListener
A RowSet object is a JavaBeans^TM component because it has properties and participates in the JavaBeans event notification mechanism. The RowSetListener interface is implemented by a component that wants to be notified about events that occur to a particular RowSet object. Such a component registers itself as a listener with a rowset via the RowSet.addRowSetListener method.

o       When the RowSet object changes one of its rows, changes all of it rows, or moves its cursor, it also notifies each listener that is registered with it. The listener reacts by carrying out its implementation of the notification method called on it.

o       RowSetEvent
As part of its internal notification process, a RowSet object creates an instance of RowSetEvent and passes it to the listener. The listener can use this RowSetEvent object to find out which rowset had the event.

2. Metadata

RowSetMetaData
This interface, derived from the ResultSetMetaData interface, provides information about the columns in a RowSet object. An application can use RowSetMetaData methods to find out how many columns the rowset contains and what kind of data each column can contain.

The RowSetMetaData interface provides methods for setting the information about columns, but an application would not normally use these methods. When an application calls the RowSet method execute, the RowSet object will contain a new set of rows, and its RowSetMetaData object will have been internally updated to contain information about the new columns.

3. The Reader/Writer Facility

        A RowSet object that implements the RowSetInternal interface can call on the RowSetReader object associated with it to populate itself with data. It can also call on the RowSetWriter object associated with it to write any changes to its rows back to the data source from which it originally got the rows. A rowset that remains connected to its data source does not need to use a reader and writer because it can simply operate on the data source directly.

RowSetInternal
By implementing the RowSetInternal interface, a RowSet object gets access to its internal state and is able to call on its reader and writer. A rowset keeps track of the values in its current rows and of the values that immediately preceded the current ones, referred to as the original values. A rowset also keeps track of (1) the parameters that have been set for its command and (2) the connection that was passed to it, if any. A rowset uses the RowSetInternal methods behind the scenes to get access to this information. An application does not normally invoke these methods directly.

RowSetReader
A disconnected RowSet object that has implemented the RowSetInternal interface can call on its reader (the RowSetReader object associated with it) to populate it with data. When an application calls the RowSet.execute method, that method calls on the rowset's reader to do much of the work. Implementations can vary widely, but generally a reader makes a connection to the data source, reads data from the data source and populates the rowset with it, and closes the connection. A reader may also update the RowSetMetaData object for its rowset. The rowset's internal state is also updated, either by the reader or directly by the method RowSet.execute.

RowSetWriter
A disconnected RowSet object that has implemented the RowSetInternal interface can call on its writer (the RowSetWriter object associated with it) to write changes back to the underlying data source. Implementations may vary widely, but generally, a writer will do the following:

§   Make a connection to the data source

§   Check to see whether there is a conflict, that is, whether a value that has been changed in the rowset has also been changed in the data source

§   Write the new values to the data source if there is no conflict

§   Close the connection

The RowSet interface may be implemented in any number of ways, and anyone may write an implementation. Developers are encouraged to use their imaginations in coming up with new ways to use rowsets.

Type III Driver – WebLogic – BEA – weblogic.jdbc.common.internal.ConnectionPool

Type III Driver – WebLogic – BEA – weblogic.jdbc.connector.internal.ConnectionPool

Type II & IV driver – Oracle DB - Oracle –

JDBC:

There are three types of statements in JDBC

Create statement : Is used to execute single SQL statements.

Prepared statement: Is used for executing parameterized quaries. Is used to run pre-compiled SEQL Statement.

Callable statement: Is used to execute stored procedures.

Stored Procedures: Is a group of SQL statements that perform a logical unit and performs a particular task.

    Are used to encapsulate a set operations or queries t execute on data.

execute()     – returns Boolean value

executeupdate( ) – returns resultset Object

executeupdate( ) – returns integer value

Loading the Driver:

  Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver”);

  Conn=DriverManager.getConnection(“jdbc:odbc:dsn”, “username”, “password”);

     ( ORACLE Driver )

  Class.forName(“Oracle.jdbc.driver.OracleDriver”);

 Conn=DriverManager.getConnection(“jdbc:oracle:thin:@192.168.1.105:1521:dbn”, “username”, “password”);

Data base connection:

Public static void main(String args[]);

Connection con;

Statement st;

Resultset rs;

try {                        // Getting all rows from Table

       Clas.forName(“sun.jdbc.odbc.jdbcodbc”);

  Conn=DriverManager.getConnction(“jdbc.odbc.dsn”, “username” , ”password”);

st = con.createstatement( );

rs = st.executestatement(“SELECT * FROM mytable”);

   while(rs.next());

      {

       String s= rs.getString(1);    or rs.setString(“COL_A”);

       int i = rs. getInt(2);

       Float f = rs.getfloat(3);

    Process(s,i,f);

     }

    catch(SQLException e)

     { }

//Getting particular rows from Table

st = con.createstatement( );

rs = st.executequery(“SELECT * FROM mytable WHERE COL A = “Prasad”);

      while(rs.next( ));

       {

        String s = rs.getString(1);

         Int i = rs.getint(2);

        Float f = rs.getfloat(3);

      Process(s,i,f);

       } 

         Catch(SQLException e);   {  }

//updating a row from table.

try {

st =  con.createstatement( );

int numupdated = st.executeupdate(“UPDATE mytable SET COL_A = “prasad”  WHERE  COL_B=”746”);

rs = st.executeupdate();

conn.close();  }

catch(SQLExceptione); {  }

// Receiving rows from table

try  {

      st = con.preparedstatement( );

   rs = st.execurtestatement(“SELECT * FROM mytable SET COL_A=?’);

  int colunm=1;

  rs.setString(colunm,”hari”);

rs = st.executeQuery( );

//update rwo from table

 st = con.createstatement( );

int numupdated = st.executeupdate(“UPDATE mytable SET COL_A =? WHERE COL_B=?”);

int column=1;

rs.setString(colunm,”Prasad”);

int column=2;

rs.setString(column,”746”);

int numupdated = st.executeupdate( );

}    catch(SqlException e);  {  }

//callable statement

try { 

     cst = con.preparecall(“{call add1(??,??)}”);

  cst.setint(1,a);

  cst.setint(2,b);

  cst.registerOurPrameter(1,Types.INTEGER);

  cst.executeQuery( );

 System.out.println(“rs.getString( )”);  }

Connection Pool with webLogic server :

 You can connect the database in your app using :

Class.forName(“weblogic.jdbc.oci.Driver”).newInstance();

Java.sql.Connection conn = Driver.connect(“jdbc:weblogic:Oracle:dbn”, ”username”, “password”);

    ( Or )

java.util.Properties prop = new java.util.Properties( );

prop.put(“user”, “hari”);

prop.put(“password”,”prasad”);

java.sql.Driver d = (java.sql.Driver)Class.forName(“weblogic.jdbc.oci.Driver”).newInstance( );

java.sql.Connection conn = d.connect(“jdbc:weblogic:Oracle:dbn”, prop);

public static void main(String args[]) throws Exception

{

 java.sql.Connection con=null;

 java.sql.satement st =null;

try {

 context ctx=null;

Hashtable ht = new Hashtable( );

ht.put(Context.INTIAL_CONTEXT_FACTORY,”weblogic:jndi:WLInitialContextFACTORY”);

ht.put(Context_PROVIDER_URL,”t3://Localhost:7001”);

//get a context from JNDI lookup

ctx = newIntialContext( ):

java.sql.Datasourse ds =(java.sql.DataSource)ctx.lookup(“OraclegbJNDI”);

con =ds.getConnection( );

System.out.Println(“Making Connection……”);

st = conn.createstatement( );

}

  finally {

       try {

                if(stmt !=null)

                       stmt.close( );

                 if(stmt !=null)

                        con.close( );  }    

What is a transaction

transaction is collection of logical operation that perform a task

Transaction should ACID properties.

A for Automicity

C for Consistency

I for Isolation

D for Durability.

A transaction can be termed as any operation such as storing, retrieving, updating or deleting records in the table that hits the database.

What is the purpose of setAutoCommit( )

It is set as 

ConnectionObject.setAutoComit();

after any updates through the program cannot be effected to the database.We have commit the transctions .For this puprpose we can set AutoCommit flag to Connection Object.

What are the three statements in JDBC & differences between them

which is used to run simple sql statements like select and update

2. PrepareStatment is used to run Pre compiled sql. 

3. CallableStatement is used to execute the stored procedures.

What is stored procedure. How do you create stored procedure ?

         Stored procedures is a group of SQL statements that performs a logical unit and performs a particular task.

               Stored procedures are used to encapsulate a set of operations or queries to execute on data.  

         Stored Procedure is a stored program in database, PL/SQL program is a Stored Procedure. Stored Procedures can be called from java by CallableStatement

          A precompiled collection of SQL statements stored under a name and processed as a unit. 

Stored procedures can: 

1.Accept input parameters and return multiple values in the form of output parameters to the calling procedure or batch.

2.Contain programming statements that perform operations in the database, including calling other procedures. 

3.Return a status value to a calling procedure or batch to indicate success or failure (and the reason for failure). 

What are batch updates?

Batch Update facility allows multiple update operations to be submitted to a database for processing at once. Using batch updates will improve the performance.

What is the difference between Resultset and Rowset

A RowSet is a disconnected, serializable version of a JDBC ResultSet. 

The RowSet is different than other JDBC interfaces in that you can write a RowSet to be vendor neutral. A third party could write a RowSet implementation that could be used with any JDBC-compliant database. The standard implementation supplied by Sun uses a ResultSet to read the rows from a database and then stores those rows as Row objects in a Vector inside the RowSet. In fact, a RowSet implementation could be written to get its data from any source. The only requirement is that the RowSet acts as if it was a ResultSet. Of course, there is no reason that a vendor couldn't write a RowSet implementation that is vendor specific. 

            The standard implementations have been designed to provide a fairly good range of functionality. The implementations provided are: 

CachedRowSetImpl - This is the implementation of the RowSet that is closest to the definition of RowSet functionality that we discussed earlier. There are two ways to load this RowSet. The execute ( ) method will load the RowSet using a Connection object. The populate( ) method will load the RowSet from a previously loaded ResultSet. 

WebRowSetImpl - This is very similar to the CachedRowSetImpl (it is a child class) but it also includes methods for converting the rows into an XML document and loading the RowSet with an XML document. The XML document can come from any Stream or Reader/Writer object. This could be especially useful for Web Services. 

JdbcRowSetImpl - This is a different style of implementation that is probably less useful in normal circumstances. The purpose of this RowSet is to make a ResultSet look like a JavaBean. It is not serializable and it must maintain a connection to the database. 

The remaining two implementations are used with the first three implementations: 

FilteredRowSetImpl - This is used to filter data from an existing RowSet. The filter will skip records that don't match the criteria specified in the filter when a next() is used on the RowSet. 

JoinRowSetImpl - This is used to simulate a SQL join command between two or more RowSet objects. 

What are the steps for connecting to the database using JDBC

Using DriverManager:

1. Load the driver class using class.forName(driverclass) and class.forName() loads the driver class and passes the control to DriverManager class

2. DriverManager.getConnection() creates the connection to the databse

 Using DataSource.

DataSource is used instead of DriverManager in Distributed Environment with the help of JNDI.

1. Use JNDI to lookup the DataSource from Naming service server.

3. DataSource.getConnection method will return Connection object to the database

What is Connection Pooling ?

      Connection pooling  is a cache of data base connections that is maintained in memory , so that the connections may be reuse.

        Connection pooling is a place where a set of connections are kept and are used by the different programers with out creating conncections to the database(it means there is a ready made connection available for the programmers where he can use). After using the connection he can send back that connection to the connection pool. Number of connections in connection pool may vary.

How do you implement Connection Pooling 

Connection Pooling can be implemented by the following way.

           * A javax.sql.ConnectionPoolDataSource interface that serves as a resource manager connection factory for pooled java.sql.Connection objects. Each database vendors provide the implementation for that interface.

              For example, the oracle vendors implementation is as follows:

oracle.jdbc.pool.oracleConnectionPoolDataSource Class.

• A javax.sql.PooledConnection interface encapsulates the physical connection for the database. Again, the vendor provides the implementation.

What Class.forName( ) method will do

      Class.forName() is used to load the Driver class which is used to connect the application with Database. Here Driver class is a Java class provided by Database vendor.

What is the difference between JDBC 1.0 and JDBC 2.0

     The JDBC 2.0 API includes many new features in the java.sql package as well as the new Standard Extension package, javax.sql. This new JDBC API moves Java applications into the world of heavy-duty database computing. New features in the java.sql package include support for SQL3 data types, scrollable result sets, programmatic updates, and batch updates. The new JDBC Standard Extension API, an integral part of Enterprise JavaBeans (EJB) technology, allows you to write distributed transactions that use connection pooling, and it also makes it possible to connect to virtually any tabular data source, including files and spread sheets. 

     The JDBC 2.0 API includes many new features like

1. Scrollable result sets
2. Batch updates
3. Connection Pooling
4. Distributed transactions
5. set autocomit ( )

What is JDBC?

JDBC is a layer of abstraction that allows users to choose between databases. It allows you to change to a different database engine and to write to a single API. JDBC allows you to write database applications in Java without having to concern yourself with the underlying details of a particular database.

What are the two major components of JDBC?

One implementation interface for database manufacturers, the other implementation interface for application and applet writers.

What is JDBC Driver interface?

The JDBC Driver interface provides vendor-specific implementations of the abstract classes provided by the JDBC API. Each vendors driver must provide implementations of the java.sql.Connection,Statement,PreparedStatement, CallableStatement, ResultSet and Driver.

 

What are the common tasks of JDBC?

Create an instance of a JDBC driver or load JDBC drivers through jdbc.drivers

Register a driver

Specify a database

Open a database connection

Submit a query

Receive results

What packages are used by JDBC?

There are 8 packages: java.sql.Driver, Connection,Statement, PreparedStatement, CallableStatement, ResultSet, ResultSetMetaData, DatabaseMetaData.

What are the flow statements of JDBC?

 A URL string -->getConnection-->DriverManager-->Driver-->Connection-->Statement-->executeQuery-->ResultSet.

1). Register the Driver

2) load the Driver

3)get the connection

4) create the statement

5) Execute the query

6) fetch the results with ResultSet

What are the steps involved in establishing a connection?

This involves two steps: (1) loading the driver and (2) making the connection.

How can you load the drivers?

Loading the driver or drivers you want to use is very simple and involves just one line of code. If, for example, you want to use the JDBC-ODBC Bridge driver, the following code will load it:

Eg.  Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

Your driver documentation will give you the class name to use. For instance, if the class name is jdbc.DriverXYZ , you would load the driver with the following line of code:

E.g.   Class.forName("jdbc.DriverXYZ");

What Class.forName will do while loading drivers?

     It is used to create an instance of a driver and register it with the DriverManager. When you have loaded a driver, it is available for making a connection with a DBMS.

How can you make the connection?

In establishing a connection is to have the appropriate driver connect to the DBMS. The following line of code illustrates the general idea:

E.g.

String url = "jdbc:odbc:Fred";

Connection con = DriverManager.getConnection(url, "Fernanda", "J8");

How can you create JDBC statements?

          A Statement object is what sends your SQL statement to the DBMS. You simply create a Statement object and then execute it, supplying the appropriate execute method with the SQL statement you want to send. For a SELECT statement, the method to use is executeQuery. For statements that create or modify tables, the method to use is executeUpdate. E.g. It takes an instance of an active connection to create a Statement object. In the following example, we use our Connection object con to create the Statement object stmt :

Statement stmt = con.createStatement();

How can you retrieve data from the ResultSet?

       First JDBC returns results in a ResultSet object, so we need to declare an instance of the class ResultSet to hold our results. The following code demonstrates declaring the ResultSet object rs.

E.g.

ResultSet rs = stmt.executeQuery("SELECT COF_NAME, PRICE FROM COFFEES");

Second:

String s = rs.getString("COF_NAME");

The method getString is invoked on the ResultSet object rs , so getString will retrieve (get) the value stored in the column COF_NAME in the current row of rs

What are the different types of Statements?

1. Create Statement  : For Simple statement used for static query.

2.Prepared Statement :For a runtime / dynamic query .Where String is a dynamic query you want to execute

3. Callable Statement (Use prepareCall) : //For Stored procedure Callable statement, where sql is stored procedure.

try

{

Connection conn = DriverManager.getConnection("URL",'USER"."PWD");

Statement stmt = conn.createStatement(); 

PreparedStatement pstmt = conn.prepareStatement(String sql);

CallableStatement cstmt = conn.prepareCall(String sql);

}

catch (SQLException ee)

{

ee.printStackTrace();

}

Don't forget all the above statements will throw the SQLException, so we need to use try catch for the same to handle the exception.

How can you use PreparedStatement?

This special type of statement is derived from the more general class, Statement. If you want to execute a Statement object many times, it will normally reduce execution time to use a PreparedStatement object instead. The advantage to this is that in most cases, this SQL statement will be sent to the DBMS right away, where it will be compiled. As a result, the PreparedStatement object contains not just an SQL statement, but an SQL statement that has been precompiled. This means that when the PreparedStatement is executed, the DBMS can just run the PreparedStatement 's SQL statement without having to compile it first.

E.g.       PreparedStatement updateSales = con.prepareStatement("UPDATE COFFEES SET SALES = ? WHERE COF_NAME LIKE ?");

How to call a Stored Procedure from JDBC?

The first step is to create a CallableStatement object. As with Statement an and PreparedStatement objects, this is done with an open Connection object. A CallableStatement object contains a call to a stored procedure;

E.g.

CallableStatement cs = con.prepareCall("{call SHOW_SUPPLIERS}");

ResultSet rs = cs.executeQuery();

How to Retrieve Warnings?

 SQLWarning objects are a subclass of SQLException that deal with database access warnings. Warnings do not stop the execution of an application, as exceptions do; they simply alert the user that something did not happen as planned. A warning can be reported on a Connection object, a Statement object (including PreparedStatement and CallableStatement objects), or a ResultSet object. Each of these classes has a getWarnings method, which you must invoke in order to see the first warning reported on the calling object

E.g.

SQLWarning warning = stmt.getWarnings();

if (warning != null) {

while (warning != null) {

System.out.println("Message: " + warning.getMessage());

System.out.println("SQLState: " + warning.getSQLState());

System.out.print("Vendor error code: ");

System.out.println(warning.getErrorCode());

warning = warning.getNextWarning();

}

}

How to Make Updates to Updatable Result Sets?

Another new feature in the JDBC 2.0 API is the ability to update rows in a result set using methods in the Java programming language rather than having to send an SQL command. But before you can take advantage of this capability, you need to create a ResultSet object that is updatable. In order to do this, you supply the ResultSet constant CONCUR_UPDATABLE to the createStatement method.

E.g.

Connection con = DriverManager.getConnection("jdbc:mySubprotocol:mySubName");

Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);

ResultSet uprs = ("SELECT COF_NAME, PRICE FROM COFFEES");