dbconnection_pool.java

this code explains about the database connection establishment.

package com.wwr.commons;

import java.sql.*;
//import com.yourcompany.*;
//import com.microsoft.jdbc.sqlserver.*;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.*;


//import com.yourcompany.struts.form.UserLoginForm;;
public class DBConnection_Pool {

    Connection conn;
    Statement stmt;
    ResultSet rs;
    int i;
    DataSource ds;
    public static DataSource dataSource = null;

    static {
        try {
            Context env = (Context) new InitialContext();
            System.out.println("--context---" + env);
            dataSource = (DataSource) env.lookup("java:comp/env/jdbc/cms");

            System.out.println("--dataSource---" + dataSource);

        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public synchronized Connection CreateConnection() throws Exception {
        //Connection con = null;

        try {
            //Load the connection driver
			/*	String driverName=constant.driverName;
            String dataBaseURL=constant.dataBaseURL;
            String dataBaseUserName=constant.dataBaseUserName;
            String dataBasePassword=constant.dataBasePassword;
            String driverName = ResourceManager.getString("database.driver");
            String dataBaseURL = ResourceManager.getString("database.url");
            String dataBaseUserName = ResourceManager.getString("database.user");
            String dataBasePassword = ResourceManager.getString("database.password");			
            Class.forName(driverName);
            //Establish a connection
            conn=DriverManager.getConnection(dataBaseURL,dataBaseUserName,dataBasePassword);*/
            conn = dataSource.getConnection();
        //System.out.println("connection---Initialized:"+conn);
        } catch (Exception e) {
            System.out.println(e);
            e.printStackTrace();
            throw new Exception(e);
        }
        return conn;
    }

    public synchronized ResultSet execQuery(String sql) throws Exception {
        try {
            System.out.println(sql);
            //create a statment
            if (conn.isClosed()) {
                conn = CreateConnection();
            }
            stmt = conn.createStatement();
            //initializing resultset
            rs = stmt.executeQuery(sql);

        } catch (Exception e) {
            System.out.println(e);
            throw new Exception(e);
        }
        return rs;
    }

    public synchronized int execUpdate(String strSQL) throws Exception {

        //  System.out.println("IN EXEC UPDATE  :::  "+strSQL);
        try {
            if (conn.isClosed()) {
                conn = CreateConnection();
            }
            stmt = conn.createStatement();//ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);
            i = stmt.executeUpdate(strSQL);
        //rs.CLOSE_CURSORS_AT_COMMIT();
        } catch (Exception e) {
            System.out.println(e);
            throw new Exception(e);
        }
        return i;

    }

    public synchronized ResultSet execQueryTest(String strSQL) throws Exception {
        try {
            //Type_scroll_insensitive method does not reflect changes made while it is still open
            if (conn.isClosed()) {
                conn = CreateConnection();
            }
            stmt = conn.createStatement();//(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);//
            rs = stmt.executeQuery(strSQL);

        } catch (Exception sqlExcep) {
            System.out.println("SQL Exception       " + sqlExcep);
            throw new Exception(sqlExcep);
        }
        return rs;
    }

    public synchronized void closeConnection() throws Exception {
        //closes all the connections

        if (rs != null) {
            rs = null;
        }

        if (stmt != null) {
            stmt.close();
        }


        //System.out.println(" *******************************************>"+conn);
        if (conn != null && !conn.isClosed()) //if(conn!=null)
        {
            System.out.println(" conn      ---->" + conn.toString());
            conn.close();
        }

    }
}