Java SSM Excel数据批量导入 基于JavaSSM实现Excel数据批量导入
梁云亮 人气:0想了解基于JavaSSM实现Excel数据批量导入的相关内容吗，梁云亮在本文为您仔细讲解JavaSSMExcel数据批量导入的相关知识和一些Code实例，欢迎阅读和指正，我们先划重点：JavaExcel数据导入,JavaExcel数据批量导入，下面大家一起来学习吧。
导入Maven依赖
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>${easyexcel.version}</version>
</dependency>
Mapper及映射文件
UserMapper.java
@Mapper
public interface UserMapper {
int batchInsert(@Param("list") List<User> list);
}
UserMapper.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.hc.mapper.UserMapper">
<resultMap id="BaseResultMap" type="com.hc.domain.User">
<!--@mbg.generated-->
<!--@Table tb_user-->
<id column="id" jdbcType="BIGINT" property="id" />
<result column="nickname" jdbcType="VARCHAR" property="nickname" />
<result column="avatar" jdbcType="VARCHAR" property="avatar" />
<result column="account" jdbcType="VARCHAR" property="account" />
<result column="password" jdbcType="VARCHAR" property="password" />
<result column="gender" jdbcType="TINYINT" property="gender" />
<result column="tel" jdbcType="VARCHAR" property="tel" />
<result column="email" jdbcType="VARCHAR" property="email" />
<result column="qq" jdbcType="VARCHAR" property="qq" />
<result column="wechat" jdbcType="VARCHAR" property="wechat" />
<result column="salt" jdbcType="VARCHAR" property="salt" />
<result column="info" jdbcType="VARCHAR" property="info" />
<result column="status" jdbcType="TINYINT" property="status" />
<result column="create_time" jdbcType="TIMESTAMP" property="createTime" />
<result column="update_time" jdbcType="TIMESTAMP" property="updateTime" />
</resultMap>
<sql id="Base_Column_List">
<!--@mbg.generated-->
id, nickname, avatar, account, `password`, gender, tel, email, qq, wechat, salt,
info, `status`, create_time, update_time
</sql>
<insert id="batchInsert" keyColumn="id" keyProperty="id" parameterType="map" useGeneratedKeys="true">
<!--@mbg.generated-->
insert into tb_user
(nickname, avatar, account, `password`, gender, tel, email, qq, wechat, salt, info,
`status`, create_time, update_time)
values
<foreach collection="list" item="item" separator=",">
(#{item.nickname,jdbcType=VARCHAR}, #{item.avatar,jdbcType=VARCHAR}, #{item.account,jdbcType=VARCHAR},
#{item.password,jdbcType=VARCHAR}, #{item.gender,jdbcType=TINYINT}, #{item.tel,jdbcType=VARCHAR},
#{item.email,jdbcType=VARCHAR}, #{item.qq,jdbcType=VARCHAR}, #{item.wechat,jdbcType=VARCHAR},
#{item.salt,jdbcType=VARCHAR}, #{item.info,jdbcType=VARCHAR}, #{item.status,jdbcType=TINYINT},
#{item.createTime,jdbcType=TIMESTAMP}, #{item.updateTime,jdbcType=TIMESTAMP})
</foreach>
</insert>
</mapper>
Excel监听器
@Log4j2
@Service
public class UserExcelListener extends AnalysisEventListener<User> {
@Resource
private UserMapper userMapper;
/**
* 批处理阈值
*/
private static final int BATCH_COUNT = 250;
@Getter
List<User> list = new ArrayList<>(BATCH_COUNT);
@Override
public void invoke(User user, AnalysisContext analysisContext) { //逐行读取数据
log.info("********** 解析到一条数据:{}", JSON.toJSONString(user));
list.add(user);
if (list.size() >= BATCH_COUNT) {
System.out.println("已经解析"+list.size()+"条数据");
//每250条，往数据库中存一次
int batchInsertRes = userMapper.batchInsert(list);
System.out.println(batchInsertRes);
list.clear();
}
}
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
log.info("**********所有数据解析完成！");
}
}
测试
@ExtendWith(SpringExtension.class)
@ContextConfiguration("/applicationContext.xml")
public class ExcelUtilTest {
@Resource
private UserExcelListener userExcelListener;
@Test
void userListener(){
EasyExcel.read("E:\\Projects\\WorksDisplay\\data\\users.xlsx", User.class, userExcelListener)
.sheet()
.doRead();
}
}
加载全部内容