mybatis映射文件 select

时间：2021-05-25 19:16:53 阅读：20 评论：0 收藏：0 [点我收藏+]

package com.lzr.dao;

import com.lzr.bean.Employee;
import org.apache.ibatis.annotations.MapKey;
import org.apache.ibatis.annotations.Param;

import java.util.List;
import java.util.Map;

/**
 * @author GGBond
 * @create 2021-05-22-19:34
 */
public interface EmployeeMapper {

    //多条记录封装一个map：Map<Integer,Employee>:键是这条记录的主键，值是记录封装后的javabean
    //告诉mybatis封装这个map的时候使用哪个属性作为map的key
    @MapKey("id")
    public Map<Integer,Employee> getEmpByLastNameLikeReturnMap(String lastName);

    //返回一条记录的map：key就是列名，值就是对应的值
    public Map<String,Object> getEmpByIdReturnMap(Integer id);

    public List<Employee> getEmpsByLastNameLike(String lastName);


}

<?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.lzr.dao.EmployeeMapper">
    <!--namespace:名称空间;指定为接口的全类名
    id：唯一标识
    resultType：返回值类型
    #{id}：从传递过来的参数中取出id值
    -->
    <!--public Map<Integer,Employee> getEmpByLastNameLikeReturnMap(String lastName);-->
    <select id="getEmpByLastNameLikeReturnMap" resultType="com.lzr.bean.Employee">
        select * from tbl_employee where last_name like #{lastName}
    </select>

    <!--public Map<String,Object> getEmpByIdReturnMap(Integer id);-->
    <select id="getEmpByIdReturnMap" resultType="map">
        select * from tbl_employee where id=#{id}
    </select>

    <!--public List<Employee> getEmpsByLastNameLike(String lastName);-->
    <!--resultType:如果返回的是一个集合，要写集合中元素的类型-->
    <select id="getEmpsByLastNameLike" resultType="com.lzr.bean.Employee">
        select * from tbl_employee where last_name like #{lastName}
    </select>

</mapper>

 @Test
    public void test04() throws IOException {
        SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
        //1.获取到的SqlSession不会自动提交数据
        SqlSession openSession = sqlSessionFactory.openSession();
        try{
            EmployeeMapper mapper = openSession.getMapper(EmployeeMapper.class);
//            List<Employee> like = mapper.getEmpsByLastNameLike("%e%");
//            for (Employee employee:like) {
//                System.out.println(employee);
//            }
//            Map<String, Object> map = mapper.getEmpByIdReturnMap(1);
//            System.out.println(map);
            Map<Integer, Employee> map = mapper.getEmpByLastNameLikeReturnMap("%r%");
            System.out.println(map);
        }finally {
            openSession.close();
        }
    }

技术分享图片

package com.lzr.bean;

import org.apache.ibatis.type.Alias;

/**
 * @author GGBond
 * @create 2021-05-22-18:17
 */
@Alias("emp")
public class Employee {
    private Integer id;
    private String lastName;
    private String email;
    private String gender;
    private Department dept;

    public Employee() {
        super();
    }

    public Employee(Integer id, String lastName, String email, String gender) {
        this.id = id;
        this.lastName = lastName;
        this.email = email;
        this.gender = gender;
    }

    public Department getDept() {
        return dept;
    }

    public void setDept(Department dept) {
        this.dept = dept;
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getLastName() {
        return lastName;
    }

    public void setLastName(String lastName) {
        this.lastName = lastName;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public String getGender() {
        return gender;
    }

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

    @Override
    public String toString() {
        return "Employee{" +
                "id=" + id +
                ", lastName=‘" + lastName + ‘\‘‘ +
                ", email=‘" + email + ‘\‘‘ +
                ", gender=‘" + gender + ‘\‘‘ +
                ‘}‘;
    }
}

package com.lzr.bean;

/**
 * @author GGBond
 * @create 2021-05-25-10:06
 */
public class Department {

    private Integer id;
    private String departmentName;

    @Override
    public String toString() {
        return "Department{" +
                "id=" + id +
                ", departmentName=‘" + departmentName + ‘\‘‘ +
                ‘}‘;
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getDepartmentName() {
        return departmentName;
    }

    public void setDepartmentName(String departmentName) {
        this.departmentName = departmentName;
    }
}

package com.lzr.dao;

import com.lzr.bean.Employee;

/**
 * @author GGBond
 * @create 2021-05-25-9:45
 */
public interface EmployeeMapperPlus {

    public Employee getEmpById(Integer id);

    public Employee getEmpAndDept(Integer 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.lzr.dao.EmployeeMapperPlus">
    
    <!--自定义某个javabean的封装规则
    type：自定义规则的java类型
    id：唯一id方便引用
    -->
    <resultMap id="MySimpleEmp" type="com.lzr.bean.Employee">
        <!--指定主键列的封装规则
        id定义主键底层会有优化；
        column:指定哪一列
        property：指定对应的javabean属性
        -->
        <id column="id" property="id"/>
        <!--定义普通列封装规则-->
        <result column="last_name" property="lastName"/>
        <!--其他把不指定的列会自动封装:我们只要写resultMap就把全部的映射规则都写上-->
        <result column="email" property="email"/>
        <result column="gender" property="gender"/>
    </resultMap>
    <!--public Employee getEmpById(Integer id);-->
    <!--resulyMap：自定义结果集映射规则-->
    <select id="getEmpById" resultMap="MySimpleEmp">
        select * from tbl_employee where id=#{id}
    </select>

    <!--
    场景一：
        查询Employee的同时查询员工对应的部门
        Employee===Depatment
        一个员工有与之对应的部门信息
    -->
    <!--
    联合查询：级联属性封装结果集
    -->
    <resultMap id="MyDifEmp" type="com.lzr.bean.Employee">
        <id column="id" property="id"/>
        <result column="last_name" property="lastName"/>
        <result column="gender" property="gender"/>
        <result column="did" property="dept.id"/>
        <result column="dept_name" property="dept.departmentName"/>
    </resultMap>
    <!--public Employee getEmpAndDept(Integer id);-->
    <select id="getEmpAndDept" resultMap="MyDifEmp">
        select e.id id,e.last_name last_name,e.gender gender,e.d_id d_id,d.id did,d.dept_name dept_name
        from tbl_employee e,tbl_dept d where e.d_id=d.id and e.id=#{id}
    </select>
</mapper>

@Test
    public void test05() throws IOException {
        SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
        SqlSession openSession = sqlSessionFactory.openSession();
        try {
            EmployeeMapperPlus mapper = openSession.getMapper(EmployeeMapperPlus.class);
//            Employee empById = mapper.getEmpById(1);
//            System.out.println(empById);
            Employee empAndDept = mapper.getEmpAndDept(1);
            System.out.println(empAndDept);
            System.out.println(empAndDept.getDept());
        }finally {
            openSession.close();
        }


    }

技术分享图片

<?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.lzr.dao.EmployeeMapperPlus">
    
    <!--自定义某个javabean的封装规则
    type：自定义规则的java类型
    id：唯一id方便引用
    -->
    <resultMap id="MySimpleEmp" type="com.lzr.bean.Employee">
        <!--指定主键列的封装规则
        id定义主键底层会有优化；
        column:指定哪一列
        property：指定对应的javabean属性
        -->
        <id column="id" property="id"/>
        <!--定义普通列封装规则-->
        <result column="last_name" property="lastName"/>
        <!--其他把不指定的列会自动封装:我们只要写resultMap就把全部的映射规则都写上-->
        <result column="email" property="email"/>
        <result column="gender" property="gender"/>
    </resultMap>
    <!--public Employee getEmpById(Integer id);-->
    <!--resulyMap：自定义结果集映射规则-->
    <select id="getEmpById" resultMap="MySimpleEmp">
        select * from tbl_employee where id=#{id}
    </select>

    <!--
    场景一：
        查询Employee的同时查询员工对应的部门
        Employee===Depatment
        一个员工有与之对应的部门信息
    -->
    <!--
    联合查询：级联属性封装结果集
    -->
    <resultMap id="MyDifEmp" type="com.lzr.bean.Employee">
        <id column="id" property="id"/>
        <result column="last_name" property="lastName"/>
        <result column="gender" property="gender"/>
        <result column="did" property="dept.id"/>
        <result column="dept_name" property="dept.departmentName"/>
    </resultMap>
    <!--使用association定义关联的单个对象的封装规则-->
    <resultMap id="MyDifEmp2" type="com.lzr.bean.Employee">
        <id column="id" property="id"/>
        <result column="last_name" property="lastName"/>
        <result column="gender" property="gender"/>

        <!--association可以指定联合的javabean对象
        property="dept"：指定哪个属性是联合的对象
        javaType：指定这个属性对象的类型[不能省略]
        -->
        <association property="dept" javaType="com.lzr.bean.Department">
            <id column="did" property="id"/>
            <result column="dept_name" property="departmentName"/>
        </association>
    </resultMap>
    <!--public Employee getEmpAndDept(Integer id);-->
    <select id="getEmpAndDept" resultMap="MyDifEmp2">
        select e.id id,e.last_name last_name,e.gender gender,e.d_id d_id,d.id did,d.dept_name dept_name
        from tbl_employee e,tbl_dept d where e.d_id=d.id and e.id=#{id}
    </select>


    <!--使用association进行分步查询
    1.先按照员工id查询员工信息
    2.根据查询员工信息中的d_id值去部门表查出部门信息
    3.部门设置到员工中；
    -->
    <resultMap id="MyEmpByStep" type="com.lzr.bean.Employee">
        <id column="id" property="id"/>
        <result column="last_name" property="lastName"/>
        <result column="email" property="email"/>
        <result column="gender" property="gender"/>
        <!--association定义关联对象的封装规则
        select：表名当前属性是调用select指定的方法查出的结果
        column：指定将哪一列的值传给这个方法
        流程：使用select指定的方法（传入column指定的这列参数的值）查出对象，并封装给property指定的属性
        -->
        <association property="dept" select="com.lzr.dao.DepartmentMapper.getDeptById"
        column="d_id">

        </association>
    </resultMap>
    <!--public Employee getEmpByIdStep(Integer id);-->
    <select id="getEmpByIdStep" resultMap="MyEmpByStep">
        select * from tbl_employee where id=#{id}
    </select>

    <!--可以使用延迟加载
    Employee==>Dept;
        我们每次查询Employee对象的时候，都将一起查询出来。
        部门信息在我们使用的时候再去查询；
        分段查询的基础之上加上两个配置；
    -->
    <!--==========================association==========================================-->

    <!--场景二：
        查询部门的时候将部门对应的所有员工信息也查询出来：注释在DepartmentMapper.xml中
    -->
    <!--public List<Employee> getEmpsByDeptId(Integer deptId);-->
    <select id="getEmpsByDeptId" resultType="com.lzr.bean.Employee">
        select * from tbl_employee where d_id=#{deptId}
    </select>

    <!--<discriminator javaType=""></discriminator>
    鉴别器：mybatis可以使用discriminator判断某列的值，然后根据某列的值改变封装行为
    封装Employee：
    -->
</mapper>

package com.lzr.bean;

import java.util.List;

/**
 * @author GGBond
 * @create 2021-05-25-10:06
 */
public class Department {

    private Integer id;
    private String departmentName;
    private List<Employee> emps;

    public List<Employee> getEmps() {
        return emps;
    }

    public void setEmps(List<Employee> emps) {
        this.emps = emps;
    }

    @Override
    public String toString() {
        return "Department{" +
                "id=" + id +
                ", departmentName=‘" + departmentName + ‘\‘‘ +
                ‘}‘;
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getDepartmentName() {
        return departmentName;
    }

    public void setDepartmentName(String departmentName) {
        this.departmentName = departmentName;
    }
}

package com.lzr.dao;

import com.lzr.bean.Employee;

import java.util.List;

/**
 * @author GGBond
 * @create 2021-05-25-9:45
 */
public interface EmployeeMapperPlus {

    public Employee getEmpById(Integer id);

    public Employee getEmpAndDept(Integer id);

    public Employee getEmpByIdStep(Integer id);

    public List<Employee> getEmpsByDeptId(Integer deptId);

}

package com.lzr.dao;

import com.lzr.bean.Department;

/**
 * @author GGBond
 * @create 2021-05-25-14:58
 */
public interface DepartmentMapper {

    public Department getDeptById(Integer id);

    public Department getDeptByIdPlus(Integer id);

    public Department getDeptByIdStep(Integer 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.lzr.dao.DepartmentMapper">

    <!--public Department getDeptById(Integer id);-->
    <select id="getDeptById" resultType="com.lzr.bean.Department">
        select id,dept_name departmentName from tbl_dept
        where id=#{id}
    </select>

    <!--
    collection嵌套结果集的方式，定义关联的集合类型元素的封装规则
    -->
    <!--public class Department {
    private Integer id;
    private String departmentName;
    private List<Employee> emps;
    did dept_name   || eid last_name email gender
    -->
    <!--嵌套结果集的方式，使用collection标签定义关联的集合类型的属性封装规则-->
    <resultMap id="MyDept" type="com.lzr.bean.Department">
        <id column="did" property="id"/>
        <result column="dept_name" property="departmentName"/>
        <!--
        collection：定义关联集合类型的属性的封装规则
        ofType：指定集合里面元素的类型
        -->
        <collection property="emps" ofType="com.lzr.bean.Employee">
            <!--定义这个集合中元素的封装规则-->
            <id column="eid" property="id"/>
            <result column="last_name" property="lastName"/>
            <result column="email" property="email"/>
            <result column="gender" property="gender"/>
        </collection>
    </resultMap>
    <!--public Department getDeptByIdPlus(Integer id);-->
    <select id="getDeptByIdPlus" resultMap="MyDept">
        select d.id did,d.dept_name dept_name,e.id eid,
        e.last_name last_name,e.email email,e.gender gender
        from tbl_dept d
        left join tbl_employee e
        on d.id=e.id
        where d.id=#{id}
    </select>


    <resultMap id="MyDeptStep" type="com.lzr.bean.Department">
        <id column="id" property="id"/>
        <id column="dept_name" property="departmentName"/>
        <collection property="emps" select="com.lzr.dao.EmployeeMapperPlus.getEmpsByDeptId"
        column="{deptId=id}" fetchType="eager">

        </collection>
    </resultMap>
    <!--public Department getDeptByIdStep(Integer id);-->
    <select id="getDeptByIdStep" resultMap="MyDeptStep">
        select id,dept_name departmentName from tbl_dept
        where id=#{id}
    </select>

    <!--扩展：多列的值传递过去：
    将多列的值封装map传递：
    column="{key1=column1,key2=column2}"
    fetchType="lazy"：表示使用延迟加载：
        -lazy：延迟
        -eager：立即
    -->
</mapper>

@Test
    public void test05() throws IOException {
        SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
        SqlSession openSession = sqlSessionFactory.openSession();
        try {
            EmployeeMapperPlus mapper = openSession.getMapper(EmployeeMapperPlus.class);
            /*Employee empById = mapper.getEmpById(1);
              System.out.println(empById);*/
            /*Employee empAndDept = mapper.getEmpAndDept(1);
            System.out.println(empAndDept);
            System.out.println(empAndDept.getDept());*/
            Employee employee = mapper.getEmpByIdStep(1);
            System.out.println(employee.getLastName());
            System.out.println(employee.getDept());
        }finally {
            openSession.close();
        }


    }
    @Test
    public void test06() throws IOException {
        SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
        SqlSession openSession = sqlSessionFactory.openSession();
        try{
            DepartmentMapper mapper = openSession.getMapper(DepartmentMapper.class);
//            Department department = mapper.getDeptByIdPlus(1);
//            System.out.println(department);
//            System.out.println(department.getEmps());
            Department deptByIdStep = mapper.getDeptByIdStep(1);
            System.out.println(deptByIdStep);
            System.out.println(deptByIdStep.getEmps());
        }finally {
            openSession.close();
        }
    }

技术分享图片

mybatis映射文件 select

原文：https://www.cnblogs.com/NBYG/p/14807667.html

踩

(0)

评论一句话评论（0）

分享档案

更多>

2021年09月23日 (328)
2021年09月24日 (313)
2021年09月17日 (191)
2021年09月15日 (369)
2021年09月16日 (411)
2021年09月13日 (439)
2021年09月11日 (398)
2021年09月12日 (393)
2021年09月10日 (160)
2021年09月08日 (222)