myJava applicaition

fdisk -l

mount -t vfat -o iocharset=cp950 /dev/sda1 /mnt/dos

實(shí)現(xiàn)Java批量插入數(shù)據(jù)庫(kù)數(shù)據(jù)，在javaeye中看到過(guò)幾篇關(guān)于實(shí)現(xiàn)Java批量插入數(shù)據(jù)庫(kù)數(shù)據(jù)，轉(zhuǎn)載時(shí)沒(méi)有找到，就自己寫(xiě)一下，也算是對(duì)自己學(xué)習(xí)過(guò)程中所遇到過(guò)的問(wèn)題做一個(gè)總結(jié)。

一般關(guān)于批量向數(shù)據(jù)庫(kù)插入數(shù)據(jù)都采用PreparedStatement、Statement…………也包括直接使用JDBC API、框架…………

也看到過(guò)幾篇關(guān)于這些內(nèi)容的總結(jié)，及大家的評(píng)論，以下為我總結(jié)的關(guān)于批量向數(shù)據(jù)庫(kù)插入數(shù)據(jù)。

1，使用JDBC API實(shí)現(xiàn)配量插入數(shù)據(jù)：有篇文章介紹過(guò)關(guān)于JDBC API、Hibernate實(shí)現(xiàn)批量插入數(shù)據(jù)，采用JDBC API 方式實(shí)現(xiàn)隨著數(shù)據(jù)的增長(zhǎng)，速度更勝于Hibernate。當(dāng)然，對(duì)于這個(gè)測(cè)試的準(zhǔn)確我并不保證，但是我也會(huì)優(yōu)先選用JDBC API方式實(shí)現(xiàn)(原因：簡(jiǎn)單、易學(xué)、相對(duì)于框架更通用，不會(huì)過(guò)時(shí))。

2，采用PreparedStatement對(duì)象實(shí)現(xiàn)批量插入數(shù)據(jù)：PreparedStatement是真正的批處理命令，不是其他的偽批處理命令可以相比的(個(gè)人意見(jiàn))，它相對(duì)于其他的實(shí)現(xiàn)批量處理是非常的強(qiáng)大，比如字段不斷改變，每次都要從文件從新讀取就只能使用PreparedStatement對(duì)象來(lái)實(shí)現(xiàn)。再有就是存在即合理，既然PreparedStatement對(duì)象可以多次高效地執(zhí)行預(yù)編譯的語(yǔ)句，就一定有其原因(JDk源碼沒(méi)有分析過(guò)，和Statement實(shí)現(xiàn)的區(qū)別不了解)。

3，實(shí)現(xiàn)批量插入數(shù)據(jù)庫(kù)數(shù)據(jù)

Java代碼

1. Class.forName("com.mysql.jdbc.Driver");
2. Connection con = (Connection) DriverManager.getConnection("jdbc:mysql://" +
3. "localhost:3306/excel2mysql", "wanle", "wanle");
4. // 關(guān)閉事務(wù)自動(dòng)提交
5. con.setAutoCommit(false);
7. SimpleDateFormat sdf = new SimpleDateFormat("HH:mm:ss:SS");
8. TimeZone t = sdf.getTimeZone();
9. t.setRawOffset(0);
10. sdf.setTimeZone(t);
11. Long startTime = System.currentTimeMillis();
13. PreparedStatement pst = (PreparedStatement) con.prepareStatement("insert into test04 values (?,'中國(guó)')");
14. for (int i = 0; i < 10000; i++) {
15. pst.setInt(1, i);
16. // 把一個(gè)SQL命令加入命令列表
17. pst.addBatch();
18. }
19. // 執(zhí)行批量更新
20. pst.executeBatch();
21. // 語(yǔ)句執(zhí)行完畢，提交本事務(wù)
22. con.commit();
24. Long endTime = System.currentTimeMillis();
25. System.out.println("用時(shí)：" + sdf.format(new Date(endTime - startTime)));
27. pst.close();
28. con.close();

            Class.forName("com.mysql.jdbc.Driver");
            Connection con = (Connection) DriverManager.getConnection("jdbc:mysql://" +
                    "localhost:3306/excel2mysql", "wanle", "wanle");
            // 關(guān)閉事務(wù)自動(dòng)提交
            con.setAutoCommit(false);

            SimpleDateFormat sdf = new SimpleDateFormat("HH:mm:ss:SS");
            TimeZone t = sdf.getTimeZone();
            t.setRawOffset(0);
            sdf.setTimeZone(t);
            Long startTime = System.currentTimeMillis();

            PreparedStatement pst = (PreparedStatement) con.prepareStatement("insert into test04 values (?,'中國(guó)')");
            for (int i = 0; i < 10000; i++) {
                pst.setInt(1, i);
                // 把一個(gè)SQL命令加入命令列表
                pst.addBatch();
            }
            // 執(zhí)行批量更新
            pst.executeBatch();
            // 語(yǔ)句執(zhí)行完畢，提交本事務(wù)
            con.commit();

            Long endTime = System.currentTimeMillis();
            System.out.println("用時(shí)：" + sdf.format(new Date(endTime - startTime)));

            pst.close();
            con.close();

插入10000條數(shù)據(jù)用時(shí)3141毫秒，對(duì)于我已經(jīng)很理想了， 畢竟我們不會(huì)使用MySQL進(jìn)行非常大型項(xiàng)目的開(kāi)發(fā)，對(duì)于10000條數(shù)據(jù)3秒多點(diǎn)，已經(jīng)可以了，我相信對(duì)于大家應(yīng)該也足以應(yīng)付了，我們不會(huì)每天都插入10000條吧，當(dāng)然對(duì)于我的話如果有這樣的需求，我不會(huì)選擇MySQL。

以上所有內(nèi)容均為對(duì)于我所學(xué)習(xí)使用過(guò)程中、實(shí)際項(xiàng)目開(kāi)發(fā)中的總結(jié)，也應(yīng)用于其中。對(duì)于批量插入，數(shù)據(jù)導(dǎo)入均采用這樣的方式。

System.getProperties().list(System.out);
Connection conn=initConnect2();
Statement statement = conn.createStatement();
//String sql = "insert into newsenginedb.ResolveTranslation select `id`,`typeId`,`disciplineId`,`collectedName`,`oddsModelObjectId`,`isApproved`,`lastUsedDate`,`note`,0 from oddsenginedb.ResolveTranslation where id =10017444";
//statement.executeUpdate(sql);
//String sql = "select `id`,`typeId`,`disciplineId`,`collectedName`,`oddsModelObjectId`,`isApproved`,`lastUsedDate`,`note`,0 from oddsenginedb.ResolveTranslation where id =10133053 or id =10159387"; //10017444
String sql = "select `id`,`typeId`,`disciplineId`,`collectedName`,`oddsModelObjectId`,`isApproved`,`lastUsedDate`,`note`,0 from oddsenginedb.ResolveTranslation limit 290";
ResultSet rs= statement.executeQuery(sql);
String aa="sdfsd'f";
aa=aa.replaceAll("'", "'");
System.out.println(aa);

while(rs.next()){
String id= rs.getString("id");
String typeId= rs.getString("typeId");
String disciplineId= rs.getString("disciplineId");
String collectedName= rs.getString("collectedName");
String oddsModelObjectId= rs.getString("oddsModelObjectId");
collectedName=collectedName.replaceAll("'", "'");
collectedName=new String(collectedName.getBytes("utf-8"),"latin1");
String isApproved= rs.getString("isApproved");
String lastUsedDate= rs.getString("lastUsedDate");
String note= rs.getString("note");
System.out.println(collectedName);
char[] test=new char[collectedName.length()];
collectedName.getChars(0, collectedName.length(), test, 0);
for(int i=0;iSystem.out.println((byte)test[i]);
}
System.out.println(collectedName);
//Connection conn2=initConnect3();
Statement statement2 = conn.createStatement();
String sql2 = "insert into newsenginedb.aa values("+id+","+typeId+","+disciplineId+",'"+collectedName+"',"+oddsModelObjectId+","+isApproved+",'"+lastUsedDate+"','"+note+"',0)";
System.out.println(sql2);
statement2.executeUpdate(sql2);

}

//package test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.text.*;

//import Record;

public class Test {
private static List sqlList = new ArrayList();
static{
sqlList.add("select b.familyName,b.givenName from UserEntityIP b where trim(ifnull(b.familyName,'')) <>'' and trim(ifnull(b.givenName,'')) <>'' group by b.familyName,b.givenName having count(*)>=2");
sqlList.add("select b.email from UserEntityIP b where trim(ifnull(b.email,'')) <>''group by b.email having count(*)>=2");
sqlList.add("select b.phone from UserEntityIP b where trim(ifnull(b.phone,'')) <>''group by b.phone having count(*)>=2");
sqlList.add("select b.addressLine1 from UserEntityIP b where trim(ifnull(b.addressLine1,'')) <>''group by b.addressLine1 having count(*)>=2");
sqlList.add("select b.birthday from UserEntityIP b where trim(ifnull(b.birthday,'')) <>''group by b.birthday having count(*)>=2");
sqlList.add("select b.city from UserEntityIP b where b.city regexp '[A-Za-z]'group by b.city having count(*)>=2");
sqlList.add("select b.gender from UserEntityIP b where trim(ifnull(b.gender,'')) <>''group by b.gender having count(*)>=2");
sqlList.add("select b.country from UserEntityIP b where trim(ifnull(b.country,'')) <>''group by b.country having count(*)>=2");
sqlList.add("select b.zipCode from UserEntityIP b where trim(ifnull(b.zipCode,'')) <>''group by b.zipCode having count(*)>=2");
sqlList.add("select b.ip from UserEntityIP b where trim(ifnull(b.ip,'')) <>''group by b.ip having count(*)>=2");
}
public static void main(String[] args) throws Exception {
Connection conn=initConnect();
Statement statement = conn.createStatement();
Long startTime =System.currentTimeMillis();

String sql = "select * from UserEntityIP";
ResultSet rs= statement.executeQuery(sql);
List recordList = new ArrayList();
while(rs.next()){
String fullName = rs.getString("familyName") +rs.getString("givenName");
String email = rs.getString("email");
String address1 = rs.getString("addressLine1");
String zipCode = rs.getString("zipCode");
String city = rs.getString("city");
String country = rs.getString("country");
String gender = rs.getString("gender");
String phone = rs.getString("phone");
String birthday = rs.getString("birthday");
String ipStr = rs.getString("ip");
Record record = new Record( fullName, email,
address1, zipCode, city,
country, gender, phone, birthday,ipStr);
recordList.add(record);
}

List distinctFullNameList = getAttList(statement, sqlList.get(0), "familyName", "givenName");
List distinctEmailList = getAttList(statement, sqlList.get(1), "email",null);
List distinctPhoneList = getAttList(statement, sqlList.get(2), "phone", null);
List distinctAddress1List = getAttList(statement, sqlList.get(3), "addressLine1",null);
List distinctBirthdayList = getAttList(statement, sqlList.get(4), "birthday", null);
List distinctCityList = getAttList(statement, sqlList.get(5), "city", null);
List distinctGenderList = getAttList(statement, sqlList.get(6), "gender", null);
List distinctCountryList = getAttList(statement, sqlList.get(7), "country", null);
List distinctZipcodeList = getAttList(statement, sqlList.get(8), "zipCode", null);
List distinctIPList = getAttList(statement, sqlList.get(9), "ip", null);


List fullNameList = new ArrayList();

List emailList = new ArrayList();

List address1List = new ArrayList();

List phoneList = new ArrayList();

List birthdayList = new ArrayList();

List cityList = new ArrayList();

List genderList = new ArrayList();

List countryList = new ArrayList();

List zipcodeList = new ArrayList();

List ipList = new ArrayList();

for(Record record:recordList){
fullNameList.add(record.getFullName());
emailList.add(record.getEmail());
address1List.add(record.getAddress1());
phoneList.add(record.getPhone());
birthdayList.add(record.getBirthday());
cityList.add(record.getCity());
genderList.add(record.getGender());
countryList.add(record.getCountry());
zipcodeList.add(record.getZipCode());
ipList.add(record.getIpStr());
}

int[] fullNameScores= calculateAttScore(fullNameList,distinctFullNameList,20);
int[] emailScores= calculateAttScore(emailList,distinctEmailList,20);
int[] address1Scores= calculateAttScore(address1List,distinctAddress1List,10);
int[] phoneScores= calculateAttScore(phoneList,distinctPhoneList,20);
int[] birthdayScores = calculateAttScore(birthdayList,distinctBirthdayList, 10);
int[] cityScores = calculateAttScore(cityList, distinctCityList, 2);
int[] genderScores = calculateAttScore(genderList, distinctGenderList, 1);
int[] countryScores = calculateAttScore(countryList, distinctCountryList, 2);
int[] zipcodeScores = calculateAttScore(zipcodeList, distinctZipcodeList, 3);
int[] ipScores = calculateAttScore(ipList, distinctIPList, 10);

int size = recordList.size();
System.out.println(fullNameScores.length);
int[] totalScores = new int[size];
for(int i=0;i totalScores[i]=fullNameScores[i]+
emailScores[i]+
address1Scores[i]+
phoneScores[i]+
birthdayScores[i]+
cityScores[i]+
genderScores[i]+
countryScores[i]+
zipcodeScores[i]+
ipScores[i];
if(totalScores[i]>=20)
System.out.println(totalScores[i] + "----------------"+fullNameList.get(i)+"----------------"+emailList.get(i)
+"----------------"+address1List.get(i)+"----------------"+
phoneList.get(i)+"----------------"+birthdayList.get(i));
}

System.out.println("Total execution time:"+(System.currentTimeMillis()-startTime));
}
static Connection initConnect(){
String driver = "com.mysql.jdbc.Driver";// 驅(qū)動(dòng)程序名

String url = "jdbc:mysql://10.0.4.162/test"; // URL指向要訪問(wèn)的數(shù)據(jù)庫(kù)名facerobot

String user = "betbrain"; // MySQL配置時(shí)的用戶名

String password = "betbrain"; // MySQL配置時(shí)的密碼
Connection conn =null;
try {

Class.forName(driver); // 加載驅(qū)動(dòng)程序

conn = DriverManager.getConnection(url, user, password);
// 連接數(shù)據(jù)庫(kù)
}catch (Exception e) {
e.printStackTrace();
System.out.println("Connecting database error!");
}
return conn;
}
static int[] calculateAttScore(List attList,List distinctList,int hash){
int [] scores = new int[attList.size()];
for(int i=0;i if(scores[i]!=0 || attList.get(i)==null)
continue;
for(int j=0;j if(attList.get(i).equals(distinctList.get(j)))
scores[i]=hash;
}
}
return scores;
}
static List getAttList(Statement statement,String sql,String attName1,String attName2) throws Exception{

ResultSet rs= statement.executeQuery(sql);
List attValueList = new ArrayList();
String attValue = null;
while(rs.next()){
if(attName2!=null && attName1!=null){
attValue = rs.getString(attName1) +rs.getString(attName2);

attValueList.add(attValue);
}
else if(attName1!=null){
attValue = rs.getString(attName1) ;

attValueList.add(attValue);
}
}
return attValueList;
}
}

//package test.vo;

public class Record {
private String fullName;
private String email;
private String address1;
private String zipCode;
private String city;
private String country;
private String gender;
private String phone;
private String birthday;
private String ipStr;

public String getFullName() {
return fullName;
}

public String getEmail() {
return email;
}

public String getAddress1() {
return address1;
}

public String getCity() {
return city;
}

public String getCountry() {
return country;
}

public String getGender() {
return gender;
}

public String getPhone() {
return phone;
}

public String getBirthday() {
return birthday;
}

public String getZipCode() {
return zipCode;
}

public Record(String fullName, String email, String address1,
String zipCode, String city, String country, String gender,
String phone, String birthday,String ipStr) {
super();
this.fullName = fullName;
this.email = email;
this.address1 = address1;
this.zipCode = zipCode;
this.city = city;
this.country = country;
this.gender = gender;
this.phone = phone;
this.birthday = birthday;
this.ipStr = ipStr;
}

public String getIpStr() {
return ipStr;
}

}

[@more@]