[JAVA] dbhelper

2007. 12. 17. 20:28

WAS는 weblogic6.1이고요
오라클 8.1.7 버젼을 사용하고 있습니다.
보시다시피 DataSource 를 통해 커넥션을 처리하고 있습니다.

1. 첫번째 클래스
아래 주요 사항중에 DataSource , Connection
객체가 멤버변수로 선언이 되어 있다는점
그리고 getConnection() ,freeConnection() 메소드가
동기화처리를 안하고 있다는 점입니다.

다음 클래스가 어떻게 수정되야 좋을지 조언 부탁드립니다.

public class OracleAdminConnectionManager{

static final String ORACLE_DATASOURCE = "java:comp/env/jdbc/OracleAdmin";
private DataSource ds = null;
Connection conn = null;

public OracleAdminConnectionManager(){

Context ctx = null;
try{
ctx = new InitialContext();
if(ctx == null)
ds = (DataSource)ctx.lookup(ORACLE_DATASOURCE);

}catch(NamingException nx){
nx.printStackTrace();

}
}

public Connection getConnection(){
try{
conn = ds.getConnection();
}catch(Exception ex){
ex.printStackTrace();
}
return conn;
}

public void freeConnection(Connection conn){
try{
conn.close();
}catch(SQLException sx){
}
}
public void finalize(){
try {
freeConnection(conn);
} catch (Exception ex) {}
}

}
2. 두번째 클래스

아래 클래스도 OracleAdminConnectionManager 를 멤버변수로 선언하고 있습니다.

package xxxxx.db.pool;

import java.sql.*;
import xxxxx.db.*;
import java.io.Reader;
import javax.servlet.http.*;

import xxxxx.util.LogDBAccess;

public class DBAdminAccess implements HttpSessionBindingListener{

// DB Connection
Connection con=null;
Statement stmt=null;

PreparedStatement pstmt =null;

ResultSet result = null;
OracleAdminConnectionManager mgr = null;
boolean conInUse = false;
int pageSize=20;
boolean hasPrev=false, hasNext=false;
ResultSubset subset=null;
String test = null;

public DBAdminAccess() {
}

public boolean setConnection(OracleAdminConnectionManager mgr) {
this.mgr = mgr;
this.con = mgr.getConnection();
if (con == null) {
conInUse=false;
LogDBAccess.err("Connection is null");
} else
conInUse = true;
return conInUse;
}

public boolean setDConnection() {
this.mgr = new OracleAdminConnectionManager();
this.con = mgr.getConnection();
if (con == null) {
conInUse=false;
LogDBAccess.err("Connection is null");
} else
conInUse = true;

return conInUse;
}

public boolean execute(String sql)
{
if (conInUse == false) return false;

try {
if (result != null) { result.close(); result=null; }
if (stmt != null) stmt.close();
stmt = con.createStatement();
return (stmt.execute(sql));
} catch (SQLException E) {
LogDBAccess.err(E,sql);
close();
return false;
} catch (Exception E) {
LogDBAccess.err(E,sql);
close();
return false;
}
}

public int executeUpdate(String sql)
{
if (conInUse == false) return 0;
try {
if (result != null) { result.close(); result=null; }
if (stmt != null) stmt.close();
stmt = con.createStatement();
return (stmt.executeUpdate(sql));
} catch (SQLException E) {
LogDBAccess.err(E,sql);
close();
return 0;
} catch (Exception E) {
LogDBAccess.err(E,sql);
close();
return 0;
}
}

public boolean execute(PreparedStatement statement) {
try {
stmt = statement;
if (result != null) { result.close(); result=null; }
return (statement.execute());
} catch (SQLException E) {
LogDBAccess.err(E," from prepareStatement execute");
close();
return false;
} catch (Exception E) {
LogDBAccess.err(E," from prepareStatement execute");
close();
return false;
}
}

/*------------------------------------------------------------
Description : Run Prepared/Callable Statement
Parameter :
nType :
0:PreparedStatement, 1:CallableStatement
Return Value
쿼리실행이 성공하면 true, 실패하면 flase
-------------------------------------------------------------*/
public boolean execute(int nType)
{
if (conInUse == false) return false;

try {
if (result != null) {
result.close();
result=null;
}
return (pstmt.execute());
} catch (SQLException E) {
LogDBAccess.err(E);
close();
return false;
} catch (Exception E) {
LogDBAccess.err(E);
close();
return false;
}
}

public PreparedStatement GetPrePareStatement()
{
return pstmt;
}

/*------------------------------------------------------------
Description : Create PreparedStatement
Parameter
None
Return Value
None
-------------------------------------------------------------*/
public PreparedStatement prepare(String sql) {
if (conInUse == false) return null;
try {
if (result != null) result.close();
pstmt = con.prepareStatement(sql);
return pstmt;

} catch (SQLException E) {
LogDBAccess.err(E,sql);
System.out.println("Esql=" + E);
close();
return null;
} catch (Exception E) {
LogDBAccess.err(E,sql);
System.out.println("Esql=" + E);
close();
return null;
}
}

/*------------------------------------------------------------
Description : Create PreparedStatement
Parameter
String : 쿼리문
resultSetType : a result set type; see ResultSet.TYPE_XXX
TYPE_FORWARD_ONLY : 커서가 앞으로만 이동할수 있다.
TYPE_SCROLL_INSENSITIVE : Scrollable, not sensitive
TYPE_SCROLL_SENSITIVE : Scrollable, sensitive
resultSetConcurrency : a concurrency type; see ResultSet.CONCUR_XXX
CONCUR_READ_ONLY : Not Updatable
CONCUR_UPDATABLE : Updatable
Return Value
None
-------------------------------------------------------------*/
public PreparedStatement prepare(String sql, int resultSetType, int resultSetConcurrency)
{
if (conInUse == false) return null;
try {
if (result != null) result.close();
pstmt = con.prepareStatement(sql, resultSetType, resultSetConcurrency);
return pstmt;

} catch (SQLException E) {
LogDBAccess.err(E,sql);
System.out.println("Esql=" + E);
close();
return null;
} catch (Exception E) {
LogDBAccess.err(E,sql);
System.out.println("Esql=" + E);
close();
return null;
}
}

/*------------------------------------------------------------
Description : PreparedStatement 인수값 설정
Parameter
None
Return Value
None
-------------------------------------------------------------*/
public void SetPrepareParameter(int nIndex, String strValue)
{
try {
pstmt.setString(nIndex, strValue);
//System.out.println("SetPrepareParameter(int, String)");
} catch (Exception E) {
LogDBAccess.err(E.toString());
close();
}
}

/*------------------------------------------------------------
Description :
Parameter
None
Return Value
None
-------------------------------------------------------------*/
public void SetPrepareParameter(int nIndex, int nValue)
{
try {
pstmt.setInt(nIndex, nValue);
//System.out.println("SetPrepareParameter(int, int)");
} catch (Exception E) {
LogDBAccess.err(E.toString());
close();
}
}

/*------------------------------------------------------------
Description :
Parameter
None
Return Value
None
-------------------------------------------------------------*/
public void SetPrepareParameter(int nIndex, float fValue)
{
try {
pstmt.setFloat(nIndex, fValue);
//System.out.println("SetPrepareParameter(int, float)");
} catch (Exception E) {
LogDBAccess.err(E.toString());
close();
}
}

/*------------------------------------------------------------
Description :
Parameter
None
Return Value
None
-------------------------------------------------------------*/
public void SetPrepareParameter(int nIndex, Date dValue)
{
try {
pstmt.setDate(nIndex, dValue);
//System.out.println("SetPrepareParameter(int, Date)");
} catch (Exception E) {
LogDBAccess.err(E.toString());
close();
}
}

/*------------------------------------------------------------
Description :
Parameter
None
Return Value
None
-------------------------------------------------------------*/
public void SetPrepareParameter(int parameterIndex, Reader reader, int nLength)
{
try {
pstmt.setCharacterStream(parameterIndex, reader, nLength);
} catch (SQLException e) {
System.out.println("JDBCConnection::SetPrepareParameter(int, Date) \n" + e.toString());
close();
}
}

/*------------------------------------------------------------
Description : ResultSet 객체 리턴
Parameter
None
Return Value
None
-------------------------------------------------------------*/
public ResultSet getResultSet(int nType) {
try {
result = pstmt.getResultSet();
return result;
} catch (SQLException E) {
LogDBAccess.err(E," from getResultSet()");
System.out.println("Esql=" + E);
close();
return null;
} catch (Exception E) {
LogDBAccess.err(E," from getResultSet()");
System.out.println("Esql=" + E);
close();
return null;
}
}

/*------------------------------------------------------------
Description : ResultSet 객체 리턴
Parameter
None
Return Value
None
-------------------------------------------------------------*/
public ResultSet getResultSet() {
try {
result = stmt.getResultSet();
return result;
} catch (SQLException E) {
LogDBAccess.err(E," from getResultSet()");
System.out.println("Esql=" + E);
close();
return null;
} catch (Exception E) {
LogDBAccess.err(E," from getResultSet()");
System.out.println("Esql=" + E);
close();
return null;
}
}

/*------------------------------------------------------------
Description : Update Count Return
Parameter
None
Return Value
None
-------------------------------------------------------------*/
public int getUpdateCount() {
try {
return stmt.getUpdateCount();
} catch (SQLException E) {
LogDBAccess.err(E," from getResultSet()");
System.out.println("Esql=" + E);
close();
return 0;
} catch (Exception E) {
LogDBAccess.err(E," from getResultSet()");
System.out.println("Esql=" + E);
close();
return 0;
}
}

/*------------------------------------------------------------
Description : Update Count Return
Parameter
None
Return Value
None
-------------------------------------------------------------*/
public int getUpdateCount(int nMode) {
try {
return pstmt.getUpdateCount();
} catch (SQLException E) {
LogDBAccess.err(E," from getResultSet()");
System.out.println("Esql=" + E);
close();
return 0;
} catch (Exception E) {
LogDBAccess.err(E," from getResultSet()");
System.out.println("Esql=" + E);
close();
return 0;
}
}

public void setPageSize(int size) {
pageSize = size;
}

public boolean hasPrev() {
if (subset != null)
return subset.hasPrev();
else return false;
}

public boolean hasNext() {
if (subset != null)
return subset.hasNext();
else return false;
}

public int getTotalPage() {
if (subset != null)
return subset.getTotalPage();
else
return 0;
}

public int getTotalNum() {
if (subset != null)
return subset.getTotalNum();
else
return 0;
}

public ResultSubset getResultSubset(int page) {
try {
result = stmt.getResultSet();
subset = new ResultSubset();
subset.init(page, pageSize, result);
return subset;
} catch (SQLException E) {
LogDBAccess.err(E," from getResultSubset():SQLException");
close();
return subset;
} catch (Exception E) {
LogDBAccess.err(E," from getResultSubset():Exception");
close();
return null;
}
}

public ResultSetMetaData getMetaData() {
if (result == null) return null;
try {
return result.getMetaData();
} catch (SQLException E) {
LogDBAccess.err(E," from getMetaData()");
close();
return null;
} catch (Exception E) {
LogDBAccess.err(E," from getMetaData()");
close();
return null;
}
}

public void setAutoCommit(boolean value) {
try {
if (conInUse == true) con.setAutoCommit(value);
} catch (SQLException E) {
LogDBAccess.err(E," from setAutoCommit()");
close();
} catch (Exception E) {
LogDBAccess.err(E," from setAutoCommit()");
close();
} finally {
}
}

public void commit() {
try {
if (conInUse == true) con.commit();
} catch (SQLException E) {
close();
LogDBAccess.err(E," from commit()");
} catch (Exception E) {
close();
LogDBAccess.err(E," from commit()");
}
}

public void rollback() {
try {
if (conInUse == true) con.rollback();
} catch (SQLException E) {
LogDBAccess.err(E," from rollback()");
close();
} catch (Exception E) {
LogDBAccess.err(E," from rollback()");

close();
}
}

/*------------------------------------------------------------
Description : 모든 자원을 해제한다.
Parameter :
None
Return Value
None
-------------------------------------------------------------*/
public void close()
{
try {
if (result != null) { result.close(); result=null; }
if (stmt != null) { stmt.close(); stmt=null; }
if (pstmt != null) { pstmt.close(); pstmt=null; }
if (mgr != null && conInUse == true) {
// mgr = OracleConnectionManager의 인스턴스
//System.out.println("DBAdminAccess.close()");
mgr.freeConnection(con);
conInUse = false;
}

//if (con != null) { con.close(); con=null; }
} catch (SQLException E) {
LogDBAccess.err(E," from close()");
} catch (Exception E) {
LogDBAccess.err(E," from close()");
}
}
/*------------------------------------------------------------
Description : 모든 자원을 해제한다.(session 종료시 -- test용)
Parameter :
None
Return Value
None
-------------------------------------------------------------*/
public void close(String test)
{
this.test =test;
try {
if (result != null) { result.close(); result=null; }
if (stmt != null) { stmt.close(); stmt=null; }
if (pstmt != null) { pstmt.close(); pstmt=null; }
if (mgr != null && conInUse == true) {
// mgr = OracleConnectionManager의 인스턴스
//System.out.println("DBAccess.close()");
mgr.freeConnection(con);
System.out.println("DBAdminSessionAccess Connection close"+test);
conInUse = false;
}

//if (con != null) { con.close(); con=null; }
} catch (SQLException E) {
LogDBAccess.err(E," from close()");
} catch (Exception E) {
LogDBAccess.err(E," from close()");
}
}

/*------------------------------------------------------------
Description : session이 살때,죽을때 동작을 하는 메소드
Parameter :
None
Return Value
None
-------------------------------------------------------------*/
public void valueBound(HttpSessionBindingEvent event){
System.out.println("DBAdminSessionAccess binding into Session");
}

public void valueUnbound(HttpSessionBindingEvent event){
test ="valueUnbound()";
try{
close(test);

}catch(Exception e){
System.out.println("valueUnbound Exception: "+e);
}finally{
con = null;
System.out.println("DBAdminSessionAccess unbinding into Session Finally");

}
}
public void finalize() {
try {
close();

} catch (Exception ex) {}
}
}

3. 상기 두 클래스를 통해 jsp를 통해 처리되는 형태

<jsp:useBean id="DBMgr" class="xxxxx.db.pool.OracleAdminConnectionManager" />
<jsp:useBean id="access" scope="page" class="xxxxx.db.pool.DBAdminAccess" />
<%
   access.setConnection(DBMgr);
   String queryNum= "select * from xxx";
try {
   access.execute(queryNum);
rs = access.getResultSet();

   중략 --

   String sql = "insert into (a, b ,c) xxx value(?,?,?)"
access.prepare(sql.toString());
access.SetPrepareParameter(1,a);
access.SetPrepareParameter(2,b);
access.SetPrepareParameter(3,c);
access.execute(1);
   }catch (Exception e) {

   }finally {
   access.close();
   }

%>