mybatis动态sql

1、if标签

<select id="queryUserByWhere" resultType="User" parameterType="User">
	select * from user
	<if test="sex != null and sex != ''">
		where sex == #{sex}
	</if>
</select>

2、where标签

where标签可以自动添加where关键字，还可以删除第一个and关键字。

<select id="queryUserByWhere" resultType="User" parameterType="User">
	select * from user
	<where>
		<if test="sex != null and sex != ''">
			and sex = #{sex}
		</if>
		<if test="username!=null and username!=''">
			and username like '%${username}%'
		</if>
	</where>
</select>

3、foreach标签

userMapper.xml:

<!-- 测试forEach标签 -->
<select id="queryUserByIds" parameterType="QueryVo" resultType="User">
	select * from user
	<where>
		<foreach collection="ids" item="item" open="id in(" close=")" separator=",">
			#{item}
		</foreach>
	</where>
</select>

如果传递的参数是对象，则collection写对象中的属性名

如果传递的参数是list，collection中就写“list”

如果传递的参数是数组，collection中就写“array”

 

QueryVo类：用于传递参数：

package club.ityuchao.pojo;

import java.util.List;

public class QueryVo {

	List<Integer> ids;

	public List<Integer> getIds() {
		return ids;
	}

	public void setIds(List<Integer> ids) {
		this.ids = ids;
	}
	
}

测试代码：

@Test
public void test2() throws IOException {
	//创建SqlSessionFactory
	InputStream inputStream = Resources.getResourceAsStream("sqlMapConfig.xml");
	SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
	SqlSessionFactory sqlSessionFactory = sqlSessionFactoryBuilder.build(inputStream);
		
	//获得session
	SqlSession sqlSession = sqlSessionFactory.openSession();
		
	UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
		
	//新建一个Integer集合
	List<Integer> ids = new ArrayList<>();
	ids.add(1);
	ids.add(10);
	ids.add(16);
	//创建一个传递参数的类
	QueryVo queryVo = new QueryVo();
	queryVo.setIds(ids);
	//执行sql语句查询
	List<User> list = userMapper.queryUserByIds(queryVo);
	for (User user : list) {
		System.out.println(user);
	}
		
	//关闭session
	sqlSession.close();
		
}

4、sql片段

Sql中可将重复的sql提取出来，使用时用include引用即可，最终达到sql重用的目的。

<!-- 声明片段 -->
<sql id="userColumn">
	id, username, birthday, sex, address
</sql>
	
<!-- 使用sql片段 -->
<select id="queryUserByWhere" parameterType="user" resultType="user">
	select <include refid="userColumn"/> from user
</select>

5、set标签

动态添加set关键字，可以删除最后一个逗号

<!-- 更新customer -->
<update id="updateCustomer" parameterType="Customer">
	update customer 
	<set>
		<if test="cust_name != null">
			cust_name = #{cust_name},
		</if>
		<if test="cust_source != null">
			cust_source = #{cust_source},
		</if>
		<if test="cust_industry != null">
			cust_industry = #{cust_industry},
		</if>
		<if test="cust_level != null">
			cust_level = #{cust_level},
		</if>
		<if test="cust_linkman != null">
			cust_linkman = #{cust_linkman},
		</if>
		<if test="cust_phone != null">
			cust_phone = #{cust_phone},
		</if>
		<if test="cust_mobile != null">
			cust_mobile = #{cust_mobile},
		</if>
		<if test="cust_zipcode != null">
			cust_zipcode = #{cust_zipcode},
		</if>
		<if test="cust_address != null">
			cust_address = #{cust_address}
		</if>
	</set>
	where cust_id = #{cust_id}
</update>