首页 专题 文章 代码 归档
Mybatis的select
2020.02.05 18:42 2020.02.05 18:42

1. Select查询

1.1. 返回List

代码:

接口:

public List<Employee> findAll();

配置:

<select id="findAll" resultType="employee">
    select * from employee;
</select>

测试:

/**
 * 查询select
 */
@Test
public void test05() throws IOException {
    SqlSession sqlSession = getSqlSession();
    EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class);
    // Employee employee = new Employee("TestInsert", "TestInsert@misiai.com", "男");

    List<Employee> all = mapper.findAll();
    for (Employee employee : all) {
        System.out.println("employee = " + employee);
    }
    sqlSession.commit();//记得提交
}

结论:在配置中返回值类型就是Bean类类型即可,即resultType="employee"

1.2. 封装Map

代码:

接口:

//返回多条记录的map: k就是列名,v就是对应的值
// 告诉以id为键值
@MapKey("id")
public Map<String, Object> findResultsMap();

//返回一条记录的map: k就是列名,v就是对应的值
public Map<String, Object> findResultMapById(Integer id);

配置:

<select id="findResultMapById" resultType="java.util.Map">
    select * from employee where  id=#{id};
</select>
<select id="findResultsMap" resultType="employee">
    select * from employee;
</select>

测试:

Map<String, Object> resultMap = mapper.findResultMapById(4);
System.out.println(resultMap);
// {gender=男, last_name=TestInsert, id=4, email=TestInsert@misiai.com}
Map<String, Object> resultMaps = mapper.findResultsMap();
//{1=Employee{id=1, lastName='Misiai', email='admin@misiai.com', gender='男'}, 
// 4=Employee{id=4, lastName='TestInsert', email='TestInsert@misiai.com', gender='男'}}
System.out.println(resultMaps);

1.3. ResultMap

简言之就是自定义返回结果有哪些类型,实现高级结果集映射。

代码:

接口:

public Employee findById(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.misiai.dao.EmployeePlusMapper">

    <!--
    id:方便引用,自定义
    type:被定义的javaBean
    -->
    <resultMap id="employeeResult" type="com.misiai.bean.Employee">
        <!--
        <id />标签,专门用来定义主键
        column:数据库对应的列名
        property:javaBean中的属性名
        -->
        <id column="id" property="id"/>
        <!--
            <result/> 标签,定义其他普通列。主键列也可用result定义,但用<id>定义更好。
        -->
        <result column="last_name" property="lastName"/>
        <!--
        其他列不指定,则默认自动封装,但既然都使用resultMap,那么建议都写上。
        -->
        <result column="email" property="email"/>
        <result column="gender" property="gender"/>
    </resultMap>

    <select id="findById" resultMap="employeeResult">
        select *
        from employee
        where id = #{id};
    </select>

</mapper>

Tips:具体请看注释!

测试:

@Test
public void test06() throws IOException {
    SqlSession sqlSession = getSqlSession();
    EmployeePlusMapper mapper = sqlSession.getMapper(EmployeePlusMapper.class);

    Employee byId = mapper.findById(4);
    System.out.println("byId = " + byId);

    sqlSession.commit();//记得提交
}

结果:

截图-1580814288


以上只是最简单的应用,更复杂应用:

1.3.1. 场景1:查询关联属性

查询Employee的同时查询员工对应的部门 Employee===Department

Department.java

package com.misiai.bean;

public class Department {
    private Integer id;
    private String departmentName;


    // ...Contructor
    // ...getter and setter...
}

新的SQL:

-- ----------------------------
-- Table structure for department
-- ----------------------------
DROP TABLE IF EXISTS `department`;
CREATE TABLE `department`  (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `dept_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for employee
-- ----------------------------
DROP TABLE IF EXISTS `employee`;
CREATE TABLE `employee`  (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `last_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `email` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `gender` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `dept_id` int(10) UNSIGNED NOT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `empl_dept`(`dept_id`) USING BTREE,
  CONSTRAINT `empl_dept` FOREIGN KEY (`dept_id`) REFERENCES `department` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;

配置:

<resultMap id="EmpAndDept" type="com.misiai.bean.Employee">
    <id column="id" property="id"/>
    <result column="email" property="email"/>
    <result column="gender" property="gender"/>
    <result column="last_name" property="lastName"/>
    <!--
    department.id中的department是在bean类定义的属性名称。
    -->
    <result column="did" property="department.id"/>
    <result column="dept_name" property="department.departmentName"/>
</resultMap>

<select id="findEmpAndDept" resultMap="EmpAndDept">
    SELECT e.id        id,
           e.email     email,
           e.gender    gender,
           e.last_name last_name,
           d.id        did,
           d.dept_name dept_name
    FROM employee e,
         department d
    WHERE e.dept_id = d.id
      AND e.id = 5
</select>

association

上列中级联属性的写法是:

<result column="did" property="department.id"/>
<result column="dept_name" property="department.departmentName"/>

但是还有其他写法:

<resultMap id="EmpAndDept" type="com.misiai.bean.Employee">
    <id column="id" property="id"/>
    <result column="email" property="email"/>
    <result column="gender" property="gender"/>
    <result column="last_name" property="lastName"/>
    <!--
    department.id中的department是在bean类定义的属性名称。
    -->
    <!--<result column="did" property="department.id"/>-->
    <!--<result column="dept_name" property="department.departmentName"/>-->
    <!--上面两列等效于:-->
    <association property="department" javaType="com.misiai.bean.Department">
        <id column="did" property="id"/>
        <result column="dept_name" property="departmentName"/>
    </association>
</resultMap>

association分步查询:

分步查询就是把一个复杂SQL语句拆分成多个简单的语句。

代码:

  1. departmet

    mapper:

    <?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.misiai.dao.DepartmentMapper">
        <select id="findById" resultType="com.misiai.bean.Department">
            select id, dept_name departmentName from department where id=#{id};
        </select>
    </mapper>

    接口:

    package com.misiai.dao;
    
    import com.misiai.bean.Department;
    
    public interface DepartmentMapper {
        public Department findById(Integer id);
    }
    
  2. Employee

    mapper:

      <!--
    EmpAndDeptStep
    使用association进行分步查询:
    1、先按照员工id查询员工信息
    2、根据查询员工信息中的d_id值去部门表查出部门信息
    3、部门设置到员工中;
    -->
      <resultMap id="EmpAndDeptStep" type="com.misiai.bean.Employee">
          <id column="id" property="id"/>
          <result column="email" property="email"/>
          <result column="gender" property="gender"/>
          <result column="last_name" property="lastName"/>
          <!-- column是要将那列的值传递过去(select要执行的语句中) -->
          <association column="dept_id" property="department" select="com.misiai.dao.DepartmentMapper.findById" >
              <!--select:表明当前属性是调用select指定的方法查出的结果-->
              <id column="dept_id" property="id"/>
              <result column="dept_name" property="departmentName"/>
          </association>
      </resultMap>
      <select id="findEmpAndDeptStep" resultMap="EmpAndDeptStep">
          select * from employee where id=#{id}
    
      </select>

    接口:

    public Employee findEmpAndDeptStep(Integer id);
  3. 测试代码

    @Test
    public void test06() throws IOException {
        SqlSession sqlSession = getSqlSession();
        EmployeePlusMapper mapper = sqlSession.getMapper(EmployeePlusMapper.class);
    
        // Employee byId = mapper.findById(4);
        // System.out.println("byId = " + byId);
    
        // 复杂的resultMap
        // Employee empAndDept = mapper.findEmpAndDept(5);
        // System.out.println("empAndDept = " + empAndDept);
    
        Employee empAndDeptStep = mapper.findEmpAndDeptStep(6);
        System.out.println("empAndDeptStep = " + empAndDeptStep);
        System.out.println(empAndDeptStep.getDepartment());
        sqlSession.commit();//记得提交
    }
1.3.2. 场景2

根据部门信息查询旗下所有员工(列表collection)

collection查询

联合查询,如果用left join 那么SQL语句是:

SELECT
    d.id did,
    d.dept_name departmentName,
    e.email email,
    e.gender gender,
    e.last_name lastName 
FROM
    department d
    LEFT JOIN employee e ON d.id = e.dept_id 
WHERE
    d.id = 1;

mapper/DepartmentMapper.xml

<resultMap id="mapFindEmp" type="com.misiai.bean.Department">
    <id column="did" property="id"/>
    <result column="departmentName" property="departmentName"/>
    <collection property="employees" ofType="Employee">
        <id column="id" property="id"/>
        <result column="email" property="email"/>
        <result column="gender" property="gender"/>
        <result column="lastName" property="lastName"/>
    </collection>
</resultMap>
<select id="findEmpsById" resultMap="mapFindEmp">
    SELECT d.id        did,
           d.dept_name departmentName,
           e.id        id,
           e.email     email,
           e.gender    gender,
           e.last_name lastName
    FROM department d
             LEFT JOIN employee e ON d.id = e.dept_id
    WHERE d.id = #{id};
</select>

com.misiai.dao.DepartmentMapper

List<Employee> findEmpsById(Integer id);

测试:

@Test
public void test08() throws IOException {
    SqlSession sqlSession = getSqlSession();
    DepartmentMapper mapper = sqlSession.getMapper(DepartmentMapper.class);
    List<Employee> empsById = mapper.findEmpsById(1);
    System.out.println("empsById = " + empsById);


    sqlSession.commit();
}

collection分步查询

代码:

  1. Employee

    com.misiai.dao.EmployeePlusMapper

    
     List<Employee> findEmployeesByDeptId(Integer id);

    该方法是定义在EmployeePlusMapper中的,根据部门id,返回所有在此部门的员工。

    对应的mapper.xml

     <!--List<Employee> findEmployeesByDeptId(Integer id);-->
     <select id="findEmployeesByDeptId" resultType="employee">
         select *
         from employee where dept_id=#{id};
     </select>
  2. Department

    接口:

     Department findEmployeesByIdStep(Integer id);

    mapper:

     <resultMap id="mapFindEmployeesStep" type="com.misiai.bean.Department">
         <id column="id" property="id"/>
         <result column="dept_name" property="departmentName"/>
    
         <!-- column是要将那列的值传递过去(select要执行的语句中) -->
         <collection property="employees" column="id"  select="com.misiai.dao.EmployeePlusMapper.findEmployeesByDeptId">
    
         </collection>
     </resultMap>
         <select id="findEmployeesByIdStep" resultMap="mapFindEmployeesStep">
             select *
             from department where id=#{id};
         </select>

    测试代码:

     @Test
     public void test08() throws IOException {
         SqlSession sqlSession = getSqlSession();
         DepartmentMapper mapper = sqlSession.getMapper(DepartmentMapper.class);
         // List<Employee> empsById = mapper.findEmpsById(1);
         // System.out.println("empsById = " + empsById);
    
         Department employeesByIdStep = mapper.findEmployeesByIdStep(1);
         System.out.println("employeesByIdStep = " + employeesByIdStep);
    
         sqlSession.commit();
     }

    结果:

     Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@6f45df59]
     ==>  Preparing: select * from department where id=?; 
     ==> Parameters: 1(Integer)
     <==    Columns: id, dept_name
     <==        Row: 1, 测试部
     ====>  Preparing: select * from employee where dept_id=?; 
     ====> Parameters: 1(Long)
     <====    Columns: id, last_name, email, gender, dept_id
     <====        Row: 5, Tom, admin1@misiai.com, 男, 1
     <====      Total: 1
     <==      Total: 1
    

    可以看出,是执行了两条语句。

1.4. 补充:鉴别器

discriminator是定义在resultMap里面的,一种分情况执行不同操作。

了解,这里不多说。

<resultMap id="mapFindEmployeesStep" type="com.misiai.bean.Department">
    <discriminator javaType="">
        <case value=""></case>
    </discriminator>
    <id column="id" property="id"/>
    <result column="dept_name" property="departmentName"/>

    <collection property="employees" column="id"  select="com.misiai.dao.EmployeePlusMapper.findEmployeesByDeptId">

    </collection>
</resultMap>
本节阅读完毕!
二维码图片 扫描关注我们哟