In the interface of the mybatis mapper, it is generally necessary to pass some parameters as query conditions when querying, sometimes one, sometimes multiple. When there is only one parameter, we only need to use the parameter name in the interface in sql, but if there are more than one, we cannot use the parameter name directly. mybatis has the following four

The first: use map to pass

1⃣️Define the interface

// Use map to pass multiple parameters to query
public List<Product> getByMap(Map<String, Object> paramMap);

2⃣️sql statement

<!--The first: through map transfer -->
     <select id="getByMap" resultType="product" parameterType="map">
         SELECT * FROM product
         WHERE product_name LIKE concat('%',#{name},'%') AND
         CAST(product_price AS INT) > #{price}
     </select>

Note:

• parameterType parameter type is map (alias is used here)

• The parameter name is the key in the map

3⃣️Inquiry

/**
     * Pass multiple parameters through map
     * 
     * @return
     */
    public void getProductsByMap() {
        System.out.println("use map way to pass multiple parameters");
        List<Product> products = new ArrayList<>();
        Map<String, Object> paramMap = new HashMap<>();
        paramMap.put("name", "shirt");
        paramMap.put("price", 200);
        sqlSession = MybatisTool.getSqlSession();
        productMapper = sqlSession.getMapper(ProductMapper.class);
        products = productMapper.getByMap(paramMap);
        printResult(products);
    }

4⃣️View results

use map way to pass multiple parameters
T The price of shirt 2 is 230 yuan
T The price of shirt 3 is 270 yuan
T The price of shirt 4 is 270 yuan

The disadvantages of this approach are:

• A map is a collection corresponding to a key value, and the user can only know its function by reading its key;

• Using map cannot limit the data type it passes, poor readability

Therefore, this method is generally not recommended.

The second: use annotations to pass

1⃣️Create an interface

 // Use annotations to pass multiple parameters to query
     public List<Product> getByAnnotation(@Param("name") String name, @Param("price") int price);

2⃣️Define sql

<!--The second: pass through annotations -->
    <select id="getByAnnotation" resultType="product">
        SELECT * FROM product
        WHERE product_name LIKE concat('%',#{name},'%') AND CAST(product_price
        AS INT) >
        #{price}
    </select>

This method does not need to set the parameter type, the parameter name is the name defined by the annotation

3⃣️Inquiry

/**
     * Pass multiple parameters through annotations
     */
    public void getProductByAnnotation() {
        System.out.println("Pass multiple parameters using annotations");
        List<Product> products = new ArrayList<>();
        sqlSession = MybatisTool.getSqlSession();
        productMapper = sqlSession.getMapper(ProductMapper.class);
        products = productMapper.getByAnnotation("shirt", 200);
        printResult(products);
    }

4⃣️View results

 Pass multiple parameters using annotations
 T The price of shirt 2 is 230 yuan
 T The price of shirt 3 is 270 yuan
 T The price of shirt 4 is 270 yuan

This method can greatly improve readability, but it is only suitable for cases with fewer parameters. Generally, this method is used for less than 5 parameters, and other methods are used for more than 5 parameters.

The third type: use javabean to pass

This method needs to encapsulate the passed parameters into a javabean, and then pass the javabean as a parameter. For convenience, I only have two parameters to encapsulate the javabean.

1⃣️Parameters are encapsulated into javabean s

/**
 * Define a Javabean to pass parameters
 */
public class ParamBean {
    public String name;
    public int price;

    public ParamBean(String name, int price) {
        this.name = name;
        this.price = price;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public int getPrice() {
        return price;
    }

    public void setPrice(int price) {
        this.price = price;
    }
}

2⃣️Create an interface

 // Using JavaBean s to pass multiple parameters to query
     public List<Product> getByJavabean(ParamBean paramBean)； 

3⃣️Define sql

 <!--Third: pass javabean transfer -->
    <select id="getByJavabean" resultType="product" parameterType="paramBean">
        SELECT * FROM product
        WHERE product_name LIKE concat('%',#{name},'%')
        AND CAST(product_price AS INT) > #{price}
     </select>

have to be aware of is:

• The parameter type parameterType is the fully qualified name or alias of the previously defined javabean;

• The parameter name in sql is the property defined in javabean;

4⃣️Inquiry

/**
     * Pass multiple parameters through javabean
     */
    public void getProductByJavabean() {
        System.out.println("use javabean way to pass multiple parameters");
        List<Product> products = new ArrayList<>();
        sqlSession = MybatisTool.getSqlSession();
        productMapper = sqlSession.getMapper(ProductMapper.class);
        ParamBean paramBean = new ParamBean("shirt", 200);
        products = productMapper.getByJavabean(paramBean);
        printResult(products);
    }

5⃣️View results

  use javabean way to pass multiple parameters
  T The price of shirt 2 is 230 yuan
  T The price of shirt 3 is 270 yuan
  T The price of shirt 4 is 270 yuan 

This method is more practical when there are more than 5 parameters.

Fourth: use mixed delivery

Suppose I want to perform a paging query, then I can encapsulate the paging parameters into a javabean separately for transmission, and encapsulate the other parameters into the above javabean, and then pass the two javabeans with annotations and get them in sql.

1⃣️ Encapsulate paging parameter javabean

/*
 * Define a paginated javabean
 */
public class PageParamBean {
    public int start;
    public int limit;

    public PageParamBean(int start, int limit) {
        super();
        this.start = start;
        this.limit = limit;
    }

    public int getStart() {
        return start;
    }

    public void setStart(int start) {
        this.start = start;
    }

    public int getLimit() {
        return limit;
    }

    public void setLimit(int limit) {
        this.limit = limit;
    }

}

2⃣️Create an interface

 // Querying with multiple parameters passed in a mixed manner
 public List<Product> getByMix(@Param("param") ParamBean paramBean, @Param("page") PageParamBean pageBean);

It can be seen that the parameters are passed here by using javabean + annotation.

3⃣️Define sql

<!--Fourth: Mixed delivery -->
    <select id="getByMix" resultType="product">
        SELECT * FROM product WHERE
        product_name LIKE concat('%',#{param.name},'%') AND CAST(product_price
        AS INT) >
        #{param.price} LIMIT #{page.limit} OFFSET #{page.start}
    </select>

As long as the annotation method is used, there is no need to define the parameter type.

4⃣️Inquiry

/**
     * Pass multiple parameters in a mixed manner
     */
    public void getProductByMix() {
        System.out.println("Passing Multiple Parameters Using Mixed Ways");
        List<Product> products = new ArrayList<>();
        sqlSession = MybatisTool.getSqlSession();
        productMapper = sqlSession.getMapper(ProductMapper.class);
        ParamBean paramBean = new ParamBean("shirt", 200);
        PageParamBean pageBean = new PageParamBean(0, 5);
        products = productMapper.getByMix(paramBean, pageBean);
        printResult(products);

5⃣️View results

 Passing Multiple Parameters Using Mixed Ways
 T The price of shirt 2 is 230 yuan
 T The price of shirt 3 is 270 yuan
 T The price of shirt 4 is 270 yuan