Hibernate查询语言总结

Hibernate查询语言总结

一  简介

    Hibernate配置了一种非常强大的查询语言，这种语言看上去很想sql，但是不要被语法结构上的相似所迷惑，HQL是非常有意识的被设计为完全面向对象的查询，它可以理解如继承、多态和关联之类的概念

Hibernate的查询的语言对大小写不敏感

二  各种常用的查询

1．from查询语句 --- 最简单的查询语句,返回的是实体类的集合

    查询语句的语法：

       from Customers 

       from Customers as c 或 from Customers c 

    List<Customers> list = session.createQuery("from Customers").list();

       for (Customers entity : list) {

           System.out.println(entity.getRealName());

       }

    注：查询的语言别名的as可以省去，并且在写的时候别名都是小写的是最好的习惯。

2．关联和连接(join)查询

Hql的查询分为内连接，左外连接，右外连接，全连接，在sql中不支持全连接。

下面分别来介绍一下连接

(1)内连接  内连接也称自然连接，是hibernate内部自己执行的的。

Query query = session

              .createQuery("from Customers as c inner join c.orders as o");

       List<Object[]> list = query.list();

       for (Object[] a : list) {

           System.out.println();

      }

执行的sql语句为：

    Hibernate: select customers0_.id as id0_0_, orders1_.id as id1_1_, customers0_.realName as realName0_0_, customers0_.pass as pass0_0_, customers0_.sex as sex0_0_, customers0_.petName as petName0_0_, customers0_.email as email0_0_, customers0_.rdate as rdate0_0_, orders1_.number as number1_1_, orders1_.address as address1_1_, orders1_.phone as phone1_1_, orders1_.odate as odate1_1_, orders1_.sum as sum1_1_, orders1_.status as status1_1_, orders1_.cid as cid1_1_ from customers customers0_ inner join orders orders1_ on customers0_.id=orders1_.cid

 

(2)左外连接   语句的查询为：

Query query = session

              .createQuery("select c.id,c.realName,o.id,o.number from Customers as c left join c.orders as o where c.id=1");

 

       List<Object[]> list = query.list();

       for (Object[] a : list) {

           System.out.println(a[0] + "==" + a[1] + "==" + a[2] + "==" + a[3]);

       }

查询的sql语句为:

Hibernate: select customers0_.id as col_0_0_, customers0_.realName as col_1_0_, orders1_.id as col_2_0_, orders1_.number as col_3_0_ from customers customers0_ leftouter join orders orders1_ on customers0_.id=orders1_.cid where customers0_.id=1

 

(3)右外连接  查询的语句为：

Query query = session

              .createQuery("select c.id,c.realName,o.id,o.number from Customers as c right join c.orders as o where c.id=1");

 

       List<Object[]> list = query.list();

       for (Object[] a : list) {

           System.out.println(a[0] + "==" + a[1] + "==" + a[2] + "==" + a[3]);

       }

执行的sql语句为：

Hibernate: select customers0_.id as col_0_0_, customers0_.realName as col_1_0_, orders1_.id as col_2_0_, orders1_.number as col_3_0_ from customers customers0_ right outer join orders orders1_ on customers0_.id=orders1_.cid where customers0_.id=1

注：

（1）查询的时候查询的语句为where，而不是on。

3．投影查询

查询的语句为：

    List<Object[]> list = session.createQuery(

              "select c.realName,c.petName from Customers as c").list();

       for (Object[] c : list) {

           System.out.println("petName====" + c[0] + "------realName=" + c[1]);

       }

查询的结果为：

Hibernate: select customers0_.realName as col_0_0_, customers0_.petName as col_1_0_ from customers customers0_

petName====熊熊------realName=老王

petName====老王------realName=熊熊

 

如果在查询的时候查询的语句省去select c.realName,c.petName，得到的是一个List<Customers>集合对象，而不是数组

 

 

询*所有得到的是一个数组对象，在from前不可以加任何语句，如果加语句的话，返回的就是一个数组

       // List<Object[]> list =

       // session.createQuery("select id,realName,pass,sex from Customers").list();

 

 

List<Customers> list = session.createQuery("from Customers").list();

 

       for (Customers entity : list) {

           System.out.println(entity.getRealName());

       }

 

4. Cireria两张表的关系查询

    根据条件查询出两张表都符合的字段

       List<Customers> entity = session.createCriteria(Customers.class)

              .add(Restrictions.like("realName", "%熊%"))

           .createCriteria("orders").add(Restrictions.like("number", "1")).list();

       for (Customers entities : entity) {

           System.out.println(entities.toString());

       }

    以上的查询的意思：

       因为用Cireria查询的时候在外没有条件，所以要用add()方法去给它赋条件，用createCriteria去指定给哪一个实体类赋条件。

查询的sql语句为：

Hibernate: select this_.id as id0_1_, this_.realName as realName0_1_, this_.pass as pass0_1_, this_.sex as sex0_1_, this_.petName as petName0_1_, this_.email as email0_1_, this_.rdate as rdate0_1_, orders1_.id as id1_0_, orders1_.number as number1_0_, orders1_.address as address1_0_, orders1_.phone as phone1_0_, orders1_.odate as odate1_0_, orders1_.sum as sum1_0_, orders1_.status as status1_0_, orders1_.cid as cid1_0_ from customers this_ inner join orders orders1_ on this_.id=orders1_.cid where this_.realName like ? and orders1_.number like ?

5．标量查询  SQLQuery

查询的语句为：

    // 把数组转换为实体类,运用addEntity()方法

       List<Customers> entities = session

              .createSQLQuery("select * from customers")

              .addEntity(Customers.class).list();

      

       for(Customers entity:entities){

           System.out.println(entity.getRealName()+"==="+entity.getPetName());

       }

执行的sql语句为：

Hibernate: select * from customers

注：标量查询和sql查询的语句一样

 

以上这几种查询都是hql中最常见的查询，必须要牢记！