java读取Excel文件，用JDBC导入数据到mysql

宇文智

已于 2022-10-14 08:35:06 修改

阅读量1.3k

点赞数 3

分类专栏：数据库文章标签： Excel poi mysql jdbc 数据库

于 2019-07-22 23:51:28 首次发布

本文链接：https://blog.csdn.net/m0_38109926/article/details/96916897

版权

数据库专栏收录该内容

4 篇文章 0 订阅

订阅专栏

本需求最重要的就是如何将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');

宇文智

关注

3
点赞
踩
20

收藏

觉得还不错? 一键收藏
1
评论
java读取Excel文件，用JDBC导入数据到mysql

本需求最重要的就是如何将Excel文件中的数据读取到java集合中。首先要知道Excel表格中的每行记录即为一个对象，我们可以使用操作Excel的org.apache.poi框架，对数据进行读取。首先需要导入poi的jar包，这里只需要导入三个jar包，分别是poi.jar、poi-ooxml.jar和commons-math3.jar。这三个jar包可以从网上下载，也可以从我分享的百度云连接下载：需要的jar。下面封装了一个读取Excel工具类用于获取数据。import bean.Course;.
复制链接

扫一扫

专栏目录