JNDI DataSource Configuration in JBoss with DB2 in Eclipse Example

Assume that Eclipse is configured with JBoss Application Server 4.0. Before an application can get data from any Database (db2, Postgress, MySql , Oracle, etc..), it needs to establish a connection to the database. In our example we are going to connect with db2 database (eg. Employee). This can be done using JDBC in following ways.

1. Use the DriverManager class to establish a connection -> Load the Db2 JDBC Driver using Class.forName() & Create a connection to a db2 database using DriverManager.getConnection() method

2. Connect to the db2 database through a DataSource object. This is to use an implementation of the javax.sql.DataSource interface in conjunction with the Java Naming and Directory Interface (JNDI).

Now we are going to connect using the IInd way i.e using an implementation of the javax.sql.DataSource interface in conjunction with JNDI. Please follow the following steps for successful data base connection with db2 . .

It is always better to recycle and reuse existing connections to a data base than opening a new connection. So it needs to maintain a DB connection pool. JBoss supports DBCB which uses the Jakarta-Commons Database Connection Pool

Steps to be followed to configure JNDI Data Source in JBoss 4.0 with DB2 (in our example: jdbc/JbossTestDS)

1) Make an appropriate JDBC driver for DB2 available to JBoss & to your web application (Necessary driver class files for Db2 are available in the JARs db2jcc.jar, db2jcc_license_cisuz.jar, db2jcc_license_cu.jar. Copy these jars to Jboss (Drive:\jboss-4.0.0\server\default\lib) also include these jars to build path of the application )

2) Three Important XML files are to be configured. They are web.xml, Jboss-web.xml, *-ds.xml. Details are given below.

Assume that installation folder of Jboss 4.0 Application Server is D:\jboss-4.0.0. JNDI name is JbossTestDS, Database Name is Employee.

a) Define a reference lookup name (eg. jdbc/JbossTestDS) to an external resource in the WEB-INF\web.xml. Modify the web application deployment descriptor (/WEB-INF/web.xml) to define the JNDI name for the Datasource (ie. jdbc/datasourcename) 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>Database Connection</description>
<res-ref-name>jdbc/JbossTestDS</res-ref-name>
<res-type>javax.sql.DataSource</res-type>
<res-auth>Container</res-auth>
</resource-ref> 


b) Next Modify the jboss-web.xml located in the WEB-INF in the application.

jboss-web.xml

The following lines will be added in the jboss-web.xml between <jboss-web> </jboss-web>

<resource-ref>
<res-ref-name>jdbc/JbossTestDS
<res-type>javax.sql.DataSource<jndi-name>java:/jdbc/JbossTestDS </resource-ref>

More than one resource reference also can be defined.

c) Deployment descriptor (web.xml) only define a resource adaptor. This resource adapter can be used by integrating into the JBoss application server using a ds.xml descriptor file. Configure JBoss *-ds.xml ( in our example : jdbc-db.xml ) located in D:\jboss-4.0.0\server\default\deploy
jdbc-ds.xml

The following lines will be added in the jdbc-ds.xml between <datasources> </datasources>

<local-tx-datasource>
<jndi-name>jdbc/JbossTestDS</jndi-name>
<connection-url>jdbc:db2://localhost:50000/employee</connection-url>
<driver-class>com.ibm.db2.jcc.DB2Driver</driver-class>
<user-name>abc</user-name>
<password>xyz</password>
<min-pool-size>0</min-pool-size>
<metadata>
<type-mapping>DB2</type-mapping>
</metadata>
</local-tx-datasource>

Note that the jndi name jdbc/JbossTestDS must match the resource reference specified in the web application deployment descriptor

Finally write Java Code to use the resource.

ConnectionFactory.java

    
 public static Connection getConnection() throws CDRCFatalException {

  Context  m_ctxLookup = null;

  DataSource  ds = null;

  Connection  conn = null;

 try {

    Context initCtx = new InitialContext();

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

     ds = (DataSource)   envCtx.lookup("jdbc/JbossTestDS");

 } catch (NamingException exc) {

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

   throw new CDRCFatalException( exc.getMessage(), "01001" );

  }



  try {

      conn = ds.getConnection();

  }catch (SQLException exc) {

   System.out.println(" Fail to connect database " + exc.toString()+ " The context is " + m_ctxLookup);

   throw new CDRCFatalException( exc.getMessage(), "02006");

  }

  return conn;

 }

You can call the above getConnection() static method whereever connection is required in Java Code

 Connection conn = null;

conn = ConnectionFactory.getConnection();

 

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