jdbc的addBatch问题,快来帮忙啊,200分

Question

public void deleteCourseInfo(String courseId) throws Exception {
Connection con = pStrategy.getDBConnection();
con.setAutoCommit(false);
try {
            Statement stmt = con.createStatement();
            Map tb = AllCourseInfoTable.getTable();
     Iterator it = tb.entrySet().iterator();
     while(it.hasNext()){
     Map.Entry entry = (Map.Entry)it.next();
     String tableName = (String)entry.getKey();
     String propertyName = (String)entry.getValue();
     String sql = "delete from " + tableName + " where " + propertyName + " = " + courseId;
     con.prepareStatement(sql);
     stmt.addBatch(sql);
     }
     stmt.executeBatch();
     con.commit();
     if (stmt != null) {
                stmt.close();
                stmt = null;
            }
}catch(Exception e){
try {
                con.rollback();
            } catch (SQLException e1) {
                throw new Exception();
            }
            throw new Exception();
}finally{
try {
                con.close();
            } catch (SQLException ex) {
                throw new Exception();
            }
}
}这段代码我想完成批量删除的工作,就是delete from table where id = ?有N条这样的语句,我想用上面的stmt.addBatch(sql);这种方法删除,但是不行啊?有错,谁能告诉我哪错了?

Answer 1

 //批量删除分类对象   
  public void deleteBat(Integer[] catNo){   
try {   
    Connection con=DBUtil.getInstance().getCon();   
    String sql="delete from cat where catno=?";   
    con.setAutoCommit(false);   
    PreparedStatement ps=con.prepareStatement(sql);   
    for (Integer in : catNo) {   
        ps.setInt(1, in);   
        ps.addBatch();   
    }   
    int[] result=ps.executeBatch();   
    con.commit();   
    for (int i : result) {   
        System.out.println(i);   
    }   
} catch (ClassNotFoundException e) {   
    e.printStackTrace();   
} catch (SQLException e) {   
    e.printStackTrace();   
} 
把Statement改成prepareStatement 看看

Answer 2

有两种方法可以进行批处理 第一种是用Statement  另外一种是PreparedStatement一、使用Statement   Statement sm=con.createStatement();
String sql="insert into errbills values()";
sm.addBatch(sql);
sql="delete from errbills where bno='124'";
sm.addBatch(sql);
sm.executeBatch();二、使用PreparedStatementString sql="delete from errblls where bno=?";
PreparedStatement ps=con.prepareStatement(sql);
for(int i=0;i<10;i++) {
   ps.setString(i);
   ps.addBatch();
}
ps.executeBatch(); Statement的addBatch(String s)是带参数的，PreparedStatement是不带参数的

Answer 3

注释掉一行：
public void deleteCourseInfo(String courseId) throws Exception {
        Connection con = pStrategy.getDBConnection();
        con.setAutoCommit(false);
        try {
            Statement stmt = con.createStatement();
            Map tb = AllCourseInfoTable.getTable();
            Iterator it = tb.entrySet().iterator();
            while(it.hasNext()){
                Map.Entry entry = (Map.Entry)it.next();
                String tableName = (String)entry.getKey();
                String propertyName = (String)entry.getValue();
                String sql = "delete from " + tableName + " where " + propertyName + " = " + courseId;
                //con.prepareStatement(sql);
                stmt.addBatch(sql);
            }
            stmt.executeBatch();
            con.commit();
            if (stmt != null) {
                stmt.close();
                stmt = null;
            }
        }catch(Exception e){
            try {
                con.rollback();
            } catch (SQLException e1) {
                throw new Exception();
            }
            throw new Exception();
        }finally{
            try {
                con.close();
            } catch (SQLException ex) {
                throw new Exception();
            }
        }
    }

Answer 4

你这一句con.prepareStatement(sql);要的有什么用啊。

Answer 5

//修改过的，试试看行不行
public void deleteCourseInfo(String courseId) throws Exception {
        Connection con = pStrategy.getDBConnection();
        con.setAutoCommit(false);
        try {
            Statement stmt = con.createStatement();
            Map tb = AllCourseInfoTable.getTable();
            Iterator it = tb.entrySet().iterator();
            while(it.hasNext()){
                Map.Entry entry = (Map.Entry)it.next();
                String tableName = (String)entry.getKey();
                String propertyName = (String)entry.getValue();
                String sql = "delete from " + tableName + " where " + propertyName + " = " + courseId;
                stmt.addBatch(sql);
            }
            stmt.executeBatch();
            con.commit();
            if (stmt != null) {
                stmt.close();
                stmt = null;
            }
        }catch(Exception e){
            try {
                con.rollback();
            } catch (SQLException e1) {
                throw new Exception();
            }
            throw new Exception();
        }finally{
            try {
                con.close();
            } catch (SQLException ex) {
                throw new Exception();
            }
        }
    }