1. What are the different types of JDBC Drivers?

Type 1:
A JDBC-ODBC bridge provides JDBC API access via one or more ODBC drivers. Note that some ODBC native code and in many cases native database client code must be loaded on each client machine that uses this type of driver. Hence, this kind of driver is generally most appropriate when automatic installation and downloading of a Java technology application is not important.
Type 2:
A native-API partly Java technology-enabled driver converts JDBC calls into calls on the client API for Oracle, Sybase, Informix, DB2, or other DBMS. Note that, like the bridge driver, this style of driver requires that some binary code be loaded on each client machine.
Type 3:
A net-protocol fully Java technology-enabled driver translates JDBC API calls into a DBMS-independent net protocol which is then translated to a DBMS protocol by a server. This net server middleware is able to connect all of its Java technology-based clients to many different databases. The specific protocol used depends on the vendor. In general, this is the most flexible JDBC API alternative. It is likely that all vendors of this solution will provide products suitable for Intranet use. In order for these products to also support Internet access they must handle the additional requirements for security, access through firewalls, etc., that the Web imposes. Several vendors are adding JDBC technology-based drivers to their existing database middleware products.
Type 4:
A native-protocol fully Java technology-enabled driver converts JDBC technology calls into the network protocol used by DBMSs directly. This allows a direct call from the client machine to the DBMS server and is a practical solution for Intranet access. Since many of these protocols are proprietary the database vendors themselves will be the primary source for this style of driver. Several database vendors have these in progress.

2. What are the Isolation levels in JDBC transaction ?

We can set the Isolation levels by using the following methods in JDBC.

myConnection.getTransactionIsolation();
myConnection.setTransactionIsolation(intValue);

myConnection.setTransactionIsolation(TRANSACTION_SERIALIZABLE);
this line will set serializable as transaction isolation level.

public static final int TRANSACTION_NONE
public static final int TRANSACTION_READ_UNCOMMITTED
public static final int TRANSACTION_READ_COMMITTED
public static final int TRANSACTION_REPEATABLE_READ
public static final int TRANSACTION_SERIALIZABLE

these values are defined in java.sql.Connection interface, refer to sun docs.

3. How do you implement the Connection Pooling in Java?

After this question is raised we will get 2 questions in mind?

What is connection pooling ?
What is instance pooling ?
Why should we go for instance pooling ?

Creation of Database Connection is an expensive task?

First, you need to establish a connection with the DBMS you want to use. Typically, a JDBC? application connects to a target data source using one of two mechanisms:

• DriverManager:   This fully implemented class requires an application to load a specific driver, using a hardcoded URL. As part of its initialization, the DriverManager class attempts to load the driver classes referenced in the jdbc.drivers system property. This allows you to customize the JDBC Drivers used by your applications.


• DataSource:   This interface is preferred over DriverManager because it allows details about the underlying data source to be transparent to your application. A DataSource object's properties are set so that it represents a particular data source.

Establishing a connection involves two steps: Loading the driver, and making the connection.

Using a DataSource Object for a connection

Using a DataSource object increases application portability by making it possible for an application to use a logical name for a data source instead of having to supply information specific to a particular driver. The following example shows how to use a DataSource to establish a connection:

You can configure a DataSource using a tool or manually. For example, Here is an example of a DataSource lookup:

 InitialContext ic = new InitialContext(); 
 DataSource ds = ic.lookup("java:comp/env/jdbc/myDB");
 Connection con = ds.getConnection();
 DataSource ds = (DataSource) org.apache.derby.jdbc.ClientDataSource()
 ds.setPort(1527);
 ds.setHost("localhost");
 ds.setUser("APP")
 ds.setPassword("APP");
 
Connection con = ds.getConnection(); 

DataSource implementations must provide getter and setter methods for each property they support. These properties typically are initialized when the DataSource object is deployed.

VendorDataSource vds = new VendorDataSource();
vds.setServerName("my_database_server");
String name = vds.getServerName();

4. What is the best order in which a JDBC driver can be choosen?

Its 4213

5. What is the difference between getting a connection from DataSource and DriverManger?

DataSource and DriverManager are the two basic ways to connect to a database in a JEE application.
The DriverManager is older facility, the DataSource is newer.

It is recommended to use the new DataSource facility to connect to databases and other resources.

DriverManager will take time as it creates the connection whenever you require.
It involves loading the driver class and registering the driver. then it will creates the connection.
   

Class.forName("com.mysql.jdbc.Driver");
Connection    myConnection =  DriverManager.getConnection(prop.getProperty("DataBaseUrl"),prop.getProperty("user"),prop.getProperty("password"));
               

Using DataSource you can get connection from connection pool. High trafic enterprise applications need
fine tuned connection pools. you can implement Apache Commons DBCP, or C3P0 Connection pools.
C3P0 additionally pool statements also, apart from connection pool.


                    BasicDataSource dataSource = new org.apache.commons.dbcp.BasicDataSource();
                    ((BasicDataSource) dataSource).setDriverClassName(prop.getProperty("driverClassName"));
                    ((BasicDataSource) dataSource).setUrl(prop.getProperty("keyClockUrl"));
                    ((BasicDataSource) dataSource).setUsername(prop.getProperty("user"));
                    ((BasicDataSource) dataSource).setPassword(prop.getProperty("password"));
                    ((BasicDataSource) dataSource).setMaxActive(Integer.valueOf(prop.getProperty("initialSize")));
                    ((BasicDataSource) dataSource).setMaxActive(Integer.valueOf(prop.getProperty("minIdle")));
                    ((BasicDataSource) dataSource).setMaxActive(Integer.valueOf(prop.getProperty("maxActive")));
                    ((BasicDataSource) dataSource).setMaxIdle(Integer.valueOf(prop.getProperty("maxIdle")));
                    ((BasicDataSource) dataSource).setMaxWait(Integer.valueOf(prop.getProperty("maxWait")));
                    ((BasicDataSource) dataSource).setDefaultAutoCommit(Boolean.getBoolean(prop.getProperty("defaultAutoCommit")));
                    ((BasicDataSource) dataSource).setDefaultReadOnly(Boolean.getBoolean(prop.getProperty("defaultReadOnly")));
                   
                    myConnection =  dataSource.getConnection();


                   
 private static ComboPooledDataSource cpds = new ComboPooledDataSource();
     cpds.setDriverClass("com.mysql.jdbc.Driver");
            cpds.setJdbcUrl("jdbc:mysql://111.xx.xxx.xxx:3306/clock_db");
            cpds.setUser("xxxxx");
            cpds.setPassword("xxxxxx");
            cpds.setInitialPoolSize(15);
            cpds.setMinPoolSize(25);
            cpds.setMaxPoolSize(50);
            cpds.setMaxStatements(15);
           cpds.setAcquireRetryAttempts(2);
            cpds.setPrivilegeSpawnedThreads(true);
            cpds.setContextClassLoaderSource("library");
            cpds.setTestConnectionOnCheckin(true);
            cpds.setTestConnectionOnCheckout(false);
            cpds.setMaxConnectionAge(30);
     
        if (cpds.getNumBusyConnectionsAllUsers() >= cpds.getMaxPoolSize()) {
   
         
          //hardReset() immediately closes all Connections managed by the DataSource, including those that are currently checked out,
          //bringing the DataSource back to the state it was in before the first client called getConnection().
          //cpds.hardReset();
         
          System.out.println("SOFT RESET CP");
          // Connections aren't close()ed
          cpds.softResetAllUsers();
       
          } 
         
         
        //create connection and do the stuff
        Connection myConnection = C3poDataSource.getConnection();
   

6. How many times you have written Class.forName ("DriverName") in your Project ?

I will write it only once...thatz the good style...

7. Can we make a database connection from HTML ?

No. HTML is client side only. Its parsed at the client side by the Web Browser. The server side scripting languages are used to deal with databases and other resources. If it is possible we need not learn JSP, PHP, ASP, Cold Fusion, CGI Scripting, PERL Scripting, RUBY, PYTHON etc and etc?

8. I have 1000 records in result set, how to get first 100 records from result set, because result set fetch all 1000 records at a time ?

9. How do you find the size of the resultset in JDBC ?

To find the size of the resultset you should traverse or loop through from that resultset atleast once. There is no direct method or function to find the size of the resultset in JDBC API. They can provide that method like ResultSet.getSize() or ResultSet.size() but after using that method you will be at the end of that resultset. That is the problem.You should write a separate query like select count(*) from table_name; to find the size of the result set.
For more information follow the link
http://java.sun.com/j2se/1.4.2/docs/guide/jdbc/getstart/resultset.html

6.1.4 Determining the Number of Rows in a Result Set

With the new cursor movement methods, it is easy to see how many rows a scrollable ResultSet object contains. All that is necessary is to go to the last row of the result set and get the number of that row. In the following example, rs will have one row for each employee.

ResultSet rs = stmt.executeQuery("SELECT LAST_NAME, FIRST_NAME FROM EMPLOYEES");

rs.last();

int numberOfRows = rs.getRow();

System.out.println("XYZ, Inc. has "
+ numberOfRows + " employees");
rs.beforeFirst();
while (next()) {
. . . // retrieve first and last names of each employee

}

Though not as convenient, it is also possible to find out how many rows a nonscrollable result set has. The following example shows one way to determine the number of rows.

ResultSet rs = stmt.executeQuery(
"SELECT COUNT(*) FROM EMPLOYEES");
rs.next();
int count = rs.getInt(1);
System.out.println("XYZ, Inc. has " + count 
+ " employees");

ResultSet rs2 = stmt.executeQuery(
"SELECT LAST_NAME, FIRST_NAME FROM EMPLOYEES");
while (rs2.next()) {

. . . // retrieve first and last names of each employee

}

With the scrollable result set, the cursor was just repositioned to start iterating through the same result set to retrieve its data. In the preceding example, however, one query is needed to get the count, and another query is needed to get a result set with the data that is desired. Both queries must, of course, produce result sets of the same size for the count to be accurate.

A second way to determine the number of rows in a forward-only result set is to iterate through the result set, incrementing a variable with each iteration, which is shown in the following example. Because an application can iterate through a forward-only result set just once, the same query needs to be executed twice. In the iteration through the first rs, the number of rows is counted; in the iteration through the second rs, the data is retrieved.

ResultSet rs = stmt.executeQuery(

"SELECT LAST_NAME, FIRST_NAME FROM EMPLOYEES");

int count = 0;

while (rs.next()) {

count++;

}

System.out.println("Company XYZ has " + count " employees.");

rs = stmt.executeQuery(

"SELECT LAST_NAME, FIRST_NAME FROM EMPLOYEES");

while (rs.next()) {

. . . // retrieve first and last names of each employee

}

10. What is ODBC ?

ODBC stands for Open Data Base Connectivity. Using a ODBC Driver you can connect to any database.