转自:https://blog.csdn.net/atggdsai/article/details/42804405
一开始尝试了单条插入,13万条记录差不多2个半钟才能完全从文件中读取到数据库中,后来用了批量插入,用时29秒!
代码如下:
- import java.io.BufferedReader;
- import java.io.File;
- import java.io.FileInputStream;
- import java.io.InputStreamReader;
- import java.sql.Connection;
- import java.sql.DriverManager;
- import java.sql.PreparedStatement;
- public class DataToMysql {
- public static void main(String[] args) {
- String url = "jdbc:mysql://localhost:3306/cityzendb";
- String username = "root";
- String password = "";
- PreparedStatement ps =null;
- Connection conn=null;
- InputStreamReader inputStreamReader = null;
- BufferedReader bufferedReader = null;
- File file=new File("E:\\data.txt");
- int currentInsert = 0;
- try {
- Class.forName("com.mysql.jdbc.Driver").newInstance();
- conn = DriverManager.getConnection(url, username, password);
- conn.setAutoCommit(false);
- String sql = "insert into data(email,password,name,identity,unknow,telephone,email2) values (?,?,?,?,?,?,?)";
- ps = conn.prepareStatement(sql);
- Long beginTime = System.currentTimeMillis();
- if(file.isFile() && file.exists()){ //判断文件是否存在
- inputStreamReader = new InputStreamReader(new FileInputStream(file),"gbk");//考虑到编码格式
- bufferedReader = new BufferedReader(inputStreamReader);
- String lineTxt = null;
- while((lineTxt = bufferedReader.readLine()) != null){
- //根据文件里每行的分隔符进行分隔并给ps赋值
- String[] strarray=lineTxt.split("----");
- ps.setString(1,strarray[0]);
- ps.setString(2,strarray[1]);
- ps.setString(3,strarray[2]);
- ps.setString(4,strarray[3]);
- ps.setString(5,strarray[4]);
- ps.setString(6,strarray[5]);
- ps.setString(7,strarray[6]);
- ps.addBatch();
- if(currentInsert%1000==0){
- ps.executeBatch();
- conn.commit();
- ps.clearBatch();
- System.out.println("已插入"+currentInsert+"条数据");
- }
- currentInsert++;
- }
- Long endTime = System.currentTimeMillis();
- System.out.println("插入"+currentInsert+"条记录共耗时:"+(endTime-beginTime)/1000+"秒");
- System.gc();
- inputStreamReader.close();
- bufferedReader.close();
- }else{
- System.out.println("找不到指定的文件");
- }
- } catch (Exception e) {
- System.out.println("读取文件内容出错");
- e.printStackTrace();
- }
- }
- }
另外,如果数据库的id字段加了自增长,假如已经增加了100条记录,用delete from table;把全部记录删除,再加记录的话,id会从101开始。如果想再从0开始,就用truncate table data;