五,MyBatis动态SQL
2021-01-09 本文已影响0人
好多可乐
一,含义:
动态SQL是指根据参数数据动态组织SQL的技术
二,动态SQL的应用场景:
比如淘宝搜索的时候,可以动态的选择比如品牌之类的附加选项进行实时查询
三,配置
- 方法1:
- 注意:配置小于号的时候,不能直接写 <,要写 <
<?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="goods">
<select id="dynamicSQL" parameterType="java.util.Map" resultType="com.imooc.mybatis.entity.Goods">
select * from t_goods
<where>
<if test="categoryId!=null">
and category_id=#{categoryId}>
</if>
<if test="currentPrice!=null">
and current_price < #{currentPrice}
</if>
</where>
</select>
</mapper>
- 方法2:
<?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="goods">
<select id="dynamicSQL" parameterType="java.util.Map" resultType="com.imooc.mybatis.entity.Goods">
select * from t_goods
where
1=1
<if test="categoryId!=null">
and category_id=#{categoryId}>
</if>
<if test="currentPrice!=null">
and current_price < #{currentPrice}
</if>
</select>
</mapper>
我们可以随意更改配置参数,比如上面我们在xml文件里配置了2个查询约束条件,在测试的时候,我们可以对这2个参数进行赋值,也可以不进行赋值,使用十分灵活。
@Test
public void testDynamic() {
SqlSession sqlSession = null;
try {
sqlSession = MyBatisUtils.openSession();
Map map = new HashMap();
// map.put("categoryId", 44);
map.put("currentPrice", 100);
List<Goods> list = sqlSession.selectList("goods.dynamicSQL", map);
for (Goods goods:list){
System.out.println(goods.getGoodsId()+"-"+goods.getTitle()+"-"+goods.getCurrentPrice());
}
sqlSession.commit();
} catch (Exception e) {
sqlSession.rollback();
} finally {
MyBatisUtils.closeSqlSession(sqlSession);
}
}
}