这篇随笔将会记录hql的常用的查询语句,为日后查看提供便利。
在这里通过定义了两个类,Type
Type来做测试, Type与Type是一对多的关系,这里仅仅贴出这两个bean的属性代码:
Type类:
private static final long serialVersionUID = 1L;
private int id;
private String name;
private Map<String,Product> products = new HashMap<>();
...
Proudct类:
private static final long serialVersionUID = 1L;
private int id;
private String name;
private String path;
private int tid;
private String descri;
private double price;
private Type type = new Type();
映射文件
Type.hbm.xml:
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://www.hibernate.org/dtd/hibernate-mapping-3.0.dtd">
<!-- Generated 2016-6-3 17:04:14 by Hibernate Tools 3.5.0.Final -->
<hibernate-mapping>
<class name="com.lgh.hibernate.entity.Type" table="T_TYPE" lazy="true">
<cache usage="read-write"/>
<id name="id" type="int">
<column name="ID" />
<generator class="native" />
</id>
<property name="name" type="java.lang.String">
<column name="NAME" />
</property>
<map name="products" table="t_product" cascade="save-update,delete" fetch="select">
<key column="tid"></key>
<map-key column="NAME" type="string"></map-key>
<one-to-many class="com.lgh.hibernate.entity.Product"/>
</map>
</class>
</hibernate-mapping>
Product.hbm.xml:
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://www.hibernate.org/dtd/hibernate-mapping-3.0.dtd">
<!-- Generated 2016-6-3 17:04:14 by Hibernate Tools 3.5.0.Final -->
<hibernate-mapping>
<class name="com.lgh.hibernate.entity.Product" table="T_PRODUCT" lazy="true">
<id name="id" type="int">
<column name="ID" />
<generator class="native" />
</id>
<property name="name" type="java.lang.String">
<column name="NAME" />
</property>
<property name="path" type="java.lang.String">
<column name="PATH" />
</property>
<property name="descri" type="java.lang.String">
<column name="DESCRI" />
</property>
<property name="price" type="double">
<column name="PRICE" />
</property>
<!-- class="com.lgh.hibernate.entity.Type" fetch="join" -->
<!-- fetch 控制如何查关联对象,
join 采用外连接去拿关联对象 没有产生延迟加载行为
select 另外发送查询语句查询
-->
<many-to-one name="type" cascade="save-update" fetch="select" >
<column name="tid" />
</many-to-one>
</class>
</hibernate-mapping>
- 最简单的查询
String hql = "from Type";
List<Type> types = session.createQuery(hql).list();
这是hql最基本的查询语句了,作用就是查出所有的Type对象放到一个List当中
- 基于 ? 的参数化形式
String hql = "from Type where name like ?";
/**
* 查询中使用?,通过setParameter的方式可以防止sql注入
* jdbc的setParameter的下标从1开始,hql的下标从0开始
*/
Query query = session.createQuery(hql).setParameter(0, "%家%");
List<Type> types = query.list();
for (Type type : types) {
System.out.println(type.getId() + " " + type.getName());
}
Hibernate: select type0_.ID as ID1_6_, type0_.NAME as NAME2_6_ from T_TYPE type0_ where type0_.NAME like ?
2 家电
10 家具
在hql中同样支持基于 ? 的参数化形式查询,注意:在jdbc中,setParameter的下标是从1开始的,而hibernate的setParameter的下标是从0开始的。
- 基于 :xx 的别名的方式设置参数
String hql = "from Type where name like :paramName";
Query query = session.createQuery(hql);
query.setParameter("paramName", "%家%");
//query.setParameter(0, "%家%");
//以数字传参会引发异常
List<Type> types = query.list();
for (Type type : types) {
System.out.println(type.getId() + " " + type.getName());
}
- 如果返回的值只有一个,可以使用uniqueResult方法
String hql = "select count(*) from Type ";
Query query = session.createQuery(hql);
Long longNum = (Long) query.uniqueResult();
System.out.println(longNum);
- 基于投影的查询
/**
* 基于投影的查询,如果返回多个值,这些值都是保存在一个object[]数组当中
*/
String hql = "select p.id, p.name from Product p ";
List<Object[]> arrObjs = session.createQuery(hql).list();
我们看下面的代码
// 二级缓存缓存的仅仅是对象,如果查询出来的是对象的一些属性,则不会被加到缓存中去
@Test
public void testCache04() {
Session session = null;
try {
session = HibernateUtil.getSession();
// String hql = "select t.name ,t.id from Type t";
/**
* 此时会发出一条sql,将Type的id 和 name 查询出来,
* 缓存缓存的仅仅是对象,如果查询出来的是对象的一些属性,则不会被加到缓存中去
*/
//数组对象
//class [Ljava.lang.Object;--------
List types = session.createQuery("select t.name,t.id from Type t").list();
for(Object t : types){
System.out.println(t.getClass()+"--------");
}
} catch (Exception e) {
e.printStackTrace();
} finally {
if (session != null) {
session.close();
}
}
}
我们知道Type对象只有两个字段,我们使用
"select t.name,t.id from Type t"
或 "from Type"
都是查询所有但是他们的返回值时不同的
select 语句返回的是一个Object[] ,from语句则是一个对象
这一点要注意
- 基于导航对象的查询
/**
*
* 在使用别名和?的hql语句查询时,?形式的查询必须放在别名前面
*/
String hql = "select p from Product p where p.name like ? and p.type.id = :paramId ";
List<Product> products = session.createQuery(hql).setParameter(0, "%草%").setParameter("paramId", 2).list();
System.out.println(products.size());
- 使用 in 进行列表查询
String hql = "select p from Product p where p.name like ? and p.type.id in :paramId ";
List<Product> products = session.createQuery(hql).setParameter(0, "%%").setParameterList("paramId", new Integer[]{2,3}).list();
System.out.println(products.size());
查询语句:
Hibernate: select product0_.ID as ID1_3_, product0_.NAME as NAME2_3_, product0_.PATH as PATH3_3_, product0_.DESCRI as DESCRI4_3_, product0_.PRICE as PRICE5_3_, product0_.tid as tid6_3_ from T_PRODUCT product0_ where (product0_.NAME like ?) and (product0_.tid in (? , ?))
3
- 分页查询
String hql = "select p from Product p where p.name like ? and p.type.id in :paramId ";
List<Product> products = session.createQuery(hql)
.setParameter(0, "%%")
.setParameterList("paramId", new Integer[]{2,3})
.setFirstResult(0).setMaxResults(2)
.list();
System.out.println(products.size());
Hibernate: select product0_.ID as ID1_3_, product0_.NAME as NAME2_3_, product0_.PATH as PATH3_3_, product0_.DESCRI as DESCRI4_3_, product0_.PRICE as PRICE5_3_, product0_.tid as tid6_3_ from T_PRODUCT product0_ where (product0_.NAME like ?) and (product0_.tid in (? , ?)) limit ?
2
- 内连接查询
String hql = "select t from Type t inner join t.products";
Query query = session.createQuery(hql);
List<Type> types = query.list();
for (Type type : types) {
System.out.println(type);
}
注意: sql 与 hql连接查询的语句的语句是有区别的
SQL : select t.* from t_product p inner join t_type t on p.tid = t.id;
HQL : select t from Type t inner join t.products
受支持的连接类型从ABSI SQL 中借鉴
/**
* inner join 内联接
* left outer join 左外连接 可简写left join
* rightouter join 右外连接 可简写right join
* full join 全连接(不常用)
*/
- 在实体类配置文件中编写HQL语句
<hibernate-mapping>
</class>
...
</class>
<!-- 我们编写的HQL语句 -->
<query name="getAll">
from Type where id > ?
</query>
</hibernate-mapping>
使用:
session = HibernateUtil.getSession();
ts = session.beginTransaction();
Query query = session.getNamedQuery("getAll").setParameter(0,9);
List<Type> types = query.list();
for (Type type : types) {
System.out.println(type);
};
ts.commit();
- group by 语句
@Test
public void testHqlSelect12() {
Session session = null;
Transaction ts = null;
try {
session = HibernateUtil.getSession();
ts = session.beginTransaction();
//String hql = "select t from Type t inner join t.products group by t.name";
String hql = "select p.type.id , p.type.name ,count(p) from Product p group by p.type.id";
Query query = session.createQuery(hql);
List<Object[]> objs = query.list();
for (int i = 0; i < objs.size(); i++) {
for (int j = 0; j < objs.get(i).length; j++) {
System.out.print(objs.get(i)[j] + " ");
}
System.out.println();
}
ts.commit();
} catch (Exception e) {
if (ts != null) {
ts.rollback();
}
e.printStackTrace();
} finally {
if (session != null) {
session.close();
}
}
}
- select 语句查询的结果直接放到list中
之前我们说到 select 语句查询的结果返回时以Object[]返回的那么如何让他返回一个List呢?
@Test
public void testHqlSelect13() {
Session session = null;
Transaction ts = null;
try {
session = HibernateUtil.getSession();
ts = session.beginTransaction();
String hql = "select new list(p,t) from Product p inner join p.type as t";
Query query = session.createQuery(hql);
List<List> proTypes = query.list();
for (List list : proTypes) {
System.out.println(((Product)list.get(0)).getName() + " " + ((Type)list.get(1)).getName());
};
ts.commit();
} catch (Exception e) {
if (ts != null) {
ts.rollback();
}
e.printStackTrace();
} finally {
if (session != null) {
session.close();
}
}
}
HQL基本上就写到这了,以后遇到了在补充