小知识，大挑战！本文正在参与“程序员必备小知识”创作活动。
场景介绍
如果只是简单的CRUD, 可能只是需要一个parameterType就可以了, 当然这个接收参数可以是是自定义类型, 也可以是基本数据类型. 可是当传入的参数个数不确定, 或者根据传入的参数是否有值来决定生成什么样的sql语句, 就需要用到mybatis的动态SQL功能了.
项目中用到的xml
mybatis映射表
<?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.raylee.dao.UserMapper">
<!-- sql语句抽取-->
<sql id="selectUser">select * from users</sql>
<!-- 根据条件判断查询操作-->
<select id="findByCondition" resultType="user" parameterType="user">
<include refid="selectUser"></include>
<where>
<if test="id != 0">
id = #{id}
</if>
<if test="username != null">
and username = #{username}
</if>
<if test="password != null">
and password = #{password}
</if>
</where>
</select>
<!-- 根据集合查询数据-->
<select id="findByIds" parameterType="list" resultType="user">
<include refid="selectUser"></include>
<where>
<foreach collection="list" open="id in (" close=")" item="id" separator=",">
#{id}
</foreach>
</where>
</select>
</mapper>
mybatis配置表
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<!-- 自定义别名-->
<typeAliases>
<typeAlias type="com.raylee.domain.User" alias="user"/>
</typeAliases>
<!-- 数据源环境-->
<environments default="developement">
<environment id="developement">
<transactionManager type="JDBC"></transactionManager>
<dataSource type="POOLED">
<property name="driver" value="com.mysql.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost:3306/LCLDataBase"/>
<property name="username" value="root"/>
<property name="password" value="root"/>
</dataSource>
</environment>
</environments>
<!-- 加载核心配置文件-->
<mappers>
<mapper resource="UserMapper.xml"/>
</mappers>
</configuration>
项目中用到的java类
接口
public interface UserMapper {
public List<User> findByCondition(User user);
public List<User> findByIds(List<Integer> ids);
}
User类
public class User {
private int id;
private String username;
private String password;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
@Override
public String toString() {
return "User{" +
"id=" + id +
", username='" + username + '\'' +
", password='" + password + '\'' +
'}';
}
}
项目中用到的数据库表
动态SQL - if标签
当我们使用普通的select标签进行sql语句查询时, 如果where后面的某个条件为空, 那么造成的后果可能就是查询不到任何结果
mybatis中xml的配置
<select id="findByCondition" resultType="user" parameterType="user">
select * from users where username = #{username} and password = #{password}
</select>
调试代码
InputStream resourceAsStream = Resources.getResourceAsStream("SqlMapConfig.xml");
SqlSessionFactory build = new SqlSessionFactoryBuilder().build(resourceAsStream);
SqlSession sqlSession = build.openSession(true);
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
User user = new User();
user.setId(1);
user.setUsername("shaosiming");
// user.setPassword("123456");
List<User> userList = mapper.findByCondition2(user);
System.out.println(userList);
生成的sql语句和对应的参数
select * from users where id = ? and username = ? and password = ?
1(Integer), shaosiming(String), null
执行的结果
空
预期结果
我们预期是当我们传入的参数有值时, 都会将该条件拼接到where语句后面, 如果没有值, 则不拼接
使用标签来完成这个功能
<!-- 根据条件判断查询操作-->
<select id="findByCondition" resultType="user" parameterType="user">
<include refid="selectUser"></include>
<where>
<if test="id != 0">
and id = #{id}
</if>
<if test="username != null">
and username = #{username}
</if>
<if test="password != null">
and password = #{password}
</if>
</where>
</select>
使用标签后生成的sql语句和参数
当使用标签后, 如果有空的参数, 则不会将该参数生成到sql语句中
select * from users WHERE id = ? and username = ?
1(Integer), shaosiming(String)
使用标签后的结果
[User{id=1, username='shaosiming', password='123456'}]
动态SQL - foreach标签
当我们使用in语句要查询多条数据时, 可能需要传入一个集合当作参数, 这里我们需要用到标签
mybatis中xml的配置
<!-- 根据集合查询数据-->
<select id="findByIds" parameterType="list" resultType="user">
<include refid="selectUser"></include>
<where>
<foreach collection="list" open="id in (" close=")" item="id" separator=",">
#{id}
</foreach>
</where>
</select>
调试代码
InputStream resourceAsStream = Resources.getResourceAsStream("SqlMapConfig.xml");
SqlSessionFactory build = new SqlSessionFactoryBuilder().build(resourceAsStream);
SqlSession sqlSession = build.openSession(true);
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
ArrayList<Integer> ids = new ArrayList<>();
ids.add(1);
ids.add(2);
ids.add(3);
List<User> byIds = mapper.findByIds(ids);
System.out.println(byIds);
生成的sql语句和参数
select * from users WHERE id in ( ? , ? , ? )
1(Integer), 2(Integer), 3(Integer)
执行结果
当前users表中只没有id为3的数据, 因此只查询出来两条
[User{id=1, username='shaosiming', password='123456'}, User{id=2, username='dasiming', password='654321'}]
SQL抽取 - sql标签
<sql id="selectUser">select * from users</sql>
SQL引入 - include标签
<include refid="selectUser"></include>
