前言
该文章记录了MaBatis动态SQL常用标签。
一、动态SQL 介绍
动态 SQL 是 MyBatis 的强大特性之一。如果你使用过 JDBC 或其它类似的框架，你应该能理解根据不同条件拼接 SQL 语句有多痛苦，例如拼接时要确保不能忘记添加必要的空格，还要注意去掉列表最后一个列名的逗号。利用动态 SQL，可以彻底摆脱这种痛苦。
使用动态 SQL 并非一件易事，但借助可用于任何 SQL 映射语句中的强大的动态 SQL 语言，MyBatis 显著地提升了这一特性的易用性。
如果你之前用过 JSTL 或任何基于类 XML 语言的文本处理器，你对动态 SQL 元素可能会感觉似曾相识。在 MyBatis 之前的版本中，需要花时间了解大量的元素。借助功能强大的基于 OGNL 的表达式，MyBatis 3 替换了之前的大部分元素，大大精简了元素种类，现在要学习的元素种类比原来的一半还要少。
- if
- choose (when, otherwise)
- trim (where, set)
- foreach
动态sql就是：可以通过标签动态的拼接sql命令。
官方文档 介绍的挺不错的，我这边将给出一些动态sql的增删改查实践。
二、动态SQL 案例
该案例实现了用户的增删改查。
1.实体类
实体类：使用了lombok
@Data
@AllArgsConstructor
public class User {
private int id;
private String name;
private String password;
private int age;
private String phone;
private String email;
}
2.接口
UserMapper接口
public interface UserMapper {
int insUser(User u);
int delUserById(int id);
int delUserByIds(String[] id);
int updUser(User u);
User selUserById(int id);
List<User> selUserList(User user);
}
3.xml映射文件
xml映射文件，将动态SQL添加上去了。可以把下面这个当作一个模板来使用
<?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="com.shengjava.mapper.UserMapper">
<!-- 结果映射 -->
<resultMap id="userResultMap" type="User">
<id property="id" column="user_id"/>
<result property="name" column="user_name"/>
<result property="password" column="user_password"/>
<result property="age" column="user_age"/>
<result property="phone" column="user_phone"/>
<result property="email" column="user_email"/>
</resultMap>
<!-- 插入语句 -->
<insert id="insUser" parameterType="User">
insert into user
<!-- 动态拼接 -->
<trim prefix="(" suffix=")" suffixOverrides=",">
<if test="id != null">user_id,</if>
<if test="name != null">user_name,</if>
<if test="password != null">user_password,</if>
<if test="age != null">user_age,</if>
<if test="phone != null">user_phone,</if>
<if test="email != null">user_email</if>
</trim>
<trim prefix="values (" suffix=")" suffixOverrides=",">
<if test="id != null">#{id},</if>
<if test="name != null">#{name},</if>
<if test="password != null">#{password},</if>
<if test="age != null">#{age},</if>
<if test="phone != null">#{phone},</if>
<if test="email != null">#{email},</if>
</trim>
</insert>
<!-- 删除语句 -->
<delete id="delUserById" parameterType="int">
delete
from user
where user_id = #{id};
</delete>
<!-- 删除语句 -->
<delete id="delUserByIds" parameterType="String">
delete
from user
where user_id in
<!-- 动态拼接（遍历） -->
<foreach item="id" collection="array" open="(" separator="," close=")">
#{id}
</foreach>
</delete>
<!-- 修改语句 -->
<update id="updUser" parameterType="User">
update user
<trim prefix="SET" suffixOverrides=",">
<if test="name != null">user_name = #{name},</if>
<if test="password != null">user_password = #{password},</if>
<if test="age != null">user_age = #{age},</if>
<if test="phone != null">user_phone = #{phone},</if>
<if test="email != null">user_email = #{email},</if>
</trim>
where user_id = #{id}
</update>
<!-- sql查询语句片段 -->
<sql id="selUser">
select user_id, user_name, user_password, user_age, user_phone, user_email
from user
</sql>
<!-- 根据id查询用户信息 -->
<select id="selUserById" parameterType="int" resultMap="userResultMap">
<!-- 引入sql语句片段 -->
<include refid="selUser"/>
where user_id = #{id}
</select>
<!-- 查询复合条件的用户列表（如果传入空用户，则查询全部） -->
<select id="selUserList" resultMap="userResultMap" parameterType="User">
<include refid="selUser"/>
<!-- where语句 -->
<where>
<!-- 动态拼接 -->
<if test="id != null ">and user_id = #{id}</if>
<if test="name != null ">and user_name = #{name}</if>
<if test="password != null ">and user_password = #{password}</if>
<if test="age != null ">and user_age = #{age}</if>
<if test="phone != null ">and user_phone = #{phone}</if>
<if test="email != null ">and user_email = #{email}</if>
</where>
</select>
</mapper>
测试类：
测试增删改查，不过需要注意，需要手动提交一下事务。
public class MyBatisTest {
SqlSessionFactory sqlSessionFactory;
@Before
public void before() throws IOException {
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
}
@Test
public void insert() {
try (SqlSession session = sqlSessionFactory.openSession()) {
UserMapper mapper = session.getMapper(UserMapper.class);
int insCount = 0;
User user = new User(101, "zhangsan", "123456", 22, "18812341234", "123456@qq.com");
insCount += mapper.insUser(user);
/*for (int id = 102; id <= 105; id++) {
User u = new User(id, "zhangsan", "123456", 22, "18812341234", "123456@qq.com");
insCount += mapper.insUser(u);
}*/
// 提交事务
session.commit();
System.out.println("成功插入条数:" + insCount);
}
}
@Test
public void deleteById() {
try (SqlSession session = sqlSessionFactory.openSession()) {
UserMapper mapper = session.getMapper(UserMapper.class);
int i = mapper.delUserById(101);
// 提交事务
session.commit();
System.out.println("成功删除条数:" + i);
}
}
@Test
public void deleteByIds() {
try (SqlSession session = sqlSessionFactory.openSession()) {
UserMapper mapper = session.getMapper(UserMapper.class);
String[] ids = {"102", "103", "104", "105"};
int i = mapper.delUserByIds(ids);
// 提交事务
session.commit();
System.out.println("成功删除条数:" + i);
}
}
@Test
public void upd() {
try (SqlSession session = sqlSessionFactory.openSession()) {
UserMapper mapper = session.getMapper(UserMapper.class);
User user = new User(101, "updatezhangsan", "update123456", 55, "update18812341234", "update123456@qq.com");
int i = mapper.updUser(user);
// 提交事务
session.commit();
System.out.println("成功修改条数:" + i);
}
}
@Test
public void select() {
try (SqlSession session = sqlSessionFactory.openSession()) {
UserMapper mapper = session.getMapper(UserMapper.class);
User user = mapper.selUserById(1);
System.out.println(user);
}
}
@Test
public void selectList() {
try (SqlSession session = sqlSessionFactory.openSession()) {
UserMapper mapper = session.getMapper(UserMapper.class);
// 查询全部则直接传null，如果需要查询指定值则创建User对象并赋值即可
List<User> users = mapper.selUserList(null);
System.out.println(users);
}
}
}
参考
官方文档：动态 SQL