mybatis多参数查询

一、利用map封装多个参数

xxxmapper.xml的查询语句（直接用占位符#{key},key就是map的key）

<select id="selectByMap" parameterType="hashmap" resultType="com.mybatis_demo.domain.User">
	select * from t_user where uname like concat('%',#{uname},'%') and age>#{age}
</select>

测试代码

//用map封装多个参数实现多参数查询
	@Test
	public void test6() {
		try {
			//读取配置文件
			InputStream in = Resources.getResourceAsStream("sqlMapConfig.xml");
			//创建sqlSessionFactoryBuilder
			SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
			//利用sqlSessionFactoryBuilder获取sqlSessionFactory
			SqlSessionFactory sqlSessionFactory = builder.build(in);
			//利用sqlSessionFactory获取sqlSeesion
			SqlSession session = sqlSessionFactory.openSession();
			//利用sqlSeesion操作数据库
			Map<String,Object> map = new HashMap<String,Object>();
			map.put("uname", "明");
			map.put("age", 95);
			List<User> list = session.selectList("UserMapper.selectByMap", map);
			for (User user : list) {
				System.out.println(user);
			}
		} catch (IOException e) {
			e.printStackTrace();
		}
	}

二、利用list封装多个参数

xxxmapper.xml的查询语句（通过foreach遍历list集合，其中item代表变量，open表示以"(“开头，separator表示变量之间以”,“隔开，close表示以”)"结尾，即遍历结果是（1，3，4，9）

<select id="selectByList" parameterType="java.util.List" resultType="com.mybatis_demo.domain.User">
	select * from t_user where uid in
	<foreach collection="list" item="item" open="(" separator="," close=")">
		#{item}
	</foreach>
</select>

测试代码：

//多参数查询，用list传递参数，在对应的mapper.xml文件中使用foreach进行遍历
	@Test
	public void test7() {
		try {
			//读取配置文件
			InputStream in = Resources.getResourceAsStream("sqlMapConfig.xml");
			//创建sqlSessionFactoryBuilder
			SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
			//利用sqlSessionFactoryBuilder获取sqlSessionFactory
			SqlSessionFactory sqlSessionFactory = builder.build(in);
			//利用sqlSessionFactory获取sqlSeesion
			SqlSession session = sqlSessionFactory.openSession();
			//利用sqlSeesion操作数据库
			List<Integer> test = new ArrayList<Integer>();
			test.add(1);
			test.add(3);
			test.add(4);
			test.add(9);
			List<User> users = session.selectList("UserMapper.selectByList", test);
			for (User user : users) {
				System.out.println(user);
			}
		} catch (IOException e) {
			e.printStackTrace();
		}
	}

三、如果使用动态代理mapper开发，还有另外一种方法

mapper映射文件

<?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.mybatis_demo.mapper.UserMapper">
<select id="selectByMoreParamter" resultType="com.mybatis_demo.domain.User">
	<!-- select * from t_user where uname like concat('%',#{param1},'%') and age = #{param2} and address = #{param3} -->
	select * from t_user where uname like concat('%',#{arg0},'%') and age = #{arg1} and address = #{arg2}
</select>
</mapper>

mapper接口

public interface UserMapper {
	//mybatis使用mapper动态代理
	
	//4大原则，一个注意
	//1.接口中的方法名需要与对应mapper.xml的id一致
	//2.接口中的返回值需要与对应mapper.xml的返回值类型保持一致
	//3.接口中的参数需要与对应mapper.xml的参数类型、个数、参数名保持一致
	//4.对应mapper.xml的名字空间需要修改成对应接口的全包名
	//注意：mapper动态代理根据返回值类型,mybatis会自动选择调用selectone还是selectlist....

	//直接传多个参数
	public User selectByMoreParamter(String uname,Integer age,String address);
	
}

测试代码

//当传入多个参数时，使用#{arg0}、#{arg1}，arg+下标获取参数，下标从0开始
	//或者使用#{param1},param+下标获取参数下标从1开始
	@Test
	public void test_selectByMoreParamter() {
		try {
			 //读取配置文件
			InputStream in = Resources.getResourceAsStream("sqlMapConfig.xml");
			//创建SqlSessionFactoryBuilder对象，用来获取SqlSessionFactory对象
			SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
			//利用SqlSessionFactoryBuilder对象build一个SqlSessionFactory对象
			SqlSessionFactory build = builder.build(in);
			//利用sqlSessionFactory获取session对象
			SqlSession session = build.openSession();
			//通过session对象获取对应mapper接口
			UserMapper mapper = session.getMapper(UserMapper.class);
			User user = mapper.selectByMoreParamter("白", 4, "北京朝阳区");
			System.out.println(user);
		} catch (IOException e) {
			e.printStackTrace();
		}
	}

总结：用map可以封装多种不同类型的参数，list只能封装同种类型的参数；使用动态代理可以使用arg+下标获取参数，下标从0开始；或者使用param+下标获取参数，下标从1开始