Hibernate第五课--查询

来源：互联网发布：数据统计问题有哪些编辑：程序博客网时间：2024/06/10 23:53

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

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

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

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

4、 QBC：Query By Cretira

5、 QBE：Query By Example

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

（二）HQL

实例一

实体类：

Category

package com.liuhao.hibernate4.demo.query;
 
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
 
import org.apache.commons.lang3.builder.ToStringBuilder;
 
@Entity
public class Category {
private int id;
private String name;
 
public Category() {
};
 
public Category(int id, String name) {
super();
this.id = id;
this.name = 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;
}
 
@Override
public String toString() {
ToStringBuilder builder = new ToStringBuilder(this);
builder.append("id", id);
builder.append("name", name);
return builder.toString();
}
}

Topic

package com.liuhao.hibernate4.demo.query;
 
import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.ManyToOne;
 
import org.apache.commons.lang3.builder.ToStringBuilder;
 
@Entity
public class Topic {
private int id;
private String title;
private Category category;
 
public Topic() {
};
 
public Topic(int id, String title, Category category) {
super();
this.id = id;
this.title = title;
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;
}
 
@ManyToOne(fetch=FetchType.LAZY)
public Category getCategory() {
return category;
}
 
public void setCategory(Category category) {
this.category = category;
}
 
@Override
public String toString() {
ToStringBuilder builder = new ToStringBuilder(this);
builder.append("id", id);
builder.append("title", title);
builder.append("category", category);
return builder.toString();
}
}

Msg

package com.liuhao.hibernate4.demo.query;
 
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.ManyToOne;
 
import org.apache.commons.lang3.builder.ToStringBuilder;
 
@Entity
public class Msg {
 
private int id;
private String cont;
private Topic topic;
 
public Msg(int id, String cont, Topic topic) {
super();
this.id = id;
this.cont = cont;
this.topic = topic;
}
 
public Msg() {
}
 
@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;
}
 
@ManyToOne
public Topic getTopic() {
return topic;
}
 
public void setTopic(Topic topic) {
this.topic = topic;
}
 
@Override
public String toString() {
ToStringBuilder builder = new ToStringBuilder(this);
builder.append("id", id);
builder.append("cont", cont);
builder.append("topic", topic);
return builder.toString();
}
}

MsgInfo

package com.liuhao.hibernate4.demo.query;
 
import org.apache.commons.lang3.builder.ToStringBuilder;
 
public class MsgInfo {
 
private int id;
private String cont;
private String topicName;
private String categoryName;
 
public MsgInfo() {
};
 
public MsgInfo(int id, String cont, String topicName, String categoryName) {
super();
this.id = id;
this.cont = cont;
this.topicName = topicName;
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;
}
 
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;
}
 
@Override
public String toString() {
ToStringBuilder builder = new ToStringBuilder(this);
builder.append("id", id);
builder.append("cont", cont);
builder.append("topicName", topicName);
builder.append("categoryName", categoryName);
return builder.toString();
}
}

导出表结构


@Test
public void schemeExport()
{
new SchemaExport(new Configuration().configure()).create(false, true);
}

1. 存储s


@Test
public void testSave()
{
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.setTitle("t "+i);
t.setCategory(c);
session.save(t);
}
for(int i=0;i<10;i++)
{
Topic t = new Topic();
t.setId(1);
Msg m = new Msg();
m.setCont("msg "+i);
m.setTopic(t);
session.save(m);
}
session.flush();
session.getTransaction().commit();
}

2. Query1


/**
 *   QL:from + 实体类名称 
 * @return @void
 */
@Test
public void testQuery1()
{
Query query = session.createQuery("from Category");
List<Category> lists = query.list();
for(Category c :lists)
{
System.out.println("Category "+c);
}
}

3. Query2


/**
 * 条件
 * @return @void
 */
@Test
public void testQuery2()
{
Query query = session.createQuery("from Category c where c.name> 'c5'");
List<Category> lists = query.list();
for(Category c :lists)
{
System.out.println("Category "+c);
}
}

4. Query3


/**
 * 
 * @return @void
 * order by
 */
@Test
public void testQuery3()
{
Query query = session.createQuery("from Category c order by name desc");
List<Category> lists = query.list();
for(Category c :lists)
{
System.out.println("Category "+c);
}
}

5. 占位符 :


/**
 * 
 * @return @void
 * :paramer 占位符  ':'
 * 
    query.setParameter("min", "c2");
query.setParameter("max", "c8");
    q.setInteger("min",2);
        q.setInteger("max",8);
 */
@Test
public void testQuery4()
{
Query query = session.createQuery("from Category c "
+ "where c.name >:min "
+ "and c.name<:max  "
+ "order by name desc");
query.setParameter("min", "c2");
query.setParameter("max", "c8");
/*
    q.setInteger("min",2);
        q.setInteger("max",8);*/
List<Category> lists = query.list();
for(Category c :lists)
{
System.out.println("Category "+c);
}
}

6. 占位符 ?


/**
 * 
 * @return @void
 * :paramer 占位符  ?
 * 
   query.setParameter(0, "c3");
   query.setParameter(1, "c8");
 */
@Test
public void testQuery5()
{
Query query = session.createQuery("from Category c "
+ "where c.name > ? "
+ "and c.name< ?  "
+ "order by name desc");
query.setParameter(0, "c3");
query.setParameter(1, "c8");
List<Category> lists = query.list();
for(Category c :lists)
{
System.out.println("Category "+c);
}
}

总结：

：name：支持setParameter（"name","test"）和setString

?：支持setParameter（0，"test"）

7. 分页

 
/**
 * 
 * @return @void
 * 分页
 */
@Test
public void testPage()
{
Query query = session.createQuery("from Category c order by c.name desc");
query.setMaxResults(4);
query.setFirstResult(1);
List<Category> lists = query.list();
for(Category c :lists)
{
System.out.println("Category "+c);
}
}

8. Query6


/**
 * 
 * @return @void
 * fetch = lazy 延迟加载
 * 只查询title 所有没有关联 Category
 */
@Test
public void testQuery6()
{
Query query = session.createQuery("from Topic t "
+ "where t.category.id = 1");
List<Topic> lists = query.list();
for(Topic t :lists)
{
System.out.println("Topic "+t.getTitle());  //fetch = lazy 延迟加载  只查询title 所有没有关联 Category
//System.out.println("Topic "+t.getTitle());//关联 Category
}
}

9. Query7

 
/**
 * 
 * @return @void
 */
@Test
public void testQuery7()
{
Query query = session.createQuery("from Msg m "
+ "where m.topic.category.id = 1");
List<Msg> lists = query.list();
for(Msg m :lists)
{
System.out.println("Topic "+m.getCont());  
//System.out.println("Topic "+t.getTitle());
}
}

10. Query8


/**
 * 
 * @return @void
 *  VO Value Object
     *  DTO data transfer object
     *  使用实体类 
 */
@Test
public void testQuery8()
{
Query query = session.createQuery("select "
+ "new com.liuhao.hibernate4.demo.query.MsgInfo("
+ "m.id, m.cont, m.topic.title, m.topic.category.name"
+ ") "
+ "from Msg m");
List<MsgInfo> lists = query.list();
for(MsgInfo m :lists)
{
System.out.println("MsgInfo "+m);
}
}

11 . Join


/**
 * 
 * @return @void
 */
@Test
public void testQuery9()
{
Query query = session.createQuery("select t.title,c.name "
+ "from Topic t join t.category c");
for(Object obj :query.list())
{
Object[] m =(Object[])obj;
System.out.println("Obj[0] "+m[0]+
   "Obj[1] "+m[1]);
}
session.getTransaction().commit();
session.close();
}

12. UniquerResult

 
/**
 * 当确定返回的实例只有一个或者null时 用uniqueResult()方法
 * @return @void
 */
@Test
public void testUniqueResult()
{
Query query = session.createQuery("from Msg m "
+ "where m.cont = :mm");
query.setParameter("mm", "msg 0");
Msg m =  (Msg) query.uniqueResult();
System.out.println("Msg "+m);
}

13. function


/**
 * 当确定返回的实例只有一个或者null时 用uniqueResult()方法
 * @return @void
 */
@Test
public void testFunction()
{
Query query = session.createQuery("select max(m.id),min(m.id),avg(m.id),sum(m.id) from Msg m");
Object obj =  query.uniqueResult();
Object[] m = (Object[])obj;
System.out.println("Object "+m[0]+
"-"+m[1]+
"-"+m[2]+
"-"+m[3]);
}

14. between


/**
 * Between
 * @return @void
 */
@Test
public void testBetween()
{
Query query = session.createQuery("from Msg m"
+ " where m.id between 3 and 5");
for(Msg m : (List<Msg>)query.list())
{
System.out.println("Msg "+m);
}
}

15. In


/**
 * in
 * @return @void
 */
@Test
public void testIn()
{
Query query = session.createQuery("from Msg m"
+ " where m.id in (3,4,5)");
for(Msg m : (List<Msg>)query.list())
{
System.out.println("Msg "+m);
}
}

16. NotNull Null

 
/**
 * Null_NotNull
 * @return @void
 */
@Test
public void testNull_NotNull()
{
    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());
        }
}

实例二：

测试数据同上

INSERT INTO `msg` VALUES ('1', 'msg 0', '1');
INSERT INTO `msg` VALUES ('2', 'msg 1', '1');
INSERT INTO `msg` VALUES ('3', 'msg 2', '1');
INSERT INTO `msg` VALUES ('4', 'msg 3', '1');
INSERT INTO `msg` VALUES ('5', 'msg 4', '1');
INSERT INTO `msg` VALUES ('6', 'msg 5', '1');
INSERT INTO `msg` VALUES ('7', 'msg 6', '1');
INSERT INTO `msg` VALUES ('8', 'msg 7', '1');
INSERT INTO `msg` VALUES ('9', 'msg 8', '1');
INSERT INTO `msg` VALUES ('10', 'msg 9', '1');
 
INSERT INTO `category` VALUES ('1', 'c0');
INSERT INTO `category` VALUES ('2', 'c1');
INSERT INTO `category` VALUES ('3', 'c2');
INSERT INTO `category` VALUES ('4', 'c3');
INSERT INTO `category` VALUES ('5', 'c4');
INSERT INTO `category` VALUES ('6', 'c5');
INSERT INTO `category` VALUES ('7', 'c6');
INSERT INTO `category` VALUES ('8', 'c7');
INSERT INTO `category` VALUES ('9', 'c8');
INSERT INTO `category` VALUES ('10', 'c9');
 
INSERT INTO `topic` VALUES ('1', '2015-01-06 11:11:11', 't 0', '1');
INSERT INTO `topic` VALUES ('2', '2015-02-06 11:11:11', 't 1', '1');
INSERT INTO `topic` VALUES ('3', '2015-03-06 11:11:11', 't 2', '1');
INSERT INTO `topic` VALUES ('4', '2015-04-06 11:11:11', 't 3', '1');
INSERT INTO `topic` VALUES ('5', '2015-05-06 11:11:11', 't 4', '1');
INSERT INTO `topic` VALUES ('6', '2015-06-06 11:11:11', 't 5', '1');
INSERT INTO `topic` VALUES ('7', '2015-07-06 11:11:11', 't 6', '1');
INSERT INTO `topic` VALUES ('8', '2015-08-06 11:11:11', 't 7', '1');
INSERT INTO `topic` VALUES ('9', '2015-09-06 11:11:11', 't 8', '1');
INSERT INTO `topic` VALUES ('10', '2015-10-06 11:11:11', 't 9', '1');

Msg，Category，MsgInfo不变

Topic修改如下

package com.liuhao.hibernate4.demo.query2;
 
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 org.apache.commons.lang3.builder.ToStringBuilder;
 
@Entity
@NamedQueries({ @NamedQuery(name = "topic.select Certain Topic", 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>();
 
public Topic() {
};
 
public Topic(int id, String title, Category category, Date createDate,
List<Msg> msgs) {
super();
this.id = id;
this.title = title;
this.category = category;
this.createDate = createDate;
this.msgs = msgs;
}
 
@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;
}
 
@ManyToOne(fetch = FetchType.LAZY)
public Category getCategory() {
return category;
}
 
public void setCategory(Category category) {
this.category = category;
}
 
public Date getCreateDate() {
return createDate;
}
 
public void setCreateDate(Date createDate) {
this.createDate = createDate;
}
 
@OneToMany(mappedBy = "topic")
public List<Msg> getMsgs() {
return msgs;
}
 
public void setMsgs(List<Msg> msgs) {
this.msgs = msgs;
}
 
@Override
public String toString() {
ToStringBuilder builder = new ToStringBuilder(this);
builder.append("id", id);
builder.append("title", title);
builder.append("category", category);
builder.append("createDate", createDate);
builder.append("msgs", msgs);
return builder.toString();
}
}

1. Empty


/**
 * Empty
 * @return @void
 */
@Test
public void testEmpty()
{
Query q = session.createQuery("from Topic t where t.msgs is empty");
for(Topic t : (List<Topic>)q.list())
{
System.out.println("Topic :"+t);
}
}

2. like


/**
 * Like
 * @return @void
 */
@Test
public void testLike1()
{
Query q = session.createQuery("from Topic t where t.title like '%5' ");
for(Topic t : (List<Topic>)q.list())
{
System.out.println("Topic :"+t);
}
}
/**
 * Like
 * @return @void
 */
@Test
public void testLike2()
{
Query q = session.createQuery("from Topic t where t.title like '_5' ");
for(Topic t : (List<Topic>)q.list())
{
System.out.println("Topic :"+t);
}
}

3. function


/**
 * Function
 * @return @void
 */
@Test
public void testFunction1()
{
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 obj : q.list())
{
Object[] o = (Object[])obj;
System.out.println("args  :"+o[0]+"--"
+o[1]+"--"
+o[2]+"--"
+o[3]+"--"
+o[4]);
}
}
/**
 * Function
 * @return @void
 */
@Test
public void testFunction2()
{
Query q = session.createQuery("select abs(t.id),"
+ "mod(t.id),"
+ "sqrt(t.id),"
+ "from Topic t");
for(Object obj : q.list())
{
Object[] o = (Object[])obj;
System.out.println("args  :"+o[0]+"--"
+o[1]+"--"
+o[2]+"--"
+o[3]+"--"
+o[4]);
}
}

4. Date


/**
 * current_date
 * @return @void
 */
@Test
public void testCurrent()
{
Query q = session.createQuery("select + ,"
+ "current_date,"
+ "current_time,"
+ "current_timesmap,"
+ "t.id,"
+ "from Topic t");
for(Object obj : q.list())
{
Object[] o = (Object[])obj;
System.out.println("args  :"+o[0]+"--"
+o[1]+"--"
+o[2]+"--"
+o[3]+"--"
+o[4]);
}
}
/**
 * Date
 * @return @void
 * @throws ParseException 
 */
@Test
public void testDate() throws ParseException
{
Query q = session.createQuery("from Topic t "
+ "where t.createDate < :date");
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
Date date =sdf.parse(sdf.format((Calendar.getInstance()).getTime()));
q.setParameter("date", date);
for(Topic t : (List<Topic>)q.list())
{
System.out.println("Topic  :"+t);
}
}

5. GroupBy


/**
 * GroupBy
 * @return @void
 * @throws ParseException 
 */
@Test
public void testGroupBy()
{
Query q = session.createQuery("select t.title,count(*) from Topic t "
+ "group by t.title");
for(Object obj : (List<Topic>)q.list())
{
Object[] o =(Object[])obj;
System.out.println("Object  :"+o[0]+"--"+o[1]);
}
}
/**
 * GroupBy
 * @return @void
 */
@Test
public void testGroupBy2()
{
Query q = session.createQuery("select count(*) from Topic t "
+ "group by t.title having count(*)>=1");
for(Long l : (List<Long>)q.list())
{
System.out.println("Object  :"+l);
}
}

6. where子查询


/**
 * Where 子查询
 * @return @void
 */
@Test
public void testWhere()
{
Query q = session.createQuery("from Topic t "
+ "where t.id <(select avg(t2.id) from Topic t2)");
for(Topic t : (List<Topic>)q.list())
{
System.out.println("Topic  :"+t);
}
}

7. not exists/exists


/**
 * 
 * exists 用法:是否至少返回一行 (true/false)
 * @return @void
 */
@Test
public void testWhere2()
{
//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("from Topic t "
+ "where not exists (select m.id from Msg m "
+ "where m.topic.id = t.id)");
for(Topic t : (List<Topic>)q.list())
{
System.out.println("Topic  :"+t);
}
}

8. update，delete


/**
 * Update_delete
 * 规范并没有说明是不是要更新persistent object，所以如果要使用，
 * 建议在单独的trasaction中执行
 * 
 * update ,delete 一定要添加事务!
 * @return @void
 */
@Test
public void testUpdate_delete()
{
session.beginTransaction();
Query q = session.createQuery("update from Category c set c.name='c00' where c.name='c0'");
int i = q.executeUpdate();
System.out.println("修改 : "+i+ " 行!");
Query q2 = session.createQuery("from Category");
for(Category c : (List<Category>)q2.list())
{
System.out.println("Category  :"+c);
}
session.getTransaction().commit();
}

9. NameQuery


/**
 * 
 * 查询命名
 * @return @void
 */
@Test
public void testNameQuery()
{                                    
Query q = session.getNamedQuery("topic.select Certain Topic");
q.setParameter("id", 5);
Topic t = (Topic) q.uniqueResult();
System.out.println("Topic  :"+t);
}

10. NativeSQL

 
/**
 * limit:取出 limit m,n
 * index 0 开始 从m开始后面的n条记录 可用于分页
 * native SQL
 * @return @void
 */
@Test
public void testNativeSQL()
{                                    
Query q = session.createSQLQuery("select * from topic limit 2,4 ").addEntity(Topic.class);
for(Topic t : (List<Topic>)q.list())
{
System.out.println("Topic  :"+t);
}
}

总结：

1. q.list（）返回类型List<?>一定要对应不然会匹配错误

2. 当确定返回的实例只有一个或者null时用uniqueResult()方法

3. q.excuteUqdate（）update，delete 注意要commit（）

4. 占位符 :param , ?

:param 只能使用setparamter（）

？使用setParamter（）和setString/Long/Boolean（）参数对应类型

5. 分页

query.setMaxResults(4);//每页显示的最大记录数
query.setFirstResult(1);//从第几条开始显示，从0开始

6. 当查询结果，使用自定义类做结果集

Query query = session.createQuery("select "
+ "new com.liuhao.hibernate4.demo.query.MsgInfo("
+ "m.id, m.cont, m.topic.title, m.topic.category.name"
+ ") "
+ "from Msg m");
List<MsgInfo> lists = query.list();
for(MsgInfo m :lists)
{
System.out.println("MsgInfo "+m);
}

7. 右外连接

Query query = session.createQuery("select t.title,c.name "
+ "from Topic t join t.category c");

8.Date

/**
 * current_date
 * @return @void
 */
@Test
public void testCurrent()
{
Query q = session.createQuery("select + ,"
+ "current_date,"
+ "current_time,"
+ "current_timesmap,"
+ "t.id,"
+ "from Topic t");
for(Object obj : q.list())
{
Object[] o = (Object[])obj;
System.out.println("args  :"+o[0]+"--"
+o[1]+"--"
+o[2]+"--"
+o[3]+"--"
+o[4]);
}
}
/**
 * Date
 * @return @void
 * @throws ParseException 
 */
@Test
public void testDate() throws ParseException
{
Query q = session.createQuery("from Topic t "
+ "where t.createDate < :date");
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
Date date =sdf.parse(sdf.format((Calendar.getInstance()).getTime()));
q.setParameter("date", date);
for(Topic t : (List<Topic>)q.list())
{
System.out.println("Topic  :"+t);
}
}

9. 命名查询

/**
 * 
 * 查询命名
 * @return @void
 */
@Test
public void testNameQuery()
{                                    
Query q = session.getNamedQuery("topic.select Certain Topic");
q.setParameter("id", 5);
Topic t = (Topic) q.uniqueResult();
System.out.println("Topic  :"+t);
}

10. Native查询

 
/**
 * limit:取出 limit m,n
 * index 0 开始 从m开始后面的n条记录 可用于分页
 * native SQL
 * @return @void
 */
@Test
public void testNativeSQL()
{                                    
Query q = session.createSQLQuery("select * from topic limit 2,4 ").addEntity(Topic.class);
for(Topic t : (List<Topic>)q.list())
{
System.out.println("Topic  :"+t);
}
}

函数

注意返回类型的接受

//QueryTest1
/**
 * 当确定返回的实例只有一个或者null时 用uniqueResult()方法
 * @return @void
 */
@Test
public void testFunction()
{
Query query = session.createQuery("select max(m.id),min(m.id),avg(m.id),sum(m.id) from Msg m");
Object obj =  query.uniqueResult();
Object[] m = (Object[])obj;
System.out.println("Object "+m[0]+
"-"+m[1]+
"-"+m[2]+
"-"+m[3]);
}
    //QueryTest2
    /**
     * Function
     * @return @void
     */
    @Test
    public void testFunction1()
    {
        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 obj : q.list())
        {
            Object[] o = (Object[])obj;
            System.out.println("args  :"+o[0]+"--"
                                +o[1]+"--"
                                +o[2]+"--"
                                +o[3]+"--"
                                +o[4]);
        }
    }
    
    /**
     * Function
     * @return @void
     */
    @Test
    public void testFunction2()
    {
        Query q = session.createQuery("select abs(t.id),"
                                    + "mod(t.id),"
                                    + "sqrt(t.id),"
                                    + "from Topic t");
        for(Object obj : q.list())
        {
            Object[] o = (Object[])obj;
            System.out.println("args  :"+o[0]+"--"
                    +o[1]+"--"
                    +o[2]+"--"
                    +o[3]+"--"
                    +o[4]);
        }
    }  
/**
 * Where 子查询
 * @return @void
 */
@Test
public void testWhere()
{
Query q = session.createQuery("from Topic t "
+ "where t.id <(select avg(t2.id) from Topic t2)");
for(Topic t : (List<Topic>)q.list())
{
System.out.println("Topic  :"+t);
}
}

各种关键字查询

 
/**
 * Between
 * @return @void
 */
@Test
public void testBetween()
{
Query query = session.createQuery("from Msg m"
+ " where m.id between 3 and 5");
for(Msg m : (List<Msg>)query.list())
{
System.out.println("Msg "+m);
}
}
/**
 * in
 * @return @void
 */
@Test
public void testIn()
{
Query query = session.createQuery("from Msg m"
+ " where m.id in (3,4,5)");
for(Msg m : (List<Msg>)query.list())
{
System.out.println("Msg "+m);
}
}
 
/**
 * Null_NotNull
 * @return @void
 */
@Test
public void testNull_NotNull()
{
    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());
        }
}
/**
 * 
 * exists 用法:是否至少返回一行 (true/false)
 * @return @void
 */
@Test
public void testWhere2()
{
//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("from Topic t "
+ "where not exists (select m.id from Msg m "
+ "where m.topic.id = t.id)");
for(Topic t : (List<Topic>)q.list())
{
System.out.println("Topic  :"+t);
}
}

--------------------------------------供查询-----------------------------

第20课 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限定查询

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

QBE (QueryBy 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();
==============================

Hibernate中 Restrictions.or()和Restrictions.disjunction()的区别是什么？
比较运算符
HQL运算符    QBC运算符    含义
=    Restrictions.eq()    等于
<>    Restrictions.not(Exprission.eq()) 不等于
>    Restrictions.gt()    大于
>=    Restrictions.ge()    大于等于
<    Restrictions.lt()    小于
<=    Restrictions.le()    小于等于
is null Restrictions.isnull() 等于空值
is not null Restrictions.isNotNull() 非空值
like    Restrictions.like()   字符串模式匹配
and   Restrictions.and()    逻辑与
and   Restrictions.conjunction() 逻辑与
or    Restrictions.or()    逻辑或
or    Restrictions.disjunction() 逻辑或
not   Restrictions.not()    逻辑非
in(列表)   Restrictions.in()   等于列表中的某一个值
ont in(列表)    Restrictions.not(Restrictions.in())不等于列表中任意一个值
between x and y Restrictions.between() 闭区间xy中的任意值
not between x and y Restrictions.not(Restrictions..between()) 小于值X或者大于值y

3: HQL
String hql = "select s.name ,avg(s.age) from Student s group bys.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的情况。

三、工具类Order提供设置排序方式

Order.asc(String propertyName)
升序排序
Order.desc(String propertyName)
降序排序

四、工具类Projections提供对查询结果进行统计与分组操作

Porjections.avg(String propertyName)
求某属性的平均值
Projections.count(String propertyName)
统计某属性的数量
Projections.countDistinct(String propertyName)
统计某属性的不同值的数量
Projections.groupProperty(String propertyName)
指定一组属性值
Projections.max(String propertyName)
某属性的最大值
Projections.min(String propertyName)
某属性的最小值
Projections.projectionList()
创建一个新的projectionList对象
Projections.rowCount()
查询结果集中记录的条数
Projections.sum(String propertyName)
返回某属性值的合计

五、 QBC分页查询

Criteria为我们提供了两个有用的方法：setFirstResult(intfirstResult)和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绑定。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();

第21课 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();

阅读全文

0 0

Hibernate第五课--查询

第20课 Query by Criteria(QBC)

一、 实体代码：

二、 Restrictions用法

三、 工具类Order提供设置排序方式

四、 工具类Projections提供对查询结果进行统计与分组操作

五、 QBC分页查询

六、 QBC复合查询

七、 QBC离线查询

第21课 Query By Example(QBE)

一、 实例代码

一、实体代码：

三、工具类Order提供设置排序方式

四、工具类Projections提供对查询结果进行统计与分组操作

一、实例代码