【hibernate框架】EJBQL第二部分

工程和上一个一样，Topic有所改变，加了一个Topic的Msg的List列表属性，这个属性用来存放每个Topic下有多少条回帖。

例子项目：BBS小项目
（EJBQL是HQL的子集，所以直接说是HQL也无可厚非）
Category(版块)：里面有不同的主题，不同的主题有不同的发帖
Topic(主题)，Msg(具体的回复帖子)

Category与Topic之间的关系是一对多(OneToMany)，Topic与Msg的关系也是一对多。(OneToMany)。

具体的类：
Category.java:

package com.bjsxt.hibernate;

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

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

Topic.java:

package com.bjsxt.hibernate;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;


import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.ManyToOne;
import javax.persistence.NamedQueries;
import javax.persistence.NamedQuery;
import javax.persistence.OneToMany;
import javax.persistence.Temporal;
import javax.persistence.TemporalType;


@Entity
@NamedQueries({
@NamedQuery(name="topic.selectCertainTopic", query="from Topic t where t.id = :id")})


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;
	}
	@Temporal(TemporalType.TIME)
	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;
	}
	
}

Msg.java:

package com.bjsxt.hibernate;

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


@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.bjsxt.hibernate;

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

各种测试：

//is empty and is not empty
	@Test
	public void testHQL_20() {
		Session session = sf.openSession();
		session.beginTransaction();
		//说t的Mags集合是否为空，即是寻找没有任何回帖的主题
		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());
		}
		session.getTransaction().commit();
		session.close();
		
	}

测试结果：

找到了没有任何回帖的主题(想得到msgs就得加一个一对多的导航)

Hibernate: 

     select

         topic0_.id as id2_,

         topic0_.category_id as category4_2_,

         topic0_.createDate as createDate2_,

         topic0_.title as title2_ 

     from

         Topic topic0_ 

     where

         not (exists (select   //用了exists

             msgs1_.id 

         from

             Msg msgs1_ 

         where

             topic0_.id=msgs1_.topic_id))

 2-t1

 3-t2

 4-t3

 5-t4

 6-t5

 7-t6

 8-t7

 9-t8

 10-t9

is Null是测试一个属性空不空，is empty是测试一个集合空不空

——————————————————————————————————————

@Test
	public void testHQL_21() {
		Session session = sf.openSession();
		session.beginTransaction();
		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());
		}
		session.getTransaction().commit();
		session.close();
		
	}

测试结果：

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_.title like '%5'

6-t5

其中，‘%’代表0个或多个，‘_’代表一个

——————————————————————————————————————

//不重要(使用HQL语句里面的一些函数)
	@Test
	public void testHQL_23() {
		Session session = sf.openSession();
		session.beginTransaction();
		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] + "-");
		}
		session.getTransaction().commit();
		session.close();
		
	}

测试结果：

Hibernate: 

     select

         lower(topic0_.title) as col_0_0_,

         upper(topic0_.title) as col_1_0_,

         trim(topic0_.title) as col_2_0_,

         concat(topic0_.title,

         '***') as col_3_0_,

         length(topic0_.title) as col_4_0_ 

     from

         Topic topic0_

 t0-T0-t0-t0***-2-

 t1-T1-t1-t1***-2-

 t2-T2-t2-t2***-2-

 t3-T3-t3-t3***-2-

 t4-T4-t4-t4***-2-

 t5-T5-t5-t5***-2-

 t6-T6-t6-t6***-2-

 t7-T7-t7-t7***-2-

 t8-T8-t8-t8***-2-

 t9-T9-t9-t9***-2-

函数测试2：

@Test
	public void testHQL_24() {
		Session session = sf.openSession();
		session.beginTransaction();
		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] );
		}
		session.getTransaction().commit();
		session.close();
		
	}

测试结果：

Hibernate: 

     select

         abs(topic0_.id) as col_0_0_,

         sqrt(topic0_.id) as col_1_0_,

         mod(topic0_.id,

         2) as col_2_0_ 

     from

         Topic topic0_

 1-1.0-1

 2-1.4142135623730951-0

 3-1.7320508075688772-1

 4-2.0-0

 5-2.23606797749979-1

 6-2.449489742783178-0

 7-2.6457513110645907-1

 8-2.8284271247461903-0

 9-3.0-1

 10-3.1622776601683795-0

——————————————————————————————————————

数据库的一些关键词

@Test
	public void testHQL_25() {
		Session session = sf.openSession();
		session.beginTransaction();
		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]);
		}
		session.getTransaction().commit();
		session.close();
		
	}

current_date指的是数据库当前日期，current_time指的是数据库当前时间, current_timestamp指的是数据库当前日期和时间。 

测试结果：

Hibernate: 

     select

         current_date as col_0_0_,

         current_time as col_1_0_,

         current_timestamp as col_2_0_,

         topic0_.id as col_3_0_ 

     from

         Topic topic0_

 2015-02-01 | 15:59:53 | 2015-02-01 15:59:53.0 | 1

 2015-02-01 | 15:59:53 | 2015-02-01 15:59:53.0 | 2

 2015-02-01 | 15:59:53 | 2015-02-01 15:59:53.0 | 3

 2015-02-01 | 15:59:53 | 2015-02-01 15:59:53.0 | 4

 2015-02-01 | 15:59:53 | 2015-02-01 15:59:53.0 | 5

 2015-02-01 | 15:59:53 | 2015-02-01 15:59:53.0 | 6

 2015-02-01 | 15:59:53 | 2015-02-01 15:59:53.0 | 7

 2015-02-01 | 15:59:53 | 2015-02-01 15:59:53.0 | 8

 2015-02-01 | 15:59:53 | 2015-02-01 15:59:53.0 | 9

 2015-02-01 | 15:59:53 | 2015-02-01 15:59:53.0 | 10

什么时候用到获取数据库的时间呢？

我直接取我程序里面的时间不就可以了吗？干嘛非要取数据库的时间呢？

因为在一个集群化的环境中，会有多台服务器围绕一个数据库来进行服务，如果你的日期需要在多台服务器上保持一致的话，你用服务器的时间能保持一致吗？肯定会有误差，但是我都用唯一的一台数据库的时间就不会有误差了。

——————————————————————————————————————

关于日期的比较:

@Test
	public void testHQL_26() {
		Session session = sf.openSession();
		session.beginTransaction();
		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());
		}
		session.getTransaction().commit();
		session.close();
		
	}

测试结果：

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_.createDate<?

 t0

 t1

 t2

 t3

 t4

 t5

 t6

 t7

 t8

 t9

——————————————————————————————————————

//group by语句用于结合合计函数，根据一个或多个列对结果集进行分组
	//你的group by后面的属性一定要在前面的select里面。
	@Test
	public void testHQL_27() {
		Session session = sf.openSession();
		session.beginTransaction();
		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]);
		}
		session.getTransaction().commit();
		session.close();
		
	}

测试结果Hibernate: 

 

测试二：

select

         topic0_.title as col_0_0_,

         count(*) as col_1_0_ 

     from

         Topic topic0_ 

     group by

         topic0_.title

 t0|1

 t1|1

 t2|1

 t3|1

 t4|1

 t5|1

 t6|1

 t7|1

 t8|1

 t9|1：

@Test
	public void testHQL_28() {
		Session session = sf.openSession();
		session.beginTransaction();
		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]);
		}
		session.getTransaction().commit();
		session.close();
		
	}

测试结果：

——————————————————————————————————————

Hibernate: 

     select

         topic0_.title as col_0_0_,

         count(*) as col_1_0_ 

     from

         Topic topic0_ 

     group by

         topic0_.title 

     having

         count(*)>=1

 t0|1

 t1|1

 t2|1

 t3|1

 t4|1

 t5|1

 t6|1

 t7|1

 t8|1

 t9|1

@Test
	public void testHQL_29() {
		Session session = sf.openSession();
		session.beginTransaction();
		//取平均值，看哪些数据小于这些平均值
		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());
		}
		session.getTransaction().commit();
		session.close();
		
	}

测试结果：

一共10个id，(1+2+3+4+5+6+7+8+9+10)/10=5.5，所以取出的是小于5的id的title。

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_.id<(

             select

                 avg(topic1_.id) 

             from

                 Topic topic1_

         )

 t0

 t1

 t2

 t3

 t4

——————————————————————————————————————

//ALL函数，就是满足ALL后条件内的所有值(其实找极端值就可以了呗)
	@Test
	public void testHQL_30() {
		Session session = sf.openSession();
		session.beginTransaction();
		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());
		}
		session.getTransaction().commit();
		session.close();
		
	}

测试结果:

——————————————————————————————————————

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_.id<all (

             select

                 topic1_.id 

             from

                 Topic topic1_ 

             where

                 mod(topic1_.id, 2)=0

         )

 t0

//用in 可以实现exists的功能
	//但是exists执行效率高
	@Test
	public void testHQL_31() {
		Session session = sf.openSession();
		session.beginTransaction();// t.id not in (1)
		//括号里的意思是msg表里面存在不存在属于某个topic的msg(总的意思是找哪个topic下面没有回帖)
		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());
		}
		session.getTransaction().commit();
		session.close();
		
	}

测试结果：

——————————————————————————————————————

Hibernate: 

     select

         topic0_.id as id2_,

         topic0_.category_id as category4_2_,

         topic0_.createDate as createDate2_,

         topic0_.title as title2_ 

     from

         Topic topic0_ 

     where

         not (exists (select

             msg1_.id 

         from

             Msg msg1_ 

         where

             msg1_.topic_id=topic0_.id))

 t1

 t2

 t3

 t4

 t5

 t6

 t7

 t8

 t9

//update and delete
	//规范并没有说明是不是要更新persistent object，所以如果要使用，建议在单独的trasaction中执行
	@Test
	public void testHQL_32() {
		Session session = sf.openSession();
		session.beginTransaction();
		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();
		session.getTransaction().commit();
		session.close();
		
	}

测试结果：

——————————————————————————————————————

Hibernate: 

     update

         Topic 

     set

         title=upper(title)

 Hibernate: 

     select

         topic0_.id as id2_,

         topic0_.category_id as category4_2_,

         topic0_.createDate as createDate2_,

         topic0_.title as title2_ 

     from

         Topic topic0_

 T0

 T1

 T2

 T3

 T4

 T5

 T6

 T7

 T8

 T9

 Hibernate: 

     update

         Topic 

     set

         title=lower(title)

//不重要
getNamedQuery方法拿到命名查询，这个命名查询写在实体类的上面
测试方法：
	@Test
	public void testHQL_33() {
		Session session = sf.openSession();
		session.beginTransaction();
		Query q = session.getNamedQuery("topic.selectCertainTopic");
		q.setParameter("id", 5);
		Topic t = (Topic)q.uniqueResult();
		System.out.println(t.getTitle());
		session.getTransaction().commit();
		session.close();
		
	}

实体类部分代码：

package com.bjsxt.hibernate;

import java.util.ArrayList;

@Entity
@NamedQueries({
@NamedQuery(name="topic.selectCertainTopic", query="from Topic t where t.id = :id")})
public class Topic {........

测试结果:

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_.id=?

 t4

——————————————————————————————————————

使用本地的SQL语句：

//Native（了解）
	@Test
	public void testHQL_34() {
		Session session = sf.openSession();
		session.beginTransaction();
		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());
		}
		session.getTransaction().commit();
		session.close();
		
	}

测试结果：

Hibernate: 

     select

         * 

     from

         category limit 2,

         4

 c2

 c3

 c4

 c5

——————————————————————————————————————