HibernateQL

查询语言---QL(Query Language)

 

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条数据,如下:

@Test
public 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_1
	for(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_1
	for(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

category   topic   msg

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

@Test
public void testHQL() {
	
	Session session = sf.getCurrentSession();
	session.beginTransaction();
	
	//注意,EJBQL是面向对象的查询语言,Category是实体类的名字,不能写表名category
	Query 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注解,对Msg
Query 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属性设置为LAZY

2.将@在Category的@Entity注解下面一行添加@BatchSize(size=5)注解
   意思是,获取Category对象的时候,一次性获取5条数据。即如果一共有10条数据,那么只会发出两条SQL语句

3.进行左连接,即将查询语句改为:Query q = session.createQuery("from Topic t left join fetch t.category");

3. 一级缓存、二级缓存、查询缓

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用二级缓存,需打开查询缓存
posted @ 2014-08-15 17:34  小样儿1020  阅读(515)  评论(0编辑  收藏  举报