fdisk -l
mount -t vfat -o iocharset=cp950 /dev/sda1 /mnt/dos
实现Java批量插入数据库数据，在javaeye中看到过几篇关于实现Java批量插入数据库数据，转载时没有找到，就自己写一下，也算是对自己学习过程中所遇到过的问题做一个总结。
一般关于批量向数据库插入数据都采用PreparedStatement、Statement…………也包括直接使用JDBC API、框架…………
也看到过几篇关于这些内容的总结，及大家的评论，以下为我总结的关于批量向数据库插入数据。
1，使用JDBC API实现配量插入数据：有篇文章介绍过关于JDBC API、Hibernate实现批量插入数据，采用JDBC API 方式实现随着数据的增长，速度更胜于Hibernate。当然，对于这个测试的准确我并不保证，但是我也会优先选用JDBC API方式实现(原因：简单、易学、相对于框架更通用，不会过时)。
2，采用PreparedStatement对象实现批量插入数据：PreparedStatement是真正的批处理命令，不是其他的伪批处理命令可以相比的(个人意见)，它相对于其他的实现批量处理是非常的强大，比如字段不断改变，每次都要从文件从新读取就只能使用PreparedStatement对象来实现。再有就是存在即合理，既然PreparedStatement对象可以多次高效地执行预编译的语句，就一定有其原因(JDk源码没有分析过，和Statement实现的区别不了解)。
3，实现批量插入数据库数据
Class.forName("com.mysql.jdbc.Driver");
Connection con = (Connection) DriverManager.getConnection("jdbc:mysql://" +
"localhost:3306/excel2mysql", "wanle", "wanle");
// 关闭事务自动提交
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 (?,'中国')");
for (int i = 0; i < 10000; i++) {
pst.setInt(1, i);
// 把一个SQL命令加入命令列表
pst.addBatch();
}
// 执行批量更新
pst.executeBatch();
// 语句执行完毕，提交本事务
con.commit();
Long endTime = System.currentTimeMillis();
System.out.println("用时：" + sdf.format(new Date(endTime - startTime)));
pst.close();
con.close();插入10000条数据用时3141毫秒，对于我已经很理想了， 毕竟我们不会使用MySQL进行非常大型项目的开发，对于10000条数据3秒多点，已经可以了，我相信对于大家应该也足以应付了，我们不会每天都插入10000条吧，当然对于我的话如果有这样的需求，我不会选择MySQL。
以上所有内容均为对于我所学习使用过程中、实际项目开发中的总结，也应用于其中。对于批量插入，数据导入均采用这样的方式。
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;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
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
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
List
List
List
List
List
List
List
List
List
List
List
List
List
List
List
List
List
List
List
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
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";// 驱动程序名
String url = "jdbc:mysql://10.0.4.162/test"; // URL指向要访问的数据库名facerobot
String user = "betbrain"; // MySQL配置时的用户名
String password = "betbrain"; // MySQL配置时的密码
Connection conn =null;
try {
Class.forName(driver); // 加载驱动程序
conn = DriverManager.getConnection(url, user, password);
// 连接数据库
}catch (Exception e) {
e.printStackTrace();
System.out.println("Connecting database error!");
}
return conn;
}
static int[] calculateAttScore(List
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
scores[i]=hash;
}
}
return scores;
}
static List
ResultSet rs= statement.executeQuery(sql);
List
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;
}
}
免责声明：本站发布的内容（图片、视频和文字）以原创、转载和分享为主，文章观点不代表本网站立场，如果涉及侵权请联系站长邮箱：is@yisu.com进行举报，并提供相关证据，一经查实，将立刻删除涉嫌侵权内容。
