教你使用java将excel数据导入MySQL

2024-04-02 19:04:59 195人浏览八月长安

摘要

目录一、pom文件二、配置文件三、mapper文件四、service文件五、实体类（数据库对应）六、excel对应的实体类七、监听器八、测试类九、启动类(没啥用）十、结构一、pom文

一、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=8080

mybatis-plus.configuration.log-impl=org.apache.ibatis.logging.stdout.StdOutImpl

spring.datasource.url=jdbc:mysql://localhost:3306/ddb_resources?serverTimezone=UTC
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.username=root
spring.datasource.passWord=root

三、mapper文件


@Mapper
public interface DdbBookNewWordMapper extends BaseMapper<DdbBookNewWord> {
}

四、service文件


@Service
public 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
@ToString
public 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对应的实体类


@Data
public 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"})
@SpringBootApplication
public class DemoApplication {
    public static void main(String[] args) {
        SpringApplication.run(DemoApplication.class, args);
    }
}