mybatis获取入参List和Map取值

公冶搔白 2022-09-08 我要评论

前言

最近在工作中需要使用到mybatis，需要实现某个功能。

但是发现需要编写一个sql，但是mybatis的映射文件入参是List集合和Map<String,Integer>,需要循环List，然后通过List循环出来的值为Key获取Map中的值作为sql的入参，遇到了一些问题。

但是经过不懈的努力，最后终于解决了这个问题。顺便分享一下自己的经验。

1.项目结构

2.pom文件配置

server:
  port: 8379
spring:
  datasource:
    username: root
    url: jdbc:mysql://localhost:3306/shop?useUnicode=true&characterEncoding=utf-8&useSSL=true&serverTimezone=UTC
    driver-class-name: com.mysql.jdbc.Driver
mybatis:
  mapper-locations: classpath:mapping/*Mapper.xml
  type-aliases-package: com.example.bean
#showSql
logging:
  level:
    com:
      example:
        dao : debug

3.其他的业务代码

package com.example.bean;
import lombok.Data;
@Data
public class SysUser {
    private Integer id;
    private String username;
    private String image;
}

package com.example.controller;
import com.example.bean.SysUser;
import com.example.service.SysUserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RestController;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
@RestController
public class SysUserController {
    @Autowired
    private SysUserService sysUserService;
    @GetMapping("/getSysUserById/{id}")
    SysUser getSysUserById(@PathVariable("id") String id) {
        List<String> ids = new ArrayList<>();
        Map<String, Integer> infoMap = new HashMap<>();
        infoMap.put("1", 1);
        infoMap.put("8", 8);
        infoMap.put("9", 9);
        ids.add(id);
        return sysUserService.getSysUserById(ids, infoMap);
    }
}

package com.example.dao;
import com.example.bean.SysUser;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import java.util.List;
import java.util.Map;
@Mapper
public interface SysUserMapper {
    SysUser getSysUserById(@Param("ids") List<String> ids,@Param("infoMap") Map<String,Integer> infoMap);
}

package com.example.service;
import com.example.bean.SysUser;
import com.example.dao.SysUserMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.List;
import java.util.Map;
@Service
public class SysUserService {
    @Autowired
    private SysUserMapper sysUserMapper;
    public SysUser getSysUserById(List<String> ids, Map<String, Integer> infoMap) {
        return sysUserMapper.getSysUserById(ids,infoMap);
    }
}

<?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.dao.SysUserMapper">
    <select id="getSysUserById" resultType="com.example.bean.SysUser">
        <foreach collection="ids" item="id">
            <bind name="userId" value="infoMap[id]"/>
            select * from sys_user where id = #{userId}
        </foreach>
    </select>
</mapper>

第一种采用#符的取值法

是可以防止sql注入的，可以通过循环List获取Map的值

<?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.dao.SysUserMapper">
    <select id="getSysUserById" resultType="com.example.bean.SysUser">
        <foreach collection="ids" item="id">
<!--            <bind name="userId" value="infoMap[id]"/>-->
            select * from sys_user where id = '${infoMap[id]}'
        </foreach>
    </select>
</mapper>