mybatis實現獲取入參是List和Map的取值

2022-06-27 18:01:10

前言

最近在工作中需要使用到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>