There are many ways to pass parameters in mybatis

1. The first method is to pass parameters in sequence by anonymous parameters

controller

@ApiOperation(value = "Multiple parameter query_Anonymous sequential parameter transfer")
@GetMapping("findByParams")
public ResultMsg findByParams(Short gender,String age)
{
    List result= employeeMapper.selectByGenderAndAge(gender,age);
    return ResultMsg.getMsg(result);
}

mapper

List<Employee> selectByGenderAndAge(Short gender,String age );

xml

<select id="selectByGenderAndAge" resultMap="BaseResultMap" >
  select * from employee where gender = #{gender} and age = #{age}
</select>

Note that if you reference by parameter name here, the following error will be reported. The mybatis error prompt is very detailed. Here we are explicitly prompted that anonymous parameters can only be used

arg1, arg0, param1, param2 have similar forms

The disadvantage of this parameter transfer method is that it is not flexible enough and must be referenced strictly according to the parameter order

BindingException: Parameter 'gender' not found. Available parameters are [arg1, arg0, param1, param2]

Therefore, the correct reference method is as follows:

  <select id="selectByGenderAndAge" resultMap="BaseResultMap" >
    select *  from employee where gender = #{param1} and age = #{param2}
  </select>

2. The second method uses @ Param annotation

controller

@ApiOperation(value = "Multiple parameter query_Annotation mode parameters")
@GetMapping("findByParams2")
public ResultMsg findByParams2(Short gender,String age)
{
    List result= employeeMapper.selectByGenderAndAge2(gender,age);
    return ResultMsg.getMsg(result);
}

mapper
Use the @ Param annotation to tell mybatis the name of the parameter, so that it can be referenced according to the parameter name in xml

List<Employee> selectByGenderAndAge( @Param("gender") Short gender,@Param("age") String age );

xml

<select id="selectByGenderAndAge" resultMap="BaseResultMap" >
  select * from employee where gender = #{gender} and age = #{age}
</select>

3. Use Map to transfer parameters

It is a recommended way to use map to pass multiple parameters in practical development

controller

@ApiOperation(value = "Multiple parameter query")
@GetMapping("findByMapParams")
public ResultMsg findByMapParams(Short gender,String age)
{
    Map params = new HashMap<>();
    params.put("gender",gender);
    params.put("age",age);
    List result= employeeMapper.selectByMapParams(params);
    return ResultMsg.getMsg(result);
}

mapper

List<Employee> selectByMapParams(Map params);

You can see that map is used to pass multiple parameters, and parameter names can be directly used for reference

<select id="selectByMapParams" resultMap="BaseResultMap" parameterType="map">
  select * from employee where gender = #{gender} and age = #{age}
</select>

4. Used Java beans to pass multiple parameters

You can also use the bean method to pass multiple parameters. When using, you can specify the parameterType as the corresponding bean type

This is the advantage of the parameter transfer method. The controller layer uses @ RequestBody to receive the entity class parameters and directly pass them to the mapper layer for calling. There is no need to convert the parameters

controller

@ApiOperation(value = "Multiple parameter query_adopt Java Bean Pass multiple parameters")
@PostMapping("findByBeans")
public ResultMsg findByBeans(@RequestBody Employee employee)
{
    List result= employeeMapper.selectByBeans(employee);
    return ResultMsg.getMsg(result);
}

mapper

List <Employee> selectByBeans(Employee employee);

xml

Parameter references directly use the bean's fields

<select id="selectByBeans" resultMap="BaseResultMap" parameterType="com.wg.demo.po.Employee">
  select
  *
  from employee where gender = #{gender} and age = #{age}
</select>

5. Directly use JSON to pass parameters

This is also a recommended parameter transfer method. After the controller layer receives JSON data, it is directly passed to the mapper layer for query operation, which is simple and convenient

controller

@ApiOperation(value = "Multiple parameter query_adopt JSON Pass multiple parameters")
@PostMapping("findByJSONObject")
public ResultMsg findByJSONObject(@RequestBody JSONObject params)
{
    List result= employeeMapper.findByJSONObject(params);
    return ResultMsg.getMsg(result);
}

mapper

List <Employee> findByJSONObject(JSONObject params);
<select id="findByJSONObject" resultMap="BaseResultMap" parameterType="com.alibaba.fastjson.JSONObject">
  select
  *
  from employee where gender = #{gender} and age = #{age}
</select>

6. Pass the Set type parameters List, Set and Array

In some complex queries (such as the in operation in sql), the traditional parameter transfer can no longer meet the requirements. At this time, the parameter transfer of List, Set and Array types is used. The specific use is as follows:

controller

@ApiOperation(value = "Multiple parameter query_adopt List,Set,Array Pass multiple parameters")
@PostMapping("findByList")
public ResultMsg findByList(@RequestBody List<String> list)
{
    List result= employeeMapper.findByList (list);
    return ResultMsg.getMsg(result);
}

mapper

List <Employee> findByList(List list);

xml

  <select id="findByList" resultMap="BaseResultMap" >
SELECT * from employee where age in
    <foreach collection="list" open="(" separator="," close=")" item="age">
      #{age}
    </foreach>
  </select>

foreach here represents a loop operation. The specific parameters are as follows:

The attributes of foreach elements mainly include item, index, collection, open, separator and close.
item represents the alias of each element in the collection during iteration, and index specifies a name to represent the location of each iteration in the iteration process,
open indicates what the statement starts with, and separator indicates what symbol is used as the separator between each iteration,

close indicates what to end with

When using foreach, the most critical and error prone attribute is the collection attribute, which must be specified, but the value of this attribute is different in different cases. There are three main cases:

1. If a single parameter is passed in and the parameter type is a list, the collection attribute value is list
2. If a single parameter is passed in and the parameter type is an array, the attribute value of collection is array
3. If there are multiple parameters passed in, we need to encapsulate them into a Map or Object

7. The parameter type is object + set

The parameters of this class are similar to those of java Bean, but more complex. For example, the following Department class includes a list of employees in addition to the basic fields

bean

@Data
public class Department {
    private Long id;

    private String deptName;

    private String descr;

    private Date createTime;

    List<Employee> employees;

}

controller

@ApiOperation(value = "Multiple parameter query_object+Set parameters")
@PostMapping("findByDepartment")
public ResultMsg findByDepartment(@RequestBody Department department)
{
    List result= employeeMapper.findByDepartment(department);
    return ResultMsg.getMsg(result);
}

mapper

List <Employee> findByDepartment(@Param("department")Department department);

xml

<select id="findByDepartment" resultMap="BaseResultMap" parameterType="com.wg.demo.po.Department">
    SELECT * from employee where dept_id =#{department.id} and age in
    <foreach collection="department.employees" open="(" separator="," close=")" item="employee">
        #{employee.age}
    </foreach>
</select>

Here, foreach corresponds to List employees in the Departmen t

Request parameter: query employees with department Id=1 and age equal to 24 and 25

{
  "createTime": "2019-07-02T10:17:16.756Z",
  "deptName": "string",
  "descr": "string",
  "employees": [
    {
      "age": "24",
    },
    {
      "age": "25",
    }
  ],
  "id": 1
}

result:

{
  "data": [
    {
      "address": "Beixin Street ndcpc",
      "age": "24",
      "createTime": 1562062434000,
      "deptId": "1",
      "gender": 1,
      "id": "318397755696631808",
      "name": "kls0bx19cy"
    },
    {
      "address": "Beixin Street lavi0",
      "age": "25",
      "createTime": 1562062436000,
      "deptId": "1",
      "gender": 1,
      "id": "318397755801489408",
      "name": "gj9q3ygikh"
    }
  ],
  "result": "SUCCESS",
  "resultCode": 200,
  "resultMsg": ""
}

Tags: Java Mybatis Back-end

Posted on Mon, 20 Sep 2021 14:51:48 -0400 by asy1mpo