package john
import java.io.IOException;
import java.util.*;
public class QueryDataRow
{
public HashMap hashColumn; private String[] data;
public QueryDataRow()
{
super();
}
public String get(String str) throws IOException
{
int colindex = ((Integer) (hashColumn.get(str))).intValue();
return DBUtil.deTransSql(data[colindex]);
}
public void setData(String[] s)
{
this.data = new String[s.length];
for (int i = 0; i < s.length; i++)
{
this.data[i] = s[i];
}
}
public void resetColumn(String columnName, String columnValue)
{
int colindex = ((Integer) (hashColumn.get(columnName))).intValue();
data[colindex] = columnValue;
}
}
package john
import java.util.*;
import java.sql.*;
public class QueryData
{
private ArrayList rows;
public QueryData()
{
rows = new ArrayList();
rows.clear();
}
public boolean setData(ResultSet rs) throws Exception
{
boolean ret = false;
try
{
ResultSetMetaData rsdata = rs.getMetaData();
int ColumnCount = rsdata.getColumnCount();
String Columns[] = new String[ColumnCount]; HashMap hashColumn = new HashMap(); for (int i = 0; i < ColumnCount; i++)
{
Columns[i] = rsdata.getColumnName(i + 1);
hashColumn.put(Columns[i], new Integer(i));
}
while (rs.next())
{
String rowdata[] = new String[ColumnCount]; for (int i = 0; i < ColumnCount; i++)
{
String column = Columns[i]; String value = rs.getString(column); value = (value != null ? value : "");
rowdata[i] = value;
} QueryDataRow row = new QueryDataRow();
row.hashColumn = hashColumn;
row.setData(rowdata);
rows.add(row);
} ret = true;
}
catch (Exception e)
{
ret = false;
throw e;
}
return ret;
}
public boolean removeRow(int index) throws Exception
{
try
{
if (rows != null)
{
rows.remove(index);
return true;
}
else
{
return false;
}
}
catch (Exception ex)
{
throw ex;
}
}
public QueryDataRow getRow(int index) throws Exception
{
try
{
if (rows != null)
{
QueryDataRow row = new QueryDataRow();
return (QueryDataRow) rows.get(index);
}
else
{
return null;
} } catch (Exception ex)
{
throw ex;
}
}
public int size()
{
if (rows != null)
{
return rows.size();
}
else
{
return 0;
}
}
}
package john
import java.io.IOException;
import java.sql.*;
import javax.sql.*; public class DBUtil
{ 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();
}
}
public DBUtil(Connection pConn)
{
conn = pConn; //DB接続 sqlResult = null;
ds = null;
sqlStatement = null;
errStr = new String("");
}
public Connection connOpen() throws Exception
{
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();
return conn;
}
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() throws Exception
{
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) throws Exception
{
try
{
QueryData qd = new QueryData();
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);
return qd;
}
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) throws Exception
{
try
{
QueryData qd = new QueryData();
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);
return qd;
}
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) throws Exception
{
try
{
sqlStatement.execute(transSql(strQuerySQL));
sqlResult = sqlStatement.getResultSet();
logger.error("DBUTIL",strQuerySQL);
return sqlResult;
}
catch(SQLException e)
{
logger.error("DBUTIL","SQL error: " + strQuerySQL);
logger.error("DBUTIL",e.getMessage());
throw new BaseException("syserr");
}
finally
{
}
}
public void doQuery(String strQuerySQL) throws Exception
{
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) throws Exception
{
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() throws Exception
{
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() throws Exception
{
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) throws IOException
{
String strOutput = strInput;
//return EucJpFile.toJIS(strOutput);
return strOutput;
}
public static String deTransSql(String strInput) throws IOException
{
String strOutput = strInput;
return strOutput;
//return EucJpFile.toCp932(strOutput);
}
}
以下是JAVA对DB操作的一种实现方式,与大家一起共享,对初学者可能有一定的帮助,当然对高手而 言,用处就不见的大了,欢迎大家提出更好的意见和建议.为了商业规则,代码的注释去掉了,请大家见谅.