Spring Boot: 集成Mybatis 返回自增主键, 存储过程调用, 传递多参数,where set trim foreach用法 association和collection嵌套查询 集合查

  1. 工程的目录结构
  2. mybatis配置
  3. 用来测试的表结构
  4. mapper.xml和mapper类的实现
  5. 自动生成器实现

说明请查看代码中的注解

1和2完成Spring Boot集成Mybatis

1. 工程的目录结构
Spring Boot: 集成Mybatis 返回自增主键, 存储过程调用, 传递多参数,where set trim foreach用法 association和collection嵌套查询 集合查

2. mybatis配置
** 2.1 添加依赖 - Spring boot 版本使用的是2.6.2**

相关依赖 点击查看
<dependencies>
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.2.1</version>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.mybatis.generator</groupId>
            <artifactId>mybatis-generator-core</artifactId>
            <version>1.3.3</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <scope>test</scope>
        </dependency>
    </dependencies>

** 2.2 配置application.properties**
配置mysql数据连接和扫描包路径

点击查看代码
mybatis.config-location=classpath:mybatis/mybatis-config.xml
mybatis.mapper-locations=classpath:mybatis/mapper/*.xml
mybatis.type-aliases-package=com.yq.mybatis.model

spring.datasource.url=jdbc:mysql://localhost:3306/employees?useUnicode=true&characterEncoding=utf-8
spring.datasource.username=root
spring.datasource.password=123456
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver

2.3 mybatis-config.xml文件只配置了别名和用于嵌套查询的延迟加载项

点击查看代码
<?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?,settings?,typeAliases?,typeHandlers?,objectFactory?,
		objectWrapperFactory?,reflectorFactory?,plugins?,
		environments?,databaseIdProvider?,mappers?
	-->
	<settings>
		<!--将first_name自动匹配java中的驼峰式命名firstName-->
		<setting name="mapUnderscoreToCamelCase" value="true"/>
		<!--将lazyLoadingEnabled设置为true表示开启延迟加载,默认为false. 用来设置全局使用延迟加载-->
		<setting name="lazyLoadingEnabled" value="true"/>
		<!--将aggressiveLazyLoading设置为false表示按需加载,默认为true-->
		<setting name="aggressiveLazyLoading" value="false"/>
	</settings>
	<typeAliases>
		<typeAlias alias="Integer" type="java.lang.Integer" />
		<typeAlias alias="Long" type="java.lang.Long" />
		<typeAlias alias="HashMap" type="java.util.HashMap" />
		<typeAlias alias="LinkedHashMap" type="java.util.LinkedHashMap" />
		<typeAlias alias="ArrayList" type="java.util.ArrayList" />
		<typeAlias alias="LinkedList" type="java.util.LinkedList" />
	</typeAliases>
</configuration>

2.4 配置扫描Mapper接口包路径,也可不像下面方式而是在每个Mapper接口上方添加@Mapper注解

@SpringBootApplication
@MapperScan("com.yq.mybatis.mapper")
public class MybatisApplication {

public static void main(String[] args) {
    SpringApplication.run(MybatisApplication.class, args);
}

}

以上就完成了对Spring Boot集成Mybatis

3. 用来测试的表结构
3.1 employees表和salaries

-- employees表

CREATE TABLE employees (
emp_no int(11) NOT NULL AUTO_INCREMENT,
birth_date date NOT NULL,
first_name varchar(14) NOT NULL,
last_name varchar(16) NOT NULL,
gender enum('M','F') NOT NULL,
hire_date date NOT NULL,
PRIMARY KEY (emp_no),
KEY first_name (first_name)
) ENGINE=InnoDB AUTO_INCREMENT=500008 DEFAULT CHARSET=latin1;

-- salaries表

CREATE TABLE salaries (
emp_no int(11) NOT NULL,
salary int(11) NOT NULL,
from_date date NOT NULL,
to_date date NOT NULL,
PRIMARY KEY (emp_no,from_date)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

** 3.2 存储过程**

-- select_employee_by_id存储过程

DELIMITER $$
CREATE PROCEDURE select_employee_by_id(in empNo int,
out firstName varchar(14),
out lastName varchar(16),
out birthDate date
)
begin
select emp_no,first_name,last_name,birth_date
into empNo,firstName,lastName,birthDate
from employees
where emp_no=empNo;

end$$
DELIMITER ;

-- select_employee_page存储过程

DELIMITER $$
CREATE PROCEDURE select_employee_page(
in firstName varchar(14),
in _offset int,
in _limit int,
out total int
)
begin
select count(1) into total from employees where first_name like concat('%',firstName,'%');
select * from employees where first_name like concat('%',firstName,'%') limit _offset,_limit;
end$$
DELIMITER ;

4. mapper.xml和mapper类的实现

** 4.1 mapper.xml看代码里面的注释**

点击查看EmployeesMapper.xml代码
<?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.yq.mybatis.mapper.EmployeesMapper" >
    <resultMap id="BaseResultMap" type="com.yq.mybatis.model.Employee">
        <id column="emp_no" property="empNo" jdbcType="BIGINT" />
        <result column="birth_date" property="birthDate" jdbcType="VARCHAR" />
        <result column="first_name" property="firstName" jdbcType="VARCHAR" />
        <result column="last_name" property="lastName" jdbcType="VARCHAR" />
        <result column="gender" property="gender" jdbcType="VARCHAR"/>
        <result column="hire_date" property="hireDate" jdbcType="VARCHAR" />
    </resultMap>

    <sql id="Base_Column_List" >
        emp_no,birth_date,first_name,last_name,gender,hire_date
    </sql>

    <sql id="Base_Where_List">
        <if test="firstName != null  and firstName != ''">
            and first_name = #{firstName}
        </if>
        <if test="gender != null and gender != ''">
            and gender = #{gender}
        </if>
    </sql>

    <select id="getAll" resultMap="BaseResultMap"  >
        SELECT
        <include refid="Base_Column_List" />
        FROM employees limit 10
    </select>

    <!--  使用单个变量作为参数 -->
    <select id="getByEmpNo" resultType="com.yq.mybatis.model.Employee" parameterType="Long">
        SELECT * FROM employees where emp_no=#{empNo,jdbcType=BIGINT}
    </select>

    <!--  使用两个字符串类型作为参数 like-->
    <select id="getByName" resultType="com.yq.mybatis.model.Employee">
        SELECT * FROM employees
        where first_name like concat('%',#{firstName},'%') and last_name=#{lastName}
    </select>

    <!--  使用两个javaBean作为参数 -->
    <select id="getByNameAndSalary" resultType="com.yq.mybatis.model.Employee">
        SELECT A.* FROM employees A join salaries B on A.emp_no=B.emp_no
         where A.first_name=#{employee.firstName} and A.last_name=#{employee.lastName} and
         B.salary=#{salary.salary}
    </select>

    <!--  #{hireDate,jdbcType=DATE} 增加类型会对类型不匹配的抛出异常 -->
    <insert id="insert" parameterType="com.yq.mybatis.model.Employee" >
       INSERT INTO employees (<include refid="Base_Column_List" />)
       VALUES (#{empNo}, #{birthDate,jdbcType=DATE}, #{firstName}, #{lastName}, #{gender}, #{hireDate,jdbcType=DATE})
    </insert>

    <!--  insert返回主键: emp_no在mysql中被设置为自增, 使用useGeneratedKeys="true" keyProperty="empNo"将自增后的emp_no赋值给empNo -->
    <!--  返回多个自增keyProperty用逗号分隔即可 -->
    <insert id="insertReturnEmpNo" parameterType="com.yq.mybatis.model.Employee" useGeneratedKeys="true" keyProperty="empNo">
        INSERT INTO employees (birth_date,first_name,last_name,gender,hire_date)
        VALUES (#{birthDate,jdbcType=DATE}, #{firstName}, #{lastName}, #{gender}, #{hireDate,jdbcType=DATE})
    </insert>

    <!--  insert返回主键: 将查询到的500006作为主键执行insert, 添加成功后将其赋值给Employee的empNo属性 -->
    <!--  select 500006可以使用其他的查询语句, order值如果连接的使mysql值为AFTER如果使oracle此值为BEFORE -->
    <!--  parameterType属性也可以省略,会自动匹配 -->
    <insert id="insertSelectKey" >
        INSERT INTO employees (birth_date,first_name,last_name,gender,hire_date)
        VALUES (#{birthDate,jdbcType=DATE}, #{firstName}, #{lastName}, #{gender}, #{hireDate,jdbcType=DATE})
        <selectKey keyColumn="emp_no" keyProperty="empNo" order="AFTER" resultType="Integer">
            select 500007
        </selectKey>
    </insert>

    <!-- if标签 判断条件 !=null / ==null使用任何类型 !='' / ==''适用于String 多条件用and和or 嵌套可使用小括号 -->
    <!-- 使用set标签 会去掉开始和结尾的,号 -->
    <update id="update" parameterType="com.yq.mybatis.model.Employee" >
        UPDATE employees
        <set>
            <if test="firstName != null and firstName!='' ">first_name = #{firstName},</if>
            <if test="lastName != null">last_name = #{lastName},</if>
        </set>
        WHERE emp_no = #{empNo}
    </update>

    <!-- choose when otherwise标签 -->
    <select id="getByCondition" resultType="com.yq.mybatis.model.Employee">
        SELECT * FROM employees
        where 1=1
        <choose>
            <when test="firstName!=null and firstName!=''">
                and first_name like concat('%',#{firstName},'%')
            </when>
            <when test="lastName!=null and lastName!=''">
                and last_name=#{lastName}
            </when>
            <otherwise>
                and emp_no=500007
            </otherwise>
        </choose>
    </select>

    <!--  where标签,会自动去掉开始和结尾的and/or, 如果if标签都不符合条件则查询会忽略where条件-->
    <select id="getUseWhere" resultType="com.yq.mybatis.model.Employee">
        SELECT * FROM employees
        <where>
            <if test="firstName != null and firstName!='' "> and first_name = #{firstName}</if>
            <if test="lastName != null"> and last_name = #{lastName}</if>
        </where>
        order by emp_no desc limit 10
    </select>

    <!-- trim标签,trim内有内容时prefix增加指定前缀,suffix增加指定后缀,prefixOverrides去除指定前缀,suffixOverrides去除指定后缀-->
    <!-- 此处prefixOverrides会同时去掉and和or前缀 中间用|分隔 -->
    <select id="getUseTrim" resultType="com.yq.mybatis.model.Employee">
        SELECT * FROM employees
        <trim prefix="where" prefixOverrides="and|or">
            <if test="firstName != null and firstName!='' "> and first_name = #{firstName}</if>
            <if test="lastName!= null"> or last_name = #{lastName}</if>
        </trim>
        order by emp_no desc limit 10
    </select>
    <!--  foreach标签:List<String> -->
    <!--  collection:是集合名;item:是集合迭代的元素值,如果元素是字符串和整数等可直接如下使用,如元素是对像可item.属性名方式-->
    <!--  index:集合迭代的当前索引; open:整个循环内容的开头字符串, close:整个循环内容的结尾字符串,separator:循环内字符分隔符-->
    <!--  下面会封装成:(1,2,3)-->
    <select id="getUseForeachListString" resultType="com.yq.mybatis.model.Employee" >
        SELECT * FROM employees
        <where>
            <if test="list!=null and list.size gt 0">
                first_name in
                <foreach collection="list" open="(" close=")" separator="," item="empNo" index="i">
                    #{empNo}
                </foreach>
            </if>
        </where>
        order by emp_no desc limit 10
    </select>
    <!--  foreach标签: List<Object>-->
    <!--  foreach会自动将List集合识别为collection="list" 可以不用在Mapper中使用注解@param("list") -->
    <!--  我们使用注解@param是因为下面的if标签 -->
    <select id="getUseForeachListObject" resultType="com.yq.mybatis.model.Employee" >
        SELECT * FROM employees
        <where>
            <if test="list!=null and list.size gt 0">
                first_name in
                <foreach collection="list" open="(" close=")" separator="," item="item" index="i">
                    #{item.firstName}
                </foreach>
            </if>
        </where>
        order by emp_no desc limit 10
    </select>
    <!--  foreach标签: Map<k,V>  在Mapper中需要使用@param("map")注解,map可以随便写-->
    <!--  下面的index和item分别是map的k,V-->
    <!--  下面用到了${key}而不是#{key}, ${key}会直接将字符串拼接到sql语句中, #{key}会使用给传入的数据两边加上引号-->
    <select id="getUseForeachMap" resultType="com.yq.mybatis.model.Employee" >
        SELECT * FROM employees
        <where>
            <foreach collection="map"  item="value" index="key">
                and ${key}= #{value}
            </foreach>
        </where>
        order by emp_no desc limit 10
    </select>

    <!--  bind标签: 可以将mysql中的concat用bind替换 如下-->
    <select id="getUseBind" resultType="com.yq.mybatis.model.Employee" >
        SELECT * FROM employees
        <where>
            <if test="firstName!=null and firstName!=''">
                <bind name="firstNameLike" value="'%'+firstName+'%'" />
                first_name like #{firstNameLike}
                <!--  first_name like concat('%',#{firstName},'%')-->
            </if>
        </where>
        order by emp_no desc limit 10
    </select>

    <delete id="delete" parameterType="Long" >
       DELETE FROM employees WHERE emp_no = #{empNo}
    </delete>

    <!--  association标签: 嵌套查询-->
    <!--  property: 是Employee对象的salaries属性,类型是List<Salary> -->
    <!--  column: 嵌套查询时使用,salaryEmpNo是子查询使用的参数名,emp_no是当前查询的一个sql语句中的列名,将查询到的emp_no赋值给salaryEmpNo供子查询使用-->
    <!--  select: 子查询com.yq.mybatis.mapper.SalaryMapper是SalaryMapper.xml文件中mapper的namespace值,selectSalaryByEmpNo是SalaryMapper.xml中的一个select -->
    <!--  fetchType: 有两个可选值lazy->延迟加载 eager->紧急加载, 设置延迟加载后查询结果的对象会多出一个handler属性JavassistProxyFactory -->
    <!--  使用延迟加载还需要配置下面两个在mybatis-config.xml文件中 -->
    <!--  <setting name="aggressiveLazyLoading" value="false"/>-->
    <!--  com.yq.mybatis.mapper.SalaryMapper.selectSalaryByEmpNo可以不必在Mapper接口中实现 -->
    <resultMap id="EmployeeAssociation" type="com.yq.mybatis.model.Employee" extends="BaseResultMap">
        <association property="salaries" column="{salaryEmpNo=emp_no}" fetchType="eager" select="com.yq.mybatis.mapper.SalaryMapper.selectSalaryByEmpNo">
        </association>
    </resultMap>
    <select id="selectEmpUseAssociation" resultMap="EmployeeAssociation" >
        SELECT * FROM employees
        order by emp_no limit 20
    </select>


    <!--  collection: 集合的嵌套查询-->
    <!--  下面的例子会将employee重复行合并,将epmloyee对象中salaries自动合并到list<Salary>集合中 -->
    <!--  查询到的结果会小于等于总行数,如果resultMap中有配置Id则只要Id值相同就认为是相同的数据行,否则会比较所有的属性值都相等认为是相同数据 -->
    <!--  下面例子中把com.yq.mybatis.mapper.SalaryMapper.BaseResultMap里面的id改成了result否则只能查询到一行Salary -->
    <resultMap id="EmployeeCollection" type="com.yq.mybatis.model.Employee" extends="BaseResultMap">
        <collection property="salaries" resultMap="com.yq.mybatis.mapper.SalaryMapper.BaseResultMap" columnPrefix="s_">
        </collection>
    </resultMap>
    <select id="selectEmpUseCollection" resultMap="EmployeeCollection" >
        SELECT A.*,B.emp_no s_emp_no,B.salary s_salary,B.from_date s_from_date,B.to_date s_to_date
        FROM employees A inner join salaries B on A.emp_no=B.emp_no
        order by A.emp_no limit 20
    </select>

    <!--  collection: 集合的嵌套查询 -> 子查询与association的配置基本相同 -->
    <resultMap id="EmployeeCollectionSubSelected" type="com.yq.mybatis.model.Employee" extends="BaseResultMap">
        <collection property="salaries" fetchType="eager" column="{salaryEmpNo=emp_no}" select="com.yq.mybatis.mapper.SalaryMapper.selectSalaryByEmpNo">
        </collection>
    </resultMap>
    <select id="selectEmpUseCollectionSubSelected" resultMap="EmployeeCollectionSubSelected" >
        SELECT * FROM employees order by emp_no limit 20
    </select>

    <!--  存储过程: 存储过程不支持mybatis二级缓存,所以将useCache设置为false -->
    <!--  OUT模式参数必须指定jdbcType, out参数会赋值给Employee对象,当然使用Map也可以 -->
    <select id="selectEmployeeById" statementType="CALLABLE" useCache="false">
        {call select_employee_by_id(
        #{empNo,mode=IN},#{firstName,mode=OUT,jdbcType=VARCHAR},
        #{lastName,mode=OUT,jdbcType=VARCHAR},#{birthDate,mode=OUT,jdbcType=VARCHAR})}
    </select>

    <!--  存储过程: 实现翻页,将查询结果赋值给List<Employee>, 将数据总行数赋值给出参total -->
    <select id="selectEmployeePage" statementType="CALLABLE" useCache="false" resultMap="BaseResultMap">
        {call select_employee_page(
        #{firstName,mode=IN},
        #{_offset,mode=IN},#{_limit,mode=IN},
        #{total,mode=OUT,jdbcType=INTEGER})}
    </select>

</mapper>
点击查看SalaryMapper.xml代码
<?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.yq.mybatis.mapper.SalaryMapper" >
    <resultMap id="BaseResultMap" type="com.yq.mybatis.model.Salary">
        <result column="emp_no" property="empNo" jdbcType="BIGINT" />
        <result column="salary" property="salary" jdbcType="INTEGER" />
        <result column="from_date" property="fromDate" jdbcType="VARCHAR" />
        <result column="to_date" property="toDate" jdbcType="VARCHAR" />
    </resultMap>

    <!-- 使用别名as "employee.lastName"将返回值赋值给Salary对象中employee属性值中的lastName  -->
    <select id="selectByEmpNo" resultType="com.yq.mybatis.model.Salary" parameterType="Long">
        SELECT A.emp_no,A.salary,A.from_date,A.to_date,
        B.first_name as "employee.firstName",
        B.last_name as "employee.lastName"
        FROM salaries A join employees B on A.emp_no=B.emp_no
        where A.emp_no=#{empNo}
    </select>

    <select id="selectSalaryByEmpNo" resultMap="BaseResultMap" >
        SELECT * FROM salaries where emp_no=#{salaryEmpNo}
    </select>
</mapper>
点击查看EmployeesMapper代码
package com.yq.mybatis.mapper;

import com.yq.mybatis.model.Employee;
import com.yq.mybatis.model.Salary;
import org.apache.ibatis.annotations.Param;

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

public interface EmployeesMapper {

    List<Employee> getAll();
    Employee getByEmpNo(Long empNo);
    List<Employee> getByName(@Param("firstName")String firstName,@Param("lastName")String lastName);
    List<Employee> getByNameAndSalary(@Param("employee")Employee employee, @Param("salary")Salary salary);
    List<Employee> getByCondition(Employee employee);
    List<Employee> getUseWhere(@Param("firstName")String firstName,@Param("lastName")String lastName);
    List<Employee> getUseTrim(@Param("firstName")String firstName,@Param("lastName")String lastName);
    List<Employee> getUseForeachListString(@Param("list")List<String> list);
    List<Employee> getUseForeachListObject(@Param("list")List<Employee> list);
    List<Employee> getUseForeachMap(@Param("map")Map<String,String> map);
    List<Employee> getUseBind(@Param("firstName")String firstName);

    List<Employee> selectEmpUseAssociation();
    List<Employee> selectEmpUseCollection();
    List<Employee> selectEmpUseCollectionSubSelected();

    void selectEmployeeById(Employee employee);

    List<Employee> selectEmployeePage(Map<String,Object> map);


    int insert(Employee employee);
    int update(Employee employee);
    int delete(Long empNo);
    int insertReturnEmpNo(Employee employee);
    int insertSelectKey(Employee employee);
}

点击查看SalaryMapper代码
package com.yq.mybatis.mapper;

import com.yq.mybatis.model.Salary;

import java.util.List;

public interface SalaryMapper {

   List<Salary> selectByEmpNo(Long empNo);
}

4.2 相关实体类如下

点击查看BaseResult代码
package com.yq.mybatis.model;

public class BaseResult<T> {
    private static final int SUCCESS_CODE = 200;
    private static final String SUCCESS_MESSAGE = "成功";

    private int code;

    private String msg;

    private T data;

    private BaseResult(int code, String msg, T data) {
        this.code = code;
        this.msg = msg;
        this.data = data;
    }

    private BaseResult() {
        this(SUCCESS_CODE, SUCCESS_MESSAGE);
    }

    private BaseResult(int code, String msg) {
        this(code, msg, null);
    }

    private BaseResult(T data) {
        this(SUCCESS_CODE, SUCCESS_MESSAGE, data);
    }

    public static <T> BaseResult<T> success() {
        return new BaseResult<>();
    }

    public static <T> BaseResult<T> successWithData(T data) {
        return new BaseResult<>(data);
    }

    public static <T> BaseResult<T> failWithCodeAndMsg(int code, String msg) {
        return new BaseResult<>(code, msg, null);
    }

    public static <T> BaseResult<T> buildWithParam(ResponseParam param) {
        return new BaseResult<>(param.getCode(), param.getMsg(), null);
    }

    public int getCode() {
        return code;
    }

    public void setCode(int code) {
        this.code = code;
    }

    public String getMsg() {
        return msg;
    }

    public void setMsg(String msg) {
        this.msg = msg;
    }

    public T getData() {
        return data;
    }

    public void setData(T data) {
        this.data = data;
    }



    public static class ResponseParam {
        private int code;
        private String msg;

        private ResponseParam(int code, String msg) {
            this.code = code;
            this.msg = msg;
        }

        public static ResponseParam buildParam(int code, String msg) {
            return new ResponseParam(code, msg);
        }

        public int getCode() {
            return code;
        }

        public void setCode(int code) {
            this.code = code;
        }

        public String getMsg() {
            return msg;
        }

        public void setMsg(String msg) {
            this.msg = msg;
        }
    }
}

点击查看Employee 代码
package com.yq.mybatis.model;

import java.util.List;

public class Employee {

    private int empNo;
    private String birthDate;
    private String firstName;
    private String lastName;
    private String gender;
    private String hireDate;
    private List<Salary> salaries;

    public List<Salary> getSalaries() {
        return salaries;
    }

    public void setSalaries(List<Salary> salaries) {
        this.salaries = salaries;
    }

    public int getEmpNo() {
        return empNo;
    }

    public void setEmpNo(int empNo) {
        this.empNo = empNo;
    }

    public String getBirthDate() {
        return birthDate;
    }

    public void setBirthDate(String birthDate) {
        this.birthDate = birthDate;
    }

    public String getFirstName() {
        return firstName;
    }

    public void setFirstName(String firstName) {
        this.firstName = firstName;
    }

    public String getLastName() {
        return lastName;
    }

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

    public String getGender() {
        return gender;
    }

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

    public String getHireDate() {
        return hireDate;
    }

    public void setHireDate(String hireDate) {
        this.hireDate = hireDate;
    }
}

点击查看Salary 代码
package com.yq.mybatis.model;

public class Salary {
    private int empNo;
    private int salary;
    private String fromDate;
    private String toDate;
    private Employee employee;

    public int getEmpNo() {
        return empNo;
    }

    public void setEmpNo(int empNo) {
        this.empNo = empNo;
    }

    public int getSalary() {
        return salary;
    }

    public void setSalary(int salary) {
        this.salary = salary;
    }

    public String getFromDate() {
        return fromDate;
    }

    public void setFromDate(String fromDate) {
        this.fromDate = fromDate;
    }

    public String getToDate() {
        return toDate;
    }

    public void setToDate(String toDate) {
        this.toDate = toDate;
    }

    public Employee getEmployee() {
        return employee;
    }

    public void setEmployee(Employee employee) {
        this.employee = employee;
    }
}

4.3 controller类代码用于访问测试

点击查看代码
package com.yq.mybatis.controller;

import com.yq.mybatis.mapper.EmployeesMapper;
import com.yq.mybatis.mapper.SalaryMapper;
import com.yq.mybatis.model.BaseResult;
import com.yq.mybatis.model.Employee;
import com.yq.mybatis.model.Salary;
import org.springframework.web.bind.annotation.*;

import javax.annotation.Resource;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@RestController
@RequestMapping("/")
public class EmployeeController {

    @Resource
    private EmployeesMapper mapper;

    @Resource
    private SalaryMapper salaryMapper;

    @GetMapping("getAll")
    public BaseResult<List<Employee>> getAll(){
        List<Employee> list=mapper.getAll();
        return BaseResult.successWithData(list);
    }

    @GetMapping("getById")
    public BaseResult<Employee> getById(Long empNo){
        return BaseResult.successWithData(mapper.getByEmpNo(empNo));
    }

    @GetMapping("getSalaryById")
    public BaseResult<List<Salary>> getSalaryById(Long empNo){
        return BaseResult.successWithData(salaryMapper.selectByEmpNo(empNo));
    }

    @GetMapping("getByName")
    public BaseResult<List<Employee>> getByName(String firstName,String lastName){
        return BaseResult.successWithData(mapper.getByName(firstName,lastName));
    }

    @GetMapping("getByNameAndSalary")
    public BaseResult<List<Employee>> getByNameAndSalary(String firstName,String lastName,int salary){
        Employee employee=new Employee();
        employee.setFirstName(firstName);
        employee.setLastName(lastName);
        Salary salary1=new Salary();
        salary1.setSalary(salary);
        return BaseResult.successWithData(mapper.getByNameAndSalary(employee,salary1));
    }

    @GetMapping("getByCondition")
    public BaseResult<List<Employee>> getByCondition(String firstName,String lastName){
        Employee employee=new Employee();
        employee.setFirstName(firstName);
        employee.setLastName(lastName);
        return BaseResult.successWithData(mapper.getByCondition(employee));
    }

    @GetMapping("getUseWhere")
    public BaseResult<List<Employee>> getUseWhere(String firstName,String lastName){

        return BaseResult.successWithData(mapper.getUseWhere(firstName,lastName));
    }

    @GetMapping("getUseTrim")
    public BaseResult<List<Employee>> getUseTrim(String firstName,String lastName){
        return BaseResult.successWithData(mapper.getUseTrim(firstName,lastName));
    }

    @GetMapping("getUseForeachListString")
    public BaseResult<List<Employee>> getUseForeachListString(String firstName,String lastName){
        List<String> list=new ArrayList<>();
//        list.add("Jack");
//        list.add("Sachin");
        return BaseResult.successWithData(mapper.getUseForeachListString(list));
    }
    @GetMapping("getUseForeachListObject")
    public BaseResult<List<Employee>> getUseForeachListObject(String firstName,String lastName){
        List<Employee> list=new ArrayList<>();
        Employee emp=new Employee();
        emp.setFirstName("Jack");

        Employee emp2=new Employee();
        emp2.setFirstName("Sachin");

        list.add(emp);
        list.add(emp2);
        return BaseResult.successWithData(mapper.getUseForeachListObject(list));
    }
    @GetMapping("getUseForeachMap")
    public BaseResult<List<Employee>> getUseForeachMap(String firstName,String lastName){
        Map<String,String> map=new HashMap<>();
        map.put("first_name","Jack");
        map.put("last_name","Yu");
        return BaseResult.successWithData(mapper.getUseForeachMap(map));
    }
    @GetMapping("getUseBind")
    public BaseResult<List<Employee>> getUseBind(String firstName){
        return BaseResult.successWithData(mapper.getUseBind(firstName));
    }

    @GetMapping("selectEmpUseAssociation")
    public BaseResult<List<Employee>> selectEmpUseAssociation(){
        List<Employee> list=mapper.selectEmpUseAssociation();
        for(Employee e:list){
            System.out.println(e.getEmpNo());
        }
        return BaseResult.success();
    }

    @GetMapping("selectEmpUseCollection")
    public BaseResult<List<Employee>> selectEmpUseCollection(){
        List<Employee> list=mapper.selectEmpUseCollection();
        return BaseResult.success();
    }

    @GetMapping("selectEmpUseCollectionSubSelected")
    public BaseResult<List<Employee>> selectEmpUseCollectionSubSelected(){
        List<Employee> list=mapper.selectEmpUseCollectionSubSelected();
        return BaseResult.success();
    }

    @GetMapping("selectEmployeeById")
    public BaseResult selectEmployeeById(){
        Employee emp=new Employee();
        emp.setEmpNo(500007);
        mapper.selectEmployeeById(emp);
        System.out.println(emp.getEmpNo()+","+emp.getFirstName()+","+emp.getLastName()+","+emp.getBirthDate());
        return BaseResult.success();
    }

    @GetMapping("selectEmployeePage")
    public BaseResult selectEmployeePage(){
        Map<String,Object> map=new HashMap<>();
        map.put("firstName","Jack");
        map.put("_offset","1");
        map.put("_limit","10");
       // map.put("total","0");
        List<Employee> list=mapper.selectEmployeePage(map);
        System.out.println("total:"+map.get("total"));

        return BaseResult.success();
    }


    @PostMapping("addEmployee")
    public BaseResult addEmployee(@RequestBody Employee employee){
        mapper.insert(employee);
        return BaseResult.success();
    }

    @PostMapping("addEmployeeAuto")
    public BaseResult<String> addEmployeeAuto(@RequestBody Employee employee){
        mapper.insertReturnEmpNo(employee);
        String empNo=String.valueOf(employee.getEmpNo());
        return BaseResult.successWithData(empNo);
    }

    @PostMapping("addEmployeeSelectKey")
    public BaseResult<String> addEmployeeSelectKey(@RequestBody Employee employee){
        mapper.insertSelectKey(employee);
        String empNo=String.valueOf(employee.getEmpNo());
        return BaseResult.successWithData(empNo);
    }

    @PutMapping("updateEmployee")
    public BaseResult updateEmployee(@RequestBody Employee employee){
        mapper.update(employee);
        return BaseResult.success();
    }

    @DeleteMapping("deleteEmployee")
    public BaseResult updateEmployee(Long empNo){
        mapper.delete(empNo);
        return BaseResult.success();
    }
}

5. 自动生成器实现

** 5.1创建generatorConfig.xml**

点击查看代码
<?xml version="1.0" encoding="utf-8" ?>
<!DOCTYPE generatorConfiguration
        PUBLIC "-//mybatis.org//DTD MyBatis Generator Configuration 1.0//EN"
        "http://mybatis.org/dtd/mybatis-generator-config_1_0.dtd">

<generatorConfiguration>
    <!-- context id:必填随便写, targetRuntime:指定生成代码运行环境可选值MyBatis3和MyBatis3Simple -->
    <!-- defaultModelType:可选值
    flat->为每张表生成一个实体类,推荐
    conditional->默认值,如果一张表的主键只有一个字段会将其合并到基本实体类中
    hierarchical->如果表有主键会单独为主键生成实体类
    -->
    <context id="MySqlContext" targetRuntime="MyBatis3" defaultModelType="flat">
        <!-- 分隔符,会给表名和字段加上此分隔符,防止因为表名或字段是关键字或中间有空格在执行sql时产生错误-->
        <!--  autoDelimitKeywords是自动给关机字添加分隔符 />-->
        <!--  <property name="autoDelimitKeywords" value="true" />-->
        <property name="beginningDelimiter" value="`" />
        <property name="endingDelimiter" value="`" />

        <commentGenerator>
            <!-- 禁止生成注释,默认为false, 因生成的注释没任何价值一般会屏蔽注释信息 -->
            <property name="suppressAllComments" value="true"/>
            <!-- 禁止在注释中生成日期 -->
            <!-- <property name="suppressDate" value="true"/>-->
            <!-- 生成数据库的注释信息 -->
            <!-- <property name="addRemarkComments" value="true"/>-->
        </commentGenerator>

        <jdbcConnection driverClass="com.mysql.cj.jdbc.Driver"
                        connectionURL="jdbc:mysql://localhost:3306/employees"
                        userId="root" password="123456">
        </jdbcConnection>
        <!-- 生成model包路径 -->
        <javaModelGenerator targetPackage="com.yq.mybatis.model" targetProject="src\main\java">
            <property name="trimStrings" value="true"/>
        </javaModelGenerator>

        <!-- 生成mapper.xml文件路径 -->
        <sqlMapGenerator targetPackage="mybatis\mapper" targetProject="src\main\resources" />

        <!-- 生成DAO Mapper接口的包路径 -->
        <javaClientGenerator type="XMLMAPPER" targetPackage="com.yq.mybatis.mapper" targetProject="src\main\java" />

        <!-- tableName->数据库表名 如果生成全部表tableName="%"即可,
        domainObjectName->表名对应生成的对象名
        useActualColumnNames-> true时生成实体对象的属性同表中的字段名,false时会尝试将其转为驼峰形式,默认false
        enableXXX:指定是否生成对应的XXX语句
        其他属性:
        schema->数据库名,如设置此值则生成的SQL的表名会变成schema.tableName
        modelType->同context的defaultModelType会将其覆盖
        immutable->同JavaMoelGenerator
        rootClass->同javaModelGenerator
        rootInterface->同javaClientGenerator
        -->
        <table tableName="titles" domainObjectName="Title" enableCountByExample="false" enableSelectByExample="false" enableUpdateByExample="false" enableDeleteByExample="false">
        </table>
    </context>
</generatorConfiguration>

** 5.2 在pom.xml增加插件**

点击查看代码
<build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
            <!-- Mybatis generator代码生成插件 配置 -->
            <plugin>
                <groupId>org.mybatis.generator</groupId>
                <artifactId>mybatis-generator-maven-plugin</artifactId>
                <version>1.3.1</version>
                <configuration>
                    <configurationFile>${basedir}/src/main/resources/mybatis/generatorConfig.xml</configurationFile>
                    <overwrite>true</overwrite>
                    <verbose>true</verbose>
                </configuration>
                <dependencies>
                    <dependency>
                        <groupId>mysql</groupId>
                        <artifactId>mysql-connector-java</artifactId>
                        <version>8.0.27</version>
                    </dependency>
                    <dependency>
                        <groupId>com.yq</groupId>
                        <artifactId>mybatis</artifactId>
                        <version>0.0.1-SNAPSHOT</version>
                    </dependency>
                </dependencies>
            </plugin>
        </plugins>
    </build>

5.3 执行自动生成器

Spring Boot: 集成Mybatis 返回自增主键, 存储过程调用, 传递多参数,where set trim foreach用法 association和collection嵌套查询 集合查

上一篇:[C++][题解]业绩排名


下一篇:JAVA面向对象学习——java面向对象概念———实例化2个对象——this使用理解