java 逐行读取csv文件存入mysql数据库
DynamicParameter.java
//主键自增,生成uuid的工具类
package com.nantian.sms.util;
import java.text.SimpleDateFormat;
public class DynamicParameter {
/**
* 获取20位随机数
* 4位年份+13位时间戳+3位随机数
* @author yuyu
*/
public static void main(String[] args) {
//调用生成id方法
System.out.println(getGuid());
}
/**
* 20位末尾的数字id
*/
public static int Guid=100;
public static String getGuid() {
DynamicParameter.Guid+=1;
long now = System.currentTimeMillis();
//获取4位年份数字
SimpleDateFormat dateFormat=new SimpleDateFormat("yyyy");
//获取时间戳
String time=dateFormat.format(now);
String info=now+"";
//获取三位随机数
//int ran=(int) ((Math.random()*9+1)*100);
//要是一段时间内的数据连过大会有重复的情况，所以做以下修改
int ran=0;
if(DynamicParameter.Guid>999){
DynamicParameter.Guid=100;
}
ran=DynamicParameter.Guid;
return time+info.substring(2, info.length())+ran;
}
}
-----------------------------------------------------------------------------------------------------------------------------------------------------------
TestReadCSV.java
package com.nantian.filereader;
import java.io.BufferedReader;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.IOException;
import java.io.LineNumberReader;
import java.nio.charset.Charset;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Map;
import com.csvreader.CsvReader;
public class TestReadCSV {
//@SHP mycat--主键自增
//static String ResultDynPara=DynamicParameter.getGuid();
public static void main(String[] args) throws ClassNotFoundException, SQLException, Exception {
String csvFilePath = "D:\\signmsg.csv";
Class.forName("com.mysql.jdbc.Driver");
Connection con=DriverManager.getConnection("jdbc:mysql://LocalHost:3306/testspringcloud", "root","root");
System.out.println("数据库连接成功！");
readCsv(csvFilePath,con);
System.out.println("数据导入完成!");
}
/**
* map.put("cardNum", split[2].trim());//卡号
map.put("idCard", split[7].trim());//证件号码
map.put("opertionId", split[8].trim());//柜员号
map.put("orgNum",split[11].trim());//网点号
map.put("mobile",split[15].trim() );//手机号
map.put("bankServiceType",split[3].trim());//行内业务类型
map.put("serviceType",split[17].trim());//外部业务类型
map.put("bankProduct", split[12].trim());//3位银行产品代码
* @param csvFilePath
* @param con
* @throws Exception
*/
private static void readCsv(String csvFilePath, Connection con) throws Exception {
int lineNumber=countLines();
System.out.println("lineNumber="+lineNumber);
BufferedReader br = null;
try {
String sql="Insert into b_signmsg(signMsgId,cardNum,idCard,opertionId,orgNum,mobile,bankServiceType,serviceType,bankProduct) values(?,?,?,?,?,?,?,?,?)";
PreparedStatement pStatement=con.prepareStatement(sql);
br = new BufferedReader(new FileReader(csvFilePath));
String line =null;
int i=1;
while((line = br.readLine())!=null){
String ResultDynPara=DynamicParameter.getGuid();
//读取到的内容给line变量
//line = br.readLine();//加上这一行会导致readLine()方法被执行了两次,导致读取数据不全
//System.out.println("line="+line);
//System.out.println("line="+line);
//ArrayList<String> csvList = new ArrayList<String>();
//CsvReader reader = new CsvReader(csvFilePath,',',Charset.forName("gbk")); //一般用这编码读就可以了
//reader.readHeaders(); // 跳过表头 如果需要表头的话，不要写这句。
//逐条读取记录，直至读完
//System.out.println(reader.readRecord());
//while(reader.readRecord()){
//csvList.add("123");
//}
//System.out.println("csvList="+csvList);
Map map=new HashMap<>();
//System.out.println(br.readLine());//读取每一行数据
//replaceAll的参数是regex,即基于规则表达式的替换,比如,可以通过replaceAll("\\d", "*")把一个字符串所有的数字字符都换成星号;
String[] split = line.replaceAll("\"", "").split(",");//去掉存入数据库中的""
System.out.println("split.length="+split.length);
System.out.println(split);
String cardNum="";
if (split[2].trim().length()!=0) {
cardNum=split[2].trim();
}
String idCard="";
if (split[7].trim().length()!=0) {
idCard= split[7].trim();
}
String opertionId="";
if (split[8].trim().length()!=0) {
opertionId=split[8].trim();
}
String orgNum="";
if (split[11].trim().length()!=0) {
orgNum=split[11].trim();
}
String mobile="";
if (split[15].trim().length()!=0) {
mobile=split[15].trim();
}
String bankServiceType="";
if (split[3].trim().length()!=0) {
bankServiceType=split[3].trim();
}
String serviceType="";
if (split[17].trim().length()!=0) {
serviceType=split[17].trim();
}
String bankProduct="";
if (split[12].trim().length()!=0) {
bankProduct=split[12].trim();
}
pStatement.setString(1, ResultDynPara);
pStatement.setString(2, cardNum);
pStatement.setString(3,idCard);
pStatement.setString(4, opertionId);
pStatement.setString(5, orgNum);
pStatement.setString(6, mobile);
pStatement.setString(7,bankServiceType );
pStatement.setString(8, serviceType);
pStatement.setString(9, bankProduct);
pStatement.execute();
System.out.println("pStatement="+pStatement);
System.out.println("第"+(i++)+"次循环");
}
} catch (SQLException e) {
e.printStackTrace();
}
}
private static int countLines() throws Exception {
int lineNumber=0;
File file4=new File("D:\\signmsg.csv");
if(file4.exists()) {
FileReader fr=new FileReader(file4);
LineNumberReader lnr=new LineNumberReader(fr);
long timeS=System.currentTimeMillis();//时间单位为ms
while(null != lnr.readLine()) {
lineNumber+=1;
}
long timeE=System.currentTimeMillis();
System.out.println("此种方法所耗时间为："+(timeE-timeS)+"ms,文件总条数为："+lineNumber+"条");
lnr.close();
}
return lineNumber;
}
}
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
需要额外导入mysql驱动包
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
数据库表结构
/*
Navicat MySQL Data Transfer
Source Server : sxnx-springcloud
Source Server Version : 50520
Source Host : localhost:3306
Source Database : testspringcloud
Target Server Type : MYSQL
Target Server Version : 50520
File Encoding : 65001
Date: 2020-04-15 10:04:12
*/
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for b_signmsg
-- ----------------------------
DROP TABLE IF EXISTS `b_signmsg`;
CREATE TABLE `b_signmsg` (
`signMsgId` bigint(100) NOT NULL COMMENT '短信签约主键，唯一标识',
`cardNum` varchar(22) NOT NULL COMMENT '卡号/账号',
`customName` varchar(50) DEFAULT NULL COMMENT '持卡户名',
`idCard` varchar(21) DEFAULT NULL COMMENT '证件号码',
`opertionId` char(255) DEFAULT '' COMMENT '柜员号',
`orgNum` char(255) DEFAULT NULL COMMENT '网点号',
`mobile` char(255) NOT NULL COMMENT '手机号',
`bankServiceType` varchar(255) DEFAULT NULL COMMENT '行内业务类型\r\n101:个人收费费实时动账\r\n111:个人免费实时动账\r\n201:对公收费费实时动账\r\n211:对公免费实时动账\r\n202:对公收费定时余额\r\n203:对公收费定时流水\r\n客户性质（1：个人 2：对公）+收费方式（0：收费 1：免费）+即时通/余额/流水 \r\n',
`serviceType` varchar(255) DEFAULT NULL COMMENT '外部业务类型（XHGRMF:个人免费 XHDGMF:对公免费 XHGYBY:个人收费包月 XHDGBY:对公收费包月）',
`bankProduct` char(255) DEFAULT NULL COMMENT '3位银行产品代码',
`regTime` varchar(14) DEFAULT NULL COMMENT '签约登记时间YYYYMMDDHHmmss',
PRIMARY KEY (`signMsgId`),
KEY `index_signmsg` (`cardNum`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='短信签约表';
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
导入结果展示
