动态SQL的思想:就是使用不同的动态SQL标签去完成字符串的拼接处理。
解决的问题是:
1、 在映射文件中,会编写很多有重叠部分的SQL语句,比如SELECT语句和WHERE语句等这些重叠语句,该如何处理
2、 如果页面传递过来一个参数,但是SQL语句中的条件有多个,此时会发生问题
1 if标签
UserMapper.xml
<select id="searchUser" parameterType="User" resultType="User">
SELECT * FROM user where 1=1
<if test="username != null and username != ''">
AND username like '%${username}%'
</if>
</select>
测试
@Test
public void testSearchUsers(){
SqlSession sqlSession = null;
try {
sqlSession = sqlSessionFactory.openSession();
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
User su = new User();
su.setUsername("小");
List<User> userList = userMapper.searchUser(su);
for (User u:userList)
System.out.println(u);
}catch (Exception e){
e.printStackTrace();
}finally {
if (sqlSession!=null)
sqlSession.close();
}
}
User{id=12, username='小小', password='111', email='1@com', phone='123', address='sasas', ordersList=null}
User{id=17, username='小', password='111', email='1@com', phone='123', address='sasas', ordersList=null}
User{id=20, username='小xiao小', password='111', email='1@com', phone='123', address='sasas', ordersList=null}
2 where 标签
上面sql中的1=1,虽然可以保证sql语句的完整性:但是存在性能问题。Mybatis提供where标签解决该问题。
<select id="searchUser" parameterType="User" resultType="User">
SELECT * FROM user
<where>
<if test="username != null and username != ''">
AND username like '%${username}%'
</if>
</where>
</select>
3 sql片段
可以把重复利用的sql提取出来,用include标签引用。
如果引用其它mapper.xml的sql片段,则在引用时需要加上namespace,如下:<include refid="namespace.sql片段”/>
<sql id="query_user_where">
<if test="username != null and username != ''">
AND username like '%${username}%'
</if>
</sql>
<select id="searchUser" parameterType="User" resultType="User">
SELECT * FROM user
<where>
<include refid="query_user_where"/>
</where>
</select>
4 foreach
包装类QueryVo
public class QueryVo {
private User user;
List<Integer> ids;
}
UserMapper.xml
<sql id="query_user_where">
<if test="user != null">
<if test="user.username != null and user.username != ''">
AND username like '%${user.username}%'
</if>
</if>
<if test="ids != null and ids.size() > 0">
<!-- collection:指定输入的集合参数的参数名称 -->
<!-- item:声明集合参数中的元素变量名 -->
<!-- open:集合遍历时,需要拼接到遍历sql语句的前面 -->
<!-- close:集合遍历时,需要拼接到遍历sql语句的后面 -->
<!-- separator:集合遍历时,需要拼接到遍历sql语句之间的分隔符号 -->
<foreach collection="ids" item="id" open=" AND id IN ( "
close=" ) " separator=",">
#{id}
</foreach>
</if>
</sql>
<select id="searchUserByVo" parameterType="queryVo" resultType="user">
SELECT *FROM user
<where>
<include refid="query_user_where"/>
</where>
</select>
如果parameterType不是POJO类型,而是List或者Array的话,那么foreach语句中,collection属性值需要固定写死为list或者array。
测试
@Test
public void testSearchUsersByVo(){
SqlSession sqlSession = null;
try {
sqlSession = sqlSessionFactory.openSession();
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
User su = new User();
su.setUsername("小");
QueryVo queryVo = new QueryVo();
queryVo.setUser(su);
List list = Arrays.asList(new Integer[]{1,12,17});
queryVo.setIds(list);
List<User> userList = userMapper.searchUserByVo(queryVo);
for (User u:userList)
System.out.println(u);
}catch (Exception e){
e.printStackTrace();
}finally {
if (sqlSession!=null)
sqlSession.close();
}
}