hibernate+spring 批量添加人员

由于用hibernate很耗内存，在此用sql;

	@SuppressWarnings("unchecked")
	public void saveClassInform(final Inform inform) {
		//保存到发件箱
		informDAO.save(inform);
		String idString  = inform.getReceiveIdList().substring(0,inform.getReceiveIdList().length()-1);
		String hql ="select distinct(o.user.uid) from TribeMember o where o.tribe.tid in ("+idString+") and o.status=1";
		final List tmList = tribeMemberDAO.findAllByHQL(hql);
		//保存到收件人收件箱
		if(null!=tmList){
			informDAO.getHibernateTemplate().execute(new HibernateCallback() {			
			public Object doInHibernate(Session session) throws HibernateException,
						SQLException {
			Iterator iterator = tmList.iterator();
			Connection connection = SessionFactoryUtils.getDataSource(informDAO.getHibernateTemplate().getSessionFactory()).getConnection() ;
			PreparedStatement preparedStatement = null;
			String sql = "insert into inform_receive(inform_id,user_id,is_read,status) value(?,?,?,?)";
			preparedStatement = connection.prepareStatement(sql);
			while(iterator.hasNext()){	
				int userId =(Integer.parseInt(iterator.next().toString()));
				preparedStatement.setInt(1, inform.getIid());
				preparedStatement.setInt(2,userId );
				preparedStatement.setInt(3, 0);
				preparedStatement.setInt(4, 1);
				preparedStatement.addBatch();
			}
				preparedStatement.executeBatch();
 				preparedStatement.close();
 				session.flush();
 				session.close();
 				connection.close();
				return null;
			}
		});
	}
	}

 另外在这里附上jdbc批量插数据的代码：

/**
	 * 插入到本地数据库
	 * @param preparedStatement
	 * @param list
	 * @throws SQLException
	 */
	public void addsMedicare(PreparedStatement preparedStatement,List<Medicare> list) throws SQLException {
		List<Medicare> medicareList = list;
		if(CollectionUtils.isNotEmpty(medicareList)){
			PreparedStatement pstmt = null;
			try {
				pstmt = preparedStatement;
				for(int i=0;i<medicareList.size();i++){
					pstmt.setString(1, medicareList.get(i).getMedicareNo());
					pstmt.setString(2, medicareList.get(i).getMedicareUsername());
					pstmt.setString(3, null);
					pstmt.setString(4, medicareList.get(i).getMedicareIdentityNo());
					pstmt.setFloat(5, medicareList.get(i).getMedicareBalance());
					pstmt.setFloat(6, medicareList.get(i).getMedicareInfund());
					pstmt.setFloat(7, medicareList.get(i).getMedicareOutfund());
					pstmt.setTimestamp(8, medicareList.get(i).getMedicareLastModifyTime());
					pstmt.setInt(9, medicareList.get(i).getMedicareFlag()==null?0:medicareList.get(i).getMedicareFlag());
					pstmt.addBatch();
				}	
				pstmt.executeBatch();
			
			} catch (Exception e) {
				e.printStackTrace();
				throw new SQLException(e.getMessage());
			} 
		}
	}
	/**
	 * 插入到语音数据库
	 * @param preparedStatement
	 * @param list
	 * @throws SQLException
	 */
	public void addsIvrMedicare(PreparedStatement preparedStatement,List<Medicare> list) throws SQLException {
		List<Medicare> medicareList = list;
		if(CollectionUtils.isNotEmpty(medicareList)){
			PreparedStatement pstmt = null;
			try {
				pstmt = preparedStatement;
				for(int i=0;i<medicareList.size();i++){
					pstmt.setString(1, medicareList.get(i).getMedicareNo());
					pstmt.setString(2, medicareList.get(i).getMedicareUsername());
					pstmt.setFloat(3, medicareList.get(i).getMedicareBalance());
					pstmt.setFloat(4, medicareList.get(i).getMedicareInfund());
					pstmt.setFloat(5, medicareList.get(i).getMedicareOutfund());
					pstmt.setTimestamp(6, medicareList.get(i).getMedicareLastModifyTime());
					pstmt.addBatch();
				}	
				pstmt.executeBatch();
			} catch (Exception e) {
				e.printStackTrace();
				throw new SQLException(e.getMessage());
			} 
		}
	}
	
	/**
	 * 同步
	 * @throws SQLException
	 */
	public void synchronizeMedicare() throws SQLException {
		long beginTimestamp = System.currentTimeMillis();
		String synMedicareSql = QUERY_SYN_MEDICARE;
		//已有同步数据 ，同步该时间之后的数据
		Timestamp curMaxTime = medicareService.getCurMaxModifyTime();
		if(curMaxTime!= null){
			synMedicareSql += " where ckr.cmodifyTime >  '"+curMaxTime+"'";
		}
		Connection synConn = null; //同步数据库连接
		Connection localConn = null; //本地数据库连接
		Connection ivrConn = null;   //语音数据库
		
		PreparedStatement localPstmt = null; 
		PreparedStatement synPstmt = null; 
		PreparedStatement ivrPstmt = null;
		ResultSet rs = null;
		List<Medicare> list = new ArrayList<Medicare>();
		int index = 0;
		try {
			synConn = DBAccessor.getSynConnection();
			synPstmt = synConn.prepareStatement(synMedicareSql);
			localConn = DBAccessor.getLocalConnection();
			localPstmt = localConn.prepareStatement(INSERT_TO_MEDICARE);
			localConn.setAutoCommit(false);
			ivrConn = DBIVRConnectionFactory.getInstance().getConnection();
			ivrPstmt = ivrConn.prepareStatement(INSERT_TO_IVR_MEDICARE);
			ivrConn.setAutoCommit(false);
			Medicare fo = null;
		
			rs = synPstmt.executeQuery();
			while(rs.next()){
				fo = new Medicare();
				fo.setMedicareId(rs.getInt(1));
				fo.setMedicareNo(rs.getString(2));
				fo.setMedicareUsername(rs.getString(3));
				fo.setMedicareIdentityNo(rs.getString(4));
				fo.setMedicareBalance(rs.getFloat(5));
				fo.setMedicareInfund(rs.getFloat(6));
				fo.setMedicareOutfund(rs.getFloat(7));
				fo.setMedicareLastModifyTime(rs.getTimestamp(8));
				fo.setMedicareFlag(0); //同步过来的都是未发送
				list.add(fo);
				index++;
				if(index%2000==0){
					addsMedicare(localPstmt,list);
					addsIvrMedicare(ivrPstmt, list);
					list.clear();
				}
			}
			if(CollectionUtils.isNotEmpty(list)){
				addsMedicare(localPstmt,list);
				addsIvrMedicare(ivrPstmt, list);
				list.clear();
			}
			localConn.commit();
			ivrConn.commit();
		} catch (Exception e) {
			e.printStackTrace();
			throw new SQLException(e.getMessage());
		} finally{
	        try 
	        { 
	        	if (synPstmt != null){synPstmt.close(); } 
	        	if (synConn != null){synConn.close();} 
	        	
	        	if (localPstmt != null) {localPstmt.close();} 
	        	if (localConn != null){localConn.close();}
	        	
	        	if (ivrPstmt != null){ivrPstmt.close(); }
	        	if (ivrConn != null){ivrConn.close(); }
	        } 
	        catch(Exception e) {} 
        }
		logger.debug("======== 花费时间"+(System.currentTimeMillis()-beginTimestamp)/(60*1000.0)+"分钟"+"共插入"+index+"条数据");
	}

 

评论

1 楼 llfzy 2010-02-26  

还用反射哦！