spring jdbcTemp 批量插入数据（包含大字段）

首先在xml配置注入

 

<!-- 批插入处理clob字段 -->
  <bean id="nativeJdbcExtractor" class="org.springframework.jdbc.support.nativejdbc.CommonsDbcpNativeJdbcExtractor" />
  <bean id="lobHandler" class="org.springframework.jdbc.support.lob.OracleLobHandler">
        <property name="nativeJdbcExtractor" ref="nativeJdbcExtractor" />
    </bean>

 涉及部分代码：

public void saveNewsEntity(List<NewsEntity> entities) {
		
		//方案一：采用hibernate批量插入
		/*try {
			logger.info("------>队列获取插入的条数："+entities.size());
			for (int i = 0; i < entities.size(); i++) {
				NewsEntity ne = entities.get(i);
				create(ne);
				newsEntityTempDao.saveNewsEntityTemp(ne);
				
				if (i % 50 == 0) { //批量插入
					this.flush();
					this.clear();
				}
			}
		} catch (DataAccessException e) {
			logger.error("数据入库失败：", e);
		} catch (Exception e) {
			logger.error("数据入库失败：", e);
		}*/
		
		//方案二：采用jdbctemp批量插入
		final List<NewsEntity> enList = entities;
		logger.info("------>队列获取插入的条数："+enList.size());
		String sql = "INSERT INTO T_OC_ORIGINAL_NEWS" +
				"(ORIGINAL_NEWS_UID,MEDIA_NAME,MEDIA_TYPE,LANGUAGE,URL,CHARSET,CHANNEL_TYPE,TITLE,KEYWORDS," +
				"PUB_TIME,SOURCE,SOURCE_URL,AUTHOR,BROWSE_CNT,NEWS_CONTENT,COMMENTADD,EXPRESSION_LEVEL,RELATIVE_LINK," +
				"COLLECTION_UID,CLUE_TYPE,CREATE_DATETIME,CREATE_BY) " +
				"VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
		
		jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter(){
			@Override
			public int getBatchSize() {
				return enList.size();
			}
			@Override
			public void setValues(PreparedStatement ps, int i)
					throws SQLException {
				NewsEntity newsEntity = enList.get(i);
				ps.setString(1, newsEntity.getOriginalNewsUid());
				ps.setString(2, newsEntity.getMediaName());
				ps.setString(3, newsEntity.getMediaType());
				ps.setString(4, newsEntity.getLanguage());
				ps.setString(5, newsEntity.getUrl());
				ps.setString(6, newsEntity.getCharset());
				ps.setString(7, newsEntity.getChannelType());
				ps.setString(8, newsEntity.getTitle());
				ps.setString(9, newsEntity.getKeywords());
				ps.setString(10, newsEntity.getPubTime());
				ps.setString(11, newsEntity.getSource());
				ps.setString(12, newsEntity.getSourceUrl());
				ps.setString(13, newsEntity.getAuthor());
				ps.setLong(14, newsEntity.getBrowseCnt());
				// 大字段
				lobHandler.getLobCreator().setClobAsString(ps, 15, newsEntity.getNewsContent());
				ps.setString(16, newsEntity.getCommentadd());
				ps.setString(17, newsEntity.getExpressionLevel());
				ps.setString(18, newsEntity.getRelativeLink());
				ps.setString(19, newsEntity.getCollectionUid());
				ps.setString(20, newsEntity.getClueType());
				ps.setTimestamp(21, new Timestamp(newsEntity.getCreateDatetime().getTime()));
				ps.setString(22, newsEntity.getCreateBy());
			}
			
		} );
		
		//插入临时表
		String enTempSql = "INSERT INTO T_OC_ORIGINAL_NEWS_TEMP(ID,URL,CREATE_DATETIME) VALUES(?,?,?)";
		jdbcTemplate.batchUpdate(enTempSql, new BatchPreparedStatementSetter(){
			@Override
			public int getBatchSize() {
				return enList.size();
			}
			@Override
			public void setValues(PreparedStatement ps, int i)
					throws SQLException {
				NewsEntity newsEntity = enList.get(i);
				ps.setString(1, newsEntity.getOriginalNewsUid());
				ps.setString(2, newsEntity.getUrl());
				ps.setTimestamp(3, new Timestamp(newsEntity.getCreateDatetime().getTime()));
			}
			
		});
	}

备注：插入日期类型时，不能直接插入date，但可以以另一种方式，timestamp方式插入。

 

转载于:https://my.oschina.net/u/175660/blog/71306