动态sql

1.mybatis核心对sql语句进行灵活操作，通过表达式进行判断，对sql进行灵活拼接，组装.

2.需求

1.用户信息综合查询列表和用户信息查询雷彪总数这两个statement德尔定义使用动态sql
2.对查询条件进行判断，如果输入参数不为空才进行查询条件拼接

eg

<!-- 查询用户总数 -->
	<!-- <select id="findUserCount" parameterType="cn.mybatis.po.UserQueryVo" resultType="int">
		select count(*) from user where user.sex = #{userCustom.sex} and user.username like '%${userCustom.username}%'
	</select> -->
修改后
<!-- 查询用户总数 -->
	<select id="findUserCount" parameterType="cn.mybatis.po.UserQueryVo" resultType="int">
		select count(*) from user
		<!-- where 可以自动去掉第一个and -->
		<where>
			<if test="userCustom!=null">
				<if test="userCustom.sex!=null and userCustom.sex !=''">
					and user.sex = #{userCustom.sex}
				</if>
				<if test="userCustom.username!=null and userCustom.username !=''">
					and user.username like '%${userCustom.username}%'
				</if>
			</if>
		</where>
	</select>

3.sql片段（多段重复 共用）

eg

<!-- 定义sql片段
	id 是唯一标识 -->
<sql id="query_user_where">
	<if test="userCustom!=null">
		<if test="userCustom.sex!=null and userCustom.sex !=''">
			and user.sex = #{userCustom.sex}
		</if>
		<if test="userCustom.username!=null and userCustom.username !=''">
			and user.username like '%${userCustom.username}%'
		</if>
	</if>
</sql>

2.使用sql片段用include标签 使用sql片段

<select id="findUserList" parameterType="cn.mybatis.po.UserQueryVo" resultType="cn.mybatis.po.UserCustom">
		select * from user
		<!-- where 可以自动去掉第一个and -->
		<where>
			<!-- 使用include 标签 引用sql片段 refid 关联
				如果引用的refid不在mapper文件中 则需要指定的namespase -->
			<include refid="query_user_where"></include>
		</where>
	</select>

4.foreach

向sql传递数组组成list mybatis 使用foreach解析 
需求 在用户查询列表和查询总数的statement中增加多个id输入查询

eg
1.添加多个id属性

public class UserQueryVo {
	
	//传入多个id
	private List<Integer> ids;
	
	//包装查询条件
	private UserCustom userCustom;
	

	public UserCustom getUserCustom() {
		return userCustom;
	}

	public void setUserCustom(UserCustom userCustom) {
		this.userCustom = userCustom;
	}

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

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

2.sql片段添加

<!-- 定义sql片段
		id 是唯一标识 -->
	<sql id="query_user_where">
		<if test="ids != null">
			<!-- collection集合 -->
			<!-- <foreach collection="ids" item="user_id" open="and(" close=")" separator="or">
				遍历每个需要拼接的串
				id = #{user_id}
			</foreach> -->
			<!-- 第二种方式 -->
			<foreach collection="ids" item="user_id" open="and id in (" close=")" separator=",">
				#{user_id}
			</foreach>
		</if>
	</sql>

3.测试类

@Test
public void testFindUserCount() throws Exception{
	//获取Session
	SqlSession session = sqlSessionFactory.openSession();
	//获取Mapper代理对象
	UserMapper userMapper = session.getMapper(UserMapper.class);

	UserQueryVo queryVo = new UserQueryVo();
	UserCustom userCustom = new UserCustom();
	//sql动态测试
	userCustom.setSex("1");
	//userCustom.setUsername("明");
	queryVo.setUserCustom(userCustom);
	
	List<Integer> ids = new ArrayList<Integer>();
	ids.add(10);
	ids.add(22);
	queryVo.setIds(ids);
	
	int count = userMapper.findUserCount(queryVo);
	
	System.out.println(count);
	
	session.close();
}

5.list查询

1.接口方法

public List<User> selectUserByList(List userlist) throws Exception;

2.mapper映射

<!-- list放置user对象 -->
<select id="selectUserByList" parameterType="java.util.List" resultType="user">
		select * from user
		<where>
			<if test="list != null">
				<foreach collection="list" item="item" open="and id in(" close=")" separator=",">
					#{item.id}
				</foreach>
			</if>
		</where> 
	</select>

3.测试类

@Test
public void testselectUserByList() throws Exception{
	//获取Session
	SqlSession session = sqlSessionFactory.openSession();
	//获取Mapper代理对象
	UserMapper userMapper = session.getMapper(UserMapper.class);

	List<User> userList = new ArrayList<User>();
	User user = new User();
	user.setId(1);
	userList.add(user);
	
	user = new User();
	user.setId(10);
	userList.add(user);
	
	List<User> list = userMapper.selectUserByList(userList);
	
	System.out.println(list);
	
	session.close();
}

传递数组综合查询用户信息

传递pojo

传递单个数组 （数组中传递的字符串）

修改list中的

parameterType="Object[]"
<if test="array != null">