一、pom文件<dependencies> <dependency> <groupid>junit</groupid> <artifactid>junit</artifactid> <version>4.12</version> </dependency> <dependency> <groupid>org.springframework.boot</groupid> <artifactid>spring-boot-starter-test</artifactid> <scope>test</scope> </dependency> <dependency> <groupid>com.alibaba</groupid> <artifactid>easyexcel</artifactid> <version>2.1.7</version> </dependency> <dependency> <groupid>com.alibaba</groupid> <artifactid>fastjson</artifactid> <version>1.2.62</version> </dependency> <dependency> <groupid>org.springframework.boot</groupid> <artifactid>spring-boot-starter</artifactid> </dependency> <dependency> <groupid>org.springframework.boot</groupid> <artifactid>spring-boot-starter-test</artifactid> <scope>test</scope> </dependency> <dependency> <groupid>com.mchange</groupid> <artifactid>c3p0</artifactid> <version>0.9.5.2</version> </dependency> <dependency> <groupid>com.baomidou</groupid> <artifactid>mybatis-plus-boot-starter</artifactid> <version>3.4.0</version> </dependency> <dependency> <groupid>org.springframework.boot</groupid> <artifactid>spring-boot-starter-jdbc</artifactid> </dependency> <dependency> <groupid>mysql</groupid> <artifactid>mysql-connector-java</artifactid> <scope>runtime</scope> </dependency> <dependency> <groupid>org.projectlombok</groupid> <artifactid>lombok</artifactid> <optional>true</optional> </dependency> <dependency> <groupid>org.springframework.boot</groupid> <artifactid>spring-boot-starter-web</artifactid> </dependency> <dependency> <groupid>org.projectlombok</groupid> <artifactid>lombok</artifactid> <optional>true</optional> </dependency> <dependency> <groupid>org.springframework.boot</groupid> <artifactid>spring-boot-starter-test</artifactid> <scope>test</scope> </dependency> </dependencies>
二、配置文件server.port=8080mybatis-plus.configuration.log-impl=org.apache.ibatis.logging.stdout.stdoutimplspring.datasource.url=jdbc:mysql://localhost:3306/ddb_resources?servertimezone=utcspring.datasource.driver-class-name=com.mysql.cj.jdbc.driverspring.datasource.username=rootspring.datasource.password=root
三、mapper文件@mapperpublic interface ddbbooknewwordmapper extends basemapper<ddbbooknewword> {}
四、service文件@servicepublic class demodao { @autowired ddbbooknewwordmapper mapper; public void save(list<datademo001> list) { // 如果是mybatis,尽量别直接调用多次insert,自己写一个mapper里面新增一个方法batchinsert,所有数据一次性插入 ddbbooknewword newword = new ddbbooknewword(); system.out.println("插入数据开始==============================="); for (datademo001 info : list) { newword.setapptype(0); newword.setfkbookid(info.getfkbookid()); newword.setword(info.getword()); newword.setsimpleexplain(info.getsimpleexplain()); newword.setimgurl("/incoming/ddb/wordimg/"+info.getfkbookid()+"/"+info.getimgurl()); newword.setsoundurl("/incoming/ddb/wordaudio/bookstudymp3/"+info.getfkbookid()+"/"+info.getsoundurl()); newword.setcreatetimeinms(1620983400709l); newword.setupdatetimeinms(1620983400709l); system.out.println(newword); mapper.insert(newword); } system.out.println("结束========================="); }}
五、实体类（数据库对应）@data@allargsconstructor@noargsconstructor@tostringpublic class ddbbooknewword { private int id; private string fkbookid; private string word; private string simpleexplain; private string imgurl; private long createtimeinms; private long updatetimeinms; private int apptype; private string soundurl;}
六、excel对应的实体类@datapublic class datademo001 { private string fkbookid; private string bookename; private string moudle; private string unit; private string word; private string soundurl; private string imgurl; private string simpleexplain;}
七、监听器// 有个很重要的点 demodatalistener 不能被spring管理，要每次读取excel都要new,然后里面用到spring可以构造方法传进去public class datademo001listener extends analysiseventlistener<datademo001> {// demodao demodao = springcontextholder.getbean(demodao.class); private static final logger logger = loggerfactory.getlogger(datademo001listener.class); /** * 每隔5条存储数据库，实际使用中可以3000条，然后清理list ，方便内存回收 */ private static final int batch_count = 100; list<datademo001> list = new arraylist<datademo001>(); /** * 假设这个是一个dao，当然有业务逻辑这个也可以是一个service。当然如果不用存储这个对象没用。 */ private demodao demodao;// public datademo001listener(demodao demodao) { // 这里是demo，所以随便new一个。实际使用如果到了spring,请使用下面的有参构造函数 this.demodao = demodao; } /** * 如果使用了spring,请使用这个构造方法。每次创建listener的时候需要把spring管理的类传进来 * * @param demodao */// public datademo001listener(demodao demodao) {// this.demodao = demodao;// } /** * 这个每一条数据解析都会来调用 * * @param data one row value. is is same as {@link analysiscontext#readrowholder()} * @param context */ @override public void invoke(datademo001 data, analysiscontext context) { system.out.println(json.tojsonstring(data)); list.add(data); // 达到batch_count了，需要去存储一次数据库，防止数据几万条数据在内存，容易oom if (list.size() >= batch_count) { savedata(); // 存储完成清理 list list.clear(); } } /** * 所有数据解析完成了 都会来调用 * * @param context */ @override public void doafterallanalysed(analysiscontext context) { // 这里也要保存数据，确保最后遗留的数据也存储到数据库 savedata(); logger.info("所有数据解析完成！"); } /** * 加上存储数据库 */ private void savedata() { demodao.save(list); }}
八、测试类 string path = "d:\java-demo\kuang-poi\"; @test public void simpleread() { string filename = path+"悠游阅读成长计划-单词部分.xls"; // 这里 需要指定读用哪个class去读，然后读取第一个sheet 文件流会自动关闭 easyexcel.read(filename, datademo001.class, new datademo001listener(demodao)).sheet().doread(); }
九、启动类(没啥用）@mapperscan("com.example.demo.mapper")@componentscan({"com.example.demo.test","com.example.demo.service"})@springbootapplicationpublic class demoapplication { public static void main(string[] args) { springapplication.run(demoapplication.class, args); }}
以上就是怎么运用java实现把excel中的数据导入mysql的详细内容。