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()+"\t姓名: "+emp.getEname()+"\t入职日期:"+emp.getHiredate()+"\t部门名称:"+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()+"\t姓名: "+emp.getEname()+"\t入职日期:"+emp.getHiredate()+"\t部门名称:"+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";
郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。