java读取Excel文件，用JDBC导入数据到mysql
西门飞翮
本需求最重要的就是如何将Excel文件中的数据读取到java集合中。首先要知道Excel表格中的每行记录即为一个对象，我们可以使用操作Excel的org.apache.poi框架，对数据进行读取。首先需要导入poi的jar包，这里只需要导入三个jar包，分别是poi.jar、poi-ooxml.jar和commons-math3.jar。这三个jar包可以从网上下载，也可以从我分享的百度云连接下载：用到的Jar包 提取码：b9ph 。下面封装了一个读取Excel工具类用于获取数据。
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Method;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
/**
* 读取Excel文件，将数据添加到List集合中，然后用JDBC技术把数据导入数据库
*/
public class ExcelPoiReaderUtils {
/**
* 将Excel中的数据读取到集合中，集合中每条数据即是一个对象
*
* @param filePath Excel文件的路径
* @param columnNames 表的字段名数组，这里的顺序要和Excel的每列数据要对应
* @param location 从Excel的某个位置开始读
* @param clazz bean类模板
* @return 有表数据的List集合
*/
public static <X> List<X> readExcel(String filePath, int[] location, String[] columnNames, Class<X> clazz) {
Sheet sheet = null; //页签对象
Row row = null; //Excel中的一行记录
Row rowHeader = null; //表头,这里的名字可能和数据库中的字段名不相等，因此使用columnNames中的名字
String cellData = null; //每个单元格中的数据
Workbook wb = null; //工作薄
int sheetIndex = location[0]; //Excel标签页的索引
int readStartRowIndex = location[1]; //从第几行开始读
List<X> result = new ArrayList<>();
if (filePath == null) {
return null;
}
String extString = filePath.substring(filePath.lastIndexOf(".")); //文件扩展名
InputStream is = null;
try {
is = new FileInputStream(filePath);
// 判断Excel文件的两种格式
if (".xls".equals(extString)) {
wb = new HSSFWorkbook(is);
} else if (".xlsx".equals(extString)) {
wb = new XSSFWorkbook(is);
} else {
wb = null;
}
if (wb != null) {
// 获取第一个sheet
sheet = wb.getSheetAt(sheetIndex - 1);
// 获取最大行数
int rownum = sheet.getPhysicalNumberOfRows();
// 获取最大列数
int colnum = sheet.getRow(1).getPhysicalNumberOfCells();
//先循环行，再循环列
for (int i = readStartRowIndex - 1; i < rownum; i++) {
//获取一行的数据
row = sheet.getRow(i);
//为每一行创建一个对象
X t = clazz.newInstance(); //通过类模板反射出对象
//有数据，循环列
if (row != null) {
for (int j = 0; j < colnum; j++) {
//获取每个单元格的数据
cellData = (String) getCellFormatValue(row.getCell(j));
//获取set方法
Method method = clazz.getMethod("set" + toFirstUpperCase(columnNames[j]), String.class);
//调用Studenter中的set方法,将单元格中的内容添加到对象中
method.invoke(t, cellData);
}
result.add(t);
} else {
break;
}
}
}
} catch (Exception e) {
e.printStackTrace();
} finally {
if(is != null){
try {
is.close();
} catch (IOException e) {
e.printStackTrace();
}
}
if(wb != null){
try {
wb.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
return result;
}
private static String toFirstUpperCase(String s){
return s.substring(0, 1).toUpperCase() + s.substring(1);
}
/**
* 获取单个单元格数据
*
* @param cell
* @return String类型的单元格数据
*/
private static Object getCellFormatValue(Cell cell) {
Object cellValue = null;
if (cell != null) {
// 判断cell的日期类型
String cellType = cell.getCellStyle().getDataFormatString();
if ("yyyy/mm;@".equals(cellType) || "m/d/yy".equals(cellType)
|| "yy/m/d".equals(cellType) || "mm/dd/yy".equals(cellType)
|| "dd-mmm-yy".equals(cellType) || "yyyy/m/d".equals(cellType)) {
return new SimpleDateFormat("yyyy/MM/dd").format(cell.getDateCellValue());
}
// 判断cell类型
switch (cell.getCellType()) {
case NUMERIC: {
cellValue = String.valueOf(cell.getNumericCellValue());
break;
}
case STRING: {
cellValue = cell.getRichStringCellValue().getString();
break;
}
default:
cellValue = "";
}
} else {
cellValue = "";
}
return cellValue;
}
}将文件的数据获取到集合中后，就可以将数据插入到数据库了。
下面是一个使用JDBC技术对mysql数据库进行操作，包括数据库的链接、数据的插入和查询的测试入口。
Main方法
import bean.Course;
import bean.SC;
import bean.Student;
import utils.JdbcUtils;
import java.sql.*;
import java.util.List;
public class Main {
private static Connection conn = null;
public static void main(String[] args) {
long starttime = System.currentTimeMillis();
//列出每个课程最高分的获得者。输出列：课程号，课程名，学号，姓名，成绩
String querySql = "SELECT c.Cname, c.Cno,s.Sname,s.Sno,x.maxGrade AS grade " +
"FROM sc LEFT JOIN student s ON sc.Sno = s.Sno LEFT JOIN course c ON sc.Cno = c.Cno " +
"LEFT JOIN (SELECT Cno,MAX(Grade) maxGrade FROM sc GROUP BY Cno) X " +
"ON sc.Cno = x.Cno " +
"WHERE x.maxGrade = sc.Grade;";
String filepath = "D:/BigDataStudy/test.xls";
int[] location = {1, 2};
String[] studentColumns = {"Sno", "Sname", "Ssex", "Sage", "Sdept"};
List<Student> students = ExcelPoiReaderUtils.readExcel(filepath, location, studentColumns, Student.class);
location = new int[]{2, 2};
String[] courseCols = {"Cno", "Cname", "Ccredit"};
List<Course> courses = ExcelPoiReaderUtils.readExcel(filepath, location, courseCols, Course.class);
location = new int[]{3, 2};
String[] scColumns = {"Sno", "Cno", "Grade"};
List<SC> sc = ExcelPoiReaderUtils.readExcel(filepath, location, scColumns, SC.class);
try {
conn = JdbcUtils.getConn();
// insertData(students);
// insertData(sc);
// insertData(courses);
queryData(querySql);
} catch (Exception e) {
e.printStackTrace();
} finally {
JdbcUtils.close();
}
long endtime = System.currentTimeMillis();
System.out.println("程序花费时间：" + (endtime - starttime) + "毫秒！！");
}
private static void queryData(String sql) {
PreparedStatement ps = null;
ResultSet rs = null;
try {
ps = conn.prepareStatement(sql);
rs = ps.executeQuery();
//获取结果数据集中的列数
ResultSetMetaData rsmd = rs.getMetaData();
int columnCount = rsmd.getColumnCount();
while (rs.next()) {
for (int i = 1; i <= columnCount; i++)
System.out.print(rs.getString(i) + '\t');
System.out.println();
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
JdbcUtils.close(ps, rs);
}
}
private static void insertData(List data) throws Exception {
String sql = "";
if (data == null) {
throw new Exception("数据为空！");
}
PreparedStatement ps = null;
ResultSet rs = null;
try {
if (data.get(0) instanceof Student) {
sql = "insert into Student(Sno,Sname,Sage,Ssex,Sdept) values(?,?,?,?,?)";
ps = conn.prepareStatement(sql); //预编译执行体
for (Student stu : (List<Student>) data) {
ps.setString(1, stu.getSno());
ps.setString(2, stu.getSname());
ps.setString(3, stu.getSage());
ps.setString(4, stu.getSsex());
ps.setString(5, stu.getSdept());
ps.addBatch();
}
} else if (data.get(0) instanceof SC) {
sql = "insert into sc(Sno,Cno,Grade) values(?,?,?)";
ps = conn.prepareStatement(sql);
for (SC s : (List<SC>) data) {
ps.setString(1, s.getSno());
ps.setString(2, s.getCno());
ps.setString(3, s.getGrade());
ps.addBatch();
}
ps.executeBatch();
} else if (data.get(0) instanceof Course) {
sql = "insert into course(Cno,Cname,Ccredit) values(?,?,?)";
ps = conn.prepareStatement(sql);
for (Course s : (List<Course>) data) {
ps.setString(1, s.getCno());
ps.setString(2, s.getCname());
ps.setString(3, s.getCcredit());
ps.addBatch();
}
}
ps.executeBatch();
} catch (Exception e) {
e.printStackTrace();
} finally {
JdbcUtils.close(ps, rs);
}
}
}
数据库连接工具类
package utils;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;
public class ConnectionUtils {
private static String driver = null;
private static String url = null;
private static String username = null;
private static String password = null;
private static Properties props = new Properties();
//ThreadLocal:保证一个线程只能有一个连接
private static ThreadLocal<Connection> tl = new ThreadLocal<Connection>();
/**
* 静态的代码块中读取db.properties
*/
static {
//类加载器读取文件。 能找到类的地方都能找到
InputStream in =
ConnectionUtils.class.getClassLoader().getResourceAsStream("db.properties");
try {
props.load(in);
driver = props.getProperty("jdbc.driver");
url = props.getProperty("jdbc.url");
username = props.getProperty("jdbc.username");
password = props.getProperty("jdbc.password");
Class.forName(driver);
} catch (Exception e) {
e.printStackTrace();
}
}
//获取连接的方法
public static Connection getConn() throws Exception {
//单例模式
Connection conn = tl.get();
if (conn == null) {
conn = DriverManager.getConnection(url, username, password);
tl.set(conn);
}
return conn;
}
//关闭连接
public static void closeConn() throws SQLException {
Connection conn = tl.get();
if (conn != null) {
conn.close();
}
tl.set(null);
}
}package utils;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import javax.sql.DataSource;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;
/**
* @author 宇文智
* @create 2020-07-27 16:54
*/
public class JdbcUtils {
private static DataSource dataSource = null;
/**
*通过工厂来自动读取配置文件中的配置信息，并创建连接池对象,返回连接对象
* @return 返回连接对象
* @throws SQLException
*/
public static Connection getConn() throws SQLException {
if(dataSource == null){
try {
Properties properties = new Properties();
InputStream resourceAsStream =JdbcUtils.class.getClassLoader().getResourceAsStream("druid.properties");
properties.load(resourceAsStream);
resourceAsStream.close();
//通过工厂来自动读取配置文件中的配置信息，并创建连接池对象
dataSource = DruidDataSourceFactory.createDataSource(properties);
} catch (Exception e) {
throw new SQLException(e);
}
}
return dataSource.getConnection();
}
public static Connection getConnOld() throws Exception {
return ConnectionUtils.getConn();
}
/**
* 关闭预编译执行体和结果集
* @param ps 预编译执行体/**
* @param rs 结果集
*/
public static void close(PreparedStatement ps,ResultSet rs) {
if(ps != null){
try {
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(rs!= null){
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
/**
* 关闭数据库连接
*/
public static void close() {
try {
ConnectionUtils.closeConn();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
package utils;
import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.ArrayList;
import java.util.List;
/**
* @author 宇文智
* @create 2020-07-28 9:02
*/
public class CommonUtils {
/**
* 通用的更新操作，可以执行DDL,可以执行除了select之外的DML
*
* @param sql 待执行的sql语句
* @param args 用于替换sql中 ? 的参数列表
* @return
*/
public static int update(Connection connection,String sql, Object... args) throws Exception {
PreparedStatement ps = null;
try {
connection = JdbcUtils.getConn();
ps = connection.prepareStatement(sql);
for (int i = 0; i < args.length; i++) {
ps.setObject(i + 1, args[i]);
}
return ps.executeUpdate();
} finally {//释放资源，资源的关闭与否，取决于是否是方法本身创建的
//connection是使用参数传过来的，不需要释放
JdbcUtils.close(ps, null);
}
}
/**
* 查询数据
* @param connection 数据库连接对象
* @param sql 待执行的查询sql
* @param clazz 类模板，告诉此查询要返回的类型
* @param args 替换sql中？的参数列表
* @param <X> 泛型
* @return 返回X类型的集合对象
* @throws Exception
*/
public static <X> List<X> Query(Connection connection, String sql, Class<X> clazz, Object... args) throws Exception {
PreparedStatement ps = null;
ResultSet rs = null;
List<X> result = new ArrayList<>();
try {
ps = connection.prepareStatement(sql);
//替换sql中的？
for (int i = 0; i < args.length; i++) {
ps.setObject(i + 1, args[i]);
}
rs = ps.executeQuery();
//打印表头
ResultSetMetaData metaData = ps.getMetaData();
int columnCount = metaData.getColumnCount();
while (rs.next()) {
X o = clazz.newInstance();
for (int i = 0; i < columnCount; i++) {
String columnLabel = metaData.getColumnLabel(i+1);
Object value = rs.getObject(columnLabel);
//获取属性定义对象
Field declaredField = clazz.getDeclaredField(columnLabel);
declaredField.setAccessible(true);//暴力反射
declaredField.set(o,value); //为每个对象中的属性设值
}
result.add(o);
}
} finally {
JdbcUtils.close(ps, rs);
}
return result;
}
}
数据库连接的配置文件
jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/studentinfo
jdbc.username=root
jdbc.password=root操作数据库的Dao层
package dao;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import utils.JdbcUtils;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;
/**
* @author 宇文智
* @create 2020-07-28 17:03
*/
public class JdbcDao<W> {
private QueryRunner queryRunner = new QueryRunner();
private Class clazz;
public JdbcDao(Class clazz){
this.clazz = clazz;
}
public List<W> getList(String sql, Object... args) throws SQLException {
Connection connection = null;
try {
connection = JdbcUtils.getConn();
BeanListHandler<W> beanListHandler = new BeanListHandler<>(clazz);
List<W> beanList = queryRunner.query(connection, sql, beanListHandler, args);
return beanList;
} finally {
JdbcUtils.close();
}
}
public W getBean(String sql, Object... args) throws SQLException {
Connection connection = null;
try {
connection = JdbcUtils.getConn();
BeanHandler<W> beanHandler = new BeanHandler<>(clazz);
W bean = queryRunner.query(connection, sql, beanHandler, args);
return bean;
} finally {
JdbcUtils.close();
}
}
public Object getValue(String sql,Object... args) throws SQLException {
Connection connection = null;
try {
connection = JdbcUtils.getConn();
ScalarHandler scalarHandler = new ScalarHandler();
return queryRunner.query(connection, sql,scalarHandler, args);
} finally {
JdbcUtils.close();
}
}
public int update(String sql,Object... args) throws SQLException {
Connection connection = null;
try {
connection = JdbcUtils.getConn();
return queryRunner.update(connection, sql, args);
} finally {
JdbcUtils.close();
}
}
}
JavaBean
public class Students {
private String Sno;
private String Sname;
private String Ssex;
private int Sage;
private String Sdept;
public String getSno() {
return Sno;
}
public void setSno(String sno) {
Sno = sno;
}
public String getSname() {
return Sname;
}
public void setSname(String sname) {
Sname = sname;
}
public String getSsex() {
return Ssex;
}
public void setSsex(String ssex) {
Ssex = ssex;
}
public int getSage() {
return Sage;
}
public void setSage(int sage) {
Sage = sage;
}
public String getSdept() {
return Sdept;
}
public void setSdept(String sdept) {
Sdept = sdept;
}
}
public class SC {
//学生选课表SC由学号（Sno）、课程号（Cno）、成绩（Grade）三个属性组成
private String Sno;
private String Cno;
private String Grade;
public String getSno() {
return Sno;
}
public void setSno(String sno) {
Sno = sno;
}
public String getCno() {
return Cno;
}
public void setCno(String cno) {
Cno = cno;
}
public String getGrade() {
return Grade;
}
public void setGrade(String grade) {
Grade = grade;
}
}
public class Course {
private String Cno;
private String Cname;
private String Ccredit;
public String getCno() {
return Cno;
}
public void setCno(String cno) {
Cno = cno;
}
public String getCname() {
return Cname;
}
public void setCname(String cname) {
Cname = cname;
}
public String getCcredit() {
return Ccredit;
}
public void setCcredit(String ccredit) {
Ccredit = ccredit;
}
}studentInfo.sql
# Host: localhost (Version: 5.6.11)
# Date: 2020-07-27 20:34:42
# Generator: MySQL-Front 5.3 (Build 4.234)
/*!40101 SET NAMES utf8 */;
#
# Structure for table "course"
#
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
`Cno` varchar(100) NOT NULL,
`Cname` varchar(255) DEFAULT NULL,
`Ccredit` int(3) DEFAULT NULL,
PRIMARY KEY (`Cno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
#
# Data for table "course"
#
INSERT INTO `course` VALUES ('C001','语文',2),('C002','英语',3),('C003','数学',4),('C004','物理',5);
#
# Structure for table "sc"
#
DROP TABLE IF EXISTS `sc`;
CREATE TABLE `sc` (
`Sno` varchar(20) NOT NULL DEFAULT '',
`Cno` varchar(10) NOT NULL DEFAULT '',
`Grade` int(2) DEFAULT NULL,
PRIMARY KEY (`Sno`,`Cno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
#
# Data for table "sc"
#
INSERT INTO `sc` VALUES ('S0001','C001',100),('S0002','C001',77),('S0002','C002',52),('S0003','C001',11),('S0003','C002',61),('S0003','C003',91),('S0004','C001',22),('S0004','C002',70),('S0004','C003',42),('S0004','C004',57),('S0005','C001',100),('S0005','C002',98),('S0005','C003',88),('S0005','C004',69),('S0006','C001',69),('S0006','C002',81),('S0006','C003',78),('S0006','C004',40),('S0007','C001',31),('S0007','C002',86),('S0007','C003',72),('S0007','C004',12),('S0008','C001',5),('S0008','C002',7),('S0008','C003',24),('S0008','C004',52),('S0009','C003',99),('S0009','C004',36),('S0010','C004',86);
#
# Structure for table "student"
#
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
`Sno` varchar(255) NOT NULL,
`Sname` varchar(255) DEFAULT NULL,
`Ssex` char(1) DEFAULT NULL,
`Sage` int(3) DEFAULT NULL,
`Sdept` varchar(255) DEFAULT NULL,
PRIMARY KEY (`Sno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
#
# Data for table "student"
#
INSERT INTO `student` VALUES ('S0001','赵大','男',20,'文学系'),('S0002','赵二','男',20,'物理系'),('S0003','赵三','男',20,'数学系'),('S0004','赵四','男',20,'文学系'),('S0005','赵五','男',20,'物理系'),('S0006','赵六','女',20,'数学系'),('S0007','赵七','女',20,'文学系'),('S0008','赵八','女',20,'物理系'),('S0009','赵九','女',20,'数学系'),('S0010','赵十','女',20,'文学系');
#
# Structure for table "user"
#
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`password` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
#
# Data for table "user"
#
INSERT INTO `user` VALUES (1,'yuwenzhi',19,'9999'),(2,'yuwenzhi',19,'9999');
类似资料：