mybatis query in condition usage in sql detailed explanation of foreach

foreach attributes mainly include item, index, collection, open, separator and close

1. item represents the alias of every 1 element in the collection when iterating,

2. index specifies a name that indicates the location of each iteration during the iteration,

3. open indicates what the statement begins with,

4. separator indicates what symbol is used as the separator between each iteration,

5. close means what to end with,

6. collection attribute, which must be specified, but the value of this attribute is different under different circumstances.

There are mainly three situations:

a, the collection attribute value is list if a single parameter is passed in and the parameter type is 1 List.

b, the property value of collection is array if a single parameter is passed in and the parameter type is an array of array.

c, if the passed parameters are multiple, we need to encapsulate them into an Map. Of course, a single parameter can also be encapsulated into an map. In fact, if you pass in parameters, it will also be encapsulated into an Map in MyBatis, and key of map is the parameter name, so at this time, the attribute value of collection is the key of the passed List or array object in its own encapsulated map.

<select id="findBy" resultMap="RfCustomerMemMap" parameterType="java.util.Map">
  SELECT
  <include refid="Column"/>
  FROM rfl_customer_mem a LEFT JOIN rfl_loan b ON a.member_no = b.loan_member_no
  WHERE a.member_no = #{memberNo} AND b.status IN
  <foreach collection="status" index="index" item="item" open="(" separator="," close=")">
   #{item}
  </foreach>
  <if test="name != null and name != ''">
   AND name = #{name}
  </if>
  <if test="idNumber != null and idNumber != ''">
   AND id_number = #{idNumber}
  </if>
  <if test="mobileNo != null and mobileNo != ''">
   AND mobile_no = #{mobileNo}
  </if>
  <if test="loanNo != null and loanNo != ''">
   AND loan_no = #{loanNo}
  </if>
  order by a.id DESC
  <if test="offset > -1 and rows > -1">
   limit #{offset},#{limit}
  </if>
 </select>

java Call Query sql Code

public List<LoanMerchantMemEntity> findMerchantMemBy(String merchantName, String merchantNo, String socialCreditCode, String loanNo, int offset, int limit) {
  List<LoanMerchantMemEntity> list = new ArrayList<LoanMerchantMemEntity>();
  Map<String, Object> filter = new HashMap<String, Object>(); 
  filter.put("merchantName", merchantName);
  filter.put("socialCreditCode", socialCreditCode);
  filter.put("status", statsList());
  filter.put("loanNo", loanNo);
  filter.put("offset", offset);
  filter.put("limit", limit);
  filter.put("merchantNo", merchantNo);
 
  try {
   List<LoanMerchantMemEntity> row = loanMerchantMemDao.findBy(filter);
  } catch (Exception e) {
   LOGGER.error(filter, " Exception in querying enterprise member information ", e);
  }
  return list;
 }

 static List<String> statsList(){
  List<String> statusList = new ArrayList<String>();
  statusList.add("SUCCESS");
  statusList.add("DUE");
  statusList.add("OVER");
  return statusList;
 }

Among them, key in map is status value type is list, which is the third usage scenario, that is, collection is key value in map

Supplement: After passing in 1 String array, use foreach statement to realize IN query in sql

When we pass an array from the foreground, we have to deal with it in the background, because the field type of the table in the database may be num or varchar;

What I'm sending here is Map. Of course, you can also use request. getparameter ("name"). This name is name corresponding to id in jsp or htm page.

Also in the following code:

 String name=(String) params.get("name");
 String[] hiddens = name.split(",");
 params.put("name", hiddens); 

When we go through the processing of this part 1, the data is stored in map, and the query is carried out after the parameters are passed in

AND Condition in

<foreach collection="name" index="index" item="item" open="(" separator="," close=")"> 
   #{item} 
  </foreach> 

Above sql, when we query OK!