mysql三种批量增加的性能分析

下面把代码写出来,希望大家批评指正.
首先domain对象.在这里使用的注解的方式,都是比较新的版本.
user.java

package com.bao.sample.s3h4.domain;
import javax.persistence.column;
import javax.persistence.entity;
import javax.persistence.generatedvalue;
import javax.persistence.generationtype;
import javax.persistence.id;
import javax.persistence.table;
import com.bao.sample.base.domain.basedomain;
@entity
@table(name = "t_user")
public class user extends basedomain {
private static final long serialversionuid = 1l;
private int id;
private string username;
private string password;
/**
* @description 注解最好标记在get方法上.注意:采用一致的标记方式,注解是以id的标记方式为准的,如果标记在get方法上,则忽略property上的注解.
* @return
*/
@id
@generatedvalue(strategy = generationtype.identity)
public int getid() {
return id;
}
public void setid(int id) {
this.id = id;
}
@column(nullable = false)
public string getusername() {
return username;
}
public void setusername(string username) {
this.username = username;
}
@column(nullable = false)
public string getpassword() {
return password;
}
public void setpassword(string password) {
this.password = password;
}
public user() {
super();
}
public user(int id, string username, string password) {
super();
this.id = id;
this.username = username;
this.password = password;
}
}

接下来是dao接口,继承一个basedao接口.

package com.bao.sample.s3h4.dao;
import java.util.list;
import com.bao.sample.base.dao.basedao;
import com.bao.sample.s3h4.domain.user;
public interface userbatchdao extends basedao<user> {
/**
* @description 批量增加操作
* @return -1:操作失败;0:执行正常;>0:执行成功的数目
*/
public int batchaddusingjdbc(list<user> users);
public int batchaddusinghibernate(list<user> users);
public int batchaddusingjdbctemplate(list<user> users);
}

userbatchdao的实现:

userbatchdaoimpl
package com.bao.sample.s3h4.dao;
import java.sql.connection;
import java.sql.preparedstatement;
import java.sql.sqlexception;
import java.util.list;
import javax.annotation.resource;
import org.hibernate.session;
import org.springframework.jdbc.core.batchpreparedstatementsetter;
import org.springframework.jdbc.core.jdbctemplate;
import org.springframework.orm.hibernate4.sessionfactoryutils;
import org.springframework.stereotype.repository;
import org.springframework.transaction.annotation.transactional;
import com.bao.sample.base.dao.basedaoimpl;
import com.bao.sample.s3h4.domain.user;
/**
*
* @description 三种批量增加方法,执行效率依次是jdbc、jdbctemplate、hibernate.<br />jdbc和jdbctemplate执行效率相近,不过jdbctemplate可以使用事务注解控制,所以优先选择.
* @author bob [e-mail]
* @date 2012-8-13
*/
@repository("userbatchdao")
public class userbatchdaoimpl extends basedaoimpl<user> implements userbatchdao {
@resource
protected jdbctemplate jdbctemplate;
/**
* 执行10w条记录,大致耗时15188ms
*/
@override
public int batchaddusingjdbc(list<user> users) {
int result = 0;
connection conn = null;
preparedstatement pstmt = null;
string sql = "insert into t_user (username,password) values (?,?)";
try {
conn = sessionfactoryutils.getdatasource(sessionfactory).getconnection();
conn.setautocommit(false);
pstmt = conn.preparestatement(sql);
for (int i = 0; i < users.size(); i++) {
int j = 1;
pstmt.setstring(j++, users.get(i).getusername());
pstmt.setstring(j++, users.get(i).getpassword());
pstmt.addbatch();
}
pstmt.executebatch();
conn.commit();
conn.setautocommit(true);
} catch (sqlexception e) {
if (conn != null) {
try {
conn.rollback();
} catch (sqlexception e1) {
e1.printstacktrace();
}
}
} finally {
if (pstmt != null) {
try {
pstmt.close();
} catch (sqlexception e) {
e.printstacktrace();
}
}
if (conn != null) {
try {
conn.close();
} catch (sqlexception e) {
e.printstacktrace();
}
}
}
return result;
}
/**
* 执行10w条记录,大致耗时131203ms,大致是jdbc或jdbctemplate的10倍.
*/
@override
// @transactional(norollbackfor = runtimeexception.class)
@transactional
public int batchaddusinghibernate(list<user> users) {
session session = this.getsession();
for (int i = 0; i < users.size(); i++) {
session.save(users.get(i));
// 添加20条以后，强制入库
// clear()清空缓存
// postgres数据库的隔离级别是已提交读(read committed)，
// 所以flush以后，数据看不到，只有commit后才能看到数据，
// 如果失败，rollback,前面的flush的数据不会入库
if (i % 20 == 0) {
session.flush();
session.clear();
}
}
return 0;
}
/**
* 执行10w条记录,大致耗时15671ms
*/
// @transactional(norollbackfor = runtimeexception.class)
@transactional
public int batchaddusingjdbctemplate(list<user> users) {
string sql = "insert into t_user (username,password) values (?,?)";
final list<user> tempusers = users;
final int count = users.size();
batchpreparedstatementsetter pss = new batchpreparedstatementsetter() {
// 为prepared statement设置参数。这个方法将在整个过程中被调用的次数
public void setvalues(preparedstatement pstmt, int i) throws sqlexception {
int j = 1;
pstmt.setstring(j++, tempusers.get(i).getusername());
pstmt.setstring(j++, tempusers.get(i).getpassword());
}
// 返回更新的结果集条数
public int getbatchsize() {
return count;
}
};
jdbctemplate.batchupdate(sql, pss);
return 0;
}
public jdbctemplate getjdbctemplate() {
return jdbctemplate;
}
}

外围的框架没有附上,有需要可以留言,我提供打包下载.
作者：听雨轩