Hibernate进阶

Native SQL

简单查询

  1. 返回List<Object[]>
     String sql = "select s.rollNo, s.name, s.address from Student s";
 SQLQuery query = session.createSQLQuery(sql);
 List list = query.list();
 Iterator iterator = list.iterator();
 for (Iterator it = query.iterate(); it.hasNext();) {
         Object[] object = (Object[]) it.next();
         System.out.println(object[0]);
         System.out.println(object[1]);
         System.out.println(object[2]);
 }

  2. 返回List<Map>,使用setResultTransformer

     String sql = "select s.roll_no, s.name, s.address from student s";
 SQLQuery query = session.createSQLQuery(sql)
     .setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP);

 List list = query.list();
 Iterator iterator = list.iterator();
 while (iterator.hasNext()) {
     Map map = (Map) iterator.next();
     System.out.println(map.get("roll_no"));
     System.out.println(map.get("name"));
     System.out.println(map.get("address"));
 }

  3. 返回持久化对象List<Clazz>，使用addEntity

     String sql = "select s.roll_no, s.name, s.address from student s";
 SQLQuery query = session.createSQLQuery(sql)
     .addEntity(Student.class);
 List<Student> query=(List<Student>)query.list();

多表复杂查询，返回未关联对象

  1. 返回多个持久化对象List<Object[]>，使用addEntity

     String sql="select {p.*}, {b.*} from person p, book b where <complicated join>";
 SQLQuery q= session.createSQLQuery(sql)
         .addEntity("p", Person.class)
         .addEntity("b", Book.class);
 List<Object[]> peopleWithBooks=(List<Object[]>)q.list();

    =>Oject[0]:Person =>Oject[1]:Book PS: {p.*}向别名为p的这个对象注入所有属性值，{}占位符里是对象的属性,而非列名

  2. 返回托管对象List<XxxVo>，使用addEntity & setResultTransformer

     String sql="select {p.*}, {b.*} from person p, book b where <complicated join>";
 SQLQuery q= session.createSQLQuery(sql)
         .addEntity("p", Person.class)
         .addEntity("b", Book.class);
 q.setResultTransformer(Transformers.aliasToBean(PeopleWithBooksVo.class));
 List<PeopleWithBooksVo> peopleWithBooks=(List<PeopleWithBooksVo>)q.list();

    =>PeopleWithBooksVo(Person，Book)

多表查询，返回关联对象

  • 对象Person：PersonId,Name,Books
  • 对象Book：Code,Description

  • 关联记录：

    PERSONID NAME CODE DESCRIPTION
    1 BEN 1234 BOOK 1
    1 BEN 5678 BOOK 2
    2 JOHN 9012 BOOK 3

  • 返回List<Object[]>，使用addEntity & addJoin

     String sql="select {p.*}, {b.*} from person p, book b where <complicated join>";
 SQLQuery q=session.createSQLQuery(sql)
     .addEntity("p", Person.class)
     .addJoin("b", "person.books");
 List<Oject[]> list = (List<Oject[]>) q.list();

    =>List<Object[]> (Object：Person，Book)，结果如下：

     List
  |- Object[]
  |   |- Person(Ben)
  |   |   |- Book(1)
  |   |   '- Book(2)
  |   '- Book(1)
  |- Object[]
  |   |- Person(Ben)
  |   |   |- Book(1)
  |   |   '- Book(2)
  |   '- Book(2)
  '- Object[]
      |- Person(John)
      |   '- Book(3)
      '- Book(3)

  • 返回List<Clazz>，使用addEntity&addJoin & setResultTransformer

     String sql="select {p.*}, {b.*} from person p, book b where <complicated join>";
 SQLQuery q=session.createSQLQuery(sql)
     .addEntity("p", Person.class)
     .addJoin("b", "person.books");
 q.addEntity("p", Person.class);
 q.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);
 List< Person>list = (List<Person>) q.list();

    => List<Person>

    PS：The duplicate.addEntity("p", Person.class) is necessary because .setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY) operates on the last entity added.

     List
  |- Person(Ben)
  |   |- Book(1)
  |   '- Book(2)
  '- Person(John)
      '- Book(3)

使用addScalar 查询托管对象

如果不设置addScalar方法可能会报转型错误的异常

  1. Hibernate 3:
     SQLQuery q = session.createSQLQuery(queryString)
         .addScalar("UNAME",Hibernate.STRING)
         .addScalar("COM",Hibernate.STRING)
         .addScalar("COM_DATE",Hibernate.DATE) 
         .setString("id", Id).list();
  2. Hibernate4:
        q.addScalar("UNAME", StringType.INSTANCE)
     .addScalar("COM", StringType.INSTANCE)
     .addScalar("COM_DATE", DateType.INSTANCE)

使用ResultTransformer 返回托管对象

  1. 使用已有的ResultTransformer DTO：

     public class AdvancedClauseSearchResultDTO{ 
     private Date effectiveDate;
     private String status;
     …
     public void getEffectiveDate() {
         return effectiveDate;
     }
     public void setEffectiveDate(Date aDate) {
         Date now = new Date(System.currentTimeMillis());
         if (now.before(effectiveDate)) {
             this.status = "Pending";
         } else if (now.after(terminationDate)) {
             this.status = "Terminated";
         } else {
             this.status = "Active";
         }
         this.effectiveDate = aDate;
     }
 }

    使用：

     SQLQuery q  = session.createSQLQuery(sb.toString());    
 query.setResultTransformer(Transformers.aliasToBean(AdvancedClauseSearchResultDTO.class));
 return (List<AdvancedClauseSearchResultDTO>)query.list();

  2. 自定义ResultTransformer

     public class AdvancedClauseSearchResultTransformer implements ResultTransformer {
     //Use the aliasTransformer to do most of the work
     ResultTransformer aliasTransformer = Transformers.aliasToBean(AdvancedClauseSearchResultDTO.class);

     @Override
     public List transformList(List list) {
     List<AdvancedClauseSearchResultDTO> result = aliasTransformer.transformList(list);
         //for each row, set the status field
        for (AdvancedClauseSearchResultDTO dto : result) {
             String status = null;
             Date effectiveDate = dto.getEffectiveDate();
             Date terminationDate = dto.getTerminationDate();
             Date now = new Date(System.currentTimeMillis());
             if (now.before(effectiveDate)) {
                 status = "Pending";
             } else if (now.after(terminationDate)) {
                 status = "Terminated";
             } else {
                 status = "Active";
             }
             dto.setStatus(status);

             if (StringUtils.isNotEmpty(dto.getReasonForAmendment())){
                 dto.setAmended(Boolean.TRUE);
             }else{
                 dto.setAmended(Boolean.FALSE);
             }
         }
         return result;
     }

     @Override
     public Object transformTuple(Object[] os, String[] strings) {
         Object result = aliasTransformer.transformTuple(os, strings);
         return result;
     }
 }

    使用：

     SQLQuery q  = session.createSQLQuery(sb.toString());
 q.setResultTransformer(new AdvancedClauseSearchResultTransformer());
 return (List<AdvancedClauseSearchResultDTO>)query.list();

addEntity VS ResultTransformer

  1. addEntity(Class clazz) 的参数必须是被Hibernate管理的持久化bean,否则会报MappingException:Unknown entity

  2. ResultTransformer可以接受一个任意的bean,只要这个bean的属性（严格说是setXxx()中的xxx）与select结果集列名存在对应关系

QBC

参考文章：

setFetchMode

List cats = sess.createCriteria(Cat.class)
    .add( Restrictions.like("name", "Fritz%") )
    .setFetchMode("mate", FetchMode.JOIN)
    .setFetchMode("kittens", FetchMode.JOIN)
    .list();

=> List<Cat> (each Cat: mate,kittens) => This query will fetch both mate and kittens by outer join.

FetchType与FetchMode：

@OneToMany(mappedBy="item",cascade=CascadeType.ALL,fetch=FetchType.EAGER) 

@Fetch(value=FetchMode.SUBSELECT)

两者比较：

  • 两者都是设定关联对象的加载策略
  • FetchType与FetchMode是JPA标准的通用加载策略注解属性
  • Fetch是Hibernate自有加载策略属性

FetchType：

  • FetchType.LAZY: 懒加载，在访问关联对象的时候加载(即从数据库读入内存)
  • FetchType.EAGER:立刻加载，在查询主对象的时候同时加载关联对象。

FetchMode：

  • FetchMode.JOIN： 始终立刻加载，使用外连(outer join)查询的同时加载关联对象，忽略FetchType.LAZY设定。
  • FetchMode.SELECT：默认懒加载(除非设定关联属性lazy=false)，当访问每一个关联对象时加载该对象，会累计产生N+1条sql语句
  • FetchMode.SUBSELECT 默认懒加载(除非设定关联属性lazy=false),在访问第一个关联对象时加载所有的关联对象。会累计产生两条sql语句。且FetchType设定有效。

createCriteria

createCriteria定义：return sub Criteria ,so all the operation is on sub Criteria 

public Criteria createCriteria(String associationPath, int joinType) {
    return new Subcriteria( this, associationPath, joinType );
}

使用示例：

List cats = sess.createCriteria(Cat.class)
    .add(Restrictions.like("name", "F%") )
    .createCriteria("kittens")
    .add(Restrictions.like("name", "F%") )
    .list();

=> List<Cat> (Cat: no kittens) => The second createCriteria() returns a new instance of Criteria that refers to the elements of the kittens collection.

List cats = sess.createCriteria(Cat.class)
    .add(Restrictions.like("name", "F%") )
    .createCriteria("kittens",JoinType.LEFT_OUTER_JOIN)
    .add(Restrictions.like("name", "F%") )
    .list();

=> List<Cat> (Cat: kittens)

createAlias

createAlias定义：return the criteria who create sub Criteria (默认使用INNER_JOIN)

public Criteria createAlias(String associationPath, String alias, int joinType) {
    new Subcriteria( this, associationPath, alias, joinType );
    return this;
}

List cats = sess.createCriteria(Cat.class)
    .createAlias("kittens", "kt")
    .createAlias("mate", "mt")
    .add(Restrictions.eqProperty("kt.name", "mt.name") )
    .list();

=> List<Cat> (Cat: no kittens and mate) => createAlias() does not create a new instance of Criteria

List cats = sess.createCriteria(Cat.class)
    .createAlias("kittens", "kt",JoinType.LEFT_OUTER_JOIN)
    .createAlias("mate", "mt",JoinType.LEFT_OUTER_JOIN)
    .add(Restrictions.eqProperty("kt.name", "mt.name") )
    .list();

=> List<Cat> (Cat: kittens and mate)

Join Restriction

类似查询：select * from A left join B on A.xx=B.xx and B.yy=zz where ...

DetachedCriteria detachedCriteria=DetachedCriteria.forClass(SysMenu.class)
                      .addOrder(Order.asc("parent" ))
                      .addOrder(Order.asc("menuOrder" ))
                      //使用createCriteria或createAlias都可
                      .createAlias("sysRoleMenus", "rm", JoinType.LEFT_OUTER_JOIN,Restrictions.eq("sysRole.id", roleId));

=> List<SysMenu> (SysMenu : no sysRoleMenus)

（HQL可使用with关键字实现）

exist - subquery

Sample1:

select A.*
FROM AETABLE A
where not exists
(
    select entryid
    FROM AETABLE B
    where B.classpk = A.classpk
    and B.userid = A.userid
    and B.modifiedDate > A.modifiedDate
)and userid = 10146

Assuming property / class names match column / table names above:

DetachedCriteria subquery = DetachedCriteria.forClass(AETable.class, "b")
 .add(Property.forName("b.classpk").eqProperty("a.classpk"))
 .add(Property.forName("b.userid").eqProperty("a.userid"))
 .add(Property.forName("b.modifiedDate").gtProperty("a.modifiedDate"));

Criteria criteria = session.createCriteria(AETable.class, "a")
 .add(Property.forName("userid").eq(new Integer(10146)))
 .add(Subqueries.notExists(subquery);

If the entryid is not the primary key, need to add projection.

DetachedCriteria subquery = DetachedCriteria.forClass(AETable.class, "b")
 .add(Property.forName("b.classpk").eqProperty("a.classpk"))
 .add(Property.forName("b.userid").eqProperty("a.userid"))
 .add(Property.forName("b.modifiedDate").gtProperty("a.modifiedDate"))
 .setProjection(Projections.property("entryId"));  // Additional projection property

Criteria criteria = session.createCriteria(AETable.class, "a")
 .add(Property.forName("userid").eq(new Integer(10146)))
 .add(Subqueries.notExists(subquery);

Sample2:

SELECT *
 FROM PIZZA_ORDER
 WHERE EXISTS (SELECT 1
                 FROM PIZZA
                 WHERE PIZZA.pizza_size_id = 1 
                 AND PIZZA.pizza_order_id = PIZZA_ORDER.pizza_order_id)

DetachedCriteria sizeCriteria = DetachedCriteria.forClass(Pizza.class,"pizza")
 .add("pizza_size_id",1)
 .add(Property.forName("pizza.pizza_order_id").eqProperty("pizzaOrder.pizza_order_id"));

Criteria criteria = Criteria.forClass(PizzaOrder.class,"pizzaOrder")
 .add(Subqueries.exists(
    sizeCriteria.setProjection(Projections.property("pizza.id"))
));

List<pizzaOrder> ordersWithOneSmallPizza = criteria.list();

Sample3:

select *
 from shuttle_station
 where id in (
    select station_id 
    from shuttle_route_schedule rs left join shuttle_route 
    where active=1 and expireTime is null
)

DetachedCriteria subCriteria=DetachedCriteria.forClass(ShuttleRouteSchedule.class,"rs")
    .add(Restrictions.eq("active", true))
    .add(Restrictions.isNull("expireTime"))
    .setProjection(Projections.distinct(Projections.property("station.id")));

DetachedCriteria detachedCriteria=DetachedCriteria.forClass(ShuttleStation.class)
    .createAlias("stationShifts","ea", JoinType.LEFT_OUTER_JOIN)
    .add(Subqueries.propertyIn("id", subCriteria));

通用查询封装示例

PS: 使用过setProjection获取信息后，重置为null，返回的不再是RootEntity~！ 参考文章: Sorting And Pagination With Hibernate Criteria - How It Can Go Wrong With Joins

@Override
@SuppressWarnings("unchecked")
public List<T> list(DetachedCriteria detachedCriteria,boolean byPage)
{
    Criteria criteria=null;
    if(detachedCriteria!=null)
        criteria=detachedCriteria.getExecutableCriteria(getSession());
    else
        criteria=this.getSession().createCriteria(this.getEntityClass());

    if (byPage && PaginationUtils.exist()){
        ResultTransformer resultTransformer=((CriteriaImpl)criteria).getResultTransformer();

        Object total = criteria.setProjection(Projections.rowCount()).uniqueResult(); 
        //System.out.println(total.toString());
        Pagination p=PaginationUtils.getPagination();
        p.setTotal(Integer.valueOf(total.toString()));

        criteria.setProjection(null); 
        criteria.setResultTransformer(resultTransformer);

        criteria.setFirstResult(p.getStart());  
        criteria.setMaxResults(p.getLimit()); 
        if (p.getSorter() != null && p.getSorter().length()!=0) {
            if("DESC".equalsIgnoreCase(PaginationUtils.getOrder()))
                criteria.addOrder(Order.desc(PaginationUtils.getSorter()));
            else
                criteria.addOrder(Order.asc(PaginationUtils.getSorter()));
        }
    }
    return criteria.list(); 
}

@Override
public List<?> listByHql(String hql, Object... params) {
    //this.getEntityClass();
    if (PaginationUtils.exist() && PaginationUtils.getSorter() != null && PaginationUtils.getSorter().length()!=0) {
        hql += " order by " + PaginationUtils.getSorter() + " "
                + PaginationUtils.getOrder();
    }
    Query query = createHQLQuery(hql, params);

    if (PaginationUtils.exist()) {
        Query countQuery = createHQLQuery("select count(*) "
                + removeSelect(hql), params);
        int total = Integer.valueOf(countQuery.uniqueResult().toString());
        PaginationUtils.setTotal(total);

        query.setFirstResult(PaginationUtils.getStart()).setMaxResults(
                PaginationUtils.getLimit());
    }
    return query.list();
}

TableGenerator

参考文章：探索 Hibernate 新 TableGenerator 机制

TableGenerator 注解和类

@TableGenerator 注解是 JPA 规范中的注解，用于确定 TABLE 主键生成器的各个参数，具体的功能要由 JPA 提供者来实现

Hibernate 中实现该注解的TableGenerator 类有两个：

  1. 原有的 TableGenerator，类名为 org.hibernate.id.TableGenerator，这是默认的 TableGenerator
  2. 新 TableGenerator，指的是 org.hibernate.id.enhanced.TableGenerator

使用JPA注解：@TableGenerator

@Id
@GeneratedValue(strategy=GenerationType.TABLE,generator="teacherID")
@TableGenerator(name="teacherID",
        table="teacherID_DB",
        pkColumnName="key_value",
        pkColumnValue="pk_value",
        valueColumnName="teacher",
        allocationSize=1)     //注意 initialValue 参数无效
public int getId() {
    return id;
}

PS： 默认使用旧的TableGenerator类，若要使用新的，需配置：

<property name="hibernate.id.new_generator_mappings" value="true"/>

使用Hibernate注解：@GenericGenerator

新 TableGenerator 的一些功能不在 JPA 中，因此不能使用 JPA 的 @TableGenerator 注解，而是要使用 Hibernate 自身的 @GenericGenerator 注解

新 TableGenerator 配置参数表：

序号 参数名 默认值 含义
1 table_name hibernate_sequence 辅助表的表名
2 value_column_name next_val 存放序列值的列名
3 segment_column_name sequence_name 存放序列名的列名
4 segment_value default 序列名
5 segment_value_length 255 序列名所在列数据类型长度
6 initial_value 1 指定序列的初始值
7 increment_size 1 指定序列默认的递增量
8 optimizer 依 increment_size 的取值而定 指定序列的优化器

TableGenerator Parameters

@GenericGenerator( name="id_gen", strategy="enhanced-table",
parameters = {
       @Parameter( name = "table_name", value = "enhanced_gen"),
       @Parameter( name ="value_column_name", value = "next"),
       @Parameter( name = "segment_column_name",value = "segment_name"),
       @Parameter( name = "segment_value", value = "emp_seq"),
       @Parameter( name = "increment_size", value = "10"),
       @Parameter( name = "optimizer",value = "pooled-lo")
})
@Id
@GeneratedValue(generator="id_gen")
public int getId() {
    return id;
}

PS： 注意配置使用新的TableGenerator

<property name="hibernate.id.new_generator_mappings" value="true"/>

Hibernate的Optimizer优化器

Optimizer 名字及实现类：

Optimizer 名字 类名 特点
none NoopOptimizer.class 没有任何优化，每次主键生成都需要访问数据库
hilo HiLoOptimizer.class hilo算法实现的主键生成机制，数据库中的值是 bucket 的序号
legacy-hilo LegacyHiLoAlgorithmOptimizer.class 旧 hilo 算法
pooled PooledOptimizer.class 也使用 hilo 算法，不同之处在于 bucket 内部数值保存在数据库中
pooled-lo PooledLoOptimizer.class 算法与 pooled 完全相同，但保存在数据库中的值不同于 pooled

选定优化器的过程： Optimizer

自定义TableGenerator

  1. 自定义TableGenerator：MyFormatTableGenerator （implements org.hibernate.id.enhanced.TableGenerator）
  2. 自定义Optimizer：MyPooledOptimizer （extends OptimizerSupport implements InitialValueAwareOptimizer）
  3. 使用：
     @Id
 @Column(name = "req_id", unique = true, nullable = false, length = 10)
 @GeneratedValue(strategy = GenerationType. TABLE, generator = "myFormatTableGenerator" )
 @GenericGenerator(name = "myFormatTableGenerator", strategy = "com.cj.support.hibernate.MyFormatTableGenerator" , parameters = {
  @Parameter(name = "format", value = "R%2$ty-%1$05d"),
  @Parameter(name = TableGenerator.TABLE_PARAM, value = "tb_generator"),
  @Parameter(name = TableGenerator.SEGMENT_COLUMN_PARAM, value = "gen_name"),
  @Parameter(name = TableGenerator.SEGMENT_VALUE_PARAM, value = "Request_PK"),
  @Parameter(name = TableGenerator.VALUE_COLUMN_PARAM, value = "gen_value"),
  @Parameter(name = TableGenerator.INITIAL_PARAM, value = "1"),
  @Parameter(name = TableGenerator.INCREMENT_PARAM, value = "20")
 })
 public String getReqId(){
     return this. reqId;
 }

Spring中配置Hibernate

<context:property-placeholder location="classpath:jdbc-prod.properties"/>
<bean id="dataSource" destroy-method="close" class="org.apache.commons.dbcp.BasicDataSource">
    <property name="driverClassName" value="${jdbc.driverClassName}"/>
    <property name="url" value="${jdbc.url}"/>
    <property name="username" value="${jdbc.username}"/>
    <property name="password" value="${jdbc.password}"/>

    <property name="initialSize" value="1"/>
    <property name="minIdle" value="2"/>
    <property name="maxActive" value="200"/>
    <property name="maxIdle" value="30"/>
    <property name="maxWait" value="1000"/>
    <property name="removeAbandoned" value="true"/>
    <!-- <property name="logAbandoned" value="true"/>   -->
    <property name="validationQuery" value="select 1 from dual"/>
    <property name="testOnBorrow" value="true"/>
</bean>

<bean id="sessionFactory" class="org.springframework.orm.hibernate4.LocalSessionFactoryBean">
    <property name="dataSource" ref="dataSource" />
    <property name="hibernateProperties">
        <props>
            <prop key="hibernate.dialect">${hibernate.dialect}</prop>
            <prop key="hibernate.id.new_generator_mappings">true</prop>
            <prop key="hibernate.show_sql">${hibernate.show.sql}</prop>
            <prop key="hibernate.format_sql">${hibernate.format.sql}</prop>
            <!-- <prop key="javax.persistence.validation.mode">none</prop> -->
        </props>
    </property>

    <property name="packagesToScan">
        <list>
            <value>com.golf.entity</value>
        </list>
    </property>
</bean>

<!-- 声明式事务 -->
<bean id="txManager" class="org.springframework.orm.hibernate4.HibernateTransactionManager">
    <property name="sessionFactory" ref="sessionFactory"/>
</bean>
<tx:advice id="txAdvice" transaction-manager="txManager" >
    <tx:attributes>
        <tx:method name="list*" propagation="REQUIRED" read-only="true"/>
        <tx:method name="get*" propagation="REQUIRED" read-only="true"/>
        <tx:method name="test*" propagation="REQUIRED" read-only="true"  />
        <tx:method name="sendMail" propagation="REQUIRED" read-only="true" no-rollback-for="Exception"/>
        <tx:method name="*"/>
    </tx:attributes>
</tx:advice>
 <aop:config>
    <aop:pointcut expression="execution(* com.cj.service.*.*(..))" id="mySearchService"/>
    <aop:advisor advice-ref="txAdvice" pointcut-ref="mySearchService"/>
</aop:config>