（15）QL1：asc、desc、获取一定范围（用占位符）、获取一条记录中的若干字段、多次导航、左外连接、uniqueResult、聚合函数、is null

板块和主题：一对多 一个板块对应多个主题，一个主题只属于一个板块
* 主题和回复：一对多 一个主题对应多个回复 一个回复只属于一个主题*
* 现在只是建立单向联系*
Category类

@Entity
public class Category {

    private int id;
    private String name;

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

}

Topic类

@Entity
public class Topic {

    private int id;
    private String name;
    private Category category;
    @Id
    @GeneratedValue
    public int getId() {
        return id;
    }

    @ManyToOne//(fetch=FetchType.LAZY)
    @JoinColumn(name="gid")
    public Category getCategory() {
        return category;
    }
    public void setCategory(Category category) {
        this.category = category;
    }

}

Msg类

@Entity 


public class Msg {

    private int id;
    private String name;
    private Topic topic;


    @Id //必须加在getId上面
    @GeneratedValue
    public int getId() {
        return id;
    }
    @ManyToOne//(fetch=FetchType.LAZY)
    @JoinColumn(name="tid")
    public Topic getTopic() {
        return topic;
    }   
}

Test

    //获取所有的分类
    /*
       select  category0_.id as id0_,category0_.name as name0_  from Category category0_
     */
    @Test
    public void HQ_1() {

        Session session=sf.getCurrentSession();
        session.beginTransaction();
        Query q=session.createQuery("from Category");
        List<Category> categories=(List<Category>)q.list();//Return the query results as a List.但是这个list并没有用泛型，强制转换为Category
        for(Category c:categories){
            System.out.println(c.getName());
        }
        session.getTransaction().commit();
    }
    /*
     * 查询name>'c5'的板块
     */
     /*
       select  category0_.id as id0_,category0_.name as name0_  from Category category0_ where  category0_.name>'c5' 
     */
    @Test
    public void HQ_2() {

        Session session=sf.getCurrentSession();
        session.beginTransaction();
        Query q=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();
    }
    /*
     * 降序获取所有的板块
     * select  category0_.id as id0_,category0_.name as name0_  from Category category0_  order by category0_.name desc
     */
    @Test
    public void HQ_3() {

        Session session=sf.getCurrentSession();
        session.beginTransaction();
        Query q=session.createQuery("from Category c order by c.name desc");
        List<Category> categories=(List<Category>)q.list();
        for(Category c:categories){
            System.out.println(c.getName());
        }
        session.getTransaction().commit();
    }

    /*
     * 降序获取所有的板块
     * select  category0_.id as id0_,category0_.name as name0_  from Category category0_  order by category0_.name desc
     */
    @Test
    public void HQ_4() {

        Session session=sf.getCurrentSession();
        session.beginTransaction();
        Query q=session.createQuery("from Category c order by c.name desc");
        List<Category> categories=(List<Category>)q.list();
        for(Category c:categories){
            System.out.println(c.getName());
        }
        session.getTransaction().commit();
    }

    /*
     * 获取一定确定范围的数据：用占位符  :变量名
     * select  category0_.id as id0_,category0_.name as name0_  from Category category0_  where category0_.id>? and category0_.id<?
     */
    @Test
    public void HQ_5() {

        Session session=sf.getCurrentSession();
        session.beginTransaction();
        //方法一：
        /*Query q=session.createQuery("from Category c where c.id> :min and c.id< :max");
        q.setInteger("min", 2);
        q.setInteger("max", 8);
        */
        //方法二：
        Query q=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.getName());
        }
        session.getTransaction().commit();
    }
    /*
     *获取一定确定范围的数据：类似于jdbc
     * select  category0_.id as id0_,category0_.name as name0_  from Category category0_  where category0_.id>? and category0_.id<?
     */
    @Test
    public void HQ_6() {

        Session session=sf.getCurrentSession();
        session.beginTransaction();
        Query q=session.createQuery("from Category c where c.id>? and c.id<?");
        q.setParameter(0, 2);
        q.setParameter(1, 8);
        List<Category> categories=(List<Category>)q.list();
        for(Category c:categories){
            System.out.println(c.getName());
        }
        session.getTransaction().commit();
    }

    /*
     *分页【当用户查询某主题下的帖子时，并不是将所有的帖子都显示，那样做会加重服务器的负担，也没这必要】
     * select  category0_.id as id0_,category0_.name as name0_  from Category category0_  where category0_.id>? and category0_.id<?
     */
    @Test
    public void HQ_7() {

        Session session=sf.getCurrentSession();
        session.beginTransaction();
        Query q=session.createQuery("from Category c order by c.name desc");
        q.setMaxResults(8);//Set the maximum number of rows to retrieve返回检索行最大值
        q.setFirstResult(2);
        List<Category> categories=(List<Category>)q.list();
        for(Category c:categories){
            System.out.println(c.getName());
        }
        session.getTransaction().commit();
    }

    /*
     *用select获取object数组的集合。
     * select  category0_.id as col_0_0_,,category0_.name as col_1_0_   from Category category0_  order by category0_.name desc
     */
    @Test
    public void HQ_8() {

        Session session=sf.getCurrentSession();
        session.beginTransaction();
        Query q=session.createQuery("select c.id,c.name from Category c order by c.name desc");
        List<Object[]> categories=(List<Object[]>)q.list();//当用select 获取特定字段的值时，在返回的多个记录中，也就是list。list中每个元素含有多个字段，可以是任意类型，
        //为了方便书写，可以将每个类型提升为object类型。简单理解就是每个元素是object数组。

        for(Object[] o:categories){
            System.out.println(o[0]+"---"+o[1]);//object数组长度是确定的，在select语句中，有
        }
        session.getTransaction().commit();
    }

    /*
     * 一次导航topic->catatory
     * 因为fetch在manytoone默认是eager，所以会获取category的信息【无论是否打印种类的名称】，这和写不写where是无关的，写where只是限定取哪些分类的主题信息
     * 当topic设置(fetch=FetchType.LAZY)时，不会发第二条select语句
     * select  category0_.id as id0_,category0_.name as name0_  from Category category0_  order by category0_.name desc
     */
    @Test
    public void HQ_9() {

        Session session=sf.getCurrentSession();
        session.beginTransaction();
        Query q=session.createQuery("from Topic  t where t.category.id=1");//因为在topic中有向category的导航，可以确定这个主题是哪个分类，
        List<Topic> topics=(List<Topic>)q.list();
        for(Topic c:topics){

            System.out.println(c.getCategory().getName());
        }
        session.getTransaction().commit();
    }
    /*
     * 二次导航msg->topic->catagory
     * 因为均为eager，所以会发3条select语句
     */
    @Test
    public void HQ_10() {

        Session session=sf.getCurrentSession();
        session.beginTransaction();
        Query q=session.createQuery("from Msg m where m.topic.category=1");//因为在topic中有向category的导航，可以确定这个主题是哪个分类，
        List<Msg> msgs=(List<Msg>)q.list();
        for(Msg m:msgs){
            System.out.println(m.getTopic().getCategory().getName());
        }
        session.getTransaction().commit();
    }

    /*
     * 
     * 可以手动建立连接left right join
     * topic0_.name as col_0_0_, category1_.name as col_1_0_  from  Topic topic0_  left outer join  Category category1_ on topic0_.category_id=category1_.id
     */
    @Test
    public void HQ_11() {

        Session session=sf.getCurrentSession();
        session.beginTransaction();
        Query q=session.createQuery("select t.name,c.name from Topic t left join t.category c");//因为在topic中有向category的导航，可以确定这个主题是哪个分类，
        List<Object[]> objs=(List<Object[]>)q.list();
        for(Object[] obj:objs){
            System.out.println(obj[0]+"---"+obj[1]);
        }
        session.getTransaction().commit();
    }

    /*
     *uniqueResult:Convenience method to return a single instance that matches the query, or null if the query returns no results
     *返回<=1个查询结果，可以简化打印操作。
     */
    @Test
    public void HQ_12() {

        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.getName());
        session.getTransaction().commit();
    }

    /*
     * 可以使用聚合函数
     *   select count(*) as col_0_0_  from   Msg msg0_
     */
    @Test
    public void HQ_13() {

        Session session=sf.getCurrentSession();
        session.beginTransaction();
        Query q=session.createQuery("select count(*) from Msg");

        long  count= (Long) q.uniqueResult();
        System.out.println(count);
        session.getTransaction().commit();
    }

    /*
     * 可以使用聚合函数
     *    select max(msg0_.id) as col_0_0_, min(msg0_.id) as col_1_0_,avg(msg0_.id) as col_2_0_,sum(msg0_.id) as col_3_0_ from  Msg msg0_
     */
    @Test
    public void HQ_14() {

        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");
        List<Object[]> lists=(List<Object[]>)q.list();
        for(Object[] objs:lists){
            System.out.println(objs[0]+"--"+objs[1]+"--"+objs[2]+"--"+objs[3]);
        }

        session.getTransaction().commit();
    }

    /*
     * 因为msg fetch=eager所以会将topic select出来，topic也是eager类型，所以会把category select出来
     * 
      select  msg0_.id as id1_, msg0_.name as name1_,msg0_.topic_id as topic3_1_ from  Msg msg0_   where    msg0_.id in (3 , 4 , 5）

        select
        topic0_.id as id2_1_,topic0_.category_id as category3_2_1_,topic0_.name as name2_1_,category1_.id as id0_0_, category1_.name as name0_0_   
        from Topic topic0_ left outer join  Category category1_ on topic0_.category_id=category1_.id         
        where
        topic0_.id=?
     *   
     */
    @Test
    public void HQ_15() {

        Session session=sf.getCurrentSession();
        session.beginTransaction();
        Query q=session.createQuery("from Msg m where m.id in(3,4,5)");
        List<Msg> msgs=(List<Msg>)q.list();
        for(Msg objs:msgs){
            System.out.println(objs.getName());
        }

        session.getTransaction().commit();
    }
    //is null 测试某个属性是否为空
    @Test
    public void HQ_16() {

        Session session=sf.getCurrentSession();
        session.beginTransaction();
        Query q=session.createQuery("from Msg m where m.name is not null");
        List<Msg> msgs=(List<Msg>)q.list();
        for(Msg objs:msgs){
            System.out.println(objs.getName());
        }

        session.getTransaction().commit();
    }