Springboot + oracle + easyExcel 实现批量导入导出_spring boot整合oracle 实现千万级数据快速导出-优快云博客

本文链接：https://blog.youkuaiyun.com/qq_47701945/article/details/129485530

1.设计客户表

id,姓名，年龄，职位，地址，家庭情况，电话，注册时间

字段	id	姓名	年龄	职位	地址	家庭情况	电话	注册时间
oracle类型	NUMBER(19,0)	VARCHAR2(255 CHAR)	NUMBER(9,0)	VARCHAR2(255 CHAR)	VARCHAR2(255 CHAR)	VARCHAR2(255 CHAR)	VARCHAR2(255 CHAR)	NUMBER(19,0)
java类型	long	String	int	String	String	String	String	long

2.创建数据库表

CREATE TABLE MY_CUSTOMER
   (	
    "ID" NUMBER(19,0)  NOT NULL PRIMARY KEY, 
	"NAME" VARCHAR2(255 CHAR) DEFAULT '_' NOT NULL , 
	"AGE" NUMBER(9,0) DEFAULT 18 NOT NULL , 
	"POSITION" VARCHAR2  (255 CHAR) DEFAULT '_' , 
	"ADDRESS" VARCHAR2(255 CHAR) DEFAULT '_' , 
	"SITUATION" VARCHAR2(255 CHAR) DEFAULT '_', 
	"PHONENUMBER" VARCHAR2(255 CHAR) DEFAULT '_', 
	"REGISTERTIME" NUMBER(19,0)
    
 )

3.为字段添加注释（日常开发都需要）

COMMENT ON COLUMN "MY_CUSTOMER"."ID" IS '主键'; 
 COMMENT ON COLUMN "MY_CUSTOMER"."NAME" IS '姓名'; 
 COMMENT ON COLUMN "MY_CUSTOMER"."AGE" IS '年龄'; 
 COMMENT ON COLUMN "MY_CUSTOMER"."POSITION" IS '职位'; 
 COMMENT ON COLUMN "MY_CUSTOMER"."ADDRESS" IS '地址'; 
 COMMENT ON COLUMN "MY_CUSTOMER"."SITUATION" IS '家庭情况'; 
 COMMENT ON COLUMN "MY_CUSTOMER"."PHONENUMBER" IS '电话'; 
 COMMENT ON COLUMN "MY_CUSTOMER"."REGISTERTIME" IS '注册时间';

4.创建序列（oracle中主键不能像mysql一样自动自增，需要定义序列）

create sequence MY_CUSTOMER_IP_SEQ 
 minvalue 1 -- 最小值为1
 nomaxvalue -- 不设置最大值
 increment by 1 -- 增长数字（步长为1）
 start with 1    -- 开始数字为1
 nocache;  -- 不缓存

5.创建实体类

@Data
public class MyCustomer implements Serializable {

    @ExcelIgnore
    private int id;
    @ExcelProperty("姓名")
    private  String name ;
    @ExcelProperty("年龄")
    private Integer age ;
    @ExcelProperty("职位")
    private  String position;
    @ExcelProperty("地址")
    private String address;
    @ExcelProperty("家庭情况")
    private  String situation;
    @ExcelProperty("电话号码")
    private String phoneNumber;
    @ExcelIgnore
    private  Long registerTime;
    @ExcelProperty("注册时间")
    @ColumnWidth(20)
    private String registerStr;
}

需要实现序列化，因为无论是使用dubbo还是easyExcel的时候都需要序列化

6.创建查询参数对象

@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
public class MyCustomerQuery implements Serializable {
    /**通过日期来筛选，开始时间*/
    private String startTime ;
    /**通过日期来筛选，结束时间*/
    private String endTime ;
    /**开始时间对应的时间戳*/
    private Long begin;
    /**结束时间对应的时间戳*/
    private Long end;
    /**姓名*/
    private String name;
}

需要实现序列化

7.定义Dao层接口

@Mapper
public interface MyCustomerDao {

    /**
     * insertMyCustomerBatch 批量插入客户
     */

    int insertMyCustomerBatch(@Param("myCustomers") List<MyCustomer> myCustomers) ;

    /**
     * 批量查询数据
     */

    List<MyCustomer> queryMyCustomerBatch(MyCustomerQuery myCustomerQuery);

}

这里还需要配置包扫描，否者无法找到对应的mapper

8.编写sql（MyCustomerDao.xml）

<insert id="insertMyCustomerBatch"  >

        insert into MY_CUSTOMER
        (id,NAME,AGE,POSITION,ADDRESS,SITUATION,PHONENUMBER,REGISTERTIME)
        select MY_CUSTOMER_IP_SEQ.nextval ,a.* from (
        <foreach collection="myCustomers" item="customer" index= "index" separator ="UNION">
            select
            <if test="customer.name != null and customer.name != ''">
                #{customer.name} as name,
            </if>
            <if test="customer.name == null or customer.name == ''">
                '-' as name,
            </if>
            <if test="customer.age != null and customer.age != ''">
                #{customer.age} as age,
            </if>
            <if test="customer.age == null or customer.age == ''">
                '-' as  age,
            </if>
            <if test="customer.position != null and customer.position != ''">
                #{customer.position}  as position,
            </if>
            <if test="customer.position == null or customer.position == ''">
                '-'as position,
            </if>
            <if test="customer.address != null and customer.address != ''">
                #{customer.address} as address,
            </if>
            <if test="customer.address == null or customer.address == ''">
                '-' as address,
            </if>
            <if test="customer.situation != null and customer.situation != ''">
                #{customer.situation} as situation,
            </if>
            <if test="customer.situation == null or customer.situation == ''">
                '-' as situation,
            </if>
            <if test="customer.phoneNumber != null and customer.phoneNumber != ''">
                #{customer.phoneNumber} as  phonenumber,
            </if>
            <if test="customer.phoneNumber == null or customer.phoneNumber == ''">
                '-' as phonenumber,
            </if>
            <if test="customer.registerTime != null and customer.registerTime != ''">
                #{customer.registerTime} as  registertime
            </if>
            <if test="customer.registerTime == null or customer.registerTime == ''">
                '-' as registertime
            </if>
            from DUAL
        </foreach>
        ) a

    </insert>

mybatis动态标签foreach中collection的值需要我们指定，这个值在dao方法中使用@Param指定

在最后一个if判断的时候不需要使用 ‘，’逗号，因为在拼接SQL的时候最后一个参数不需要逗号，如果写了会报为知from错误

这里的dual是oracle中虚拟表，只有一行

这里使用union分割，目的是将我们使用dual得到的一行行数据拼接起来组成一个表

这里的MY_CUSTOMER_IP_SEQ.nextval就是获取序列的下一个值，这样就可以保证id自增

oracle特殊语法，可以直接通过insert 语句直接将一张虚拟表的数据插入到数据库中，不需要 insert value

<select id="queryMyCustomerBatch" resultType="com.mysteel.demo.MyCustomer">
            select *
                from my_customer
            <where>
                <if test="name !=null and name != '' ">
                instr(name, #{name}) > 0
                </if>
                <if test="begin != null">
                    and REGISTERTIME >= #{begin}
                </if>
                <if test="end != null">
                    and REGISTERTIME &lt; #{end}
                </if>
            </where>
    </select>

9.编写service接口

public interface MyCustomerService {
    /***
     * easyexcel批量导出
     * @param myCustomerQuery 查询参数，根据姓名和起始时间模糊查询
     * @return java.util.List<com.mysteel.demo.MyCustomer>
     */
    List<MyCustomer> queryMyCustomerBatch(MyCustomerQuery myCustomerQuery);


    /**
     * 从excel中批量插入数据库
     * @param myCustomers
     * @return java.lang.Integer
     */
    public Boolean insertMyCustomerBatch(List<MyCustomer> myCustomers) ;

}

10.编写serviceimpl

@Service("myCustomerService")
public class MyCustomerServiceImpl  implements MyCustomerService{
    @Autowired
    private MyCustomerDao myCustomerDao;

    @Override
    public List<MyCustomer> queryMyCustomerBatch(MyCustomerQuery myCustomerQuery) {
        List<MyCustomer> myCustomers = new ArrayList<>();
        if (myCustomerQuery !=null) {
            myCustomers = myCustomerDao.queryMyCustomerBatch(myCustomerQuery);
            if (!CollectionUtils.isEmpty(myCustomers)){
                for (MyCustomer customer : myCustomers){
                    //注册时间转换成字符串
                    customer.setRegisterStr(DateUtils.formatDate(customer.getRegisterTime()));
                }
            }
        }
        return myCustomers;
    }

    @Override
    @Transactional(propagation = Propagation.REQUIRED)
    public Boolean insertMyCustomerBatch(List<MyCustomer> myCustomers) {
        int flag = myCustomerDao.insertMyCustomerBatch(myCustomers);
        return flag != 0;
    }

}

我的项目是写在dubbo应用中的，这里的@Service注解需要带上value属性，否则启动dubbo的时候会报错

11.编写AO层接口

public interface MyCustomerAO {
     void addMyCustomerBatchToExcel(MyCustomerQuery myCustomerQuery, HttpServletResponse response) throws IOException;

     void insertMyCustomerBatchFromExcel(MyCustomerQuery myCustomerQuery, MultipartFile file) throws IOException;
}

12. 编写AO接口实现类

@Component
public class myCustomerAOImpl implements MyCustomerAO {
    @Autowired
    MyCustomerService myCustomerService;
    @Override
    public void addMyCustomerBatchToExcel(MyCustomerQuery myCustomerQuery, HttpServletResponse response) throws IOException {
        // 获取需要查询写入到excel中的Customer集合
        List<MyCustomer> myCustomers = myCustomerService.queryMyCustomerBatch(myCustomerQuery);

        //调用方法，将数据写入excel
        EasyExcel.write(response.getOutputStream(),MyCustomer.class).sheet("写操作").doWrite(myCustomers);
    }

    @Override
    public void insertMyCustomerBatchFromExcel(MyCustomerQuery myCustomerQuery, MultipartFile file) throws IOException {
        // 调用easyExcel自定义监听器，执行批量导入操作
        EasyExcel.read(file.getInputStream(), MyCustomer.class, new ExcelListener(myCustomerService)).sheet().doRead();
    }
}

13.编写easyExcel读数据的监听器类

public class ExcelListener extends AnalysisEventListener<MyCustomer> {
    private MyCustomerService myCustomerService;
    public ExcelListener(){

    }
    /**因为继承了AnalysisEventListener，而 AnalysisEventListener底层实现了过滤器，
     * 故这个类不能交给spring管理，所以这能通过构造方法注入
     * */
    public ExcelListener(MyCustomerService myCustomerService){
        this.myCustomerService = myCustomerService;
    }

    /**定义了一个list，用于将每一行的数据存储起来，我们在dao层直接批量插入list*/
    private final List<MyCustomer> totalCustomerBatch = new ArrayList<>();
    @Override
    public void invoke(MyCustomer data, AnalysisContext context) {
        // 将excel中日期格式字符串转换为long类型的
        String registerStr = data.getRegisterStr();
        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        try {
            Date date = simpleDateFormat.parse(registerStr);
            data.setRegisterTime(date.getTime());
        } catch (ParseException e) {
            throw new RuntimeException(e);
        }
        // 修改完日期格式以后将所有的customer放入list，全部执行完以后执行批量查询
        totalCustomerBatch.add(data);
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        //执行批量插入
        myCustomerService.insertMyCustomerBatch(totalCustomerBatch);
    }
}

14.创建controller

@RequestMapping("/mycustomer")
@Controller
public class MyCustomerController {
    @Autowired
    private MyCustomerAO myCustomerAO;
    /***
     * 进入到我的客户页面
     * @return java.lang.String
     */
    @RequestMapping("toExcelPage")
    public String toMyCustomerPage () {
        return "demo/testMyCustomer";
    }

    /***
     * easyExcel写操作，将数据写入excel
     * @param myCustomerQuery 模糊查询对象，根据姓名、开始时间和结束时间匹配
     * @param response  响应对象，将查询到的数据会写
     */
    @RequestMapping("toExcel")
    public void addMyCustomerBatchToExcel(MyCustomerQuery myCustomerQuery ,HttpServletResponse response) throws ParseException, IOException {
        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        //将前端传来的日期时间转换成时间错，用于限制日期的范围
        if (!StringUtil.isBlank(myCustomerQuery.getStartTime())) {
            myCustomerQuery.setBegin(simpleDateFormat.parse(myCustomerQuery.getStartTime()).getTime());
        }
        if (!StringUtil.isBlank(myCustomerQuery.getEndTime())) {
            myCustomerQuery.setBegin(simpleDateFormat.parse(myCustomerQuery.getEndTime()).getTime());
        }
        //设置response的格式
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        // 这里URLEncoder.encode可以防止中文乱码 当然和easyExcel没有关系
        String fileName = URLEncoder.encode("userInfo", "UTF-8").replaceAll("\\+", "%20");
        response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");

        myCustomerAO.addMyCustomerBatchToExcel(myCustomerQuery,response);
    }

    /***
     * easyExcel文件读操作，将excel文件中的内容读入数据库
     * @param myCustomerQuery
     * @param file
     */
    @RequestMapping("toDatabase")
    public void selectMyCustomerFromExcel(MyCustomerQuery myCustomerQuery,
                                            @RequestParam("importExcel") MultipartFile file) throws IOException {
        //获取文件的路径
        String fileName = file.getOriginalFilename();
        myCustomerAO.insertMyCustomerBatchFromExcel(myCustomerQuery ,file);
    }
}

15：添加dubbo的消费者和服务者

dubbo-consumer.xml

<dubbo:reference interface="com.mysteel.demo.service.MyCustomerService" id="myCustomerService" timeout="10000" check="false"/>

dubbo-provider.xml

<dubbo:service interface="com.mysteel.demo.service.MyCustomerService" ref="myCustomerService" timeout="10000"/>

16.创建前端页面

<html >
<head>
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8"/>
    <meta http-equiv="pragma" content="no-cache"/>
    <meta http-equiv="cache-control" content="no-cache, must-revalidate"/>
    <meta http-equiv="expires" content="0"/>
    <title>Document</title>
</head>
<body>

<form id="frm2" name="frm2" method="post" action="/mycustomer/toExcel.ms">
    姓名&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;：<input type="text" name="name" id="operateStartTime" size="15"  value="">
    <br/>
    开始时间：<input type="date" name="startTime" id="startTime" size="15"  value="">
    <br/>
    结束时间：<input type="date" name="endTime" id="endTime" value="">
    <br/>
    <input name="export" type="submit" class="buttonBg"  value="导出" style="color: red"/>
</form>

<br/>

<form action="/mycustomer/toDatabase.ms" method="post" enctype="multipart/form-data">
    <div class="input-line">
        <input type="file" name="importExcel" id="importExcel">
        <input type="submit" value="导入">
    </div>
</form>

    </body>
</html>

我的前端页面是通过volecity与后端交互的，所以文件的后缀是.ms，这里需要根据自己的引擎修改

到这里代码部分已经写完了，我们需要测试我们的功能就需要向我们的oracle中添加几条测试数据

INSERT into MY_CUSTOMER (id,name,age,POSITION,ADDRESS,SITUATION,PHONENUMBER,REGISTERTIME) values (MY_CUSTOMER_IP_SEQ.nextval,'张三',18,'','','','',1678348319000);
INSERT into MY_CUSTOMER (id,name,age,POSITION,ADDRESS,SITUATION,PHONENUMBER,REGISTERTIME) values (MY_CUSTOMER_IP_SEQ.nextval,'李四',19,'','','','',1678348319000);