Hql, Hibernate Query Language, is an object-oriented query language, which is based on objects, not tables and fields in the database.

1. Preparation

Firstly, there are two classes: TBook class and Category class. There is a many-to-one relationship between them.

Two classes correspond to two tables. Let's save some data first.

@Test
public void save() {
    Session session = null;
    Transaction tran = null;

    try {
        session = SessionUtil.getSession();
        tran = session.beginTransaction();
        Category c1 = new Category();
        c1.setCategory("literature");

        Category c2 = new Category();
        c2.setCategory("History");

        Category c3 = new Category();
        c3.setCategory("Knight errant");

        Category c4 = new Category();
        c4.setCategory("science fiction");

        Category c5 = new Category();
        c5.setCategory("Romance");

        TBook book1 = new TBook();
        book1.setBName("The Dream of Red Mansion");
        book1.setAuthor("Cao Xueqin");
        book1.setBPrice(60);
        book1.setPubDate(new Date());
        book1.setCategory(c1);

        TBook book2 = new TBook();
        book2.setBName("Comprehensive Mirror for Aid Government");
        book2.setAuthor("author of the history Zizhi Tongjian");
        book2.setBPrice(63);
        book2.setPubDate(new Date());
        book2.setCategory(c2);

        TBook book3 = new TBook();
        book3.setBName("Heroic Biography of Archery");
        book3.setAuthor("Jin Yong");
        book3.setBPrice(73);
        book3.setPubDate(new Date());
        book3.setCategory(c3);

        TBook book4 = new TBook();
        book4.setBName("Hundred Years of Loneliness");
        book4.setAuthor("Gabriel Garcia Marquez");
        book4.setBPrice(56);
        book4.setPubDate(new Date());
        book4.setCategory(c4);

        TBook book5 = new TBook();
        book5.setBName("Pride and Prejudice");
        book5.setAuthor("jane austen");
        book5.setBPrice(60);
        book5.setPubDate(new Date());
        book5.setCategory(c5);

        TBook book6 = new TBook();
        book6.setBName("Journey to the West");
        book6.setAuthor("Luo Guanzhong");
        book6.setBPrice(68);
        book6.setPubDate(new Date());
        book6.setCategory(c1);

        session.save(book1);
        session.save(book2);
        session.save(book3);
        session.save(book4);
        session.save(book5);
        session.save(book6);

        tran.commit();

    } catch (Exception ex) {
        ex.printStackTrace();
    } finally {
        session.close();
    }
}

Detailed data in the table

2. Hql query

2.1. Query for a single attribute

@Test
public void query1(){
    Session session = null;
    try {
        session = SessionUtil.getSession();

        List<String> list = session.createQuery("select BName from TBook").list();
        for (String name : list) {
            System.out.println(name);
        }
    } catch (Exception e) {
        e.printStackTrace();
    }finally{
        SessionUtil.close(session);
    }
}

The Dream of Red Mansion
 Comprehensive Mirror for Aid Government
 Heroic Biography of Archery
 Hundred Years of Loneliness
 Pride and Prejudice
 Journey to the West

2.2. Queries returning multiple attributes

@Test
public void query2(){
    Session session = null;
    try {
        session = SessionUtil.getSession();
        List<Object[]> list = session.createQuery("select BName,BPrice from TBook")
                            .list();
        for(Object[] book : list){
            System.out.println("Title:"+book[0]+"\t\t Price:"+book[1]);
        }
    } catch (Exception e) {
        e.printStackTrace();
    }finally{
        SessionUtil.close(session);
    }
}

Title: Dream of Red Mansions Price: 60
 Title: Zizhi Tongjian Price: 63
 Title: Heroes of Archery Price: 73
 Title: Centennial Loneliness Price: 56
 Title: Pride and Prejudice Price: 60
 Title: Journey to the West Price: 68

Note that when querying multiple attributes, the query results in a collection of object data.

Another way

@Test
    public void query3(){
        Session session = null;
        try {
            session = SessionUtil.getSession();
            List<TBook> list = session.createQuery("select new TBook(BName,BPrice) from TBook").list();
            for(TBook book : list){
                System.out.println(book.getBName()+"---"+book.getBPrice());
            }

        } catch (Exception e) {
            e.printStackTrace();
        }finally{
            SessionUtil.close(session);
        }
    }

Since new TBook(BName,BPrice) is used in Hql, there must be such a constructor in your pojo class.

2.3. Query all attributes

@Test
public void query3(){
    Session session = null;
    try {
        session = SessionUtil.getSession();
        List<TBook> list = session.createQuery("from TBook").list();
        for(TBook book : list){
            System.out.println(book);
        }
    } catch (Exception e) {
        e.printStackTrace();
    }finally{
        SessionUtil.close(session);
    }
}

TBook [id=1, author = Cao Xueqin, BName = Dream of Red Mansions, BPrice=60, pubDate=2017-06-10 14:51:57.0]
TBook [id=2, author = Sima Guang, BName = Zizhi Tongjian, BPrice=63, pubDate=2017-06-10 14:51:57.0]
TBook [id=3, author = Jinyong, BName = Heroes of Archery, BPrice=73, pubDate=2017-06-10 14:51:57.0]
TBook [id=4, author = Marquez, BName = 100 years of loneliness, BPrice=56, pubDate=2017-06-10 14:51:57.0]
TBook [id=5, author = Jane Austen, BName = pride and prejudice, BPrice = 60, pubDate = 2017-06-104:51:57.0]
TBook [id=6, author = Luo Guanzhong, BName = Journey to the West, BPrice=68, pubDate=2017-06-10 14:51:57.0]

Where hql can also be written in full, using an alias, that is, select t from TBook t

2.4. Splicing Query Statement of Conditional Query

@Test
public void query4(){
    Session session = null;
    try {
        session = SessionUtil.getSession();
        List<TBook> list = session.createQuery("from TBook where id<"+4).list();            
        for(TBook book : list){
            System.out.println(book);           }
    } catch (Exception e) {
        e.printStackTrace();
    }finally{
        SessionUtil.close(session);
    }
}

2.5. Placeholder Mode of Conditional Query

@Test
public void query5(){
    Session session = null;
    try {
        session = SessionUtil.getSession();
        List<TBook> list = session.createQuery("from TBook where id<?")
                            .setInteger(0,3).list();
        for(TBook book : list){
            System.out.println(book);
        }

    } catch (Exception e) {
        e.printStackTrace();
    }finally{
        SessionUtil.close(session);
    }
}

Note that the table below starts at 0.

Thereafter, the query results are no longer listed.

2.6. Alias-based queries for conditional queries

@Test
public void query6(){
    Session session = null;
    try {
        session = SessionUtil.getSession();
        List<TBook> list = session.createQuery("from TBook where id<:id")
                .setParameter("id",4).list();
        for(TBook book : list){
            System.out.println(book);
        }
    } catch (Exception e) {
        e.printStackTrace();
    }finally{
        SessionUtil.close(session);
    }
}

2.7. Paging query

Just set the starting position and the maximum number of query data. Note that the setting of the starting position is not included.

@Test
public void query7(){
    Session session = null;
    try {
        session = SessionUtil.getSession();
        List<TBook> list = session.createQuery("from TBook")
                .setFirstResult(0).setMaxResults(4).list();
        for(TBook book : list){
            System.out.println(book);
        }
    } catch (Exception e) {
        e.printStackTrace();
    }finally{
        SessionUtil.close(session);
    }
}

Result:

TBook [id=1, author = Cao Xueqin, BName = Dream of Red Mansions, BPrice=60, pubDate=2017-06-10 14:51:57.0]
TBook [id=2, author = Sima Guang, BName = Zizhi Tongjian, BPrice=63, pubDate=2017-06-10 14:51:57.0]
TBook [id=3, author = Jinyong, BName = Heroes of Archery, BPrice=73, pubDate=2017-06-10 14:51:57.0]
TBook [id=4, author = Marquez, BName = 100 years of loneliness, BPrice=56, pubDate=2017-06-10 14:51:57.0]

2.8. count of aggregated queries

@Test
public void query8(){
    Session session = null;
    try {
        session = SessionUtil.getSession();
        //Object count = session.createQuery("select count(*) from TBook").uniqueResult();
        Number count = (Number)session.createQuery("select count(*) from TBook").uniqueResult();
        System.out.println(count.intValue());
    } catch (Exception e) {
        e.printStackTrace();
    }finally{
        SessionUtil.close(session);
    }
}

2.9. avg of aggregated queries

@Test
public void query9(){
    Session session = null;
    try {
        session = SessionUtil.getSession();
        Number priceavg = (Number)session.createQuery("select avg(BPrice) from TBook").uniqueResult();
        System.out.println(priceavg.doubleValue());
    } catch (Exception e) {
        e.printStackTrace();
    }finally{
        SessionUtil.close(session);
    }
}

2.10. Sort Query

@Test
public void query10(){
    Session session = null;
    try {
        session = SessionUtil.getSession();
        List<TBook> list = session.createQuery("from TBook order by BPrice").list();
        for(TBook book : list){
            System.out.println(book);
        }
    } catch (Exception e) {
        e.printStackTrace();
    }finally{
        SessionUtil.close(session);
    }
}

Result:

TBook [id=4, author = Marquez, BName = 100 years of loneliness, BPrice=56, pubDate=2017-06-10 14:51:57.0]
TBook [id=1, author = Cao Xueqin, BName = Dream of Red Mansions, BPrice=60, pubDate=2017-06-10 14:51:57.0]
TBook [id=5, author = Jane Austen, BName = pride and prejudice, BPrice = 60, pubDate = 2017-06-104:51:57.0]
TBook [id=2, author = Sima Guang, BName = Zizhi Tongjian, BPrice=63, pubDate=2017-06-10 14:51:57.0]
TBook [id=6, author = Luo Guanzhong, BName = Journey to the West, BPrice=68, pubDate=2017-06-10 14:51:57.0]
TBook [id=3, author = Jinyong, BName = Heroes of Archery, BPrice=73, pubDate=2017-06-10 14:51:57.0]

2.11. Group Query

@Test
public void query11(){
    Session session = null;
    try {
        session = SessionUtil.getSession();
        List<Object[]> list = session.createQuery("select t.category.category,count(t.BName) from TBook t "
                + "group by t.category.category").list();

        for (Object[] book : list) {
            System.out.println(book[0]+"-----"+book[1]);
        }
    } catch (Exception e) {
        e.printStackTrace();
    }finally{
        SessionUtil.close(session);
    }
}

Result:

History - - - 1
 Literature - - - 2
 Knight-errant - - - 1
 Science Fiction - - - 1
 Love - - - 1