java连接mysql批量写入数据



1、采用公认的MYSQL最快批量提交办法

public void index() throws UnsupportedEncodingException, Exception
{
//1000个一提交
int COMMIT_SIZE=25000;
//一共多少个
int COUNT=100000;

long a=System.currentTimeMillis();
Connection conn= null;

try {
Class.forName("com.mysql.jdbc.Driver");
String url="jdbc:mysql://10.10.3.13/new_lxyy_db?useUnicode=true&characterEncoding=UTF-8&rewriteBatchedStatements=true";
String user="root";
String password="dsideal";

conn= DriverManager.getConnection(url,user,password);
long starTime = System.currentTimeMillis();

conn.setAutoCommit(false);
PreparedStatement pstmt = conn.prepareStatement("load data local infile '' "+ "into table loadtest fields terminated by ','");
StringBuilder sb = new StringBuilder();
for (int i = 1; i <= COUNT; i++)
{
sb.append(i + "," + i+"abc" + "\n");
if (i % COMMIT_SIZE == 0)
{
InputStream is = new ByteArrayInputStream(sb.toString().getBytes());
((com.mysql.jdbc.Statement) pstmt).setLocalInfileInputStream(is);
pstmt.execute();
conn.commit();
sb.setLength(0);
}
}
InputStream is = new ByteArrayInputStream(sb.toString().getBytes());
((com.mysql.jdbc.Statement) pstmt).setLocalInfileInputStream(is);
pstmt.execute();
conn.commit();

long endTime = System.currentTimeMillis();
System.out.println("program runs " + (endTime - starTime) + "ms");
} catch (SQLException e) {
e.printStackTrace();
}finally{
conn.close();
}

//在最好的一行加上:
System.out.println("\r插入数据条数："+COUNT+",提交的阀值："+COMMIT_SIZE+",执行耗时 : "+(System.currentTimeMillis()-a)/1000f+" 秒 ");
renderNull();
}

2、对于表采用MyIsam引擎，效果嗷嗷的！

 

 

插入数据条数：40000,提交的阀值：1000,执行耗时 : 0.379 秒
插入数据条数：40000,提交的阀值：500,执行耗时 : 0.527 秒
插入数据条数：40000,提交的阀值：2000,执行耗时 : 0.306 秒
插入数据条数：40000,提交的阀值：5000,执行耗时 : 0.253 秒
插入数据条数：40000,提交的阀值：10000,执行耗时 : 0.198 秒
插入数据条数：40000,提交的阀值：15000,执行耗时 : 0.197 秒

插入数据条数：40000,提交的阀值：20000,执行耗时 : 0.255 秒
插入数据条数：40000,提交的阀值：25000,执行耗时 : 0.208 秒

插入数据条数：100000,提交的阀值：25000,执行耗时 : 0.402 秒

 

以上数据是在吴缤的MYSQL机器上获取的，我的机器上的海豚数据如下：

插入数据条数：100000,提交的阀值：25000,执行耗时 : 0.226 秒 

 

 注：后来黄海在研究如何批量更新数据时，找到了如下的方法：

把数据保存到一个文本文件中，字段间用TAB分隔，然后在mysql中执行

LOAD DATA INFILE "/PATH/oo.txt" REPLACE INTO TABLE xx; 有时间应该试试，据说效果不错啊！

如果有乱码问题，可以参考：http://hi.baidu.com/setcookie/item/ae43ea14403d29f9dceeca3a

 

后来补充了一个从数据库生成CSV文件的代码：

import java.io.BufferedInputStream;import java.io.BufferedReader;import java.io.File;import java.io.FileInputStream;import java.io.FileOutputStream;import java.io.FileReader;import java.io.IOException;import java.io.InputStream;import java.io.InputStreamReader;import java.io.OutputStreamWriter;import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;import java.util.List;import java.util.Properties;import au.com.bytecode.opencsv.CSVReader;import au.com.bytecode.opencsv.CSVWriter;public class Main {       /**     * @param args     * @throws Exception      */    public static void main(String[] args) throws Exception     {                testexportcsv();    }    private static void testexportcsv() throws IOException, SQLException, ClassNotFoundException     {         //当前目录                String v_path=Main.class.getResource("/").getPath()+"dsideal_config.properties";                //读取配置信息        String driverClass = readValue(v_path, "driverClass");         String jdbcUrl=readValue(v_path, "jdbcUrl");        String user=readValue(v_path, "user");        String password=readValue(v_path, "password");        File tempFile = new File("c:/allbill.csv");                     // 加载驱动程序          Class.forName(driverClass);          // 连续数据库          Connection conn = DriverManager.getConnection(jdbcUrl, user, password);          if(!conn.isClosed())          {                            //生成 UTF-8格式的CSV文件            OutputStreamWriter osw = new OutputStreamWriter(new FileOutputStream(tempFile),"UTF-8");            // statement用来执行SQL语句              Statement stmt= conn.createStatement();              // 要执行的SQL语句              String sql = "select * from t_resource_structure";              ResultSet rs = stmt.executeQuery(sql);            CSVWriter writer = new CSVWriter(osw,',');            writer.writeAll(rs, false);            writer.close();                     rs.close();            stmt.close();        }           conn.close();                  //测试一下读取         BufferedReader br=new BufferedReader(new InputStreamReader(new FileInputStream(tempFile.getAbsolutePath()),"UTF-8"));           CSVReader reader = new CSVReader(br);         List<String[]> myEntries = reader.readAll();                  System.out.println(myEntries.get(0)[3].toString());         reader.close();        }        //根据key读取value    public static String readValue(String filePath,String key) {     Properties props = new Properties();           try {            InputStream in = new BufferedInputStream (new FileInputStream(filePath));            props.load(in);            String value = props.getProperty (key);               return value;           } catch (Exception e) {            e.printStackTrace();            return null;           }    }}

 

在Sqlite中我们可以使用Replace into 的方法进行更新，参考：

http://www.cnblogs.com/liping13599168/archive/2011/05/24/2054908.html