MyBatis中一对多和多对一表关系查询案例(二)

比眉伴天荒 2023-07-18 14:29 6阅读 0赞

1 表结构 员工表和部门表：一个员工所属一个部门，一个部门下有多个员工，这样就形成了一对多和多对一的表关系，下面用Mybatis展示给大家。  
![在这里插入图片描述][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]

2 表中的模拟数据内容  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW5nNjEzMjMyNg_size_16_color_FFFFFF_t_70 2]  
![在这里插入图片描述][20200329135130530.png]  
3 idea中项目的整体结构  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW5nNjEzMjMyNg_size_16_color_FFFFFF_t_70 3]  
本项目采用Maven的管理形式，通过使用Maven中聚合来管理各个层次。如果想要了解Maven聚合关系的请在留言处，给我留言，我也会解答大家在学习过程中遇到的问题。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW5nNjEzMjMyNg_size_16_color_FFFFFF_t_70 4]  
4 Pojo层  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW5nNjEzMjMyNg_size_16_color_FFFFFF_t_70 5]

package com.zxf.pojo;
    
    import java.util.List;
    
    public class Dept { 
        private String dept_id;
        private String dept_name;
        private String dept_manager;
    
        private List<Employee> employees;
    
        public String getDept_id() { 
            return dept_id;
        }
    
        public void setDept_id(String dept_id) { 
            this.dept_id = dept_id;
        }
    
        public String getDept_name() { 
            return dept_name;
        }
    
        public void setDept_name(String dept_name) { 
            this.dept_name = dept_name;
        }
    
        public String getDept_manager() { 
            return dept_manager;
        }
    
        public void setDept_manager(String dept_manager) { 
            this.dept_manager = dept_manager;
        }
    
        public List<Employee> getEmployees() { 
            return employees;
        }
    
        public void setEmployees(List<Employee> employees) { 
            this.employees = employees;
        }
    }

package com.zxf.pojo;
    
    import java.io.Serializable;
    
    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 Dept dept;
    
        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 Dept getDept() { 
            return dept;
        }
    
        public void setDept(Dept dept) { 
            this.dept = dept;
        }
    }

4 mapper层

package com.zxf.mapper;
    
    import com.zxf.pojo.Dept;
    
    public interface DeptMapper { 
        Dept selectDeptById(String dept_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.DeptMapper">
        
        <select id="selectDeptById" parameterType="string" resultMap="deptResultMap">
            SELECT * from dept WHERE dept_id=#{ dept_id}
        </select>
        
          <resultMap id="deptResultMap" type="dept">
                <id property="dept_id" column="dept_id"></id>
                <result property="dept_name" column="dept_name"></result>
              <result property="dept_manager" column="dept_manager"></result>
              
              <collection property="employees" javaType="ArrayList" column="dept_id"
                          ofType="Employee"
                          select="com.zxf.mapper.EmployeeMapper.selectEmployeeByDeptId"
                        >
                  <id property="emp_no" column="emp_no"></id>
                  <result property="dept_id" column="dept_id"></result>
                  <result property="emp_name" column="emp_name"></result>
                  <result property="emp_email" column="emp_email"></result>
                  <result property="emp_gender" column="emp_gender"></result>
              </collection>
          </resultMap>
    </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">
        
        <select id="selectEmployeeById" parameterType="string" resultMap="employeeResultMap">
             SELECT * from employee_basic e ,dept d
             WHERE e.dept_id = d.dept_id
             and  e.emp_no = #{ emp_no}
        </select>
        
        <select id="selectEmployeeByDeptId" parameterType="string" resultMap="employeeResultMap">
                SELECT * from employee_basic WHERE dept_id = #{ dept_id}
        </select>
        
        <resultMap id="employeeResultMap" type="Employee">
            <id property="emp_no" column="emp_no"></id>
            <result property="dept_id" column="dept_id"></result>
            <result property="emp_name" column="emp_name"></result>
            <result property="emp_email" column="emp_email"></result>
            <result property="emp_gender" column="emp_gender"></result>
            
            <association property="dept"  javaType="dept">
                <id  property="dept_id" column="dept_id"></id>
                <result property="dept_name" column="dept_name"></result>
                <result property="dept_manager" column="dept_manager"></result>
            </association>
        </resultMap>
    </mapper>

有关数据库连接和log4j的配置

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

<?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.DeptMapper"></mapper>
        </mappers>
    </configuration>

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

5 service层，这里只写了简单的测试类  
![在这里插入图片描述][20200329135923502.png]

package com.zxf;
    
    import com.zxf.mapper.DeptMapper;
    import com.zxf.pojo.Dept;
    import com.zxf.pojo.Employee;
    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 DeptMapper deptMapper;
        @Before  //用于测试方法之前执行。
        public   void init() throws Exception{ 
            is= Resources.getResourceAsStream("zhangMyBatis.xml");
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
            session=sqlSessionFactory.openSession();
            deptMapper=session.getMapper(DeptMapper.class);
        }
        @After  //用于测试方法之后执行。
        public void destory()throws Exception{ 
            session.commit();
            session.close();
            is.close();
        }
    
        @Test
        public void test1(){ 
            //通过部门编号查询，当前部门的人员
            Dept dept = deptMapper.selectDeptById("101");
              System.out.print(dept.getDept_name());
              System.out.println(" 部门负责人编号："+dept.getDept_manager());
              System.out.println("***********************************************");
            List<Employee> employees = dept.getEmployees();
             for (Employee e:employees){ 
                 System.out.print(e.getEmp_name()+" ");
                 System.out.println(e.getEmp_gender());
    
             }
        }
    
    }

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW5nNjEzMjMyNg_size_16_color_FFFFFF_t_70 6]  
103 技术部门的人员情况  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW5nNjEzMjMyNg_size_16_color_FFFFFF_t_70 7]

package com.zxf;
    
    import com.zxf.mapper.DeptMapper;
    import com.zxf.mapper.EmployeeMapper;
    import com.zxf.pojo.Employee;
    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;
    
    public class Test2 { 
        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("HW9801");
            System.out.print(employee.getEmp_name()+" 所在部门:");
            System.out.println(employee.getDept().getDept_name());
    
        }
    }

![在这里插入图片描述][20200329140109176.png]  
例如员工编号：hw9803所在的部门  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW5nNjEzMjMyNg_size_16_color_FFFFFF_t_70 8]  
6 最后还有个父模块的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>mybatisProject3</artifactId>
      <packaging>pom</packaging>
      <version>1.0-SNAPSHOT</version>
      <modules>
        <module>mybatisProject3-pojo</module>
        <module>mybatisProject3-mapper</module>
        <module>mybatisProject3-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>
      </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>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>

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW5nNjEzMjMyNg_size_16_color_FFFFFF_t_70]: /images/20230528/15bbf53244164c538e63cff9fe0980e1.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW5nNjEzMjMyNg_size_16_color_FFFFFF_t_70 1]: /images/20230528/ae17eef3f7e3461eb87ca0afcf380da7.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW5nNjEzMjMyNg_size_16_color_FFFFFF_t_70 2]: /images/20230528/5f553810612f4ba89ac4908e901ff6cd.png
[20200329135130530.png]: /images/20230528/f3a6ab79e67343de8afceef4270103ae.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW5nNjEzMjMyNg_size_16_color_FFFFFF_t_70 3]: /images/20230528/39006f6f635c4c48934ec558f3ed5998.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW5nNjEzMjMyNg_size_16_color_FFFFFF_t_70 4]: /images/20230528/ad9f136b82d3462094dad58162746ebb.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW5nNjEzMjMyNg_size_16_color_FFFFFF_t_70 5]: /images/20230528/8571dde5847d452cb2841580bdc623a7.png
[20200329135923502.png]: /images/20230528/6e5a4352a4e64a09a1db08abd74618ce.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW5nNjEzMjMyNg_size_16_color_FFFFFF_t_70 6]: /images/20230528/b05b548074b94202bda9f38074d43e81.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW5nNjEzMjMyNg_size_16_color_FFFFFF_t_70 7]: /images/20230528/2b219c9bb1514e54b40ac966b8df5d15.png
[20200329140109176.png]: /images/20230528/ddbf86362b3b4b9d85efa47914b220ca.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW5nNjEzMjMyNg_size_16_color_FFFFFF_t_70 8]: /images/20230528/7484e101e06941c5a07881c3c1671aea.png