Mybatis的select - 教程|迷思爱学习乐园|兴趣是最好的老师

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语句拆分成多个简单的语句。

代码：

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);
}

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);

测试代码

@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分步查询

代码：

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>

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>

本节阅读完毕!

Mybatis的参数处理

Mybatis的动态SQL

他人讨论(0篇)

我要讨论