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

妖狐艹你老母 2023-05-28 08:25 7阅读 0赞

1 准备两张表employee\_basic（员工信息表）,employee\_school(员工学历表)  
![在这里插入图片描述][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]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW5nNjEzMjMyNg_size_16_color_FFFFFF_t_70 3]  
3 创建pojo实体类

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 EmployeeSchool employeeSchool;
    
        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 EmployeeSchool getEmployeeSchool() { 
            return employeeSchool;
        }
    
        public void setEmployeeSchool(EmployeeSchool employeeSchool) { 
            this.employeeSchool = employeeSchool;
        }
    
    }

package com.zxf.pojo;
    
    import java.io.Serializable;
    
    public class EmployeeSchool implements Serializable { 
        private String emp_id;
        private String emp_xueli;
        private String emp_major;
        private String by_date;
        private String by_school;
    
        public String getEmp_id() { 
            return emp_id;
        }
    
        public void setEmp_id(String emp_id) { 
            this.emp_id = emp_id;
        }
    
        public String getEmp_xueli() { 
            return emp_xueli;
        }
    
        public void setEmp_xueli(String emp_xueli) { 
            this.emp_xueli = emp_xueli;
        }
    
        public String getEmp_major() { 
            return emp_major;
        }
    
        public void setEmp_major(String emp_major) { 
            this.emp_major = emp_major;
        }
    
        public String getBy_date() { 
            return by_date;
        }
    
        public void setBy_date(String by_date) { 
            this.by_date = by_date;
        }
    
        public String getBy_school() { 
            return by_school;
        }
    
        public void setBy_school(String by_school) { 
            this.by_school = by_school;
        }
    }

3 编写Mapper层的数据库操作接口类

package com.zxf.mapper;
    
    import com.zxf.pojo.EmployeeSchool;
    
    public interface EmployeeSchoolMapper { 
        public EmployeeSchool selectEmployeeSchoolById(String emp_id);
    }

4 编写对应接口的Mybatis的映射配置文件

<?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="employeeMap">
              SELECT * FROM employee_basic WHERE emp_no=#{ id}
          </select>
        
        <resultMap id="employeeMap" 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>
            
              <association property="employeeSchool" column="emp_no"
                           select="com.zxf.mapper.EmployeeSchoolMapper.selectEmployeeSchoolById"
              javaType="EmployeeSchool"/>
    
        </resultMap>
    </mapper>

5 编写Mybatis的核心数据库资源配置文件

<?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.EmployeeSchoolMapper"/>
            <mapper class="com.zxf.mapper.EmployeeMapper"/>
        </mappers>
    </configuration>

数据库属性文件

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

6 编写测试程序

package com.zxf;
    
    import com.zxf.mapper.EmployeeMapper;
    import com.zxf.mapper.EmployeeSchoolMapper;
    import com.zxf.mapper.TeacherMapper;
    import com.zxf.pojo.Employee;
    import com.zxf.pojo.EmployeeSchool;
    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 Test6 { 
        private SqlSessionFactory sqlSessionFactory;
        private SqlSession session;
        //private EmployeeSchoolMapper employeeSchoolMapper;
        private EmployeeMapper employeeMapper;
        private InputStream is;
        @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(){ EmployeeSchool employeeSchool = employeeSchoolMapper.selectEmployeeSchoolById("HW9808"); System.out.println(employeeSchool.getEmp_xueli()+","+employeeSchool.getBy_school()+","+employeeSchool.getEmp_major()); } */
        @Test
        public void test2(){ 
            Employee employee = employeeMapper.selectEmployeeById("HW9806");
            System.out.println(employee.getEmp_name()+","+employee.getEmployeeSchool().getBy_school());
    
        }
    }

例如查询员工编号为HW9806的学历和名字  
![在这里插入图片描述][20200328163905240.png]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW5nNjEzMjMyNg_size_16_color_FFFFFF_t_70]: /images/20230528/f59c820e922d477da64f6943e087288d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW5nNjEzMjMyNg_size_16_color_FFFFFF_t_70 1]: /images/20230528/537877f8824a4635be1c68974e352aee.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW5nNjEzMjMyNg_size_16_color_FFFFFF_t_70 2]: /images/20230528/f6100ea602f044c8937474a639bc4961.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW5nNjEzMjMyNg_size_16_color_FFFFFF_t_70 3]: /images/20230528/f5d575498d6247febfd8c9e4fda1e48d.png
[20200328163905240.png]: /images/20230528/0879d2a9a9944229b897932817462bb3.png