Mybaits动态sql之foreach标签_insert foreach 拼接

Mybaits动态sql之foreach标签

​ 一条insert语句插入多行，查询语句中的in（…），这些sql的参数是数组。使用foreach标签来遍历变量，拼接sql。

一条insert语句插入多行

接口方法

public Integer addUser(@Param("users")List<User> users);
1

sql映射

<!-- insert into t_user(name,age)values ('a1',11),('a2',12),(a3,13) -->		
  		<insert id="addUser">
  			insert 
  				into 
  					t_user
  						(name,age) 
  					values
  					<foreach collection="users" item="user" separator=",">
  						(#{user.name},#{user.age}) 
  					</foreach>
  		</insert>
1
2
3
4
5
6
7
8
9
10
11

测试一下

	@Test
	public void test4() throws IOException {
		InputStream in = Resources.getResourceAsStream("mybatis-cfg.xml");
		SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(in);
		SqlSession session = factory.openSession(true);
		// 获取代理类
		UserMapper dao = session.getMapper(UserMapper.class);
		
		dao.addUser(Arrays.asList(new User("b1",15),new User("b2",16),new User("b3",17)));
		session.close();
	}
1
2
3
4
5
6
7
8
9
10
11

输出打印粘贴

[QC] DEBUG [main] org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(159) | ==>  Preparing: insert into t_user (name,age) values (?,?) , (?,?) , (?,?) 
[QC] DEBUG [main] org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(159) | ==> Parameters: b1(String), 15(Integer), b2(String), 16(Integer), b3(String), 17(Integer)
[QC] DEBUG [main] org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(159) | <==    Updates: 3
1
2
3

查询语句中的in（…）

接口方法

public List<User> query1(@Param("ids") List<Integer> ids);
1

sql映射

  		<select id="query1" resultType="user">
  			select 
  				* 
  			from t_user
  			<!-- select * from t_user where id in ( 1, 2, 3, 4  ) -->
  			<where>
  				<if test="ids != null">
  					id in 
  					<foreach collection="ids" open=" ( " close=" )" item="id" separator=" , ">
  						#{id}
  					</foreach>
  				</if>
  				
  			</where>
  		</select>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

测试一下

	@Test
	public void test2() throws IOException {
		InputStream in = Resources.getResourceAsStream("mybatis-cfg.xml");
		SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(in);
		SqlSession session = factory.openSession(true);
		// 获取代理类
		UserMapper dao = session.getMapper(UserMapper.class);
		List<User> list = dao.query1(Arrays.asList(2,3,4,5,7,9,20,22));
		for (User user2 : list) {
			System.out.println(user2);
		}
		session.close();
	}
1
2
3
4
5
6
7
8
9
10
11
12
13

输出打印粘贴

[QC] DEBUG [main] org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(159) | ==>  Preparing: select * from t_user WHERE id in ( ? , ? , ? , ? , ? , ? , ? , ? ) 
[QC] DEBUG [main] org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(159) | ==> Parameters: 2(Integer), 3(Integer), 4(Integer), 5(Integer), 7(Integer), 9(Integer), 20(Integer), 22(Integer)
[QC] DEBUG [main] org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(159) | <==      Total: 0
1
2
3

项目打包 提取码:9vru 环境:eclipse，maven