Hibernate学习笔记（十二）

最新推荐文章于 2023-09-04 18:24:20 发布

原创最新推荐文章于 2023-09-04 18:24:20 发布 · 430 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#hibernate #object #query #string #session #iterator

hibernate 专栏收录该内容

15 篇文章

订阅专栏

本文详细介绍了Hibernate查询语言HQL和EJBQL的使用方法，包括基本查询、条件查询、排序、分页等操作，并展示了如何使用Query By Criteria (QBC) 和 Query By Example (QBE) 进行更灵活的查询。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

Hibernate查询(Query Language)

HQL VS EJBQL

一、 Hibernate可以使用的查询语言

1、 NativeSQL：本地语言(数据库自己的SQL语句)

2、 HQL ：Hibernate自带的查询语句，可以使用HQL语言，转换成具体的方言

3、 EJBQL：JPAQL 1.0，可以认为是HQL的一个子节(重点)

4、 QBC：Query By Cretira

5、 QBE：Query By Example

注意：上面的功能是从1至5的比较，1的功能最大，5的功能最小

二、实例一

1、版块

@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;}}

2、主题

@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;}}

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

4、临时类

public class MsgInfo { //VO DTO Value Object username p1 p2 UserInfo->User->DB
	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;}}

三、实体一测试代码：

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

		/** QL:from + 实体类名称 */
		Query q = session.createQuery("from Category");
		List<Category> categories = (List<Category>)q.list();
		for(Category c : categories) {
			System.out.println(c.getName());
		}
		
	
		/* 可以为实体类起个别名，然后使用它 */
		Query q = session.createQuery("from Category c where c.name > 'c5'");
		List<Category> categories = (List<Category>)q.list();
		for(Category c : categories) {
			System.out.println(c.getName());
		}
	
		//排序
		Query q = session.createQuery("from Category c order by c.name desc");
		List<Category> categories = (List<Category>)q.list();
		for(Category c : categories) {
			System.out.println(c.getName());
		}
	
		 //为加载上来的对象属性起别名，还可以使用
		Query q = session.createQuery("select distinct c from Category c order by c.name desc");
		List<Category> categories = (List<Category>)q.list();
		for(Category c : categories) {
			System.out.println(c.getName());
		}
	
		/*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();
		for(Category c : categories) {
			System.out.println(c.getId() + "-" + c.getName());
		}
	
		Query q = session.createQuery("from Category c where c.id > ? and c.id < ?");
		q.setParameter(0, 2)
			.setParameter(1, 8);
                  //q.setParameter(1, 8);
		List<Category> categories = (List<Category>)q.list();
		for(Category c : categories) {
			System.out.println(c.getId() + "-" + c.getName());
		}
	
                  //分页
		Query q = session.createQuery("from Category c order by c.name desc");
		q.setMaxResults(4);//每页显示的最大记录数
		q.setFirstResult(2);//从第几条开始显示，从0开始
		List<Category> categories = (List<Category>)q.list();
		for(Category c : categories) {
			System.out.println(c.getId() + "-" + c.getName());
		}
	
		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]);
		}
	
	      //设定fetch type 为lazy后将不会有第二条sql语句
		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());
			//System.out.println(t.getCategory().getName());
		}
	
	
		Query q = session.createQuery("from Msg  m where m.topic.category.id = 1");
		for(Object o : q.list()) {
			Msg m = (Msg)o;
			System.out.println(m.getCont());
		}
	
          //了解即可
	//VO Value Object
	//DTO data transfer object
		Query q = session.createQuery("select new com.bjsxt.hibernate.MsgInfo(m.id, m.cont, m.topic.title, m.topic.category.name) from Msg");
		for(Object o : q.list()) {
			MsgInfo m = (MsgInfo)o;
			System.out.println(m.getCont());
		}
	
	         //动手测试left right join
	        //为什么不能直接写Category名，而必须写t.category
	        //因为有可能存在多个成员变量（同一个类），需要指明用哪一个成员变量的连接条件来做连接
		Query q = session.createQuery("select t.title, c.name from Topic t join t.category c "); //join Category c
		for(Object o : q.list()) {
			Object[] m = (Object[])o;
			System.out.println(m[0] + "-" + m[1]);
		}
	
	         //学习使用uniqueResult
		Query q = session.createQuery("from Msg m where m = :MsgToSearch "); //不重要
		Msg m = new Msg();
		m.setId(1);
		q.setParameter("MsgToSearch", m);
		Msg mResult = (Msg)q.uniqueResult();
		System.out.println(mResult.getCont());
	
		Query q = session.createQuery("select count(*) from Msg m");
		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]);
	
		Query q = session.createQuery("from Msg m where m.id between 3 and 5");
		for(Object o : q.list()) {
			Msg m = (Msg)o;
			System.out.println(m.getId() + "-" + m.getCont());
		}
	
		Query q = session.createQuery("from Msg m where m.id in (3,4, 5)");
		for(Object o : q.list()) {
			Msg m = (Msg)o;
			System.out.println(m.getId() + "-" + m.getCont());
		}
	

	         //is null 与 is not null
		Query q = session.createQuery("from Msg m where m.cont is not null");
		for(Object o : q.list()) {
			Msg m = (Msg)o;
			System.out.println(m.getId() + "-" + m.getCont());
		}

四、实例二

注意：实体二，实体类，只是在实体一的基础上修改了Topic类，添加了多对一的关联关系

@Entity
@NamedQueries({
	@NamedQuery(name="topic.selectCertainTopic", query="from Topic t where t.id = :id")
		})
	/*@NamedNativeQueries(
		{
	@NamedNativeQuery(name="topic.select2_5Topic", query="select * from topic limit 2, 5")
		})*/
public class Topic {
	private int id;
	private String title;
	private Category category;
	private Date createDate;
	private List<Msg> msgs = new ArrayList<Msg>();
	@OneToMany(mappedBy="topic")
	public List<Msg> getMsgs() {return msgs;}
	public void setMsgs(List<Msg> msgs) {this.msgs = msgs;}
	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;}}

五、实例二测试代码

注意：测试数据是实例一的测试数据

                  //is empty and is not empty
		Query q = session.createQuery("from Topic t where t.msgs is empty");
		for(Object o : q.list()) {
			Topic t = (Topic)o;
			System.out.println(t.getId() + "-" + t.getTitle());
		}
	
		Query q = session.createQuery("from Topic t where t.title like '%5'");
		for(Object o : q.list()) {
			Topic t = (Topic)o;
			System.out.println(t.getId() + "-" + t.getTitle());
		}
	
		Query q = session.createQuery("from Topic t where t.title like '_5'");
		for(Object o : q.list()) {
			Topic t = (Topic)o;
			System.out.println(t.getId() + "-" + t.getTitle());
		}

	   //不重要
		Query q = session.createQuery("select lower(t.title)," +
						 "upper(t.title)," +
						 "trim(t.title)," +
						 "concat(t.title, '***')," +
						 "length(t.title)" +
						 " from Topic t ");
		for(Object o : q.list()) {
			Object[] arr = (Object[])o;
		System.out.println(arr[0] + "-" + arr[1] + "-" + arr[2] + "-" + arr[3] + "-" + arr[4] + "-");
		}
	
		Query q = session.createQuery("select abs(t.id)," +
						 "sqrt(t.id)," +
						 "mod(t.id, 2)" +
						" from Topic t ");
		for(Object o : q.list()) {
			Object[] arr = (Object[])o;
			System.out.println(arr[0] + "-" + arr[1] + "-" + arr[2] );
		}
	
Query q = session.createQuery("select current_date, current_time, current_timestamp, t.id from Topic t");
		for(Object o : q.list()) {
			Object[] arr = (Object[])o;
			System.out.println(arr[0] + " | " + arr[1] + " | " + arr[2] + " | " + arr[3]);
		}
	
		Query q = session.createQuery("from Topic t where t.createDate < :date");
		q.setParameter("date", new Date());
		for(Object o : q.list()) {
			Topic t = (Topic)o;
			System.out.println(t.getTitle());
		}
	
		Query q = session.createQuery("select t.title, count(*) from Topic t group by t.title") ;
		for(Object o : q.list()) {
			Object[] arr = (Object[])o;
			System.out.println(arr[0] + "|" + arr[1]);
		}
	
Query q = session.createQuery("select t.title, count(*) from Topic t group by t.title having count(*) >= 1") ;
		for(Object o : q.list()) {
			Object[] arr = (Object[])o;
			System.out.println(arr[0] + "|" + arr[1]);
		}
	
		Query q = session.createQuery("from Topic t where t.id < (select avg(t.id) from Topic t)") ;
		for(Object o : q.list()) {
			Topic t = (Topic)o;
			System.out.println(t.getTitle());
		}
	
		Query q = session.createQuery("from Topic t where t.id < ALL (select t.id from Topic t where mod(t.id, 2)= 0) ") ;
		for(Object o : q.list()) {
			Topic t = (Topic)o;
			System.out.println(t.getTitle());
		}
	
	//用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)") ;
		for(Object o : q.list()) {
			Topic t = (Topic)o;
			System.out.println(t.getTitle());
		}
	
	//update and delete
	//规范并没有说明是不是要更新persistent object，所以如果要使用，建议在单独的trasaction中执行
		Query q = session.createQuery("update Topic t set t.title = upper(t.title)") ;
		q.executeUpdate();
		q = session.createQuery("from Topic");
		for(Object o : q.list()) {
			Topic t = (Topic)o;
			System.out.println(t.getTitle());
		}
		session.createQuery("update Topic t set t.title = lower(t.title)")
			.executeUpdate();
	
        //不重要
		Query q = session.getNamedQuery("topic.selectCertainTopic");
		q.setParameter("id", 5);
		Topic t = (Topic)q.uniqueResult();
		System.out.println(t.getTitle());
	
	//Native（了解）
		SQLQuery q = session.createSQLQuery("select * from category limit 2,4").addEntity(Category.class);
		List<Category> categories = (List<Category>)q.list();
		for(Category c : categories) {
			System.out.println(c.getName());
		}

Query by Criteria(QBC)

QBC(Query By Criteria)查询方式是Hibernate提供的“更加面向对象”的一种检索方式。QBC在条件查询上比HQL查询更为灵活，而且支持运行时动态生成查询语句。

在Hibernate应用中使用QBC查询通常经过3个步骤
  (1)使用Session实例的createCriteria()方法创建Criteria对象
  (2)使用工具类Restrictions的相关方法为Criteria对象设置查询对象
  (3)使用Criteria对象的list()方法执行查询，返回查询结果

一、实体代码：

注意：数据是使用Hibernate查询章节的数据

//criterion 标准/准则/约束
		Criteria c = session.createCriteria(Topic.class) //from Topic					 
					 .add(Restrictions.gt("id", 2)) //greater than = id > 2
					 .add(Restrictions.lt("id", 8)) //little than = id < 8
					 .add(Restrictions.like("title", "t_"))
					 .createCriteria("category")
				          .add(Restrictions.between("id", 3, 5)) //category.id >= 3 and category.id <=5
					 ;
		//DetachedCriterea
		for(Object o : c.list()) {
			Topic t = (Topic)o;
			System.out.println(t.getId() + "-" + t.getTitle());
		}

二、 Restrictions用法

Hibernate中Restrictions的方法                       说明
Restrictions.eq                                                     ＝
Restrictions.allEq                             利用Map来进行多个等于的限制
Restrictions.gt                                                       ＞
Restrictions.ge                                                     ＞＝
Restrictions.lt                                                       ＜
Restrictions.le                                                      ＜＝
Restrictions.between                                    BETWEEN
Restrictions.like                                              LIKE
Restrictions.in                                                     in
Restrictions.and                                               and
Restrictions.or                                                     or
Restrictions.sqlRestriction                   用SQL限定查询

============================================

1:QBE (Query By Example)
  Criteria cri = session.createCriteria(Student.class);
  cri.add(Example.create(s)); //s是一个Student对象
  list cri.list();
  实质：创建一个模版，比如我有一个表serial有一个 giftortoy字段，我设置serial.setgifttoy("2"), 则这个表中的所有的giftortoy为2的数据都会出来

2: QBC (Query By Criteria) 主要有Criteria,Criterion,Oder,Restrictions类组成
  session = this.getSession();
  Criteria cri = session.createCriteria(JdItemSerialnumber.class);
  Criterion cron = Restrictions.like("customer",name);
  cri.add(cron);
  list = cri.list();
3: HQL
  String hql = "select s.name ,avg(s.age) from Student s group by s.name";
  Query query = session.createQuery(hql);
  list = query.list();
  ....

4: 本地SQL查询
  session = sessionFactory.openSession();
  tran = session.beginTransaction();
  SQLQuery sq = session.createSQLQuery(sql);
  sq.addEntity(Student.class);
  list = sq.list();
  tran.commit();

5: QID
  Session的get()和load()方法提供了根据对象ID来检索对象的方式。该方式被用于事先知道了要检索对象ID的情况。

三、 QBC分页查询

Criteria为我们提供了两个有用的方法：setFirstResult(int firstResult)和setMaxResults(int maxResults).
setFirstResult(int firstResult)方法用于指定从哪一个对象开始检索（序号从0开始），默认为第一个对象（序号为0）；setMaxResults(int maxResults)方法用于指定一次最多检索出的对象数目，默认为所有对象。

Session session = HibernateSessionFactory.getSessionFactory().openSession();

Transaction ts = null;

Criteria criteria = session.createCriteria(Order.class);

int pageSize = 15;

int pageNo = 1;

criteria.setFirstResult((pageNo-1)*pageSize);

criteria.setMaxResults(pageSize);

Iterator it = criteria.list().iterator();

ts.commit();

HibernateSessionFactory.closeSession();

四、 QBC复合查询

复合查询就是在原有的查询基础上再进行查询。例如在顾客对定单的一对多关系中，在查询出所有的顾客对象后，希望在查询定单中money大于1000的定单对象。

Session session = HibernateSessionFactory.getSessionFactory().openSession();

Transaction ts = session.beginTransaction();

Criteria cuscriteria = session.createCriteria(Customer.class);

Criteria ordCriteria = cusCriteria.createCriteria("orders");

ordCriteria.add(Restrictions.gt("money", new Double(1000)));

Iterator it = cusCriteria.list().iterator();

ts.commit();

HibernateSessionFactory.closeSession();

五、 QBC离线查询

离线查询又叫DetachedCriteria查询，它可以在Session之外进行构造，只有在需要执行查询时才与Session绑定。

Query By Example(QBE)

QBE查询就是检索与指定样本对象具有相同属性值的对象。因此QBE查询的关键就是样本对象的创建，样本对象中的所有非空属性均将作为查询条件。QBE查询的功能子集，虽然QBE没有QBC功能大，但是有些场合QBE使用起来更为方便。
工具类Example为Criteria对象指定样本对象作为查询条件

一、实例代码

Session session = sf.openSession();
		session.beginTransaction();
		Topic tExample = new Topic();
		tExample.setTitle("T_");		
		Example e = Example.create(tExample)
					.ignoreCase().enableLike();
		Criteria c = session.createCriteria(Topic.class)
					 .add(Restrictions.gt("id", 2))
					 .add(Restrictions.lt("id", 8))
					 .add(e);		
		for(Object o : c.list()) {
			Topic t = (Topic)o;
			System.out.println(t.getId() + "-" + t.getTitle());
		}
		session.getTransaction().commit();
		session.close();

Session session = HibernateSessionFactory.getSessionFactory().openSession();

Transaction ts = session.beginTransaction();

Customer c = new Customer();

c.setCname("Hibernate");

Criteria criteria = session.createCriteria(Customer.class);

Criteria.add(Example.create(c));

Iterator it = criteria.list().iterator();

ts.commit();

HibernateSessionFactory.closeSession();

Query.list与query.iterate(不太重要)

一、 query.iterate查询数据

* query.iterate()方式返回迭代查询

* 会开始发出一条语句：查询所有记录ID语句

* Hibernate: select student0_.id as col_0_0_ from t_student student0_

* 然后有多少条记录，会发出多少条查询语句。

* n + 1问题：n:有n条记录，发出n条查询语句；1 ：发出一条查询所有记录ID语句。

* 出现n+1的原因：因为iterate(迭代查询)是使用缓存的，

第一次查询数据时发出查询语句加载数据并加入到缓存，以后再查询时hibernate会先到ession缓存(一级缓存)中查看数据是否存在，如果存在则直接取出使用，否则发出查询语句进行查询。

	session = HibernateUtils.getSession();
			tx = session.beginTransaction();
			
			/**
			 * 出现N+1问题
			 * 发出查询id列表的sql语句
			 * Hibernate: select student0_.id as col_0_0_ from t_student student0_
			 * 
			 * 再依次发出根据id查询Student对象的sql语句
			 * Hibernate: select student0_.id as id1_0_, student0_.name as name1_0_, 
			 * student0_.createTime as createTime1_0_, student0_.classesid as classesid1_0_ 
			 * from t_student student0_ where student0_.id=?
			 */
			Iterator students = session.createQuery("from Student").iterate();
			
			while (students.hasNext()){
				Student student = (Student)students.next();
				System.out.println(student.getName());
			}
	tx.commit();

二、 query.list()和query.iterate()的区别

先执行query.list()，再执行query.iterate，这样不会出现N+1问题，

* 因为list操作已经将Student对象放到了一级缓存中，所以再次使用iterate操作的时候

* 它首先发出一条查询id列表的sql，再根据id到缓存中取数据，只有在缓存中找不到相应的

* 数据时，才会发出sql到数据库中查询

	List students = session.createQuery("from Student").list();
			
			for (Iterator iter = students.iterator(); iter.hasNext();){
				Student student = (Student)iter.next();
				System.out.println(student.getName());
			}

System.out.println("---------------------------------------------------------");
			 // 不会出现N+1问题，因为list操作已经将数据加入到一级缓存。
			Iterator iters = session.createQuery("from Student").iterate();
			
			while (iters.hasNext()){
				Student student = (Student)iters.next();
				System.out.println(student.getName());
			}

三、两次query.list()

* 会再次发出查询sql

* 在默认情况下list每次都会向数据库发出查询对象的sql，除非配置了查询缓存

* 所以：虽然list操作已经将数据放到一级缓存，但list默认情况下不会利用缓存，而再次发出sql

* 默认情况下，list会向缓存中放入数据，但不会使用数据。

	List students = session.createQuery("from Student").list();
			
			for (Iterator iter = students.iterator(); iter.hasNext();){
				Student student = (Student)iter.next();
				System.out.println(student.getName());
			}
			
			System.out.println("------------------------------------------------");
			
			//会再次发现SQL语句进行查询，因为默认情况list只向缓存中放入数据，不会使用缓存中数据
			students = session.createQuery("from Student").list();
			
			for (Iterator iter = students.iterator(); iter.hasNext();){
				Student student = (Student)iter.next();
				System.out.println(student.getName());
			}