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

}

目录
相关文章
|
Java 数据库连接
HQL数据查询(Hibernate推荐)
HQL数据查询(Hibernate推荐)
104 0
常用的Hql语句
本文主要讲常用的Hql语句
110 0
|
SQL XML 缓存
HQL的使用
HQL(Hibernate Query Language) 是面向对象的查询语言, 它和 SQL 查询语言有些相似. 在 Hibernate 提供的各种检索方式中, HQL 是使用最广的一种检索方式
275 0
|
SQL Java 数据库连接
|
SQL Java 数据库连接
HQL查询
一、前言        HQL(Hibernate QueryLanguage) 是面向对象的查询语言,它和SQL查询语言有些相似。 二、语法 1、Select/update/delete…… from …… where …… group by …… having ……orde...
1047 0
|
SQL Java 数据库连接

热门文章

最新文章