hibernate-HQL（query language）-16

几种查询方式：

1. nativeSQL
   缺点：不能跨平台
   优点：需要存储过程、复杂查询的时候 重要
2. HQL(hibernate query language)
   把hql语言 转换为方言
3. EJB QL(JP QL 1.0)
   可以认为是hql的子集
4. QBC
5. QBE

public class HibernateORMappingTest {
    public static SessionFactory sf = null;

    @BeforeClass
    public static void beforeClass(){
        Configuration cfg = new AnnotationConfiguration();
        sf = cfg.configure().buildSessionFactory();
    }

    @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.setTitle("t"+i);
            t.setCreateDate(new Date());
            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 = session.createQuery(" from Category ");
        List<Category> categorys = (List<Category>)q.list();
        for(Category c:categorys){
            System.out.println(c.getName());
        }
        session.getTransaction().commit();
    }

    @Test
    public void testHQL_02(){
        Session session = sf.getCurrentSession();
        session.beginTransaction();
        Query q = session.createQuery(" from Category c where c.name>'c5' ");
        List<Category> categorys = (List<Category>)q.list();
        for(Category c:categorys){
            System.out.println(c.getName());
        }
        session.getTransaction().commit();
    }

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

    @Test
    public void testHQL_04(){
        Session session = sf.getCurrentSession();
        session.beginTransaction();
        //  对象不重复  即主键不同
        Query q = session.createQuery(" select distinct c from Category c order by c.name desc ");
        List<Category> categorys = (List<Category>)q.list();
        for(Category c:categorys){
            System.out.println(c.getName());
        }
        session.getTransaction().commit();
    }

    @Test
    public void testHQL_05(){
        Session session = sf.getCurrentSession();
        session.beginTransaction();
        Query q = session.createQuery("  from Category c where c.id > :min and c.id < :max ");
//        Query q = session.createQuery("  from Category c where c.id > :min and c.id < :max ")
//                .setParameter("min",2)
//                .setParameter("max",8);

//        q.setParameter("min",2);
//        q.setParameter("max",8);

        q.setInteger("min",2);
        q.setInteger("max",8);
        List<Category> categorys = (List<Category>)q.list();
        for(Category c:categorys){
            System.out.println(c.getName());
        }
        session.getTransaction().commit();
    }
    //分页
    @Test
    public void testHQL_07(){
        Session session = sf.getCurrentSession();
        session.beginTransaction();
        Query q = session.createQuery(" from Category c order by c.name desc ");
        q.setMaxResults(4);// 每页4条
        q.setFirstResult(2);// 从第二条开始
        List<Category> categorys = (List<Category>)q.list();
        for(Category c:categorys){
            System.out.println(c.getName());
        }
        session.getTransaction().commit();
    }

    @Test
    public void testHQL_08(){
        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[]> categorys = (List<Object[]>)q.list();
        for(Object[] o:categorys){
            System.out.println(o[1]);
        }
        session.getTransaction().commit();
    }

    // Topic中设定fetchtype为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.getTitle());
//            System.out.println(t.getCategory().getId());
        }
        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(((Msg) o).getCont());
        }
        session.getTransaction().commit();
    }

    // 为什么t.category不能写成 Category
    // 因为可能有多个成员变量类型都是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 ");

        for(Object o:q.list()){
            Object[] m = (Object[])o;
            System.out.println(m[0]+"-"+m[1]);
        }
        session.getTransaction().commit();
    }

    @Test
    public void testSchemaExport(){
        //  true  是否将建表语句输出到控制台   true  是否在数据库中建表
        new SchemaExport(new AnnotationConfiguration().configure()).create(true,true);
    }


    @AfterClass
    public static void AfterClass(){
        sf.close();

    }

}