涉及大数据量操作,框架选型时我们需要对比hibernate和mybatis的性能,我负责hibernate,写出了hibernate的增删改查代码,修改数量级参数,即可获得操作时间
前辈们对hibernate的性能优化有很多方法,最常用的方法就是分段提交 和绕过hibernate API 直接用JDBC去操作数据库,众所周知,JDBC肯定是最快的!
以下代码体现了分段提交 以及用JDBC操作数据库
增:
package com.shuyu.hibernate;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;
import org.hibernate.cfg.Configuration;
import com.shuyu.bean.BaseDepartment;
public class BaseInsert {
public static void main(String[] args) {
/** 加载配置文件*/
Configuration configuration = new Configuration().configure();
SessionFactory sessionFactory = configuration.buildSessionFactory();
/** 打开session*/
Session session = sessionFactory.openSession();
/** 利用session打开事务*/
Transaction transaction = session.beginTransaction();
long count = 0;
long startTime = System.currentTimeMillis();
for(int i=0; i<50000; i++){
BaseDepartment basedepartment = new BaseDepartment("haa-"+i);
session.save(basedepartment);
if(i%1000==0){
session.flush();
session.clear();
}
}
long endTime = System.currentTimeMillis();
float excTime = (float)(endTime - startTime)/1000;
/** 提交事务*/
transaction.commit();
/** 关闭session*/
session.close();
System.out.println("执行时间: " + excTime + "s " +"插入条数: "+count );
System.out.println("====================================================================");
System.out.println("program end !");
}
}
删:
package com.shuyu.hibernate;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;
import org.hibernate.cfg.Configuration;
public class BaseDelete {
public static void main(String[] args) {
/** 加载配置文件*/
Configuration configuration = new Configuration().configure();
SessionFactory sessionFactory = configuration.buildSessionFactory();
/** 打开session*/
Session session = sessionFactory.openSession();
/** 利用session打开事务*/
Transaction transaction = session.beginTransaction();
long count = 0 ;
long startTime = System.currentTimeMillis();
String hql = "delete BaseDepartment where department_guid "
+ " like '%ha%'";
Query query = session.createQuery(hql);
long size = query.executeUpdate();
long endTime = System.currentTimeMillis();
float excTime = (float)(endTime - startTime)/1000;
/** 提交事务*/
transaction.commit();
/** 关闭session*/
session.close();
System.out.println("执行时间: " + excTime + "s " +"删除条数: "+size );
System.out.println("====================================================================");
System.out.println("program end !");
}
}
改:
package com.shuyu.hibernate;
import org.hibernate.CacheMode;
import org.hibernate.ScrollMode;
import org.hibernate.ScrollableResults;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;
import org.hibernate.cfg.Configuration;
import com.shuyu.bean.BaseDepartment;
public class BaseUpdate {
public static void main(String[] args){
/** 加载配置文件*/
Configuration configuration = new Configuration().configure();
SessionFactory sessionFactory = configuration.buildSessionFactory();
/** 打开session*/
Session session = sessionFactory.openSession();
/** 利用session打开事务*/
Transaction transaction = session.beginTransaction();
long count = 0;
/** 方法1:使用 hibernate 批量更新表数据 */
/** 方法一
Iterator BaseDepartments = session.createQuery("from BaseDepartment b "
+ "where b.departmentIndex > -1 and rownum <=30000").iterate();
long startTime = System.currentTimeMillis();
while(BaseDepartments.hasNext()){
BaseDepartment basedepartment=(BaseDepartment)BaseDepartments.next();
basedepartment.setDepartmentIndex(basedepartment.getDepartmentIndex() + 80);
count++;
if(count%1000==0){
session.flush();
session.evict(basedepartment);
session.clear();
}
}
*/
/** 方法二 */
ScrollableResults BaseDepartments = session.createQuery("from BaseDepartment b "
+ "where b.departmentIndex > -1 and rownum <=30000")
.setCacheMode(CacheMode.IGNORE).scroll(ScrollMode.FORWARD_ONLY);
long startTime = System.currentTimeMillis();
while(BaseDepartments.next()){
BaseDepartment d = (BaseDepartment)BaseDepartments.get(0);
d.setDepartmentFullname("hhhhh"+count);
if(++count % 5000 ==0){
session.flush();
session.clear();
}
}
/**方法三 */
/**
long startTime = System.currentTimeMillis();
String hql = "update BaseDepartment set department2wname = "
+ " 'haaaaaaa' where rownum <=30000";
Query query = session.createQuery(hql);
long size = query.executeUpdate();
*/
/** 方法2:绕过 Hibernate API,直接通过JDBC API来做批量操作 */
long endTime = System.currentTimeMillis();
float excTime = (float)(endTime - startTime)/1000;
/** 提交事务*/
transaction.commit();
/** 关闭session*/
session.close();
System.out.println("执行时间: " + excTime + "s " +"更新条数: "+count );
//System.out.println("执行时间: " + excTime + "s " +"更新条数: "+size );
System.out.println("====================================================================");
System.out.println("program end !");
}
}
查:
package com.shuyu.hibernate;
import java.util.List;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;
import org.hibernate.cfg.Configuration;
public class BaseQuery {
public static void main(String[] args) {
/** 加载配置文件*/
Configuration configuration = new Configuration().configure();
SessionFactory sessionFactory = configuration.buildSessionFactory();
/** 打开session*/
Session session = sessionFactory.openSession();
/** 利用session打开事务*/
Transaction transaction = session.beginTransaction();
/** list集合的大小 */
long numSingle = 0 ;
long numMany = 0 ;
long startTime = System.currentTimeMillis();
List rst = session.createSQLQuery("select * from BASE_DEPARTMENT WHERE ROWNUM <= 10").list();
String sql = "select * from (select A.DEPARTMENT_GUID,B.EMPLOYEE_GUID,C.EMPLOYEE_ROLE_ID,D.IS_ALIASUSER,E.ID "+
"from base_department A,base_employee B,base_employee_role C,base_emp_dep D,base_role E " +
"where A.department_guid = B.employee_guid and B.employee_guid = C.EMPLOYEE_ROLE_ID and "+
"C.employee_role_id = D.EMPLOYEE_GUID and D.employee_guid = E.ID ) t where ROWNUM <= 10";
List rstMany = session.createSQLQuery(sql).list();
/**
* 不建议使用以下设定范围的方式,因为查询效率低
* List rst = session.createQuery("from BaseDepartment").setFirstResult(0).setMaxResults(10000).list();
*/
long endTime = System.currentTimeMillis();
float excTime = (float)(endTime - startTime)/1000;
/** 提交事务*/
transaction.commit();
/** 关闭session*/
session.close();
numSingle = rst.size();
numMany = rstMany.size();
System.out.println("执行时间: " + excTime + "s " + "单表记录条数 :" + numSingle);
System.out.println("执行时间: " + excTime + "s " + "多表记录条数 :" + numMany);
System.out.println("====================================================================");
System.out.println("program end !");
}
/**
* 查询数据性能测试
*/
public void queryData(){
/**
* 左连接 以A作主表
* select count(*) from
(
select A.DEPARTMENT_GUID,B.Employee_Guid,C.EMPLOYEE_ROLE_ID,D.IS_ALIASUSER,E.ID
from base_department A
left join base_employee B on A.department_guid = B.employee_guid
left join base_employee_role C on B.employee_guid = C.EMPLOYEE_ROLE_ID
left join base_emp_dep D on C.employee_role_id = D.EMPLOYEE_GUID
left join base_role E on D.employee_guid = E.ID
) t
*/
/**
* 左连接 以B作主表
* select count(*) from
(
select A.DEPARTMENT_GUID,B.Employee_Guid,C.EMPLOYEE_ROLE_ID,D.IS_ALIASUSER,E.ID
from base_employee B
left join base_department A on A.department_guid = B.employee_guid
left join base_employee_role C on B.employee_guid = C.EMPLOYEE_ROLE_ID
left join base_emp_dep D on C.employee_role_id = D.EMPLOYEE_GUID
left join base_role E on D.employee_guid = E.ID
) t
*/
}
}
JDBC:
package com.shuyu.hibernate;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class BaseJDBC {
public static void main(String[] args) {
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
String username = "system";
String password = "123456";
String url = "jdbc:oracle:thin:@192.168.1.139:1521:orcl";
Connection con = DriverManager.getConnection(url,username,password);
Statement stmt = con.createStatement();
String sql = "update BASE_DEPARTMENT set DEPARTMENT_2WNAME= "
+ "'hhhhh' where rownum <= 500000";
long startTime = System.currentTimeMillis();
int executeResult = stmt.executeUpdate(sql);
long endTime = System.currentTimeMillis();
float excTime = (float)(endTime - startTime)/1000;
System.out.println("数据更新成功!");
System.out.println("执行时间: " + excTime + "s " + "记录条数 :" + executeResult);
System.out.println("====================================================================");
System.out.println("program end !");
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
}
}
}