MyBatis传递参数的几种方法

蓝黑2020

已于 2022-04-20 14:40:14 修改

阅读量1k

点赞数 1

分类专栏： DB Java 文章标签：数据库 mybatis

于 2022-04-20 11:37:49 首次发布

本文链接：https://blog.csdn.net/duke_ding2/article/details/124285141

版权

Java 同时被 2 个专栏收录

44 篇文章 3 订阅

订阅专栏

32 篇文章 0 订阅

订阅专栏

环境

Db2：V11.5
MyBatis：3.5.6

现有table T1 如下：

[db2inst1@ruili1 ~]$ db2 "select * from t1"

C1          C2          C3
----------- ----------- ----------------------------------------------------------------------------------------------------
          1         100 aaa
          2         110 bbb
          3         150 ccc
          4         100 ddd
          5         110 aaa

  5 record(s) selected.

测试

首先定义2个POJO：

Input1 ：传入参数

package pojo;

public class Input1 {
	private int p1;
	private String p2;

	public int getP1() {
		return p1;
	}

	public void setP1(int p1) {
		this.p1 = p1;
	}

	public String getP2() {
		return p2;
	}

	public void setP2(String p2) {
		this.p2 = p2;
	}
}

Output1 ：接收查询结果

package pojo;

public class Output1 {
	private int c1;
	private int c2;
	private String c3;

	public int getC1() {
		return c1;
	}

	public void setC1(int c1) {
		this.c1 = c1;
	}

	public int getC2() {
		return c2;
	}

	public void setC2(int c2) {
		this.c2 = c2;
	}

	public String getC3() {
		return c3;
	}

	public void setC3(String c3) {
		this.c3 = c3;
	}
}

测试1：单个简单参数

对于单个简单参数，在mapper中可以直接使用，并且任意命名。

Test0419_1Mapper.java

package dao;

import java.util.List;

import pojo.Output1;

public interface Test0419_1Mapper {
	public List<Output1> read(int p1);
}

Test0419_1Mapper.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="dao.Test0419_1Mapper">
	
	<select id="read" resultType="pojo.Output1">
		select c1, c2, c3 from t1
 		where c2 = #{xxx}
	</select>
</mapper>

此处，将其随意命名为 #{xxx} 即可使用。

Test0419_1.java

package service;

import java.io.IOException;
import java.io.InputStream;
import java.util.List;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import dao.Test0419_1Mapper;
import pojo.Output1;

public class Test0419_1 {
	public static void main(String[] args) throws IOException {
		String resource = "mybatis-config.xml";
		
		InputStream inputStream = Resources.getResourceAsStream(resource);
		
		SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
		
		SqlSession sqlSession = sqlSessionFactory.openSession(true);
		
		try {
			Test0419_1Mapper mapper = sqlSession.getMapper(Test0419_1Mapper.class);
			
			List<Output1> list = mapper.read(100);
			list.forEach(e -> System.out.println("c1: " + e.getC1() + ", c2: " + e.getC2() + ", c3: " + e.getC3()));
		} finally {
			sqlSession.close();
		}
	}
}

运行结果如下：

c1: 1, c2: 100, c3: aaa
c1: 4, c2: 100, c3: ddd

测试2：多个简单参数

可直接用位置参数来获取参数值。使用 arg0 、 arg1 、 arg2 或者 param1 、 param2 、 param3 来代表第1、2、3个参数。

注意： arg<N> 是以 0 开始计数，而 param<N> 是以 1 开始计数的。
注意：二者不可混用，否则会出错。

Test0419_2Mapper.java

package dao;

import java.util.List;

import pojo.Output1;

public interface Test0419_2Mapper {
	public List<Output1> read(int p1, int p2, String p3);
}

Test0419_2Mapper.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="dao.Test0419_2Mapper">
	
	<select id="read" resultType="pojo.Output1">
		select c1, c2, c3 from t1
 		where c1 = #{param1} and c2 = #{param2} and c3 = #{param3}
	</select>
</mapper>

Test0419_2.java

package service;

import java.io.IOException;
import java.io.InputStream;
import java.util.List;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import dao.Test0419_2Mapper;
import pojo.Output1;

public class Test0419_2 {
	public static void main(String[] args) throws IOException {
		String resource = "mybatis-config.xml";
		
		InputStream inputStream = Resources.getResourceAsStream(resource);
		
		SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
		
		SqlSession sqlSession = sqlSessionFactory.openSession(true);
		
		try {
			Test0419_2Mapper mapper = sqlSession.getMapper(Test0419_2Mapper.class);
			
			List<Output1> list = mapper.read(1, 100, "aaa");
			list.forEach(e -> System.out.println("c1: " + e.getC1() + ", c2: " + e.getC2() + ", c3: " + e.getC3()));
		} finally {
			sqlSession.close();
		}
	}
}

运行结果如下：

c1: 1, c2: 100, c3: aaa

测试3：多个简单参数，使用注解来命名

对于多个参数，如果觉得位置参数不直观，可以使用 @Param 注解来直接命名变量。

Test0419_3Mapper.java

package dao;

import java.util.List;

import pojo.Output1;

import org.apache.ibatis.annotations.Param;

public interface Test0419_3Mapper {
	public List<Output1> read(@Param("c1")int p1, @Param("c2")int p2, @Param("c3")String p3);
}

此处直接定义了 c1 和 p1 的对应关系。 c2 和 p2 ， c3 和 p3 也同理。

Test0419_3Mapper.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="dao.Test0419_3Mapper">
	
	<select id="read" resultType="pojo.Output1">
		select c1, c2, c3 from t1
 		where c1 = #{c1} and c2 = #{c2} and c3 = #{c3}
	</select>
</mapper>

Test0419_3.java

package service;

import java.io.IOException;
import java.io.InputStream;
import java.util.List;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import dao.Test0419_3Mapper;
import pojo.Output1;

public class Test0419_3 {
	public static void main(String[] args) throws IOException {
		String resource = "mybatis-config.xml";
		
		InputStream inputStream = Resources.getResourceAsStream(resource);
		
		SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
		
		SqlSession sqlSession = sqlSessionFactory.openSession(true);
		
		try {
			Test0419_3Mapper mapper = sqlSession.getMapper(Test0419_3Mapper.class);
			
			List<Output1> list = mapper.read(1, 100, "aaa");
			list.forEach(e -> System.out.println("c1: " + e.getC1() + ", c2: " + e.getC2() + ", c3: " + e.getC3()));
		} finally {
			sqlSession.close();
		}
	}
}

运行结果如下：

c1: 1, c2: 100, c3: aaa

测试4：多个复杂参数

此处复杂指的是对象类型，其实和测试3没什么区别，只不过在使用对象的时候，需要获取对象的一些属性值。

Test0419_4Mapper.java

package dao;

import java.util.List;

import pojo.Input1;
import pojo.Output1;

import org.apache.ibatis.annotations.Param;

public interface Test0419_4Mapper {
	public List<Output1> read(@Param("c1")int p1, @Param("p2")Input1 input1);
}

Test0419_4Mapper.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="dao.Test0419_4Mapper">
	
	<select id="read" resultType="pojo.Output1">
		select c1, c2, c3 from t1
 		where c1 = #{c1} and c2 = #{p2.p1} and c3 = #{p2.p2}
	</select>
</mapper>

注意，这里使用了 #{p2.p1} 的写法，其中 p2 代表了Input1的实例对象， p1 是其属性，类似于Java里的 getP1() 方法。同理，别把 #{p2.p2} 中的2个 p2 搞混了。

Test0419_4.java

package service;

import java.io.IOException;
import java.io.InputStream;
import java.util.List;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import dao.Test0419_4Mapper;
import pojo.Input1;
import pojo.Output1;

public class Test0419_4 {
	public static void main(String[] args) throws IOException {
		String resource = "mybatis-config.xml";
		
		InputStream inputStream = Resources.getResourceAsStream(resource);
		
		SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
		
		SqlSession sqlSession = sqlSessionFactory.openSession(true);
		
		try {
			Test0419_4Mapper mapper = sqlSession.getMapper(Test0419_4Mapper.class);
			
			Input1 input1 = new Input1();
			input1.setP1(100);
			input1.setP2("aaa");
			List<Output1> list = mapper.read(1, input1);
			list.forEach(e -> System.out.println("c1: " + e.getC1() + ", c2: " + e.getC2() + ", c3: " + e.getC3()));
		} finally {
			sqlSession.close();
		}
	}
}

运行结果如下：

c1: 1, c2: 100, c3: aaa

测试5：单个复杂参数Map对象

如果不想传入多个参数，可以把多个参数包含在一个Map对象里。

Test0419_5Mapper.java

package dao;

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

import pojo.Output1;

public interface Test0419_5Mapper {
	public List<Output1> read(Map<String, Object> map);
}

Test0419_5Mapper.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="dao.Test0419_5Mapper">
	
	<select id="read" parameterType="map" resultType="pojo.Output1">
		select c1, c2, c3 from t1
 		where c1 = #{p1} and c2 = #{p2.p1} and c3 = #{p2.p2}
	</select>
</mapper>

注意，这里是通过key值来获取对象的。

Test0419_5.java

package service;

import java.io.IOException;
import java.io.InputStream;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import dao.Test0419_5Mapper;
import pojo.Input1;
import pojo.Output1;

public class Test0419_5 {
	public static void main(String[] args) throws IOException {
		String resource = "mybatis-config.xml";
		
		InputStream inputStream = Resources.getResourceAsStream(resource);
		
		SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
		
		SqlSession sqlSession = sqlSessionFactory.openSession(true);
		
		try {
			Test0419_5Mapper mapper = sqlSession.getMapper(Test0419_5Mapper.class);
			
			Input1 input1 = new Input1();
			input1.setP1(100);
			input1.setP2("aaa");
			
			Map<String, Object> map = new HashMap<String, Object>();
			map.put("p1", 1);
			map.put("p2", input1);
			List<Output1> list = mapper.read(map);
			list.forEach(e -> System.out.println("c1: " + e.getC1() + ", c2: " + e.getC2() + ", c3: " + e.getC3()));
		} finally {
			sqlSession.close();
		}
	}
}

运行结果如下：

c1: 1, c2: 100, c3: aaa

测试6：单个复杂参数List对象

Test0419_6Mapper.java

package dao;

import java.util.List;

import pojo.Input1;
import pojo.Output1;

public interface Test0419_6Mapper {
	public List<Output1> read(List<Input1> listInput);
}

Test0419_6Mapper.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="dao.Test0419_6Mapper">
	
	<select id="read" parameterType="list" resultType="pojo.Output1">
		select c1, c2, c3 from t1
 		where (c2, c3) in (values
 		<foreach item="item" index="index" collection="list" separator=",">
			(#{item.p1}, #{item.p2})
		</foreach>
		)
	</select>
</mapper>

Test0419_6.java

package service;

import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import dao.Test0419_6Mapper;
import pojo.Input1;
import pojo.Output1;

public class Test0419_6 {
	public static void main(String[] args) throws IOException {
		String resource = "mybatis-config.xml";
		
		InputStream inputStream = Resources.getResourceAsStream(resource);
		
		SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
		
		SqlSession sqlSession = sqlSessionFactory.openSession(true);
		
		try {
			Test0419_6Mapper mapper = sqlSession.getMapper(Test0419_6Mapper.class);
			
			List<Input1> listInput1 = new ArrayList<Input1>();
			
			Input1 input1 = new Input1();
			input1.setP1(100);
			input1.setP2("aaa");
			listInput1.add(input1);
			
			input1 = new Input1();
			input1.setP1(110);
			input1.setP2("bbb");
			listInput1.add(input1);
			
			List<Output1> list = mapper.read(listInput1);
			list.forEach(e -> System.out.println("c1: " + e.getC1() + ", c2: " + e.getC2() + ", c3: " + e.getC3()));
		} finally {
			sqlSession.close();
		}
	}
}

运行结果如下：

c1: 1, c2: 100, c3: aaa
c1: 2, c2: 110, c3: bbb

测试7：单个复杂参数Map对象（包含List对象和其它对象）

Test0419_7Mapper.java

package dao;

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

import pojo.Output1;

public interface Test0419_7Mapper {
	public List<Output1> read(Map<String, Object> map);
}

Test0419_7Mapper.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="dao.Test0419_7Mapper">
	
	<select id="read" parameterType="map" resultType="pojo.Output1">
		select c1, c2, c3 from t1
 		where c1 = #{p1} and (c2, c3) in (values
 		<foreach item="item" index="index" collection="p2" separator=",">
			(#{item.p1}, #{item.p2})
		</foreach>
		)
	</select>
</mapper>

Test0419_7.java

package service;

import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import dao.Test0419_7Mapper;
import pojo.Input1;
import pojo.Output1;

public class Test0419_7 {
	public static void main(String[] args) throws IOException {
		String resource = "mybatis-config.xml";
		
		InputStream inputStream = Resources.getResourceAsStream(resource);
		
		SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
		
		SqlSession sqlSession = sqlSessionFactory.openSession(true);
		
		try {
			Test0419_7Mapper mapper = sqlSession.getMapper(Test0419_7Mapper.class);
			
			List<Input1> listInput1 = new ArrayList<Input1>();
			
			Input1 input1 = new Input1();
			input1.setP1(100);
			input1.setP2("aaa");
			listInput1.add(input1);
			
			input1 = new Input1();
			input1.setP1(110);
			input1.setP2("bbb");
			listInput1.add(input1);
			
			Map<String, Object> map = new HashMap<String, Object>();
			map.put("p1", 1);
			map.put("p2", listInput1);
			
			List<Output1> list = mapper.read(map);
			list.forEach(e -> System.out.println("c1: " + e.getC1() + ", c2: " + e.getC2() + ", c3: " + e.getC3()));
		} finally {
			sqlSession.close();
		}
	}
}

运行结果如下：

c1: 1, c2: 100, c3: aaa

测试8：多个复杂对象，使用注解来命名

Test0419_8Mapper.java

package dao;

import java.util.List;

import pojo.Input1;
import pojo.Output1;

import org.apache.ibatis.annotations.Param;

public interface Test0419_8Mapper {
	public List<Output1> read(@Param("p1")int p1, @Param("p2")List<Input1> listInput1);
}

Test0419_8Mapper.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="dao.Test0419_8Mapper">
	
	<select id="read" resultType="pojo.Output1">
		select c1, c2, c3 from t1
 		where c1 = #{p1} and (c2, c3) in (values
 		<foreach item="item" index="index" collection="p2" separator=",">
			(#{item.p1}, #{item.p2})
		</foreach>
		)
	</select>
</mapper>

Test0419_8.java

package service;

import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import dao.Test0419_8Mapper;
import pojo.Input1;
import pojo.Output1;

public class Test0419_8 {
	public static void main(String[] args) throws IOException {
		String resource = "mybatis-config.xml";
		
		InputStream inputStream = Resources.getResourceAsStream(resource);
		
		SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
		
		SqlSession sqlSession = sqlSessionFactory.openSession(true);
		
		try {
			Test0419_8Mapper mapper = sqlSession.getMapper(Test0419_8Mapper.class);
			
			List<Input1> listInput1 = new ArrayList<Input1>();
			
			Input1 input1 = new Input1();
			input1.setP1(100);
			input1.setP2("aaa");
			listInput1.add(input1);
			
			input1 = new Input1();
			input1.setP1(110);
			input1.setP2("bbb");
			listInput1.add(input1);
			
			List<Output1> list = mapper.read(1, listInput1);
			list.forEach(e -> System.out.println("c1: " + e.getC1() + ", c2: " + e.getC2() + ", c3: " + e.getC3()));
		} finally {
			sqlSession.close();
		}
	}
}

运行结果如下：

c1: 1, c2: 100, c3: aaa

总结

测试情景	ParameterType （可选）	如何命名参数	Mapper (java)	Mapper (xml)
单个参数（简单）	`parameterType="int"`	任意命名	`public List<Output1> read(int p1);`	`<select id="read" resultType="pojo.Output1">` `select c1, c2, c3 from t1` `where c2 = #{xxx}` `</select>`
单个参数（对象）	`parameterType="pojo.Input1"`	直接指定属性名，比如 `#{p1}` 。还可以通过 `#{p1.xx}` 联动	`public List<Output1> read(Input1 input1);`	`<select id="read" resultType="pojo.Output1">` `select c1, c2, c3 from t1` `where c2 = #{p1} and c3 = #{p2}` `</select>`
单个参数（List）	`parameterType="list"`	通过 `foreach` 来遍历list，也可以通过下标（比如 `list[0]` ）直接访问	`public List<Output1> read(List<Input1> listInput);`	`<select id="read" parameterType="list" resultType="pojo.Output1">` `select c1, c2, c3 from t1` `where (c2, c3) in (values` `<foreach item="item" index="index" collection="list" separator=",">` `(#{item.p1}, #{item.p2})` `</foreach>` `)` `</select>`
单个参数（Map）	`parameterType="map"`	通过key值获取value对象	`public List<Output1> read(Map<String, Object> map);`	`<select id="read" parameterType="map" resultType="pojo.Output1">` `select c1, c2, c3 from t1` `where c1 = #{v1} and (c2, c3) in (values` `<foreach item="item" index="index" collection="v2" separator=",">` `(#{item.p1}, #{item.p2})` `</foreach>` `)` `</select>`
多个参数（位置参数）	N/A	通过位置命名	`public List<Output1> read(int p1, int p2, String p3);`	`<select id="read" resultType="pojo.Output1">` `select c1, c2, c3 from t1` `where c1 = #{param1} and c2 = #{param2} and c3 = #{param3}` `</select>`
多个参数（注解）	N/A	通过注解命名	`public List<Output1> read(@Param("c1")int p1, @Param("p2")Input1 input1);`	`<select id="read" resultType="pojo.Output1">` `select c1, c2, c3 from t1` `where c1 = #{c1} and c2 = #{p2.p1} and c3 = #{p2.p2}` `</select>`