Shulou(Shulou.com)06/01 Report--

This article will explain in detail how to use Java to import data from Excel into MySQL. The editor thinks it is very practical, so I share it with you as a reference. I hope you can get something after reading this article.

I. Pom file junit junit 4.12 org.springframework.boot spring-boot-starter-test test com.alibaba easyexcel 2.1.7 com.alibaba fastjson 1.2.62 org.springframework.boot spring-boot-starter org.springframework.boot spring-boot-starter-test test com.mchange c3p0 0.9.5.2 com .baomidou mybatis-plus-boot-starter 3.4.0 org.springframework.boot spring-boot-starter-jdbc mysql mysql-connector-java runtime org.projectlombok lombok true Org.springframework.boot spring-boot-starter-web org.projectlombok lombok true org.springframework.boot spring-boot-starter-test test II. Configuration file 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 III, mapper file @ Mapperpublic interface DdbBookNewWordMapper extends BaseMapper {} IV, service file @ Servicepublic class DemoDAO {@ Autowired DdbBookNewWordMapper mapper Public void save (List list) {/ / if it is mybatis, try not to call insert directly for many times, write a new method batchInsert in a mapper, and insert all data into DdbBookNewWord newWord = new DdbBookNewWord (); System.out.println ("insert data start = ="); 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 ("end =");} V, entity class (database correspondence) @ 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;} VI. The entity class corresponding to 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 7. Listener / / there is a very important point that DemoDataListener cannot be managed by spring. You need to new every time you read excel, and then you can use spring to input public class DataDemo001Listener extends AnalysisEventListener {/ / DemoDAO demoDAO = SpringContextHolder.getBean (DemoDAO.class); private static final Logger LOGGER = LoggerFactory.getLogger (DataDemo001Listener.class). / * every 5 entries are stored in the database, 3000 can be used in practice, and then clean up the list to facilitate memory recovery * / private static final int BATCH_COUNT = 100; List list = new ArrayList (); / * assume that this is a DAO, of course, this can also be a service if there is business logic. Of course, it's useless if you don't have to store this object. * / private DemoDAO demoDAO;// public DataDemo001Listener (DemoDAO demoDAO) {/ / this is demo, so just new one. If spring is used, use the following parameter constructor this.demoDAO = demoDAO;} / * if spring is used, use this constructor. Each time you create a Listener, you need to pass the classes managed by spring into * * @ param demoDAO * / / public DataDemo001Listener (DemoDAO demoDAO) {/ / this.demoDAO = demoDAO;//} / * this every data parsing will call * * @ 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) / / when BATCH_COUNT is reached, you need to store the database once to prevent tens of thousands of pieces of data from being stored in memory. It is easy to OOM if (list.size () > = BATCH_COUNT) {saveData (); / / Storage completes cleaning list list.clear () }} / * all data parsing will be called * * @ param context * / @ Override public void doAfterAllAnalysed (AnalysisContext context) {/ / the data is also saved here to ensure that the final remaining data is also stored in the database saveData (); LOGGER.info ("all data parsing is complete!") ;} / * add storage database * / private void saveData () {demoDAO.save (list);}} 8. Test String path = "D:\ java-demo\ kuang-poi\"; @ Test public void simpleRead () {String fileName = path+ "leisurely Reading growth Plan-word part .xls" / / here you need to specify which class to read, and then reading the first sheet file stream will automatically close EasyExcel.read (fileName, DataDemo001.class, new DataDemo001Listener (demoDAO)) .sheet () .doRead () 9. Startup class @ 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) }} this is the end of the article on "how to use Java to import data from Excel into MySQL". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, please share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.