Complete reference to core java interview questions basic to advanced level : SE to Team Lead level Part - 7

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

JDBC Application

JDBC Type II Driver

DBMS Client libraries (native)

DBMS Server libraries (native)

DBMS

JDBC API

SP API

SP N/W Libraries

OCI Libraries

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 :

JDBC Application

JDBC Type III Driver

Middleware Listener

DBMS Interface Client

DBMS Interface Server Listener

JDBC API

Net protocol

OCI Libraries

DBMS

DBMS API

                 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

DBMS Interface Server Listener

JDBC Application

JDBC Type IV Driver

JDBC API

DBMS API

DBMS

Native Protocol

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 –

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