由于项目需要生成数万条数据持久化到数据库当中,然后就在程序中封装了一个List集合对象,需要把该集合中的实体插入到数据库中,应该要比循环对象一个一个插入的效果更好吧,这个肯定不是最优的方法,但应该可以能减少和数据库连接吧,由于才疏学浅,有什么更好的办法进行大量数据插入到数据库中,一定要赐教啊!
具体看代码吧:
Controller层代码:
/**
* 新增和修改的数据进行保存
* @param festivalbeforeJson
* @return
*/
@RequestMapping(value="/save", produces = MediaType.APPLICATION_JSON_VALUE)
public Map<String, Object> save() {
Map<String, Object> modelMap = new HashMap<>();
List<StorageFestivalbeforeCheck> data = new ArrayList<StorageFestivalbeforeCheck>();
//向数据库插入120000条记录
for (int i=1; i<=120000; i++) {
data.add(getFestivalbefore(i));
}
System.out.println("查看List集合存放了多少条记录。。。。。。。。"+data.size());
long beginTime = System.currentTimeMillis();
System.out.println("。。。。。。。。。。。开始插入。。。。。。。。。。。。。");
int a = 3000;//每次提3000条
int loop = (int) Math.ceil(data.size() / (double) a);
List<StorageFestivalbeforeCheck> tempList = new ArrayList<StorageFestivalbeforeCheck>(a);
int start, stop;
for (int i = 0; i < loop; i++) {
tempList.clear();
start = i * a;
stop = Math.min(i * a + a - 1, data.size() - 1);
System.out.println("range:" + start + " - " + stop);
for (int j = start; j <= stop; j++) {
tempList.add(data.get(j));
}
festivalbeforeCheckService.saveList(tempList);
System.out.println("已经插入" + (stop + 1) + " 条");
}
modelMap.put("status", "success");
long endTime = System.currentTimeMillis();
System.out.println("。。。。。。。。。。插入完成,耗时 " + (endTime - beginTime) + " 毫秒!。。。。。。。。。");
return modelMap;
}
或者使用下面的更加简便的方法:
@RequestMapping(value="/save", produces = MediaType.APPLICATION_JSON_VALUE)
public Map<String, Object> save() {
Map<String, Object> modelMap = new HashMap<>();
int pageCount = 0;
List<StorageFestivalbeforeCheck> data = new ArrayList<StorageFestivalbeforeCheck>();
//向数据库插入120000条记录
for (int i=1; i<=120000; i++) {
data.add(getFestivalbefore(i));
}
List<List<StorageFestivalbeforeCheck>> allOrdersList = Lists.partition(data , 3000);
for (List<StorageFestivalbeforeCheck> tempOrderList : allOrdersList) {
if(CollectionUtils.isNotEmpty(tempOrderList)){
festivalbeforeCheckService.saveList(tempOrderList);
pageCount++;
if(tempOrderList.size() == 3000){
logger.info("已经插入条数:{}", pageCount * 3000);
}else{
logger.info("已经插入条数:{}", ((pageCount-1) * 3000 + tempOrderList.size()) );
}
}
}
return modelMap;
}
//设置保存的记录
public StorageFestivalbeforeCheck getFestivalbefore(int i) {
StorageFestivalbeforeCheck festivalbeforeCheck = new StorageFestivalbeforeCheck();
festivalbeforeCheck.setHouseId(i);
festivalbeforeCheck.setOrgId(11);
festivalbeforeCheck.setCheckDate(new Date());
festivalbeforeCheck.setCheckPerson("孙悟空");
festivalbeforeCheck.setCheckRecord(1);
festivalbeforeCheck.setImplementCase(0);
festivalbeforeCheck.setDutyArrangement(1);
festivalbeforeCheck.setOperationArrangement(1);
festivalbeforeCheck.setLeadSystem(0);
festivalbeforeCheck.setSafetyEducation(1);
festivalbeforeCheck.setCleaningDrain(1);
festivalbeforeCheck.setEmergencyFacilities(0);
festivalbeforeCheck.setEmergencyPlan(1);
festivalbeforeCheck.setProblem("发现有漏水情况"+i);
festivalbeforeCheck.setOpinion("必须赶紧完善"+i);
return festivalbeforeCheck;
}
xxxMapper.xml:
<insert id="saveList" parameterType="java.util.List">
insert into storage_festivalbefore_check (
id, house_id, org_id,
check_date, check_person, check_record,
implement_case, duty_arrangement, operation_arrangement,
lead_system, safety_education, cleaning_drain,
emergency_facilities, emergency_plan, problem,
opinion
)
VALUES
<foreach collection="list" item="item" index= "index" separator =",">
(
#{item.id,jdbcType=INTEGER}, #{item.houseId,jdbcType=INTEGER}, #{item.orgId,jdbcType=INTEGER},
#{item.checkDate,jdbcType=TIMESTAMP}, #{item.checkPerson,jdbcType=VARCHAR}, #{item.checkRecord,jdbcType=INTEGER},
#{item.implementCase,jdbcType=INTEGER}, #{item.dutyArrangement,jdbcType=INTEGER}, #{item.operationArrangement,jdbcType=INTEGER},
#{item.leadSystem,jdbcType=INTEGER}, #{item.safetyEducation,jdbcType=INTEGER}, #{item.cleaningDrain,jdbcType=INTEGER},
#{item.emergencyFacilities,jdbcType=INTEGER}, #{item.emergencyPlan,jdbcType=INTEGER}, #{item.problem,jdbcType=VARCHAR},
#{item.opinion,jdbcType=VARCHAR}
)
</foreach>
</insert>
xxxMapper.java(部分)
void saveList(List<StorageFestivalbeforeCheck> festivalbeforeList);
下面是Mybatis中关于foreach的介绍(从网上查看并根据自己的理解整理的):
foreach的主要用在构建in条件中,它可以在SQL语句中进行迭代一个集合。
foreach元素的属性主要有 item,index,collection,open,separator,close。
item表示集合中每一个元素进行迭代时的别名,index指定一个名字,用于表示在迭代过程中,每次迭代到的位置,open表示该语句以什么开始,separator表示在每次进行迭代之间以什么符号作为分隔 符,close表示以什么结束,在使用foreach的时候最关键的也是最容易出错的就是collection属性,该属性是必须指定的,但是在不同情况 下,该属性的值是不一样的,主要有一下3种情况:
1.如果传入的是单参数且参数类型是一个List的时候并且List中保存时实体类对象,collection属性值为list,如上面的代码案例;如果List中存放的是Integer这种的封装类型,则collection属性值就是你定义的属性参数名称,比如:
List<StorageFestivalbeforeCheck> selectIds(@Param("Ids") List<Integer> Ids);
<select id="selectIds" resultMap="BaseResultMap">
select
<include refid="Base_Column_List"/>
from storage_festivalbefore_check
<where>
id in
<foreach collection="Ids" item="item" open="(" separator="," close=")">
#{item}
</foreach>
</where>
ORDER BY create_time DESC
</select>
2.如果传入的是单参数且参数类型是一个array数组的时候,collection的属性值为array
3.如果传入的参数是多个的时候,我们就需要把它们封装成一个Map了,当然单参数也可以封装成map
使用批量插入执行的SQL语句应该等价于:
insert into storage_festivalbefore_check
( id, house_id, org_id, check_date, check_person, check_record, implement_case, duty_arrangement, operation_arrangement, lead_system, safety_education, cleaning_drain, emergency_facilities, emergency_plan, problem, opinion )
VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )
,( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )
,( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )
, ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )
, ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )
, ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )
, ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )
, ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )
, ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )
, ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )
, ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )
, ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )
, ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )
, ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )
, ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )
, ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )
, ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )
, ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )
, ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )
, ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )