MyBatis中多对多关系查询案例(三)

柔光的暖阳◎ 2023-07-19 10:34 10阅读 0赞

1 表结构  
因为是多对多关系，所以用到3张表，我们在平时一个公司一个人可能身兼多个职位，而一个位置肯定会有多个人员。所以人员和职位之间就存在了多对多的关系。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW5nNjEzMjMyNg_size_16_color_FFFFFF_t_70]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW5nNjEzMjMyNg_size_16_color_FFFFFF_t_70 1]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW5nNjEzMjMyNg_size_16_color_FFFFFF_t_70 2]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW5nNjEzMjMyNg_size_16_color_FFFFFF_t_70 3]  
多对多的第三张表  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW5nNjEzMjMyNg_size_16_color_FFFFFF_t_70 4]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW5nNjEzMjMyNg_size_16_color_FFFFFF_t_70 5]  
2因为使用了是Maven的聚合管理模式。项目结构如下  
![在这里插入图片描述][20200330140755610.png]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW5nNjEzMjMyNg_size_16_color_FFFFFF_t_70 6]  
3 主项目的pom文件内容

<?xml version="1.0" encoding="UTF-8"?>
    
    <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
      xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
      <modelVersion>4.0.0</modelVersion>
      <groupId>com.zxf</groupId>
      <artifactId>mybatisProject4</artifactId>
      <packaging>pom</packaging>
      <version>1.0-SNAPSHOT</version>
      <modules>
        <module>mybatisProject4-pojo</module>
        <module>mybatisProject4-mapper</module>
        <module>mybatisProject4-service</module>
      </modules>
      
      <properties>
        <junit.version>4.12</junit.version>
        <mybatis.version>3.2.8</mybatis.version>
        <mysql.version>5.1.32</mysql.version>
        <log4j.version>1.2.17</log4j.version>
        <cglib.version>2.2.2</cglib.version>
        <asm.version>3.1</asm.version>
      </properties>
      <dependencyManagement>
        <dependencies>
          <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>${ junit.version}</version>
          </dependency>
          
          <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>${ mybatis.version}</version>
          </dependency>
          <dependency>
            <groupId>cglib</groupId>
            <artifactId>cglib</artifactId>
            <version>${ cglib.version}</version>
          </dependency>
          <dependency>
            <groupId>asm</groupId>
            <artifactId>asm</artifactId>
            <version>${ asm.version}</version>
          </dependency>
          
          <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>${ mysql.version}</version>
          </dependency>
          <dependency>
            <groupId>log4j</groupId>
            <artifactId>log4j</artifactId>
            <version>${ log4j.version}</version>
          </dependency>
        </dependencies>
      </dependencyManagement>
       <build>
         
         <resources>
           <resource>
             <directory>src/main/java</directory>
             <includes>
               <include>**/*.xml</include> </includes> </resource> <resource> <directory>src/main/resouces</directory> <includes> <include>**/*.xml</include>
               <include>**/*.properties</include> </includes> </resource> </resources> </build> </project>

4 pojo类内容

package com.zxf.pojo;
    
    import java.io.Serializable;
    import java.util.List;
    
    public class Employee implements Serializable { 
        private String emp_no;
        private String emp_name;
        private String dept_id;
        private String emp_gender;
        private String emp_email;
        // 这里假定，员工和工作岗位（角色）是多对多的关系，
        // 每一个员工可有多于一个的工作岗位（角色），每个工作岗位（角色）可有多个员工
        private List<Job> jobs;
    
        public String getEmp_no() { 
            return emp_no;
        }
    
        public void setEmp_no(String emp_no) { 
            this.emp_no = emp_no;
        }
    
        public String getEmp_name() { 
            return emp_name;
        }
    
        public void setEmp_name(String emp_name) { 
            this.emp_name = emp_name;
        }
    
        public String getDept_id() { 
            return dept_id;
        }
    
        public void setDept_id(String dept_id) { 
            this.dept_id = dept_id;
        }
    
        public String getEmp_gender() { 
            return emp_gender;
        }
    
        public void setEmp_gender(String emp_gender) { 
            this.emp_gender = emp_gender;
        }
    
        public String getEmp_email() { 
            return emp_email;
        }
    
        public void setEmp_email(String emp_email) { 
            this.emp_email = emp_email;
        }
    
        public List<Job> getJobs() { 
            return jobs;
        }
    
        public void setJobs(List<Job> jobs) { 
            this.jobs = jobs;
        }
    }

package com.zxf.pojo;
    
    import java.io.Serializable;
    import java.util.List;
    
    public class Job implements Serializable { 
        private String job_id;
        private String job_name;
        private String job_task;
    
        // 这里假定，员工和工作岗位（角色）是多对多的关系，
        // 每一个员工可有多于一个的工作岗位（角色），每个工作岗位（角色）可有多个员工
        private List<Employee> employees;
    
        public String getJob_id() { 
            return job_id;
        }
    
        public void setJob_id(String job_id) { 
            this.job_id = job_id;
        }
    
        public String getJob_name() { 
            return job_name;
        }
    
        public void setJob_name(String job_name) { 
            this.job_name = job_name;
        }
    
        public String getJob_task() { 
            return job_task;
        }
    
        public void setJob_task(String job_task) { 
            this.job_task = job_task;
        }
    
        public List<Employee> getEmployees() { 
            return employees;
        }
    
        public void setEmployees(List<Employee> employees) { 
            this.employees = employees;
        }
    }

5 mapper层  
pom.xml文件内容

log4j内容

log4j.rootCategory=DEBUG,CONSOLE,LOGFILE
    log4j.appender.CONSOLE=org.apache.log4j.ConsoleAppender
    log4j.appender.CONSOLE.layout=org.apache.log4j.PatternLayout
    log4j.appender.CONSOLE.layout.ConversionPattern=%C %d{ YYYY-MM-dd hh:mm:ss} %L %m %n
    
    log4j.appender.LOGFILE=org.apache.log4j.FileAppender
    log4j.appender.LOGFILE.File=h://zhang.log
    log4j.appender.LOGFILE.Append=true
    log4j.appender.LOGFILE.layout=org.apache.log4j.PatternLayout
    log4j.appender.LOGFILE.layout.ConversionPattern=%C %m %n  %L

数据库连接信息

jdbc.driver=com.mysql.jdbc.Driver
    jdbc.url=jdbc:mysql://192.168.0.148:3306/test
    jdbc.user=root
    jdbc.password=abc

Mybatis核心配置文件  
zhangMybatis.xml

<?xml version="1.0" encoding="UTF-8"?>
    <!DOCTYPE configuration
            PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
            "http://mybatis.org/dtd/mybatis-3-config.dtd">
    <configuration>
        <properties resource="jdbc.properties"/>
        <settings>
            <setting name="logImpl" value="LOG4J"/>
        </settings>
        <typeAliases>
            <package name="com.zxf.pojo"/>
        </typeAliases>
    
        
        <environments default="abc">
            <environment id="abc">
                <transactionManager type="JDBC"></transactionManager>
                <dataSource type="POOLED">
                    <property name="driver" value="${jdbc.driver}"/>
                    <property name="url" value="${jdbc.url}"/>
                    <property name="username" value="${jdbc.user}"/>
                    <property name="password" value="${jdbc.password}"/>
                </dataSource>
            </environment>
        </environments>
        <mappers>
            <mapper class="com.zxf.mapper.EmployeeMapper"></mapper>
            <mapper class="com.zxf.mapper.JobMapper"></mapper>
        </mappers>
    </configuration>

mapper层的接口及映射配置文件

package com.zxf.mapper;
    
    import com.zxf.pojo.Employee;
    
    public interface EmployeeMapper { 
        Employee selectEmployeeById(String emp_no);
    }

<?xml version="1.0" encoding="UTF-8"?>
    <!DOCTYPE mapper
            PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
            "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
    <mapper namespace="com.zxf.mapper.EmployeeMapper">
        <resultMap id="employeeResultMap" type="Employee">
            <id property="emp_no" column="emp_no"></id>
            <result property="emp_name" column="emp_name"></result>
            <result property="dept_id" column="dept_id"></result>
            <result property="emp_gender" column="emp_gender"></result>
            <result property="emp_email" column="emp_email"></result>
            
             <collection property="jobs" javaType="ArrayList" column="emp_no"
                         ofType="job" select="com.zxf.mapper.JobMapper.selectJobByEmpId"
                         fetchType="lazy">
                 <id property="job_id" column="job_id"></id>
                 <result property="job_name" column="job_name"></result>
                 <result property="job_task" column="job_task"></result>
             </collection>
        </resultMap>
         <select id="selectEmployeeById" parameterType="string" resultMap="employeeResultMap">
              SELECT * from  employee_basic WHERE emp_no = #{ emp_no}
         </select>
    
        <select id="selectEmployeeByJobId" parameterType="string" resultType="Employee">
            SELECT * from employee_basic where  emp_no IN
            (SELECT emp_no from employee_job WHERE job_id = #{ job_id})
        </select>
    </mapper>

package com.zxf.mapper;
    
    import com.zxf.pojo.Job;
    
    public interface JobMapper { 
        Job  selectJobById(String job_id);
    }

<?xml version="1.0" encoding="UTF-8"?>
    <!DOCTYPE mapper
            PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
            "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
    <mapper namespace="com.zxf.mapper.JobMapper">
       <select id="selectJobByEmpId" parameterType="string" resultType="job">
           SELECT * from job where
           job_id in (SELECT job_id from employee_job where emp_no=#{ emp_no})
       </select>
        <resultMap id="jobResultMap" type="job">
            <id property="job_id" column="job_id" />
            <result property="job_name" column="job_name" />
            <result property="job_task" column="job_task" />
            
            <collection property="employees" javaType="ArrayList"
                        column="job_id" ofType="Employee"
                        select="com.zxf.mapper.EmployeeMapper.selectEmployeeByJobId"
                        fetchType="lazy">
                <id property="emp_no" column="emp_no" />
                <result property="emp_name" column="emp_name" />
                <result property="dept_id" column="dept_id" />
                <result property="emp_gender" column="emp_gender" />
                <result property="emp_email" column="emp_email" />
            </collection>
        </resultMap>
        <select id="selectJobById" parameterType="string" resultMap="jobResultMap">
            SELECT * from job where  job_id = #{ job_id}
        </select>
    </mapper>

6 service层，这里面就一个基本的测试；来测试我们直接写的多对多关系查询  
pom文件

package com.zxf.service;
    
    import com.zxf.mapper.EmployeeMapper;
    import com.zxf.pojo.Employee;
    import com.zxf.pojo.Job;
    import org.apache.ibatis.io.Resources;
    import org.apache.ibatis.session.SqlSession;
    import org.apache.ibatis.session.SqlSessionFactory;
    import org.apache.ibatis.session.SqlSessionFactoryBuilder;
    import org.junit.After;
    import org.junit.Before;
    import org.junit.Test;
    
    import java.io.InputStream;
    import java.util.List;
    
    public class Test1 { 
        private SqlSessionFactory sqlSessionFactory;
        private SqlSession session;
        private InputStream is;
    
        private EmployeeMapper employeeMapper;
        @Before  //用于测试方法之前执行。
        public   void init() throws Exception{ 
            is= Resources.getResourceAsStream("zhangMyBatis.xml");
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
            session=sqlSessionFactory.openSession();
            employeeMapper=session.getMapper(EmployeeMapper.class);
        }
        @After  //用于测试方法之后执行。
        public void destory()throws Exception{ 
            session.commit();
            session.close();
            is.close();
        }
    
        @Test
        public void test1(){ 
            Employee employee = employeeMapper.selectEmployeeById("HW9804");
            System.out.println(employee.getEmp_name());
            List<Job> jobs = employee.getJobs();
            for(Job j:jobs){ 
                System.out.println(j.getJob_name());
            }
        }
    
    
    }

员工李莉莉编号为HW9804 的多个职位为  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW5nNjEzMjMyNg_size_16_color_FFFFFF_t_70 7]

package com.zxf.service;
    
    import com.zxf.mapper.EmployeeMapper;
    import com.zxf.mapper.JobMapper;
    import com.zxf.pojo.Employee;
    import com.zxf.pojo.Job;
    import org.apache.ibatis.io.Resources;
    import org.apache.ibatis.session.SqlSession;
    import org.apache.ibatis.session.SqlSessionFactory;
    import org.apache.ibatis.session.SqlSessionFactoryBuilder;
    import org.junit.After;
    import org.junit.Before;
    import org.junit.Test;
    
    import java.io.InputStream;
    import java.util.List;
    
    public class Test2 { 
        private SqlSessionFactory sqlSessionFactory;
        private SqlSession session;
        private InputStream is;
    
        private JobMapper jobMapper;
    
        @Before  //用于测试方法之前执行。
        public   void init() throws Exception{ 
            is= Resources.getResourceAsStream("zhangMyBatis.xml");
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
            session=sqlSessionFactory.openSession();
            jobMapper=session.getMapper(JobMapper.class);
        }
        @After  //用于测试方法之后执行。
        public void destory()throws Exception{ 
            session.commit();
            session.close();
            is.close();
        }
    
        @Test
        public void test1(){ 
            Job job = jobMapper.selectJobById("2602");
            System.out.println(job.getJob_name());
            System.out.println("****************职位人员*************************");
                    List<Employee> employees = job.getEmployees();
            for(Employee e:employees){ 
                System.out.println(e.getEmp_name());
            }
    
        }
    }

职位编号为2602 下面所包含的员工  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW5nNjEzMjMyNg_size_16_color_FFFFFF_t_70 8]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW5nNjEzMjMyNg_size_16_color_FFFFFF_t_70]: /images/20230528/8adfee2f523a4b60a0eeeede7d686bbf.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW5nNjEzMjMyNg_size_16_color_FFFFFF_t_70 1]: /images/20230528/17772a5ddd074187b08ded93a1220e96.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW5nNjEzMjMyNg_size_16_color_FFFFFF_t_70 2]: /images/20230528/98fa074366c24e6c9167ea21f1155efd.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW5nNjEzMjMyNg_size_16_color_FFFFFF_t_70 3]: /images/20230528/9d2ad2317719491abd416327fb24325f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW5nNjEzMjMyNg_size_16_color_FFFFFF_t_70 4]: /images/20230528/51b2e9662c774fcdb4a2fc78bae321b2.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW5nNjEzMjMyNg_size_16_color_FFFFFF_t_70 5]: /images/20230528/92cd59863d504365a22e66890fcf7ef4.png
[20200330140755610.png]: /images/20230528/5f722eacbc024cc7a5440b7312de9a71.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW5nNjEzMjMyNg_size_16_color_FFFFFF_t_70 6]: /images/20230528/df2fcb4136fd42e08cba3e984e699889.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW5nNjEzMjMyNg_size_16_color_FFFFFF_t_70 7]: /images/20230528/133f3bf594e04b5d9cfe90d0f6d59847.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW5nNjEzMjMyNg_size_16_color_FFFFFF_t_70 8]: /images/20230528/f54dd9d536fd4b8685975ddcb706b2fb.png