【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;@Entitypublic class Category {private int id;private String name;@Id@GeneratedValuepublic 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@GeneratedValuepublic 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;@Entitypublic class Msg {private int id;private String cont;private Topic topic;@ManyToOnepublic Topic getTopic() {return topic;}public void setTopic(Topic topic) {this.topic = topic;}@Id@GeneratedValuepublic 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->DBprivate 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@Testpublic 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是测试一个集合空不空
——————————————————————————————————————

@Testpublic 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语句里面的一些函数)@Testpublic 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：

@Testpublic 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
——————————————————————————————————————
数据库的一些关键词

@Testpublic 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


什么时候用到获取数据库的时间呢？
我直接取我程序里面的时间不就可以了吗？干嘛非要取数据库的时间呢？
因为在一个集群化的环境中，会有多台服务器围绕一个数据库来进行服务，如果你的日期需要在多台服务器上保持一致的话，你用服务器的时间能保持一致吗？肯定会有误差，但是我都用唯一的一台数据库的时间就不会有误差了。
——————————————————————————————————————
关于日期的比较:

@Testpublic 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里面。@Testpublic 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：


测试二：

@Testpublic 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
——————————————————————————————————————

@Testpublic 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后条件内的所有值(其实找极端值就可以了呗)@Testpublic 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执行效率高@Testpublic 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中执行@Testpublic 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方法拿到命名查询，这个命名查询写在实体类的上面测试方法：@Testpublic 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（了解）@Testpublic 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