本文主要探讨hibernate的简单查询，主要是使用Query进行的查询。
1.首先看下annotation的API中关于查询的描述
2.3. 映射查询
使用注解还可以映射EJBQL/HQL查询. @NamedQuery 和@NamedQueries是可使用在类和包上的注解. 但是它们的定义在session factory/entity manager factory范围中是都可见的. 命名式查询通过它的名字和实际的查询字符串来定义.
javax.persistence.NamedQueries(
@javax.persistence.NamedQuery(name="plane.getAll", query="select p from Plane p")
)
package org.hibernate.test.annotations.query;
...
@Entity
@NamedQuery(name="night.moreRecentThan", query="select n from Night n where n.date >= :date")
public class Night {
...
}
public class MyDao {
doStuff() {
Query q = s.getNamedQuery("night.moreRecentThan");
q.setDate( "date", aMonthAgo );
List results = q.list();
...
}
...
}
还可以通过定义 QueryHint 数组的hints 属性为查询提供一些hint信息.
下面是目前可以使用的一些Hibernate hint：
表 2.2. Query hints
| hint | description |
|---|---|
| org.hibernate.cacheable | 查询是否与二级缓存交互(默认值为false) |
| org.hibernate.cacheRegion | 设置缓存区名称 (默认为otherwise) |
| org.hibernate.timeout | 查询超时设定 |
| org.hibernate.fetchSize | 所获取的结果集(resultset)大小 |
| org.hibernate.flushMode | 本次查询所用的刷新模式 |
| org.hibernate.cacheMode | 本次查询所用的缓存模式 |
| org.hibernate.readOnly | 是否将本次查询所加载的实体设为只读(默认为false) |
| org.hibernate.comment | 将查询注释添加入所生成的SQL |
你还可以映射本地化查询(也就是普通SQL查询). 不过这需要你使用@SqlResultSetMapping注解来描述SQL的resultset的结构 (如果你打算定义多个结果集映射，可是使用@SqlResultSetMappings). @SqlResultSetMapping和@NamedQuery, @SqlResultSetMapping一样,可以定义在类和包一级. 但是@SqlResultSetMapping的作用域为应用级. 下面我们会看到,@NamedNativeQuery 注解中 resultSetMapping参数值为@SqlResultSetMapping的名字. 结果集映射定义了通过本地化查询返回值和实体的映射. 该实体中的每一个字段都绑定到SQL结果集中的某个列上. 该实体的所有字段包括子类的所有字段以及 关联实体的外键列都必须在SQL查询中有对应的定义. 如果实体中的属性和SQL查询中的列名相同,这种情况下可以不进行定义字段映射.
@NamedNativeQuery(name="night&area", query="select night.id nid, night.night_duration, "
+ " night.night_date, area.id aid, night.area_id, area.name "
+ "from Night night, Area area where night.area_id = area.id", resultSetMapping="joinMapping")
@SqlResultSetMapping(name="joinMapping", entities={
@EntityResult(entityClass=org.hibernate.test.annotations.query.Night.class, fields = {
@FieldResult(name="id", column="nid"),
@FieldResult(name="duration", column="night_duration"),
@FieldResult(name="date", column="night_date"),
@FieldResult(name="area", column="area_id"),
discriminatorColumn="disc"
}),
@EntityResult(entityClass=org.hibernate.test.annotations.query.Area.class, fields = {
@FieldResult(name="id", column="aid"),
@FieldResult(name="name", column="name")
})
}
)
在上面这个例子中,名为night&area的查询 和joinMapping结果集映射对应. 该映射返回两个实体,分别为Night 和Area,其中每个属性都和一个列关联, 列名通过查询获取.下面我们看一个隐式声明属性和列映射关系的例子.
@Entity
@SqlResultSetMapping(name="implicit", entities=@EntityResult(entityClass=org.hibernate.test.annotations.query.SpaceShip.class))
@NamedNativeQuery(name="implicitSample", query="select * from SpaceShip", resultSetMapping="implicit")
public class SpaceShip {
private String name;
private String model;
private double speed;
@Id
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
@Column(name="model_txt")
public String getModel() {
return model;
}
public void setModel(String model) {
this.model = model;
}
public double getSpeed() {
return speed;
}
public void setSpeed(double speed) {
this.speed = speed;
}
}
在这个例子中,我们只需要定义结果集映射中的实体成员. 属性和列名之间的映射借助实体中包含映射信息来完成. 在这个例子中,model属性绑定到model_txt列. 如果和相关实体的关联设计到组合主键, 那么应该使用@FieldResult注解来定义每个外键列. @FieldResult的名字由以下几部分组成： 定义这种关系的属性名字+"."＋主键名或主键列或主键属性.
@Entity
@SqlResultSetMapping(name="compositekey",
entities=@EntityResult(entityClass=org.hibernate.test.annotations.query.SpaceShip.class,
fields = {
@FieldResult(name="name", column = "name"),
@FieldResult(name="model", column = "model"),
@FieldResult(name="speed", column = "speed"),
@FieldResult(name="captain.firstname", column = "firstn"),
@FieldResult(name="captain.lastname", column = "lastn"),
@FieldResult(name="dimensions.length", column = "length"),
@FieldResult(name="dimensions.width", column = "width")
}),
columns = { @ColumnResult(name = "surface"),
@ColumnResult(name = "volume") } )
@NamedNativeQuery(name="compositekey",
query="select name, model, speed, lname as lastn, fname as firstn, length, width, length * width as surface from SpaceShip",
resultSetMapping="compositekey")
} )
public class SpaceShip {
private String name;
private String model;
private double speed;
private Captain captain;
private Dimensions dimensions;
@Id
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
@ManyToOne(fetch= FetchType.LAZY)
@JoinColumns( {
@JoinColumn(name="fname", referencedColumnName = "firstname"),
@JoinColumn(name="lname", referencedColumnName = "lastname")
} )
public Captain getCaptain() {
return captain;
}
public void setCaptain(Captain captain) {
this.captain = captain;
}
public String getModel() {
return model;
}
public void setModel(String model) {
this.model = model;
}
public double getSpeed() {
return speed;
}
public void setSpeed(double speed) {
this.speed = speed;
}
public Dimensions getDimensions() {
return dimensions;
}
public void setDimensions(Dimensions dimensions) {
this.dimensions = dimensions;
}
}
@Entity
@IdClass(Identity.class)
public class Captain implements Serializable {
private String firstname;
private String lastname;
@Id
public String getFirstname() {
return firstname;
}
public void setFirstname(String firstname) {
this.firstname = firstname;
}
@Id
public String getLastname() {
return lastname;
}
public void setLastname(String lastname) {
this.lastname = lastname;
}
}
注意
观察dimension属性你会发现Hibernate支持用"."符号来表示嵌入式对象. EJB3实现不必支持这个特征,但是我们做到了:-)
如果查询返回的是单个实体,或者你打算使用系统默认的映射, 这种情况下可以不使用resultSetMapping 而是使用resultClass属性:
@NamedNativeQuery(name="implicitSample", query="select * from SpaceShip",
resultClass=SpaceShip.class)
public class SpaceShip {
某些本地查询返回的是scalar值,例如报表查询. 你可以通过@ColumnResult将其映射到 @SqlResultsetMapping上. 甚至还可以在同一个本地查询的结果中混合实体和scalar类型(不过这种情况比较少见).
@SqlResultSetMapping(name="scalar", columns=@ColumnResult(name="dimension"))
@NamedNativeQuery(name="scalar", query="select length*width as dimension from SpaceShip", resultSetMapping="scalar")
本地查询中还有另外一个hint属性： org.hibernate.callable. 这个属性的布尔变量值表明这个查询是否是一个存储过程.
List cats = session.createQuery( "from Cat as cat where cat.birthdate < ?") .setDate(0, date) .list(); List mothers = session.createQuery( "select mother from Cat as cat join cat.mother as mother where cat.name = ?") .setString(0, name) .list(); List kittens = session.createQuery( "from Cat as cat where cat.mother = ?") .setEntity(0, pk) .list(); Cat mother = (Cat) session.createQuery( "select cat.mother from Cat as cat where cat = ?") .setEntity(0, izi) .uniqueResult();]] Query mothersWithKittens = (Cat) session.createQuery( "select mother from Cat as mother left join fetch mother.kittens"); Set uniqueMothers = new HashSet(mothersWithKittens.list());
Hibernate queries sometimes return tuples of objects. Each tuple is returned as an array:
Iterator kittensAndMothers = sess.createQuery( "select kitten, mother from Cat kitten join kitten.mother mother") .list() .iterator(); while ( kittensAndMothers.hasNext() ) { Object[] tuple = (Object[]) kittensAndMothers.next(); Cat kitten = (Cat) tuple[0]; Cat mother = (Cat) tuple[1]; .... }
//named parameter (preferred) Query q = sess.createQuery("from DomesticCat cat where cat.name = :name"); q.setString("name", "Fritz"); Iterator cats = q.iterate();
//positional parameter Query q = sess.createQuery("from DomesticCat cat where cat.name = ?"); q.setString(0, "Izi"); Iterator cats = q.iterate();
//named parameter list List names = new ArrayList(); names.add("Izi"); names.add("Fritz"); Query q = sess.createQuery("from DomesticCat cat where cat.name in (:namesList)"); q.setParameterList("namesList", names); List cats = q.list();
Collection blackKittens = session.createFilter( pk.getKittens(), "where this.color = ?") .setParameter( Color.BLACK, Hibernate.custom(ColorUserType.class) ) .list() );
Collection blackKittenMates = session.createFilter( pk.getKittens(), "select this.mate where this.color = eg.Color.BLACK.intValue") .list();
Even an empty filter query is useful, e.g. to load a subset of elements in a large collection:
Collection tenKittens = session.createFilter( mother.getKittens(), "") .setFirstResult(0).setMaxResults(10) .list();
Criteria crit = session.createCriteria(Cat.class); crit.add( Restrictions.eq( "color", eg.Color.BLACK ) ); crit.setMaxResults(10); List cats = crit.list();
Criteria以及相关的样例(Example)API将会再第 15 章 条件查询(Criteria Queries)中详细讨论。
List cats = session.createSQLQuery("SELECT {cat.*} FROM CAT {cat} WHERE ROWNUM<10") .addEntity("cat", Cat.class) .list();
List cats = session.createSQLQuery( "SELECT {cat}.ID AS {cat.id}, {cat}.SEX AS {cat.sex}, " + "{cat}.MATE AS {cat.mate}, {cat}.SUBCLASS AS {cat.class}, ... " + "FROM CAT {cat} WHERE ROWNUM<10") .addEntity("cat", Cat.class) .list()
SQL queries can contain named and positional parameters, just like Hibernate queries. More information about native SQL queries in Hibernate can be found in 第 16 章 Native SQL查询.
新建3个类，Category、Topic和Msg，其中Category和Topic是1对多，Topic和Msg是1对多
另外还有1个查询辅助类MsgInfo，注意此类不适用注解
Category
package com.baosight.model;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
@Entity
public class Category {
private String id;
private String name;
@Id
@GeneratedValue
public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
}
package com.baosight.model;
import java.util.Date;
import java.util.List;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.ManyToOne;
import javax.persistence.NamedQueries;
import javax.persistence.NamedQuery;
import javax.persistence.OneToMany;
@Entity
@NamedQueries({ @NamedQuery(name = "topic.selectCertainTopic", query = "from Topic t where t.id=:id") })
public class Topic {
private String id;
private String title;
private Category category;
private Date createDate;
private List<Msg> msgs;
@ManyToOne
public Category getCategory() {
return category;
}
public void setCategory(Category category) {
this.category = category;
}
@Id
@GeneratedValue
public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}
public String getTitle() {
return title;
}
public void setTitle(String title) {
this.title = title;
}
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;
}
}
package com.baosight.model;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.ManyToOne;
@Entity
public class Msg {
private String id;
private String cont;
private Topic topic;
@Id
@GeneratedValue
public String getId() {
return id;
}
public void setId(String 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;
}
}
MsgInfo
package com.baosight.model;
public class MsgInfo {
private String id;
private String cont;
private String topicName;
private String categoryName;
public MsgInfo(String id, String cont, String topicName, String categoryName) {
super();
this.id = id;
this.cont = cont;
this.topicName = topicName;
this.categoryName = categoryName;
}
public String getId() {
return id;
}
public void setId(String 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;
}
}
<?xml version='1.0' encoding='utf-8'?>
<!DOCTYPE hibernate-configuration PUBLIC
"-//Hibernate/Hibernate Configuration DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">
<hibernate-configuration>
<session-factory>
<!-- Database connection settings -->
<property name="connection.driver_class">oracle.jdbc.driver.OracleDriver</property>
<property name="connection.url">jdbc:oracle:thin:@127.0.0.1:1521:orcl</property>
<property name="connection.username">scott</property>
<property name="connection.password">tiger</property>
<!-- JDBC connection pool (use the built-in) -->
<!-- <property name="connection.pool_size">1</property> -->
<!-- SQL dialect -->
<property name="dialect">org.hibernate.dialect.Oracle9iDialect</property>
<!-- Enable Hibernate's automatic session context management -->
<property name="current_session_context_class">thread</property>
<!-- Disable the second-level cache -->
<property name="cache.provider_class">org.hibernate.cache.NoCacheProvider</property>
<!-- Echo all executed SQL to stdout -->
<property name="show_sql">true</property>
<property name="format_sql">true</property>
<!-- Drop and re-create the database schema on startup -->
<!-- <property name="hbm2ddl.auto">update</property> -->
<mapping class="com.baosight.model.Category"/>
<mapping class="com.baosight.model.Topic"/>
<mapping class="com.baosight.model.Msg"/>
</session-factory>
</hibernate-configuration>JUnit测试类
package com.baosight.model;
import java.util.Date;
import java.util.List;
import java.util.Map;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.cfg.AnnotationConfiguration;
import org.hibernate.tool.hbm2ddl.SchemaExport;
import org.junit.AfterClass;
import org.junit.BeforeClass;
import org.junit.Test;
public class OrMappingTest {
private static SessionFactory sessionFactory;
@BeforeClass
public static void beforeClass() {
//new SchemaExport(new AnnotationConfiguration().configure()).create(false, true);
sessionFactory = new AnnotationConfiguration().configure().buildSessionFactory();
}
@AfterClass
public static void afterClass() {
sessionFactory.close();
}
@Test
public void testSave() {
//插入数据
Session session = sessionFactory.getCurrentSession();
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.setCreateDate(new Date());
t.setCategory(c);
session.save(t);
}
for(int i=0;i<10;i++){
Topic t = new Topic();
t.setId("11");
Msg m = new Msg();
m.setCont("m"+i);
m.setTopic(t);
session.save(m);
}
session.getTransaction().commit();
}
@Test
public void testHQL01() {
Session session = sessionFactory.getCurrentSession();
session.beginTransaction();
//查询
List<Category> list = (List<Category>)session.createQuery("from Category").list();
for(Category c : list) {
System.out.println(c.getName());
}
session.getTransaction().commit();
}
@Test
public void testHQL02() {
Session session = sessionFactory.getCurrentSession();
session.beginTransaction();
//条件查询
List<Category> list = (List<Category>)session.createQuery("from Category c where c.name>'c5'").list();
for(Category c : list) {
System.out.println(c.getName());
}
session.getTransaction().commit();
}
@Test
public void testHQL03() {
//排序等
String sql = "";
// sql = "from Category c order by c.name desc";
sql = "select distinct c from Category c order by c.name desc";
Session session = sessionFactory.getCurrentSession();
session.beginTransaction();
List<Category> list = (List<Category>)session.createQuery(sql).list();
for(Category c : list) {
System.out.println(c.getName());
}
session.getTransaction().commit();
}
@Test
public void testHQL04() {
//传参查询
String sql = "";
sql = "from Category c where c.id>:min and c.id<:max order by c.name desc";
Session session = sessionFactory.getCurrentSession();
session.beginTransaction();
Query q = session.createQuery(sql);
q.setParameter("min", "2");
q.setParameter("max", "8");
List<Category> list = (List<Category>)q.list();
for(Category c : list) {
System.out.println(c.getId()+"--"+c.getName());
}
session.getTransaction().commit();
}
@Test
public void testHQL05() {
//分页查询
String sql = "";
sql = "from Category c order by c.name desc";
Session session = sessionFactory.getCurrentSession();
session.beginTransaction();
Query q = session.createQuery(sql);
q.setMaxResults(4);//每页条数
q.setFirstResult(2);//开始行号
List<Category> list = (List<Category>)q.list();
for(Category c : list) {
System.out.println(c.getId()+"--"+c.getName());
}
session.getTransaction().commit();
}
@Test
public void testHQL06() {
//查询字段
String sql = "";
sql = "select c.id,c.name from Category c order by c.name desc";
Session session = sessionFactory.getCurrentSession();
session.beginTransaction();
Query q = session.createQuery(sql);
List<Object[]> list = (List<Object[]>)q.list();
for(Object[] c : list) {
System.out.println(c[0]+"--"+c[1]);
}
session.getTransaction().commit();
}
@Test
public void testHQL07() {
//关联查询
String sql = "";
sql = "from Topic t where t.category.id=1";
Session session = sessionFactory.getCurrentSession();
session.beginTransaction();
Query q = session.createQuery(sql);
List<Topic> list = (List<Topic>)q.list();
for(Topic c : list) {
System.out.println(c.getId()+"--"+c.getTitle());
// System.out.println(c.getCategory().getId()+"--"+c.getCategory().getName());
}
session.getTransaction().commit();
}
@Test
public void testHQL08() {
//多级关联查询
String sql = "";
sql = "from Msg m where m.topic.category.id=1";
Session session = sessionFactory.getCurrentSession();
session.beginTransaction();
Query q = session.createQuery(sql);
List<Msg> list = (List<Msg>)q.list();
for(Msg c : list) {
System.out.println(c.getId()+"--"+c.getCont());
// System.out.println(c.getTopic().getTitle()+"--"+c.getTopic().getCategory().getName());
}
session.getTransaction().commit();
}
@Test
public void testHQL09() {
//通过辅助类查询
String sql = "";
sql = "select new com.baosight.model.MsgInfo(m.id,m.cont,m.topic.title,m.topic.category.name) from Msg m";
Session session = sessionFactory.getCurrentSession();
session.beginTransaction();
Query q = session.createQuery(sql);
List list = q.list();
for(Object c : list) {
MsgInfo m = (MsgInfo)c;
System.out.println(m.getId()+"--"+m.getCont());
System.out.println(m.getTopicName()+"--"+m.getCategoryName());
}
session.getTransaction().commit();
}
@Test
public void testHQL10() {
//连接查询个别字段
String sql = "";
sql = "select m.cont,t.title from Msg m join m.topic t";
Session session = sessionFactory.getCurrentSession();
session.beginTransaction();
Query q = session.createQuery(sql);
List<Object[]> list = (List<Object[]>)q.list();
for(Object[] c : list) {
System.out.println(c[0]+"--"+c[1]);
}
session.getTransaction().commit();
}
@Test
public void testHQL11() {
//使用uniqueResult
String sql = "";
sql = "from Msg m where m=:msgToSearch";
Session session = sessionFactory.getCurrentSession();
session.beginTransaction();
Query q = session.createQuery(sql);
Msg m = new Msg();
m.setId("21");
q.setParameter("msgToSearch", m);
Msg msg = (Msg) q.uniqueResult();
System.out.println(msg.getId()+"--"+msg.getCont());
session.getTransaction().commit();
}
@Test
public void testHQL12() {
//使用uniqueResult
String sql = "";
sql = "select count(*) from Msg m";
Session session = sessionFactory.getCurrentSession();
session.beginTransaction();
Query q = session.createQuery(sql);
Long count = (Long) q.uniqueResult();
System.out.println(count);
session.getTransaction().commit();
}
@Test
public void testHQL13() {
//使用聚合函数
String sql = "";
sql = "select max(m.id),min(m.id),avg(m.id),sum(m.id) from Msg m";
Session session = sessionFactory.getCurrentSession();
session.beginTransaction();
Query q = session.createQuery(sql);
Object[] o = (Object[]) q.uniqueResult();
System.out.println(o[0]+"--"+o[1]+"--"+o[2]+"--"+o[3]);
session.getTransaction().commit();
}
@Test
public void testHQL14() {
//between and
//in
String sql = "";
// sql = "from Msg m where m.id between 21 and 24";
// sql = "from Msg m where m.id in(22,23,24)";
sql = "from Msg m where m.topic is not null";
Session session = sessionFactory.getCurrentSession();
session.beginTransaction();
Query q = session.createQuery(sql);
List<Msg> list = (List<Msg>)q.list();
for(Msg c : list) {
System.out.println(c.getId()+"--"+c.getCont());
// System.out.println(c.getTopic().getTitle()+"--"+c.getTopic().getCategory().getName());
}
session.getTransaction().commit();
}
@Test
public void testHQL15() {
//is empty
//like
String sql = "";
// sql = "from Topic t where t.msgs is empty";
// sql = "from Topic t where t.title like '%5'";
sql = "from Topic t where t.title like '_5'";
Session session = sessionFactory.getCurrentSession();
session.beginTransaction();
Query q = session.createQuery(sql);
List<Topic> list = (List<Topic>)q.list();
for(Topic c : list) {
System.out.println(c.getId()+"--"+c.getTitle());
// System.out.println(c.getTopic().getTitle()+"--"+c.getTopic().getCategory().getName());
}
session.getTransaction().commit();
}
@Test
public void testHQL16() {
//字段函数
String sql = "";
sql = "select lower(m.cont),upper(m.cont),trim(m.cont),concat(m.cont,'***'),length(m.cont) from Msg m";
Session session = sessionFactory.getCurrentSession();
session.beginTransaction();
Query q = session.createQuery(sql);
List<Object[]> list = (List<Object[]>)q.list();
for(Object[] c : list) {
System.out.println(c[0]+"--"+c[1]+"--"+c[2]+"--"+c[3]+"--"+c[4]);
}
session.getTransaction().commit();
}
@Test
public void testHQL17() {
//运算函数
String sql = "";
sql = "select abs(m.id),sqrt(m.id),mod(m.id,2) from Msg m";
Session session = sessionFactory.getCurrentSession();
session.beginTransaction();
Query q = session.createQuery(sql);
List<Object[]> list = (List<Object[]>)q.list();
for(Object[] c : list) {
System.out.println(c[0]+"--"+c[1]+"--"+c[2]);
}
session.getTransaction().commit();
}
@Test
public void testHQL18() {
//日期函数
String sql = "";
sql = "select current_date,sysdate,current_timestamp,m.id from Msg m";
Session session = sessionFactory.getCurrentSession();
session.beginTransaction();
Query q = session.createQuery(sql);
List<Object[]> list = (List<Object[]>)q.list();
for(Object[] c : list) {
System.out.println(c[0]+"--"+c[1]+"--"+c[2]+"--"+c[3]);
}
session.getTransaction().commit();
}
@Test
public void testHQL19() {
//日期参数
String sql = "";
sql = "from Topic t where t.createDate<:date";
Session session = sessionFactory.getCurrentSession();
session.beginTransaction();
Query q = session.createQuery(sql);
q.setParameter("date", new Date());
List<Topic> list = (List<Topic>)q.list();
for(Topic c : list) {
System.out.println(c.getId()+"--"+c.getTitle());
// System.out.println(c.getTopic().getTitle()+"--"+c.getTopic().getCategory().getName());
}
session.getTransaction().commit();
}
@Test
public void testHQL20() {
//分组查询
String sql = "";
// sql = "select t.title,count(*) from Topic t group by t.title";
sql = "select t.title,count(*) from Topic t group by t.title having count(*)>1";
Session session = sessionFactory.getCurrentSession();
session.beginTransaction();
Query q = session.createQuery(sql);
List<Object[]> list = (List<Object[]>)q.list();
for(Object[] c : list) {
System.out.println(c[0]+"--"+c[1]);
}
session.getTransaction().commit();
}
@Test
public void testHQL21() {
//子查询
//exists的使用
String sql = "";
// sql = "from Topic t where t.id<(select avg(t1.id) from Topic t1)";
// sql = "from Topic t where t.id< all(select t1.id from Topic t1 where mod(t1.id,2)=0)";
sql = "from Topic t where not exists(select m.id from Msg m where m.topic.id=t.id)";
// sql = "from Topic t where exists(select m.id from Msg m where m.topic.id=t.id)";
Session session = sessionFactory.getCurrentSession();
session.beginTransaction();
Query q = session.createQuery(sql);
List<Topic> list = (List<Topic>)q.list();
for(Topic c : list) {
System.out.println(c.getId()+"--"+c.getTitle());
// System.out.println(c.getTopic().getTitle()+"--"+c.getTopic().getCategory().getName());
}
session.getTransaction().commit();
}
@Test
public void testHQL22() {
//update
String sql = "";
sql = "update Topic t set t.title=upper(t.title)";
Session session = sessionFactory.getCurrentSession();
session.beginTransaction();
Query q = session.createQuery(sql);
q.executeUpdate();
q = session.createQuery("from Topic");
List<Topic> list = (List<Topic>)q.list();
for(Topic c : list) {
System.out.println(c.getId()+"--"+c.getTitle());
// System.out.println(c.getTopic().getTitle()+"--"+c.getTopic().getCategory().getName());
}
session.createQuery("update Topic t set t.title=lower(t.title)").executeUpdate();
session.getTransaction().commit();
}
@Test
public void testHQL23() {
//NamedQuery
Session session = sessionFactory.getCurrentSession();
session.beginTransaction();
Query q = session.getNamedQuery("topic.selectCertainTopic");
q.setParameter("id", "15");
Topic t = (Topic) q.uniqueResult();
System.out.println(t.getId()+"--"+t.getTitle());
session.getTransaction().commit();
}
@Test
public void testHQL24() {
//update
Session session = sessionFactory.getCurrentSession();
session.beginTransaction();
SQLQuery q = session.createSQLQuery("select * from category").addEntity(Category.class);
List<Category> list = (List<Category>)q.list();
for(Category c : list) {
System.out.println(c.getId()+"--"+c.getName());
// System.out.println(c.getTopic().getTitle()+"--"+c.getTopic().getCategory().getName());
}
session.getTransaction().commit();
}
@Test
public void testSchemaExport() {
//建表
new SchemaExport(new AnnotationConfiguration().configure()).create(false, true);
}
public static void main(String[] args) {
beforeClass();
}
}
4.1注意Topic中的private List<Msg> msgs;是后来测试is empty时添加的，刚开始并没有
4.2执行测试类的testSave插入测试数据
5.首先进行简单查询
5.1执行testHQL01进行单表查询
通过List<Category> list = (List<Category>)session.createQuery("from Category").list();
查询出结果集并进行遍历
5.2执行testHQL02进行单表条件查询
List<Category> list = (List<Category>)session.createQuery("from Category c where c.name>'c5'").list();
5.3执行testHQL03进行单表查询排序和使用distinct
sql = "from Category c order by c.name desc";
sql = "select distinct c from Category c order by c.name desc";
5.4执行testHQL04进行单表查询传入查询参数
使用:xx定义传入参数，使用q.setParameter("xx", Obj);传入参数
sql = "from Category c where c.id>:min and c.id<:max order by c.name desc";
q.setParameter("min", "2");
q.setParameter("max", "8");
5.5执行testHQL05进行单表分页查询
使用q.setMaxResults(xx);设置每页条数；使用q.setFirstResult(xx);设置开始行号
sql = "from Category c order by c.name desc";
q.setMaxResults(4);//每页条数
q.setFirstResult(2);//开始行号
5.6执行testHQL06进行单表字段查询
注意此时查询的结果是Object[]组成的list
sql = "select c.id,c.name from Category c order by c.name desc";
List<Object[]> list = (List<Object[]>)q.list();
6.进行关联查询
6.1多对一关联查询，使用testHQL07
查询语句为sql = "from Topic t where t.category.id=1";这体现了hibernate对象查询的本质，直接通过对象进行关联查询
6.2使用testHQL08进行多级关联
sql = "from Msg m where m.topic.category.id=1";
6.3使用辅助类进行查询，此查询和ibatis里面的查询辅助类相似
sql = "select new com.baosight.model.MsgInfo(m.id,m.cont,m.topic.title,m.topic.category.name) from Msg m";
运行testHQL09
6.4进行连接查询，运行testHQL10
sql = "select m.cont,t.title from Msg m join m.topic t";
注意使用m.topic就指明了二者之间的连接字段
6.5使用uniqueResult，运行testHQL11
查询结果只有1条记录时可以使用类似于Msg msg = (Msg) q.uniqueResult();进行查询
一般适用于通过id进行查询，或者是使用聚合函数查询出单条记录等
6.6使用count查询统计，运行testHQL12
sql = "select count(*) from Msg m";
Long count = (Long) q.uniqueResult();
查询结果是Long
6.7使用聚合函数查询统计，运行testHQL13
sql = "select max(m.id),min(m.id),avg(m.id),sum(m.id) from Msg m";
7.条件查询
7.1使用between and/in/is not null查询，运行testHQL14
sql = "from Msg m where m.id between 21 and 24";
sql = "from Msg m where m.id in(22,23,24)";
sql = "from Msg m where m.topic is not null";
7.2使用is empty/like查询，运行testHQL15
上面6.7中is null是在多的一方查询1，如果想要在1的一方查询多，需要在1中配置@OneToMany(mappedBy="topic")
public List<Msg> getMsgs() {
查询时可以使用sql = "from Topic t where t.msgs is empty";
sql = "from Topic t where t.title like '%5'";
sql = "from Topic t where t.title like '_5'";
8.使用常用函数进行查询
8.1使用字符串函数查询，运行testHQL16
sql = "select lower(m.cont),upper(m.cont),trim(m.cont),concat(m.cont,'***'),length(m.cont) from Msg m";
8.2使用数学运算函数查询，运行testHQL17
sql = "select abs(m.id),sqrt(m.id),mod(m.id,2) from Msg m";
8.3使用日期函数查询，运行testHQL18
sql = "select current_date,sysdate,current_timestamp,m.id from Msg m";
8.4使用日期函数传参查询，运行testHQL19
sql = "from Topic t where t.createDate<:date";q.setParameter("date", new Date());
9.复杂查询
9.1使用group by和having进行分组查询，运行testHQL20
sql = "select t.title,count(*) from Topic t group by t.title";
sql = "select t.title,count(*) from Topic t group by t.title having count(*)>1";
9.2使用子查询和exists，运行testHQL21
sql = "from Topic t where t.id<(select avg(t1.id) from Topic t1)";
sql = "from Topic t where t.id< all(select t1.id from Topic t1 where mod(t1.id,2)=0)";
sql = "from Topic t where not exists(select m.id from Msg m where m.topic.id=t.id)";
sql = "from Topic t where exists(select m.id from Msg m where m.topic.id=t.id)";
9.3使用executeUpdate进行修改和删除操作等，运行testHQL22
sql = "update Topic t set t.title=upper(t.title)";
q.executeUpdate();
10.其它查询形式
10.1使用NamedQuery进行查询，运行testHQL23
需要先使用annotation进行声明
@NamedQueries({ @NamedQuery(name = "topic.selectCertainTopic", query = "from Topic t where t.id=:id") })
Query q = session.getNamedQuery("topic.selectCertainTopic");
10.2使用NativeSQL进行查询，运行testHQL24
注意，此时使用的不是hibernate的对象查询，二是使用sql进行查询，类似于JDBC
SQLQuery q = session.createSQLQuery("select * from category").addEntity(Category.class);
以上主要探讨了hibernate使用Query进行查询的情况，需要在具体的使用中仔细体会。
扫一扫
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
