How to Configure JDBC Datasource (JNDI) in Tomcat 7 with DB2 in Ecplise Example.

The following steps tells how to Configure JDBC Data Resources (JNDI) in Tomcat 7 with DB2 in Ecplise with Example. Assume that Eclipse is configured with Apache Tomcat Application Server 7.0.

A database connection pool creates and manages a pool of connections to a database. It is always better to recycle and reuse already existing connections to a dB than opening a new connection. Tomcat supports DBCP connection pool from the Commons project by default. It is also possible to use any other connection pool that implements javax.sql.DataSource

Suppose if you need to access db2 database via a JDBC driver, follow the following steps for successful data base connection with db2.

Steps to be followed to configure JDBC Data Resources (JNDI) in Tomcat 7 with DB2 (in our example jdbc/javaOnlineDS)

In Brief :
1) Place appropriate JDBC drivers of DB2 to Tomcat for  your web application (i.e place it inside WEB-INF\lib)
2) Configure web.xml, context.xml files as described in the detail section.

In Detail :
Copy all necessary JDBC drivers of DB2 database, to the lib folder under WEB-INF. i.e  copy db2 jdbc drivers db2java.zip, db2jcc.jar, db2jcc_license_cisuz.jar, db2jcc_license_cu.jar to the lib folder.

Next, Declare Resource Requirements in web.xml. Modify the web application deployment descriptor (/WEB-INF/web.xml) to declare the JNDI name for the Database resource (ie. jdbc/javaOnlineDS) for the web application level.

web.xml
The following lines will be added in the web.xml before </web-app> for declaring resource requirements.

<resource-ref>

<description>DB Connection Pool</description>

<res-ref-name>jdbc/javaOnlineDS</res-ref-name>

<res-type>javax.sql.DataSource</res-type>

<res-auth>Container</res-auth>

</resource-ref >

context.xml

The following lines will be added in the context.xml under META-INF between <Context> </Context>

<Resource name="jdbc/javaOnlineDS" auth="Container"   type="javax.sql.DataSource" username="abc" password="*****" driverClassName="com.ibm.db2.jcc.DB2Driver"  url="jdbc:db2://localhost:50000/test" maxActive="8" maxIdle="4" />

Note that the resource name jdbc/javaOnlineDS must match the value specified in the web application deployment descriptor


Now let us write Java code to use the resource. Create a connection class

ConnectionFactory.java

package javaonline;

import java.sql.Connection;

import java.sql.SQLException;

import javax.naming.Context;

import javax.naming.InitialContext;

import javax.naming.NamingException;

import javax.sql.DataSource;


public class ConnectionFactory {

 
  public static Connection getConnection()  {

    Context  m_ctxLookup = null;

    DataSource  dataSource = null;

    Connection  conn  = null;


   try {
 
      Context initCtx = new InitialContext();

      Context envCtx = (Context) initCtx.lookup("java:comp/env");

      dataSource = (DataSource)   envCtx.lookup("jdbc/javaOnlineDS");

    } catch (NamingException exc) {

      System.out.println("Lookup fail of database " + exc.toString()+ "The context is " + m_ctxLookup);

       }


    try {

      conn = dataSource.getConnection();

    }catch (SQLException exc) {

      System.out.println("Getting Connection. Fail of database " + exc.toString()+ "The context is " + m_ctxLookup);

    }

    return conn;

   }

}



Call the above getConnection() static method in DAO class

public class GetDetailsDAO { 
public String getData() throws  JSONException

 {  

  PreparedStatement ps = null;

     ResultSet rs = null;

     Connection conn = null;

    try

     {     

      conn = ConnectionFactory.getConnection();


      String strQry = "SELECT * from Employee";

      ps = conn.prepareStatement(strQry);

      rs = ps.executeQuery();

  ----

  ----
         } catch (SQLException se) {}

 }
}

Read More

Java to create & use DataSource Object in DB2 in the application using DataSource interface without using tools

The preferred way to connect to a database is using DataSource interface rather than using DriverManager interface. DriverManager class requires JDBC driver class name and driver URL. So we have to mention the Driver class and Driver URL which are specific to a JDBC vendor, driver implementation. This reduces the Portabilty of the Application. To improve potability of your applications among data sources, DataSource interface may be used. JDBC version 2.0 provides the DataSource interface. The following program creates datasource object to connect to a database using the DataSource interface in the application itself without using any tool like WAS. Let us see the steps involved to create datasource object and connect to database.

1. Import the package which contains the DataSource interface implementation.

2. Create object of the DataSource implementation (in our example DB2DataSource which has built in support for connection pooling )

3. Set the properties like DatabaseName, Description, User, and Password of the DataSource object.

4. To associate the datasource object with the logical name jdbc/studentDS, register the object with the Java Naming and Directory Interface Naming (JNDI). This is optional.

// Code Starts here

import java.sql.*;        
import javax.sql.*;       // JDBC 2.0 
import com.ibm.db2.jcc.DB2DataSource;   //DB2 Universal JDBC Driver interface supports connection pool
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.*;
import javax.naming.*;    
public class DB2DS {
  public static void main(String[] argv) {
DB2DataSource ds=null;
 try {
ds=new DB2DataSource();        //inbuilt support for connection pool
ds.setDatabaseName("student");          
ds.setDescription("Student Database");
ds.setUser("abc"); //db user id
ds.setPassword("*******"); // db password

 } catch (Exception e) {
      System.out.println("Error"); 
      e.printStackTrace();
      return;
      }
Connection conn = null;
 PreparedStatement pstmt = null;
 ResultSet rs=null;
   try {
    conn=ds.getConnection();      
    if (conn != null) System.out.println("Database Connection Established ");  else System.out.println("DB Connection Failed ");
    pstmt=conn.prepareStatement("Select * from stu_detail");
    rs=pstmt.executeQuery();
    if(rs!=null)
      {
      while(rs.next())
       {
       System.out.println("Student ID: "+rs.getString("ID"));
       System.out.println("Student Name: "+rs.getString("name"));
       }
      }
     } catch (SQLException e) {
        System.out.println("Error in Connection");
        e.printStackTrace();
        return;
     }
}
}

Read More