hibernate HQL

1. hsql介绍：

1.查询所有客户

代码：

@Test
public void  TestDemo1(){
    Session session=   HibernateUtils.openSession();
    Transaction transaction=  session.beginTransaction();
    Query query=session.createQuery("from Customer ");//Hsql查询
    List<Customer> list= query.list();
    for (Customer c:list
         ) {
        System.out.println(c.getCname());
    }
    transaction.commit();
    session.close();
}

结果：

Hibernate: select customer0_.id as id2_, customer0_.name as name2_ from t_customer customer0_
ssml
ssml
ssml001
ssml002
ssml002
ssml003

2.1 选择查询

public void demo02(){

//2 简单条件查询

Session session = factory.openSession();

session.beginTransaction();

//1 指定数据，cid OID名称

//Query query = session.createQuery("from Customer where cid = 1");

//2 如果使用id，也可以（了解）

//Query query = session.createQuery("from Customer where id = 1");

//3 对象别名 ,格式： 类 [as] 别名

//Query query = session.createQuery("from Customer as c where c.cid = 1");

//4 查询所有项，mysql--> select * from...

Query query = session.createQuery("select c from Customer as c where c.cid = 1");

Customer customer = (Customer) query.uniqueResult();

System.out.println(customer);

session.getTransaction().commit();

session.close();

}

3.1 投影查询（部分）

   public void demo04(){
        //4 投影
        Session session = HibernateUtils.openSession();
        session.beginTransaction();

        //1 默认
        //如果单列 ，select c.cname from，需要List<Object>
        //如果多列，select c.cid,c.cname from ，需要List<Object[]>  ,list存放每行，Object[]多列
//    Query query = session.createQuery("select c.cid,c.cname from Customer c");
        //2 将查询部分数据，设置Customer对象中
        // * 格式：new Customer(c.cid,c.cname)
        // * 注意：Customer必须提供相应的构造方法。
        // * 如果投影使用oid，结果脱管态对象。
        Query query = session.createQuery("select new Customer(c.cid,c.cname) from Customer c");

        List<Customer> allCustomer = query.list();
        for (Customer customer : allCustomer) {
            System.out.println(customer.getCname() + " : " + customer.getOrderSet().size());
        }

        session.getTransaction().commit();
        session.close();
    }

结果：

Hibernate: select customer0_.id as col_0_0_, customer0_.name as col_1_0_ from t_customer customer0_
ssml : 0
ssml : 0
ssml001 : 0
ssml002 : 0

4.1 排序

代码：

@Test
public void demo03(){
    //3排序 ，mysql--> select... order by 字段  [asc]|desc ,....
    Session session = HibernateUtils.openSession();
    session.beginTransaction();

    Query query = session.createQuery("from Customer order by cid desc");

    List<Customer> allCustomer = query.list();
    for (Customer customer : allCustomer) {
        System.out.println(customer.getCid()+"---------"+customer.getCname());
    }

    session.getTransaction().commit();
    session.close();
}

结果：

Hibernate: select customer0_.id as id2_, customer0_.name as name2_ from t_customer customer0_ order by customer0_.id desc
6---------ssml003
5---------ssml002
4---------ssml002
3---------ssml001
2---------ssml
1---------ssml

5.1 分页

//分页
@Test
public void demo05(){
    //分页
    Session session = HibernateUtils.openSession();
    session.beginTransaction();

    Query query = session.createQuery("from Customer");
    // * 开始索引 , startIndex 算法： startIndex = (pageNum - 1) * pageSize;
    // *** pageNum 当前页（之前的 pageCode）
    query.setFirstResult(0);
    // * 每页显示个数 ， pageSize
    query.setMaxResults(2);

    List<Customer> allCustomer = query.list();
    for (Customer customer : allCustomer) {
        System.out.println(customer.getCid()+"---------"+customer.getCname());
    }

    session.getTransaction().commit();
    session.close();
}

结果：

Hibernate: select customer0_.id as id2_, customer0_.name as name2_ from t_customer customer0_ limit ?
1---------ssml
2---------ssml

6.1 绑定参数

/*  绑定参数

 * 方式1：占位符，使用? 在hql语句替换具体参数

 * 设置参数 query.setXxx(int , object)

 * 参数1：?位置，从0开始。

 * 参数2：实际参数

 * 例如：String --> query.setString(int,String)

 * 方式2：别名 , 格式 “属性= :别名 ”

 * 设置参数 query.setXxx(String,object)

 * 参数1：别名

 * 参数2：实际参数

 * 例如：Integer --> query.setInteger(String,Integer)

 * 提供公共设置方法

 * setParameter(int|string , Object)

 */

代码：

 @Test
    public void demo06(){
        Session session = HibernateUtils.openSession();
        session.beginTransaction();

        Integer cid = 1;

        //方式1占位符，
// Query query = session.createQuery("from Customer where cid = ?");
// query.setInteger(0, cid);//从0开始
        //方式2 别名 , 格式 “属性= :别名 ”
        Query query = session.createQuery("from Customer where cid = :id");
   // query.setInteger("id", cid);
     query.setParameter("id", cid);

       Customer customer = (Customer) query.uniqueResult();
        System.out.println(customer);

        session.getTransaction().commit();
        session.close();
    }

结果：

Hibernate: select customer0_.id as id2_, customer0_.name as name2_ from t_customer customer0_ where customer0_.id=?
Hibernate: select orderset0_.cid as cid2_1_, orderset0_.id as id1_, orderset0_.id as id1_0_, orderset0_.price as price1_0_, orderset0_.cid as cid1_0_ from t_order orderset0_ where orderset0_.cid=?
Customer{cid=1, cname='ssml', orderSet=[com.ssml.dentity.Order@54422e18]}

7.1 聚合函数和分组

代码

  @Test
   public void demo07(){
       /* 7  聚合函数
        */
       Session session = HibernateUtils.openSession();
       session.beginTransaction();

       //1
// Query query = session.createQuery("select count(*) from Customer");
       //2 别名
// Query query = session.createQuery("select count(c) from Customer c");
       //3 oid
     Query query = session.createQuery("select count(cid) from Customer");

       Long numLong = (Long) query.uniqueResult();
       int num = numLong.intValue();

       System.out.println(num);


       session.getTransaction().commit();
       session.close();
   }

结果：

Hibernate: select count(customer0_.id) as col_0_0_ from t_customer customer0_
6

8.1 连接查询
   
   

    
    

   
   
   
   

    
    
    
    
1.交叉连接 ，等效 sql 笛卡尔积
    
    
2.隐式内连接，等效 sql 隐式内连接
    
    
3.内连接，等效sql内连接
    
    
4.迫切内连接，hibernate底层使用 内连接。
    
    
5.左外连接，等效sql左外连接
    
    
6.迫切左外连接，hibernate底层使用 左外连接
    
    
7.右外连接，等效sql右外连接

   
   
/* 8 链接查询 ： 左外连接和迫切左外连接？
 * * 左外连接 , left outer join
 * 底层使用sql的左外连接，hibernate进行数据自动封装，将一条记录，封装给两个对象（Customer，Order）
 * 将两个对象添加到一个对象数组中Object[Customer,Order]
 * * 迫切左外链接 left outer join fetch
 * 底层使用sql的左外连接，hibernate将一条记录封装给Customer，讲order数据封装Order，并将order关联到Customer
 * customer.getOrderSet().add(order)
 * 默认查询的数据重复
 */
   @Test
   public void demo08(){
       /* 8 链接查询 ： 左外连接和迫切左外连接？
        * * 左外连接 , left outer join
        *     底层使用sql的左外连接，hibernate进行数据自动封装，将一条记录，封装给两个对象（Customer，Order）
        *     将两个对象添加到一个对象数组中Object[Customer,Order]
        * * 迫切左外链接 left outer join fetch
        *     底层使用sql的左外连接，hibernate将一条记录封装给Customer，讲order数据封装Order，并将order关联到Customer
        *        customer.getOrderSet().add(order)
        *     默认查询的数据重复
        */
       Session session = HibernateUtils.openSession();
       session.beginTransaction();

       //左外连接
List list = session.createQuery("from Customer c left outer join c.orderSet ").list();//数据重复！！！！！！！！！！！！！！！

       for ( Object o:list
            ) {
           System.out.println(o.toString()+"======"+list.size());
       }
       session.getTransaction().commit();
       session.close();
   }
//数据去重复
代码：
   @Test
   public void demo08(){

       Session session = HibernateUtils.openSession();
       session.beginTransaction();

       //左外连接
List list = session.createQuery("select distinct c from Customer c left outer join c.orderSet ").list();

       for ( Object o:list
            ) {
           System.out.println(o.toString()+"======"+list.size());
       }
       session.getTransaction().commit();
       session.close();
   }
结果：
Hibernate: select orderset0_.cid as cid2_1_, orderset0_.id as id1_, orderset0_.id as id1_0_, orderset0_.price as price1_0_, orderset0_.cid as cid1_0_ from t_order orderset0_ where orderset0_.cid in (select customer0_.id from t_customer customer0_ left outer join t_order orderset1_ on customer0_.id=orderset1_.cid)
Customer{cid=1, cname='ssml', orderSet=[com.ssml.dentity.Order@563f38c4]}======6
Customer{cid=2, cname='ssml', orderSet=[]}======6
Customer{cid=3, cname='ssml001', orderSet=[com.ssml.dentity.Order@543295b0]}======6
Customer{cid=4, cname='ssml002', orderSet=[com.ssml.dentity.Order@54422e18]}======6
Customer{cid=5, cname='ssml002', orderSet=[com.ssml.dentity.Order@117159c0, com.ssml.dentity.Order@3e8c3cb, com.ssml.dentity.Order@27406a17]}======6
Customer{cid=6, cname='ssml003', orderSet=[]}======6