dbdao3

最新推荐文章于 2024-11-18 14:51:17 发布

foxconnMan

最新推荐文章于 2024-11-18 14:51:17 发布

阅读量592

点赞数

文章标签： null sql string object integer insert

本文链接：https://blog.csdn.net/foxconnMan/article/details/2285705

版权

/**
* 執行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參數物件陣列，形如： Object obj []= new Object[3]; 
* obj[0] = new String("string1"); obj[1] = new
* String("string2"); obj[2] = new Integer(1); 
* @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;
}

/**
*
* 執行多條SQL語句，用半形分號分隔﹐可以設置isAutoCommit的屬性﹐然后commit(),rollback() 使用方法： 
* 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異常
*/

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;
   }
  }
  ArrayList al=new ArrayList();
  ArrayList bl=new ArrayList(al);
  return rowResult;
}

/**
* 新增批處理方法支持SQL預編譯 ,但一次只能處理一個SQL. 
* 注意: 
* <ul>
* <li>為避免用戶漏置 autocommit參數,出現非預期的結果,本方法內置 autoCommit=false
* <li>方法內未調用connection.commit(),connection.close()方法,需用戶自行調用.
* <li>方法內置 connection.rollback(),用戶只需捕獲異常即可.
* </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;
  }
}

/**
* 關閉連接。 
*
* @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;
 }