jdbc connector(4)

/**
*
* 執行多條SQL語句，用半形分號分隔﹐可以設置isAutoCommit的屬性﹐然后commit(),rollback() 使用方法：<br>
* DBDAO dao = new DBDAO(); //或者直接調用帶參數的構造方法﹐不再設置isAutoCommit的屬性
* dao.setAutoCommit(false); int[] count = dao.doBatch(sql); dao.commit();
* //或者dao.rollback(); dao.close();
*
* @param sql
* String 形如:"select * from tlb_a;delete from tlb_b"
* @return int[] 對應分號相隔的sql語句執行成功的條數
* @throws SQLException
* SQL異常
* @deprecated instead use
* <code>doBatch(String sql, Object[][] param)</code>
*/
@Deprecated
public int[] doBatch(String sql) throws SQLException {
int[] rowResult = null;
String a;
Statement statement = null;
try {
statement = connection.createStatement();
java.util.StringTokenizer st = new java.util.StringTokenizer(sql, ";");
while (st.hasMoreElements()) {
a = st.nextToken();
statement.addBatch(a);
}
rowResult = statement.executeBatch();
} catch (SQLException e) {
LOG.error(e.toString(), e);
throw e;
} finally {
if (statement != null) {
statement.close();
statement = null;
}
}
return rowResult;
}
/**
* 新增批處理方法 支持SQL預編譯 ,但一次只能處理一個SQL.<br>
* <b> 注意:<br>
* <ul>
* <li>為避免用戶漏置 autocommit參數,出現非預期的結果,本方法內置 autoCommit=false
* <li>方法內未調用connection.commit(),connection.close()方法,需用戶自行調用.
* <li>方法內置 connection.rollback(),用戶只需捕獲異常即可.</b>
* </ul>
* 例如:
*
* <pre>
* DBDAO db = new DBDAO();
* String sql = "update table1 set column1=?, column2=? where id=?";
* Object[][] param = { { "value11", "value12", 1 },
* { "value21", "value22", 2 },
* { "value31", "value32", 3 } };
* db.doBatch(sql, param);
* </pre>
* @param sql
* @param param
* @return
* @throws SQLException
*/


/**
* 新增批處理方法 支持SQL預編譯 ,但一次只能處理一個SQL.<br>
* <b> 注意:<br>
* <ul>
* <li>為避免用戶漏置 autocommit參數,出現非預期的結果,本方法內置 autoCommit=false
* <li>方法內未調用connection.commit(),connection.close()方法,需用戶自行調用.
* <li>方法內置 connection.rollback(),用戶只需捕獲異常即可.</b>
* </ul>
* 例如:
*
* <pre>
* DBDAO db = new DBDAO();
* String sql = "update table1 set column1=?, column2=? where id=?";
* Object[][] param = { { "value11", "value12", 1 }, { "value21", "value22", 2 }, { "value31", "value32", 3 } };
* db.doBatch(sql, param);
* </pre>
*
* @param sql
* @param param
* @return
* @throws SQLException
*/
public int[] doBatch(String sql, Object[][] param) throws SQLException {
int[] rowResult = null;
PreparedStatement pstmt = null;
try {
connection.setAutoCommit(false);
pstmt = connection.prepareStatement(sql);
for (int i = 0; i < param.length; i++) {
for (int j = 0; j < param[i].length; j++)
pstmt.setObject(j + 1, param[i][j]);
pstmt.addBatch();
}
rowResult = pstmt.executeBatch();
} catch (SQLException e) {
connection.rollback();
LOG.error(e.toString(), e);
throw e;
} finally {
if (pstmt != null) {
pstmt.close();
pstmt = null;
}
}
return rowResult;
}

/**
* close statement object
*
* @throws SQLException
*/
public void closeStatement() throws SQLException {
if (statement != null) {
statement.close();
statement = null;
}
}

/**
* close preparedstatement object
*
* @throws SQLException
*/
public void closePreparedStatement() throws SQLException {
if (preparedStatement != null) {
preparedStatement.close();
preparedStatement = null;
}
}

/**
* 關閉連接。 <p/>
*
* @throws SQLException
* SQL異常
*/
public void close() throws SQLException {
try {
if (statement != null) {
statement.close();
statement = null;
}
if (preparedStatement != null) {
preparedStatement.close();
preparedStatement = null;
}
if (resultset != null) {
resultset.close();
resultset = null;
}
if (connection != null) {
connection.close();
connection = null;
}
} catch (SQLException e) {
LOG.error(e.toString(), e);
throw e;
}
}
/**
* 調試程式時使用
*
* @param sql
* @param pram
* @return
*/
public static String toSqlString(String sql, Object[] pram) throws Exception {
String str = sql;
try {
for (int i = 0; i < pram.length; i++) {
try {
str = str.replaceFirst("\\?", "'" + pram[i] + "'");
} catch (PatternSyntaxException e) {
e.printStackTrace();
}
}
} catch (Exception e) {
LOG.error(e);
}
LOG.info(str);
return str;
}