快捷導(dǎo)航

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

更新時(shí)間：2012年08月14日 17:15:07 作者：

最近在深入學(xué)習(xí)hibernate,在進(jìn)行批量操作時(shí),發(fā)現(xiàn)hibernate批量操作性能非常低.于是就想找一個(gè)性能較高的方法,在對(duì)jdbc、jdbcTemplate、hibernate進(jìn)行測(cè)試后,發(fā)現(xiàn)jdbc的執(zhí)行效率是最高的,jdbcTemplate也很相近,hibernate就不考慮了,慘不忍睹啊

下面把代碼寫(xiě)出來(lái),希望大家批評(píng)指正.
首先domain對(duì)象.在這里使用的注解的方式,都是比較新的版本.
User.java

復(fù)制代碼代碼如下:

 
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 注解最好標(biāo)記在get方法上.注意:采用一致的標(biāo)記方式,注解是以Id的標(biāo)記方式為準(zhǔn)的,如果標(biāo)記在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; 
} 
} 

接下來(lái)是Dao接口,繼承一個(gè)BaseDao接口.

復(fù)制代碼代碼如下:

 
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:執(zhí)行正常;>0:執(zhí)行成功的數(shù)目 
*/ 
public int batchAddUsingJdbc(List<User> users); 
public int batchAddUsingHibernate(List<User> users); 
public int batchAddUsingJdbcTemplate(List<User> users); 
} 

UserBatchDao的實(shí)現(xiàn):

復(fù)制代碼代碼如下:

 
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 三種批量增加方法,執(zhí)行效率依次是jdbc、jdbcTemplate、hibernate.<br />jdbc和jdbcTemplate執(zhí)行效率相近,不過(guò)jdbcTemplate可以使用事務(wù)注解控制,所以?xún)?yōu)先選擇. 
* @author Bob hehe198504@126.com 
* @date 2012-8-13 
*/ 
@Repository("userBatchDao") 
public class UserBatchDaoImpl extends BaseDaoImpl<User> implements UserBatchDao { 
@Resource 
protected JdbcTemplate jdbcTemplate; 
/** 
* 執(zhí)行10W條記錄,大致耗時(shí)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; 
} 
/** 
* 執(zhí)行10W條記錄,大致耗時(shí)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條以后，強(qiáng)制入庫(kù) 
// clear()清空緩存 
// postgres數(shù)據(jù)庫(kù)的隔離級(jí)別是已提交讀(Read committed)， 
// 所以flush以后，數(shù)據(jù)看不到，只有commit后才能看到數(shù)據(jù)， 
// 如果失敗，rollback,前面的flush的數(shù)據(jù)不會(huì)入庫(kù) 
if (i % 20 == 0) { 
session.flush(); 
session.clear(); 
} 
} 
return 0; 
} 
/** 
* 執(zhí)行10W條記錄,大致耗時(shí)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設(shè)置參數(shù)。這個(gè)方法將在整個(gè)過(guò)程中被調(diào)用的次數(shù) 
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()); 
} 
// 返回更新的結(jié)果集條數(shù) 
public int getBatchSize() { 
return count; 
} 
}; 
jdbcTemplate.batchUpdate(sql, pss); 
return 0; 
} 
public JdbcTemplate getJdbcTemplate() { 
return jdbcTemplate; 
} 
} 