HQL（Hibernate Query Language）

1. NativeSQL > HQL > EJB QL(JP QL 1.0) > QBC(Query By Criteria) > QBE(Query By Example)
2. 总结：QL应该和导航关系结合，共同为查询提供服务。

以下图为例：

Category:

package com.bjsxt.hibernate;

import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;

@Entity
public class Category {
    
    private Integer id;
    
    private String name;

    @Id
    @GeneratedValue
    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }
}

Topic:

package com.bjsxt.hibernate;

import java.util.Date;

import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.ManyToOne;

@Entity
public class Topic {
    
    private Integer id;
    
    private String title;
    
    private Category category;
    
    private Date createDate;

    @Id
    @GeneratedValue
    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getTitle() {
        return title;
    }

    public void setTitle(String title) {
        this.title = title;
    }

    @ManyToOne(fetch=FetchType.LAZY)
    public Category getCategory() {
        return category;
    }

    public void setCategory(Category category) {
        this.category = category;
    }

    public Date getCreateDate() {
        return createDate;
    }

    public void setCreateDate(Date createDate) {
        this.createDate = createDate;
    }
}

Msg:

package com.bjsxt.hibernate;

import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.ManyToOne;

@Entity
public class Msg {
    
    private Integer id;
    
    private String cont;
    
    private Topic topic;

    @Id
    @GeneratedValue
    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getCont() {
        return cont;
    }

    public void setCont(String cont) {
        this.cont = cont;
    }

    @ManyToOne
    public Topic getTopic() {
        return topic;
    }

    public void setTopic(Topic topic) {
        this.topic = topic;
    }
}

MsgInfo:

package com.bjsxt.hibernate;

//VO(Value Object) DTO(Data Transfer Object)
public class MsgInfo {    

    private Integer id;
    
    private String cont;
    
    private String topicName;
    
    private String categoryName;
    
    public MsgInfo(Integer id,String cont,String topicName,String categoryName){
        this.id = id;
        this.cont = cont;
        this.topicName = topicName;
        this.categoryName = categoryName;
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getCont() {
        return cont;
    }

    public void setCont(String cont) {
        this.cont = cont;
    }

    public String getTopicName() {
        return topicName;
    }

    public void setTopicName(String topicName) {
        this.topicName = topicName;
    }

    public String getCategoryName() {
        return categoryName;
    }

    public void setCategoryName(String categoryName) {
        this.categoryName = categoryName;
    }
}

HibernateQLTest1:

package com.bjsxt.hibernate;

import java.util.Date;
import java.util.List;

import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.cfg.AnnotationConfiguration;
import org.hibernate.tool.hbm2ddl.SchemaExport;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;

public class HibernateQLTest {
    private static SessionFactory sf = null;
    
    @Before
    public void beforeClass(){
//        new SchemaExport(new AnnotationConfiguration().configure()).create(false, true);
        sf = new AnnotationConfiguration().configure().buildSessionFactory();
    }
    
    @After
    public void afterClass(){
        if(sf != null){
            sf.close();
        }
    }
    
    @Test
    public void testSave(){
        
        Session session = sf.getCurrentSession();
        session.beginTransaction();
        
        for(int i=0; i<10; i++){
            Category c = new Category();
            c.setName("c" + i);
            session.save(c);
        }
        
        for(int i=0; i<10; i++){
            Category c = new Category();
            c.setId(1);
            Topic t = new Topic();
            t.setCategory(c);
            t.setCreateDate(new Date());
            t.setTitle("t"+i);
            session.save(t);
        }
        
        for(int i=0; i<10; i++){
            Topic t = new Topic();
            t.setId(1);
            Msg m = new Msg();
            m.setTopic(t);
            m.setCont("m" + i);
            session.save(m);
        }
        
        session.getTransaction().commit();
    }
    
    @Test
    public void testHQL_01(){
        Session session = sf.getCurrentSession();
        session.beginTransaction();
        Query q = (Query) session.createQuery("from Category");
        List<Category> categories = (List<Category>)q.list();
        for(Category c : categories){
            System.out.println(c.getName());
        }
        session.getTransaction().commit();
    }
    
    @Test
    public void testHQL_02(){
        Session session = sf.getCurrentSession();
        session.beginTransaction();
        Query q = (Query) session.createQuery("from Category c where c.name > 'c5'");
        List<Category> categories = (List<Category>)q.list();
        for(Category c : categories){
            System.out.println(c.getName());
        }
        session.getTransaction().commit();
    }
    
    @Test
    public void testHQL_03(){
        Session session = sf.getCurrentSession();
        session.beginTransaction();
        Query q = (Query) session.createQuery("from Category c order by name desc");
        List<Category> categories = (List<Category>)q.list();
        for(Category c : categories){
            System.out.println(c.getName());
        }
        session.getTransaction().commit();
    }
    
    @Test
    public void testHQL_04(){
        Session session = sf.getCurrentSession();
        session.beginTransaction();
        Query q = (Query) session.createQuery(
                "select distinct c from Category c order by name desc");//主键不同
        List<Category> categories = (List<Category>)q.list();
        for(Category c : categories){
            System.out.println(c.getName());
        }
        session.getTransaction().commit();
    }
    
    @Test
    public void testHQL_05(){
        Session session = sf.getCurrentSession();
        session.beginTransaction();
        /*Query q = (Query) session.createQuery(
                "from Category c where c.id > :min and c.id < :max");//主键不同
//        q.setParameter("min", 2);
//        q.setParameter("max", 8);
        q.setInteger("min", 2);
        q.setInteger("max", 8);*/
        
        Query q = (Query) session.createQuery("from Category c where c.id > :min and c.id < :max")
                .setInteger("min", 2)
                .setInteger("max", 8);
        List<Category> categories = (List<Category>)q.list();
        for(Category c : categories){
            System.out.println(c.getId() + "-" +c.getName());
        }
        session.getTransaction().commit();
    }
    
    /**
     * 链式编程
     */
    @Test
    public void testHQL_06(){
        Session session = sf.getCurrentSession();
        session.beginTransaction();
        Query q = (Query) session.createQuery("from Category c where c.id > ? and c.id < ?")
                .setInteger(0, 2)//索引从0开始
                .setInteger(1, 8);
        List<Category> categories = (List<Category>)q.list();
        for(Category c : categories){
            System.out.println(c.getId() + "-" +c.getName());
        }
        session.getTransaction().commit();
    }
    
    /**
     * 分页
     */
    @Test
    public void testHQL_07(){
        Session session = sf.getCurrentSession();
        session.beginTransaction();
        Query q = (Query) session.createQuery("from Category c order by name desc");
        q.setMaxResults(4);//每页4条
        q.setFirstResult(2);//从第二行开始
        List<Category> categories = (List<Category>)q.list();
        for(Category c : categories){
            System.out.println(c.getId() + "-" +c.getName());
        }
        session.getTransaction().commit();
    }
    
    /**
     * 分页(换一种获取方式)
     */
    @Test
    public void testHQL_08(){
        Session session = sf.getCurrentSession();
        session.beginTransaction();
        Query q = (Query) session.createQuery("select c.id,c.name from Category c order by name desc");
        q.setMaxResults(4);//每页4条
        q.setFirstResult(2);//从第二行开始
        List<Object[]> categories = (List<Object[]>)q.list();
        for(Object[] c : categories){
            System.out.println(c[0] + "-" +c[1]);
        }
        session.getTransaction().commit();
    }
    
    /**
     * 设定 fetch type 为 lazy 后将不会有第二条sql语句
     */
    @Test
    public void testHQL_09(){
        Session session = sf.getCurrentSession();
        session.beginTransaction();
        Query q = session.createQuery("from Topic t where t.category.id = 1");
        List<Topic> topics = (List<Topic>)q.list();
        for(Topic t : topics){
            System.out.println(t.getId() + "-" + t.getTitle());
//            System.out.println(t.getCategory().getName());
        }
        session.getTransaction().commit();
    }
    
    /**
     * 设定 fetch type 为 lazy 后将不会有第二条sql语句
     */
    @Test
    public void testHQL_10(){
        Session session = sf.getCurrentSession();
        session.beginTransaction();
        Query q = session.createQuery("from Topic t where t.category.id = 1");
        List<Topic> topics = (List<Topic>)q.list();
        for(Topic t : topics){
            System.out.println(t.getId() + "-" + t.getTitle());
//            System.out.println(t.getCategory().getName());
        }
        session.getTransaction().commit();
    }
    
    @Test
    public void testHQL_11(){
        Session session = sf.getCurrentSession();
        session.beginTransaction();
        Query q = session.createQuery("from Msg m where m.topic.category.id = 1");
        for(Object o : q.list()){
            Msg m = (Msg)o;
            System.out.println(m.getCont());
        }
        session.getTransaction().commit();
    }
    
    /**
     * VO(Value Object)
     * DTO(Data Transfer Object)
     */
    @Test
    public void testHQL_12(){
        Session session = sf.getCurrentSession();
        session.beginTransaction();
        Query q = session.createQuery("select new com.bjsxt.hibernate.MsgInfo(m.id, m.cont, m.topic.title, m.topic.category.name) from Msg m");
        for(Object o : q.list()){
            MsgInfo m = (MsgInfo)o;
            System.out.println(m.getCont());
        }
        session.getTransaction().commit();
    }
    
    //动手测试left right join
    //为什么不能直接写 Category 名，而必须写 t.category
    //因为有可能存在多个成员变量（同一个类），需要指明哪一个成员变量的连接条件来做连接
    @Test
    public void testHQL_13(){
        Session session = sf.getCurrentSession();
        session.beginTransaction();
        Query q = session.createQuery("select t.title,c.name from Topic t join t.category c");
        List<Object[]> list = q.list();
        for(Object[] o : list){
            System.out.println(o[0] + "-" + o[1]);
        }
        session.getTransaction().commit();
    }
    
    //学习使用 uniqueResult
    @Test
    public void testHQL_14(){
        Session session = sf.getCurrentSession();
        session.beginTransaction();
        Query q = session.createQuery("from Msg m where m = :MsgToSearch");//不重要
        Msg m = new Msg();
        m.setId(1);
        q.setParameter("MsgToSearch", m);
        
        Msg mResult = (Msg)q.uniqueResult();
        System.out.println(mResult.getCont());
        
        session.getTransaction().commit();
    }
    
    @Test
    public void testHQL_15(){
        Session session = sf.getCurrentSession();
        session.beginTransaction();
        Query q = session.createQuery("select count(*) from Msg m");
        
        long count = (Long)q.uniqueResult();
        System.out.println(count);        
        session.getTransaction().commit();
    }
    
    @Test
    public void testHQL_16(){
        Session session = sf.getCurrentSession();
        session.beginTransaction();
        Query q = session.createQuery("select max(m.id), min(m.id), avg(m.id), sum(m.id) from Msg m");
        
        Object[] o = (Object[])q.uniqueResult();
        System.out.println(o[0] + "-" + o[1] + "-" + o[2] + "-" + o[3]);
        
        session.getTransaction().commit();
    }
    
    @Test
    public void testHQL_17(){
        Session session = sf.getCurrentSession();
        session.beginTransaction();
        Query q = session.createQuery("from Msg m where m.id between 3 and 5");
        
        for(Object o : q.list()){
            Msg m = (Msg)o;
            System.out.println(m.getId() + "-" +m.getCont());
        }
        
        session.getTransaction().commit();
    }
    
    @Test
    public void testHQL_18(){
        Session session = sf.getCurrentSession();
        session.beginTransaction();
        Query q = session.createQuery("from Msg m where m.id in(3,4,5)");
        
        for(Object o : q.list()){
            Msg m = (Msg)o;
            System.out.println(m.getId() + "-" +m.getCont());
        }
        
        session.getTransaction().commit();
    }
    
    @Test
    public void testHQL_19(){
        Session session = sf.getCurrentSession();
        session.beginTransaction();
        Query q = session.createQuery("from Msg m where m.cont is not null");
        
        for(Object o : q.list()){
            Msg m = (Msg)o;
            System.out.println(m.getId() + "-" +m.getCont());
        }
        
        session.getTransaction().commit();
    }
    
    @Test
    public void testSchemaExport(){
        new SchemaExport(new AnnotationConfiguration().configure()).create(false, true);
    }
    
    public static void main(String[] args) {
//        new HibernatTreeTest().testSave();
        new HibernateQLTest().beforeClass();
    }
    
}

HibernateQLTest2:

package com.bjsxt.hibernate;

import java.util.Date;
import java.util.List;

import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.cfg.AnnotationConfiguration;
import org.hibernate.tool.hbm2ddl.SchemaExport;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;

public class HibernateQLTest {
    private static SessionFactory sf = null;
    
    @Before
    public void beforeClass(){
//        new SchemaExport(new AnnotationConfiguration().configure()).create(false, true);
        sf = new AnnotationConfiguration().configure().buildSessionFactory();
    }
    
    @After
    public void afterClass(){
        if(sf != null){
            sf.close();
        }
    }
    
    @Test
    public void testSave(){
        
        Session session = sf.getCurrentSession();
        session.beginTransaction();
        
        for(int i=0; i<10; i++){
            Category c = new Category();
            c.setName("c" + i);
            session.save(c);
        }
        
        for(int i=0; i<10; i++){
            Category c = new Category();
            c.setId(1);
            Topic t = new Topic();
            t.setCategory(c);
            t.setCreateDate(new Date());
            t.setTitle("t"+i);
            session.save(t);
        }
        
        for(int i=0; i<10; i++){
            Topic t = new Topic();
            t.setId(1);
            Msg m = new Msg();
            m.setTopic(t);
            m.setCont("m" + i);
            session.save(m);
        }
        
        session.getTransaction().commit();
    }
    
    //is empty and is not empty
    @Test
    public void testHQL_20(){
        Session session = sf.getCurrentSession();
        session.beginTransaction();
        Query q = session.createQuery("from Topic t where t.msgs is empty");
        
        for(Object o : q.list()){
            Topic t = (Topic)o;
            System.out.println(t.getId() + "-" +t.getTitle());
        }
        
        session.getTransaction().commit();
    }
    
    @Test
    public void testHQL_21(){
        Session session = sf.getCurrentSession();
        session.beginTransaction();
        Query q = session.createQuery("from Topic t where t.title like '%5'");
        
        for(Object o : q.list()){
            Topic t = (Topic)o;
            System.out.println(t.getId() + "-" +t.getTitle());
        }
        
        session.getTransaction().commit();
    }
    
    @Test
    public void testHQL_22(){
        Session session = sf.getCurrentSession();
        session.beginTransaction();
        Query q = session.createQuery("from Topic t where t.title like '_5'");
        
        for(Object o : q.list()){
            Topic t = (Topic)o;
            System.out.println(t.getId() + "-" +t.getTitle());
        }
        
        session.getTransaction().commit();
    }
    
    //不重要
    @Test
    public void testHQL_23(){
        Session session = sf.getCurrentSession();
        session.beginTransaction();
        Query q = session.createQuery("select lower(t.title),"
                + "upper(t.title),"
                + "trim(t.title),"
                + "concat(t.title,'***'),"
                + "length(t.title) "
                + "from Topic t");
        
        for(Object o : q.list()){
            Object[] arr = (Object[])o;
            System.out.println(arr[0] + "-" + arr[1] + "-" + arr[2] + "-" + arr[3] + "-" + arr[4]);
        }
        
        session.getTransaction().commit();
    }
    
    @Test
    public void testHQL_24(){
        Session session = sf.getCurrentSession();
        session.beginTransaction();
        Query q = session.createQuery("select abs(t.id),"
                + "sqrt(t.id),"
                + "mod(t.id,2) "
                + "from Topic t");
        
        for(Object o : q.list()){
            Object[] arr = (Object[])o;
            System.out.println(arr[0] + "-" + arr[1] + "-" + arr[2]);
        }
        
        session.getTransaction().commit();
    }
    
    @Test
    public void testHQL_25(){
        Session session = sf.getCurrentSession();
        session.beginTransaction();
        Query q = session.createQuery("select current_date,current_time,current_timestamp,t.id from Topic t");
        
        for(Object o : q.list()){
            Object[] arr = (Object[])o;
            System.out.println(arr[0] + "|" + arr[1] + "|" + arr[2] + "|" + arr[3]);
        }
        
        session.getTransaction().commit();
    }
    
    @Test
    public void testHQL_26(){
        Session session = sf.getCurrentSession();
        session.beginTransaction();
        Query q = session.createQuery("from Topic t where t.createDate < :date");
        q.setParameter("date", new Date());
        
        for(Object o : q.list()){
            Topic t = (Topic)o;
            System.out.println(t.getTitle());
        }
        
        session.getTransaction().commit();
    }
    
    @Test
    public void testHQL_27(){
        Session session = sf.getCurrentSession();
        session.beginTransaction();
        Query q = session.createQuery("select t.title, count(*) from Topic t group by t.title");
        
        for(Object o : q.list()){
            Object[] arr = (Object[])o;
            System.out.println(arr[0] + "|" + arr[1]);
        }
        
        session.getTransaction().commit();
    }
    
    @Test
    public void testHQL_28(){
        Session session = sf.getCurrentSession();
        session.beginTransaction();
        Query q = session.createQuery("select t.title, count(*) from Topic t group by t.title having count(*) > 0");
        
        for(Object o : q.list()){
            Object[] arr = (Object[])o;
            System.out.println(arr[0] + "|" + arr[1]);
        }
        
        session.getTransaction().commit();
    }
    
    @Test
    public void testHQL_29(){
        Session session = sf.getCurrentSession();
        session.beginTransaction();
        Query q = session.createQuery("from Topic t where t.id < (select avg(t.id) from Topic t)");
        
        for(Object o : q.list()){
            Topic t = (Topic)o;
            System.out.println(t.getTitle());
        }
        
        session.getTransaction().commit();
    }
    
    @Test
    public void testHQL_30(){
        Session session = sf.getCurrentSession();
        session.beginTransaction();
        Query q = session.createQuery("from Topic t where t.id < ALL (select t.id from Topic t where mod(t.id,2) = 0)");
        
        for(Object o : q.list()){
            Topic t = (Topic)o;
            System.out.println(t.getTitle());
        }
        
        session.getTransaction().commit();
    }
    
    //用in可以实现 exists 的功能
    //但是 exists 执行效率高
    @Test
    public void testHQL_31(){
        Session session = sf.getCurrentSession();
        session.beginTransaction();
        Query q = session.createQuery("from Topic t where not exists (select m.id from Msg m where m.topic.id = t.id)");//就是沒有回帖的topic
        
        for(Object o : q.list()){
            Topic t = (Topic)o;
            System.out.println(t.getTitle());
        }
        
        session.getTransaction().commit();
    }
    
    @Test
    public void testHQL_32(){
        Session session = sf.getCurrentSession();
        session.beginTransaction();
        
        Query q = session.createQuery("update Topic t set t.title = upper(t.title)");
        q.executeUpdate();
        q = session.createQuery("from Topic");
        for(Object o : q.list()){
            Topic t = (Topic)o;
            System.out.println(t.getTitle());
        }
        
        q = session.createQuery("update Topic t set t.title = lower(t.title)");
        q.executeUpdate();
        
        session.getTransaction().commit();
    }
    
    //不重要
    @Test
    public void testHQL_33(){
        Session session = sf.getCurrentSession();
        session.beginTransaction();
        
        Query q = session.getNamedQuery("topic.selectCertainTopic");
        q.setParameter("id", 5);
        Topic t = (Topic)q.uniqueResult();
        System.out.println(t.getTitle());
        
        session.getTransaction().commit();
    }
    
    //Native(不太重要)
    @Test
    public void testHQL_34(){
        Session session = sf.getCurrentSession();
        session.beginTransaction();
        
        SQLQuery q = session.createSQLQuery("select * from category limit 2,4").addEntity(Category.class);
        List<Category> categories = (List<Category>)q.list();
        for(Category c : categories){
            System.out.println(c.getName());
        }
        
        session.getTransaction().commit();
    }
    
    @Test
    public void testSchemaExport(){
        new SchemaExport(new AnnotationConfiguration().configure()).create(false, true);
    }
    
    public static void main(String[] args) {
//        new HibernatTreeTest().testSave();
        new HibernateQLTest().beforeClass();
    }
    
}

jar包链接: https://pan.baidu.com/s/1slvwzbn 密码: hwef

代码链接1: https://pan.baidu.com/s/1skCHfU9 密码: ka73

代码链接2: https://pan.baidu.com/s/1qYruCUC 密码: v4eb