`
zjx2388
  • 浏览: 1300088 次
  • 性别: Icon_minigender_2
  • 来自: 北京
社区版块
存档分类
最新评论

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

阅读更多
	/**
	 * 更新数据库已有的customer信息
	 * @param List<CustomerBean>
	 * @return 
	 */
	public int updateExistsInfo(List<CustomerBean> 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<CustomerBean>
	 * @return 
	 */
	public int insertNewInfo(List<CustomerBean> 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);

...

分享到:
评论

相关推荐

Global site tag (gtag.js) - Google Analytics