hibernate 语言查询(hibernate query language)HQL;
HQL用面向对象的方式生成SQL
1. 以类和属性来代替表和数据列;
2. 支持多台;
3. 支持各种关联;
4.HQL支持多有的关系型数据库操作。
5.连接、投影、聚合、排序、子查询、SQL函数。
hql语言中关键字不区分大小写,实体类名和属性名区分大小写;
1、简单属性查询:
※ 单一属性查询,返回结果集属性列表,元素类型和实体类中相应的属性一致;
※ 多个属性查询,返回的集合元素是Object数组,每个Obect中包含了要查的属性,
我们可以将Objec转为Obejct[]类型按下标取出属性;
※ 如果认为返回数组不够对象化,可以采用hql动态实例化student对象。
示例:
/**
* 单一属性查询,返回的是 : 属性类型的集合
*/
public void testQuery1(){
Session session = HibernateUtils.getSession();
try {
session.beginTransaction();
List<String> studentList = session.createQuery("select name from User").list();
for(Iterator<String> iter = studentList.iterator();iter.hasNext();){
System.out.println(iter.next());
}
session.getTransaction().commit();
} catch (HibernateException e) {
e.printStackTrace();
session.getTransaction().rollback();
}finally{
HibernateUtils.closeSession(session);
}
}
/**
* 多个属性查询方法1,返回的是: Object类型的数组,将数组中的每个元素转换为objec[],
*从每个元素转化为的obejct[] 按下标查询取出其中的值
*/
public void testQuery2(){
Session session = HibernateUtils.getSession();
try {
session.beginTransaction();
List studentList = session.createQuery("select name, createTime from User").list();
for(Iterator iter = studentList.iterator();iter.hasNext();){
Object[] obj = (Object[])iter.next();
System.out.println(obj[0]+"的创建时间是: " + obj[1]);
}
session.getTransaction().commit();
} catch (HibernateException e) {
e.printStackTrace();
session.getTransaction().rollback();
}finally{
HibernateUtils.closeSession(session);
}
}
/**
* 多个属性查询方法2,如果认为返回数组不够对象化,可以采用hql动态实例化student对象
* 此时list中为对象属性集合
*/
public void testQuery3(){
Session session = HibernateUtils.getSession();
try {
session.beginTransaction();
List<User> studentList = session.createQuery("select new User(name, createTime) from User").list();
for(Iterator<User> iter = studentList.iterator();iter.hasNext();){
System.out.println(iter.next().getName()+"的创建时间是: " + iter.next().getCreateTime());
}
session.getTransaction().commit();
} catch (HibernateException e) {
e.printStackTrace();
session.getTransaction().rollback();
}finally{
HibernateUtils.closeSession(session);
}
}
/**
* 可以使用别用进行查询
*/
public void testQuery4(){
Session session = HibernateUtils.getSession();
try {
session.beginTransaction();
//User实体类使用了别名u代替 User u
List studentList = session.createQuery("select u.name, u.createTime from User u").list();
for(Iterator iter = studentList.iterator();iter.hasNext();){
Object[] obj = (Object[])iter.next();
System.out.println(obj[0]+"的创建时间是: " + obj[1]);
}
session.getTransaction().commit();
} catch (HibernateException e) {
e.printStackTrace();
session.getTransaction().rollback();
}finally{
HibernateUtils.closeSession(session);
}
}
/**
* 可以使用别用进行查询 ,关键字 as
*/
public void testQuery5(){
Session session = HibernateUtils.getSession();
try {
session.beginTransaction();
//User实体类使用了别名u代替 User as u 用关键字 as
List studentList = session.createQuery("select u.name, u.createTime from User as u").list();
for(Iterator iter = studentList.iterator();iter.hasNext();){
Object[] obj = (Object[])iter.next();
System.out.println(obj[0]+"的创建时间是: " + obj[1]);
}
session.getTransaction().commit();
} catch (HibernateException e) {
e.printStackTrace();
session.getTransaction().rollback();
}finally{
HibernateUtils.closeSession(session);
}
}
2、实体对象查询:
※ 单一对象查询,返回的是 对象的的集合;
※ 可以忽略select ,使用form User;
※ 可以使用别用进行查询 from Usr u;
※ 如果不省略select 查询实体对象,那么必须指定别名select u from User u;
※ 不能使用*通配(例如:select * from...),除了在函数中以外;
※ 使用query.iterate() 迭代器方法,有可能会出现 N + 1 问题(所谓N+1,指的是在查询中发出了N+1条语句),这
和session缓存(一级缓存有关),如果缓存中有id列表中的数据,那么就直接从缓存中读取;
※ 使用quert.list()方法,不管查多少次,都是直接发sql查找,list会向缓存中放入数据,但不会利用数据,不会从缓存
中找,除非配置了查询缓存;
/**
* 使用iterate()迭代器方法查询,出现N+1问题,
* 1: Hibernate: select user0_.u_id as col_0_0_ from t_user user0_
* N: Hibernate: select user0_.u_id as u1_0_0_, user0_.u_name as u2_0_0_ where user0_.u_id=?
* 该方法是根据第一条语句查到的id集合先从缓存中有没有该id对应的记录,没有就发sql查询。
* 若该方法使用不当,相当损耗性能。
*/
public void testQuery1(){
Session session = HibernateUtils.getSession();
try {
session.beginTransaction();
Iterator<User> students = session.createQuery("from User").iterate();
while(students.hasNext()){
System.out.println(students.next().getName());
}
session.getTransaction().commit();
} catch (HibernateException e) {
e.printStackTrace();
session.getTransaction().rollback();
}finally{
HibernateUtils.closeSession(session);
}
}
public void testQuery2(){
Session session = HibernateUtils.getSession();
try {
session.beginTransaction();
Query query = session.createQuery("from User");
System.out.println("--------------mark1-------------");
List sutdentsList = query.list();//一级缓存session 中这时候已经有了user的列表
Iterator<User> iter = sutdentsList.iterator();
while(iter.hasNext()){
System.out.println(iter.next().getName());
}
System.out.println("---------------mark2-------------");
//不会出现N+1问题,因为list操作已经将对象放入了session缓存中(一级缓存),
//所以再次使用iterate操作的时候,它首先发出一条查询id列表的sql,
//再根据id从缓存中取数据,只有在缓存中找不到相应的数据时,才会发出sql
//到数据库中查询
Iterator<User> students = query.iterate();
while(students.hasNext()){
System.out.println(students.next().getName());
}
session.getTransaction().commit();
} catch (HibernateException e) {
e.printStackTrace();
session.getTransaction().rollback();
}finally{
HibernateUtils.closeSession(session);
}
}
打印输出:
--------------mark1-------------
Hibernate: select user0_.u_id as u1_0_, user0_.u_name as u2_0_, user0_.createTime as createTime0_, user0_.g_id as g4_0_ from t_user user0_
班级1的学生1
班级1的学生2
.....
---------------mark2-------------
Hibernate: select user0_.u_id as col_0_0_ from t_user user0_
班级1的学生1
班级1的学生2
.....
从打印信息还可以看出,query也是存在lazy策略的。
3、条件查询:
※ 可以采用拼字符串的方法传递参数;
※ 可以采用 ? 来传递参数(索引从0开始);
※ 可以采用:参数名 来传递参数;
※ 如果传递多个参数,可以采用setparametersList方法;
※ 在hql中可以使用数据库的函数,如:date_format ,但这样会降低可移植性,不推荐用;
/**
* 字符串拼接模糊查询 like
*/
public void testQuery3(){
Session session = HibernateUtils.getSession();
try {
session.beginTransaction();
Query query = session.createQuery("select new User(u.name, u.createTime) from User as u where u.name like '%1%'");
Iterator<User> students = query.iterate();
while(students.hasNext()){
System.out.println(students.next().getName() +"的创建时间:" + students.next().getCreateTime());
}
session.getTransaction().commit();
} catch (HibernateException e) {
e.printStackTrace();
session.getTransaction().rollback();
}finally{
HibernateUtils.closeSession(session);
}
}
/**
* like 查询 ?占位符
*/
public void testQuery4(){
Session session = HibernateUtils.getSession();
try {
session.beginTransaction();
Query query = session.createQuery("select new User(u.name, u.createTime) from User as u where u.name like ?");
query.setParameter(0, "%1%");
Iterator<User> students = query.iterate();
while(students.hasNext()){
System.out.println(students.next().getName() +"的创建时间:" + students.next().getCreateTime());
}
session.getTransaction().commit();
} catch (HibernateException e) {
e.printStackTrace();
session.getTransaction().rollback();
}finally{
HibernateUtils.closeSession(session);
}
}
/**
* 知道query是基于方法链,返回自身类型
*/
public void testQuery5(){
Session session = HibernateUtils.getSession();
try {
session.beginTransaction();
//Hibernate的 hql支持基于方法链的编程,即query下的所有子方法都返回了自身,
//所以testQuery4()这部分可以写成下面这样:
Query query = session.createQuery("select new User(u.name, u.createTime) from User as u where u.name like ?")
.setParameter(0, "%1%");
Iterator<User> students = query.iterate();
while(students.hasNext()){
System.out.println(students.next().getName() +"的创建时间:" + students.next().getCreateTime());
}
session.getTransaction().commit();
} catch (HibernateException e) {
e.printStackTrace();
session.getTransaction().rollback();
}finally{
HibernateUtils.closeSession(session);
}
}
public void testQuery6(){
Session session = HibernateUtils.getSession();
try {
session.beginTransaction();
//使用:参数名称的方式传递参数值,注意 ":"和参数值之间不能有空格
List stdentsList = session.createQuery("select new User(u.id, u.name, u.createTime) from User as u where u.name like :myhql")
.setParameter("myhql","%1%")
.list();
for(Iterator<User> students = stdentsList.iterator();students.hasNext();){
System.out.println(students.next().getId() +" "+ students.next().getName() +"的创建时间:" + students.next().getCreateTime());
}
session.getTransaction().commit();
} catch (HibernateException e) {
e.printStackTrace();
session.getTransaction().rollback();
}finally{
HibernateUtils.closeSession(session);
}
}
public void testQuery7(){
Session session = HibernateUtils.getSession();
try {
session.beginTransaction();
//Hibernate的 hql支持基于方法链的编程,即query下的所有子方法都返回了自身,
//所以testQuery4()这部分可以写成下面这样:
Query query = session.createQuery("select new User(u.name, u.createTime) " +
"from User as u where u.name like :myname and u.id = :myid")
.setParameter("myname", "%1%")
.setParameter("myid", 1);
Iterator<User> students = query.iterate();
while(students.hasNext()){
User user = students.next();
System.out.println(user.getName() +"的创建时间:" + user.getCreateTime());
}
session.getTransaction().commit();
} catch (HibernateException e) {
e.printStackTrace();
session.getTransaction().rollback();
}finally{
HibernateUtils.closeSession(session);
}
}
/**
* 模糊查询, 多个占位符,用 0 ,1 ..标识顺序
*/
public void testQuery8(){
Session session = HibernateUtils.getSession();
try {
session.beginTransaction();
Query query = session.createQuery("select new User(u.name, u.createTime) " +
"from User as u where u.name like ? and u.id = ?")
.setParameter(0, "%1%")
.setParameter(1, 1);
Iterator<User> students = query.iterate();
while(students.hasNext()){
User user = students.next();
System.out.println(user.getName() +"的创建时间:" + user.getCreateTime());
}
session.getTransaction().commit();
} catch (HibernateException e) {
e.printStackTrace();
session.getTransaction().rollback();
}finally{
HibernateUtils.closeSession(session);
}
}
public void testQuery9(){
Session session = HibernateUtils.getSession();
try {
session.beginTransaction();
//支持in,需要使用setParameterList进行参数传递
List stdentsList = session.createQuery("select new User(u.id, u.name, u.createTime) " +
"from User as u where u.id in (:myids)")
.setParameterList("myids", new Object[]{1,2,3,4,5})
.list();
for(Iterator<User> students = stdentsList.iterator();students.hasNext();){
User user = students.next();
System.out.println(user.getId() +" "+ user.getName() +"的创建时间:" + user.getCreateTime());
}
session.getTransaction().commit();
} catch (HibernateException e) {
e.printStackTrace();
session.getTransaction().rollback();
}finally{
HibernateUtils.closeSession(session);
}
}
/**
* hql支持数据库函数
*/
public void testQuery10(){
Session session = HibernateUtils.getSession();
try {
session.beginTransaction();
//查询2011年3月创建的学生
List stdentsList = session.createQuery("select new User(u.id, u.name, u.createTime) " +
"from User as u where date_format(u.createTime,'%Y-%m') = ?")
.setParameter(0, "2011-03")
.list();
for(Iterator<User> students = stdentsList.iterator();students.hasNext();){
User user = students.next();
System.out.println(user.getId() +" "+ user.getName() +"的创建时间:" + user.getCreateTime());
}
session.getTransaction().commit();
} catch (HibernateException e) {
e.printStackTrace();
session.getTransaction().rollback();
}finally{
HibernateUtils.closeSession(session);
}
}
public void testQuery11(){
Session session = HibernateUtils.getSession();
try {
session.beginTransaction();
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
//查询2009-03-10到2010-05-30创建的学生
List stdentsList = session.createQuery("select new User(u.id, u.name, u.createTime) " +
"from User as u where u.createTime between ? and ?")
.setParameter(0, sdf.parse("2009-03-10 00:00:00"))
.setParameter(1, sdf.parse("2009-03-10 23:59:59"))
.list();
for(Iterator<User> students = stdentsList.iterator();students.hasNext();){
User user = students.next();
System.out.println(user.getId() +" "+ user.getName() +"的创建时间:" + user.getCreateTime());
}
session.getTransaction().commit();
} catch (Exception e) {
e.printStackTrace();
session.getTransaction().rollback();
}finally{
HibernateUtils.closeSession(session);
}
}
4、hibernate也支持直接使用sql进行查询:
public void testQuery(){
Session session = HibernateUtils.getSession();
try {
session.beginTransaction();
List studentList = session.createSQLQuery("select * from t_user").list();
for(Iterator iter = studentList.iterator();iter.hasNext();){
Object[] obj = (Object[])iter.next();
System.out.println(obj[1]+"的创建时间是: " + obj[2]);
}
session.getTransaction().commit();
} catch (HibernateException e) {
e.printStackTrace();
session.getTransaction().rollback();
}finally{
HibernateUtils.closeSession(session);
}
}
5、外置命名查询:将hql语句放在一个配置文件(放在映射文件中),所有的方法都要调用该配置文件的hql进行查询。
<?xml version="1.0"?> <!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN" "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd"> <hibernate-mapping> <class name="com.wyx.hibernate.User" table="t_user"> <id name="id" column="u_id"> <generator class="native"/> </id> <!-- 除了标识以外,都用property来映射 ,生成的字段可以重命名,unique="true"标识唯一--> <property name="name" column="u_name" not-null="true" length="30"/> <property name="createTime"/> <many-to-one name="group" column="g_id" cascade="all"/> </class> <query name="searchUsers"> <![CDATA[ SELECT u FROM User u WHERE u.id = ? ]]> </query> </hibernate-mapping>
用到了<![CDATA[ ]]>标签,这样特殊字符就不需要转义了。<query>写在任何映射文件中都能找得到。
public void testQuery1(){
Session session = HibernateUtils.getSession();
try {
session.beginTransaction();
List studentList = session.getNamedQuery("searchUsers")
.setParameter(0, 1)
.list();
for(Iterator iter = studentList.iterator();iter.hasNext();){
User user =(User)iter.next();
System.out.println(user.getName()+"的创建时间是: " + user.getCreateTime());
}
session.getTransaction().commit();
} catch (HibernateException e) {
e.printStackTrace();
session.getTransaction().rollback();
}finally{
HibernateUtils.closeSession(session);
}
}
使用 session.getNamedQuery(String qureyString)调用。
6、查询过滤器:
※ 在类的映射文件中定义过滤参数,过滤器的名字在工程中式唯一的;
※ 在类的映射中使用这些参数;
※ 在程序中启用过滤器;
<?xml version="1.0"?> <!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN" "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd"> <hibernate-mapping> <class name="com.wyx.hibernate.User" table="t_user"> <id name="id" column="u_id"> <generator class="native"/> </id> <!-- 除了标识以外,都用property来映射 ,生成的字段可以重命名,unique="true"标识唯一--> <property name="name" column="u_name" not-null="true" length="30"/> <property name="createTime"/> <many-to-one name="group" column="g_id" cascade="all"/> <!-- condition指过滤条件,特殊符号要转义,id < :myid --> <filter name="filtertest" condition="u_id < :myid"/> </class> <query name="searchUsers"> <![CDATA[ SELECT u FROM User u WHERE u.id = ? ]]> </query> <filter-def name="filtertest"> <filter-param name="myid" type="integer"/> </filter-def> </hibernate-mapping>
/**
* 测试查询过滤器
*/
public void testQuery2(){
Session session = HibernateUtils.getSession();
try {
session.beginTransaction();
//启用过滤器 , 将过滤器的参数设置好
session.enableFilter("filtertest").setParameter("myid", 10);
//查询 id < 10 的全部user
List studentList = session.createQuery("from User").list();
for(Iterator iter = studentList.iterator();iter.hasNext();){
User user =(User)iter.next();
System.out.println(user.getName()+"的创建时间是: " + user.getCreateTime());
}
session.getTransaction().commit();
} catch (HibernateException e) {
e.printStackTrace();
session.getTransaction().rollback();
}finally{
HibernateUtils.closeSession(session);
}
}
打印:
Hibernate: select user0_.u_id as u1_0_, user0_.u_name as u2_0_, user0_.createTime as createTime0_, user0_.g_id as g4_0_ from t_user user0_ where user0_.u_id < ?
这里有个疑问:hbm.xml中 condition 的值 u_id 是数据库的字段,我用实体的实行id表示会出错,理论上应该都是用hql表达的,我只要用id标识运行测试方法就会发:
Hibernate: select user0_.u_id as u1_0_, user0_.u_name as u2_0_, user0_.createTime as createTime0_, user0_.g_id as g4_0_ from t_user user0_ where user0_.id < ?
where条件明显不对,希望高手帮我解释一下,小弟不胜感激。
7、分页查询:hibernate的分页查询操作比较简单,并且有比较高的可移植性。
※ setFirstResult(),从0开始;
※ setMaxResults(),每页显示多少条数据;
public void testQuery2(){
Session session = HibernateUtils.getSession();
try {
session.beginTransaction();
List studentList = session.createQuery("from User")
.setFirstResult(0)//设置从哪个元素开始,下标从0开始
.setMaxResults(5)//设置每页显示几个元素
.list();
for(Iterator iter = studentList.iterator();iter.hasNext();){
User user =(User)iter.next();
System.out.println(user.getName()+"的创建时间是: " + user.getCreateTime());
}
session.getTransaction().commit();
} catch (HibernateException e) {
e.printStackTrace();
session.getTransaction().rollback();
}finally{
HibernateUtils.closeSession(session);
}
}
打印输出sql:
Hibernate: select user0_.u_id as u1_0_, user0_.u_name as u2_0_, user0_.createTime as createTime0_, user0_.g_id as g4_0_ from t_user user0_ limit ?
8、对象导航查询:在hql中采用 " . "进行导航。
public void testQuery1(){
Session session = HibernateUtils.getSession();
try {
session.beginTransaction();
List object = session.createQuery("select u.name from User u " +
"where u.group.name like ?")
.setParameter(0, "%1%")
.list();
for(Iterator iter = object.iterator();iter.hasNext();){
String name =(String)iter.next();
System.out.println(name);
}
session.getTransaction().commit();
} catch (HibernateException e) {
e.printStackTrace();
session.getTransaction().rollback();
}finally{
HibernateUtils.closeSession(session);
}
}
9、连接查询:
※ 内联
※ 外连(左连 / 右连)
/**
* 内连接查 */
public void testQuery(){
Session session = HibernateUtils.getSession();
try {
session.beginTransaction();
//默认就是内连接,所以关键字inner可以省略
//List studentList = session.createQuery("select u.name, g.name from User u join u.group g").list();
List studentList = session.createQuery("select u.name, g.name from User u inner join u.group g").list();
for(Iterator iter = studentList.iterator();iter.hasNext();){
Object[] obj = (Object[])iter.next();
System.out.println(obj[0]+ " " + obj[1]);
}
session.getTransaction().commit();
} catch (HibernateException e) {
e.printStackTrace();
session.getTransaction().rollback();
}finally{
HibernateUtils.closeSession(session);
}
}
左外连接:
List studentList = session.createQuery("select u.name, g.name from User u left join u.group g").list();
右外连接:
List studentList = session.createQuery("select u.name, g.name from User u right join u.group g").list();
10、统计查询:
统计总数:
public void testQueryCount(){
Session session = HibernateUtils.getSession();
try {
session.beginTransaction();
List students = session.createQuery("select count(*) from User").list();
Long count =(Long)students.get(0);
System.out.println(count);
session.getTransaction().commit();
} catch (HibernateException e) {
e.printStackTrace();
session.getTransaction().rollback();
}finally{
HibernateUtils.closeSession(session);
}
}
注:如果返回值是单一的,可以采用另一种更好的方式:query.uniqueResult()
public void testQueryCount1(){
Session session = HibernateUtils.getSession();
try {
session.beginTransaction();
Long count = (Long)session.createQuery("select count(*) from User").uniqueResult();
System.out.println(count);
session.getTransaction().commit();
} catch (HibernateException e) {
e.printStackTrace();
session.getTransaction().rollback();
}finally{
HibernateUtils.closeSession(session);
}
}
public void testQuery1(){
Session session = HibernateUtils.getSession();
try {
session.beginTransaction();
List students = session.createQuery("select g.name, count(u) from Group g join g.users u group by g.name order by g.id").list();
for(Iterator iter = students.iterator();iter.hasNext();){
Object[] obj =(Object[])iter.next();
System.out.println(obj[0] +" ," +obj[1]);
}
session.getTransaction().commit();
} catch (HibernateException e) {
e.printStackTrace();
session.getTransaction().rollback();
}finally{
HibernateUtils.closeSession(session);
}
}
11、DML风格的操作(尽量少用,因为和缓存不同步,会直接更新数据库,缓存中的数据不做处理,可能会产生脏数据)
/**
* DML批量更新演示
*
*/
public void testQuery2(){
Session session = HibernateUtils.getSession();
try {
session.beginTransaction();
session.createQuery("update User u set u.name =? where u.id <= ?")
.setParameter(0, "张三")
.setParameter(1, 10)
.executeUpdate();
session.getTransaction().commit();
} catch (HibernateException e) {
e.printStackTrace();
session.getTransaction().rollback();
}finally{
HibernateUtils.closeSession(session);
}
}