HQL语句：三表查询（一对多，一对多）

实体类：CrmDepartment

package com.learning.crm.department.domain;

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

import com.learning.crm.post.domain.CrmPost;

public class CrmDepartment {
        /*
         * 
         * CREATE TABLE `crm_department` (
              `depId` VARCHAR(255) NOT NULL PRIMARY KEY,
              `depName` VARCHAR(50) DEFAULT NULL
            );
         * 
         */
    
    private String depId;
    private String depName;
    
    
    //一对多 ： 一个部门有【多个职务】
    private Set<CrmPost> posts=new HashSet<CrmPost>();


    public String getDepId() {
        return depId;
    }


    public void setDepId(String depId) {
        this.depId = depId;
    }


    public String getDepName() {
        return depName;
    }


    public void setDepName(String depName) {
        this.depName = depName;
    }


    public Set<CrmPost> getPosts() {
        return posts;
    }


    public void setPosts(Set<CrmPost> posts) {
        this.posts = posts;
    }


    @Override
    public String toString() {
        return "CrmDepartment [depId=" + depId + ", depName=" + depName + "]";
    }
    
    
    
}

 

CrmPost:

package com.learning.crm.post.domain;

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

import com.learning.crm.department.domain.CrmDepartment;
import com.learning.crm.staff.domain.CrmStaff;

public class CrmPost {
        /*
         * 
            CREATE TABLE `crm_post` (
              `postId` varchar(255) NOT NULL PRIMARY KEY,
              `postName` varchar(100) DEFAULT NULL,
              `depId` varchar(255) DEFAULT NULL,
              CONSTRAINT FOREIGN KEY (`depId`) REFERENCES `crm_department` (`depId`)
            );
         * 
         */
    
    private String postId;
    private String postName;
    
    //多对一        多个职务对应【一个部门】
    private CrmDepartment department;
    
    //一对多          一个职务有【多个员工】
    private Set<CrmStaff> staffs=new HashSet<CrmStaff>();

    public String getPostId() {
        return postId;
    }

    public void setPostId(String postId) {
        this.postId = postId;
    }

    public String getPostName() {
        return postName;
    }

    public void setPostName(String postName) {
        this.postName = postName;
    }

    public CrmDepartment getDepartment() {
        return department;
    }

    public void setDepartment(CrmDepartment department) {
        this.department = department;
    }

    public Set<CrmStaff> getStaffs() {
        return staffs;
    }

    public void setStaffs(Set<CrmStaff> staffs) {
        this.staffs = staffs;
    }

    @Override
    public String toString() {
        return "CrmPost [postId=" + postId + ", postName=" + postName + ", department=" + department + "]";
    }

    
    
    
    
}

 

CrmStaff:

package com.learning.crm.staff.domain;

import java.util.Date;

import com.learning.crm.post.domain.CrmPost;

public class CrmStaff {
        
    /*
     * 
        CREATE TABLE `crm_staff` (
          `staffId` VARCHAR(255) NOT NULL PRIMARY KEY,
          `loginName` VARCHAR(100) DEFAULT NULL,
          `loginPwd` VARCHAR(100) DEFAULT NULL,
          `staffName` VARCHAR(100) DEFAULT NULL,
          `gender` VARCHAR(20) DEFAULT NULL,
          `onDutyDate` DATETIME DEFAULT NULL,
          `postId` VARCHAR(255) DEFAULT NULL,
          CONSTRAINT FOREIGN KEY (`postId`) REFERENCES `crm_post` (`postId`)
        );
     * 
     */
    
    private String staffId;
    private String loginName;
    private String loginPwd;
    private String staffName;
    private String gender;
    private Date onDutyDate;
    
    //多对一   多个员工对应【一个职务】
    
    private CrmPost post;

    public String getStaffId() {
        return staffId;
    }

    public void setStaffId(String staffId) {
        this.staffId = staffId;
    }

    public String getLoginName() {
        return loginName;
    }

    public void setLoginName(String loginName) {
        this.loginName = loginName;
    }

    public String getLoginPwd() {
        return loginPwd;
    }

    public void setLoginPwd(String loginPwd) {
        this.loginPwd = loginPwd;
    }

    public String getStaffName() {
        return staffName;
    }

    public void setStaffName(String staffName) {
        this.staffName = staffName;
    }

    public String getGender() {
        return gender;
    }

    public void setGender(String gender) {
        this.gender = gender;
    }

    public Date getOnDutyDate() {
        return onDutyDate;
    }

    public void setOnDutyDate(Date onDutyDate) {
        this.onDutyDate = onDutyDate;
    }

    public CrmPost getPost() {
        return post;
    }

    public void setPost(CrmPost post) {
        this.post = post;
    }

    @Override
    public String toString() {
        return "CrmStaff [staffId=" + staffId + ", loginName=" + loginName + ", loginPwd=" + loginPwd + ", staffName="
                + staffName + ", gender=" + gender + ", onDutyDate=" + onDutyDate + ", post=" + post + "]";
    }
    
    
    
    
}

 

spring中多表查询+条件查询

package com.learning.crm.staff.dao.impl;

import static org.junit.Assert.*;

import java.io.Serializable;
import java.util.List;

import org.aspectj.weaver.patterns.ThisOrTargetAnnotationPointcut;
import org.junit.Test;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;

import com.learning.crm.department.dao.DepartmentDao;
import com.learning.crm.post.dao.PostDao;
import com.learning.crm.post.dao.impl.PostDaoImpl;
import com.learning.crm.staff.dao.StaffDao;
import com.learning.crm.staff.domain.CrmStaff;

public class StaffDaoImpl extends HibernateDaoSupport implements StaffDao{
    
    
    private PostDao postDao;

    public void setPostDao(PostDao postDao) {
        this.postDao = postDao;
    }

    @Override
    public List<CrmStaff> findAllStaffByCondition(String postName, String depName, String staffName) {
        
        //where连接查询
//        List<Object[]> list = this.getHibernateTemplate().find(" select s.staffName,p.postName,d.depName from CrmStaff s,CrmPost p,CrmDepartment d where s.post=p "
//                + "and s.post.department=d and p.postName =? and d.depName=? and s.staffName=? ",postName,depName,staffName);
        //join两表连接查询
//        List<Object[]> list = this.getHibernateTemplate().find(" select s.staffName,p.postName,d.depName from CrmStaff s left join s.post p where 1=1 and "
//                + " p.postName =? and s.staffName=? ",postName,staffName);
        
        //join三表连接查询
        List<CrmStaff> list = this.getHibernateTemplate().find(" select s from CrmStaff s left join s.post p left join p.department d where 1=1 and "
                + " d.depName=? and p.postName =? and s.staffName=? ",depName,postName,staffName);
        
        //+ " p.postName =? and s.staffName=? ",postName,staffName
        //and p.postName =? and=d.depName=? and s.staffName=? 
        //where s.post.postName=p.postName 
        //s.post.department.depName=d.depName
        
        /*for (Object[] objects : list) {
            for (Object object : objects) {
                System.out.print(object);
            }
            System.out.println();
        }*/
            
        for (CrmStaff object : list) {
            object.getPost().getDepartment().getDepName();
            object.getPost().getPostName();
            System.out.println(object);
        }
    
        return null;
    }
    
    
    
}