Hibernate学习笔记（十二）

Hibernate查询(Query Language)

HQL VS EJBQL

一、 Hibernate可以使用的查询语言

1、 NativeSQL：本地语言(数据库自己的SQL语句)

2、 HQL ：Hibernate自带的查询语句，可以使用HQL语言，转换成具体的方言

3、 EJBQL：JPAQL 1.0，可以认为是HQL的一个子节(重点)

4、 QBC：Query By Cretira

5、 QBE：Query By Example

注意：上面的功能是从1至5的比较，1的功能最大，5的功能最小

二、 实例一

1、 版块

@Entity
public class Category {
	private int id;
	private String name;
	@Id
	@GeneratedValue
	public 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;}}

2、 主题

@Entity
public class Topic {
	private int id;
	private String title;
	private Category category;
	//private Category category2;
	
	private Date createDate;	
	public Date getCreateDate() {return createDate;}
	public void setCreateDate(Date createDate) {this.createDate = createDate;}
	@ManyToOne(fetch=FetchType.LAZY)
	public Category getCategory() {	return category;}
	public void setCategory(Category category) {this.category = category;	}
	@Id
	@GeneratedValue
	public 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;}}

3、 主题回复

@Entity
public class Msg {
	private int id;
	private String cont;
	private Topic topic;	
	@ManyToOne
	public Topic getTopic() {return topic;}
	public void setTopic(Topic topic) {this.topic = topic;}
	@Id
	@GeneratedValue
	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;}}

4、 临时类

public class MsgInfo { //VO DTO Value Object username p1 p2 UserInfo->User->DB
	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;}}

三、 实体一测试代码：

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

		/** QL:from + 实体类名称 */
		Query q = session.createQuery("from Category");
		List<Category> categories = (List<Category>)q.list();
		for(Category c : categories) {
			System.out.println(c.getName());
		}
		
	
		/* 可以为实体类起个别名，然后使用它 */
		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());
		}
	
		//排序
		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());
		}
	
		 //为加载上来的对象属性起别名，还可以使用
		Query q = session.createQuery("select distinct c from Category c order by c.name desc");
		List<Category> categories = (List<Category>)q.list();
		for(Category c : categories) {
			System.out.println(c.getName());
		}
	
		/*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.getId() + "-" + c.getName());
		}
	
		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());
		}
	
                  //分页
		Query q = session.createQuery("from Category c order by c.name desc");
		q.setMaxResults(4);//每页显示的最大记录数
		q.setFirstResult(2);//从第几条开始显示，从0开始
		List<Category> categories = (List<Category>)q.list();
		for(Category c : categories) {
			System.out.println(c.getId() + "-" + c.getName());
		}
	
		Query q = session.createQuery("select c.id,  c.name from Category c order by c.name desc");
		List<Object[]> categories = (List<Object[]>)q.list();
		for(Object[] o : categories) {
			System.out.println(o[0] + "-" + o[1]);
		}
	
	      //设定fetch type 为lazy后将不会有第二条sql语句
		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().getName());
		}
	
	
		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());
		}
	
          //了解即可
	//VO Value Object
	//DTO data transfer object
		Query q = session.createQuery("select new com.bjsxt.hibernate.MsgInfo(m.id, m.cont, m.topic.title, m.topic.category.name) from Msg");
		for(Object o : q.list()) {
			MsgInfo m = (MsgInfo)o;
			System.out.println(m.getCont());
		}
	
	         //动手测试left right join
	        //为什么不能直接写Category名，而必须写t.category
	        //因为有可能存在多个成员变量（同一个类），需要指明用哪一个成员变量的连接条件来做连接
		Query q = session.createQuery("select t.title, c.name from Topic t join t.category c "); //join Category c
		for(Object o : q.list()) {
			Object[] m = (Object[])o;
			System.out.println(m[0] + "-" + m[1]);
		}
	
	         //学习使用uniqueResult
		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());
	
		Query q = session.createQuery("select count(*) from Msg m");
		long count = (Long)q.uniqueResult();
		System.out.println(count);
	
	         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]);
	
		Query q = session.createQuery("from Msg m where m.id between 3 and 5");
		for(Object o : q.list()) {
			Msg m = (Msg)o;
			System.out.println(m.getId() + "-" + m.getCont());
		}
	
		Query q = session.createQuery("from Msg m where m.id in (3,4, 5)");
		for(Object o : q.list()) {
			Msg m = (Msg)o;
			System.out.println(m.getId() + "-" + m.getCont());
		}
	

	         //is null 与 is not null
		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.getId() + "-" + m.getCont());
		}

四、 实例二
注意：实体二，实体类，只是在实体一的基础上修改了Topic类，添加了多对一的关联关系
@Entity
@NamedQueries({
	@NamedQuery(name="topic.selectCertainTopic", query="from Topic t where t.id = :id")
		})
	/*@NamedNativeQueries(
		{
	@NamedNativeQuery(name="topic.select2_5Topic", query="select * from topic limit 2, 5")
		})*/
public class Topic {
	private int id;
	private String title;
	private Category category;
	private Date createDate;
	private List<Msg> msgs = new ArrayList<Msg>();
	@OneToMany(mappedBy="topic")
	public List<Msg> getMsgs() {return msgs;}
	public void setMsgs(List<Msg> msgs) {this.msgs = msgs;}
	public Date getCreateDate() {return createDate;}
	public void setCreateDate(Date createDate) {this.createDate = createDate;	}
	@ManyToOne(fetch=FetchType.LAZY)
	public Category getCategory() {	return category;}
	public void setCategory(Category category) {this.category = category;}
	@Id
	@GeneratedValue
	public 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;}}
五、 实例二测试代码
注意：测试数据是实例一的测试数据
                  //is empty and is not empty
		Query q = session.createQuery("from Topic t where t.msgs is empty");
		for(Object o : q.list()) {
			Topic t = (Topic)o;
			System.out.println(t.getId() + "-" + t.getTitle());
		}
	
		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.getId() + "-" + t.getTitle());
		}
	
		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.getId() + "-" + t.getTitle());
		}

	   //不重要
		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] + "-");
		}
	
		Query q = session.createQuery("select abs(t.id)," +
						 "sqrt(t.id)," +
						 "mod(t.id, 2)" +
						" from Topic t ");
		for(Object o : q.list()) {
			Object[] arr = (Object[])o;
			System.out.println(arr[0] + "-" + arr[1] + "-" + arr[2] );
		}
	
Query q = session.createQuery("select current_date, current_time, current_timestamp, t.id from Topic t");
		for(Object o : q.list()) {
			Object[] arr = (Object[])o;
			System.out.println(arr[0] + " | " + arr[1] + " | " + arr[2] + " | " + arr[3]);
		}
	
		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.getTitle());
		}
	
		Query q = session.createQuery("select t.title, count(*) from Topic t group by t.title") ;
		for(Object o : q.list()) {
			Object[] arr = (Object[])o;
			System.out.println(arr[0] + "|" + arr[1]);
		}
	
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]);
		}
	
		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.getTitle());
		}
	
		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.getTitle());
		}
	
	//用in 可以实现exists的功能
	//但是exists执行效率高
          // t.id not in (1)
		Query q = session.createQuery("from Topic t where not exists (select m.id from Msg m where m.topic.id=t.id)") ;
        //Query q = session.createQuery("from Topic t where 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.getTitle());
		}
	
	//update and delete
	//规范并没有说明是不是要更新persistent object，所以如果要使用，建议在单独的trasaction中执行
		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.getTitle());
		}
		session.createQuery("update Topic t set t.title = lower(t.title)")
			.executeUpdate();
	
        //不重要
		Query q = session.getNamedQuery("topic.selectCertainTopic");
		q.setParameter("id", 5);
		Topic t = (Topic)q.uniqueResult();
		System.out.println(t.getTitle());
	
	//Native（了解）
		SQLQuery q = session.createSQLQuery("select * from category limit 2,4").addEntity(Category.class);
		List<Category> categories = (List<Category>)q.list();
		for(Category c : categories) {
			System.out.println(c.getName());
		}
Query by Criteria(QBC)
QBC(Query By Criteria)查询方式是Hibernate提供的“更加面向对象”的一种检索方式。QBC在条件查询上比HQL查询更为灵活，而且支持运行时动态生成查询语句。 

在Hibernate应用中使用QBC查询通常经过3个步骤 

  (1)使用Session实例的createCriteria()方法创建Criteria对象 

  (2)使用工具类Restrictions的相关方法为Criteria对象设置查询对象 

  (3)使用Criteria对象的list()方法执行查询，返回查询结果
一、 实体代码：
注意：数据是使用Hibernate查询章节的数据
//criterion 标准/准则/约束
		Criteria c = session.createCriteria(Topic.class) //from Topic					 
					 .add(Restrictions.gt("id", 2)) //greater than = id > 2
					 .add(Restrictions.lt("id", 8)) //little than = id < 8
					 .add(Restrictions.like("title", "t_"))
					 .createCriteria("category")
				          .add(Restrictions.between("id", 3, 5)) //category.id >= 3 and category.id <=5
					 ;
		//DetachedCriterea
		for(Object o : c.list()) {
			Topic t = (Topic)o;
			System.out.println(t.getId() + "-" + t.getTitle());
		}
二、 Restrictions用法
Hibernate中Restrictions的方法                       说明  

Restrictions.eq                                                      ＝  

Restrictions.allEq                             利用Map来进行多个等于的限制  

Restrictions.gt                                                       ＞  

Restrictions.ge                                                     ＞＝  

Restrictions.lt                                                       ＜ 

Restrictions.le                                                      ＜＝  

Restrictions.between                                    BETWEEN 

Restrictions.like                                               LIKE 

Restrictions.in                                                      in 

Restrictions.and                                                and 

Restrictions.or                                                     or 

Restrictions.sqlRestriction                   用SQL限定查询 
============================================
1:QBE (Query By Example)

  Criteria cri = session.createCriteria(Student.class);

  cri.add(Example.create(s)); //s是一个Student对象

  list cri.list();   

  实质：创建一个模版，比如我有一个表serial有一个 giftortoy字段，我设置serial.setgifttoy("2"), 则这个表中的所有的giftortoy为2的数据都会出来



2: QBC (Query By Criteria) 主要有Criteria,Criterion,Oder,Restrictions类组成

  session = this.getSession();

  Criteria cri = session.createCriteria(JdItemSerialnumber.class);

  Criterion cron = Restrictions.like("customer",name);

  cri.add(cron);

  list = cri.list();

3: HQL

  String hql = "select s.name ,avg(s.age) from Student s group by s.name";

  Query query = session.createQuery(hql);

  list = query.list();

  ....



4: 本地SQL查询

  session = sessionFactory.openSession();

  tran = session.beginTransaction();

  SQLQuery sq = session.createSQLQuery(sql);

  sq.addEntity(Student.class);

  list = sq.list();

  tran.commit();



5: QID 

  Session的get()和load()方法提供了根据对象ID来检索对象的方式。该方式被用于事先知道了要检索对象ID的情况。
三、 QBC分页查询 
 Criteria为我们提供了两个有用的方法：setFirstResult(int firstResult)和setMaxResults(int maxResults). 

setFirstResult(int firstResult)方法用于指定从哪一个对象开始检索（序号从0开始），默认为第一个对象（序号为0）；setMaxResults(int maxResults)方法用于指定一次最多检索出的对象数目，默认为所有对象。 
Session session = HibernateSessionFactory.getSessionFactory().openSession();   
Transaction ts = null;   
Criteria criteria = session.createCriteria(Order.class);   
int pageSize = 15;   
int pageNo = 1;   
criteria.setFirstResult((pageNo-1)*pageSize);   
criteria.setMaxResults(pageSize);   
Iterator it = criteria.list().iterator();   
ts.commit();   
HibernateSessionFactory.closeSession();  
四、 QBC复合查询 
复合查询就是在原有的查询基础上再进行查询。例如在顾客对定单的一对多关系中，在查询出所有的顾客对象后，希望在查询定单中money大于1000的定单对象。 

Session session = HibernateSessionFactory.getSessionFactory().openSession();   
Transaction ts = session.beginTransaction();   
Criteria cuscriteria = session.createCriteria(Customer.class);   
Criteria ordCriteria = cusCriteria.createCriteria("orders");   
ordCriteria.add(Restrictions.gt("money", new Double(1000)));   
Iterator it = cusCriteria.list().iterator();   
ts.commit();   
HibernateSessionFactory.closeSession();  
五、 QBC离线查询 
 离线查询又叫DetachedCriteria查询，它可以在Session之外进行构造，只有在需要执行查询时才与Session绑定。
Query By Example(QBE)
QBE查询就是检索与指定样本对象具有相同属性值的对象。因此QBE查询的关键就是样本对象的创建，样本对象中的所有非空属性均将作为查询条件。QBE查询的功能子集，虽然QBE没有QBC功能大，但是有些场合QBE使用起来更为方便。 

        工具类Example为Criteria对象指定样本对象作为查询条件
一、 实例代码
Session session = sf.openSession();
		session.beginTransaction();
		Topic tExample = new Topic();
		tExample.setTitle("T_");		
		Example e = Example.create(tExample)
					.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.getId() + "-" + t.getTitle());
		}
		session.getTransaction().commit();
		session.close();
Session session = HibernateSessionFactory.getSessionFactory().openSession();   
Transaction ts = session.beginTransaction();   
Customer c = new Customer();   
c.setCname("Hibernate");   
Criteria criteria = session.createCriteria(Customer.class);   
Criteria.add(Example.create(c));   
Iterator it = criteria.list().iterator();   
ts.commit();   
HibernateSessionFactory.closeSession();
Query.list与query.iterate(不太重要)
一、  query.iterate查询数据
* query.iterate()方式返回迭代查询
 * 会开始发出一条语句：查询所有记录ID语句 
 * Hibernate: select student0_.id as col_0_0_ from t_student student0_
 * 然后有多少条记录，会发出多少条查询语句。
 * n + 1问题：n:有n条记录，发出n条查询语句；1 ：发出一条查询所有记录ID语句。
 * 出现n+1的原因：因为iterate(迭代查询)是使用缓存的，
第一次查询数据时发出查询语句加载数据并加入到缓存，以后再查询时hibernate会先到ession缓存(一级缓存)中查看数据是否存在，如果存在则直接取出使用，否则发出查询语句进行查询。
	session = HibernateUtils.getSession();
			tx = session.beginTransaction();
			
			/**
			 * 出现N+1问题
			 * 发出查询id列表的sql语句
			 * Hibernate: select student0_.id as col_0_0_ from t_student student0_
			 * 
			 * 再依次发出根据id查询Student对象的sql语句
			 * Hibernate: select student0_.id as id1_0_, student0_.name as name1_0_, 
			 * student0_.createTime as createTime1_0_, student0_.classesid as classesid1_0_ 
			 * from t_student student0_ where student0_.id=?
			 */
			Iterator students = session.createQuery("from Student").iterate();
			
			while (students.hasNext()){
				Student student = (Student)students.next();
				System.out.println(student.getName());
			}
	tx.commit();
二、  query.list()和query.iterate()的区别
先执行query.list()，再执行query.iterate，这样不会出现N+1问题，
 * 因为list操作已经将Student对象放到了一级缓存中，所以再次使用iterate操作的时候
 * 它首先发出一条查询id列表的sql，再根据id到缓存中取数据，只有在缓存中找不到相应的
 * 数据时，才会发出sql到数据库中查询
	List students = session.createQuery("from Student").list();
			
			for (Iterator iter = students.iterator(); iter.hasNext();){
				Student student = (Student)iter.next();
				System.out.println(student.getName());
			}						
System.out.println("---------------------------------------------------------");
			 // 不会出现N+1问题，因为list操作已经将数据加入到一级缓存。
			Iterator iters = session.createQuery("from Student").iterate();
			
			while (iters.hasNext()){
				Student student = (Student)iters.next();
				System.out.println(student.getName());
			}
三、  两次query.list()
 * 会再次发出查询sql
 * 在默认情况下list每次都会向数据库发出查询对象的sql，除非配置了查询缓存
 * 所以：虽然list操作已经将数据放到一级缓存，但list默认情况下不会利用缓存，而再次发出sql
 * 默认情况下，list会向缓存中放入数据，但不会使用数据。
	List students = session.createQuery("from Student").list();
			
			for (Iterator iter = students.iterator(); iter.hasNext();){
				Student student = (Student)iter.next();
				System.out.println(student.getName());
			}
			
			System.out.println("------------------------------------------------");
			
			//会再次发现SQL语句进行查询，因为默认情况list只向缓存中放入数据，不会使用缓存中数据
			students = session.createQuery("from Student").list();
			
			for (Iterator iter = students.iterator(); iter.hasNext();){
				Student student = (Student)iter.next();
				System.out.println(student.getName());
			}