java语言实现csv入库mysql

main方法实现

用到了java汉子转拼音，大部分csv入库表头字段都是中文，若是用在mysql字段不切实际，所以需要先把文化转化英文
因为csv格式头部空了行数，写代码要注意整体修改规范

mysql建表，表字段显示

整体实现步骤看代码

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @title JDBC工具类
 *      连接数据库
 *      执行SQL
 *      查询对象
 *      查询集合
 * @author Xingbz
 */
public class JdbcUtil {
    /** 驱动名称 */
    private static final String DRIVER_NAME = "com.mysql.jdbc.Driver";
    /** 数据库链接地址 */
    private static final String url = "jdbc:mysql://localhost:3306/rpadb?serverTimezone=GMT%2B8";
    /** 用户名 */
    private static final String userName = "root";
    /** 密码 */
    private static final String password = "root";

    /** 定义连接 */
    private static Connection conn;
    /** 定义STMT */
    private static PreparedStatement stmt;
    /** 定义结果集 */
    private static ResultSet rs;

    /** 初始化加载链接 */
    static {
//        Properties prop = new Properties();//从配置文件中读取信息
        try {
//            prop.load(DBUtil.class.getClassLoader().getResourceAsStream("jdbc.properties"));
//            String driver = prop.getProperty("driver");
//            String url = prop.getProperty("url");
//            String userName = prop.getProperty("userName");
//            String password = prop.getProperty("password");
            Class.forName(DRIVER_NAME);
            conn = DriverManager.getConnection(url, userName, password);
        } catch (ClassNotFoundException e) {
            System.err.println("驱动加载失败");
            e.printStackTrace();
        } catch (SQLException e) {
            System.err.println("数据库链接异常");
            e.printStackTrace();
        }
    }

    /** 获取链接 */
    public static Connection getConn() {
        return conn;
    }

    /** 关闭链接,释放资源 */
    public static void close() {
        try {
            if (rs != null) {
                rs.close();
                rs = null;
            }
            if (stmt != null) {
                stmt.close();
                stmt = null;
            }

            if (conn != null) {
                conn.close();
                conn = null;
            }
        } catch (SQLException e) {
            System.err.println("资源释放发生异常");
        }
    }

    /**
     * 获取指定数据库下所有的表名
     * @param dbNm
     * @return
     */
    public static List<String> getAllTableName(String dbNm) {
        List<String> result = new ArrayList<String>();
        Statement st = null;
        try {
            st = conn.createStatement();
            ResultSet rs = st.executeQuery("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES  WHERE TABLE_SCHEMA='" + dbNm + "'");
            while (rs.next()) {
                result.add(rs.getString(1));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (st != null) {
                try {
                    st.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            close();
        }
        return result;
    }
    /** 执行SQL返回ResultSet
     * @return*/
    public static boolean exitTable(String tableName) {
        try{
            ResultSet resultSet = executeSql("show tables like '" + tableName + "'");
            if(resultSet.next()){
                Object object = resultSet.getObject(1);
                System.out.println(object);
                return true;
            }
            System.err.println("不存在");
        }catch (Exception e){
            e.printStackTrace();
        }
        return false;
    }

    /** 执行SQL返回ResultSet */
    public static void execute(String sql, Object... args) {
        try {
//            System.out.println("准备执行SQL : \n" + sql);
            stmt = conn.prepareStatement(sql);
            if (null != args && args.length != 0) {
                for (int i = 0; i < args.length; i++) {
                    stmt.setObject(i + 1, args[i]);
                }
            }
            stmt.execute();
        } catch (SQLException e) {
            System.err.println("数据查询异常");
            e.printStackTrace();
        }
    }

    /** 执行SQL返回ResultSet */
    public static void execute2(String sql, List<String> list) {
        try {
      System.out.println("准备执行SQL : \n" + sql);
            stmt = conn.prepareStatement(sql);
            if (null != list && list.size() != 0) {
                for (int i = 0; i < list.size(); i++) {
                    stmt.setObject(i + 1, list.get(i));
                }
            }
            stmt.execute();
        } catch (SQLException e) {
            System.err.println("数据查询异常");
            e.printStackTrace();
        }
    }
    /** 执行SQL返回ResultSet */
    public static ResultSet executeSql(String sql, Object... args) {
        try {
//            System.out.println("准备执行SQL : \n" + sql);
            stmt = conn.prepareStatement(sql);
            if (null != args && args.length != 0) {
                for (int i = 0; i < args.length; i++) {
                    stmt.setObject(i + 1, args[i]);
                }
            }

            rs = stmt.executeQuery();
        } catch (SQLException e) {
            System.err.println("数据查询异常");
            e.printStackTrace();
        }
        return rs;
    }

    /**
     * @title 查询数据结果 , 并封装为对象
     * @author Xingbz
     */
    private static <T> T excuteQuery(Class<T> klass, String sql, Object... args) {
        try {
            rs = executeSql(sql, args);
            ResultSetMetaData metaData = rs.getMetaData();

            Map<String, Object> resultMap = new HashMap<>();
            if (rs.next()) {
                for (int i = 1; i <= metaData.getColumnCount(); i++) {
                    String columnname = metaData.getColumnLabel(i);
                    Object obj = rs.getObject(i);
                    resultMap.put(columnname, obj);
                }
            }

            return JSON.parseObject(JSON.toJSONString(resultMap), klass);
        } catch (Exception e) {
            System.err.println("数据查询异常");
            e.printStackTrace();
        } finally {
            close();
        }
        return JSON.toJavaObject(new JSONObject(), klass);
    }

    /**
     * @title 查询数据结果 , 并封装为List
     * @author Xingbz
     */
    private static <T> List<T> excuteQueryToList(Class<T> klass, String sql, Object... args) {
        try {
            rs = executeSql(sql, args);
            List<Map<String, String>> resultList = new ArrayList<>();
            Map<String, String> resultMap = new HashMap<>();
            while (rs.next()) {
                ResultSetMetaData metaData = rs.getMetaData();
                int columnCount = metaData.getColumnCount();
                for (int i = 1; i <= columnCount; i++) {
                    resultMap.put(metaData.getColumnName(i), rs.getString(i));
                }
                resultList.add(resultMap);
            }

            return JSON.parseArray(JSON.toJSONString(resultList), klass);
        } catch (Exception e) {
            System.err.println("数据查询异常");
            e.printStackTrace();
        } finally {
            close();
        }
        return JSON.parseArray("[]", klass);
    }
}

import net.sourceforge.pinyin4j.PinyinHelper;
import net.sourceforge.pinyin4j.format.HanyuPinyinCaseType;
import net.sourceforge.pinyin4j.format.HanyuPinyinOutputFormat;
import net.sourceforge.pinyin4j.format.HanyuPinyinToneType;
import net.sourceforge.pinyin4j.format.HanyuPinyinVCharType;
import net.sourceforge.pinyin4j.format.exception.BadHanyuPinyinOutputFormatCombination;

/**
 * 拼音工具类
 *
 * @author lsf
 */
public class PingYinUtil {
    /**
     * 将字符串中的中文转化为拼音,其他字符不变
     *
     * @param inputString
     * @return
     */
    public static String getPingYin(String inputString) {
        HanyuPinyinOutputFormat format = new HanyuPinyinOutputFormat();
        format.setCaseType(HanyuPinyinCaseType.LOWERCASE);
        format.setToneType(HanyuPinyinToneType.WITHOUT_TONE);
        format.setVCharType(HanyuPinyinVCharType.WITH_V);

        char[] input = inputString.trim().toCharArray();
        String output = "";

        try {
            for (int i = 0; i < input.length; i++) {
                if (java.lang.Character.toString(input[i]).matches("[\\u4E00-\\u9FA5]+")) {
                    String[] temp = PinyinHelper.toHanyuPinyinStringArray(input[i], format);
                    output += temp[0];
                } else
                    output += java.lang.Character.toString(input[i]);
            }
        } catch (BadHanyuPinyinOutputFormatCombination e) {
            e.printStackTrace();
        }
        return output;
    }
    /**
     * 获取汉字串拼音首字母，英文字符不变
     * @param chinese 汉字串
     * @return 汉语拼音首字母
     */
    public static String getFirstSpell(String chinese) {
        StringBuffer pybf = new StringBuffer();
        char[] arr = chinese.toCharArray();
        HanyuPinyinOutputFormat defaultFormat = new HanyuPinyinOutputFormat();
        defaultFormat.setCaseType(HanyuPinyinCaseType.LOWERCASE);
        defaultFormat.setToneType(HanyuPinyinToneType.WITHOUT_TONE);
        for (int i = 0; i < arr.length; i++) {
            if (arr[i] > 128) {
                try {
                    String[] temp = PinyinHelper.toHanyuPinyinStringArray(arr[i], defaultFormat);
                    if (temp != null) {
                        pybf.append(temp[0].charAt(0));
                    }
                } catch (BadHanyuPinyinOutputFormatCombination e) {
                    e.printStackTrace();
                }
            } else {
                pybf.append(arr[i]);
            }
        }
        return pybf.toString().replaceAll("\\W", "").trim();
    }
    /**
     * 获取汉字串拼音，英文字符不变
     * @param chinese 汉字串
     * @return 汉语拼音
     */
    public static String getFullSpell(String chinese) {
        StringBuffer pybf = new StringBuffer();
        char[] arr = chinese.toCharArray();
        HanyuPinyinOutputFormat defaultFormat = new HanyuPinyinOutputFormat();
        defaultFormat.setCaseType(HanyuPinyinCaseType.LOWERCASE);
        defaultFormat.setToneType(HanyuPinyinToneType.WITHOUT_TONE);
        for (int i = 0; i < arr.length; i++) {
            if (arr[i] > 128) {
                try {
                    pybf.append(PinyinHelper.toHanyuPinyinStringArray(arr[i], defaultFormat)[0]);
                } catch (BadHanyuPinyinOutputFormatCombination e) {
                    e.printStackTrace();
                }
            } else {
                pybf.append(arr[i]);
            }
        }
        return pybf.toString();
    }
}

import cn.hutool.core.text.csv.CsvData;
import cn.hutool.core.text.csv.CsvReader;
import cn.hutool.core.text.csv.CsvRow;
import cn.hutool.core.text.csv.CsvUtil;

import java.io.File;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;


public class createTable {


    public static void main(String[] args) throws IOException, SQLException {
        String path="D:\\tmp\\zhibiao.csv";
        String tableName = "tabl42";
        System.out.println(JdbcUtil.exitTable(tableName));

//        createTableByCsvHearder(path,tableName);
    }

    public static void createTableByCsvHearder(String csvPath, String tableName){

        boolean b = JdbcUtil.exitTable(tableName);
        if(!b){
            CsvReader reader1 = CsvUtil.getReader();
            CsvData read = reader1.read(new File(csvPath));
            // 读第7行，下标从0开始。
            CsvRow row = read.getRow(4);
            System.out.println(row);
            List<String> rawList = row.getRawList();
            for (String s : rawList) {
                String pinyin = PingYinUtil.getFirstSpell(s);
                System.out.println(pinyin);
            }
            System.out.println(rawList);

            StringBuffer sb = new StringBuffer();
            sb.append("create table "+tableName+" (");
            for (int i = 0; i < rawList.size(); i++) {
                String item = rawList.get(i);
                String pinyin = PingYinUtil.getFirstSpell(item);
                sb.append(""+pinyin+" varchar(255)");
                // 最后一列不需要“,”
                if(i != rawList.size() -1){
                    sb.append(",");
                }
            }
            sb.append(")");

            String createSql = sb.toString();
            JdbcUtil.execute(createSql);

        }

    }


}

import cn.hutool.core.text.csv.CsvData;
import cn.hutool.core.text.csv.CsvReader;
import cn.hutool.core.text.csv.CsvRow;
import cn.hutool.core.text.csv.CsvUtil;
import cn.hutool.db.sql.SqlUtil;
import com.rosense.commons.SQLUtils;

import java.io.File;
import java.io.IOException;
import java.sql.SQLException;
import java.util.List;


public class InsertCsvToTable {


    public static void main(String[] args) throws IOException, SQLException {
        //String path="D:\\tmp\\zhibiao.csv";
        String path="C:\\Users\\小柠檬\\Desktop\\自定义编排ftp内容\\徐州南通指标监控_盛莉莉-20210830081500(子报表 1).csv";
        String tableName = "tabl423";

     InsertCsvToTable(path,tableName);
    }

    public static void InsertCsvToTable(String csvPath, String tableName){

        createTable.createTableByCsvHearder(csvPath,tableName);

        CsvReader reader1 = CsvUtil.getReader();
        CsvData read = reader1.read(new File(csvPath));
        String insertSql = SQLUtils.getInsertSql(tableName, JdbcUtil.getConn());
        List<CsvRow> rows = read.getRows();
        for (int i = 5; i < rows.size() - 1; i++) {
            CsvRow strings = rows.get(i);
            System.out.println(strings.getRawList());
            JdbcUtil.execute2(insertSql,strings.getRawList());
        }

    }
//
//    public void loadTianYuanCells(Connection conn_from, Connection conn_to, String tableName) {
//        Statement st = null;
//        ResultSet rs = null;
//        PreparedStatement ps = null;
//        try {
//            String sql = "select oid,tgt_oid from " + tableName;
//            st = conn_from.createStatement();
//            rs = st.executeQuery(sql);
//            ps = conn_to.prepareStatement(SQLUtils.getInsertSql("tbl_adjc_4g_new", conn_to));
//            int count = 0;
//            Set<String> set = new HashSet<String>();
//            int total = 1;
//            while (rs.next()) {
//                String cellid = rs.getString("oid");
//                String ncellid = rs.getString("tgt_oid");
//                String key = ncellid + cellid;
//                BaseInfo base1 = cellMap.get(cellid);
//                BaseInfo base2 = cellMap.get(ncellid);
//                if (total++ % 100000 == 0) {
//                    System.out.println(tableName + "=" + total);
//                }
//                if (base1 == null || base2 == null || base1.city == 0)
//                    continue;
//                if (set.contains(key)) {
//                    continue;
//                }
//
//                set.add(key);
//                int idx = 1;
//                ps.setInt(idx++, base1.city);
//                ps.setString(idx++, base1.cellid);
//                ps.setString(idx++, base2.cellid);
//                ps.addBatch();
//                if (count++ > 5000) {
//                    ps.executeBatch();
//                    count = 0;
//                }
//            }
//            ps.executeBatch();
//        } catch (Exception e) {
//            e.printStackTrace();
//        } finally {
//            SQLUtils.closeAll(rs, st, conn_from);
//            SQLUtils.closeAll(rs, ps, conn_to);
//        }
//    }

}