读取Excel文件写入数据库(JDBC版)

场景:有一个Excel表格,要求把里面的数据导入MySQL,又不想太麻烦的搞定(当然还有其他的办法哦,比如一些数据库可视化工具直接导入等等,这里主要是重温一下JDBC的操作)!

1.将需要导入的Excel文件转化为csv保存

2.直接上代码咯

mysql-connector-java-8.0.25.jar

配置JDBC

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class JDBCUtils {
    
    private static final String connectionURL = "jdbc:mysql://localhost:3308/saas_logistics?useUnicode=true&characterEncoding=UTF8&useSSL=false";
    private static final String username = "root";
    private static final String password = "root";
    
    //创建数据库的连接
    public static Connection getConnection() {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            return   DriverManager.getConnection(connectionURL,username,password);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }
    
    //关闭数据库的连接
    public static void close(ResultSet rs,Statement stmt,Connection con) throws SQLException {
        if(rs!=null)
            rs.close();
        if(stmt!=null)
            stmt.close();
        if(con!=null)
            con.close();
    }
}

插入数据

import java.io.BufferedReader;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class LogisticsCode {

	public static void main(String[] args) throws Exception {
		copyFile("F:/Downloads/快递鸟接口支持快递公司编码.csv");
	}

	private static void copyFile(String fileDir) throws Exception {
		try {
			StringBuffer sb = new StringBuffer();
			BufferedReader br = null;
			br = new BufferedReader(new FileReader(fileDir));
			int i = 1;
			while (br.ready()) {
				String brs = br.readLine();
				insert(i+"", brs.split(",")[0], brs.split(",")[1]);
				i++;
				Thread.sleep(100);//防止插入失败
			}
			br.close();
			System.out.println(sb.toString());
		} catch (FileNotFoundException e) {
			System.out.println("文件不存在!:" + e);
			throw new RuntimeException("文件不存在!", e);
		} catch (IOException e) {
			System.out.println("文件读取错误!:" + e);
			throw new RuntimeException("文件读取错误!", e);
		}
	}

	public static void insert(String id, String company, String code) throws SQLException {
		// 注册驱动 使用驱动连接数据库
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			con = JDBCUtils.getConnection();
			String sql = "INSERT INTO `logistics_code` (id, company, `code`)VALUES(?,?,?)";
			stmt = con.prepareStatement(sql);
			stmt.setString(1, id);
			stmt.setString(2, company);
			stmt.setString(3, code);
			int result = stmt.executeUpdate();// 返回值代表收到影响的行数
			System.out.println("插入成功" + company);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JDBCUtils.close(rs, stmt, con);
		}
	}
}