SpringBoot+Mybatis框架搭建（二）----数据库语句分离为xml/多条件联合查询

背景：1框架搭建（一）未将mapper语句与方法分离，可将语句配置成xml；

           2.（且语句可支持多条件联合查询）

项目路径如下：

1.在原框架基础上application.yml里添加mapper和实体类配置

mybatis: #mybatis配置
 mapper-locations: classpath:mapper/*.xml   //数据库语句路径
 type-aliases-package: Class4.entity  //实体路径

server:
 port: 8080   //本地端口

spring:
 datasource:
  driverClassName: com.mysql.jdbc.Driver
  url: jdbc:mysql://xxx:xxx/qqzw_resource_manage_dev   //数据库
  username: admin   //用户名
  password: Hanyu@2021  //密码

2.resources下新建UserMapper.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="Class4.mapper.UserinfoMapper">
    <resultMap id="ResultMap" type="Class4.entity.UserInfo">
        <id column="id" property="id" />
        <id column="age" property="age" />
        <id column="info" property="info" />
        <id column="name" property="name" />
    </resultMap>

<!--多条件组合查询，id!=null的条件是id不为int类型，需要设置为Interger-->
    <select id="selectById" resultMap="ResultMap">
        select id, age,name, info from test_user_info where 1=1
            <if test="id != null">
                and id = #{id,jdbcType=TINYINT}
            </if>
            <if test="name != null and name != ''">
                and name = #{name,jdbcType=VARCHAR}
            </if>

    </select>

    <insert id="insert" >
       insert into test_user_info
       values(#{id,jdbcType=TINYINT},#{age,jdbcType=INTEGER},#{name,jdbcType=VARCHAR},#{info,jdbcType=VARCHAR})
    </insert>

    <insert id="update" >
       update test_user_info set name='jiangger' where id= #{id,jdbcType=TINYINT}
    </insert>

</mapper>

3.mapper/UserinfoMapper简化

package Class4.mapper;

import Class4.entity.UserInfo;
import org.apache.ibatis.annotations.*;
import org.apache.ibatis.type.JdbcType;
import org.springframework.stereotype.Repository;

import java.util.List;

/**
 * @author grjiang
 * @description:mapper
 * @date 2021-07-02 18:47
 */
    @Mapper
    public interface UserinfoMapper {
       List<UserInfo> selectById(Integer  id);//springMVC 不接收基本数据类型为空，id 应该为改为Integer 

      int insert(Integer id, int age,String name,String info); int update(int id); }

4.控制层controller/MysqlController, 修改注解

package Class4.controller;

import Class4.entity.UserInfo;
import Class4.mapper.UserinfoMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.*;

import javax.annotation.Resource;
import javax.xml.ws.ResponseWrapper;
import java.util.List;

/**
 * @author grjiang
 * @description:实现
 * @date 2021-07-02 19:01
 */
@Controller
@RequestMapping("/test")
public class MysqlController {

    @Resource   //注解变更
    UserinfoMapper userinfoMapper;


    @RequestMapping(value="/select",method= RequestMethod.GET)
    @ResponseBody    //springMVC 不接收基本数据类型为空，id 改为Integer ,多条件下可以将所有条件参数全部写上，@RequesParam可写可不写
    public List<UserInfo> select(String name,Integer id){
        return userinfoMapper.selectById(id,name);
    }

    @RequestMapping(value="/insert",method= RequestMethod.GET)
    @ResponseBody
    public int add(@RequestParam Integer id, int age,String name,String info){
       return userinfoMapper.insert(id,age,name,info);
    }

    @RequestMapping(value="/update",method= RequestMethod.GET)
    @ResponseBody
    public int add(@RequestParam Integer id){
        return userinfoMapper.update(id);
    }


}