Hibernate的hql查询

1. 什么是HQL?

              HQL是Hibernate Query Language即Hibernate查询语言

           [select/update/delete……] from Entity [where……] [group by……] [having……] [order by……]

2. 为什么使用HQL

       不需要再编写繁复的SQL 语句，针对实体类及其属性进行查询

    查询结果是直接存放在List 中的对象，不需要再次封装

     独立于数据库(针对不同数据库进行查询，跨数据库,Mysql->Oracle)，

     对不同的数据库根据Hibernate dialect 属性的配置自动生成不同的SQL 语句执行

3. 怎么使用HQL语句

(1) 获取Session对象

(2) 编写HQL语句；

(3) 创建Query对象；

(4) 执行查询，得到查询结果。

列子：  查询全部用户信息

Hiberbate工具类：

HibernateUtil.java

package com.gec.cn.dao.utils;

import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.cfg.Configuration;

public class HibernateUtil {
	
	public static SessionFactory sf = null;
	public static ThreadLocal<Session> threadLocal = new ThreadLocal<Session>();
	
	static {
		Configuration configuration = new Configuration().configure("hibernate.cfg.xml");
		sf = configuration.buildSessionFactory();
	}
	
	public static SessionFactory getSessionFactory() {
		return sf;
	}
	
	public static Session getSession() {
		Session session = threadLocal.get();
		if(session == null) {
			if(sf != null) {
				session = sf.openSession();
				threadLocal.set(session);
			}
		}
		return session;
	}
}

实体类：

User.java

package com.gec.cn.dao.domain;

import java.io.Serializable;

public class User implements Serializable{
	
	private Long id;
	private String name;
	private String sex;
	private Double salary;
	
	public User() {
		super();
	}
	
	public User(Long id, String name, String sex, Double salary) {
		super();
		this.id = id;
		this.name = name;
		this.sex = sex;
		this.salary = salary;
	}
	
	public Long getId() {
		return id;
	}
	public void setId(Long id) {
		this.id = id;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public String getSex() {
		return sex;
	}
	public void setSex(String sex) {
		this.sex = sex;
	}
	public Double getSalary() {
		return salary;
	}
	public void setSalary(Double salary) {
		this.salary = salary;
	}
	
	@Override
	public String toString() {
		return "User [id=" + id + ", name=" + name + ", sex=" + sex + ", salary=" + salary + "]";
	}
	
}

User映射配置文件：

User.hbm.xml

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping
        SYSTEM
        "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd" >

<hibernate-mapping >

    <class name="com.gec.cn.dao.domain.User" table="tb_user">
        <!-- 主键配置 -->
        <id name="id" column="id" type="long">
        	<!-- 主键生成策略：increment表示自增 -->
            <generator class="increment"/>
        </id>
		<!-- 普通属性映射配置 -->
        <property name="name" column="name" type="string"/>
        <property name="sex" column="sex" type="java.lang.String"/>
        <property name="salary" column="salary" type="java.lang.Double"/>

    </class>

</hibernate-mapping>

Hibernate主配置文件：

hibernate.cfg.xml

<?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>

        <!-- 数据库的链接信息 -->
        <property name="connection.driver_class">com.mysql.jdbc.Driver</property>
        <property name="connection.url">jdbc:mysql://localhost:3306/test</property>
        <property name="connection.username">root</property>
        <property name="connection.password">111111</property>

        <!-- 数据库方言：用于告诉hibernate根据所连接的数据库自动生成相对应数据库的sql语句 -->
        <property name="dialect">org.hibernate.dialect.MySQL5Dialect</property>

        <!-- 让hibernate打印sql语句到控制台  -->
        <property name="show_sql">true</property>
        <property name="format_sql">true</property>

        <!-- 让hibernate帮我们自动根据映射文件创建表：正向工程 -->
        <property name="hbm2ddl.auto">update</property>

		<!-- 注册映射文件 Mapping -->
        <mapping resource="com/gec/cn/dao/domain/User.hbm.xml"/>
        <mapping resource="com/gec/cn/dao/domain/Student.hbm.xml"/>

    </session-factory>

</hibernate-configuration>

测试类：
TestHQL.java

package com.gec.cn.test;

import java.util.List;

import org.hibernate.Query;
import org.hibernate.Session;

import com.gec.cn.dao.domain.User;
import com.gec.cn.dao.utils.HibernateUtil;

public class TestHQL {
	public static void main(String[] args) {
		Session session = null;
		try {
			session = HibernateUtil.getSession();
			
			String hql = "select u from User u";
			Query query = session.createQuery(hql);
			List<User> userlist = query.list();
			
			for(User user : userlist) {
				System.out.println("查询结果：" + user);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			session.close();
		}
		
	}
}

4. hql绑定参数方式：

(1) 字符串拼接方式(不推荐使用)

   SELECTu FROM User u WHERE u.loginName = '"+user.getLoginName()+"' ANDu.pass = '"+user.getPass()+"'"

   会产生sql漏洞注入的安全隐患，可读性底

(2) 用占位符方式和命名参数方式可以解决

//命参数方式
      public static User login(User user) {
         User resultUser = null;
        
         Session session = HibernateUtil.getSession();
         String loginHql = "select u from User u where (u.loginName =:loginName and u.pass = :pass) "
                + "or (u.email = :email and u.pass = :pass)";
        
         Query query = session.createQuery(loginHql);
         query.setString("pass", user.getPass());
         query.setString("loginName", user.getLoginName());
         query.setString("email", user.getEmail());
         List<User> users = query.list();
         if(!users.isEmpty()) {
            resultUser = users.get(0);
         }
        
         return resultUser;
      }

5. 模拟百度模糊查询。

关键字：like '%xxx%'

@Override
   public List<User>getUsersByConditions(ConditionDTO conditionDTO) {
      List<User> conditionUsers = null;
      Session session = null;
      try {
         session = HibernateUtil.getSession();
         StringBuilder hqlConditions = new StringBuilder("select u from User u where 1=1 ");
        
         if(conditionDTO.getJob() != null) {
            hqlConditions.append(" and job = :job ");
         }
         if(conditionDTO.getSalary() != null) {
            hqlConditions.append(" and salary > :salary ");
         }
         if(conditionDTO.getHireDayStart() != null) {
            hqlConditions.append(" and hireDay > :hireDayStart ");
         }
         if(conditionDTO.getHireDayEnd() != null) {
            hqlConditions.append(" and hireDay < :hireDayEnd ");
         }
 
         Query query = session.createQuery(hqlConditions.toString());
         query.setProperties(conditionDTO);
         conditionUsers = query.list();
      } catch (Exception e) {
         e.printStackTrace();
      } finally {
         session.close();
      }
      return conditionUsers;
   }
 

6. 投影查询

 

 

7. 分页查询

@Override
   public Long getCount() { //获取总记录数
      Long rows = -1L;
      Session session = HibernateUtil.getSession();
      String countHql = "selectcount(*) from User";
      Query query = session.createQuery(countHql);
      rows = (Long)query.uniqueResult();
      session.close();
      return rows;
   }
 
   @Override
   public List<User> getOnePageInfo(intcurrPage, intpageSize) { //获取每页的对象集合
      List<User> userList = null;
      Session session = HibernateUtil.getSession();
      String pageHql = "fromUser";
      Query query = session.createQuery(pageHql);
      query.setFirstResult((currPage-1)*pageSize);
      query.setMaxResults(pageSize);
      userList = query.list();
      session.close();
      return userList;
   }