读取出错!文本为几百M,刚才搞了下,插入问题解决了!但问题又来了,只能插入第一条,代码如下:
代码如下:package com.records.info;
import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStreamReader;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;public class DBconn {
ArrayList<String> column3string=new ArrayList<String>();
ArrayList<String> column13string=new ArrayList<String>();
String driver = "com.mysql.jdbc.Driver";
static String dbName = "dyform";
static String password = "root";
static String userName = "root";
static String url = "jdbc:mysql://localhost:3307/" + dbName;
static String sql = "select * from workinfo";
Connection conn=null;
public static Connection getConnection(){
String encoding="utf-8";
Connection conn=null;
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
try {
conn = DriverManager.getConnection(url, userName, password);
} catch (SQLException e) {
e.printStackTrace();
}
return conn;
}
public void readFile(String filename) throws SQLException{
try {
File file = new File(filename);
if (file.isFile() && file.exists()) {
InputStreamReader read = new InputStreamReader(
new FileInputStream(file),"utf-8");
BufferedReader bufferedReader = new BufferedReader(read);
String lineTXT = null;
while ((lineTXT = bufferedReader.readLine()) != null) {
String str="";
str+=" "+ lineTXT;
List<String> info=new ArrayList<String>();
String[] temp = info.toArray(new String[info.size()]);
if (temp!=null||temp.length!=0) {
temp = lineTXT.split(" ");
/* System.out.println("这是 第一个字段:" + temp[0]);
System.out.println("这是 第二个字段:" + temp[1]);
*/
/* String s0=line.substring(0,index);
String s1=line.substring(index,length);
column3string.add(s0);
column13string.add(curNum);*/
/*
column3string.add(temp[0]);
column13string.add(temp[1]);*/
}
column3string.add(temp[0]);
column13string.add(temp[1]);
}
read.close();
}else{
System.out.println("找不到指定的文件!");
}
} catch (Exception e) {
System.out.println("读取文件内容操作出错");
e.printStackTrace();
}
int length=column3string.size();
String sql = "insert into workinfo(column3,column13) values(?,?)";
int count = 0;//计数器
Connection conn =getConnection();
PreparedStatement pstmt = conn.prepareStatement(sql);
while(count<length){
conn.setAutoCommit(false);//设置数据手动提交,自己管理事务
String s1=column3string.get(0);
String s2=column13string.get(1);
count++;//每读取一行数据,计数器+1
pstmt.setString(1, s1);
pstmt.setString(2, s2);
pstmt.addBatch();//用PreparedStatement的批量处理 if(count%2000==0){//当增加了500个批处理的时候再提交
pstmt.executeBatch();//执行批处理
conn.commit();//提交
conn.close();//关闭数据库
conn =getConnection();//重新获取一次连接
conn.setAutoCommit(false);
pstmt = conn.prepareStatement(sql);
}
}
System.out.println("已插入"+count+"条数据");
if(count%2000!=0){//while循环外的判断,为了防止上面判断后剩下最后少于500条的数据没有被插入到数据库
pstmt.executeBatch();
conn.commit();
}
pstmt.close();
conn.close();
}
public void show(){
System.out.println("This is string:");
for(int i=0;i<column3string.size();i++){
System.out.println(column3string.get(i));
}
System.out.println("This is integer:");
for(int i=0;i<column13string.size();i++){
System.out.println(column13string.get(i));
}
}
public static void main(String[] args) {
DBconn test=new DBconn ();
test.show();
long timeTestStart=System.currentTimeMillis();//记录开始时间
try {
test.readFile("D:\\ProgramFiles\\tomcat7\\webapps\\ExcelDemo1\\upload\\1_attlog.dat");
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
System.out.println("succeed");
long timeTestEnd=System.currentTimeMillis();//记录结束时间
long time=timeTestEnd-timeTestStart;
long secondTime=time/1000;
System.out.println("Time:"+secondTime+" seconds");
}
}
代码如下:package com.records.info;
import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStreamReader;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;public class DBconn {
ArrayList<String> column3string=new ArrayList<String>();
ArrayList<String> column13string=new ArrayList<String>();
String driver = "com.mysql.jdbc.Driver";
static String dbName = "dyform";
static String password = "root";
static String userName = "root";
static String url = "jdbc:mysql://localhost:3307/" + dbName;
static String sql = "select * from workinfo";
Connection conn=null;
public static Connection getConnection(){
String encoding="utf-8";
Connection conn=null;
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
try {
conn = DriverManager.getConnection(url, userName, password);
} catch (SQLException e) {
e.printStackTrace();
}
return conn;
}
public void readFile(String filename) throws SQLException{
try {
File file = new File(filename);
if (file.isFile() && file.exists()) {
InputStreamReader read = new InputStreamReader(
new FileInputStream(file),"utf-8");
BufferedReader bufferedReader = new BufferedReader(read);
String lineTXT = null;
while ((lineTXT = bufferedReader.readLine()) != null) {
String str="";
str+=" "+ lineTXT;
List<String> info=new ArrayList<String>();
String[] temp = info.toArray(new String[info.size()]);
if (temp!=null||temp.length!=0) {
temp = lineTXT.split(" ");
/* System.out.println("这是 第一个字段:" + temp[0]);
System.out.println("这是 第二个字段:" + temp[1]);
*/
/* String s0=line.substring(0,index);
String s1=line.substring(index,length);
column3string.add(s0);
column13string.add(curNum);*/
/*
column3string.add(temp[0]);
column13string.add(temp[1]);*/
}
column3string.add(temp[0]);
column13string.add(temp[1]);
}
read.close();
}else{
System.out.println("找不到指定的文件!");
}
} catch (Exception e) {
System.out.println("读取文件内容操作出错");
e.printStackTrace();
}
int length=column3string.size();
String sql = "insert into workinfo(column3,column13) values(?,?)";
int count = 0;//计数器
Connection conn =getConnection();
PreparedStatement pstmt = conn.prepareStatement(sql);
while(count<length){
conn.setAutoCommit(false);//设置数据手动提交,自己管理事务
String s1=column3string.get(0);
String s2=column13string.get(1);
count++;//每读取一行数据,计数器+1
pstmt.setString(1, s1);
pstmt.setString(2, s2);
pstmt.addBatch();//用PreparedStatement的批量处理 if(count%2000==0){//当增加了500个批处理的时候再提交
pstmt.executeBatch();//执行批处理
conn.commit();//提交
conn.close();//关闭数据库
conn =getConnection();//重新获取一次连接
conn.setAutoCommit(false);
pstmt = conn.prepareStatement(sql);
}
}
System.out.println("已插入"+count+"条数据");
if(count%2000!=0){//while循环外的判断,为了防止上面判断后剩下最后少于500条的数据没有被插入到数据库
pstmt.executeBatch();
conn.commit();
}
pstmt.close();
conn.close();
}
public void show(){
System.out.println("This is string:");
for(int i=0;i<column3string.size();i++){
System.out.println(column3string.get(i));
}
System.out.println("This is integer:");
for(int i=0;i<column13string.size();i++){
System.out.println(column13string.get(i));
}
}
public static void main(String[] args) {
DBconn test=new DBconn ();
test.show();
long timeTestStart=System.currentTimeMillis();//记录开始时间
try {
test.readFile("D:\\ProgramFiles\\tomcat7\\webapps\\ExcelDemo1\\upload\\1_attlog.dat");
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
System.out.println("succeed");
long timeTestEnd=System.currentTimeMillis();//记录结束时间
long time=timeTestEnd-timeTestStart;
long secondTime=time/1000;
System.out.println("Time:"+secondTime+" seconds");
}
}
String s2=column13string.get(1);
//这里输出一下s1和s2看一看。 看看到底是输入数据库有问题还是读取数据有问题。
count++;//每读取一行数据,计数器+1
pstmt.setString(1, s1);
pstmt.setString(2, s2);