三种批量增加的性能分析

      最近在深入学习hibernate,在进行批量操作时,发现hibernate批量操作性能非常低.于是就想找一个性能较高的方法,在对jdbc、jdbcTemplate、hibernate进行测试后,发现jdbc的执行效率是最高的,jdbcTemplate也很相近,hibernate就不考虑了,惨不忍睹啊.下面把代码写出来,希望大家批评指正.

首先domain对象.在这里使用的注解的方式,都是比较新的版本.

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的实现:

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

}

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

另,批量增加可以改造为泛型

public void batchAdd(List<T> batchArgs) {
        String tableName = metadataUtils.getTableName(clazz);// 表名
        StringBuffer fieldNames = new StringBuffer();// 字段名
        StringBuffer placeholders = new StringBuffer(); // 占位符

        final ClassMetadata meta = sessionFactory.getClassMetadata(clazz);
        final String[] properties = meta.getPropertyNames();
        final List<T> temp = batchArgs;
        final int count = batchArgs.size();

        for (String property : properties) {
            fieldNames.append(metadataUtils.getColumnName(clazz, property)).append(",");
            placeholders.append("?,");
        }

        fieldNames.deleteCharAt(fieldNames.length() - 1);
        placeholders.deleteCharAt(placeholders.length() - 1);

        StringBuffer sql = new StringBuffer(" INSERT INTO ").append(tableName).append(" (")
                .append(fieldNames.toString()).append(") VALUES (").append(placeholders).append(") ");

        BatchPreparedStatementSetter pss = new BatchPreparedStatementSetter() {
            // 为prepared statement设置参数。这个方法将在整个过程中被调用的次数
            public void setValues(PreparedStatement pstmt, int i) throws SQLException {

                for (int j = 0; j < properties.length; j++) {
                    Object fieldValue = meta.getPropertyValue(temp.get(i), properties[j]);
                    jdbcUtils.setParameter(fieldValue, pstmt, j + 1, false);
                }
            }

            // 返回更新的结果集条数
            public int getBatchSize() {
                return count;
            }
        };

        jdbcTemplate.batchUpdate(sql.toString(), pss);
    }

需要用到两个工具类:

package com.bao.sample.base.util.dao;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.sql.Types;
import java.util.Date;

import org.springframework.stereotype.Repository;

@Repository("jdbcUtils")
public class JdbcUtils {

    /**
     * @Description 目前支持String,Boolean,Byte,Character,Date类型的数据
     * @param fieldValue
     * @param ps
     * @param index 索引
     * @param isSearch
     * @throws SQLException
     */
    public void setParameter(Object fieldValue, PreparedStatement ps, int index, boolean isSearch)
            throws SQLException {

        Class<?> clazzValue = fieldValue.getClass();
        if (clazzValue == String.class) {
            if (isSearch) {
                ps.setString(index, "%" + (String) fieldValue + "%");
            } else {
                ps.setString(index, (String) fieldValue);
            }
        } else if (clazzValue == boolean.class || clazzValue == Boolean.class) {
            ps.setBoolean(index, (Boolean) fieldValue);
        } else if (clazzValue == byte.class || clazzValue == Byte.class) {
            ps.setByte(index, (Byte) fieldValue);
        } else if (clazzValue == char.class || clazzValue == Character.class) {
            ps.setObject(index, fieldValue, Types.CHAR);
        } else if (clazzValue == Date.class) {
            ps.setTimestamp(index, new Timestamp(((Date) fieldValue).getTime()));
        } else if (clazzValue.isArray()) {
            Object[] arrayValue = (Object[]) fieldValue;
            StringBuffer sb = new StringBuffer();
            for (int j = 0; j < arrayValue.length; j++) {
                sb.append(arrayValue[j]).append("、");
            }
            ps.setString(index, sb.deleteCharAt(sb.length() - 1).toString());
        } else {
            ps.setObject(index, fieldValue, Types.NUMERIC);
        }
    }

}

package com.bao.sample.base.util.dao;

import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import org.hibernate.cfg.Configuration;
import org.hibernate.mapping.Column;
import org.hibernate.mapping.PersistentClass;
import org.hibernate.mapping.PrimaryKey;
import org.hibernate.mapping.Property;
import org.springframework.stereotype.Repository;

@Repository("metadataUtils")
public class MetadataUtils {

    /**
     * @Description 获取PersistentClass对象
     * @param clazz
     * @return
     */
    public PersistentClass getPersistentClass(Class<?> clazz) {

        PersistentClass result = null;
        Configuration config = new Configuration().addAnnotatedClass(clazz);// .addAnnotatedClass(ClassUAO.class);

        config.buildMappings();// 此步不可少，如果没有创建，将报空指针

        result = config.getClassMapping(clazz.getName());
        return result;
    }

    /**
     * @Description 获取实体对应的表名
     * @param clazz
     * @return
     */
    public String getTableName(Class<?> clazz) {
        return getPersistentClass(clazz).getTable().getName();
    }

    /**
     * @Description 获取实体对应表的主键字段名称
     * @param clazz
     * @return
     */
    public PrimaryKey getPrimaryKey(Class<?> clazz) {
        return getPersistentClass(clazz).getTable().getPrimaryKey();
    }

    /**
     * @Description 获取实体对应表的主键字段名称，只适用于唯一主键的情况
     * @param clazz
     * @return
     */
    public String getUniqueKeyName(Class<?> clazz) {
        return getPrimaryKey(clazz).getColumn(0).getName();
    }

    /**
     * @Description 通过实体类和属性，获取实体类属性对应的表字段名称
     * @param clazz
     * @param propertyName
     * @return
     */
    public String getColumnName(Class<?> clazz, String propertyName) {

        Property property = getPersistentClass(clazz).getProperty(propertyName);
        Iterator<?> it = property.getColumnIterator();
        if (it.hasNext()) {
            Column column = (Column) it.next();
            return column.getName();
        }
        return null;
    }

    /**
     * @Description 获取实体类对应的表所有字段名称
     * @param clazz
     * @param primaryKeyFlag
     *            true:包括主键;false:不包括主键
     * @return
     */
    public List<String> getAllColumns(Class<?> clazz, boolean primaryKeyFlag) {
        List<String> columnNames = new ArrayList<String>();

        @SuppressWarnings("unchecked")
        Iterator<Column> it = getPersistentClass(clazz).getTable().getColumnIterator();

        for (Iterator<Column> iterator = it; iterator.hasNext();) {
            Column column = iterator.next();
            String columnName = column.getName();

            if (primaryKeyFlag) {// 得到这张表的所有字段名
                columnNames.add(column.getName());
            } else if (!columnName.equals(getUniqueKeyName(clazz))) {// 得到这张表除主键外的字段名
                columnNames.add(column.getName());
            }
            // 两者效果相同,不上上面方式较于理解
            // if (primaryKeyFlag || (!columnName.equals(getUniqueKeyName(clazz)))) {// 得到这张表的字段名
            // columnNames.add(column.getName());
            // }
        }

        return columnNames;

    }

}

经过测试,发现向mysql数据库增加1000条数据,直接使用jdbc耗时1609ms,使用jdbcTemplate耗时1625ms,使用泛型的批量增加耗时1719ms.开发人员可以根据具体情况选择.

注:代码不完整,需要完整可以联系我.QQ:417824818