定时指定ftp目录下csv文件入库到mysql数据库

数据源

FTP——>单机程序业务说明
程序启动之后，通过方法每小时下载FTP 132.255.150.217上最新的一个csv表到本地，如果不是只获取最新的表，文件无需改代码，通过方法会获取ftp目录下所有的csv文件，在通过本地入库到mysql数据库中,因为是单机程序，做了定时处理，针对客户需求，ftp文件每隔一个小时会有一个新的csv文件，所以在定时方法可以选用一个小时触发一次程序，功能做了处理，第一个文件入库时会判断这个表是否存在mysql，如果存在无需创建，只会把文件的内容读进去，因为csv表头空了几行，底部多了一行共记录，所以代码加了遍历，如果不存在此表会获取表字段，通过java汉子转拼音工具类，把汉字变成字母拼写的方式当做mysql的表字段,效果如下

代码部分
createTable 建表的工具类,读取csv过滤空行和结尾/入库过滤小区表名称

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 com.rosense.commons.SQLUtils;

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="C:\\ash\\ftp\\徐州南通指标监控_盛莉莉-20210915101500(子报表 1).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);

        }

    }

    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());
        }

    }


}

JdbcUtil 连接工具类

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 = "root123!";

    /** 定义连接 */
    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);
    }
}

PingYinUtil 将字符串中的中文转化为拼音,其他字符不变，让数据库字段回显拼音

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();
    }
}

test 方法测试类

import cn.hutool.*;
import cn.hutool.core.io.FileUtil;
import cn.hutool.extra.ftp.Ftp;
import com.mcg.util.AshUtils;

import java.io.File;
import java.util.ArrayList;
import java.util.List;

public class test {
    public static void main(String[] args) throws Exception {

        //TODO ftp下载
        Ftp ftp = new Ftp("132.225.150.217", 21, "unicom217", "Qwer+1234");
        String path = "/指标监控_盛莉莉";
        List<String> list = ftp.ls(path);
        List<String> bendi = new ArrayList<>();//*本地目录*//*
        File ftpdir = new File("C:\\ash\\ftp");
        File[] ftpfiles = ftpdir.listFiles();
        for (File f : ftpfiles) {
            String sa = f.getName();
            bendi.add(sa);
        }
        System.out.println("开始下载");
        for (String s1 : list) {
            if (!bendi.contains(s1)) {
                File newf = new File("C:\\ash\\ftp\\" + s1);
                ftp.download(path, s1, newf);
             /*   File ff = new File("C:\\ash\\ftptemp\\"+s1);
                AshUtils.copyFileUsingFileStreams(newf,ff);*/
                String path1=newf.getPath();
                String tableName = "tablwubo";
                createTable.InsertCsvToTable(path1,tableName);
            }
            //TODO 针对对端目录的单个文件下载到本地目录，aaa.csv则表示创建输入流内容输入到此文件
//        File newf = new File("C:\\ash\\ftp\\aaa.csv");
//        ftp.download("/指标监控_盛莉莉","徐州南通指标监控_盛莉莉-20210913161500(子报表 1).csv",newf);

        }
        ftp.close();
    }
}

所用到的依赖以及jar包

       <!--hutool-->
		<dependency>
			<groupId>cn.hutool</groupId>
			<artifactId>hutool-all</artifactId>
			<version>5.5.6</version>
		</dependency>

        <dependency>
			<groupId>com.belerweb</groupId>
			<artifactId>pinyin4j</artifactId>
			<version>2.5.0</version>
		</dependency>

jar提取:sqlUtil的jar包

提取链接：https://pan.baidu.com/s/13TYIKpo1QjZFIFoq3Y3DYw

提取码：3636

hutool的jar包

链接：https://pan.baidu.com/s/1uGmjyZq7a1jPU88eCQP8TA

提取码：3636