Using a List in a Spring Boot + MyBatis SQL Query

Table Structure

Suppose we already have a list containing three phone numbers: [1311, 1451, 2334]. Then the following SQL query returns the expected result:

1

SELECT * FROM user_info where phone in (1311,1451,2334)

Now the real requirement is slightly different: we have a list of phone numbers with a variable length, and we want to query the matching user information from that list.

Code

Start with the XML mapper:

1
2
3
4
5
6
7


<!-- Accept a list and return a list<map> -->
<select id="getUserInfoByPhone" parameterType="list" resultType="map">
        SELECT * FROM user_info where phone in
        <foreach collection="list" item="item" open="(" separator="," close=")">
            #{item}
        </foreach>
</select>

Then make sure the MyBatis XML file is correctly mapped to the mapper interface. If the mapping is wrong, MyBatis will complain that it cannot find the method.

1
2
3


#application.yml
mybatis:
  mapper-locations: classpath:mapper/**/*.xml

Next, define the mapper interface:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10


import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;

import java.util.List;
import java.util.Map;

@Mapper
public interface ListInSqlMapper {
    List<Map<String, Object>> getUserInfoByPhone(@Param("list") List<String> phoneNum);
}

Then write the controller:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17


@RestController
public class ListInSqlController {

    @Autowired
    private ListInSqlMapper listInSqlMapper;

    @GetMapping(value = "/userbyphone")
    public List<Map<String, Object>> getUserInfoByPhone() {
        // For testing, just hard-code a few phone numbers here
        List<String> phoneNum = new ArrayList<>();
        phoneNum.add("1311");
        phoneNum.add("1451");
        phoneNum.add("2334");
        phoneNum.add("2333");
        return listInSqlMapper.getUserInfoByPhone(phoneNum);
    }
}

Query Result

Now test it with Postman:

The test succeeds.