HibernateTemplate和JdbcTemplate插入大批量数据对比

HibernateTemplate循环save方式

1
2
3
4
5
    public void forHiberInsert(List<SimsPdinfoStore> list){
        for(SimsPdinfoStore pds : list ){
            pDInfoStoreDao.save(pds);
        }
    }

JdbcTemplate批量插入方式

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
    public void batachInsertPdStore(List<SimsPdinfoStore> list) {
        final List<SimsPdinfoStore> tempList = list;
        String sql = "insert into sims_pdinfo_store(store_num, pd_type_code, reg_org_id, reg_user_id,"
                + " reg_day, last_up_tms, iflag, syn_flag) " + "values(?, ?, ?, ?, ?, ?, ?, ?)";
        jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
            public void setValues(PreparedStatement ps, int i) throws SQLException {
                String storeNum = tempList.get(i).getId().getStoreNum();
                String pdTypeCode = tempList.get(i).getId().getPdTtypeCode();
                String regOrgId = tempList.get(i).getRegOrgId();
                String regUserId = tempList.get(i).getRegUserId();
                Timestamp regDay = tempList.get(i).getRegDay();
                Timestamp lastUpTms = tempList.get(i).getLastUpTms();
                String iFlag = tempList.get(i).getIflag();

                ps.setString(1, storeNum);
                ps.setString(2, pdTypeCode);
                ps.setString(3, regOrgId);
                ps.setString(4, regUserId);
                ps.setTimestamp(5, regDay);
                ps.setTimestamp(6, lastUpTms);
                ps.setString(7, iFlag);
                ps.setString(8, "1"); // 未同步
            }

            public int getBatchSize() {
                return tempList.size();
            }
        });
    }

两个测试方法

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
    public void testInsert1(String arg) throws Exception {
        int round = Integer.parseInt(arg);
        Long start = System.currentTimeMillis();
        List<SimsPdinfoStore>  testList  = new ArrayList<SimsPdinfoStore>();
        for(int i =0 ; i< round; i++){
            SimsPdinfoStore s = new SimsPdinfoStore();
            SimsPdinfoStoreId comId = new SimsPdinfoStoreId();
            Long num = 90000000000L+i;
            comId.setPdTtypeCode(String.valueOf(num));
            comId.setStoreNum("11111111111");
            s.setId(comId);
            testList.add(s);
        }
       
        forHiberInsert(testList);
        Long end = System.currentTimeMillis();
        LogUtil.APP.info("采用循环save方式,构建并且插入"+round+"条,耗时:"+(end-start)+"ms");
    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
    public void testInsert2(String arg) throws Exception {
        int round = Integer.parseInt(arg);
        Long start = System.currentTimeMillis();
        List<SimsPdinfoStore>  testList  = new ArrayList<SimsPdinfoStore>();
        for(int i =0 ; i< round; i++){
            SimsPdinfoStore s = new SimsPdinfoStore();
            SimsPdinfoStoreId comId = new SimsPdinfoStoreId();
            Long num = 90000000000L+i;
            comId.setPdTtypeCode(String.valueOf(num));
            comId.setStoreNum("11111111111");
            s.setId(comId);
            testList.add(s);
        }
       
        batachInsertPdStore(testList);
        Long end = System.currentTimeMillis();
        LogUtil.APP.info("采用循环BatchPreparedStatementSetter方式,构建并且插入"+round+"条,耗时:"+(end-start)+"ms");
    }

实际测试,插入10万条数据,JdbcTemplate只需500~1000ms,HibernateTemplate至少需要200s,效率相差200倍以上。
HibernateTemplate在插入过程中还出现了两次JVM异常(插入到五六万条的时候):

#
# A fatal error has been detected by the Java Runtime Environment:
#
# EXCEPTION_ACCESS_VIOLATION (0xc0000005) at pc=0x000000005c493873, pid=12464, tid=13900
#
# JRE version: Java(TM) SE Runtime Environment (8.0_40-b25) (build 1.8.0_40-b25)
# Java VM: Java HotSpot(TM) 64-Bit Server VM (25.40-b25 mixed mode windows-amd64 compressed oops)
# Problematic frame:
# V [jvm.dll+0x213873]
#
# Failed to write core dump. Minidumps are not enabled by default on client versions of Windows
#
# An error report file with more information is saved as:
# D:\apps\eclipse_64\eclipse-jee-mars-1-win32-x86_64\eclipse\hs_err_pid12464.log
#
# If you would like to submit a bug report, please visit:
# http://bugreport.java.com/bugreport/crash.jsp
#