springboot中使用mybatis的参数传递方式以及动态sql

实体类

@Data
public class User {
    private Integer id;
    private String username;
    private String password;
    private Integer age;
    private int gender;
    private String address;
    private String birthday;
    private String job;
}

• 单参数传递

       //动态sql + 单参数传递(直接写)
      List<User> selectUserById(Integer id);
  

  对应的xml为

  <select id="selectUserById" resultType="com.example.crucialfunctiontest.entity.User">
   select * from user where id &gt;= #{id};
  </select>
  

• 少量多个参数传递

  注解传递参数 适合于参数不多 #{}里面的名称对应的是注解@Param括号里面修饰的名称。 非常好

   List<User> selectUserByAnnotation(@Param("id") Integer id, @Param("username") String username);
  

  对应的xml为

  <select id="selectUserByAnnotation" resultType="com.example.crucialfunctiontest.entity.User">
      select * from user
      <where>
          <if test="id != null and id != ''">
              and id >= #{id}
          </if>
          <if test="username != null and username != ''">
              and username like concat('%',#{username},'%')
          </if>
      </where>
  </select>
  

• 实体类传参

  #{}里面的名称对应的是User类里面的成员属性。 这种方法很直观，但需要建一个实体类，扩展不容易，需要加属性，看情况使用 注意test='id!=null and id != " " ' 和 test="username != null and username != ''"的区别 单双引号 是否有空格

  List<User> selectUserByEntity(User user);
  

  对应的xml为

  <!--一定加双引号-->
  <select id="selectUserByEntity" resultType="com.example.crucialfunctiontest.entity.User">
      select * from user
      <where>
          <if test='id!=null and id != " " '>
              and id &gt;= #{id}
          </if>
          <if test='username!=null and username !=" " '>
              and username like concat('%',#{username},'%')
          </if>
      </where>
  </select>
  

案例展示

mapper接口定义（内涵参数的传递形式 推荐@param注解 以及 实体类传递参数）

package com.example.crucialfunctiontest.mapper;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.example.crucialfunctiontest.entity.User;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;

import java.util.List;

/**
 * @Author xushupeng
 * @Date 2024-12-20 22:34
 */
@Mapper
public interface UserMapper extends BaseMapper<User> {

    List<User> getAllUser();

    //动态sql + 单参数传递(直接写)
    List<User> selectUserById(Integer id);


    //注解传递参数 适合于参数不多
    //#{}里面的名称对应的是注解@Param括号里面修饰的名称。 非常好
    List<User> selectUserByAnnotation(@Param("id") Integer id, @Param("username") String username);

    //实体类传参
    //#{}里面的名称对应的是User类里面的成员属性。
    //这种方法很直观，但需要建一个实体类，扩展不容易，需要加属性，看情况使用。
    List<User> selectUserByEntity(User user);

    //动态插入
    int insertUser(User user);

    int updateUser(User user);

//    choose(when,otherwise)语句
//    有时候，我们不想用到所有的查询条件，只想选择其中一个，查询条件有一个满足即可，使用choose标签可以解决此类问题，类似于Java的Switch语句
    // 通过choose查询学生信息
    List<User> selectUserByChoose(@Param("username") String name, @Param("gender") int gender, @Param("age") Integer age);

}

xml sql语句（包含参数传递 以及动态sql使用）

<?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.example.crucialfunctiontest.mapper.UserMapper">
    <insert id="insertUser" >
        insert into user
        <trim prefix="(" suffix=")" suffixOverrides=",">

            <if test="username != null and username != ''">
                username,
            </if>
            <if test="password != null and password != ''">
                password,
            </if>
            <if test="age != null and age != ''">
                age,
            </if>
            <if test="gender != null and gender != ''">
                gender,
            </if>
            <if test="birthday != null and birthday != ''">
                birthday,
            </if>
            <if test="job != null and job != ''">
                job
            </if>
        </trim>
        values
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="username != null and username != ''">
                #{username},
            </if>
            <if test="password != null and password != ''">
                #{password},
            </if>
            <if test="age != null and age != ''">
                #{age},
            </if>
            <if test="gender != null and gender != ''">
                #{gender},
            </if>
            <if test="birthday != null and birthday != ''">
                #{birthday},
            </if>
            <if test="job != null and job != ''">
                #{job}
            </if>
        </trim>
    </insert>

    <update id="updateUser" parameterType="com.example.crucialfunctiontest.entity.User">
            update user
            <set>
                <if test='username != null and username !=" "'>username=#{username},</if>
                <if test='password != null and password !=" "'>password=#{password},</if>
                <if test='age != null and age !=" "'>age=#{age},</if>
                <if test='gender != null and gender !=" "'>gender=#{gender},</if>
                <if test='birthday != null and birthday !=" "'>birthday=#{birthday},</if>
                <if test='job != null and job !=" "'>job=#{job}</if>
            </set>
            where id=#{id}

    </update>

    <select id="getAllUser" resultType="com.example.crucialfunctiontest.entity.User">
        select * from user;
    </select>

    <select id="selectUserById" resultType="com.example.crucialfunctiontest.entity.User">
     select * from user where id &gt;= #{id};
    </select>

    <select id="selectUserByAnnotation" resultType="com.example.crucialfunctiontest.entity.User">

        select * from user
        <where>
            <if test="id != null and id != ''">
                and id >= #{id}
            </if>
            <if test="username != null and username != ''">
                and username like concat('%',#{username},'%')
            </if>
        </where>

    </select>

    <!--一定加双引号-->
    <select id="selectUserByEntity" resultType="com.example.crucialfunctiontest.entity.User">
        select * from user
        <where>
            <if test='id!=null and id != " " '>
                and id &gt;= #{id}
            </if>
            <if test='username!=null and username !=" " '>
                and username like concat('%',#{username},'%')
            </if>
        </where>

</select>
    <select id="selectUserByChoose" resultType="com.example.crucialfunctiontest.entity.User">

        select * from user
        <where>
            <choose>
                <when test='username!=null and username !=" " '>
                    username like concat('%',#{name},'%')
                </when>
                <when test="gender!=null">
                    and gender=#{gender}
                </when>
                <when test="age!=null">
                    and age>=#{age}
                </when>
                <otherwise>
                    and 1=1
                </otherwise>

            </choose>
        </where>

    </select>

</mapper>