(一)HQL
HQL:Hibernate Qusery Language,如果你已经熟悉它,就会发现它跟SQL非常相像。不过你不要被表面的假象迷惑,HQL是面向对象的(OO,用生命的眼光看待每一个对象,他们是如此鲜活)。如果你对JAVA和SQL语句有一定了解的话,那么HQL对你简直易如反掌,你完全可以利用在公车上的时间掌握它。以下从几个方面进行慢慢深入:1。大小些敏感大家知道SQL-92 Query是对大小写不敏感的,但是在HQL(前面提到它是OO的)中对对象类的名称和属性确实大小写敏感的(符合java编程语法)。
HQL子句本身大小写无关,但是其中出现的类名和属性名必须注意大小写区分如:sElectcat.name from Cat as cat和selectcat.name from Cat as cat是一样的但是:sElectcat.name fromCATas cat和select cat.name fromCatas cat确实不一样的。2。from语句最简单的:from eg.Cat它只是简单的返回所有eg.Cat的实例,通常我们此时会为eg.Cat其个别名,因为在query的其余部分可能会用到(参看上边关于大小写敏感时的例子情形),如:from eg.Cat as cat这里as可以省略。
上边只是单表查询,多表的情况如下写法:from eg.Cat, eg.Dog
from eg.Cat as cat, eg.Dog as dog
3。join相关(inner) join
left (outer) join
right (outer) join
full join
HQL同样对SQL中的这些特性支持下面插播一个小话题,关于上边的那些特性,我一直都没怎么用,今天既然说到这里,就想把上边的几个特性的用法说一下,也算对自己的一个补充:
假设有两个表:部门、员工,下面列举一些数据:员工(Employee):IDName DepNo
001 Jplateau01
002 Jony01
003 Camel 02
部门(Department):IDName
01研发部02营销部在Hibernate中我们操纵的都是对象,所以我们操纵的是部门类和员工
1).(inner) join
select employee.ID as id1,employee.Name as name1,
department.ID as id2,department.Name as name2 from Employee as employee
join Department as department on employee.DepNo=department.ID (注意到条件语句我用on没有用where)那么执行结果是什么呢?id1 name1 id2 name2
++++++++++++++++++++++++++++++++++++++
001 Jplateau 01研发部002 Jony 01研发部2).left (outer) join
select employee.ID as id1,employee.Name as name1,department.ID as id2,department.Name
as name2 from Employee as employee left join Department as department on employee.DepNo=
department.ID那么执行结果又该是什么呢?id1 name1 id2 name2
++++++++++++++++++++++++++++++++++++++
001 Jplateau 01研发部002 Jony 01研发部003 Camel null null
{就是说此时我要已第一个表的记录多少为准,第二个表中没有相应纪录的时候填充null}
3). right (outer) join
select employee.ID as id1,employee.Name as name1,department.ID as id2,department.Name
as name2 from Employee as employee right join Department as department on employee.DepNo=
department.ID那么执行结果又该是什么呢?id1 name1 id2 name2
++++++++++++++++++++++++++++++++++++++
001 Jplateau 01研发部002 Jony 01研发部null null 02营销部{就是说此时我要已第二个表的记录多少为准,第一个表中没有相应纪录的时候填充null}
4。select语句就是要确定你要从查询中返回哪些对象或者哪些对象的属性。写几个例子吧:select employee form Employee as employee
select employee form Employee as employee where employee.Name like 'J%'
select employee.Name form Employee as employee where employee.Name like 'J%'
select employee.ID as id1,employee.Name as name1,department.ID as id2,department.Name
as name2 from Employee as employee right join Department as department on employee.DepNo=
department.ID
selectelements(employee.Name) from Employee as employee(不明白elements到底是做什么用的?望给于说明)等等
5。数学函数JDO目前好像还不支持此类特性。avg(...), sum(...), min(...), max(...)
count(*)
count(...), count(distinct ...), count(all...)其用法和SQL基本相同select distinct employee.name from Employee as employee
select count(distinct employee.name),count(employee) from Employee as employee
6。polymorphism (暂时不知道如何解释?)
from com.test.Animal as animal不光得到所有Animal得实例,而且可以得到所有Animal的子类(如果我们定义了一个子类Cat)一个比较极端的例子from java.lang.Object as o可以得到所有持久类的实例7。where语句定义查询语句的条件,举几个例子吧:from Employee as employee where employee.Name='Jplateau'
from Employee as employee where employee.Name like 'J%'
from Employee as employee where employee.Name like '%u'在where语句中“=”不光可以比较对象的属性,也可以比较对象,如:select animal from com.test.Animal as animal where animal.name=dog
8。表达式在SQL语句中大部分的表达式在HQL中都可以使用:mathematical operators +, -, *, /
binary comparison operators =, >=, <=, <>, !=, like
logical operations and, or, not
string concatenation ||
SQL scalar functions like upper() and lower()
Parentheses ( ) indicate grouping
in, between, is null
JDBC IN parameters ?
named parameters :name, :start_date, :x1(这种应该是另一种"?"的变通解决方法)SQL literals 'foo', 69, '1970-01-01 10:00:01.0'
Java public static final constants eg.Color.TABBY其他不必解释了,在这里我只想对查询中的参数问题说明一下:大家知道在SQL中进行传递参数进行查询的时候,我们通常用PreparedStatement,在语句中写一大堆的“?”,在hql中也可以用这种方法,如:List mates = sess.find(
"select employee.name from Employee as employee " +
"where employee.Name=? ",
name,
Hibernate.STRING
);
(说明:上面利用Session里的find方法,在hibernate的api Session中重载了很多find方法,它可以满足你多种形式的查询)上边是一个参数的情形,这种情况下紧接着引入参数和定义参数的类型,当为多个参数,调用另一个find方法,它的后两个参数都是数组的形式。还有另外一种方法来解决上边的问题,JDO也有这样的方法,不过和hibernate的表现形式上有差别,但他们两个骨子里却是一样的,如:Query q = sess.createQuery("select employee.name from Employee as employee where employee.Name=:name");
q.setString("name", "Jplateau");
//当有多个参数的时候在此逐一定义Iterator employees = q.iterate();
9。order语句和sql语句没什么差别,如:select employee.name from Employee as employee where employee.Name like 'J%' order by employee.ID desc (或者asc)
10。group by语句同样和sql语句没什么差别,如:select employee.name,employee.DepNo from Employee as employee group by employee.DepNo
select foo.id, avg( elements(foo.names) ), max( indices(foo.names) ) from eg.Foo foo group by foo.id
{Note: You may use the elements and indices constructs inside a select clause, even on databases with no subselects.}谁帮我解释一下上边两句,谢过!11。子查询hibernate同样支持子查询,写几个例子:from eg.Cat as fatcat where fatcat.weight > ( select avg(cat.weight) from eg.DomesticCat cat )(二)条件查询Criteria Query。数学函数JDO目前好像还不支持此类特性。avg(...), sum(...), min(...), max(...)
count(*)
count(...), count(distinct ...), count(all...)其用法和SQL基本相同select distinct employee.name from Employee as employee
select count(distinct employee.name),count(employee) from Employee as employee
6。polymorphism (暂时不知道如何解释?)
from com.test.Animal as animal不光得到所有Animal得实例,而且可以得到所有Animal的子类(如果我们定义了一个子类Cat)一个比较极端的例子from java.lang.Object as o可以得到所有持久类的实例7。where语句定义查询语句的条件,举几个例子吧:from Employee as employee where employee.Name='Jplateau'
from Employee as employee where employee.Name like 'J%'
from Employee as employee where employee.Name like '%u'在where语句中“=”不光可以比较对象的属性,也可以比较对象,如:select animal from com.test.Animal as animal where animal.name=dog
8。表达式在SQL语句中大部分的表达式在HQL中都可以使用:mathematical operators +, -, *, /
binary comparison operators =, >=, <=, <>, !=, like
logical operations and, or, not
string concatenation ||
SQL scalar functions like upper() and lower()
Parentheses ( ) indicate grouping
in, between, is null
JDBC IN parameters ?
named parameters :name, :start_date, :x1(这种应该是另一种"?"的变通解决方法)SQL literals 'foo', 69, '1970-01-01 10:00:01.0'
Java public static final constants eg.Color.TABBY其他不必解释了,在这里我只想对查询中的参数问题说明一下:大家知道在SQL中进行传递参数进行查询的时候,我们通常用PreparedStatement,在语句中写一大堆的“?”,在hql中也可以用这种方法,如:List mates = sess.find(
"select employee.name from Employee as employee " +
"where employee.Name=? ",
name,
Hibernate.STRING
);
(说明:上面利用Session里的find方法,在hibernate的api Session中重载了很多find方法,它可以满足你多种形式的查询)上边是一个参数的情形,这种情况下紧接着引入参数和定义参数的类型,当为多个参数,调用另一个find方法,它的后两个参数都是数组的形式。还有另外一种方法来解决上边的问题,JDO也有这样的方法,不过和hibernate的表现形式上有差别,但他们两个骨子里却是一样的,如:Query q = sess.createQuery("select employee.name from Employee as employee where employee.Name=:name");
q.setString("name", "Jplateau");
//当有多个参数的时候在此逐一定义Iterator employees = q.iterate();
9。order语句和sql语句没什么差别,如:select employee.name from Employee as employee where employee.Name like 'J%' order by employee.ID desc (或者asc)
10。group by语句同样和sql语句没什么差别,如:select employee.name,employee.DepNo from Employee as employee group by employee.DepNo
select foo.id, avg( elements(foo.names) ), max( indices(foo.names) ) from eg.Foo foo group by foo.id
{Note: You may use the elements and indices constructs inside a select clause, even on databases with no subselects.}谁帮我解释一下上边两句,谢过!11。子查询hibernate同样支持子查询,写几个例子:from eg.Cat as fatcat where fatcat.weight > ( select avg(cat.weight) from eg.DomesticCat cat )(二)条件查询Criteria Query
Criteria criteria = osession.createCriteria(Owner.class);
criteria.add(Expression.eq("age", new Integer(100)));
criteria.setFirstResult(2); //从返回结果的第二条记录开始的5条记录
criteria.setMaxResults(5);
List lc=criteria.list();
System.out.println("条件查询");
System.out.println(lc.size());
(三)原生SQL语句查询