dbconnection.java

this is example use EJB with jboss.

/*
 * Copyright(C) 2008, NTT AT Co., Ltd.
 * Project: AWGStar
 *
 * Notes:
 *  N/A
 *
 * Record of change:
 * Date         Version      Name       Content
 * 2008/12/15   1.0          TriNT      First create       
 */
package jp.co.ntt.awgview.server.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.CallableStatement;
import java.sql.Statement;

import jp.co.ntt.awgview.server.common.LogWriter;

/**
 * Class name : DBConnection <BR>
 * 
 * Package : jp.co.nttat.awgstar.server.dao <BR>
 * 
 * Description: A connection (session) with a specific database. <BR>
 * SQL statements are executed and results are returned within the context of a connection. <BR>
 * A SQL statement is precompiled and stored in a PreparedStatement object. <BR>
 * 
 * @author : AI&T
 * @version : 1.0
 */
public class DBConnection {

	/** Connection */
	private Connection conn = null;

	/** Prepared statement */
	private PreparedStatement preStmt = null;

	/** Callable statement */
	private CallableStatement cstmt = null;

	/**
	 * AutoCommit is set default as false. If you want transaction is committed
	 * manually. Call setAutoCommit() to set autoCommit to true.
	 */
	private boolean autoCommit = false;

	/**
	 * Empty constructor
	 */
	public DBConnection() {
	}

	/**
	 * DBRecord constructs a new DBRecord by connection.
	 * 
	 * @param connection
	 *            The connection of database
	 * @throws DatabaseException
	 */
	public DBConnection(Connection connection) {
		this.conn = connection;
	}

	/**
	 * Query message log using the SQL statement. Return an ResultSet.
	 * 
	 * @param sqlString
	 *            SQL statement
	 * @throws DatabaseException
	 *             SQLException occurs
	 * @return ResultSet
	 */

	public ResultSet execQuery(String sqlString) throws SQLException {

		LogWriter.getDBLogger().debug("Start execute query statement with sql = " + sqlString);

		try {
			/* try close statement before creating a new one */
			closeStatement();
			/* create prepared statement */
			preStmt = conn.prepareStatement(sqlString,
					ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY);
			// preStmt = conn.prepareStatement(sqlString);
			ResultSet rs = null;
			rs = preStmt.executeQuery();
			return rs;
		} catch (SQLException e) {
			LogWriter.getDBLogger().error("Error execute query sql statement failed SQL=" + sqlString);
			throw e;

		} finally {
			/* close prepare statement object */
			//LogWriter.getDBLogger().info("finally execute query statement with sql = " + sqlString);
		}
	}

	public ResultSet execQuery(String sqlString, String ...param) throws SQLException, Exception{
		
		LogWriter.getDBLogger().debug("Start execute query statement with sql = " + sqlString);
		try {
			/* try close statement before creating a new one */
			closeStatement();
			/* create prepared statement */
			preStmt = conn.prepareStatement(sqlString,
					ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY);
			int length = param.length;
			
			for (int i = 0; i< length; i++){
				 preStmt.setString(i + 1, param[i]);
			}
			
			ResultSet rs = preStmt.executeQuery();
			
			return rs;
		} catch (SQLException e) {
			LogWriter.getDBLogger().error("Error execute query sql statement failed SQL=" + sqlString);
			throw e;

		} finally {
			/* close prepare statement object */
//			LogWriter.getDBLogger().debug("End execute query statement with sql = " + sqlString);
		}
	}
	public int execUpdate(String sqlString) throws SQLException {
		try {
			if (conn != null) {
				/* try close statement before creating a new one */
				closeStatement();
				// preStmt = conn.prepareStatement(sqlString);
				// int r = preStmt.executeUpdate();
				Statement stmt = conn.createStatement();
				int rowCount = stmt.executeUpdate(sqlString);
				stmt.close();
				return rowCount;
			}
			return -1;
		} catch (SQLException e) {
			throw e;
		}
	}
	

	/**
	 * Set transaction Isolation for connection.
	 * 
	 * @param level
	 *          
	 */
	public void setTransIsolation(int level) {
		try {
			if (conn != null && !conn.isClosed()) {
				conn.setTransactionIsolation(level);
			}
		} catch (SQLException se) {
			LogWriter.getDBLogger().error(se.toString());
		}
	}

	/**
	 * This method is optimized for handling stored procedure call statements
	 * 
	 * @param strProcedure
	 *            The Procedure SQL
	 * @throws DatabaseException
	 *             SQLException occurs
	 * @return boolean
	 */
	public boolean prepareCall(String strProcedure) throws Exception {
		boolean isSuccessful = false;
		try {
			this.cstmt = conn.prepareCall(strProcedure);
			this.cstmt.execute();
			if (autoCommit) {
				conn.commit();
			}
			isSuccessful = true;
		} catch (SQLException se) {
			if (conn != null) {
				try {
					conn.rollback();
				} catch (SQLException ex) {
					LogWriter.getDBLogger().error("Execute SQL statement failed");
				}
			}
			/* close prepare statement object */
			closeCallableStatement();
			isSuccessful = false;
			LogWriter.getDBLogger().error("Execute SQL statement failed. SQL Procedure = "
							+ strProcedure + "\nException: " + se.toString());
		} finally {
			// DEBUG_END_EXE_SQL_DATABASE + " [SQL = " + strProcedure;
			/* close prepare statement object */
			closeCallableStatement();
		}
		return isSuccessful;
	}

	/**
	 * Close prepared statement.
	 */
	public void closeStatement() {
		try {
			if (preStmt != null) {
				preStmt.close();
				preStmt = null;
			}
		} catch (SQLException se) {
			/* in case of error, just ignore and continue to work */
			LogWriter.getDBLogger().error("Close prepared statement failed" + se.toString());
			LogWriter.getDBLogger().error("SQL = " + this.preStmt.toString());
			preStmt = null;
		}
	}

	/**
	 * Close Callable statement.
	 */
	private void closeCallableStatement() {
		try {
			if (this.cstmt != null) {
				this.cstmt.close();
				this.cstmt = null;
			}
		} catch (SQLException se) {
			/* in case of error, just ignore and continue to work */
			LogWriter.getDBLogger().error("Close callable statement failed" + se.toString());
			LogWriter.getDBLogger().error("SQL = " + this.cstmt.toString());
			this.cstmt = null;
		}
	}

	/**
	 * This method will commit database
	 * 
	 * @return true or false
	 * @throws DatabaseException
	 *             SQLException occurs
	 */
	public boolean commit() throws Exception {
		try {
			if (conn != null) {
				conn.commit();
				return true;
			}
			return false;
		} catch (SQLException se) {
			LogWriter.getDBLogger().error("Could not commit transaction. \nException:" + se.toString());
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException se1) {
				LogWriter.getDBLogger().error("Rollback database failed. \nException:" + se1.toString());
			}
			throw new Exception("Could not commit");
		}
	}

	/**
	 * This method will rollback database
	 * 
	 * @return true if success, otherwise false
	 * @throws DatabaseException
	 *             SQLException occurs
	 */
	public boolean rollback() {
		try {
			LogWriter.getDBLogger().error("System is rolling back...");
			if (conn != null) {
				conn.rollback();
				LogWriter.getDBLogger().error("Rollback success!");
				return true;
			}

		} catch (SQLException e) {
			LogWriter.getDBLogger().error("Rollback failed!");
		}
		return false;
	}

	/**
	 * This method use to set auto commit status of this connection
	 * 
	 * @param autoCommit
	 *            boolean
	 * @throws DatabaseException
	 *             SQLException occurs
	 */
	public void setAutoCommit(boolean autoCommit) throws SQLException {
		try {
			this.autoCommit = autoCommit;
			conn.setAutoCommit(this.autoCommit);
		} catch (SQLException sqle) {
			LogWriter.getDBLogger().error("Could not set auto commit");
			throw sqle;
		}
	}

	/**
	 * Close connection.
	 * 
	 * @throws DatabaseException
	 *             SQLException occurs
	 */
	public void closeConnection() throws Exception {

		/* close prepare statement object */
		closeStatement();
		
		/* close Callable statement object */
		closeCallableStatement();
		
		/* close connection */
		try {
			if (conn != null && !conn.isClosed()) {
				conn.close();
				conn = null;
			}
		} catch (SQLException se) {
			LogWriter.getDBLogger().error("Failed to close connection. Exception is " + se.toString());
			conn = null;
			throw se;
		}
		LogWriter.getDBLogger().debug("Debug connection closed");
	}

	/**
	 * Get connection
	 * 
	 * @return Connection
	 */
	public Connection getConnection() {
		return this.conn;
	}
}