hibernate3第五章之hql

一、一些基本的hql语句

1.下面是论坛例子：板块(category)、帖子(topic)、回复(msg）、临时数据（msginfo)

import javax.persistence.*;@Entitypublic class Category {private int id;private String name;@Id@GeneratedValuepublic int getId() {return id;}public void setId(int id) {this.id = id;}public String getName() {return name;}public void setName(String name) {this.name = name;} }

import java.util.Date;import javax.persistence.*;@Entitypublic class Topic {private int id;private String title;private Category category;private Date createDate;@Id@GeneratedValuepublic int getId() {return id;}public void setId(int id) {this.id = id;}public String getTitle() {return title;}public void setTitle(String title) {this.title = title;}@ManyToOne(fetch=FetchType.LAZY)public Category getCategory() {return category;}public void setCategory(Category category) {this.category = category;}public Date getCreateDate() {return createDate;}public void setCreateDate(Date createDate) {this.createDate = createDate;}}

import javax.persistence.*;@Entitypublic class Msg {private int id;private String cont;private Topic topic;@Id@GeneratedValuepublic int getId() {return id;}public void setId(int id) {this.id = id;}public String getCont() {return cont;}public void setCont(String cont) {this.cont = cont;}@ManyToOnepublic Topic getTopic() {return topic;}public void setTopic(Topic topic) {this.topic = topic;}}

public class MsgInfo {private int id;private String cont;private String topicName; private String categoryName; public MsgInfo(int id,String cont,String topicName,String categoryName){super();this.id=id;this.cont=cont;this.topicName=topicName;this.categoryName=categoryName;}public String getTopicName() {return topicName;}public void setTopicName(String topicName) {this.topicName = topicName;}public String getCategoryName() {return categoryName;}public void setCategoryName(String categoryName) {this.categoryName = categoryName;}public int getId() {return id;}public void setId(int id) {this.id = id;}public String getCont() {return cont;}public void setCont(String cont) {this.cont = cont;}  }

import java.util.Date;import java.util.List;import org.hibernate.*;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 Modeltest {private static SessionFactory sf=null;@BeforeClasspublic static void beforeClass(){sf=new AnnotationConfiguration().configure().buildSessionFactory();}@Testpublic 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();}@Testpublic void testHQL_1(){Session session=sf.getCurrentSession();session.beginTransaction();//Query q=session.createQuery("from Category");//Query q=session.createQuery("from Category c where c.name >'c5'");//Query q=session.createQuery("from Category c order by c.name desc");Query q=session.createQuery("select distinct c from Category c order by c.name desc");//按对象查询，是根据对象ID查询的，distinct写不写都一样List<Category> categories=(List<Category>)q.list();for(Category c:categories){System.out.println(c.getName());}session.getTransaction().commit();}@Testpublic void testHQL_2(){Session session=sf.getCurrentSession();session.beginTransaction(); //Query q=session.createQuery("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);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.getName());}session.getTransaction().commit();}@Testpublic void testHQL_3(){Session session=sf.getCurrentSession();session.beginTransaction(); Query q=session.createQuery("from Category c order by c.name desc");q.setFirstResult(0);//从第几条记录开始q.setMaxResults(4);//每页显示多少个List<Category> categories=(List<Category>)q.list();for(Category c:categories){System.out.println(c.getName());}session.getTransaction().commit();}@Testpublic void testHQL_4(){Session session=sf.getCurrentSession();session.beginTransaction(); Query q=session.createQuery("select c.id,c.name from Category c ");List<Object[]> categories=(List<Object[]>)q.list();for(Object[] o:categories){System.out.println(o[0]+":"+o[1]);}session.getTransaction().commit();}//目前是lazy 会在用到category的时候再发SQL语句取category   如果是eager则直接全部取出@Testpublic void testHQL_5(){Session session=sf.getCurrentSession();session.beginTransaction(); Query q=session.createQuery("  from Topic t where t.category.id=1 ");List<Topic> topices=(List<Topic>)q.list();for(Topic t:topices){System.out.println(t.getTitle());System.out.println(t.getCategory().getName());}session.getTransaction().commit();}@Testpublic void testHQL_6(){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(m.getCont());}session.getTransaction().commit();}@Testpublic void testHQL_7(){Session session=sf.getCurrentSession();session.beginTransaction(); Query q=session.createQuery("select new com.wzy.model.MsgInfo(m.id,m.cont,m.topic.title,m.topic.category.name) from Msg m");for(Object o:q.list()){MsgInfo m=(MsgInfo)o;System.out.println(m.getCont());}session.getTransaction().commit();}@Testpublic void testHQL_8(){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();}@Testpublic void testHQL_9(){Session session=sf.getCurrentSession();session.beginTransaction(); Query q=session.createQuery("from Msg m where m= :MsgToSearch");Msg m= new Msg();m.setId(1);q.setParameter("MsgToSearch", m);Msg mResult=(Msg)q.uniqueResult();System.out.println(mResult.getCont());session.getTransaction().commit();}@Testpublic void testHQL_10(){Session session=sf.getCurrentSession();session.beginTransaction(); Query q=session.createQuery("select count(*) from Msg m");long count=(Long)q.uniqueResult();System.out.println(count);session.getTransaction().commit();}@Testpublic void testHQL_11(){Session session=sf.getCurrentSession();session.beginTransaction(); Query q=session.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]);session.getTransaction().commit();}@Testpublic void testHQL_12(){Session session=sf.getCurrentSession();session.beginTransaction(); //Query q=session.createQuery("from Msg m where m.id between 3 and 5");//Query q=session.createQuery("from Msg m where m.id in (3,4,7)");Query q=session.createQuery("from Msg m where m.cont is not null");for(Object  o :  q.list()){Msg m=(Msg)o;System.out.println(m.getCont()+":"+m.getId());}session.getTransaction().commit();}@Testpublic void testSchemaExport(){new SchemaExport(new AnnotationConfiguration().configure()).create(false, true);}@AfterClasspublic static void afterClass(){sf.close();}}

二、一些带聚合函数、子查询的hql语句

import javax.persistence.*;@Entitypublic class Category {private int id;private String name;@Id@GeneratedValuepublic int getId() {return id;}public void setId(int id) {this.id = id;}public String getName() {return name;}public void setName(String name) {this.name = name;} }

import java.util.ArrayList;import java.util.Date;import java.util.List;import javax.persistence.*;@Entitypublic class Topic {private int id;private String title;private Category category;private Date createDate;private List<Msg> msg=new ArrayList<Msg>();@OneToMany(mappedBy="topic")public List<Msg> getMsg() {return msg;}public void setMsg(List<Msg> msg) {this.msg = msg;}@Id@GeneratedValuepublic int getId() {return id;}public void setId(int id) {this.id = id;}public String getTitle() {return title;}public void setTitle(String title) {this.title = title;}@ManyToOne(fetch=FetchType.LAZY)public Category getCategory() {return category;}public void setCategory(Category category) {this.category = category;}public Date getCreateDate() {return createDate;}public void setCreateDate(Date createDate) {this.createDate = createDate;}}

import javax.persistence.*;@Entitypublic class Msg {private int id;private String cont;private Topic topic;@Id@GeneratedValuepublic int getId() {return id;}public void setId(int id) {this.id = id;}public String getCont() {return cont;}public void setCont(String cont) {this.cont = cont;}@ManyToOnepublic Topic getTopic() {return topic;}public void setTopic(Topic topic) {this.topic = topic;}}

import java.util.Date;import java.util.List;import org.hibernate.*;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 Modeltest {private static SessionFactory sf=null;@BeforeClasspublic static void beforeClass(){sf=new AnnotationConfiguration().configure().buildSessionFactory();}@Testpublic 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();}@Testpublic void testHQL_13(){Session session=sf.getCurrentSession();session.beginTransaction();  Query q=session.createQuery("from Topic t where t.msg is empty");for(Object  o :  q.list()){Topic t=(Topic)o;System.out.println(t.getTitle()+":"+t.getId());}session.getTransaction().commit();}@Testpublic void testHQL_14(){Session session=sf.getCurrentSession();session.beginTransaction();  //Query q=session.createQuery("from Topic t where t.title like '%5%'"); //%代表多个字符 ； _代表一个字符Query q=session.createQuery("from Topic t where t.title like '_5%'");for(Object  o :  q.list()){Topic t=(Topic)o;System.out.println(t.getTitle()+":"+t.getId());}session.getTransaction().commit();}@Testpublic void testHQL_15(){Session session=sf.getCurrentSession();session.beginTransaction();  //小写，大写，去空格，字符拼接，长度Query q=session.createQuery("select lower(t.title),upper(t.title),trim(t.title),concat(t.title,'***'),length(t.title) from Topic t");for(Object  o :  q.list()){Object[] arr=(Object[])o;System.out.println(arr[0]+":"+arr[1]+":"+arr[2]+":"+arr[3]+":"+arr[4]);}session.getTransaction().commit();}@Testpublic void testHQL_16(){Session session=sf.getCurrentSession();session.beginTransaction();   Query q=session.createQuery("select abs(t.id),sqrt(t.id),mod(t.id,2),current_date,current_time,current_timestamp from Topic t");for(Object  o :  q.list()){Object[] arr=(Object[])o;System.out.println(arr[0]+" - "+arr[1]+" - "+arr[2]+" - "+arr[3]+" - "+arr[4]+" - "+arr[5] );}session.getTransaction().commit();}@Testpublic void testHQL_17(){Session session=sf.getCurrentSession();session.beginTransaction();   Query q=session.createQuery("from Topic t where  t.createDate<:date");q.setParameter("date", new Date());for(Object  o :  q.list()){Topic t=(Topic)o;System.out.println(t.getCreateDate());}session.getTransaction().commit();}@Testpublic void testHQL_18(){Session session=sf.getCurrentSession();session.beginTransaction();   //Query q=session.createQuery("select t.title,count(*) from Topic t group by t.title  ");Query q=session.createQuery("select t.title,count(*) from Topic t group by t.title having count(*) >=1  ");for(Object  o :  q.list()){Object[] arr=(Object[])o;System.out.println(arr[0]+" - "+arr[1]);}session.getTransaction().commit();}@Testpublic void testHQL_19(){Session session=sf.getCurrentSession();session.beginTransaction();   Query q=session.createQuery("from Topic t where  t.id<(select avg(t.id) from Topic t)");for(Object  o :  q.list()){Topic t=(Topic)o;System.out.println(t.getCreateDate());}session.getTransaction().commit();}@Testpublic void testHQL_20(){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)");for(Object  o :  q.list()){Topic t=(Topic)o;System.out.println(t.getCreateDate());}session.getTransaction().commit();}@Testpublic void testHQL_21(){Session session=sf.getCurrentSession();session.beginTransaction();  // exists和in 差不多  但是exists效率高Query q=session.createQuery("from Topic t where not exists(select m.id from Msg m where m.topic.id=t.id)");for(Object  o :  q.list()){Topic t=(Topic)o;System.out.println(t.getCreateDate()+"--"+t.getTitle());}session.getTransaction().commit();}@Testpublic void testHQL_22(){Session session=sf.getCurrentSession();session.beginTransaction();   Query q=session.createQuery("update Topic t set t.title = upper(t.title)");q.executeUpdate();q=session.createQuery("from Topic");for(Object  o :  q.list()){Topic t=(Topic)o;System.out.println(t.getCreateDate()+"--"+t.getTitle());}session.createQuery("update Topic t set t.title=lower(t.title)").executeUpdate(); session.getTransaction().commit();}@Testpublic void testSchemaExport(){new SchemaExport(new AnnotationConfiguration().configure()).create(false, true);}@AfterClasspublic static void afterClass(){sf.close();}}

三、有命名的hql查询、使用SQL查询

import javax.persistence.*;@Entitypublic class Category {private int id;private String name;@Id@GeneratedValuepublic int getId() {return id;}public void setId(int id) {this.id = id;}public String getName() {return name;}public void setName(String name) {this.name = name;} }

import java.util.ArrayList;import java.util.Date;import java.util.List; import javax.persistence.*; @Entity@NamedQueries({@NamedQuery(name="topic.selectCertainTopic",query="from Topic t where t.id=:id")})public class Topic {private int id;private String title;private Category category;private Date createDate;private List<Msg> msg=new ArrayList<Msg>();@OneToMany(mappedBy="topic")public List<Msg> getMsg() {return msg;}public void setMsg(List<Msg> msg) {this.msg = msg;}@Id@GeneratedValuepublic int getId() {return id;}public void setId(int id) {this.id = id;}public String getTitle() {return title;}public void setTitle(String title) {this.title = title;}@ManyToOne(fetch=FetchType.LAZY)public Category getCategory() {return category;}public void setCategory(Category category) {this.category = category;}public Date getCreateDate() {return createDate;}public void setCreateDate(Date createDate) {this.createDate = createDate;}}

import javax.persistence.*;@Entitypublic class Msg {private int id;private String cont;private Topic topic;@Id@GeneratedValuepublic int getId() {return id;}public void setId(int id) {this.id = id;}public String getCont() {return cont;}public void setCont(String cont) {this.cont = cont;}@ManyToOnepublic Topic getTopic() {return topic;}public void setTopic(Topic topic) {this.topic = topic;}}

import java.util.Date;import java.util.List;import org.hibernate.*;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 Modeltest {private static SessionFactory sf=null;@BeforeClasspublic static void beforeClass(){sf=new AnnotationConfiguration().configure().buildSessionFactory();}@Testpublic 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();}<span style="white-space:pre"></span>//有命名的hql查询@Testpublic void testHQL_23(){//不重要  还不如写配置文件里Session session=sf.getCurrentSession();session.beginTransaction(); Query q=session.getNamedQuery("topic.selectCertainTopic");q.setParameter("id", 3);Topic t=(Topic)q.uniqueResult();System.out.println(t.getTitle());session.getTransaction().commit();}@Testpublic void testHQL_24(){Session session=sf.getCurrentSession();session.beginTransaction(); SQLQuery q=session.createSQLQuery("select * from category limit 2,4").addEntity(Category.class);//SQL查询List<Category> category=(List<Category>)q.list();for(Category c: category){System.out.println(c.getName());} session.getTransaction().commit();}@Testpublic void testSchemaExport(){new SchemaExport(new AnnotationConfiguration().configure()).create(false, true);}@AfterClasspublic static void afterClass(){sf.close();}}

四、QBC和QBE

import javax.persistence.*;@Entitypublic class Category {private int id;private String name;@Id@GeneratedValuepublic int getId() {return id;}public void setId(int id) {this.id = id;}public String getName() {return name;}public void setName(String name) {this.name = name;} }

import java.util.ArrayList;import java.util.Date;import java.util.List; import javax.persistence.*; @Entity@NamedQueries({@NamedQuery(name="topic.selectCertainTopic",query="from Topic t where t.id=:id")})public class Topic {private int id;private String title;private Category category;private Date createDate;private List<Msg> msg=new ArrayList<Msg>();@OneToMany(mappedBy="topic")public List<Msg> getMsg() {return msg;}public void setMsg(List<Msg> msg) {this.msg = msg;}@Id@GeneratedValuepublic int getId() {return id;}public void setId(int id) {this.id = id;}public String getTitle() {return title;}public void setTitle(String title) {this.title = title;}@ManyToOne(fetch=FetchType.LAZY)public Category getCategory() {return category;}public void setCategory(Category category) {this.category = category;}public Date getCreateDate() {return createDate;}public void setCreateDate(Date createDate) {this.createDate = createDate;}}

import javax.persistence.*;@Entitypublic class Msg {private int id;private String cont;private Topic topic;@Id@GeneratedValuepublic int getId() {return id;}public void setId(int id) {this.id = id;}public String getCont() {return cont;}public void setCont(String cont) {this.cont = cont;}@ManyToOnepublic Topic getTopic() {return topic;}public void setTopic(Topic topic) {this.topic = topic;}}

import java.util.Date;import java.util.List;import org.hibernate.*;import org.hibernate.cfg.AnnotationConfiguration;import org.hibernate.criterion.Example;import org.hibernate.criterion.Restrictions;import org.hibernate.tool.hbm2ddl.SchemaExport;import org.junit.AfterClass;import org.junit.BeforeClass;import org.junit.Test;public class Modeltest {private static SessionFactory sf=null;@BeforeClasspublic static void beforeClass(){sf=new AnnotationConfiguration().configure().buildSessionFactory();}@Testpublic 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();}@Testpublic void testHQL_QBC_1(){Session session=sf.getCurrentSession();session.beginTransaction();  //QBC: Query By Criteria//gt:>  lt:<  like:模糊查询  createCriteria:取出关联表  between("id",2,5)：category中id在2和5之间的Criteria c=session.createCriteria(Topic.class).add(Restrictions.gt("id", 2)).add(Restrictions.lt("id", 8)).add(Restrictions.like("title", "t_")).createCriteria("category").add(Restrictions.between("id", 2, 5));for(Object  o :  c.list()){Topic t=(Topic)o;System.out.println(t.getTitle()+":"+t.getId());}session.getTransaction().commit();}@Testpublic void testHQL_QBE_1(){Session session=sf.getCurrentSession();session.beginTransaction();  //QBE: Query By Example 先做个例子（exampleOft)  然后找和这个例子差不多的记录(e)Topic exampleOft=new Topic();exampleOft.setTitle("T_");Example e=Example.create(exampleOft).ignoreCase().enableLike();Criteria c=session.createCriteria(Topic.class).add(Restrictions.gt("id", 2)).add(Restrictions.lt("id", 8)).add(e);for(Object  o :  c.list()){Topic t=(Topic)o;System.out.println(t.getTitle()+":"+t.getId());}session.getTransaction().commit();}@Testpublic void testSchemaExport(){new SchemaExport(new AnnotationConfiguration().configure()).create(false, true);}@AfterClasspublic static void afterClass(){sf.close();}}

以上基本只有一、二和三的SQL比较常用。