目录一、pom文件二、配置文件三、mapper文件四、service文件五、实体类（数据库对应）六、excel对应的实体类七、监听器八、测试类九、启动类(没啥用）十、结构
一、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的文章就介绍到这了,更多相关java将excel数据导入MySQL内容请搜索以前的文章或继续浏览下面的相关文章希望大家以后多多支持！
不给自己一点轻松的机会，好象世界的每个角落都需要自己的脚去留个痕迹，才叫人生。
