工程和上一个一样,Topic有所改变,加了一个Topic的Msg的List列表属性,这个属性用来存放每个Topic下有多少条回帖。
例子项目:BBS小项目
(EJBQL是HQL的子集,所以直接说是HQL也无可厚非)
Category(版块):里面有不同的主题,不同的主题有不同的发帖
Topic(主题),Msg(具体的回复帖子)
Category与Topic之间的关系是一对多(OneToMany),Topic与Msg的关系也是一对多。(OneToMany)。
具体的类:
Category.java:
Topic.java:
Msg.java:
MsgInfo.java:
各种测试:
测试结果:
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
找到了没有任何回帖的主题(想得到msgs就得加一个一对多的导航)
is Null是测试一个属性空不空,is empty是测试一个集合空不空
——————————————————————————————————————
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个或多个,‘_’代表一个
——————————————————————————————————————
测试结果:
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:
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
——————————————————————————————————————
数据库的一些关键词
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
什么时候用到获取数据库的时间呢?
我直接取我程序里面的时间不就可以了吗?干嘛非要取数据库的时间呢?
因为在一个集群化的环境中,会有多台服务器围绕一个数据库来进行服务,如果你的日期需要在多台服务器上保持一致的话,你用服务器的时间能保持一致吗?肯定会有误差,但是我都用唯一的一台数据库的时间就不会有误差了。
——————————————————————————————————————
关于日期的比较:
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
——————————————————————————————————————
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:
测试二:
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
——————————————————————————————————————
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
一共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<all (
select
topic1_.id
from
Topic topic1_
where
mod(topic1_.id, 2)=0
)
t0
——————————————————————————————————————
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
——————————————————————————————————————
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)
——————————————————————————————————————
测试结果:
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语句:
Hibernate:
select
*
from
category limit 2,
4
c2
c3
c4
c5
——————————————————————————————————————
转载请注明出处:http://blog.csdn.net/acmman/article/details/43917909
例子项目: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(); }
测试结果:
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
找到了没有任何回帖的主题(想得到msgs就得加一个一对多的导航)
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(); }测试结果:
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
一共10个id,(1+2+3+4+5+6+7+8+9+10)/10=5.5,所以取出的是小于5的id的title。
——————————————————————————————————————
//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
——————————————————————————————————————
转载请注明出处:http://blog.csdn.net/acmman/article/details/43917909