JDBC Resources and Connection Pool : Explain

JDBC Resources

A JDBC resource (data source) provides applications with a means of connecting to a database. Typically, the administrator creates a JDBC resource for each database accessed by the applications deployed in a domain. (However, more than one JDBC resource can be created for a database.)

 

JDBC Resources

To store, organize, and retrieve data, most applications use relational databases. J2EE applications access relational databases through the JDBC API.

To create a JDBC resource, specify a unique JNDI name that identifies the resource. (See the section JNDI Names and Resources.) Expect to find the JNDI name of a JDBC resource in java:comp/env/jdbc subcontext. For example, the JNDI name for the resource of a payroll database could be java:comp/env/jdbc/payrolldb. Because all resource JNDI names are in the java:comp/env subcontext, when specifying the JNDI name of a JDBC resource in the Admin Console, enter only jdbc/name. For example, for a payroll database specify jdbc/payrolldb.

JDBC Connection Pools

To create a JDBC resource, specify the connection pool with which it is associated. Multiple JDBC resources can specify a single connection pool.

A JDBC connection pool is a group of reusable connections for a particular database. Because creating each new physical connection is time consuming, the server maintains a pool of available connections to increase performance. When an application requests a connection, it obtains one from the pool. When an application closes a connection, the connection is returned to the pool.

The properties of connection pools can vary with different database vendors. Some common properties are the database’s name (URL), user name, and password.

How JDBC Resources and Connection Pools Work Together

To store, organize, and retrieve data, most applications use relational databases. Java EE applications access relational databases through the JDBC API. Before an application can access a database, it must get a connection.

At runtime, here’s what happens when an application connects to a database:

  1. The application gets the JDBC resource (data source) associated with the database by making a call through the JNDI API.

    Given the resource’s JNDI name, the naming and directory service locates the JDBC resource. Each JDBC resource specifies a connection pool.

  2. Via the JDBC resource, the application gets a database connection.

    Behind the scenes, the application server retrieves a physical connection from the connection pool that corresponds to the database. The pool defines connection attributes such as the database name (URL), user name, and password.

  3. Now that it’s connected to the database, the application can read, modify, and add data to the database.

    The applications access the database by making calls to the JDBC API. The JDBC driver translates the application’s JDBC calls into the protocol of the database server.

  4. When it’s finished accessing the database, the application closes the connection.

    The application server returns the connection to the connection pool. Once it’s back in the pool, the connection is available for the next application.

    Sun Java System JDBC Driver for Oracle 8.1.7 and 9.x Databases

    The JAR files for this driver are smbase.jar, smoracle.jar, and smutil.jar. Configure the connection pool using the following settings:

    • Name: Use this name when you configure the JDBC resource later.

    • Resource Type: Specify the appropriate value.

    • Database Vendor: Oracle

    • DataSource Classname: com.sun.sql.jdbcx.oracle.OracleDataSource

    • Properties:

      • serverName – Specify the host name or IP address of the database server.

      • portNumber – Specify the port number of the database server.

      • SID – Set as appropriate.

      • user – Set as appropriate.

      • password – Set as appropriate.

    • URL: jdbc:sun:oracle://serverName[:portNumber][;SID=databaseName]

    Sun Java System JDBC Driver for Microsoft SQL Server Databases

    The JAR files for this driver are smbase.jar, smsqlserver.jar, and smutil.jar. Configure the connection pool using the following settings:

    • Name: Use this name when you configure the JDBC resource later.

    • Resource Type: Specify the appropriate value.

    • Database Vendor: mssql

    • DataSource Classname: com.sun.sql.jdbcx.sqlserver.SQLServerDataSource

    • Properties:

      • serverName – Specify the host name or IP address and the port of the database server.

      • portNumber – Specify the port number of the database server.

      • user – Set as appropriate.

      • password – Set as appropriate.

      • selectMethod – Set to cursor.

    • URL: jdbc:sun:sqlserver://serverName[:portNumber]

60. What is the difference between TYPE_SCROLL_INSENSITIVE and TYPE_SCROLL_SENSITIVE

You will get a scrollable ResultSet object if you specify one of these ResultSet constants.The difference between the two has to do with whether a result set reflects changes that are made to it while it is open and whether certain methods can be called to detect these changes. Generally speaking, a result set that is TYPE_SCROLL_INSENSITIVE does not reflect changes made while it is still open and one that is TYPE_SCROLL_SENSITIVE does. All three types of result sets will make changes visible if they are closed and then reopened:

 

Statement stmt =
con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
ResultSet srs =
stmt.executeQuery(“SELECT COF_NAME, PRICE FROM COFFEES”);
srs.afterLast();
while (srs.previous())
{
String name = srs.getString(“COF_NAME”);
float price = srs.getFloat(“PRICE”);
System.out.println(name + ” ” + price);
}

59. 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”);

58. 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();

}

}

57. How can you load the driver.

Driver can be loaded in 2 ways :

1. class.forName(“sun.jdbc.odbc.JdbcOdbc”);

2. Driver d=new Driver(“sun.jdbc.odbc.JdbcOdbc”);

   DriverManager.registerDriver(d);

vendar specific one: Driver d=new oracle.driver.oracleDriver();

56. How can you retrieve data from the ResultSet

Example:

Statement stmt = conn.createStatement(); 
ResultSet rs = stmt.executeQuery(SELECT COF_NAME, PRICE FROM COFFEES”); 
while (rs .next() ) 

//Iam assuming there are 3 columns in the table. 
System.out.println ( rs.getString(1)); 
System.out.println(rs.getString(2)); 
System.out.println(rs.getString(3)); 

//don’t forget to close the resultset, statement & connection 
rs.close(); //First  
stmt.close(); //Second 
con.close(); //Last  
System.out.println(“You are done”); 

55. How do you implement Connection Pooling

Connection Pooling can be implemented by the following way. 

  1.  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. 
    1. For example, the oracle vendors implementation is as follows: oracle.jdbc.pool.oracleConnectionPoolDataSource Class. 
  2. A javax.sql.PooledConnection interface encapsulates the physical connection for the database. Again, the vendor provides the implementation.

Code for connecting the connection pooling

InitalContext ic=new InitialContext();

Hashtable ht=new Hashtable();

ht.put(“Context.INITIAL_PROVIDER”,weblogic.jndi.InitialCla)

//u have to set weblogic properties first and the jndi name that u r defining in 

//weblogic while creating the connectionpool

ht.put(“Context.PROVIDER_URL”,t3://localhost:7001);

ht.put(“Context.SECURITY_PRINCIPAL”,username);

ht.put(“Context.SECURITY_CREDENTIALS”,passwordof weblogic);

DataSource ds=(DataSource)ic.lookup(“jndiname”);

Connection con=ds.getConnection();

Statement stmt=con.createStatement();