17hibernate hibernate的Query Language(查询语言)

按功能从大到小排列：
1.NativeSQL//专门用于具体某个数据库的，不能跨数据库平台
		//如财务报表的子查询、关联查询，只能用nativeSQL
		//想提高查询效率，用存储过程PLSQL，只能用nativeSQL
2.HQL//hibernate自带的查询语言，和SQL类似
	//内部会转化为具体的方言
3.EJBQL(JPQL1.0)//可以认为是HQL的一个子集
				//跨O/R Mapping的产品
4.QBC(Query By Criteria)
5.QBE(Query By Example)//可以看做是QBC的一个子集
总结：QL应该和导航关系结合，共同为查询提供服务

实例：
@Entity
public class Category//板块
{
	private int id;
	private String name;
}

@Entity
public class Topic//主题帖子
{
	private int id;
	private String title;
	@ManyToOne
	private Category category;
	private Date createDate;
}

@Entity
public class Msg//回复
{
	private int id;
	private String cont;
	@ManyToOne
	private Topic topic;
}

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

EJBQL示例：
1.
public void testHQL_01(){
	Session session = sf.openSession();
	session.beginTransaction();

	Query q = session.createQuery("from Category");
	List<Category> categories = (List<Category>)q.list();
	for(Category c:categories){
		System.out.println(c.getName());
	}
	session.getTransaction().commit();
	session.close();
}


2."from Category c where c.name > 'c5'";
3."from Category c order by c.name desc";
4."select distince c from Category c order by c.name desc";
5."from Category c where c.id > :min and c.id < :max";
	//:是占位符
	q.setParameter("min",2);//q.setInteger("min",2);
	q.setParameter("max",8);
6.链式编程：
	Query q = session.createQuery(
	"from Category c where c.id > :min and c.id < :max"
	).setInteger("min",2)
	.setInteger("max",8);
7.分页:
	Query q = session.createQuery(
	"from Category c order by c.name desc");
	q.setMaxResults(4);
	q.setFirstResult(2);//结果从c7到c4
8.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]);
	}
9.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());
	}
	//只有fetchType为eager，会将category也取出来
10.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.getcategory().getName());
	}
//fetchType为lazy的时候，只有拿到category对象的时候才会查询category
11."from Msg m where m.topic.category.id = 1"
12.值对象：
	"select new com.hibernate.MsgInfo
	(m.id,m.cont,m.topic.title,m.topic.categoryname) 
	from Msg"
	public class MsgInfo//VO Value Object 或 DTO data transfer object
	{
		private int id;
		private String cont;
		private String topicName;
		private String categoryName;
	}//临时对象一定要有构造方法
13.手写join后面跟属性名：
	"select t.title,c.name from Topic t join t.category c"
	//为什么不能直接写Category名，而必须写t.category?
	//因为Topic类有可能存在多个成员变量（同一个类，也导航到Category类），
	//需要指明用哪一个成员变量的连接条件来做连接
	//否则存在这样的情况就会有问题：
	@Entity
	public class Topic
	{
		private int id;
		private String title;
		@ManyToOne
		private Category category;
		@ManyToOne//或者OneToOne
		private Category category2;
		private Date createDate;
	}
14.学习使用uniqueResult
	"from Msg m where m = :MsgToSearch"//不重要
	Msg m = new Msg();
	m.setId(1);
	q.setParameter("MsgToSearch",m);
	Msg mResult = (Msg)q.uniqueResult();
	//明知查询结果只有一个，就不用产生list再去循环
15."select count(*) from Msg m"	
	long count = (long)q.uniqueResult();
16."select max(m.id),min(m.id),avg(m.id),sum(m.id) from Msg"
	Object[] o = (Object[])q.uniqueResult();
17."from Msg m where m.id between 3 and 5"
18."from Msg m where m.id in (3,4,5)"
19."from Msg m where m.cont is not null"

QBC：//面向对象的语言
//criteria	标准/约束
public void testQBC(){
	Session session = sf.openSession();
	session.beginTransaction();

	Criteria c = session.createCriteria(Topic.class)//相当于"from Topic"
		.add(Restrictions.gt("id",2))
		.add(Restrictions.lt("id",8))
		.add(Restrictions.like("title","t_"))
		.createCriteria("category")//属性名，与Category表做连接
		.add(Restrictions.between("id",3,5))//Category的id>3及<5
		;
	//还有一种是DetachedCriteria，自己创建后绑定到session上，更灵活
	for(Object o:c.list()){
		Topic t = (Topic)o;
		System.out.println(t.getId()+"-"+t.getTitle());
	}
	session.getTransaction().commit();
	session.close();
}


QBE：//缺点：产生的典型必须有确定值，不能是范围
public void testQBE(){
	Session session = sf.openSession();
	session.beginTransaction();
	Topic tExample = new Topic();
	tExample.setTitle("T_");
	Example e = Example.create(tExample)//静态工厂方法
		.ignoreCase().enableLike();//查询谁的title Like T_
	Criteria c = session.createCriteria(Topic.class)//相当于"from Topic"
		.add(Restrictions.gt("id",2))
		.add(Restrictions.lt("id",8))
		.add(e);
		//相当于先查询id在2和8之间的，然后再去结果里面查找title Like T_
	for(Object o:c.list()){
		Topic t = (Topic)o;
		System.out.println(t.getId()+"-"+t.getTitle());
	}
	session.getTransaction().commit();
	session.close();
}