HQL使用大全

package xy.Test;

import Java.util.Date; import java.util.List;

import org.hibernate.Query; import org.hibernate.SQLQuery; import org.hibernate.Session; import org.hibernate.cfg.AnnotationConfiguration; import org.hibernate.tool.hbm2ddl.SchemaExport;

import xy.Utility.HibernateAnnotationUtil; import xy.model.Category; import xy.model.Msg; import xy.model.Topic;

public class TestClass  {

 public static void Insert()  {   Session session = HibernateAnnotationUtil.getSessionFactory().getCurrentSession();   session.beginTransaction();      // 设置10个板块   for(int i = 0;i<10;i++)   {    Category c = new Category();    c.setName("c" + i);    session.save(c);   }      // 给第一个板块设置10个主题   for(int i = 0;i<10;i++)   {    Category c = (Category)session.get(Category.class, 1);    Topic t = new Topic();    t.setCategory(c);    t.setTitle("t" + i);    t.setCreateTime(new Date());    session.save(t);   }      // 给第一个板块设置10条回复   for(int i = 0;i<10;i++)   {    Topic t = (Topic)session.get(Topic.class, 1);    Msg m = new Msg();    m.setContent("m"+i);    m.setTopic(t);    session.save(m);   }   session.getTransaction().commit();  }    public static void GetList()  {   Session s = HibernateAnnotationUtil.getSessionFactory().getCurrentSession();   s.beginTransaction();   Query q = s.createQuery("select c from Category c");   List<Category> list = (List<Category>)q.list();   for(Category c : list)   {    System.out.println(c.getName());   }   s.getTransaction().commit();  }      public static void GetList1()  {   Session s = HibernateAnnotationUtil.getSessionFactory().getCurrentSession();   s.beginTransaction();   Query q = s.createQuery("select c from Category c where c.name > 'c5'");   List<Category> list = (List<Category>)q.list();   for(Category c : list)   {    System.out.println(c.getName());   }   s.getTransaction().commit();  }    public static void GetOrderedList()  {   Session s = HibernateAnnotationUtil.getSessionFactory().getCurrentSession();   s.beginTransaction();   Query q = s.createQuery("select c from Category c order by c.name desc");   List<Category> list = (List<Category>)q.list();   for(Category c : list)   {    System.out.println(c.getName());   }   s.getTransaction().commit();  }    public static void GetOrderedList2()  {   Session s = HibernateAnnotationUtil.getSessionFactory().getCurrentSession();   s.beginTransaction();   Query q = s.createQuery("select distinct c from Category c order by c.name desc");   List<Category> list = (List<Category>)q.list();   for(Category c : list)   {    System.out.println(c.getName());   }   s.getTransaction().commit();  }    public static void GetOrderedList3()  {   Session s = HibernateAnnotationUtil.getSessionFactory().getCurrentSession();   s.beginTransaction();   Query q = s.createQuery("select c from Category c where c.id > :min and c.id< :max");   //q.setParameter("min", 2);   //q.setParameter("max", 8);   q.setInteger("min", 2);   q.setInteger("max", 8);   List<Category> list = (List<Category>)q.list();   for(Category c : list)   {    System.out.println(c.getName());   }   s.getTransaction().commit();  }    // 可以用于分页  public static void GetOrderedListPage()  {   Session s = HibernateAnnotationUtil.getSessionFactory().getCurrentSession();   s.beginTransaction();   Query q = s.createQuery("select c from Category c");   q.setMaxResults(5);   q.setFirstResult(2);   List<Category> list = (List<Category>)q.list();   for(Category c : list)   {    System.out.println(c.getName());   }   s.getTransaction().commit();  }    public static void GetNameList()  {   Session s = HibernateAnnotationUtil.getSessionFactory().getCurrentSession();   s.beginTransaction();   Query q = s.createQuery("select c.name from Category c");   List<String> list = (List<String>)q.list();   for(String str : list)   {    System.out.println(str);   }   s.getTransaction().commit();  }    public static void GetIdAndNameList()  {   Session s = HibernateAnnotationUtil.getSessionFactory().getCurrentSession();   s.beginTransaction();   Query q = s.createQuery("select c.id, c.name from Category c");   List<Object[]> list = (List<Object[]>)q.list();   for(Object[] o : list)   {    System.out.println(o[0] + "........" + o[1]);   }   s.getTransaction().commit();  }    // 体现了对象之间的关联  public static void GetTopic()  {   Session s = HibernateAnnotationUtil.getSessionFactory().getCurrentSession();   s.beginTransaction();   Query q = s.createQuery("select t.title from Topic t where t.category.id = 1");   List<String> list = (List<String>)q.list();   for(String str : list)   {    System.out.println(str);   }   s.getTransaction().commit();  }      // 体现了对象之间的关联  public static void GetMsg()  {   Session s = HibernateAnnotationUtil.getSessionFactory().getCurrentSession();   s.beginTransaction();   Query q = s.createQuery("select m from Msg m where m.topic.category.id = 1");   List<Msg> list = (List<Msg>)q.list();   for(Msg m : list)   {    System.out.println(m.getContent());   }   s.getTransaction().commit();  }    public static void Join()  {   Session s = HibernateAnnotationUtil.getSessionFactory().getCurrentSession();   s.beginTransaction();   Query q = s.createQuery("select c.name,t.title from Topic t join t.category c");   List<Object[]> list = (List<Object[]>)q.list();   for(Object[] m : list)   {    System.out.println(m[0]+ "...."+m[1]);   }   s.getTransaction().commit();  }      public static void UniqueResult()  {   Session s = HibernateAnnotationUtil.getSessionFactory().getCurrentSession();   s.beginTransaction();   Query q = s.createQuery("select m from Msg m where m = :MsgResult");   Msg m = new Msg();   m.setId(1);   q.setParameter("MsgResult", m);      // 会使用UniqueResult   Msg msg = (Msg)q.uniqueResult();   System.out.println(msg.getContent());   s.getTransaction().commit();     }    public static void GetCount()  {   Session s = HibernateAnnotationUtil.getSessionFactory().getCurrentSession();   s.beginTransaction();   Query q = s.createQuery("select count(*) from Msg");   long count = (Long)q.uniqueResult();   System.out.println(count);  }    public static void MinOrMaxList()  {   Session s = HibernateAnnotationUtil.getSessionFactory().getCurrentSession();   s.beginTransaction();   Query q = s.createQuery("select max(m.id),min(m.id),avg(m.id),sum(m.id) from Msg m");   Object[] o = (Object[])q.uniqueResult();   System.out.println(o[0] + "-" + o[1] + "-" + o[2] + "-" + o[3]);  }      public static void BetweenList()  {   Session s = HibernateAnnotationUtil.getSessionFactory().getCurrentSession();   s.beginTransaction();   Query q = s.createQuery("select m from Msg m where m.id between 3 and 5");      List<Msg> list = (List<Msg>)q.list();   for(Msg m : list)   {    System.out.println(m.getContent());   }   s.getTransaction().commit();  }    public static void InList()  {   Session s = HibernateAnnotationUtil.getSessionFactory().getCurrentSession();   s.beginTransaction();   Query q = s.createQuery("select m from Msg m where m.id in (3,4,5)");      List<Msg> list = (List<Msg>)q.list();   for(Msg m : list)   {    System.out.println(m.getContent());   }   s.getTransaction().commit();  }    public static void IsNotNull()  {   Session s = HibernateAnnotationUtil.getSessionFactory().getCurrentSession();   s.beginTransaction();   Query q = s.createQuery("select m from Msg m where m.content is not null");      List<Msg> list = (List<Msg>)q.list();   for(Msg m : list)   {    System.out.println(m.getContent());   }   s.getTransaction().commit();  }    // is not empty针对集合  public static void IsNotEmpty()  {   Session s = HibernateAnnotationUtil.getSessionFactory().getCurrentSession();   s.beginTransaction();   Query q = s.createQuery("select t from Topic t where t.listMsg is not empty");      List<Topic> list = (List<Topic>)q.list();   for(Topic m : list)   {    System.out.println(m.getId());   }   s.getTransaction().commit();  }    //  %表示多个匹配  //  _表示一个匹配  public static void Like1()  {   Session s = HibernateAnnotationUtil.getSessionFactory().getCurrentSession();   s.beginTransaction();   Query q = s.createQuery("select t from Topic t where t.title like '%5'");      List<Topic> list = (List<Topic>)q.list();   for(Topic m : list)   {    System.out.println(m.getId());   }   s.getTransaction().commit();  }      public static void Like2()  {   Session s = HibernateAnnotationUtil.getSessionFactory().getCurrentSession();   s.beginTransaction();   Query q = s.createQuery("select t from Topic t where t.title like '_5'");      List<Topic> list = (List<Topic>)q.list();   for(Topic m : list)   {    System.out.println(m.getId());   }   s.getTransaction().commit();  }    public static void SomeMethods()  {   Session s = HibernateAnnotationUtil.getSessionFactory().getCurrentSession();   s.beginTransaction();   Query q = s.createQuery("select lower(t.title),"          +"upper(t.title),"         +"trim(t.title),"         +"concat(t.title,'***'),"         +"length(t.title)"         +" from Topic t");      List<Object[]> list = (List<Object[]>)q.list();   for(Object[] m : list)   {    System.out.println(m[0] + "...." + m[1] + "....." +m[3]);   }   s.getTransaction().commit();  }    // 取数据库服务器时间  // 为什么要取数据库时间呢？因为可能多台服务器围绕一个数据库服务器  public static void DateMethods()  {   Session s = HibernateAnnotationUtil.getSessionFactory().getCurrentSession();   s.beginTransaction();   Query q = s.createQuery("select current_date,current_time,current_timestamp,t.id from Topic t");      List<Object[]> list = (List<Object[]>)q.list();   for(Object[] m : list)   {    System.out.println(m[0] + "...." + m[1] + "....." +m[2] + "......." + m[3]);   }   s.getTransaction().commit();  }      // 取数据库服务器时间  // 为什么要取数据库时间呢？因为可能多台服务器围绕一个数据库服务器  public static void DateMethods2()  {   Session s = HibernateAnnotationUtil.getSessionFactory().getCurrentSession();   s.beginTransaction();   Query q = s.createQuery("select t.id,t.title from Topic t where t.createTime <= :Date");   q.setParameter("Date", new Date());      List<Object[]> list = (List<Object[]>)q.list();   for(Object[] m : list)   {    System.out.println(m[0] + "...." + m[1]);   }   s.getTransaction().commit();  }    // Group by 后面必须出现在select里面  public static void GroupBy()  {   Session s = HibernateAnnotationUtil.getSessionFactory().getCurrentSession();   s.beginTransaction();   Query q = s.createQuery("select t.title,count(*) from Topic t group by t.title");      List<Object[]> list = (List<Object[]>)q.list();   for(Object[] m : list)   {    System.out.println(m[0] + "...." + m[1]);   }   s.getTransaction().commit();  }    // having后面跟的是函数，否则的话用where就行了  public static void HavingMehtods()  {   Session s = HibernateAnnotationUtil.getSessionFactory().getCurrentSession();   s.beginTransaction();   Query q = s.createQuery("select t.title,count(*) from Topic t group by t.title having count(*) >= 1");      List<Object[]> list = (List<Object[]>)q.list();   for(Object[] m : list)   {    System.out.println(m[0] + "...." + m[1]);   }   s.getTransaction().commit();  }    // 查询语句嵌套  public static void Embedded()  {   Session s = HibernateAnnotationUtil.getSessionFactory().getCurrentSession();   s.beginTransaction();   Query q = s.createQuery("select t.title,t.id from Topic t where t.id < (select avg(t.id) from Topic t)");      List<Object[]> list = (List<Object[]>)q.list();   for(Object[] m : list)   {    System.out.println(m[0] + "...." + m[1]);   }   s.getTransaction().commit();  }      // All指的是取到的所有值  // < All指的是小于取到的所有值，也就是取最小的值  public static void AllMethods()  {   Session s = HibernateAnnotationUtil.getSessionFactory().getCurrentSession();   s.beginTransaction();   Query q = s.createQuery("select t.title,t.id from Topic t where t.id < ALL(select t.id from Topic t)");      List<Object[]> list = (List<Object[]>)q.list();   for(Object[] m : list)   {    System.out.println(m[0] + "...." + m[1]);   }   s.getTransaction().commit();  }    // exsit方法的用法  // 查找没有回复的主题  // 与IsNotEmpty()效果一样  // exists比in效率高  public static void ExistOrNot()  {   Session s = HibernateAnnotationUtil.getSessionFactory().getCurrentSession();   s.beginTransaction();   Query q = s.createQuery("select t.title,t.id from Topic t where not exists (select m.id from Msg m where m.topic.id = t.id)");   List<Object[]> list = (List<Object[]>)q.list();   for(Object[] m : list)   {    System.out.println(m[0] + "...." + m[1]);   }   s.getTransaction().commit();  }      public static void Update()  {   Session s = HibernateAnnotationUtil.getSessionFactory().getCurrentSession();   s.beginTransaction();   Query q = s.createQuery("update Topic t set t.title = upper(t.title)");   q.executeUpdate();   s.getTransaction().commit();  }      public static void Delete()  {   Session s = HibernateAnnotationUtil.getSessionFactory().getCurrentSession();   s.beginTransaction();   Query q = s.createQuery("delete Msg m where m.id = 1");   q.executeUpdate();   s.getTransaction().commit();  }    public static void GetQueryWithQueryName()  {   Session s = HibernateAnnotationUtil.getSessionFactory().getCurrentSession();   s.beginTransaction();   Query q = s.getNamedQuery("select.certainTopic");   q.setParameter("id", 5);   Topic t = (Topic)q.uniqueResult();   System.out.println(t.getTitle());   s.getTransaction().commit();  }    // NativeSQL  // 这里from的是表名，不是对象名  public static void NativeSQL()  {   Session s = HibernateAnnotationUtil.getSessionFactory().getCurrentSession();   s.beginTransaction();   SQLQuery q = s.createSQLQuery("select * from category limit 2,5").addEntity(Category.class);   List<Category> list = (List<Category>)q.list();   for(Category c : list)   {    System.out.println(c.getName());   }      s.getTransaction().commit();  }  

}