Hibernate框架-HQL语法

一、Hibernate查询总结：

1.oid查询-get
2.对象属性导航查询
3.HQL
4.Criteria
5.原生SQL

二、HQL语法

1、基础语法

 public void fun1(){
        Session session = HibernateUtils.openSession();
        Transaction tx = session.beginTransaction();
        //----------------------------------------------------
        String hql = " from  cn.itcast.domain.Customer ";//完整写法
        String hql2 = " from  Customer "; //简单写法
        String hql3 = " from java.lang.Object "; 
        
        Query query = session.createQuery(hql3);
        
        List list = query.list();
        
        System.out.println(list);
        //----------------------------------------------------
        tx.commit();
        session.close();  
}

2、排序查询

String hql1 = " from  cn.itcast.domain.Customer order by cust_id asc ";//完整写法
String hql2 = " from  cn.itcast.domain.Customer order by cust_id desc ";//完整写法
		
Query query = session.createQuery(hql2);
List list = query.list();
		
System.out.println(list);

3、条件查询

String hql1 = " from  cn.itcast.domain.Customer where cust_id =? ";//完整写法
String hql2 = " from  cn.itcast.domain.Customer where cust_id = :id ";//完整写法
		
Query query = session.createQuery(hql2);
//query.setParameter(0, 2l);
query.setParameter("id", 2l);	
List list = query.list();
		
System.out.println(list);

4、分页查询

String hql1 = " from  cn.itcast.domain.Customer  ";//完整写法	

Query query = session.createQuery(hql1);	
//limit ?,?
// (当前页数-1)*每页条数
query.setFirstResult(2);
query.setMaxResults(2);	
List list = query.list();
		
System.out.println(list);

5、统计查询

String hql1 = " select count(*) from  cn.itcast.domain.Customer  ";//完整写法
String hql2 = " select sum(cust_id) from  cn.itcast.domain.Customer  ";//完整写法
String hql3 = " select avg(cust_id) from  cn.itcast.domain.Customer  ";//完整写法
String hql4 = " select max(cust_id) from  cn.itcast.domain.Customer  ";//完整写法
String hql5 = " select min(cust_id) from  cn.itcast.domain.Customer  ";//完整写法
		
Query query = session.createQuery(hql5);
Number number  = (Number) query.uniqueResult();
		
System.out.println(number);

6、投影查询

String hql1 = " select cust_name from  cn.itcast.domain.Customer  ";
String hql2 = " select cust_name,cust_id from  cn.itcast.domain.Customer  ";
String hql3 = " select new Customer(cust_id,cust_name) from  cn.itcast.domain.Customer  ";
		
Query query = session.createQuery(hql3);	
List list = query.list();
		
System.out.println(list);

7、多表查询

7.1 原生SQL

 交叉连接-笛卡尔积(避免)
        select * from A,B
内连接
        |-隐式内连接
            select * from A,B  where b.aid = a.id
        |-显式内连接
           select * from A inner join B on b.aid = a.id
外连接
        |- 左外
            select * from A left [outer] join B on b.aid = a.id
        |- 右外
            select * from A right [outer] join B on b.aid = a.id

7.2 HQL

HQL的多表查询
        内连接(迫切)
        外连接
            |-左外(迫切)

7.3 HQL语法

内连接：

//HQL 内连接 => 将连接的两端对象分别返回.放到数组中.
public void fun1(){
	Session session = HibernateUtils.openSession();
	Transaction tx = session.beginTransaction();
	//----------------------------------------------------
	String hql = " from Customer c inner join c.linkMens ";
		
	Query query = session.createQuery(hql);
	List<Object[]> list = query.list();
		
	for(Object[] arr : list){
		System.out.println(Arrays.toString(arr));
	}
	//----------------------------------------------------
	tx.commit();
	session.close();
}
	
//HQL 迫切内连接 => 帮我们进行封装.返回值就是一个对象
public void fun2(){
	Session session = HibernateUtils.openSession();
	Transaction tx = session.beginTransaction();
	//----------------------------------------------------
	String hql = " from Customer c inner join fetch c.linkMens ";
		
	Query query = session.createQuery(hql);
	List<Customer> list = query.list();
		
	System.out.println(list);
	//----------------------------------------------------
	tx.commit();
	session.close();
		
}

左外链接：

//HQL 左外连接 => 将连接的两端对象分别返回.放到数组中.
public void fun3(){
	Session session = HibernateUtils.openSession();
	Transaction tx = session.beginTransaction();
	//----------------------------------------------------
	String hql = " from Customer c left join c.linkMens ";
		
	Query query = session.createQuery(hql);
	List<Object[]> list = query.list();
		
	for(Object[] arr : list){
		System.out.println(Arrays.toString(arr));
	}
	//----------------------------------------------------
	tx.commit();
	session.close();
}

右外连接：

//HQL 右外连接 => 将连接的两端对象分别返回.放到数组中.
public void fun4(){
	Session session = HibernateUtils.openSession();
	Transaction tx = session.beginTransaction();
	//----------------------------------------------------
	String hql = " from Customer c right join c.linkMens ";
	
	Query query = session.createQuery(hql);
	List<Object[]> list = query.list();
	
	for(Object[] arr : list){
		System.out.println(Arrays.toString(arr));
	}
	//----------------------------------------------------
	tx.commit();
	session.close();
}