- 工程的目录结构
- mybatis配置
- 用来测试的表结构
- mapper.xml和mapper类的实现
- 自动生成器实现
说明请查看代码中的注解
1和2完成Spring Boot集成Mybatis
1. 工程的目录结构
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 执行自动生成器