学习脚步—HQL查询语句(自己学习使用)

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.AfterClass;

import org.junit.BeforeClass;

import org.junit.Test;

public class HibernateQLTest {

       private static SessionFactory sf;

       @BeforeClass

       public static void beforeClass() {

              sf = new AnnotationConfiguration().configure().buildSessionFactory();

       }

       @AfterClass

       public static void afterClass() {

              sf.close();

       }

       @Test

       public void testSchemaExport() {

              new SchemaExport(new AnnotationConfiguration().configure()).create(false, true);

       }

       @Test

       public void testSave() {

              Session session = sf.openSession();

              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.setCont("m" + i);

                     m.setTopic(t);

                     session.save(m);

              }

              session.getTransaction().commit();

              session.close();

       }

       @Test

       public void testHQL_01() {

              Session session = sf.openSession();

              session.beginTransaction();

Query q = session.createQuery("from Category"); //EJBQL语句，也可称作HQL，因为前者是后者的一个子集。面向对象的查询语言，所以Category是类名。

              List<Category> categories = (List<Category>)q.list();//将结果查询出来并组装成对象放到list集合中，要做一个类型转换。

              for(Category c : categories) {

                     System.out.println(c.getName());

              }

              session.getTransaction().commit();

              session.close();

       }

       @Test

       public void testHQL_02() {

              Session session = sf.openSession();

              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();

              session.close();

       }

       @Test

       public void testHQL_03() {

              Session session = sf.openSession();

              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();

              session.close();  

       }

       @Test

       public void testHQL_04() {

              Session session = sf.openSession();

              session.beginTransaction();

              Query q = session.createQuery("select distinct c from Category c order by c.name desc");//倒叙排列，distinct c是指对象不会重复，就是主键不会重复，这里写不写无所谓。

              List<Category> categories = (List<Category>)q.list();

              for(Category c : categories) {

                     System.out.println(c.getName());

              }

              session.getTransaction().commit();

              session.close();

       }

       @Test

       public void testHQL_05() {

              Session session = sf.openSession();

              session.beginTransaction();

              /*Query q = session.createQuery("from Category c where c.id > :min and c.id < :max");//:是占位符，min是占位符的名称

              //q.setParameter("min", 2); 自动转换格式

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

              q.setInteger("min", 2); 自己指定类型，这句代码的返回值是querry本身，所以就有了下面的链式编程

              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.getId() + "-" + c.getName());

              }

              session.getTransaction().commit();

              session.close();

       }

       @Test 

       public void testHQL_06() {

              Session session = sf.openSession();

              session.beginTransaction();

              Query q = session.createQuery("from Category c where c.id > ? and c.id < ?");

              q.setParameter(0, 2)

.setParameter(1, 8);     //简单解释了链式编程

//            q.setParameter(1, 8);

              List<Category> categories = (List<Category>)q.list();

              for(Category c : categories) {

                     System.out.println(c.getId() + "-" + c.getName());

              }

              session.getTransaction().commit();

              session.close();

       }

       //分页

       @Test

       public void testHQL_07() {

              Session session = sf.openSession();

              session.beginTransaction();

       //oracle中需要三层嵌套，mysql中需要使用limit

              Query q = session.createQuery("from Category c order by c.name desc");

              q.setMaxResults(4);        //设定最大的结果集是4，即每页是4条

              q.setFirstResult(2);         //从第2条开始取       运行结果:倒叙排列结果是9 8 7 ……1

                                //那么按照约束则最终显示的是：8 7 6 5

              List<Category> categories = (List<Category>)q.list();

              for(Category c : categories) {

                     System.out.println(c.getId() + "-" + c.getName());

              }

              session.getTransaction().commit();

              session.close();

       }

       @Test

       public void testHQL_08() {

              Session session = sf.openSession();

              session.beginTransaction();

              Query q = session.createQuery("select c.id,  c.name from Category c order by c.name desc");

       //以前都是取的是对象，现在是两个字段，返回是一个list，list里面装了Object类型的数组，

       1   c0    一条记录是一个数组，左边是数组展开里面的内容

       2   c1    多个数组组合到一起可以看做是一个list                   

              List<Object[]> categories = (List<Object[]>)q.list();

              for(Object[] o : categories) {

                     System.out.println(o[0] + "-" + o[1]);

              }

              session.getTransaction().commit();

              session.close();

       }

       //设定fetch type 为lazy后将不会有第二条sql语句

       @Test

       public void testHQL_09() {

              Session session = sf.openSession();

              session.beginTransaction();

              Query q = session.createQuery("from Topic t where t.category.id = 1"); //两张表联合查询

       //many-to-one的映射（fetchType类型默认是EAGER）最后结果是输出category的id为1的//topic的titile属性

              List<Topic> topics = (List<Topic>)q.list();

              for(Topic t : topics) {

                     System.out.println(t.getTitle());

                     System.out.println(t.getCategory().getName());    //1+N问题

              }

              session.getTransaction().commit();

              session.close();

       }

       //设定fetch type 为lazy后将不会有第二条sql语句

       @Test

       public void testHQL_10() {

              Session session = sf.openSession();

              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());

              }

              session.getTransaction().commit();

              session.close();

       }

       @Test

       public void testHQL_11() {

              Session session = sf.openSession();

              session.beginTransaction();

              Query q = session.createQuery("from Msg m where m.topic.category.id = 1");//导航再导航

//其实相当于是取了板块为1的里面的所有的帖子

//many-to-one设置的是导航，不设置导航QL语句就会麻烦，需要表连接，可以如此写//m.topic.category.id就是因为设置了导航