PreparedStatement 批量更新

Posted on

PreparedStatement 批量更新,插入数据到Oracle - 生活在爪洼岛上 - ITeye技术网站

首页 资讯 精华 论坛 问答 博客 专栏 群组 更多 ▼

招聘 搜索

您还未登录 ! 登录 注册

生活在爪洼岛上

PreparedStatement 批量更新,插入数据到Oracle **

博客分类:

批量更新,插入代码 收藏代码

  1. ///
  2. /* 更新数据库已有的customer信息
  3. /* @param List
  4. /* @return
  5. /*/
  6. public int updateExistsInfo(List updateList){
  7. //查询的SQL语句
  8. String sql = "update t_customer set LICENSE_KEY=?,CORPORATE_NAME=?,INTEGRATED_CLASSIFICATION=?,BOSSHEAD=?," +
  9. "CONTACT_PHONE=?,ORDER_FREQUENCY=?,CONTACT_ADDRESS=?,USER_ID=? where CUSTOMER_ID=?" ;
  10. //插入需要的数据库对象
  11. Connection conn = null;
  12. PreparedStatement pstmt = null;
  13. try {
  14. conn = new DBSource().getConnection();
  15. //设置事务属性
  16. conn.setAutoCommit(false);
  17. pstmt = conn.prepareStatement(sql,ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
  18. for(CustomerBean cbean : updateList){
  19. pstmt.setString(1, cbean.getLicense_key());
  20. pstmt.setString(2, cbean.getCorporate_name());
  21. pstmt.setString(3, cbean.getIntegrated_classification());
  22. pstmt.setString(4, cbean.getBosshead());
  23. pstmt.setString(5, cbean.getContact_phone());
  24. pstmt.setString(6, cbean.getOrder_frequency());
  25. pstmt.setString(7, cbean.getContact_address());
  26. pstmt.setInt (8, cbean.getUser_id());
  27. pstmt.setInt (9, cbean.getCustomer_id());
  28. pstmt.addBatch();
  29. }
  30. int[] tt = pstmt.executeBatch();
  31. System.out.println("update : " + tt.length);
  32. //提交,设置事务初始值
  33. conn.commit();
  34. conn.setAutoCommit(true);
  35. //插入成功,返回
  36. return tt.length;
  37. }catch(SQLException ex){
  38. try{
  39. //提交失败,执行回滚操作
  40. conn.rollback();
  41. }catch (SQLException e) {
  42. e.printStackTrace();
  43. System.err.println("updateExistsInfo回滚执行失败!!!");
  44. }
  45. ex.printStackTrace();
  46. System.err.println("updateExistsInfo执行失败");
  47. //插入失败返回标志0
  48. return 0;
  49. }finally {
  50. try{
  51. //关闭资源
  52. if(pstmt != null)pstmt.close();
  53. if(conn != null)conn.close();
  54. }catch (SQLException e) {
  55. e.printStackTrace();
  56. System.err.println("资源关闭失败!!!");
  57. }
  58. }
  59. }
  60. ///
  61. /* 插入数据中没有的customer信息
  62. /* @param List
  63. /* @return
  64. /*/
  65. public int insertNewInfo(List insertList){
  66. //查询的SQL语句
  67. String sql = "insert into t_customer(CUSTOMER_ID," +
  68. "LICENSE_KEY,CORPORATE_NAME,INTEGRATED_CLASSIFICATION,BOSSHEAD,CONTACT_PHONE," +
  69. "ORDER_FREQUENCY,CONTACT_ADDRESS,USER_ID,CUSTOMER_NUM,CUSTOMER_CODING," +
  70. "INVESTIGATION_TIME,SMS_REC_FLAG,WAP_FLAG,PRICE_GATHERING_FLAG,SOCIETY_STOCK_FLAG," +
  71. "REGION_TYPE)" +
  72. "VALUES(CUSTOMER.NEXTVAL," +
  73. "?,?,?,?,?," +
  74. "?,?,?,?,?," +
  75. "TO_DATE(?,'YYYY-MM-DD'),?,0,0,0," +
  76. "?)" ;
  77. //插入需要的数据库对象
  78. Connection conn = null;
  79. PreparedStatement pstmt = null;
  80. try {
  81. conn = new DBSource().getConnection();
  82. //设置事务属性
  83. conn.setAutoCommit(false);
  84. pstmt = conn.prepareStatement(sql,ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
  85. for(CustomerBean cbean : insertList){
  86. pstmt.setString(1, cbean.getLicense_key());
  87. pstmt.setString(2, cbean.getCorporate_name());
  88. pstmt.setString(3, cbean.getIntegrated_classification());
  89. pstmt.setString(4, cbean.getBosshead());
  90. pstmt.setString(5, cbean.getContact_phone());
  91. pstmt.setString(6, cbean.getOrder_frequency());
  92. pstmt.setString(7, cbean.getContact_address());
  93. pstmt.setInt(8, cbean.getUser_id());
  94. pstmt.setString(9, "gyyc00000");//
  95. pstmt.setString(10, "95000000");//
  96. pstmt.setString(11, getToday());
  97. pstmt.setInt(12, cbean.getSms_rec_flag());
  98. pstmt.setInt(13, cbean.getRegion_type());
  99. pstmt.addBatch();
  100. }
  101. int[] tt = pstmt.executeBatch();
  102. System.out.println("insert : " + tt.length);
  103. //提交,设置事务初始值
  104. conn.commit();
  105. conn.setAutoCommit(true);
  106. //插入成功,返回
  107. return tt.length;
  108. }catch(SQLException ex){
  109. try{
  110. //提交失败,执行回滚操作
  111. conn.rollback();
  112. }catch (SQLException e) {
  113. e.printStackTrace();
  114. System.err.println("insertNewInfo回滚执行失败!!!");
  115. }
  116. ex.printStackTrace();
  117. System.err.println("insertNewInfo执行失败");
  118. //插入失败返回标志0
  119. return 0;
  120. }finally {
  121. try{
  122. //关闭资源
  123. if(pstmt != null)pstmt.close();
  124. if(conn != null)conn.close();
  125. }catch (SQLException e) {
  126. e.printStackTrace();
  127. System.err.println("资源关闭失败!!!");
  128. }
  129. }
  130. }
    /// / 更新数据库已有的customer信息 / @param List / @return // public int updateExistsInfo(List updateList){ //查询的SQL语句 String sql = "update t_customer set LICENSE_KEY=?,CORPORATE_NAME=?,INTEGRATED_CLASSIFICATION=?,BOSSHEAD=?," + "CONTACT_PHONE=?,ORDER_FREQUENCY=?,CONTACT_ADDRESS=?,USER_ID=? where CUSTOMER_ID=?" ; //插入需要的数据库对象 Connection conn = null; PreparedStatement pstmt = null; try { conn = new DBSource().getConnection(); //设置事务属性 conn.setAutoCommit(false); pstmt = conn.prepareStatement(sql,ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY); for(CustomerBean cbean : updateList){ pstmt.setString(1, cbean.getLicense_key()); pstmt.setString(2, cbean.getCorporate_name()); pstmt.setString(3, cbean.getIntegrated_classification()); pstmt.setString(4, cbean.getBosshead()); pstmt.setString(5, cbean.getContact_phone()); pstmt.setString(6, cbean.getOrder_frequency()); pstmt.setString(7, cbean.getContact_address()); pstmt.setInt (8, cbean.getUser_id()); pstmt.setInt (9, cbean.getCustomer_id()); pstmt.addBatch(); } int[] tt = pstmt.executeBatch(); System.out.println("update : " + tt.length); //提交,设置事务初始值 conn.commit(); conn.setAutoCommit(true); //插入成功,返回 return tt.length; }catch(SQLException ex){ try{ //提交失败,执行回滚操作 conn.rollback(); }catch (SQLException e) { e.printStackTrace(); System.err.println("updateExistsInfo回滚执行失败!!!"); } ex.printStackTrace(); System.err.println("updateExistsInfo执行失败"); //插入失败返回标志0 return 0; }finally { try{ //关闭资源 if(pstmt != null)pstmt.close(); if(conn != null)conn.close(); }catch (SQLException e) { e.printStackTrace(); System.err.println("资源关闭失败!!!"); } } } /// / 插入数据中没有的customer信息 / @param List / @return // public int insertNewInfo(List insertList){ //查询的SQL语句 String sql = "insert into t_customer(CUSTOMER_ID," + "LICENSE_KEY,CORPORATE_NAME,INTEGRATED_CLASSIFICATION,BOSSHEAD,CONTACT_PHONE," + "ORDER_FREQUENCY,CONTACT_ADDRESS,USER_ID,CUSTOMER_NUM,CUSTOMER_CODING," + "INVESTIGATION_TIME,SMS_REC_FLAG,WAP_FLAG,PRICE_GATHERING_FLAG,SOCIETY_STOCK_FLAG," + "REGION_TYPE)" + "VALUES(CUSTOMER.NEXTVAL," + "?,?,?,?,?," + "?,?,?,?,?," + "TO_DATE(?,'YYYY-MM-DD'),?,0,0,0," + "?)" ; //插入需要的数据库对象 Connection conn = null; PreparedStatement pstmt = null; try { conn = new DBSource().getConnection(); //设置事务属性 conn.setAutoCommit(false); pstmt = conn.prepareStatement(sql,ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY); for(CustomerBean cbean : insertList){ pstmt.setString(1, cbean.getLicense_key()); pstmt.setString(2, cbean.getCorporate_name()); pstmt.setString(3, cbean.getIntegrated_classification()); pstmt.setString(4, cbean.getBosshead()); pstmt.setString(5, cbean.getContact_phone()); pstmt.setString(6, cbean.getOrder_frequency()); pstmt.setString(7, cbean.getContact_address()); pstmt.setInt(8, cbean.getUser_id()); pstmt.setString(9, "gyyc00000");// pstmt.setString(10, "95000000");// pstmt.setString(11, getToday()); pstmt.setInt(12, cbean.getSms_rec_flag()); pstmt.setInt(13, cbean.getRegion_type()); pstmt.addBatch(); } int[] tt = pstmt.executeBatch(); System.out.println("insert : " + tt.length); //提交,设置事务初始值 conn.commit(); conn.setAutoCommit(true); //插入成功,返回 return tt.length; }catch(SQLException ex){ try{ //提交失败,执行回滚操作 conn.rollback(); }catch (SQLException e) { e.printStackTrace(); System.err.println("insertNewInfo回滚执行失败!!!"); } ex.printStackTrace(); System.err.println("insertNewInfo执行失败"); //插入失败返回标志0 return 0; }finally { try{ //关闭资源 if(pstmt != null)pstmt.close(); if(conn != null)conn.close(); }catch (SQLException e) { e.printStackTrace(); System.err.println("资源关闭失败!!!"); } } }

Notice:

//设置事务属性 conn.setAutoCommit(false); pstmt = conn.prepareStatement(sql,ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY); for(CustomerBean cbean : updateList){ pstmt.setString(1, cbean.getLicense_key()); ...
pstmt.addBatch(); } int[] tt = pstmt.executeBatch(); System.out.println("update : " + tt.length); //提交,设置事务初始值 conn.commit(); conn.setAutoCommit(true); ... 分享到:

[面试技巧]如何向你的面试官“发问”(转 ... | 最后期限中的经典管理名录

评论

发表评论

您还没有登录,请您登录后再发表评论

zjx2388的博客

zjx2388

  • 浏览: 211663 次
  • 性别: Icon_minigender_2
  • 来自: 北京
  • 最近访客 更多访客>>

danrise的博客

danrise

lijianlee的博客

lijianlee yslflsy的博客

yslflsy

ws_nihao的博客

ws_nihao

文章分类

存档分类

最新评论

希望本站内容对您有点用处,有什么疑问或建议请在后面留言评论
转载请注明作者(RobinChia)和出处 It so life ,请勿用于任何商业用途