mybatis参数为Map、List、Map<String, List<Integer>>、List<Map<String, Integer>>时mpper.xml的写法

以Oracle数据库Scott用户下的emp表举例，其中参数为List<Map<String, Integer>>时mapper.xml的写法需要注意，Oracle与MySQL不同

MySQL:

<update id="updateEmpByMapList" parameterType="list">
        <foreach item="paramMap" collection="paramMapList" open="" close="" separator="">
            update emp set sal = #{paramMap.SAL, jdbcType=INTEGER} where empno = #{paramMap.EMPNO, jdbcType=INTEGER};
        </foreach>
    </update>

并且需要在jdbc驱动后增加参数allowMultiQueries=true

Oracle：

<update id="updateEmpByMapList" parameterType="list">
        begin
        <foreach item="paramMap" collection="paramMapList" open="" close="" separator="">
            update emp set sal = #{paramMap.SAL, jdbcType=INTEGER} where empno = #{paramMap.EMPNO, jdbcType=INTEGER};
        </foreach>
        commit;
        end;
    </update>

mapper接口

package com.alphajuns.dao;

import org.apache.ibatis.annotations.Param;

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

public interface EmpMapper {

    List<Map<String, ?>> findEmpByMap(@Param("paramMap") Map<String,?> paramMap);

    List<Map<String, ?>> findEmpByList(@Param("paramList") List<Integer> paramList);

    void updateEmpByMapList(@Param("paramMapList") List<Map<String, Integer>> paramMapList);

    List<Map<String, ?>> findEmpByListMap(@Param("paramListMap") Map<String, List<Integer>> paramListMap);
}

mapper.xml

<?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.alphajuns.dao.EmpMapper">

    <select id="findEmpByMap" parameterType="map" resultType="map">
        select * from emp where job = #{paramMap.JOB, jdbcType=VARCHAR}
    </select>

    <select id="findEmpByList" parameterType="list" resultType="map">
       select * from emp where empno in
        <foreach item="item" index="index" collection="paramList" open="(" separator="," close=")">
            #{item}
        </foreach>
    </select>

    <update id="updateEmpByMapList" parameterType="list">
        begin
        <foreach item="paramMap" collection="paramMapList" open="" close="" separator="">
            update emp set sal = #{paramMap.SAL, jdbcType=INTEGER} where empno = #{paramMap.EMPNO, jdbcType=INTEGER};
        </foreach>
        commit;
        end;
    </update>

    <select id="findEmpByListMap" parameterType="map" resultType="map">
        select * from emp where empno in 
            <foreach item="empno" index="index" collection="paramListMap.empnoList" open="(" close=")" separator=",">
                #{empno}
            </foreach>
    </select>

</mapper>

service接口

package com.alphajuns.service;

import com.alphajuns.pojo.Employee;

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

public interface EmployeeService {

    List<Map<String, ?>> findEmpByMap(Map<String, ?> paramMap);

    List<Map<String, ?>> findEmpByList(List<Integer> paramList);

    void updateEmpByMapList(List<Map<String, Integer>> paramMapList);

    List<Map<String, ?>> findEmpByListMap(Map<String, List<Integer>> paramListMap);
}

service实现类

package com.alphajuns.service.impl;

import com.alphajuns.dao.EmpMapper;
import com.alphajuns.dao.IEmpDao;
import com.alphajuns.pojo.Employee;
import com.alphajuns.service.EmployeeService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

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

/**
 * @ClassName EmployeeServiceImpl
 * @Description TODO
 * @Author AlphaJunS
 * @Date 2020/3/25 19:51
 * @Version 1.0
 */
@Service
public class EmployeeServiceImpl implements EmployeeService {

    @Autowired
    private EmpMapper empMapper;

    @Override
    public List<Map<String, ?>> findEmpByMap(Map<String, ?> paramMap) {
        List<Map<String, ?>> empMapList = empMapper.findEmpByMap(paramMap);
        return empMapList;
    }

    @Override
    public List<Map<String, ?>> findEmpByList(List<Integer> paramList) {
        List<Map<String, ?>> empMapList = empMapper.findEmpByList(paramList);
        return empMapList;
    }

    @Override
    public void updateEmpByMapList(List<Map<String, Integer>> paramMapList) {
        empMapper.updateEmpByMapList(paramMapList);
    }

    @Override
    public List<Map<String, ?>> findEmpByListMap(Map<String, List<Integer>> paramListMap) {
        List<Map<String, ?>> empMapList = empMapper.findEmpByListMap(paramListMap);
        return empMapList;
    }


}

junit测试类

import org.junit.Before;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

/**
 * @ClassName JunitSuperTest
 * @Description TODO
 * @Author AlphaJunS
 * @Date 2020/3/25 20:39
 * @Version 1.0
 */
@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations = {"classpath*:applicationContext.xml"})
public class JunitSuperTest {

    @Before
    public void setUp() {
        System.out.println("======进入Junit方法======");
    }

}

import com.alphajuns.service.EmployeeService;
import org.junit.Test;
import org.springframework.beans.factory.annotation.Autowired;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @ClassName MapperTest
 * @Description mapper接口测试
 * @Author AlphaJunS
 * @Date 2020/3/30 20:47
 * @Version 1.0
 */
public class MapperTest extends JunitSuperTest {

    @Autowired
    private EmployeeService employeeService;

    @Test
    public void testFindEmpByMap() {
        Map<String, String> pramaMap = new HashMap<>();
        pramaMap.put("JOB", "CLERK");
        List<Map<String, ?>> empMapList = employeeService.findEmpByMap(pramaMap);
        System.out.println("empMapList:" + empMapList);
    }

    @Test
    public void testFindEmpByList() {
        List<Integer> empnoList = new ArrayList<>();
        empnoList.add(7788);
        empnoList.add(7900);
        List<Map<String, ?>> empMapList = employeeService.findEmpByList(empnoList);
        System.out.println("empMapList:" + empMapList);
    }

    @Test
    public void testUpdateEmpByMapList() {
        Map<String, Integer> pramaMap = new HashMap<>();
        pramaMap.put("EMPNO", 7902);
        pramaMap.put("SAL", 4000);
        Map<String, Integer> map = new HashMap<>();
        map.put("EMPNO", 7934);
        map.put("SAL", 2300);
        List<Map<String, Integer>> paramMapList = new ArrayList<>();
        paramMapList.add(pramaMap);
        paramMapList.add(map);
        employeeService.updateEmpByMapList(paramMapList);
    }

    @Test
    public void testFindEmpByListMap() {
        Map<String, List<Integer>> pramaListMap = new HashMap<>();
        List<Integer> empnoList = new ArrayList<>();
        empnoList.add(7369);
        empnoList.add(7499);
        pramaListMap.put("empnoList", empnoList);
        List<Map<String, ?>> empMapList = employeeService.findEmpByListMap(pramaListMap);
        System.out.println("empMapList:" + empMapList);
    }

}