java逐⾏读取csv⽂件存⼊mysql数据库DynamicParameter.java
//主键⾃增,⽣成uuid的⼯具类
package com.nantian.sms.util;
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 Guid();
public static void main(String[] args) throws ClassNotFoundException, SQLException, Exception {
String csvFilePath = "D:\\signmsg.csv";
Class.forName("sql.jdbc.Driver");
Connection Connection("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 Guid();
//读取到的内容给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")); //⼀般⽤这编码读就可以了
//adHeaders(); // 跳过表头如果需要表头的话，不要写这句。
//逐条读取记录，直⾄读完
//System.out.adRecord());
//adRecord()){
//csvList.add("123");
//}
//System.out.println("csvList="+csvList);
Map map=new HashMap<>();
//System.out.adLine());//读取每⼀⾏数据
//replaceAll的参数是regex，即基于规则表达式的替换,⽐如，可以通过replaceAll("\\d", "*")把⼀个字符串所有的数字字符都换成星号;
String[] split = placeAll("\"", "").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="";
java的jdbc连接数据库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);
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");
ists()) {
FileReader fr=new FileReader(file4);
LineNumberReader lnr=new LineNumberReader(fr);
long timeS=System.currentTimeMillis();//时间单位为ms
while(null != adLine()) {
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='短信签约表';
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
导⼊结果展⽰

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。