（16）QL2:模糊查询（%，_）、常用的函数、group by、having、子查询、update 、delete、native

    /*
     * 功能：获取没有回复的帖子
     * is empty and is not empty 判断一个集合是否为空
        select
        topic0_.id as id2_, topic0_.category_id as category3_2_, topic0_.name as name2_ 
        from Topic topic0_ 
        where
         not (exists (select msgs1_.id  from Msg msgs1_  where  topic0_.id=msgs1_.topic_id))
         将topic中的每条记录和msg进行比较(通过id比较),若存在，则说明有回帖，不选该帖子。当不存在时，才输出
     */
    @Test
    public void HQ_1() {

        Session session=sf.getCurrentSession();
        session.beginTransaction();
        Query q=session.createQuery("from Topic t where t.msgs is empty");
        List<Topic> topcis=(List<Topic>)q.list();
        for(Topic t:topcis){
            System.out.println(t.getName());
        }
        session.getTransaction().commit();
    }

     /*
      * 模糊查询：
      * %表示任意0个或者多个字符。可匹配任意类型和长度的字符

     */
    @Test
    public void HQ_2() {

        Session session=sf.getCurrentSession();
        session.beginTransaction();
        Query q=session.createQuery("from Topic t where t.name like '%5'");
        List<Topic> topcis=(List<Topic>)q.list();
        for(Topic t:topcis){
            System.out.println(t.getName());
        }
        session.getTransaction().commit();
    }
    /*
     * 模糊查询
     * 表示任意单个字符。匹配任意单个字符。
     * 
     */
    @Test
    public void HQ_3() {

        Session session=sf.getCurrentSession();
        session.beginTransaction();
        Query q=session.createQuery("from Topic t where t.name like '_5'");
        List<Topic> topcis=(List<Topic>)q.list();
        for(Topic t:topcis){
            System.out.println(t.getName());
        }
        session.getTransaction().commit();
    }

    /*
     * 对一些字段使用函数
     * 
     */
    @Test
    public void HQ_4() {

        Session session=sf.getCurrentSession();
        session.beginTransaction();
        Query q=session.createQuery("select lower(t.name),upper(t.name),trim(t.name),concat(t.name,'***'),length(t.name) from Topic t");
        List<Object[]> objs=(List<Object[]>)q.list();
        for(Object[] obj:objs){

            System.out.println(obj[0]+"--"+obj[1]+"--"+obj[2]+"--"+obj[3]+"--"+obj[4]);
        }
        session.getTransaction().commit();
    }

    /*
     * abs:求绝对值
     *sqrt:开平方根
     */
    @Test
    public void HQ_5() {

        Session session=sf.getCurrentSession();
        session.beginTransaction();

        Query q=session.createQuery("select abs(t.id),sqrt(t.id),mod(t.id,2) from Topic t");
        List<Object[]> topics=(List<Object[]>)q.list();

        for(Object[] obj:topics){
            System.out.println(obj[0]+"--"+obj[1]+"--"+obj[2]);
        }
        session.getTransaction().commit();
    }
    /*
     *
     * 经常用到
     */
    @Test
    public void HQ_6() {

        Session session=sf.getCurrentSession();
        session.beginTransaction();
        Query q=session.createQuery("select current_date,current_time,current_timestamp,t.id from Topic t");

        List<Object[]> ts=(List<Object[]>)q.list();
        for(Object[] obj:ts){
            System.out.println(obj[0]+"--"+obj[1]+"--"+obj[2]);//2017-11-04--16:51:25--2017-11-04 16:51:25.0
        }
        session.getTransaction().commit();
    }

    /*
     * group by:字段必须是select中的字段。
     */
    @Test
    public void HQ_7() {

        Session session=sf.getCurrentSession();
        session.beginTransaction();
        Query q=session.createQuery("select t.name,count(*) from Topic t group by t.name");//count(*)是一组中有几个主题

        List<Object[]> ts=(List<Object[]>)q.list();
        for(Object[] obj:ts){
            System.out.println(obj[0]+"--"+obj[1]);
        }
        session.getTransaction().commit();
    }

    /*
     *having是对分组后结果进行筛选,having中的字段也必须是select中的字段
     */
    @Test
    public void HQ_8() {

        Session session=sf.getCurrentSession();
        session.beginTransaction();
        Query q=session.createQuery("select t.name,count(*) from Topic t group by t.name having count(*)>=2");
        List<Object[]> categories=(List<Object[]>)q.list();
        for(Object[] o:categories){
            System.out.println(o[0]+"---"+o[1]);
        }
        session.getTransaction().commit();
    }

    /*
     * 子查询
     */
    @Test
    public void HQ_9() {

        Session session=sf.getCurrentSession();
        session.beginTransaction();
        Query q=session.createQuery("from Topic  t where t.id <(select avg(t.id) from Topic t)");
        List<Topic> topics=(List<Topic>)q.list();
        for(Topic c:topics){

            System.out.println(c.getName());
        }
        session.getTransaction().commit();
    }
    /*
     *all:所有
     */
    @Test
    public void HQ_10() {

        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)");
        List<Topic> topics=(List<Topic>)q.list();
        for(Topic m:topics){
            System.out.println(m.getName());
        }


        session.getTransaction().commit();
    }

    @Test
    public void HQ_11() {

        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)");
        List<Topic> topics=(List<Topic>)q.list();
        for(Topic t:topics){
            System.out.println(t.getName());
        }
        session.getTransaction().commit();
    }

    /*
     *update and delete 
     */
    @Test
    public void HQ_12() {

        Session session=sf.getCurrentSession();
        session.beginTransaction();
        Query q=session.createQuery("update Topic t set t.name=upper(t.name)");
        q.executeUpdate();

        q=session.createQuery("from Topic");
        List<Topic> topics=(List<Topic>)q.list();
        for(Topic t:topics){
            System.out.println(t.getName());
        }
        session.getTransaction().commit();
    }

    /*
     * Native:因为在hibernate.cfg.xml中设置的方言是  <property name="dialect">org.hibernate.dialect.MySQLDialect</property>
     * 所以可以用本地语言。
     * limit 2,4 从2开始，偏移4个
     */
    @Test
    public void HQ_13() {

        Session session=sf.getCurrentSession();
        session.beginTransaction();
        Query q=session.createSQLQuery("select * from category limit 2,4").addEntity(Category.class);//注意此时select中是实体名，并不是类名
        List<Category> cas=q.list();
        for(Category c:cas){
            System.out.println(c.getName());
        }

        session.getTransaction().commit();
    }