Hibernate(十)HQL查询二

一、数据库的emp名和dept表

建立持久化类和配置文件，可以用MyEclipse直接生成

持久化类

package entity;

import java.util.Date;

public class Emp implements java.io.Serializable {

    // Fields

    private Short empno;
    private Dept dept;
    private String ename;
    private String job;
    private Short mgr;
    private Date hiredate;
    private Double sal;
    private Double comm;

    // Constructors

    /** default constructor */
    public Emp() {
    }

    /** minimal constructor */
    public Emp(Short empno) {
        this.empno = empno;
    }

    /** full constructor */
    public Emp(Short empno, Dept dept, String ename, String job, Short mgr,
            Date hiredate, Double sal, Double comm) {
        this.empno = empno;
        this.dept = dept;
        this.ename = ename;
        this.job = job;
        this.mgr = mgr;
        this.hiredate = hiredate;
        this.sal = sal;
        this.comm = comm;
    }

    // Property accessors

    public Short getEmpno() {
        return this.empno;
    }

    public void setEmpno(Short empno) {
        this.empno = empno;
    }

    public Dept getDept() {
        return this.dept;
    }

    public void setDept(Dept dept) {
        this.dept = dept;
    }

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

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

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

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

    public Short getMgr() {
        return this.mgr;
    }

    public void setMgr(Short mgr) {
        this.mgr = mgr;
    }

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

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

    public Double getSal() {
        return this.sal;
    }

    public void setSal(Double sal) {
        this.sal = sal;
    }

    public Double getComm() {
        return this.comm;
    }

    public void setComm(Double comm) {
        this.comm = comm;
    }

}

package entity;

import java.util.HashSet;
import java.util.Set;



public class Dept implements java.io.Serializable {

    // Fields

    private Byte deptno;
    private String dname;
    private String loc;
    private Set emps = new HashSet(0);

    // Constructors

    /** default constructor */
    public Dept() {
    }

    /** minimal constructor */
    public Dept(Byte deptno) {
        this.deptno = deptno;
    }

    /** full constructor */
    public Dept(Byte deptno, String dname, String loc, Set emps) {
        this.deptno = deptno;
        this.dname = dname;
        this.loc = loc;
        this.emps = emps;
    }

    // Property accessors

    public Byte getDeptno() {
        return this.deptno;
    }

    public void setDeptno(Byte deptno) {
        this.deptno = deptno;
    }

    public String getDname() {
        return this.dname;
    }

    public void setDname(String dname) {
        this.dname = dname;
    }

    public String getLoc() {
        return this.loc;
    }

    public void setLoc(String loc) {
        this.loc = loc;
    }

    public Set getEmps() {
        return this.emps;
    }

    public void setEmps(Set emps) {
        this.emps = emps;
    }

}

emp类配置文件

<?xml version="1.0" encoding="utf-8"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">

<hibernate-mapping>
    <class name="entity.Emp" table="EMP" schema="SCOTT">
        <id name="empno" type="java.lang.Short">
            <column name="EMPNO" precision="4" scale="0" />
            <generator class="assigned" />
        </id>
        <many-to-one name="dept" class="entity.Dept" fetch="select">
            <column name="DEPTNO" precision="2" scale="0" />
        </many-to-one>
        <property name="ename" type="java.lang.String">
            <column name="ENAME" length="10" />
        </property>
        <property name="job" type="java.lang.String">
            <column name="JOB" length="9" />
        </property>
        <property name="mgr" type="java.lang.Short">
            <column name="MGR" precision="4" scale="0" />
        </property>
        <property name="hiredate" type="java.util.Date">
            <column name="HIREDATE" length="7" />
        </property>
        <property name="sal" type="java.lang.Double">
            <column name="SAL" precision="7" />
        </property>
        <property name="comm" type="java.lang.Double">
            <column name="COMM" precision="7" />
        </property>
    </class>
</hibernate-mapping>

dept类配置文件 

<?xml version="1.0" encoding="utf-8"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">

<hibernate-mapping>
    <class name="entity.Dept" table="DEPT" schema="SCOTT">
        <id name="deptno" type="java.lang.Byte">
            <column name="DEPTNO" precision="2" scale="0" />
            <generator class="assigned" />
        </id>
        <property name="dname" type="java.lang.String">
            <column name="DNAME" length="14" />
        </property>
        <property name="loc" type="java.lang.String">
            <column name="LOC" length="13" />
        </property>
        <set name="emps" inverse="true">
            <key>
                <column name="DEPTNO" precision="2" scale="0" />
            </key>
            <one-to-many class="entity.Emp" />
        </set>
    </class>
</hibernate-mapping>

<?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="dialect">
            org.hibernate.dialect.Oracle9Dialect
        </property>
        <property name="connection.url">
            jdbc:oracle:thin:@localhost:1521:orcl
        </property>
        <property name="connection.username">root</property>
        <property name="connection.password">root</property>
        <property name="connection.driver_class">
            oracle.jdbc.OracleDriver
        </property>
    
        <mapping resource="entity/Emp.hbm.xml" />
        <mapping resource="entity/Dept.hbm.xml" />

    </session-factory>

</hibernate-configuration>

二、Query接口分页实现

• 根据结果获得总亡录数：

Querty query=session.createQuery("from Emp");
List list=query.list();
int count=list.size();

• 计算总页数：

int totalPages=(count%pageSize==0)?(count/pageSize):(count/pageSize+1);

• 实现分页：

query.setFirstResult((pageIndex-1)*pageSize); //开始记录数
query.setMaxResults(pageSize);//每页显示最大记录数
List resultList=query.list();  //每页显示的记录集合 

 查询全部员工

/*
     * 查询员工表内容
     */
    public static void findEmp(){
        Session session=new Configuration().configure().buildSessionFactory().openSession();
        //查询员工表按入职日期升序排，日期相同按名字降序
        String hql=" from Emp e order by e.hiredate ,e.ename desc ";
        Query query=session.createQuery(hql);
        List<Emp> emps=query.list();
        for (Emp emp : emps) {
            System.out.println("员工编号："+emp.getEmpno()+"	姓名： "+emp.getEname()+"	入职日期："+emp.getHiredate()+"	部门名称："+emp.getDept().getDname());
        }

    }

分页查询

/*
     * 分页查询
     */
    public static void pageDisplay(){
        Session session=new Configuration().configure().buildSessionFactory().openSession();
        String hql="from Emp";
        //第几页
        int pageIndex=4;
        //每页显示记录数
        int pageSize=4;
        Query query=session.createQuery(hql);
        //起始记录数
        query.setFirstResult((pageIndex-1)*pageSize);
        //每页显示最多记录数
        query.setMaxResults(pageSize);
        List<Emp> emps=query.list();
        for (Emp emp : emps) {
            System.out.println("员工编号："+emp.getEmpno()+"	姓名： "+emp.getEname()+"	入职日期："+emp.getHiredate()+"	部门名称："+emp.getDept().getDname());
        }
        
    }

三、连接查询

部门表中有个40部门，员工表中没有人是40部门的，

首先向EMP表中插入一条没有部门的记录

insert into emp(empno,ename,job,hiredate,sal)values(8888,'张三','业务员',sysdate,800);

3.1、查询部门的所有员工使用内连接

String hql="from Dept d inner join d.emps";

3.2、查询部门的所有员工使用左外连接

String hql="from Dept d left join d.emps";

3.3、查询部门的所有员工使用右外连接

String hql="from Dept d right join d.emps";