mybatis多参数查询

短命女 2024-04-17 17:20 70阅读 0赞

# 一、利用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('%',#{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开始 #