通过mybatis 提供的各种标签方法实现动态拼接 sql
1、if 标签
格式:
<if test=" ">
</if>
需求:根据条件查询用户
数据库和POJO 类的创建就省略了,直接写Mapper.xml 和 mapper 的接口文件
<!-- 根据性别和名字查询用户-->
<select id="queryUserByWhere" parameterType="User" resultType="User">
select id, username, birthday, sex, address 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>
// 根据条件查询用户信息
public List<User> queryUserByWhere(User user);
测试
//根据性别和名字查询用户
@Test
public void queryUserByWhere() throws Exception {
// 创建SqlSessionFactory
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder()
.build(Resources.getResourceAsStream("sqlMapConfig.xml"));
SqlSession session = sqlSessionFactory.openSession();
UserMapper userMapper = session.getMapper(UserMapper.class);
User user = new User();
user.setUsername("王");
user.setSex("男");
List<User> list = userMapper.queryUserByWhere(user);
for (User u : list) {
System.out.println(u);
}
}
2、Where标签
格式:
<where>
</where>
可参考上面实例
3、sql 片段
格式:
<sql id=" ">
</sql>
sql 中可以将重复的sql 提取出来,使用时用 include 引用即可,达到 sql 重用的目的即可。
例如可以将上面的 id, username, birthday, sex, address 提取出来,作为 sql 片段。
<sql id="userFields">
id, username, birthday, sex, address
</sql>
<!-- 根据性别和名字查询用户-->
<!-- 声明sql片段 -->
<sql id="userFields">
id, username, birthday, sex, address
</sql>
<select id="queryUserByWhere" parameterType="User" resultType="User">
select <include refid="userFields"/> 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>
4、foreach 标签
<foreach collection=" " item=" " open=" " separator=" " >
</foreach>
需求:根据多个 id 查找用户
Mapper.xml 和 mapper 接口文件如下:
<select id="queryUserByIds" parameterType="QueryVo" resultType="User">
select <include refid="userFields"/> from user
<where>
<foreach collection="ids" item="item" open="id in(" close=")" separator=",">
#{item}
</foreach>
</where>
</select>
//根据多个id查询用户信息
public List<User> queryUserByIds(QueryVo vo);
测试文件
@Test
public void queryUserByIds() throws Exception {
// 创建SqlSessionFactory
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder()
.build(Resources.getResourceAsStream("sqlMapConfig.xml"));
SqlSession session = sqlSessionFactory.openSession();
UserMapper userMapper = session.getMapper(UserMapper.class);
QueryVo vo = new QueryVo();
List<Integer> ids = new ArrayList<Integer>();
ids.add(1);
ids.add(10);
ids.add(16);
ids.add(22);
vo.setIds(ids);
List<User> list = userMapper.queryUserByIds(vo);
for (User u : list) {
System.out.println(u);
}
}
还有一些标签,就不一一列举了,大家可以去查看官方文档学习了解。