NHibernate HQL 函数例子

 

HQL 函数 参考

假设有一个数据库表Animal
结构如下



<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
    assembly="NHibernate.Test"
    namespace="NHibernate.Test.Hql"
    default-access="field.camelcase-underscore"
    default-lazy="true">
    
    <import class="SummaryItem"/>
    
    <class name="Animal">
        <id name ="Id">
            <generator class="native" />
        </id>
        <property name="Description"/>
        <property name="BodyWeight" type="float"/>
        <joined-subclass name="Human">
            <key column="animalId"/>
            <component name="Name">
                <property name="First" column="name_first"/>
                <property name="Initial" column="name_initial"/>
                <property name="Last" column="name_last"/>
            </component>
            <property name="NickName"/>
            <property name="Birthdate" type="Date"/>
        </joined-subclass>
    </class>
</hibernate-mapping>



另有一个类继承自Animal即人类：



    public class Human: Animal
    {
        private Name _name;
        public virtual Name Name
        {
            get { return _name; }
            set { _name = value; }
        }

        private String _nickName;
        public virtual String NickName
        {
            get { return _nickName; }
            set { _nickName = value; }
        }

        private DateTime _birthdate;
        public virtual DateTime Birthdate
        {
            get { return _birthdate; }
            set { _birthdate = value; }
        }

    }



下面是一组HQL的例子，代码中的s都代表Nhibernate的Session

统计个数
1.用select方法统计(去除了重复)
s.CreateQuery("select count(distinct a.id) from Animal a").UniqueResult()
或
s.CreateQuery("select count(*) from Animal").UniqueResult();
2.用where 方法统计
s.CreateQuery("select count(a.id) from Animal a having count(a.id)>1").UniqueResult();

统计平均数
1.用select方法统计
s.CreateQuery("select avg(a.BodyWeight) from Animal a").UniqueResult();
2.用where 方法统计
s.CreateQuery("select avg(a.BodyWeight) from Animal a having avg(a.BodyWeight)>0").UniqueResult();

取 最大/最小值/合计 的例子和上面的例子类似，只是avg可以换成 max,min,sum

返回一个指定的类
假设你有一个类：SummaryItem即统计类,你要返回一个列表的统计结果，你可以
s.CreateQuery("select distinct new SummaryItem(a.Description, sum(BodyWeight)) from Animal a").List<SummaryItem>()

在HQL中进行计算
s.CreateQuery("select a.Id, sum(BodyWeight)/avg(BodyWeight) from Animal a group by a.Id having sum(BodyWeight)>0").List()

很难得HQL还有SubString Locate Trim Length Bit_length Coalesce Upper等字符计算功能,你可以

SubString
hql = "select substring(a.Description, 3) from Animal a";
或者
hql = "from Animal a where substring(a.Description, 2, 3) = 'bcd'";
或
hql = "from Animal a where substring(a.Description, 2, 3) = ?";
s.CreateQuery(hql).SetParameter(0, "bcd").UniqueResult();

Locate
hql = "select locate('bc', a.Description, 2) from Animal a";
或
hql = "from Animal a where locate('bc', a.Description) = 2";

Trim
hql = "select trim(a.Description) from Animal a where a.Description=' def'";
或
hql = "from Animal a where trim(a.Description) = 'abc'";
或
hql = "from Animal a where trim(leading from a.Description) = 'def'";
或
hql = "from Animal a where trim(both from a.Description) = 'abc'";

Length
hql = "select length(a.Description) from Animal a where a.Description = '1234'";
hql = "from Animal a where length(a.Description) = 5";

Bit_length
hql = "select bit_length(a.Description) from Animal a";
hql = "from Animal a where bit_length(a.Description) = 24";

Coalesce
hql = "select coalesce(h.NickName, h.Name.First, h.Name.Last) from Human h";
hql = "from Human h where coalesce(h.NickName, h.Name.First, h.Name.Last) = 'max'";

Upper,Lower例子类似

类型转换的例子

Cast
hql = "select cast(a.BodyWeight as Double) from Animal a";
hql = "select cast(7+123-5*a.BodyWeight as Double) from Animal a";
hql = "from Animal a where cast(a.BodyWeight as string) like '1.%'";

str
hql = "select str(a.BodyWeight) from Animal a";
hql = "from Animal a where str(123) = '123'";

很难得HQL还有Nullif等判断功能,你可以

Nullif
hql1 = "select nullif(h.NickName, '1e1') from Human h";
hql2 = "from Human h where not(nullif(h.NickName, '1e1') is null)";

很难得HQL还有Abs Mod Sqrt等数学函数功能,你可以

Abs
hql = "select abs(a.BodyWeight*-1) from Animal a";
hql = "from Animal a where abs(a.BodyWeight*-1)>0";
hql = "select abs(a.BodyWeight*-1) from Animal a group by abs(a.BodyWeight*-1) having abs(a.BodyWeight*-1)>0";

Mod
hql = "select mod(cast(a.BodyWeight as int), 3) from Animal a";
hql = "from Animal a where mod(20, 3) = 2";
hql = "from Animal a where mod(cast(a.BodyWeight as int), 4)=0";

和时间相关的例子

hql = "select second(current_timestamp()), minute(current_timestamp()), hour(current_timestamp()) from Animal";
hql = "select day(h.Birthdate), month(h.Birthdate), year(h.Birthdate) from Human h";