2021-07-07

一个简单的txt文本文件读取并存入数据库的程序

创建一个类
public class import_ipflow {
//创建日志对象
private static Logger log = Logger.getLogger(import_ipflow.class);
private static List ipflows = new ArrayList();
private static String[] textArrays ;
private static List<String[]> lists = new ArrayList<String[]>();
public static void main(String[] args) {
log.info(“开始读取文本文件”);
parseText();
log.info(“文件大小为：”+lists.size()+“条”);
//遍历lists集合，存入到ipflows集合中
setIpflow();
//遍历ipflows集合，将数据存入库中
inputIpflow(ipflows);
//删除已读取的文件
deleteFile();
}
private static void deleteFile() {
Date dt = new Date();
SimpleDateFormat sdf=new SimpleDateFormat(“yyyyMMddHH”);
Calendar rightNow = Calendar.getInstance();
rightNow.setTime(dt);
//时间为7小时前
rightNow.add(Calendar.HOUR, -7);
Date dt1=rightNow.getTime();
String date = sdf.format(dt1);
try {
File file = new File(“/var/ftp/pub/fulx_zhuanxian_”+date+“.txt”);
if(file.exists()) {
file.delete();
log.info(“已删除日期为：”+date+“的文件”);
}
} catch (Exception e) {
log.info(e.toString());
e.printStackTrace();
}
}
private static void inputIpflow(List ipflows2) {
//使用c3p0连接数据库，配置在最下方
ComboPooledDataSource pool = new ComboPooledDataSource();
Connection conn = null;
String sql = null;
PreparedStatement ps=null;
try {
conn = pool.getConnection();
log.info(“连接数据库成功,开始插入数据”);
int i=0;
for(Ipflow ipflow : ipflows2) {
if(ipflow.getPath_code().contains(“vpn0”)||ipflow.getPath_code().contains(“eg10”)||ipflow.getPath_code().contains(“5401”)) {
sql = “insert into IPFLOW(CITY_ID,PATH_CODE,NODE_NAME,NODE_IP,PORT_NAME,PORT_DESC,
BANDWIDTH,INPUT_VAL,OUTPUT_VAL,SYN_TIME,PATH_TYPE,VAG_INPUT,VAG_OUTPUT) values(?,?,?,?,?,?,?,?,?,?,?,?,?)”;
ps = conn.prepareStatement(sql);
ps.setInt(1, ipflow.getCity_id());
ps.setString(2, ipflow.getPath_code());
ps.setString(3, ipflow.getNode_name());
ps.setString(4, ipflow.getNode_ip());
ps.setString(5, ipflow.getPort_name());
ps.setString(6, ipflow.getPort_desc());
ps.setString(7, ipflow.getBandwidth());
ps.setLong(8, ipflow.getInput_val());
ps.setLong(9, ipflow.getOutput_val());
ps.setString(10, ipflow.getSyn_time());
ps.setInt(11,ipflow.getPath_type());
ps.setString(12, ipflow.getVag_input());
ps.setString(13, ipflow.getVag_output());
ps.executeUpdate();
ps.close();
i++;
}
if(i%1000==0){
log.info(“插入新数据1000条”);
}
}
log.info(“ipflow表插入完成，新增数据：”+i+“条”);
} catch (SQLException e) {
e.printStackTrace();
System.out.println(“出错了”);
}finally {
if(conn != null) {
try {
conn.close();
} catch (SQLException e) {
log.info(e.toString());
e.printStackTrace();
}
}
}
}
private static void setIpflow() {
for (String[] list : lists) {
if(list.length>=11) {
Ipflow ipflow = new Ipflow();
ipflow.setCity_id(DbUtil.getCity_id(list[0]));
ipflow.setPath_code(DbUtil.getPath_code(list[5]));
ipflow.setNode_name(list[1]);
ipflow.setNode_ip(list[2]);
ipflow.setPort_name(list[3]);
ipflow.setPort_desc(DbUtil.getPort_desc(list[4], ipflow));
ipflow.setBandwidth(list[6]);
ipflow.setInput_val(Long.parseLong(list[7]));
ipflow.setOutput_val(Long.parseLong(list[8]));
ipflow.setVag_input(list[9]);
ipflow.setVag_output(list[10]);
ipflow.setSyn_time(DbUtil.getNowDate());
ipflow.setPath_type(-1);
ipflows.add(ipflow);
}
}
}
private static void parseText() {
BufferedReader br = null;
String line = “”;
int i=0,j=0,m=0;
try {
String date = getTime();
log.info(“读取时间为：”+date+“，的文件”);
br = new BufferedReader(new InputStreamReader(
new FileInputStream(new File(“/var/ftp/pub/fulx_zhuanxian_”+date+“.txt”)),“gbk”));
while( (line = br.readLine()) != null) {
//按照指定的字符拆分每一行数据，并存到字符串数组中
textArrays = line.split(“\|”+“\|”+“\|”);
//将每一个数组存入集合中
lists.add(textArrays);
}
} catch (Exception e) {
log.info(e.toString());
e.printStackTrace();
}
}
private static String getTime() {
String threeHoursAgoTime=“”;
Date dt = new Date();
SimpleDateFormat sdf=new SimpleDateFormat(“yyyyMMddHH”);
Calendar rightNow = Calendar.getInstance();
rightNow.setTime(dt);
rightNow.add(Calendar.HOUR, -3);
Date dt1=rightNow.getTime();
threeHoursAgoTime = sdf.format(dt1);
return threeHoursAgoTime;
}
}

c3p0.properties 文件内容如下
c3p0.driverClass=oracle.jdbc.driver.OracleDriver
c3p0.jdbcUrl=jdbc:oracle:thin:@localhost:1521:ORCL
c3p0.user=admin123
c3p0.password=admin123

ipflow实体类需要自己写，这个没有难度
需要导入c3p0，log4j以及数据库的包，其它的包java有自带