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

第二种方式采用$符的取值法
也可以获取,虽然不能防止sql注入,但是可以做白名单的校验,防止sql注入
4.总结
本人小白一枚,以上仅为个人经验,希望能给大家一个参考,也希望大家多多支持w3xue。