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); } }