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 + "]";

    }

}

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 + "]";

    }

}

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 + "]";

    }

}

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;

    }

}