07-Hibernate3.6.2 查询01 by HQL

 

在Hibernate中可以使用以下方式进行查询：

         NativeSQL：不能实现跨数据库，本地的数据库语言

         HQL：Hibernate自带的查询语言，将HQL语言根据方言进行转换SQL

         EJBQL(JPQL1.0):可以认为是HQL的子集（这部分用的最多）

         QBC(Query by Criteria)：按条件查询

         QBE(Query by Example)：按样例查询

功能由下到上功能由弱到强

 

HQL

         Hibernate提供了一种非常强大的查询语言HQL，其语法类似SQL，但是HQL是非常有意识的被设计为完全面向对象的查询（SQL提供的聚合函数更加丰富）。HQL除了Java类和属性的名称外，查询语句对大小写并不敏感。

 

首先我们定义几个实体

Category.java

package com.iflytek.QBC;

import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;

/**
 * @author xudongwang 2011-11-3 板块
 * 
 *         板块与主题之间是一对多关系
 */
@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;
	}
}

 

Msg.java

package com.iflytek.QBC;

import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.ManyToOne;
/**
 * @author xudongwang 2011-11-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;
	}

}

 

MsgInfo.java

package com.iflytek.HQL1;

/**
 * @author xudongwang 2011-11-3
 * 
 */
public class MsgInfo { // VO或DTO Value Object一般用来装临时的值
	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;
	}

}

 

Topic.java

package com.iflytek.HQL1;

import java.util.Date;

import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.ManyToOne;
/**
 * @author xudongwang 2011-11-3
 * 
 */
@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;
	}

}

 

一、集合查询

Session session = sf.openSession();
		session.beginTransaction();
		// 这里使用的Query是org.hibernate.Query
		// 同时这里需要写的类名而不要写表名
		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();

说明：通过Session的CreateQuery方法创建一个Query对象，它包含一个HQL查询语句。

调用Query的list()方法执行查询语句，该方法返回List类型的查询结果，在List集合中存放了符合查询条件的持久化对象。

 

二、去重复排序

		// distinct这里是和id的
		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.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();

 

四、传参“？”：

	Query q = session
				.createQuery("from Category c where c.id > ? and c.id < ?");
		q.setParameter(0, 2).setParameter(1, 8);
		// q.setParameter(1, 8);

 

五、分页：

		Query q = session.createQuery("from Category c order by c.name desc");
		q.setMaxResults(4);
		q.setFirstResult(2);
	// 这里所有使用参数的都可以使用链式编程

 

六、取对象指定字段：

	// 上面去的都是对象，这里取的是字段，其返回值是Object类型的数组
		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]);
		}

 

// 为什么不能直接写Category名，而必须写t.category
		// 因为有可能存在多个成员变量（同一个类），需要指明用哪一个成员变量的连接条件来做连接
		Query q = session
				.createQuery("select t.title, c.name from Topic t join t.category c "); // join
		for (Object o : q.list()) {
			Object[] m = (Object[]) o;
			System.out.println(m[0] + "-" + m[1]);
		}

 

七、多表查询中的fetch

// 设定fetch type 为lazy后将不会有第二条sql语句
		// 两张表联合查询
		Query q = session.createQuery("from Topic t where t.category.id = 1");

 

如果Topic中：

	@ManyToOne(fetch = FetchType.LAZY)
	public Category getCategory() {
		return category;
	}

 

   Hibernate: 
    select
        topic0_.id as id2_,
        topic0_.category_id as category4_2_,
        topic0_.createDate as createDate2_,
        topic0_.title as title2_ 
    from
        Topic topic0_ 
    where
        topic0_.category_id=1

 

如果Topic中：

	@ManyToOne
	public Category getCategory() {
		return category;
	}

 

    select
        topic0_.id as id2_,
        topic0_.category_id as category4_2_,
        topic0_.createDate as createDate2_,
        topic0_.title as title2_ 
    from
        Topic topic0_ 
    where
        topic0_.category_id=1
Hibernate: 
    select
        category0_.id as id0_0_,
        category0_.name as name0_0_ 
    from
        Category category0_ 
    where
        category0_.id=?

 

八、构造方法查询

// VO Value Object
		// DTO data transfer object
		Query q = session
				.createQuery("select new com.iflytek.QL1.MsgInfo(m.id, m.cont, m.topic.title, m.topic.category.name) from Msg m");

 

九、uniqueResult

	Query q = session.createQuery("from Msg m where m = :MsgToSearch "); // 不重要，而QBE要比这个要灵活
		Msg m = new Msg();
		m.setId(1);
		q.setParameter("MsgToSearch", m);

		//如果有多个值抛错,如果有值且只有一个,返回一个object,如果没值,返回null
		Msg mResult = (Msg) q.uniqueResult();

 

十、聚合函数

1、count：

Query q = session.createQuery("select count(*) from Msg m");	
// count(*)返回的是long类型
long count = (Long) q.uniqueResult();

 

2、max、min、avg、sum：

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]);

 

3、between and：

Query q = session.createQuery("from Msg m where m.id between 3 and 5");

 

4、in：

Query q = session.createQuery("from Msg m where m.id in (3,4, 5)");

 

5、is null 与 is not null：

Query q = session.createQuery("from Msg m where m.cont is not null");

 

6、is empty 与 is not empty：

Query q = session.createQuery("from Topic t where t.msgs is empty")

 

7、like：

// %表示0个或多个
Query q = session.createQuery("from Topic t where t.title like '%5'");

// _表示一个
Query q = session.createQuery("from Topic t where t.title like '_5'");

 

8、other

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("from Topic t where t.createDate < :date");
		q.setParameter("date", new Date());

Query q = session
				.createQuery("select t.title, count(*) from Topic t group by t.title having count(*) >= 1");

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执行效率高
		// 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)")

 

十一、更新

Query q = session.createQuery("update Topic t set t.title = upper(t.title)");
q.executeUpdate();

 

十二、NamedQueries

// 在topic类上加上
// @NamedQueries( { @NamedQuery(name = "topic.selectCertainTopic", query
// = "from Topic t where t.id = :id") })
Query q = session.getNamedQuery("topic.selectCertainTopic");
q.setParameter("id", 5);
Topic t = (Topic) q.uniqueResult();

 

十三、NativeSQL

//createSQLQuery使用数据库本身的sql语言，所以里面的category是表名了
SQLQuery q = session.createSQLQuery("select * from category limit 2,4").addEntity(Category.class);
List<Category> categories = (List<Category>) q.list();