这里使用BufferedReader将csv中数据读取到list中:
/**
* 把数据从csv中读取到list
* @return
*/ public static List<KGInfo> readCSV(String fileName) {
List<KGInfo>kgInfoList=new ArrayList<>();
try {
BufferedReader reader = new BufferedReader(new FileReader("E:\\ftpdata\\"+fileName));// 文件名
reader.readLine();//第一行信息,为标题信息,不用,如果需要,注释掉
String line = null;
while ((line = reader.readLine()) != null) {
KGInfo kgInfo=new KGInfo();
String item[] = line.split(",");// CSV格式文件为逗号分隔符文件,这里根据逗号切分
kgInfo.setMerchantOrderID(item[0]);
kgInfo.setAuthCode(item[1]);
kgInfo.setAmountRequested(item[2]);
kgInfoList.add(kgInfo);
}
// System.out.println("从CSV中读取到的数据:" + kgInfoList);
} catch (Exception e) {
e.printStackTrace();
}
return kgInfoList;
}
将list数据存储到mysql数据库:
/**
* 批量插入到数据库
* @param list
*/ public static void insertTel_test(List<KGInfo> list) throws SQLException {
Connection conn = null;
// 驱动程序名
String driver = "com.mysql.jdbc.Driver";
// URL指向要访问的数据库名mydata
String url = "jdbc:mysql://***:3306/test?characterEncoding=utf8&allowMultiQueries=true&connectTimeout=5000&autoReconnect=true&zeroDateTimeBehavior=convertToNull";
// MySQL配置时的用户名
String user = "test";
// MySQL配置时的密码
String password = "*****";
// 遍历查询结果集
// 加载驱动程序
try {
Class.forName(driver);
// 连接MySQL数据库!!
conn = DriverManager.getConnection(url, user, password);
conn.setAutoCommit(false);
PreparedStatement prep = conn.prepareStatement("INSERT INTO `test`.`kadata1` VALUES (?,?,?)"); //需要替换
int num=0;
for (KGInfo value : list) {
num++;
prep.setString(1, value.getMerchantOrderID());
prep.setString(2,value.getAuthCode());
prep.setString(3,value.getAmountRequested());
prep.addBatch();
if(num>50000){
System.out.println(prep);
prep.executeBatch();
conn.commit();
num=0;
}
System.out.println(prep);
prep.executeBatch();
conn.commit();
}
} catch (Exception e) {
e.printStackTrace();
}finally {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}