三种批量增加的性能分析

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

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

User.java 
 1 package com.bao.sample.s3h4.domain;
 2 
 3 import javax.persistence.Column;
 4 import javax.persistence.Entity;
 5 import javax.persistence.GeneratedValue;
 6 import javax.persistence.GenerationType;
 7 import javax.persistence.Id;
 8 import javax.persistence.Table;
 9 
10 import com.bao.sample.base.domain.BaseDomain;
11 
12 @Entity
13 @Table(name = "t_user")
14 public class User extends BaseDomain {
15 
16     private static final long serialVersionUID = 1L;
17     private int id;
18     private String username;
19     private String password;
20 
21     /**
22      * @Description 注解最好标记在get方法上.注意:采用一致的标记方式,注解是以Id的标记方式为准的,如果标记在get方法上,则忽略property上的注解.
23      * @return
24      */
25     @Id
26     @GeneratedValue(strategy = GenerationType.IDENTITY)
27     public int getId() {
28         return id;
29     }
30 
31     public void setId(int id) {
32         this.id = id;
33     }
34 
35     @Column(nullable = false)
36     public String getUsername() {
37         return username;
38     }
39 
40     public void setUsername(String username) {
41         this.username = username;
42     }
43 
44     @Column(nullable = false)
45     public String getPassword() {
46         return password;
47     }
48 
49     public void setPassword(String password) {
50         this.password = password;
51     }
52 
53     public User() {
54         super();
55     }
56 
57     public User(int id, String username, String password) {
58         super();
59         this.id = id;
60         this.username = username;
61         this.password = password;
62     }
63 
64 }

接下来是Dao接口,继承一个BaseDao接口.

UserBatchDao 
 1 package com.bao.sample.s3h4.dao;
 2 
 3 import java.util.List;
 4 
 5 import com.bao.sample.base.dao.BaseDao;
 6 import com.bao.sample.s3h4.domain.User;
 7 
 8 public interface UserBatchDao extends BaseDao<User> {
 9     
10     /**
11      * @Description 批量增加操作
12      * @return -1:操作失败;0:执行正常;>0:执行成功的数目
13      */
14     public int batchAddUsingJdbc(List<User> users);
15     
16     public int batchAddUsingHibernate(List<User> users);
17     
18     public int batchAddUsingJdbcTemplate(List<User> users);
19 
20 }

UserBatchDao的实现:

UserBatchDaoImpl 
  1 package com.bao.sample.s3h4.dao;
  2 
  3 import java.sql.Connection;
  4 import java.sql.PreparedStatement;
  5 import java.sql.SQLException;
  6 import java.util.List;
  7 
  8 import javax.annotation.Resource;
  9 
 10 import org.hibernate.Session;
 11 import org.springframework.jdbc.core.BatchPreparedStatementSetter;
 12 import org.springframework.jdbc.core.JdbcTemplate;
 13 import org.springframework.orm.hibernate4.SessionFactoryUtils;
 14 import org.springframework.stereotype.Repository;
 15 import org.springframework.transaction.annotation.Transactional;
 16 
 17 import com.bao.sample.base.dao.BaseDaoImpl;
 18 import com.bao.sample.s3h4.domain.User;
 19 
 20 /**
 21  * 
 22  * @Description 三种批量增加方法,执行效率依次是jdbc、jdbcTemplate、hibernate.<br />jdbc和jdbcTemplate执行效率相近,不过jdbcTemplate可以使用事务注解控制,所以优先选择.
 23  * @author Bob hehe198504@126.com
 24  * @date 2012-8-13
 25  */
 26 @Repository("userBatchDao")
 27 public class UserBatchDaoImpl extends BaseDaoImpl<User> implements UserBatchDao {
 28 
 29     @Resource
 30     protected JdbcTemplate jdbcTemplate;
 31 
 32     /**
 33      * 执行10W条记录,大致耗时15188ms
 34      */
 35     @Override
 36     public int batchAddUsingJdbc(List<User> users) {
 37 
 38         int result = 0;
 39 
 40         Connection conn = null;
 41         PreparedStatement pstmt = null;
 42         String sql = "insert into t_user (username,password) values (?,?)";
 43 
 44         try {
 45             conn = SessionFactoryUtils.getDataSource(sessionFactory).getConnection();
 46             conn.setAutoCommit(false);
 47             pstmt = conn.prepareStatement(sql);
 48 
 49             for (int i = 0; i < users.size(); i++) {
 50 
 51                 int j = 1;
 52                 pstmt.setString(j++, users.get(i).getUsername());
 53                 pstmt.setString(j++, users.get(i).getPassword());
 54                 pstmt.addBatch();
 55 
 56             }
 57             pstmt.executeBatch();
 58             conn.commit();
 59             conn.setAutoCommit(true);
 60 
 61         } catch (SQLException e) {
 62             if (conn != null) {
 63                 try {
 64                     conn.rollback();
 65                 } catch (SQLException e1) {
 66                     e1.printStackTrace();
 67                 }
 68             }
 69         } finally {
 70             if (pstmt != null) {
 71                 try {
 72                     pstmt.close();
 73                 } catch (SQLException e) {
 74                     e.printStackTrace();
 75                 }
 76             }
 77 
 78             if (conn != null) {
 79                 try {
 80                     conn.close();
 81                 } catch (SQLException e) {
 82                     e.printStackTrace();
 83                 }
 84             }
 85         }
 86 
 87         return result;
 88     }
 89 
 90     /**
 91      * 执行10W条记录,大致耗时131203ms,大致是jdbc或jdbcTemplate的10倍.
 92      */
 93     @Override
 94     // @Transactional(noRollbackFor = RuntimeException.class)
 95     @Transactional
 96     public int batchAddUsingHibernate(List<User> users) {
 97 
 98         Session session = this.getSession();
 99 
100         for (int i = 0; i < users.size(); i++) {
101 
102             session.save(users.get(i));
103             // 添加20条以后，强制入库
104             // clear()清空缓存
105             // postgres数据库的隔离级别是已提交读(Read committed)，
106             // 所以flush以后，数据看不到，只有commit后才能看到数据，
107             // 如果失败，rollback,前面的flush的数据不会入库
108             if (i % 20 == 0) {
109                 session.flush();
110                 session.clear();
111             }
112         }
113 
114         return 0;
115     }
116 
117     /**
118      *  执行10W条记录,大致耗时15671ms
119      */
120     // @Transactional(noRollbackFor = RuntimeException.class)
121     @Transactional
122     public int batchAddUsingJdbcTemplate(List<User> users) {
123 
124         String sql = "insert into t_user (username,password) values (?,?)";
125 
126         final List<User> tempUsers = users;
127         final int count = users.size();
128 
129         BatchPreparedStatementSetter pss = new BatchPreparedStatementSetter() {
130             // 为prepared statement设置参数。这个方法将在整个过程中被调用的次数
131             public void setValues(PreparedStatement pstmt, int i) throws SQLException {
132 
133                 int j = 1;
134                 pstmt.setString(j++, tempUsers.get(i).getUsername());
135                 pstmt.setString(j++, tempUsers.get(i).getPassword());
136             }
137 
138             // 返回更新的结果集条数
139             public int getBatchSize() {
140                 return count;
141             }
142         };
143 
144         jdbcTemplate.batchUpdate(sql, pss);
145 
146         return 0;
147     }
148 
149     public JdbcTemplate getJdbcTemplate() {
150         return jdbcTemplate;
151     }
152 
153 }

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

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

BaseDaoImpl 
 1 public void batchAdd(List<T> batchArgs) {
 2         String tableName = metadataUtils.getTableName(clazz);// 表名
 3         StringBuffer fieldNames = new StringBuffer();// 字段名
 4         StringBuffer placeholders = new StringBuffer(); // 占位符
 5 
 6         final ClassMetadata meta = sessionFactory.getClassMetadata(clazz);
 7         final String[] properties = meta.getPropertyNames();
 8         final List<T> temp = batchArgs;
 9         final int count = batchArgs.size();
10 
11         for (String property : properties) {
12             fieldNames.append(metadataUtils.getColumnName(clazz, property)).append(",");
13             placeholders.append("?,");
14         }
15 
16         fieldNames.deleteCharAt(fieldNames.length() - 1);
17         placeholders.deleteCharAt(placeholders.length() - 1);
18 
19         StringBuffer sql = new StringBuffer(" INSERT INTO ").append(tableName).append(" (")
20                 .append(fieldNames.toString()).append(") VALUES (").append(placeholders).append(") ");
21 
22         BatchPreparedStatementSetter pss = new BatchPreparedStatementSetter() {
23             // 为prepared statement设置参数。这个方法将在整个过程中被调用的次数
24             public void setValues(PreparedStatement pstmt, int i) throws SQLException {
25 
26                 for (int j = 0; j < properties.length; j++) {
27                     Object fieldValue = meta.getPropertyValue(temp.get(i), properties[j]);
28                     jdbcUtils.setParameter(fieldValue, pstmt, j + 1, false);
29                 }
30             }
31 
32             // 返回更新的结果集条数
33             public int getBatchSize() {
34                 return count;
35             }
36         };
37 
38         jdbcTemplate.batchUpdate(sql.toString(), pss);
39     }

需要用到两个工具类:

JdbcUtils 
 1 package com.bao.sample.base.util.dao;
 2 
 3 import java.sql.PreparedStatement;
 4 import java.sql.SQLException;
 5 import java.sql.Timestamp;
 6 import java.sql.Types;
 7 import java.util.Date;
 8 
 9 import org.springframework.stereotype.Repository;
10 
11 @Repository("jdbcUtils")
12 public class JdbcUtils {
13 
14     /**
15      * @Description 目前支持String,Boolean,Byte,Character,Date类型的数据
16      * @param fieldValue
17      * @param ps
18      * @param index 索引
19      * @param isSearch
20      * @throws SQLException
21      */
22     public void setParameter(Object fieldValue, PreparedStatement ps, int index, boolean isSearch)
23             throws SQLException {
24 
25         Class<?> clazzValue = fieldValue.getClass();
26         if (clazzValue == String.class) {
27             if (isSearch) {
28                 ps.setString(index, "%" + (String) fieldValue + "%");
29             } else {
30                 ps.setString(index, (String) fieldValue);
31             }
32         } else if (clazzValue == boolean.class || clazzValue == Boolean.class) {
33             ps.setBoolean(index, (Boolean) fieldValue);
34         } else if (clazzValue == byte.class || clazzValue == Byte.class) {
35             ps.setByte(index, (Byte) fieldValue);
36         } else if (clazzValue == char.class || clazzValue == Character.class) {
37             ps.setObject(index, fieldValue, Types.CHAR);
38         } else if (clazzValue == Date.class) {
39             ps.setTimestamp(index, new Timestamp(((Date) fieldValue).getTime()));
40         } else if (clazzValue.isArray()) {
41             Object[] arrayValue = (Object[]) fieldValue;
42             StringBuffer sb = new StringBuffer();
43             for (int j = 0; j < arrayValue.length; j++) {
44                 sb.append(arrayValue[j]).append("、");
45             }
46             ps.setString(index, sb.deleteCharAt(sb.length() - 1).toString());
47         } else {
48             ps.setObject(index, fieldValue, Types.NUMERIC);
49         }
50     }
51 
52 }
MetadataUtils 
  1 package com.bao.sample.base.util.dao;
  2 
  3 import java.util.ArrayList;
  4 import java.util.Iterator;
  5 import java.util.List;
  6 
  7 import org.hibernate.cfg.Configuration;
  8 import org.hibernate.mapping.Column;
  9 import org.hibernate.mapping.PersistentClass;
 10 import org.hibernate.mapping.PrimaryKey;
 11 import org.hibernate.mapping.Property;
 12 import org.springframework.stereotype.Repository;
 13 
 14 @Repository("metadataUtils")
 15 public class MetadataUtils {
 16 
 17     /**
 18      * @Description 获取PersistentClass对象
 19      * @param clazz
 20      * @return
 21      */
 22     public PersistentClass getPersistentClass(Class<?> clazz) {
 23 
 24         PersistentClass result = null;
 25         Configuration config = new Configuration().addAnnotatedClass(clazz);// .addAnnotatedClass(ClassUAO.class);
 26 
 27         config.buildMappings();// 此步不可少，如果没有创建，将报空指针
 28 
 29         result = config.getClassMapping(clazz.getName());
 30         return result;
 31     }
 32 
 33     /**
 34      * @Description 获取实体对应的表名
 35      * @param clazz
 36      * @return
 37      */
 38     public String getTableName(Class<?> clazz) {
 39         return getPersistentClass(clazz).getTable().getName();
 40     }
 41 
 42     /**
 43      * @Description 获取实体对应表的主键字段名称
 44      * @param clazz
 45      * @return
 46      */
 47     public PrimaryKey getPrimaryKey(Class<?> clazz) {
 48         return getPersistentClass(clazz).getTable().getPrimaryKey();
 49     }
 50 
 51     /**
 52      * @Description 获取实体对应表的主键字段名称，只适用于唯一主键的情况
 53      * @param clazz
 54      * @return
 55      */
 56     public String getUniqueKeyName(Class<?> clazz) {
 57         return getPrimaryKey(clazz).getColumn(0).getName();
 58     }
 59 
 60     /**
 61      * @Description 通过实体类和属性，获取实体类属性对应的表字段名称
 62      * @param clazz
 63      * @param propertyName
 64      * @return
 65      */
 66     public String getColumnName(Class<?> clazz, String propertyName) {
 67 
 68         Property property = getPersistentClass(clazz).getProperty(propertyName);
 69         Iterator<?> it = property.getColumnIterator();
 70         if (it.hasNext()) {
 71             Column column = (Column) it.next();
 72             return column.getName();
 73         }
 74         return null;
 75     }
 76 
 77     /**
 78      * @Description 获取实体类对应的表所有字段名称
 79      * @param clazz
 80      * @param primaryKeyFlag
 81      *            true:包括主键;false:不包括主键
 82      * @return
 83      */
 84     public List<String> getAllColumns(Class<?> clazz, boolean primaryKeyFlag) {
 85         List<String> columnNames = new ArrayList<String>();
 86 
 87         @SuppressWarnings("unchecked")
 88         Iterator<Column> it = getPersistentClass(clazz).getTable().getColumnIterator();
 89 
 90         for (Iterator<Column> iterator = it; iterator.hasNext();) {
 91             Column column = iterator.next();
 92             String columnName = column.getName();
 93 
 94             if (primaryKeyFlag) {// 得到这张表的所有字段名
 95                 columnNames.add(column.getName());
 96             } else if (!columnName.equals(getUniqueKeyName(clazz))) {// 得到这张表除主键外的字段名
 97                 columnNames.add(column.getName());
 98             }
 99             // 两者效果相同,不上上面方式较于理解
100             // if (primaryKeyFlag || (!columnName.equals(getUniqueKeyName(clazz)))) {// 得到这张表的字段名
101             // columnNames.add(column.getName());
102             // }
103         }
104 
105         return columnNames;
106 
107     }
108 
109 }

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

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

posted @ 2012-08-14 11:52  葛一凡  阅读(538)  评论(2)  编辑  收藏  举报