packagejohnimportjava.io.IOException;import java.sql.*;import javax.sql.*;public classDBUtil
{public static final String STRING = "0";public static final String DATE = "1";public static final String INT = "2";private Connection conn = null; //DB接続
private ResultSet sqlResult = null;private DataSource ds = null;
Statement sqlStatement= null;/*エラー情報*/
public String errStr = null;private static Logger logger = Logger.getLogger(DBUtil.class.getName());static{try{
Class.forName("oracle.jdbc.driver.OracleDriver");
}catch(Exception ex)
{
ex.printStackTrace();
}
}publicDBUtil(Connection pConn)
{
conn= pConn; //DB接続
sqlResult= null;
ds= null;
sqlStatement= null;
errStr= new String("");
}public Connection connOpen() throwsException
{try{if (Common.CONN_FLAG == true)
{
String strDbIp= Common.getData("other.dbhost");
String strDbPort= Common.getData("other.dbport");
String strDbName= Common.getData("other.dbname");
String strUsername= Common.getData("other.dbuser");
String strPassword= Common.getData("other.dbpswd");
String strDBUrl= "jdbc:oracle:thin:@"+strDbIp+":"+strDbPort+":"+strDbName;
conn=DriverManager.getConnection(strDBUrl, strUsername, strPassword);
}
conn.setAutoCommit(false);
sqlStatement= null;
sqlStatement=conn.createStatement();returnconn;
}catch(SQLException e)
{
String strErr= "SQL connection error: /n"
+e.getMessage();
logger.error("DBUTIL",strErr);this.errStr +=strErr;throw new BaseException("syserr");
}
}public void connClose() throwsException
{try{if(sqlStatement != null)
sqlStatement.close();
sqlStatement= null;if(conn != null)if(!conn.isClosed())
conn.close();
conn= null;
}catch(SQLException ex)
{
logger.error("DBUTIL",ex.getMessage());
logger.error("DBUTIL","DB closing exception!");throw new BaseException("syserr");
}
}public QueryData selQuery(String strQuerySQL) throwsException
{try{
QueryData qd= newQueryData();
Statement tmpSQLStatement=conn.createStatement();
tmpSQLStatement.execute(transSql(strQuerySQL));
sqlResult= null;
sqlResult=tmpSQLStatement.getResultSet();
qd.setData(sqlResult);if(sqlResult != null) sqlResult.close();
sqlResult= null;
tmpSQLStatement.close();
tmpSQLStatement= null;
logger.error("DBUTIL",strQuerySQL);returnqd;
}catch(Exception e)
{
logger.error("DBUTIL","SQL error: " +strQuerySQL);
logger.error("DBUTIL",e.getMessage());throw new BaseException("syserr");
}finally{
}
}public QueryData selQureyPrep(String pQuerySql,String[][] conditionlist) throwsException
{try{
QueryData qd= newQueryData();
PreparedStatement prep=conn.prepareStatement(transSql(pQuerySql));int cnt = 0;for (int x = 0; x < conditionlist.length;x++)
{boolean isFalse = true;while(isFalse)
{int mode = 0;
String strMode=conditionlist[x][0];if (!Common.checkNull(strMode))
{
isFalse= false;break;
}else{
cnt++;
mode=Integer.parseInt(strMode);
}switch(mode)
{case 0:
prep.setString(cnt,String.valueOf(conditionlist[x][1]));
isFalse= false;break;case 1:
prep.setDate(cnt,Date.valueOf(conditionlist[x][1]));
isFalse= false;break;case 2:
prep.setInt(cnt,Integer.parseInt(conditionlist[x][1]));
isFalse= false;break;default:
isFalse= false;break;
}
}
}
sqlResult= null;
sqlResult=prep.executeQuery();
qd.setData(sqlResult);if(sqlResult != null) sqlResult.close();
sqlResult= null;
prep.close();
prep= null;
logger.error("DBUTIL",pQuerySql);returnqd;
}catch(Exception e)
{
logger.error("DBUTIL","SQL error: " +pQuerySql);
logger.error("DBUTIL",e.getMessage());throw new BaseException("syserr");
}finally{
}
}public ResultSet selQuery_large(String strQuerySQL) throwsException
{try{
sqlStatement.execute(transSql(strQuerySQL));
sqlResult=sqlStatement.getResultSet();
logger.error("DBUTIL",strQuerySQL);returnsqlResult;
}catch(SQLException e)
{
logger.error("DBUTIL","SQL error: " +strQuerySQL);
logger.error("DBUTIL",e.getMessage());throw new BaseException("syserr");
}finally{
}
}public void doQuery(String strQuerySQL) throwsException
{try{
Statement SQLStatement=conn.createStatement();
SQLStatement.executeUpdate(transSql(strQuerySQL));
logger.error("DBUTIL",strQuerySQL);
SQLStatement.close();
SQLStatement= null;
}catch(SQLException e)
{
logger.error("DBUTIL","SQL error: " +strQuerySQL);
logger.error("DBUTIL",e.getMessage());throw new BaseException("syserr");
}finally{
}
}public void doQueryPrep(String pQuerySql,String[][] conditionlist) throwsException
{try{
PreparedStatement prep=conn.prepareStatement(transSql(pQuerySql));int cnt = 0;for (int x = 0; x < conditionlist.length;x++){boolean isFalse = true;while(isFalse){int mode = 0;
String strMode=conditionlist[x][0];if (!Common.checkNull(strMode)){
isFalse= false;break;
}else{
cnt++;
mode=Integer.parseInt(strMode);
}switch(mode) {case 0:
prep.setString(cnt,String.valueOf(conditionlist[x][1]));
isFalse= false;break;case 1:
prep.setDate(cnt,Date.valueOf(conditionlist[x][1]));
isFalse= false;break;case 2:
prep.setInt(cnt,Integer.parseInt(conditionlist[x][1]));
isFalse= false;break;default:
isFalse= false;break;
}
}
}
prep.executeUpdate();
logger.error("DBUTIL","SQL error: " +pQuerySql);
prep.close();
prep= null;
}catch(SQLException e)
{
logger.error("DBUTIL","SQL error: " +pQuerySql);
logger.error("DBUTIL",e.getMessage());throw new BaseException("syserr");
}finally{
}
}public boolean doCommit() throwsException
{try{
conn.commit();return true;
}catch(SQLException e)
{
logger.error("DBUTIL","SQL commit error! ");
logger.error("DBUTIL",e.getMessage());
conn.rollback();throw new BaseException("syserr");
}finally{
}
}public void doRollback() throwsException
{try{if(conn != null)
conn.rollback();
}catch(Exception e)
{
logger.error("DBUTIL","SQL rollback error! ");
logger.error("DBUTIL",e.getMessage());
}
}public static String transSql(String strInput) throwsIOException
{
String strOutput=strInput;//return EucJpFile.toJIS(strOutput);
returnstrOutput;
}public static String deTransSql(String strInput) throwsIOException
{
String strOutput=strInput;returnstrOutput;//return EucJpFile.toCp932(strOutput);
}
}