經(jīng)常需要用到excel導(dǎo)入數(shù)據(jù)庫(kù)的同學(xué)儿礼，其實(shí)可以不用每次費(fèi)事編寫(xiě)POI-java代碼砾淌，現(xiàn)在maven可以方便的半自動(dòng)導(dǎo)入瘦麸，只需要配置好相關(guān)設(shè)置，方便了許多
示例使用mysql DB
excel內(nèi)容格式如下
Paste_Image.png
按照excel格式建立對(duì)應(yīng)的表結(jié)構(gòu)
SET SESSION FOREIGN_KEY_CHECKS=0;
/* Drop Tables */
DROP TABLE learn_maven_list;
/* Create Tables */
CREATE TABLE `learn_maven_list` (
`id` VARCHAR(64) NOT NULL COLLATE 'utf8_bin',
`license_no` VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8_bin',
`appler` VARCHAR(255) NOT NULL COLLATE 'utf8_bin',
`farm` VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8_bin',
`address` VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8_bin',
`contect` VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8_bin',
`product` VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8_bin',
`large` VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8_bin',
`date` DATE NULL DEFAULT NULL,
`pirorid` VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8_bin',
PRIMARY KEY (`id`),
INDEX `appler` (`appler`, `id`)
)
COLLATE='utf8_bin'
ENGINE=InnoDB;
注意表名應(yīng)該和excel的sheet名一致避咆，如果有多個(gè)sheet舟肉，依次建立表結(jié)構(gòu)即可
Paste_Image.png
POM文件如下
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.mycompany.app</groupId>
<artifactId>my-app</artifactId>
<version>1.0-SNAPSHOT</version>
<packaging>jar</packaging>
<name>my-app</name>
<url>http://maven.apache.org</url>
<properties>
<mysql.driver.version>5.1.13</mysql.driver.version>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
</properties>
<dependencies>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>${mysql.driver.version}</version>
<scope>runtime</scope>
</dependency>
</dependencies>
<profiles>
<!-- 初始化數(shù)據(jù)庫(kù) -->
<profile>
<id>init-db</id>
<dependencies>
<dependency>
<groupId>org.dbunit</groupId>
<artifactId>dbunit</artifactId>
<version>2.4.8</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.2-FINAL</version>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-antrun-plugin</artifactId>
<version>1.7</version>
<configuration>
<target>
<!-- mysql -->
<property name="dbunit.datatype" value="org.dbunit.ext.mysql.MySqlDataTypeFactory" />
<property file="wee.properties" />
<sql driver="${jdbc.driver}" url="${jdbc.url}" userid="${jdbc.username}" password="${jdbc.password}"
onerror="continue" encoding="${project.build.sourceEncoding}">
<classpath refid="maven.test.classpath" />
<transaction src="db/cms/wee_data.sql"/>
</sql>
<taskdef name="dbunit" classname="org.dbunit.ant.DbUnitTask" classpathref="maven.test.classpath" />
<!-- mysql、mssql -->
<dbunit driver="${jdbc.driver}" url="${jdbc.url}" userid="${jdbc.username}" password="${jdbc.password}">
<dbconfig>
<property name="datatypeFactory" value="${dbunit.datatype}" />
</dbconfig>
<classpath refid="maven.test.classpath" />
<operation type="INSERT" src="db/cms/wee_data.xls" format="xls" transaction="true"/>
</dbunit>
</target>
</configuration>
</plugin>
</plugins>
</build>
</profile>
</profiles>
</project>
文件中的wee.properties中需要定義好datasouce
#============================#
#===== Database sttings =====#
#============================#
#mysql database setting
jdbc.type=mysql
jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://127.0.0.1:3306/learn_maven?useUnicode=true&characterEncoding=utf-8
jdbc.username=root
jdbc.password=
最后執(zhí)行批處理文件
@echo off
echo [INFO] Re-create the schema and provision the sample data.
pause
cd %~dp0
cd ..
set path=%MAVEN_HOME%/bin;%windir%/system32;%path%
call mvn antrun:run -Pinit-db
cd db
pause
導(dǎo)入成功
Paste_Image.png
