PreparedStatement 批量更新
Posted onPreparedStatement 批量更新,插入数据到Oracle - 生活在爪洼岛上 - ITeye技术网站
生活在爪洼岛上
PreparedStatement 批量更新,插入数据到Oracle **
博客分类:
- ///
- /* 更新数据库已有的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("资源关闭失败!!!");
- }
- }
- }
/// / 更新数据库已有的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
- 浏览: 211663 次
- 性别:
- 来自: 北京
最近访客 更多访客>>
文章分类
- 全部博客 (451)
- J2SE (95)
- J2EE (93)
- database (66)
- by-talk (33)
- JavaScript (48)
- Tools/Software (48)
- Page (12)
- Linux (7)
- 职场 (18)
- Android (4)
- 网络编程 (4)
- 认证考试 (16)
- IELTS (2)
- Portal服务器 (1)
- Portlet容器 (1)
- Portlet 的区别 (1)
- Carefx_relate (2)
- Linux sub= (0)
- GWT (1)
-
社区版块
我的资讯 (0)
- 我的论坛 (24)
- 我解决的问题 (0)
存档分类
- 2012-04 (3)
- 2012-03 (4)
- 2011-10 (10)
-
评论排行榜
- jdk与jre的区别
最新评论
- zjx2388: yilv99 写道汗。。。<option value=& ... jstl中下拉列表有默认值的两种html写法
- yilv99: 汗。。。<option value="一类&q ... jstl中下拉列表有默认值的两种html写法
- java_ganbin: ... JAVA生成简单的随机字符串(a-zA-Z0-9)
- xlblank: // 把时间转换成整型 public St ... java中replaceAll()
- zdfeng: 我喜欢,java看着就是爽,虽然是android,基础,还是 ... Android 下载文件及写入SD卡 声明:ITeye文章版权属于作者,受法律保护。没有作者书面许可不得转载。若作者同意转载,必须以超链接形式标明文章原始出处和作者。 © 2003-2011 ITeye.com. All rights reserved. [ 京ICP证110151号 京公网安备110105010620 ]