hibernate的动态查询

 在查询条件很多的情况下，传递过多的参数很不方便。可以将参数封装在对象中，再使用query接口的setPorperties()方法为HQL中的命名参数赋值。

setProperties()方法把对象的属性匹配到命名参数上，需注意命名参数名称要与Java对象的属性匹配。

hibernateHql.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">oracle.jdbc.driver.OracleDriver</property>
        <!-- 指定数据库链接的url，hibernate链接的数据库名 -->
        <property name="connection.url">jdbc:oracle:thin:@localhost:1521:orcl</property>
        <!-- 指定连接数据库的用户名 -->
        <property name="connection.username">scott</property>
        <!-- 指定连接数据库的用户口令 -->
        <property name="connection.password">123156</property>
        <!-- Enable Hibernate's automatic session context management -->
        <property name="current_session_context_class">thread</property>

        <!--格式化sql -->
        <property name="format_sql ">true</property>
        <!-- 打印sql 控制台-->
        <property name="show_sql">true</property>
        <!-- 指定数据库方言 -->
        <property name="dialect">org.hibernate.dialect.Oracle10gDialect</property>
        <!-- 根据需要自动创建数据库表 -->
        <property name="hbm2ddl.auto">update</property>
        <!--关联小配置文件-->
        <mapping resource="cn/day03hql/dynamic/entity/Emp.hbm.xml"></mapping>
    </session-factory>
</hibernate-configuration>


实体类

public class Emp {
    private Integer empno;
    private String ename;
    private String job;
    private Integer mgr;
    private Date hiredate;
    private Integer sal;
    private Integer comm;
    private Integer  deptno;

    public Integer getEmpno() {
        return empno;
    }
    public void setEmpno(Integer empno) {
        this.empno = empno;
    }

    public String getEname() {return ename;}
    public void setEname(String ename) {
        this.ename = ename;
    }

    public String getJob() {
        return job;
    }
    public void setJob(String job) {
        this.job = job;
    }

    public Integer getMgr() {
        return mgr;
    }
    public void setMgr(Integer mgr) {
        this.mgr = mgr;
    }

    public Date getHiredate() {
        return hiredate;
    }
    public void setHiredate(Date hiredate) {
        this.hiredate = hiredate;
    }

    public Integer getSal() {
        return sal;
    }
    public void setSal(Integer sal) {
        this.sal = sal;
    }

    public Integer getComm() {
        return comm;
    }
    public void setComm(Integer comm) {
        this.comm = comm;
    }

    public Integer getDeptno() {
        return deptno;
    }
    public void setDeptno(Integer deptno) {
        this.deptno = deptno;
    }
}

多条件查询的辅助类

public class EmpCodition {
    private String job;
    private Integer sal;
    private Date fromhiredate;
    private Date endhiredate;

    public String getJob() {
        return job;
    }
    public void setJob(String job) {
        this.job = job;
    }

    public Integer getSal() {
        return sal;
    }
    public void setSal(Integer sal) {
        this.sal = sal;
    }

    public Date getFromhiredate() {
        return fromhiredate;
    }
    public void setFromhiredate(Date fromhiredate) {
        this.fromhiredate = fromhiredate;
    }

    public Date getEndhiredate() {
        return endhiredate;
    }
    public void setEndhiredate(Date endhiredate) {
        this.endhiredate = endhiredate;
    }
}

小配置文件

<?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">
<!-- 映射文件开始 -->
<hibernate-mapping package="cn.day03hql.dynamic.entity">
    <!--表名称-->
    <class name="Emp" table="EMP" schema="scott">
        <!--列名-->
       <id name="empno" column="EMPNO">
           <!--主键生成的策略  native：自动生成主键字段-->
           <generator class="native"></generator>
       </id>
        <property name="ename" column="ENAME"></property>
        <property name="job" column="JOB"></property>
        <property name="mgr" column="MGR"></property>
        <property name="hiredate" column="HIREDATE"></property>
        <property name="sal" column="SAL"></property>
        <property name="comm" column="COMM"></property>
        <property name="deptno" column="DEPTNO"></property>
    </class>
</hibernate-mapping>


测试类

//动态查询
@Test
public void test01() throws ParseException {
    //读取大配置文件
    Configuration cfg=new Configuration().configure("hibernateHql.cfg.xml");
    //session工厂
    SessionFactory factory=cfg.buildSessionFactory();
    //session对象
    Session session = factory.openSession();
    String hql="from Emp where 1=1";
    EmpCodition empCodition=new EmpCodition();
    empCodition.setJob("CLERK");
    empCodition.setSal(1000);
    SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd");
    //入职时间
    Date parse = sdf.parse("1981-04-01");
    empCodition.setFromhiredate(parse);
    //离职时间
    Date endparse = sdf.parse("1985-09-09");
    empCodition.setEndhiredate(endparse);

    StringBuffer  buffer=new StringBuffer(hql);
    if (empCodition.getJob()!=null){
        buffer.append(" and job=:job");
    }
    if (empCodition.getSal()!=null){
        buffer.append(" and sal>:sal");
    }
    if (empCodition.getFromhiredate()!=null){
        buffer.append(" and hiredate>=:fromhiredate");
    }
    if (empCodition.getEndhiredate()!=null){
        buffer.append(" and hiredate<=:endhiredate");
    }
    Query query = session.createQuery(buffer.toString());
    query.setProperties(empCodition);
    List<Emp> list = query.list();
    for (Emp item:list){
        System.out.println(item.getEname());
    }
    session.close();
}