jdbc connector(3)

/**
* 資料事務提交 要先將自動提交設置狀態設置為不自動﹐然后才commit
*/
public void commit() throws SQLException {
try {
connection.commit();
} catch (SQLException e) {
LOG.error(e.toString(), e);
throw e;
}finally{
close();
}
}

/**
* 資料事務回滾,在執行SQL的過程中出現異常時執行 要先將自動提交設置狀態設置為不自動
*/
public void rollback() throws SQLException {
try {
connection.rollback();
} catch (SQLException e) {
LOG.error(e.toString(), e);
throw e;
}finally{
close();
}
}

/**
* 將查詢的結果放在一個ResultSet中 使用方法：<br>
* DBDAO dao = new DBDAO(); Result rs = dao.doSQLQueryRS(sql); 解析rs后要顯示關閉
* rs.close(); dao.close();
*
* @param sql
*            String 任何一條SQL查詢語句形如:select * from tlb_doc where id=?
* @return ResultSet 查詢結果返回對象集
* @throws SQLException
*/
public ResultSet executeQuery(String sql) throws SQLException {
resultset = null;
try {
statement = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY);
resultset = statement.executeQuery(sql);
connection.commit();//for DBLink與seq
} catch (SQLException e) {
LOG.error(e.toString(), e);
LOG.error("SQL:" + sql);
throw e;
}
return resultset;
}

/**
* 將查詢的結果放在一個數組中返回
* @param sql
* @param args
* @return
* @throws Exception
*/
public String[][] executeQueryArray(String sql, Object[] args) throws Exception {
String sTmp;
String[][] sArrRtn = null;
int iCol = 0, i = 0, j = 0;
boolean bFlg = false;
ResultSet rs = null;
ResultSetMetaData rsmd = null;
try {
rs = executeQuery(sql,args);
rsmd = rs.getMetaData();
iCol = rsmd.getColumnCount();
bFlg = rs.last();
if (bFlg)
i = rs.getRow();
rs.beforeFirst();
sArrRtn = new String[i + 1][iCol];
for (i = 0; i < iCol; i++) {
sTmp = rsmd.getColumnName(i + 1).trim();
sArrRtn[0][i] = sTmp;
}
while (rs.next()) {
j = j + 1;
for (i = 0; i < iCol; i++) {
Object o = rs.getObject(i + 1);
sTmp = String.valueOf(o == null ? "" : o);
sTmp = sTmp.equalsIgnoreCase("null") ? "" : sTmp;
sArrRtn[j][i] = sTmp;
}
}
} catch (SQLException e) {
LOG.error(e.toString(), e);
throw e;
} catch (Exception e) {
LOG.error(e.toString(), e);
throw e;
}
return sArrRtn;
}

/**
* 將查詢的結果放在一個ResultSet中 使用方法：<br>
* DBDAO dao = new DBDAO(); rs = dao.executeQuery(sql,obj); 解析rs后要顯示關閉
* rs.close(); dao.close();
*
* @param sql
*            String 任何一條SQL查詢語句形如:select * from tlb_doc where id=?
* @param args
*            Object[] 參數對象， 形如︰<p/> Object obj []= new Object[1]; </br>
*            obj[0] = new String("2"); </br>
* @return ResultSet 查詢結果返回對象集
* @throws SQLException
*/
public ResultSet executeQuery(String sql, Object[] args) throws SQLException {
resultset = null;
try {
preparedStatement = connection.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY);
if (args != null) {
for (int k = 0; k < args.length; k++) {
preparedStatement.setObject(k + 1, args[k]);
}
}
resultset = preparedStatement.executeQuery();
connection.commit();//for DBLink與seq
} catch (SQLException e) {
LOG.error(e.toString(), e);
LOG.error("SQL:" + sql);
for (int i = 0; i < args.length; i++) {
LOG.error("args[" + i + "]" + args[i]);
}
throw e;
}
return resultset;
}

/**
* 執行SQL更新語句，針對PreparedStatement﹐可以用于事物處理
*
* @return int 執行成功的SQL條數
* @throws SQLException
*             SQL異常
*/
public int executeUpdate() throws SQLException {
int num = 0;
try {
if (preparedStatement != null) {
num = preparedStatement.executeUpdate();
if (isAutoCommit) {
connection.commit();
}
}
} catch (SQLException e) {
if (isAutoCommit) {
connection.rollback();
}
LOG.error(e.toString(), e);
throw e;
} finally {
if (preparedStatement != null) {
preparedStatement.close();
preparedStatement = null;
}
}
return num;
}

/**
* 執行SQL更新語句，針對Statement﹐可以用于事物處理
*
* @param sql
*            String 執行的SQL語句
* @return int 執行成功的SQL條數
*/
public int executeUpdate(String sql) throws SQLException {
int num = 0;
Statement statement = null;
try {
statement = createStatement();
num = statement.executeUpdate(sql);
if (isAutoCommit) {
connection.commit();
}
} catch (SQLException e) {
if (isAutoCommit) {
connection.rollback();
}
LOG.error(e.toString(), e);
throw e;
} finally {
if (statement != null) {
statement.close();
statement = null;
}
}
return num;
}

/**
* 執行資料更新操作，接收參數﹐可以用于執行多條操作的事物處理﹐可以回滾
*
* @param sql-String
*            增加，修改和刪除SQL語句，注意語句要帶？參數 形如：insert into tbl_group values(?,?,?)
* @param args []
*            Object參數物件陣列， 形如： <p/> Object obj []= new Object[3]; <br/>
*            obj[0] = new String("string1"); <br/> obj[1] = new
*            String("string2");<br/> obj[2] = new Integer(1);<br/>
* @return int 執行成功返回受影響的記錄數
*/
public int executeUpdate(String sql, Object[] args) throws SQLException {
int r = 0;
PreparedStatement preparedStatement = null;
try {
preparedStatement = connection.prepareStatement(sql);
if (args != null) {
for (int i = 0; i < args.length; i++) {
preparedStatement.setObject(i + 1, args[i]);
}
}
r = preparedStatement.executeUpdate();
if (isAutoCommit) {
connection.commit();
}
} catch (SQLException e) {
if (isAutoCommit) {
connection.rollback();
}
LOG.error(e.toString(), e);
throw e;
} finally {
if (preparedStatement != null) {
preparedStatement.close();
preparedStatement = null;
}
}
return r;
}