hibernate--连接查询

红太狼 2021-03-26 14:46 650阅读 0赞

创建实体类

package cn.bdqn.bean;
    
    import java.util.ArrayList;
    import java.util.Date;
    import java.util.List;
    
    /**
     * @author 小豆腐
     *
     *员工的实体类
     */
    public class Emp {
    
        private Integer empNo; 
        private String empName;
        private String job;
        private Double salary;
        private Date hireDate;
        
        //多个员工属于一个部门
        private  Dept dept;
    
        public Integer getEmpNo() {
            return empNo;
        }
    
        public void setEmpNo(Integer empNo) {
            this.empNo = empNo;
        }
    
        public String getEmpName() {
            return empName;
        }
    
        public void setEmpName(String empName) {
            this.empName = empName;
        }
    
        public String getJob() {
            return job;
        }
    
        public void setJob(String job) {
            this.job = job;
        }
    
        public Double getSalary() {
            return salary;
        }
    
        public void setSalary(Double salary) {
            this.salary = salary;
        }
    
        public Date getHireDate() {
            return hireDate;
        }
    
        public void setHireDate(Date hireDate) {
            this.hireDate = hireDate;
        }
    
        public Dept getDept() {
            return dept;
        }
    
        public void setDept(Dept dept) {
            this.dept = dept;
        }
    
        public Emp(Integer empNo, String empName, String job, Double salary,
                Date hireDate, Dept dept) {
            super();
            this.empNo = empNo;
            this.empName = empName;
            this.job = job;
            this.salary = salary;
            this.hireDate = hireDate;
            this.dept = dept;
        }
    
        public Emp() {
            super();
        }
    
        @Override
        public String toString() {
            return "Emp [empNo=" + empNo + ", empName=" + empName + ", job=" + job
                    + ", salary=" + salary + ", hireDate=" + hireDate + ", dept="
                    + dept + "]";
        }
    }

package cn.bdqn.bean;
    
    import java.util.HashSet;
    import java.util.Set;
    
    import com.sun.org.apache.bcel.internal.generic.NEW;
    
    /**
     * 
     *  部门的实体类
     */
    public class Dept {
        
        private  Integer  deptNo;
        private  String  deptName;
        private  String  location;  
        //一个部门对应多个员工
        private  Set<Emp> emps=new HashSet<>();
        public Integer getDeptNo() {
            return deptNo;
        }
        public void setDeptNo(Integer deptNo) {
            this.deptNo = deptNo;
        }
        public String getDeptName() {
            return deptName;
        }
        public void setDeptName(String deptName) {
            this.deptName = deptName;
        }
        public String getLocation() {
            return location;
        }
        public void setLocation(String location) {
            this.location = location;
        }
        public Set<Emp> getEmps() {
            return emps;
        }
        public void setEmps(Set<Emp> emps) {
            this.emps = emps;
        }
        public Dept(Integer deptNo, String deptName, String location, Set<Emp> emps) {
            super();
            this.deptNo = deptNo;
            this.deptName = deptName;
            this.location = location;
            this.emps = emps;
        }
        public Dept() {
            super();
        }
        @Override
        public String toString() {
            return "Dept [deptNo=" + deptNo + ", deptName=" + deptName
                    + ", location=" + location + ", emps=" + emps.size() + "]";
        }
        
        
        
        
    }

创建对应的数据库表

![640281-20170221214806023-794596821.png][]

![640281-20170221214824866-1228158882.png][]

创建对应的映射文件

<?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.bdqn.bean">
        <class name="Dept">
           <id name="deptNo"> 
              <generator class="assigned"/>
           </id>  
           <property name="deptName" column="dName"/>
           <property name="location" column="loc"/>
           
           <set name="emps" cascade="all" inverse="true">
            <key column="DEPTNO"/>
             <one-to-many class="Emp"/>
           </set>
      </class>
    </hibernate-mapping>

<?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.bdqn.bean">
      <class name="Emp">
           <id name="empNo"> 
              <generator class="assigned"/>
           </id>  
           <property name="empName"/>
           <property name="job"/>
           <property name="salary"/>
           <property name="hireDate"/>
           
           <many-to-one  name="dept" column="DEPTNO" class="Dept"/> 
      </class>
    </hibernate-mapping>

在hibernate.cfg.xml文件中 管理两个映射文件之后，创建测试类代码

public class EmpTest {
        Session session =null;
        Transaction transaction=null;
        
        @Before
        public  void  before(){
             session = HibernateSessionUtil.getCurrentSession();
             transaction= session.beginTransaction();
        }
        
        /**
         * 先给两个表中 增加测试数据
         */
        @Test
        public  void  testAdd(){
            /**
             * 因为我们设置了级联操作 cascade=all
             * 那么我们就可以在保存部门信息的时候保存员工
             */
            Dept dept=new Dept();
            dept.setDeptNo(2);
            dept.setDeptName("市场部");
            dept.setLocation("2楼");
            //创建员工
            Emp emp1=new Emp(5, "员工5", "程序猿5", 5000.0,dept);
            Emp emp2=new Emp(6, "员工6", "程序猿6", 545000.0,dept);
            Emp emp3=new Emp(7, "员工7", "程序猿7", 2000.0,dept);
            Emp emp4=new Emp(8, "员工8", "程序猿8", 98000.0,dept);
            Set<Emp> emps=new HashSet<>();
            emps.add(emp1);
            emps.add(emp2);
            emps.add(emp3);
            emps.add(emp4);
            dept.setEmps(emps);  //把所有的员工放入了集合中
            session.save(dept);
            transaction.commit();
        }
        
        
        /**
         *  所有的迫切连接返回的都是一个对象！     有fetch===》迫切连接
         *  所有的非迫切连接返回的都是一个数组！
         *  
         *  
         *  01.使用内连接查询  两个表的所有数据
         *  sql语句
         *  select * from emp inner join dept 
              on dept.deptno=emp.deptno
            
            hql语句
            from Emp e inner join e.dept(Emp类中的关联属性名)
         */
        @Test
        public  void  test01(){
            String hql="from Emp e inner join e.dept";
             List<Object[]> list = session.createQuery(hql).list();
             for (Object[] objects : list) {
                System.out.println(objects[0]);  //Emp对象
                System.out.println(objects[1]); //Dept对象
            }
        }
        
        /**
         * 02.迫切内连接   返回的就是一个对象
         */
        @Test
        public  void  test02(){
            String hql="from Emp e inner join fetch e.dept";
             List<Emp> list = session.createQuery(hql).list();
             for (Emp emp : list) {
                System.out.println(emp);  //Emp对象
            }
        }
        /**
         * 03.隐式内连接   返回的就是一个对象
         * e.dept.deptNo  通过这个属性 进行关联连接
         */
        @Test
        public  void  test03(){
            String hql="from Emp e  where e.dept.deptNo=:dNo";
            List<Emp> list = session.createQuery(hql).setParameter("dNo", 2).list();
            for (Emp emp : list) {
                System.out.println(emp);  //Emp对象
            }
        }
        /**
         * 04.隐式内连接 投影 查询        查询部门编号为2的所有员工的姓名和薪水
         * e.dept.deptNo  通过这个属性 进行关联连接
         */
        @Test
        public  void  test04(){
            String hql="select empName,salary from Emp e where e.dept.deptNo=:dNo ";
            List<Object[]> list = session.createQuery(hql).setParameter("dNo", 2).list();
            for (Object[] objects : list) {
                System.out.print(objects[0]+"\t");  //姓名
                System.out.println(objects[1]);  //薪水
            }
        }
        /**
         * 05.使用左外连接   查询员工和部门的信息
         *  以左表为准，右表中没有对应的数据，返回null！
         */
        @Test
        public  void  test05(){
            String  hql="from  Emp e left join e.dept";
            List <Object[]>list = session.createQuery(hql).list();
            for (Object[] objects : list) {
                System.out.print(objects[0]+"\t"); //Emp对象
                System.out.println(objects[1]); //Dept对象
            }
        }
        /**
         * 06.使用迫切左外连接   查询员工和部门的信息
         *  以左表为准，右表中没有对应的数据，对象的属性null！
         *  Emp类中的dept属性为null！
         */
        @Test
        public  void  test06(){
            String  hql="from  Emp e left join fetch e.dept";
            List <Emp>list = session.createQuery(hql).list();
            for (Emp emp : list) {
                System.out.println(emp); //Emp对象
            }
        }
        /**
         * 07.使用右外连接   查询员工和部门的信息
         *  以右表为准，左表中没有对应的数据，返回null！
         */
        @Test
        public  void  test07(){
            String  hql="from  Emp e right join e.dept";
            List <Object[]>list = session.createQuery(hql).list();
            for (Object[] objects : list) {
                System.out.print(objects[0]+"\t"); //Emp对象
                System.out.println(objects[1]); //Dept对象
                System.out.println("***********************************");
            }
        }
        /**
         * 08.使用迫切右外连接   查询员工和部门的信息
         *  对象就是null！   虽然可以写  迫切右外连接  但是没有实际的意义！
         *  @Test
          public  void  test08(){
            String  hql="from  Emp e right join fetch e.dept";
            List <Emp>list = session.createQuery(hql).list();
            for (Emp emp : list) {
                System.out.println(emp.getEmpName()); //Emp对象   空指针异常
            }
        }
         */
        
        
        
    }

[640281-20170221214806023-794596821.png]: /images/20210326/1616769957760.png
[640281-20170221214824866-1228158882.png]: /images/20210326/1616769937450.png