Using Type-4 DB2 driver for Communication between java application and DB2 server

This is my second Post on JDBC the first was based on Type-1 architecture based JDBC Drivers and this one is for Type-4
 
I suppose that you have Installed the DB2 database server and JDK/JRE on your system if you have not installed yet Do it now,before starting this tutorial.

Now to create an application which can communicate with DB2 server you need
1. JDBC driver.
2. CLASSPATH environment variable of our system set to JDBC driver provided by DB2.
3. Little knowledge of basic JAVA and  following interfaces
    a.) Driver interface.
    b.) Statement and PreparedStatement Interfaces.
    c.) ResultSet Interface.
   
>> JDBC driver requirement is not a reason to worry,because type-4 drivers are shipped with database products by default by the database vendors.
For your DB2 version you can find it in the installation directory of your DB2
for windows OS it is mostly ->  c:\program files\IBM
then you can find drivers in the SQLLIB\java directory in the IBM folder
the driver names are   db2jcc.jar and  db2jcc_license_cu.jar

>> Now that you have located your drivers you need to add complete path for these driver to the CLASSPATH environment variable of your system.
To do this proceed with following steps:

1. open control panel.
2. select system option.
3. select advanced system settings.
4. a new small window will pop up select environment variables from here 
5. now see if there is already a CLASSPATH variable in system or user variables or not

case 1: If it is already there select it to edit and add full path for both the drivers mentioned above separated by a semicolon(;) and save the settings by clicking on ok button.

case 2: If the CLASSPATH variable is not there already create a new one provide the name CLASSPATH to it and add path for both  drivers.

>>Half of your JOB is done already. Its time for coding the application

Keep in mind:

• Default port no for DB2 is : 50000

• Host name : localhost ( if the server is on your own machine).

• Driver URL:

       For type-4 Drivers:

                      "jdbc:db2://localhost:50000/test","username","password"

       and for type-2 Drivers it is: 

                      "jdbc:db2:database","username","password"

• Never forget to close the connection after using it,its not a matter of consideration here in our sample application.but if you are designing some enterprise level application for your employer,and you forget to close the connections then only god can save you from getting fired .. mind it.

import java.util.*;

import java.sql.*;

class test

{

    public static void main(String args[])

    {

  

        try

        {

          /* load the driver class and registers the driver with driver pool automatically*/

         Class.forName("com.ibm.db2.jcc.DB2Driver");

          /* create a connection object using DriverManager class of java.sql package*/ 

         Connection conn=DriverManager.getConnection("jdbc:db2://localhost:5000/test","hsd","hsdhaka91");

         /*create statement class object and use it to send query to the connected database*/

         ResultSet rs=conn.createStatement().executeQuery("select * from test.employee");

         while(rs.next())

         {

            // access the data records/rows here.          

           // if there is no record in the tablename table this block will not be executed.

         }

         conn.close();

        }

      

        catch(Exception ex)

        {

         System.out.println(ex);

        }

    }  

}

Same application but without using the DriverManager

import java.util.*;

import java.sql.*;

class test

{

    public static void main(String args[])

    {

   

        try

        {

         /* load the driver class,registers the driver with driver pool and creates an object to Driver class*/

        com.ibm.db2.jcc.DB2Driver obj=new com.ibm.db2.jcc.DB2Driver();

        /* Properties  is an inbuilt class in the java.util package used to store properties */

        Properties p=new Properties();

        p.setProperty("user","username");  //provide username as second argument.

        p.setProperty("password","userpassword");   // provide password for the database user

        Connection conn=obj.connect("jdbc:db2://localhost:50000/dbname",p);

        ResultSet rs=conn.createStatement().executeQuery("select * from tablename");

        while(rs.next())

        {

            // access the data records/rows here.

            // if there is no record in the tablename table this block will not be executed.

        }

        conn.close();

        }

       

        catch(Exception ex)

        {

        System.out.println(ex);

        }

    }   

}

              

In the second application we are not taking support of DriverManager class to create connection object

but we are directly calling connect("URL",property object); method  to create the connection.

This connect method is a member of Driver class object.

>> You can also use PreparedStatement Interface to increase the user interactivity of your application.

I think This tutorial will be helpful for my readers.

Please provide your valuable Feedback for this post.