JDBC小结

最近使用jdbc处理数据，来简单总结下这一时期使用过的

 Statement st = null; 用法

Connection conn = null;
Statement st = null;
DBHelper dbHelper = new DBHelper();
conn = dbHelper.getConn(); 

//更新,添加,删除
String sql = "update pairing set user_id = " + user_id + " WHERE id in (" + ids + ")";
try {
      st = conn.createStatement();
      st.execute(sql); //执行语句
   } catch (SQLException throwables) {
      throwables.printStackTrace();
   } 

---------------------------------------------------------------------------------------

Connection conn = null;
Statement st = null;
ResultSet rs = null;
DBHelper dbHelper = new DBHelper();
conn = dbHelper.getConn(); 

//查询
String sql = "SELECT id FROM pairing where user_id is null ORDER BY RAND() LIMIT 19493";
try {
      st = conn.createStatement();
      rs = st.executeQuery(sql); //获得结果集
      while (rs.next()) {
          idList.add(rs.getInt("id"));
      }
} catch (SQLException throwables) {
         throwables.printStackTrace();
}

PreparedStatement ps = null;

Connection connection = null;
ResultSet rs = null;
PreparedStatement ps = null;
DBHelper dbHelper = new DBHelper();
connection = dbHelper.getConn();

//查询
String sql = " SELECT judgement FROM eye_record WHERE scenenature = ? AND facenature = ? AND partid = ? ";
try {
     ps = connection.prepareStatement(sql);
     ps.setInt(1, scenenatureId);
     ps.setInt(2, facenatureId);
     ps.setInt(3, partId);
     //获取结果集
     rs = ps.executeQuery();
     while (rs.next()){
        judgementList.add(rs.getInt("judgement"));
     }
} catch (SQLException throwables) {
    throwables.printStackTrace();
} 


---------------------------------------------------------------------------------------



Connection connection = null;
PreparedStatement ps = null;
DBHelper dbHelper = new DBHelper();
connection = dbHelper.getConn();

//增,删,改
StringBuffer sb = new StringBuffer();
sb.append(" INSERT INTO result_data (partid, score, list_eye1, list_eye2, list_hand1,
        list_hand2, ans_list1, ans_list2, res_score,res_report) ")
        .append("VALUES(?,?,?,?,? ,?,?,?,?,?) ");
String sql = sb.toString();
try {
     ps = connection.prepareStatement(sql);
     ps.setInt(1, partid);
     ps.setDouble(2, old_val);
     ps.setString(3, String.valueOf(list_eye1));
     ....
     ps.executeUpdate(); //执行sql
} catch (SQLException throwables) {
    throwables.printStackTrace();
}

批量操作 

Connection connection = null;
PreparedStatement ps1 = null;
PreparedStatement ps2 = null;
ResultSet rs = null;
DBHelper dbHelper = new DBHelper();
connection = dbHelper.getConn();

StringBuffer sb = new StringBuffer();
sb.append(" UPDATE " + fileurl + " SET face_pos =  ")
        .append(" (SELECT facepos FROM (SELECT * FROM eye_record)f WHERE f.id = ?) ")
        .append(" WHERE non = ? ");
String sql1 = sb.toString();

String sql2 = "SELECT id,coord_x,coord_y,face_pos FROM " + fileurl;

try {
    connection.setAutoCommit(false); //关闭自动提交sql
    ps1 = connection.prepareStatement(sql1);
    for(int i = 0; i < 80; i++){
        ps1.setInt(1, i + eye_recordId);
        ps1.setInt(2, i + eye_recordId);
        ps1.addBatch(); //添加一条sql
        if(i%40 == 0){
           ps.executeBatch(); //执行sql
           ps.clearBatch(); //清空
           System.out.println(" 提交第 " + i + " 条--------------------------");
         }
     }
     ps1.executeBatch();//执行sql
     ps2 = connection.prepareStatement(sql2);
     rs = ps2.executeQuery();
     ...
     connection.commit(); //手动提交
} catch (SQLException throwables) {
     try {
         connection.rollback(); //回滚
         throwables.printStackTrace();
     } catch (SQLException e) {
         e.printStackTrace();
     }
}