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

按功能从大到小排列：1.NativeSQL//专门用于具体某个数据库的，不能跨数据库平台//如财务报表的子查询、关联查询，只能用nativeSQL//想提高查询效率，用存储过程PLSQL，只能用nativeSQL2.HQL//hibernate自带的查询语言，和SQL类似//内部会转化为具体的方言3.EJBQL(JPQL1.0)//可以认为是HQL的一个子集//跨O/R Mapping的产品4.QBC(Query By Criteria)5.QBE(Query By Example)//可以看做是QBC的一个子集总结：QL应该和导航关系结合，共同为查询提供服务实例：@Entitypublic class Category//板块{private int id;private String name;}@Entitypublic class Topic//主题帖子{private int id;private String title;@ManyToOneprivate Category category;private Date createDate;}@Entitypublic class Msg//回复{private int id;private String cont;@ManyToOneprivate 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到c48.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对象的时候才会查询category11."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类），//需要指明用哪一个成员变量的连接条件来做连接//否则存在这样的情况就会有问题：@Entitypublic class Topic{private int id;private String title;@ManyToOneprivate Category category;@ManyToOne//或者OneToOneprivate 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();}