使用java怎么将excel数据导入MySQL

这期内容当中小编将会给大家带来有关使用java怎么将excel数据导入MySQL，文章内容丰富且以专业的角度为大家分析和叙述，阅读完这篇文章希望大家可以有所收获。

一、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);        private static final int BATCH_COUNT = 100;    List<DataDemo001> list = new ArrayList<DataDemo001>();        private DemoDAO demoDAO;//    public DataDemo001Listener(DemoDAO demoDAO) {        // 这里是demo，所以随便new一个。实际使用如果到了spring,请使用下面的有参构造函数        this.demoDAO = demoDAO;    }    //    public DataDemo001Listener(DemoDAO demoDAO) {//        this.demoDAO = demoDAO;//    }        @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();        }    }        @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可以用来干什么

Java主要应用于：1. web开发；2. Android开发；3. 客户端开发；4. 网页开发；5. 企业级应用开发；6. Java大数据开发；7.游戏开发等。

上述就是小编为大家分享的使用java怎么将excel数据导入MySQL了，如果刚好有类似的疑惑，不妨参照上述分析进行理解。如果想知道更多相关知识，欢迎关注编程网数据库频道。