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

这篇文章主要为大家详细介绍了MySQL三种批量增加的性能分析，具有一定的参考价值，可以用来参考一下。

对此感兴趣的朋友，看看idc笔记做的技术笔记！

代码如下:

 
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; 
} 
} 

代码如下:

 
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); 
} 

代码如下:

 
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 hehe198504@126.com 
* @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; 
} 
} 

注：关于MySQL三种批量增加的性能分析的内容就先介绍到这里，更多相关文章的可以留意