mysql batch最多_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 {

/**

* @Description 批量增加操作

* @return -1:操作失败;0:执行正常;>0:执行成功的数目

*/

public int batchAddUsingJdbc(List users);

public int batchAddUsingHibernate(List users);

public int batchAddUsingJdbcTemplate(List 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.
jdbc和jdbcTemplate执行效率相近,不过jdbcTemplate可以使用事务注解控制,所以优先选择.

* @author Bob hehe198504@126.com

* @date 2012-8-13

*/

@Repository("userBatchDao")

public class UserBatchDaoImpl extends BaseDaoImpl implements UserBatchDao {

@Resource

protected JdbcTemplate jdbcTemplate;

/**

* 执行10W条记录,大致耗时15188ms

*/

@Override

public int batchAddUsingJdbc(List 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 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 users) {

String sql = "insert into t_user (username,password) values (?,?)";

final List 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;

}

}

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