Spring Boot: 集成Mybatis 返回自增主键， 存储过程调用, 传递多参数，where set trim foreach bind selectKey choose when other

        
            org.mybatis.spring.boot
            mybatis-spring-boot-starter
            2.2.1
        

        
            mysql
            mysql-connector-java
            runtime
        
        
            org.mybatis.generator
            mybatis-generator-core
            1.3.3
        
        
            org.springframework.boot
            spring-boot-starter-test
            test
        
        
            org.springframework.boot
            spring-boot-starter-web
        
        
            junit
            junit
            test
        
    

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

<?xml version="1.0" encoding="UTF-8" ?>



	
		
		
		
		
		
		
	
	
		
		
		
		
		
		
	

<?xml version="1.0" encoding="UTF-8" ?>


    
        
        
        
        
        
        
    

    
        emp_no,birth_date,first_name,last_name,gender,hire_date
    

    
        
            and first_name = #{firstName}
        
        
            and gender = #{gender}
        
    

    
        SELECT
        
        FROM employees limit 10
    

    
    
        SELECT * FROM employees where emp_no=#{empNo,jdbcType=BIGINT}
    

    
    
        SELECT * FROM employees
        where first_name like concat('%',#{firstName},'%') and last_name=#{lastName}
    

    
    
        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}
    

    
    
       INSERT INTO employees ()
       VALUES (#{empNo}, #{birthDate,jdbcType=DATE}, #{firstName}, #{lastName}, #{gender}, #{hireDate,jdbcType=DATE})
    

    
    
    
        INSERT INTO employees (birth_date,first_name,last_name,gender,hire_date)
        VALUES (#{birthDate,jdbcType=DATE}, #{firstName}, #{lastName}, #{gender}, #{hireDate,jdbcType=DATE})
    

    
    
    
    
        INSERT INTO employees (birth_date,first_name,last_name,gender,hire_date)
        VALUES (#{birthDate,jdbcType=DATE}, #{firstName}, #{lastName}, #{gender}, #{hireDate,jdbcType=DATE})
        
            select 500007
        
    

    
    
    
        UPDATE employees
        
            first_name = #{firstName},
            last_name = #{lastName},
        
        WHERE emp_no = #{empNo}
    

    
    
        SELECT * FROM employees
        where 1=1
        
            
                and first_name like concat('%',#{firstName},'%')
            
            
                and last_name=#{lastName}
            
            
                and emp_no=500007
            
        
    

    
    
        SELECT * FROM employees
        
             and first_name = #{firstName}
             and last_name = #{lastName}
        
        order by emp_no desc limit 10
    

    
    
    
        SELECT * FROM employees
        
             and first_name = #{firstName}
             or last_name = #{lastName}
        
        order by emp_no desc limit 10
    
    
    
    
    
    
        SELECT * FROM employees
        
            
                first_name in
                
                    #{empNo}
                
            
        
        order by emp_no desc limit 10
    
    
    
    
    
        SELECT * FROM employees
        
            
                first_name in
                
                    #{item.firstName}
                
            
        
        order by emp_no desc limit 10
    
    
    
    
    
        SELECT * FROM employees
        
            
                and ${key}= #{value}
            
        
        order by emp_no desc limit 10
    

    
    
        SELECT * FROM employees
        
            
                
                first_name like #{firstNameLike}
                
            
        
        order by emp_no desc limit 10
    

    
       DELETE FROM employees WHERE emp_no = #{empNo}
    

    
    
    
    
    
    
    
    
    
        
        
    
    
        SELECT * FROM employees
        order by emp_no limit 20
    


    
    
    
    
    
        
        
    
    
        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 * FROM employees order by emp_no limit 20
    

    
    
    
        {call select_employee_by_id(
        #{empNo,mode=IN},#{firstName,mode=OUT,jdbcType=VARCHAR},
        #{lastName,mode=OUT,jdbcType=VARCHAR},#{birthDate,mode=OUT,jdbcType=VARCHAR})}
    

    
    
        {call select_employee_page(
        #{firstName,mode=IN},
        #{_offset,mode=IN},#{_limit,mode=IN},
        #{total,mode=OUT,jdbcType=INTEGER})}
    

<?xml version="1.0" encoding="UTF-8" ?>


    
        
        
        
        
    

    
    
        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 * FROM salaries where emp_no=#{salaryEmpNo}
    

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

    List selectEmpUseAssociation();
    List selectEmpUseCollection();
    List selectEmpUseCollectionSubSelected();

    void selectEmployeeById(Employee employee);

    List selectEmployeePage(Map map);


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

package com.yq.mybatis.mapper;

import com.yq.mybatis.model.Salary;

import java.util.List;

public interface SalaryMapper {

   List selectByEmpNo(Long empNo);
}

package com.yq.mybatis.model;

public class BaseResult {
    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  BaseResult success() {
        return new BaseResult<>();
    }

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

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

    public static  BaseResult 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;
        }
    }
}

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

    public List getSalaries() {
        return salaries;
    }

    public void setSalaries(List 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;
    }
}

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

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> getAll(){
        List list=mapper.getAll();
        return BaseResult.successWithData(list);
    }

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

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

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

    @GetMapping("getByNameAndSalary")
    public BaseResult> 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> 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> getUseWhere(String firstName,String lastName){

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

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

    @GetMapping("getUseForeachListString")
    public BaseResult> getUseForeachListString(String firstName,String lastName){
        List list=new ArrayList<>();
//        list.add("Jack");
//        list.add("Sachin");
        return BaseResult.successWithData(mapper.getUseForeachListString(list));
    }
    @GetMapping("getUseForeachListObject")
    public BaseResult> getUseForeachListObject(String firstName,String lastName){
        List 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> getUseForeachMap(String firstName,String lastName){
        Map map=new HashMap<>();
        map.put("first_name","Jack");
        map.put("last_name","Yu");
        return BaseResult.successWithData(mapper.getUseForeachMap(map));
    }
    @GetMapping("getUseBind")
    public BaseResult> getUseBind(String firstName){
        return BaseResult.successWithData(mapper.getUseBind(firstName));
    }

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

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

    @GetMapping("selectEmpUseCollectionSubSelected")
    public BaseResult> selectEmpUseCollectionSubSelected(){
        List 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 map=new HashMap<>();
        map.put("firstName","Jack");
        map.put("_offset","1");
        map.put("_limit","10");
       // map.put("total","0");
        List 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 addEmployeeAuto(@RequestBody Employee employee){
        mapper.insertReturnEmpNo(employee);
        String empNo=String.valueOf(employee.getEmpNo());
        return BaseResult.successWithData(empNo);
    }

    @PostMapping("addEmployeeSelectKey")
    public BaseResult 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();
    }
}

<?xml version="1.0" encoding="utf-8" ?>



    
    
    
        
        
        
        
        

        
            
            
            
            
            
            
        

        
        
        
        
            
        

        
        

        
        

        
        
    

        
            
                org.springframework.boot
                spring-boot-maven-plugin
            
            
            
                org.mybatis.generator
                mybatis-generator-maven-plugin
                1.3.1
                
                    ${basedir}/src/main/resources/mybatis/generatorConfig.xml
                    true
                    true
                
                
                    
                        mysql
                        mysql-connector-java
                        8.0.27
                    
                    
                        com.yq
                        mybatis
                        0.0.1-SNAPSHOT