Hibernate 与 原生 Jdbc 批量安插时的差距

CPU : Genuine Intel(R) CPU T2080 @ 1.73GHz
Memory : 1G

mysql> select version();
+------------------+
| version()         |
+------------------+
| 5.1.32-community |
+------------------+
1 row in set (0.00 sec)

package com.model;

import java.io.Serializable;
import java.sql.Timestamp;
import java.util.UUID;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;

/**
 * @author <a href="liu.anxin13@gmail.com">Tony</a>
 */
@Entity
@Table(name = "T_USERINFO")
@org.hibernate.annotations.Entity(selectBeforeUpdate = true, dynamicInsert = true, dynamicUpdate = true)
public class UserInfo implements Serializable {

	private static final long serialVersionUID = -4855456169220894250L;

	@Id
	@Column(name = "ID", length = 32)
	private String id = UUID.randomUUID().toString().replaceAll("-", "");

	@Column(name = "CREATE_TIME", updatable = false)
	private Timestamp createTime = new Timestamp(System.currentTimeMillis());

	@Column(name = "UPDATE_TIME", insertable = false)
	private Timestamp updateTime = new Timestamp(System.currentTimeMillis());

	// setter/getter
}

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xmlns="http://www.springframework.org/schema/beans"
	xmlns:context="http://www.springframework.org/schema/context"
	xmlns:tx="http://www.springframework.org/schema/tx"
	xmlns:aop="http://www.springframework.org/schema/aop"
	xsi:schemaLocation="http://www.springframework.org/schema/beans
		http://www.springframework.org/schema/beans/spring-beans-2.5.xsd
		http://www.springframework.org/schema/context
		http://www.springframework.org/schema/context/spring-context-2.5.xsd
		http://www.springframework.org/schema/tx
		http://www.springframework.org/schema/tx/spring-tx-2.5.xsd
		http://www.springframework.org/schema/aop
		http://www.springframework.org/schema/aop/spring-aop-2.5.xsd">

	<context:component-scan base-package="com.dao,com.service" />

	<context:property-placeholder location="classpath:jdbc.properties" />

	<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource"
		destroy-method="close">
		<property name="driverClass" value="${jdbc.driver}" />
		<property name="jdbcUrl" value="${jdbc.url}" />
		<property name="user" value="${jdbc.username}" />
		<property name="password" value="${jdbc.password}" />

		<property name="maxPoolSize" value="50" />
		<property name="minPoolSize" value="5" />
		<property name="initialPoolSize" value="5" />
		<property name="acquireIncrement" value="5" />
		<property name="maxIdleTime" value="1800" />
		<property name="idleConnectionTestPeriod" value="1800" />
		<property name="maxStatements" value="1000" />
		<property name="breakAfterAcquireFailure" value="true" />
		<property name="testConnectionOnCheckin" value="true" />
		<property name="testConnectionOnCheckout" value="false" />
	</bean>

	<bean id="sessionFactory"
		class="org.springframework.orm.hibernate3.annotation.AnnotationSessionFactoryBean">
		<property name="dataSource" ref="dataSource" />
		<property name="configLocation" value="classpath:hibernate.cfg.xml" />
	</bean>

	<bean id="hibernateTemplate" class="org.springframework.orm.hibernate3.HibernateTemplate">
		<property name="sessionFactory" ref="sessionFactory" />
	</bean>
	
	<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
		<property name="dataSource" ref="dataSource" />
	</bean>

	<bean id="transactionManager"
		class="org.springframework.orm.hibernate3.HibernateTransactionManager">
		<property name="sessionFactory" ref="sessionFactory" />
	</bean>

	<tx:advice id="txAdvice" transaction-manager="transactionManager">
		<tx:attributes>
			<tx:method name="*" isolation="READ_COMMITTED"
				rollback-for="Throwable" />
		</tx:attributes>
	</tx:advice>

	<aop:config>
		<aop:pointcut id="services" expression="execution(* com.service.*.*(..))" />
		<aop:advisor advice-ref="txAdvice" pointcut-ref="services" />
	</aop:config>

</beans>

<?xml version='1.0' encoding='UTF-8'?>
<!DOCTYPE hibernate-configuration PUBLIC
          "-//Hibernate/Hibernate Configuration DTD 3.0//EN"
          "http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">

<hibernate-configuration>

	<session-factory>

		<property name="hibernate.dialect">
			org.hibernate.dialect.MySQLDialect
		</property>

		<!-- <property name="hibernate.jdbc.batch_size">50</property> -->

		<!-- 下面两句注释与否, 测试时也会有很大差别 -->
		<property name="hibernate.order_inserts">true</property>
		<property name="hibernate.order_updates">true</property>
		
		<!-- 表生成后请改为 none -->
		<property name="hibernate.hbm2ddl.auto">update</property>
		
		<!-- 如果数据量不多看看生成的 SQL 语句, 否则还是免了吧... -->
		<property name="hibernate.show_sql">false</property>
		<property name="hibernate.format_sql">false</property>

		<property name="hibernate.current_session_context_class">
			org.hibernate.context.ThreadLocalSessionContext
		</property>

		<mapping class="com.model.UserInfo" />

	</session-factory>

</hibernate-configuration>

package com.dao;

import java.io.Serializable;
import java.sql.SQLException;

import org.apache.log4j.Logger;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.springframework.orm.hibernate3.HibernateTemplate;
import org.springframework.stereotype.Repository;

/**
 * @author <a href="mailto:liu.anxin13@gmail.com">Tony</a>
 */
@Repository("hibernateDAO")
public class HibernateDAO<T extends Serializable> {

	private static final Logger log = Logger.getLogger(HibernateDAO.class);

	@Autowired
	@Qualifier("hibernateTemplate")
	private HibernateTemplate template;

	public Serializable save(T entity) {
		try {
			return template.save(entity);
		} catch (Exception e) {
			log.info("save exception : " + e.getMessage());
			throw new RuntimeException(e);
		}
	}

	public void flush() {
		try {
			template.flush();
		} catch (Exception e) {
			log.error("flush exception : " + e.getMessage());
			throw new RuntimeException(e);
		}
	}
	
	public int executeByHql(String hql, Object... values)
			throws HibernateException {
		try {
			return template.bulkUpdate(hql, values);
		} catch (Exception e) {
			log.error("executeByHql exception : " + e.getMessage());
			throw new HibernateException(e);
		}
	}

	public void clear() {
		try {
			template.clear();
		} catch (Exception e) {
			log.error("clear exception : " + e.getMessage());
			throw new RuntimeException(e);
		}
	}
	
	public void execute(final String sql, final Object... values) {
		template.execute(new HibernateCallback() {
			
			public Object doInHibernate(Session session) throws HibernateException,
					SQLException {
				Query query = session.createSQLQuery(sql);
				
				int i = 0;
				for (Object obj : values) {
					query.setParameter(i++, obj);
				}
				return query.executeUpdate();
			}
		});
	}

}

package com.service;

import java.util.Date;

import org.apache.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.stereotype.Service;

import com.dao.HibernateDAO;
import com.model.UserInfo;
import com.util.Global;
import com.util.StringUtils;

/**
 * @author <a href="mailto:liu.anxin13@gmail.com">Tony</a>
 */
@Service("hibernateService")
public class HibernateService {
	
	private static final Logger log = Logger.getLogger(HibernateService.class);

	@Autowired
	@Qualifier("hibernateDAO")
	private HibernateDAO<UserInfo> dao;

	public void testOcean(long num) {
		UserInfo user = null;
		
		log.info("start test Hibernate...");
		long begin = System.currentTimeMillis();
		
		for (int i = 1; i < (num + 1); i++) {
			user = new UserInfo();
			dao.save(user);
			
			user = null;
			
			if (Global.IS_BATCH) {
				if (i % Global.BATCH_NUMBER == 0) {
					dao.flush();
					dao.clear();
				}
			}
			if (i % 20000 == 0)
				System.out.printf("%s [%8d] number with a count...\n", StringUtils.getStringFromDate(new Date(), ""), i);
		}

		long end = System.currentTimeMillis();
		log.info("insert " + num + " count, consume " + (end - begin) / 1000.00000000 + " seconds");
	}
	
	public int count() {
		long begin = System.currentTimeMillis();
		int rt = dao.executeByHql("SELECT COUNT(ID) FROM UserInfo");
		
		long end = System.currentTimeMillis();
		log.info("query count, consume [" + (end - begin) / 1000.00000000 + "] seconds");
		
		return rt;
	}
	
	public void truncate() {
		long begin = System.currentTimeMillis();
		
		// 执行 native SQL
		dao.execute("TRUNCATE T_USERINFO");
		
		long end = System.currentTimeMillis();
		log.info("truncate table, consume [" + (end - begin) / 1000.00000000 + "] seconds");
	}
	
}

package com.test;

import org.apache.log4j.Logger;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

import com.service.HibernateService;
import com.util.Global;

/**
 * @author <a href="mailto:liu.anxin13@gmail.com">Tony</a>
 */
@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations = { "classpath:applicationContext.xml" })
public class TestHibernate {

	private static final Logger log = Logger.getLogger(TestHibernate.class);

	@Autowired
	@Qualifier("hibernateService")
	private HibernateService hibernate;

	@Test
	public void testHibernate() {
		hibernate.testOcean(Global.NUM);
		
		System.err.printf("see momory! have a quick please...\n");
		try {
			Thread.sleep(10000);
		} catch (Exception e) {
			log.error("exception: " + e.getMessage());
		}
	}

	// @Test
	public void testCount() {
		log.info("count: " + hibernate.count());
	}
	
	// @Test
	public void truncate() {
		hibernate.truncate();
	}

}

package com.dao;

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.util.Properties;

import org.apache.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

/**
 * @author <a href="mailto:liu.anxin13@gmail.com">Tony</a>
 */
@Repository("jdbcDAO")
public class JdbcDAO {
	
	private static final Logger log = Logger.getLogger(JdbcDAO.class);
	
	@Autowired
	@Qualifier("jdbcTemplate")
	private JdbcTemplate template;
	
	public Connection getConn() {
		try {
			return template.getDataSource().getConnection();
		} catch (Exception e) {
			log.info("获取连接时异常: " + e.getMessage());
			throw new RuntimeException(e);
		}
	}
	
	// 如果感觉使用 spring 获取的 Connection 太慢, 直接使用下面的直连...
	
	private static Properties p = new Properties();
	
	static {
		try {
			p.load(JdbcDAO.class.getClassLoader().getResourceAsStream("jdbc.properties"));
		} catch (IOException e) {
		}
	}
	
	public Connection getConnection() {
		try {
			String driver = p.getProperty("jdbc.driver");
			String url = p.getProperty("jdbc.url");
			String user = p.getProperty("jdbc.username");
			String password = p.getProperty("jdbc.password");
			
			Class.forName(driver);
			return DriverManager.getConnection(url, user, password);
		} catch (Exception e) {
			log.error("connection exception: " + e.getMessage());
			throw new RuntimeException(e);
		}
	}

}

package com.service;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Date;

import org.apache.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.stereotype.Service;

import com.dao.JdbcDAO;
import com.model.UserInfo;
import com.util.Global;
import com.util.StringUtils;

/**
 * @author <a href="mailto:liu.anxin13@gmail.com">Tony</a>
 */
@Service("jdbcService")
public class JdbcService {
	
	private static final Logger log = Logger.getLogger(JdbcService.class);
	
	@Autowired
	@Qualifier("jdbcDAO")
	private JdbcDAO dao;

	/**
	 * 使用 addbatch 的方式进行海量插入
	 * 
	 * @param num
	 */
	public void testOcean(long num) {
		// 使用 spring 获取连接
		Connection conn = dao.getConn();
		
		try {
			conn.setAutoCommit(false);
			String sql = "INSERT INTO T_USERINFO(CREATE_TIME, ID) VALUES(?, ?)";
			PreparedStatement pstm = conn.prepareStatement(sql);
			UserInfo user = null;
			
			log.info("start test jdbc...");
			long begin = System.currentTimeMillis();
			
			for (int i = 1; i < (num + 1); i++) {
				// 要保证公平, 也在循环中 new 对象
				user = new UserInfo();
				pstm.setTimestamp(1, user.getCreateTime());
				pstm.setString(2, user.getId());
				pstm.addBatch();
				
				user = null;
				
				// 批处理
				if (Global.IS_BATCH) {
					if (i % Global.BATCH_NUMBER == 0) {
						pstm.executeBatch();
						conn.commit();
						pstm.clearBatch();
					}
				}
				if (i % 20000 == 0)
					System.out.printf("%s [%8d] number with a count...\n", 
							StringUtils.getStringFromDate(new Date(), ""), i);
			}
			pstm.executeBatch();
			conn.commit();
			pstm.clearBatch();

			long end = System.currentTimeMillis();
			log.info("insert " + num + " count, consume " + (end - begin) / 1000.00000000 + " seconds");
			
		} catch (Exception e) {
			log.error("exception: " + e.getMessage());
			throw new RuntimeException(e);
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
				conn = null;
			}
		}
	}
	
	/**
	 * 使用拼接字符串的方式进行海量插入
	 * 
	 * @param num
	 */
	public void testOceanWithSplit(long num) {
		Connection conn = dao.getConn();
		
		try {
			conn.setAutoCommit(false);
			Statement st = conn.createStatement();
			
			StringBuilder sql = new StringBuilder();
			String str = "INSERT INTO T_USERINFO(CREATE_TIME, ID) VALUES ";
			sql.append(str);
			UserInfo user = null;
			
			log.info("start test jdbc with split...");
			long begin = System.currentTimeMillis();
			for (int i = 1; i < (num + 1); i++) {
				// 要保证公平, 也在循环中 new 对象
				user = new UserInfo();
				
				sql.append("('").append(user.getCreateTime());
				sql.append("', '");
				sql.append(user.getId()).append("'),");
				
				user = null;
				
				// 批处理
				if (Global.IS_BATCH) {
					if (i % Global.BATCH_NUMBER == 0) {
						// 执行并提交至数据库
						st.execute(sql.deleteCharAt(sql.length() - 1).toString());
						conn.commit();
						
						// 重新开始拼接字符串
						sql.delete(str.length(), sql.length());
					}
				}
				if (i % 20000 == 0)
					System.out.printf("%s [%8d] number with a count...\n", 
							StringUtils.getStringFromDate(new Date(), ""), i);
			}
			// 如果 总数据量不能整除批量数则将余下的数据进行执行
			if (num % Global.BATCH_NUMBER != 0) {
				st.execute(sql.deleteCharAt(sql.length() - 1).toString());
				conn.commit();
			}
			
			long end = System.currentTimeMillis();
			log.info("insert " + num + " count, consume " + (end - begin) / 1000.00000000 + " seconds");
			
		} catch (Exception e) {
			log.error("exception: " + e.getMessage());
			throw new RuntimeException(e);
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
				conn = null;
			}
		}
	}
	
	/**
	 * 查询数据量
	 * 
	 * @return
	 */
	public int count() {
		int rt = 0;
		Connection conn = dao.getConn();
		long begin = System.currentTimeMillis();
		String sql = "SELECT COUNT(*) FROM T_USERINFO";
		try {
			ResultSet rs = conn.createStatement().executeQuery(sql);
			rs.next();
			rt = rs.getInt(1);
		} catch (Exception e) {
			log.error("exception: " + e.getMessage());
			throw new RuntimeException(e);
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
				conn = null;
			}
		}
		long end = System.currentTimeMillis();
		log.info("query count, consume [" + (end - begin) / 1000.00000000 + "] seconds");
		return rt;
	}
	
	/**
	 * 清表
	 */
	public void truncate() {
		// 使用直连获取连接.
		Connection conn = dao.getConnection();
		
		String sql = "TRUNCATE T_USERINFO";
		long begin = System.currentTimeMillis();
		
		try {
			conn.createStatement().executeUpdate(sql);
		} catch (Exception e) {
			log.error("exception : " + e.getMessage());
			throw new RuntimeException(e);
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
				conn = null;
			}
		}
		
		long end = System.currentTimeMillis();
		log.info("truncate table, consume [" + (end - begin) / 1000.00000000 + "] seconds");
	}

}

package com.test;

import org.apache.log4j.Logger;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

import com.service.JdbcService;
import com.util.Global;

/**
 * @author <a href="mailto:liu.anxin13@gmail.com">Tony</a>
 */
@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations = { "classpath:applicationContext.xml" })
public class TestJdbc {
	
	private static final Logger log = Logger.getLogger(TestJdbc.class);
	
	@Autowired
	@Qualifier("jdbcService")
	private JdbcService jdbc;
	
	@Test
	public void testJdbcWithSplit() {
		jdbc.testOceanWithSplit(Global.NUM);
		
		nothing();
	}
	
	// @Test
	public void testJdbc() {
		jdbc.testOcean(Global.NUM);
		
		nothing();
	}
	
	// @Test
	public void truncateJdbc() {
		jdbc.truncate();
	}
	
	public void nothing() {
		System.err.printf("see momory! have a quick please...\n");
		try {
			Thread.sleep(10000);
		} catch (Exception e) {
			log.error("exception: " + e.getMessage());
		}
	}
	
	// @Test
	public void testCount() {
		int count = jdbc.count();
		log.info("count: " + count);
	}

}

乖乖, 一条 select count 居然花了这么久

public class test {

    public static Connection cn;

    static {
        try {
            Class.forName("oracle.jdbc.OracleDriver");
            cn = DriverManager.getConnection//隐去
        } catch (SQLException ex) {
            Logger.getLogger(test.class.getName()).log(Level.SEVERE, null, ex);
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(test.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public static void main(String[] args) throws SQLException {
        cn.setAutoCommit(false);
        test(100, 10);
        test(1000, 100);
        test(10000, 1000);
        test(100000, 1000);
        cn.close();
    }

    static void test(int count, int commitlength) throws SQLException {
        System.out.println("测试：数据长度[" + count + "],每" + commitlength + "提交一次");
        Long d1 = System.currentTimeMillis();
        clear();
        Long d2 = System.currentTimeMillis();
        updateString(count, commitlength);
        Long d3 = System.currentTimeMillis();
        clear();
        Long d4 = System.currentTimeMillis();
        updatePrepared(count, commitlength);
        Long d5 = System.currentTimeMillis();
        clear();
        Long d6 = System.currentTimeMillis();
        updatePrepared(count, commitlength);
        Long d7 = System.currentTimeMillis();
        clear();
        Long d8 = System.currentTimeMillis();
        updateString(count, commitlength);
        Long d9 = System.currentTimeMillis();

        System.out.println("第一步清理耗时:" + (d2 - d1) + "ms");
        System.out.println("第一次一般提交耗时:" + (d3 - d2) + "ms");
        System.out.println("第二步清理耗时:" + (d4 - d3) + "ms");
        System.out.println("第一次Prepared提交耗时:" + (d5 - d4) + "ms");
        System.out.println("第二步清理耗时:" + (d6 - d5) + "ms");
        System.out.println("第二次Prepared提交耗时:" + (d7 - d6) + "ms");
        System.out.println("第三步清理耗时:" + (d8 - d7) + "ms");
        System.out.println("第二次一般提交耗时:" + (d9 - d8) + "ms");
        System.out.println("-----------------------------------------------");
    }

    static void updateString(int count, int commitlength) throws SQLException {
        String sqlhead = "insert into ZH(zh,product,state) values";
        Statement stmt = cn.createStatement();
        int k = 0;
        for (int i = 0; i < count; i++) {
            k++;
            String zh = "zh" + i;
            String pro = "pro" + i;
            String state = "state" + i;
            StringBuilder sb = new StringBuilder();
            sb.append(sqlhead).append(" ('").append(zh).append("','").append(pro).append("','").append(state).append("')");
            stmt.addBatch(sb.toString());
            if (k > commitlength) {
                k = 0;
                stmt.executeBatch();
                cn.commit();
            }
        }
        stmt.executeBatch();
        cn.commit();
        stmt.close();
    }

    static void updatePrepared(int count, int commitlength) throws SQLException {
        PreparedStatement pstmt = cn.prepareStatement("insert into ZH(zh,product,state) values (?,?,?)");
        int k = 0;
        for (int i = 0; i < count; i++) {
            k++;
            String zh = "zh" + i;
            String pro = "pro" + i;
            String state = "state" + i;
            pstmt.setString(1, zh);
            pstmt.setString(2, pro);
            pstmt.setString(3, state);
            pstmt.addBatch();
            if (k > commitlength) {
                k = 0;
                pstmt.executeBatch();
                cn.commit();
                pstmt.clearBatch();
            }
        }
        pstmt.executeBatch();
        cn.commit();
        pstmt.close();
    }

    static void clear() throws SQLException {
        String sqlhead = "delete from ZH";
        Statement stmt = cn.createStatement();
        stmt.executeUpdate(sqlhead);
        cn.commit();
        stmt.close();
    }
}

// 从开始到最后, 我只有这一个 StringBuilder 对象, 每次生成一条SQL, 数据库执行时却插入了多条, 然后又开始拼接 SQL...
StringBuilder sql = new StringBuilder();
String str = "INSERT INTO T_USERINFO(CREATE_TIME, ID) VALUES ";
sql.append(str);
for (int i = 1; i < (num + 1); i++) {
	// 要保证公平, 也在循环中 new 对象
	user = new UserInfo();
	
	sql.append("('").append(user.getCreateTime());
	sql.append("', '");
	sql.append(user.getId()).append("'),");

	if (i % 50 == 0) {
		// 执行并提交至数据库. 只执行一条 SQL, 结果却会在数据库插入多条数据.
		// 并没有使用 addbatch! addbatch 时使用预编译, 这一点我还是知道的...
		st.execute(sql.deleteCharAt(sql.length() - 1).toString());
		conn.commit();
		
		// 重新开始拼接字符串
		sql.delete(str.length(), sql.length());
	}
}
st.execute(sql.deleteCharAt(sql.length() - 1).toString());
conn.commit();