数据源
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
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
