App下載

如何運(yùn)用Java實(shí)現(xiàn)把Excel中的數(shù)據(jù)導(dǎo)入MySQL

來源: 辦公任 2021-08-18 10:48:42 瀏覽數(shù) (3704)
反饋

一、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,盡量別直接調(diào)用多次insert,自己寫一個(gè)mapper里面新增一個(gè)方法batchInsert,所有數(shù)據(jù)一次性插入
        DdbBookNewWord newWord = new DdbBookNewWord();
        System.out.println("插入數(shù)據(jù)開始===============================");
        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("結(jié)束=========================");
    }
}

五、實(shí)體類（數(shù)據(jù)庫(kù)對(duì)應(yīng)）

@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對(duì)應(yīng)的實(shí)體類

@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;
}

七、監(jiān)聽器

// 有個(gè)很重要的點(diǎn) DemoDataListener 不能被spring管理，要每次讀取excel都要new,然后里面用到spring可以構(gòu)造方法傳進(jìn)去
public class DataDemo001Listener extends AnalysisEventListener<DataDemo001> {

//    DemoDAO demoDAO = SpringContextHolder.getBean(DemoDAO.class);

    private static final Logger LOGGER = LoggerFactory.getLogger(DataDemo001Listener.class);
    /**
     * 每隔5條存儲(chǔ)數(shù)據(jù)庫(kù)，實(shí)際使用中可以3000條，然后清理list ，方便內(nèi)存回收
     */
    private static final int BATCH_COUNT = 100;
    List<DataDemo001> list = new ArrayList<DataDemo001>();
    /**
     * 假設(shè)這個(gè)是一個(gè)DAO，當(dāng)然有業(yè)務(wù)邏輯這個(gè)也可以是一個(gè)service。當(dāng)然如果不用存儲(chǔ)這個(gè)對(duì)象沒用。
     */
    private DemoDAO demoDAO;
//
    public DataDemo001Listener(DemoDAO demoDAO) {
        // 這里是demo，所以隨便new一個(gè)。實(shí)際使用如果到了spring,請(qǐng)使用下面的有參構(gòu)造函數(shù)
        this.demoDAO = demoDAO;
    }

    /**
     * 如果使用了spring,請(qǐng)使用這個(gè)構(gòu)造方法。每次創(chuàng)建Listener的時(shí)候需要把spring管理的類傳進(jìn)來
     *
     * @param demoDAO
     */
//    public DataDemo001Listener(DemoDAO demoDAO) {
//        this.demoDAO = demoDAO;
//    }

    /**
     * 這個(gè)每一條數(shù)據(jù)解析都會(huì)來調(diào)用
     *
     * @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);
        // 達(dá)到BATCH_COUNT了，需要去存儲(chǔ)一次數(shù)據(jù)庫(kù)，防止數(shù)據(jù)幾萬條數(shù)據(jù)在內(nèi)存，容易OOM
        if (list.size() >= BATCH_COUNT) {
            saveData();
            // 存儲(chǔ)完成清理 list
            list.clear();
        }
    }

    /**
     * 所有數(shù)據(jù)解析完成了 都會(huì)來調(diào)用
     *
     * @param context
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        // 這里也要保存數(shù)據(jù)，確保最后遺留的數(shù)據(jù)也存儲(chǔ)到數(shù)據(jù)庫(kù)
        saveData();
        LOGGER.info("所有數(shù)據(jù)解析完成！");
    }

    /**
     * 加上存儲(chǔ)數(shù)據(jù)庫(kù)
     */
    private void saveData() {
        demoDAO.save(list);
    }
}

八、測(cè)試類

 String path = "D:\java-demo\kuang-poi\";

    @Test
    public void simpleRead() {
        String fileName = path+"悠游閱讀成長(zhǎng)計(jì)劃-單詞部分.xls";
        // 這里 需要指定讀用哪個(gè)class去讀，然后讀取第一個(gè)sheet 文件流會(huì)自動(dòng)關(guān)閉
        EasyExcel.read(fileName, DataDemo001.class, new DataDemo001Listener(demoDAO)).sheet().doRead();
    }

九、啟動(dòng)類(沒啥用）

@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);
    }
}

十、結(jié)構(gòu)

在這里插入圖片描述

那么，關(guān)于如何使用Java把Excel文件中的數(shù)據(jù)導(dǎo)入數(shù)據(jù)庫(kù)的詳細(xì)步驟和完整代碼的全部?jī)?nèi)容就介紹完了，想要了解更多關(guān)于Java中的數(shù)據(jù)操作的內(nèi)容，您可以閱讀W3Cschool以前發(fā)布過的一些相關(guān)內(nèi)容的文章，也可以多多關(guān)注接下來的內(nèi)容。


Java

0 人點(diǎn)贊