hibernate查询的使用的点滴积累

/**
  * 前台查询酒店的级别，设备，类型
  *
  * @param propertyName
  * @return
  */
 @SuppressWarnings("unchecked")
 public Map<String, Integer> findByHotelInfo(final String propertyName) {
  final String queryString = "select new Map(? as hotelInfoName,count(?) as hotelInfoCount ) from Hotel group by ?";
  return (Map<String, Integer>) this.getHibernateTemplate().execute(
    new HibernateCallback() {
     @Override
     public Object doInHibernate(Session session)
       throws HibernateException, SQLException {
      Query query = session.createQuery(queryString);
      query.setString(0, propertyName);
      query.setString(1, propertyName);
      query.setString(2, propertyName);
      return query.uniqueResult();
     }
    });
 }

 

 

// 价格部分，是否有效价格的过滤（ordinal最高）

exists函数的使用如下：

  sb.append(" and exists (\r\n");
  sb.append(" select s2.roomTypeId, s2.statusDate, max(p2.ordinal)" +
    " from RoomStatus s2, RoomTypePrice p2 \r\n" +
    " where s2.roomTypeId = p2.roomTypeId and s2.statusDate between p2.from and p2.to   \r\n" +
    "  and s2.roomTypeId = s.roomTypeId and s2.statusDate = s.statusDate \r\n" +
    "  and s.statusDate >= :checkInDate and s.statusDate < :checkOutDate\r\n" +
    " group by s.roomTypeId, s.statusDate\r\n" +
    " having p.ordinal = max(p2.ordinal))\r\n");
 
  // 价格部分，最高最低区间
  if (condition.getBreakfirst())
   sb.append(" and ((0 not in elements(p.prepayBreakfastTypes)" +
     " or 0 not in elements(p.cashBreakfastTypes))" +
     " or (ri.cashBreakfast <> 0 or ri.prepayBreakfast  <>0 ))\n");

 

 

 

 

在逻辑层，显示层那里都可以看到随手写的查询语句？这样的做法极度的破坏了分层的架构，无论如何的XP也应该遵循一定的管理与规范，那么统一管理查询语句的重要性就凸现了。

      统一管理查询语句有何优点？

      1、保持系统的分层架构，管理语句是持久层的责任，由它自己管理是最适合不过。松散的耦合总是我们向往的目标。

      2、统一管理方便修改，可以减小人手修改带来的低级错误。

     

      OK，接下来要考虑如何管理这些语句来了。

      1、配置文件管理

      在hibernate的mapping文件内使用<query>     

<query name='findUserById'>
     from User eo where eo.id = ?
</query>

      <query>里面就是要使用的hql语句 属性name就是语句保存在容器里的别名。

    

      在hibernate的mapping文件内使用<sql-query>    

<sql-query name="findUserByName">
     <return alias="user" class="hibernate.entity.User"/>
               SELECT user.id AS {user.id},
                            user.name AS {user.name}
               FROM t_user user WHERE user.name = ?
</sql-query>

     <sql-query>里面的语句必须是sql语句，属性name就是语句保存在容器里的别名，<reruen>里面的东东标明了返回对象的类型与别名，别名主要用于对应sql里面{}的内容。

   

      写好mapping文件后当然要告诉hibernate将这些语句加入到容器里面咯，配置方法有很多种，这里只列了使用spring结合hibernate的配置方式，在SessionFactoryBean的配置里面加入     

<property name="mappingLocations">
       <list>
           <value>
                classpath:hbm/name-query.hbm.xml
           </value>
       </list>
</property>

    <sql-query>的使用比较复杂，所以不是遇到复杂的跨表查询时，不推荐使用。

     

 

     2、标签管理

       一般的习惯都是使用@NamedQueries将与自己相关的语句统一在实体里面，如查询User的语句都是放到User对象里面

@Entity
@Table(name = "t_user")
@Cache(usage = CacheConcurrencyStrategy.READ_WRITE) @NamedQueries( {
       @NamedQuery(name = "User.findById",
               query = "FROM User eo where eo.id=? ") })
public class User implements java.io.Serializable {

                private int id;

                private String name;

     使用标签管理可以比较好的分类查询语句，也不用搞麻烦的配置文件，虽然说修改配置文件的查询语句可以不用重新编译就能生效，但是查询语句修改也不会十分频繁，所以标签管理是一个不错的选择。

 

 

      或者以上的方式都不喜欢，你甚至可以自己写统一管理查询语句的文件，手工注入到你的系统容器内，在DAO层写一套自己的NamQuery也是可以的。萝卜青菜，各有所爱，每个人都会有自己习惯的管理方式，但无论方式是什么，起码有这种想法都是好的。

 

 

 

 

 

 

 

 

Native sql 本地sql在Hibernate中

http://www.hibernate.org/hib_docs/v3/reference/en/html/querysql.html

 

使用SQLQuery

 

1.      标量查询scalar query

如

sess.CreateSQLQuery("SELECT * FROM CATS")

 .AddScalar("ID", NHibernateUtil.Int64)

 .AddScalar("NAME", NHibernateUtil.String)

 .AddScalar("BIRTHDATE", NHibernateUtil.Date)

返回List的Object[]，每个Object有上述三个字段组成。

2.      Entity query

如

sess.CreateSQLQuery("SELECT * FROM CATS").AddEntity(typeof(Cat));

sess.CreateSQLQuery("SELECT ID, NAME, BIRTHDATE FROM CATS").AddEntity(typeof(Cat));

返回List的Cat[]。

 

 

3.      处理association和collections

如

sess.CreateSQLQuery("SELECT c.*, m.*  FROM CATS c, CATS m WHERE c.MOTHER_ID = c.ID")

 .AddEntity("cat", typeof(Cat))

 .AddEntity("mother", typeof(Cat))

每行将返回两个Cat对象：一个Cat，一个Cat的mother。

但是上面的代码会造成列名的冲突问题。

因此：

sess.CreateSQLQuery("SELECT {cat.*}, {mother.*}  FROM CATS c, CATS m WHERE c.MOTHER_ID = c.ID")

 .AddEntity("cat", typeof(Cat))

 .AddEntity("mother", typeof(Cat))

 

4.      alias和property引用

Description	Syntax	Example
A simple property	{[aliasname].[propertyname]}	A_NAME as {item.Name}
A composite property	{[aliasname].[componentname].[propertyname]}	CURRENCY as {item.Amount.Currency}, VALUE as {item.Amount.Value}
Discriminator of an entity	{[aliasname].class}	DISC as {item.class}
All properties of an entity	{[aliasname].*}	{item.*}
A collection key	{[aliasname].key}	ORGID as {coll.key}
The id of an collection	{[aliasname].id}	EMPID as {coll.id}
The element of an collection	{[aliasname].element}	XID as {coll.element}
property of the element in the collection	{[aliasname].element.[propertyname]}	NAME as {coll.element.Name}
All properties of the element in the collection	{[aliasname].element.*}	{coll.element.*}
All properties of the the collection	{[aliasname].*}	{coll.*}

 

 

5.      得到non-managed entities

处理继承

native sql查询的实体是一个继承结构中的一部分的话，就必须包括进来其基类和子类的属性。

6.      参数

Query query = sess.CreateSQLQuery("SELECT * FROM CATS WHERE NAME like ?").AddEntity(typeof(Cat));

IList pusList = query.SetString(0, "Pus%").List();

query = sess.createSQLQuery("SELECT * FROM CATS WHERE NAME like :name").AddEntity(typeof(Cat));

IList pusList = query.SetString("name", "Pus%").List();          

Named sql queries

1.      Scalar query

<sql-query name="mySqlQuery">

    <return-scalar column="name" type="String"/>

    <return-scalar column="age" type="Int64"/>

    SELECT p.NAME AS name,

           p.AGE AS age,

    FROM PERSON p WHERE p.NAME LIKE 'Hiber%'

</sql-query>

2.      Entity query

<sql-query name="persons">

    <return alias="person" class="eg.Person"/>

    SELECT person.NAME AS {person.Name},

           person.AGE AS {person.Age},

           person.SEX AS {person.Sex}

    FROM PERSON person

    WHERE person.NAME LIKE :namePattern

</sql-query>

IList people = sess.GetNamedQuery("persons")

    .SetString("namePattern", namePattern)

    .SetMaxResults(50)

    .List();

return的含义：这个查询返回一个alias的实体。

3.      return-join和load-collection

<sql-query name="personsWith">

    <return alias="person" class="eg.Person"/>

    <return-join alias="address" property="person.MailingAddress"/>

    SELECT person.NAME AS {person.Name},

           person.AGE AS {person.Age},

           person.SEX AS {person.Sex},

           adddress.STREET AS {address.Street},

           adddress.CITY AS {address.City},

           adddress.STATE AS {address.State},

           adddress.ZIP AS {address.Zip}

    FROM PERSON person

    JOIN ADDRESS adddress

        ON person.ID = address.PERSON_ID AND address.TYPE='MAILING'

    WHERE person.NAME LIKE :namePattern

</sql-query>

return-join和load-collection都可以参照第一部分中的assocation和collection。

4.      利用resultset扩展关于结果集映射（参考上面的return和return-join）的信息

<resultset name="personAddress">

    <return alias="person" class="eg.Person"/>

    <return-join alias="address" property="person.MailingAddress"/>

</resultset>

<sql-query name="personsWith" resultset-ref="personAddress">

    SELECT person.NAME AS {person.Name},

           person.AGE AS {person.Age},

           person.SEX AS {person.Sex},

           adddress.STREET AS {address.Street},

           adddress.CITY AS {address.City},

           adddress.STATE AS {address.State},

           adddress.ZIP AS {address.Zip}

    FROM PERSON person

    JOIN ADDRESS adddress

        ON person.ID = address.PERSON_ID AND address.TYPE='MAILING'

    WHERE person.NAME LIKE :namePattern

</sql-query>

用程序的方式处理上面的配置信息：

IList cats = sess.CreateSQLQuery(

        "select {cat.*}, {kitten.*} from cats cat, cats kitten where kitten.mother = cat.id"

    )

    .SetResultSetMapping("catAndKitten")

    .List();

5.      显示指定结果集中的column的显示名字（return-property）

<sql-query name="mySqlQuery">

    <return alias="person" class="eg.Person">

        <return-property name="Name" column="myName"/>

        <return-property name="Age" column="myAge"/>

        <return-property name="Sex" column="mySex"/>

    </return>

    SELECT person.NAME AS myName,

           person.AGE AS myAge,

           person.SEX AS mySex,

    FROM PERSON person WHERE person.NAME LIKE :name

</sql-query>

将多column映射为一个名字

<sql-query name="organizationCurrentEmployments">

    <return alias="emp" class="Employment">

        <return-property name="Salary">

            <return-column name="VALUE"/>

            <return-column name="CURRENCY"/>

        </return-property>

        <return-property name="EndDate" column="myEndDate"/>

    </return>

        SELECT EMPLOYEE AS {emp.Employee}, EMPLOYER AS {emp.Employer},

        STARTDATEAS {emp.StartDate}, ENDDATEAS {emp.EndDate},

        REGIONCODE as {emp.RegionCode}, EIDAS {emp.Id}, VALUE, CURRENCY

        FROM EMPLOYMENT

        WHERE EMPLOYER = :id AND ENDDATE IS NULL

        ORDER BY STARTDATE ASC

</sql-query>

6.      stored procedures

<sql-query name="selectAllEmployments_SP">

    <return alias="emp" class="Employment">

        <return-property name="employee" column="EMPLOYEE"/>

        <return-property name="employer" column="EMPLOYER"/>

        <return-property name="startDate" column="STARTDATE"/>

        <return-property name="endDate" column="ENDDATE"/>

        <return-property name="regionCode" column="REGIONCODE"/>

        <return-property name="id" column="EID"/>

        <return-property name="salary">

            <return-column name="VALUE"/>

            <return-column name="CURRENCY"/>

        </return-property>

    </return>

    exec selectAllEmployments

</sql-query>

stored procedures的使用有一些限制，如果不遵守，就不能在Hibernate中使用stored procedures，而只能用session.connection()。限制根据db的不同而不同，因为不同的db中stored procedures有差别。

Stored procedure queries can't be paged with setFirstResult()/setMaxResults().

Recommended call form is standard SQL92: { ? = call functionName(<parameters>) } or { ? = call procedureName(<parameters>}. Native call syntax is not supported.

For Oracle the following rules apply:

·         A function must return a result set. The first parameter of a procedure must be an OUT that returns a result set. This is done by using a SYS_REFCURSOR type in Oracle 9 or 10. In Oracle you need to define a REF CURSOR type, see Oracle literature.

For Sybase or MS SQL server the following rules apply:

·         The procedure must return a result set. Note that since these servers can/will return multiple result sets and update counts, Hibernate will iterate the results and take the first result that is a result set as its return value. Everything else will be discarded.

·         If you can enable SET NOCOUNT ON in your procedure it will probably be more efficient, but this is not a requirement.

Custom sql for create, update ,delete, loading