HibernateQL

NativeSQL-------功能最强大

HQL--Hibernate QL

EJB QL (JP QL)---HQL的一个子集

QBC---Query By Criteria

QBE---Query By Example------功能最小

本节主要介绍EJB QL，它是HQL的子集

使用的是：org.hibernate.Query

示例：

Category版块：id，name

Topic主题：id，title，createDate，category

Msg回帖：id，cont，topic

Topic(多对一)Category

Msg(多对一)Topic

1.建Category、Topic、Msg实体类

2.在hibernate.cfg.xml中添加mapping语句

3.建Junit测试类

3.1 写测试方法testSchemaExport()，建好表

3.2 写测试方法testSave()，在每张表里存10条数据，如下：

@Testpublic void testSave() {session session = sf.getCurrentSession();session.beginTransaction();//存入10个版块for(int i=0;i<10;i++){Category category = new Category();category.setName("c"+i);session.save(category);}//存入10个主题，属于category_1for(int i=0;i<10;i++){Category category = new Category();category.setId(1);Topic topic = new Topic();topic.setTitle("t"+i);topic.setCteateDate(new Date());topic.setCategory(category);session.save(topic);}//存入10条回帖，属于topic_1for(int i=0;i<10;i++){Topic topic = new Topic();topic.setId(1);Msg msg = new Msg();msg.setCont("m"+i);msg.setTopic(topic);session.save(msg);}session.getTransaction().commit();}

以下表格依次为category、topic、msg

3.3 写测试方法testHQL()，进行相关查询

@Testpublic void testHQL() {Session session = sf.getCurrentSession();session.beginTransaction();//注意，EJBQL是面向对象的查询语言,Category是实体类的名字，不能写表名categoryQuery q = session.createQuery("from Category");for(Object o : q.list()){Category c = (Category) o;System.out.PRintln( c.getId()+ "-" +c.getName());}session.getTransaction().commit();}

EJB QL 查询语句---举例：

1.以对象的形式取出

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");//这里，冒号是占位符Query q = session.createQuery("from Category c where c.id > :min and c.id < :max");q.setParameter("min", 2);//q.setInteger("max", 8);  //两种方式皆可//分页显示Query q = session.createQuery("from Category c order by c.name desc");q.setMaxResults(4);   //每页显示4条q.setFirstResult(2);  //从第2条开始显示Query q = session.createQuery("from Msg m where m.topic.category.id=1");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,5)");Query q = session.createQuery("from Msg m where m.cont is not null");Query q = session.createQuery("from Topic t where t.category.id=1");//若测试此语句，需在Topic实体类中设置@OneToMany注解，对MsgQuery q = session.createQuery("from Topic t where t.msgs is empty");Query q = session.createQuery("from Topic t where t.title like '%5'");Query q = session.createQuery("from Topic t where t.title like '_5'");Query q = session.createQuery("from Topic t where t.id < (select avg(t.id) from Topic t)") ;Query q = session.createQuery("from Topic t where t.id < ALL (select t.id from Topic t where mod(t.id, 2)= 0) ");//用in 可以实现exists的功能//但是exists执行效率高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)") ;Query q = session.createQuery("from Topic t where t.id in (select m.id from Msg m where m.topic.id=t.id)") ;session.createQuery("update Topic t set t.title = upper(t.title)").executeUpdate();Query q = session.createQuery("from Topic");

2.以字段的形式取出

Query q = session.createQuery("select c.id, c.name from Category c order by c.name desc");//为什么不能直接写Category名，而必须写t.category//因为有可能存在多个成员变量（同一个类），需要指明用哪一个成员变量的连接条件来做连接Query q = session.createQuery("select t.title, c.name from Topic t join t.category c");Query q = session.createQuery("select lower(t.title)," +           "upper(t.title)," +          "trim(t.title)," +          "concat(t.title, '***')," +          "length(t.title)" +          " from Topic t ");Query q = session.createQuery("select abs(t.id)," +         "sqrt(t.id)," +         "mod(t.id, 2)" +         " from Topic t ");Query q = session.createQuery("select current_date, current_time, current_timestamp, t.id from Topic t");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") ;

1.以对象的形式取出---打印输出语句

//可以用List打印输出List<Category> categories = (List<Category>)q.list();for(Category c : categories){System.out.println(c.getId() + "-" + c.getName());}//List也可以写成如下形式for(Object o : q.list()){Category c = (Category) o;System.out.println( c.getId()+ "-" +c.getName());}//也可以用iterate打印输出Iterator<Category> it = (Iterator<Category>) q.iterate();while(it.hasNext()){Category c = it.next();System.out.println(c.getId()+"-"+c.getName());}

2.以字段的形式取出---打印输出语句   

//List,以数组的形式取出for(Object o : q.list()){Object[] obj = (Object[])o;System.out.println(obj[0]+ "-" + obj[1]);}//List的另一种形式List<Object[]> categories = (List<Object[]>)q.list();for(Object[] o : categories){System.out.println(o[0] + "-" + o[1]);}

3.查询结果只有一个值时---q.uniqueResult()

Query q = session.createQuery("select count(*) from Msg");//当查询结果只有一个值的时候用q.uniqueResult()//count(*)返回的是一个Long类型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]);

1.list() 和 iterate()

Query q = session.createQuery("from Category");List<Category> categories = (List<Category>)q.list();或者：Iterator<Category> it = (Iterator<Category>) q.iterate();区别：1.list取所有2.Iterate先取ID，等用到的时候再根据ID来取对象3.Session中list第二次发出，仍会到数据库查询4.Literate第二次，首先找session级缓存

2. Hibernate 1+N问题

对于两个关联关系的表Topic(多对一)Category，当获取Topic对象的时候(Query q = session.createQuery("from Topic");)，除了会发出查询Topic表的SQL语句，也会发出查询其关联的Category表的SQL语句。解决办法：1.将@ManyToOne注解的fetch属性设置为LAZY2.将@在Category的@Entity注解下面一行添加@BatchSize(size=5)注解   意思是，获取Category对象的时候，一次性获取5条数据。即如果一共有10条数据，那么只会发出两条SQL语句3.进行左连接，即将查询语句改为：Query q = session.createQuery("from Topic t left join fetch t.category");

1.什么是缓存：在内存里开辟一块空间，把本来应该存在硬盘上的东西，放到内存里，将来再读取的时候，直接从内存里读，这部分内存就叫缓存。2.一级缓存---session级别的缓存3.二级缓存---sessionFactory级别的缓存，可以跨越session存在4.hibernate.cfg.xml配置<property name="cache.use_second_level_cache">true</property><property name="cache.provider_class">org.hibernate.cache.EhCacheProvider</property>5.load默认使用二级缓存，iterate默认使用二级缓存6.list默认往二级缓存加数据，但是查询的时候不使用7.如果query用二级缓存，需打开查询缓存