MyBatis最佳实践:动态 SQL

第一章：MyBatis 映射文件 SQL 深入

配置 log4j.properrties 配置文件

该配置文件可打印输出 SQL 查询的日志

#将等级为DEBUG的日志信息输出到console和file这两个目的地，console和file的定义在下面的代码
log4j.rootLogger=DEBUG,console,file#控制台输出的相关设置
log4j.appender.console = org.apache.log4j.ConsoleAppender
log4j.appender.console.Target = System.out
log4j.appender.console.Threshold=DEBUG
log4j.appender.console.layout = org.apache.log4j.PatternLayout
log4j.appender.console.layout.ConversionPattern=[%c]-%m%n#文件输出的相关设置
log4j.appender.file = org.apache.log4j.RollingFileAppender
log4j.appender.file.File=./log/kuang.log
log4j.appender.file.MaxFileSize=10mb
log4j.appender.file.Threshold=DEBUG
log4j.appender.file.layout=org.apache.log4j.PatternLayout
log4j.appender.file.layout.ConversionPattern=[%p][%d{yy-MM-dd}][%c]%m%n#日志输出级别
log4j.logger.org.mybatis=DEBUG
log4j.logger.java.sql=DEBUG
log4j.logger.java.sql.Statement=DEBUG
log4j.logger.java.sql.ResultSet=DEBUG
log4j.logger.java.sql.PreparedStatement=DEBUG

动态 SQL 语句之 if 标签：

之前做过拼接 SQL 语句查询条件的查询，需要动态的拼接 SQL 语句

UserMapper 接口的方法

public interface UserMapper {//条件查询public List<User> findByWhere(User user);
}a

UserMapper.xml 配置文件

sql"><!-- 动态 SQL 条件查询 -->
<select id="findByWhere" resultType="com.qcby.model.User">select * from user where 1 = 1<if test="username != null and username != ''">and username like #{username}</if><if test="sex != null and sex != ''">and sex = #{sex}</if>
</select>

测试方法：

@Test
public void test() throws IOException {//加载主配置文件InputStream in = Resources.getResourceAsStream("SqlMapConfig.xml");//创建 SqlSessionFactory 对象SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(in);SqlSession session = factory.openSession();UserMapper userMapper = session.getMapper(UserMapper.class);User user = new User();user.setSex("男");List<User> byWhere = userMapper.findByWhere(user);for (User u : byWhere){System.out.println(u);}session.close();in.close();
}

动态 SQL 语句之 where 标签：

where 标签的目的就是为了去掉 where 1 = 1 的拼接
where 标签使用在 if 标签的外面

代码：

sql"><!-- 动态 SQL 条件查询 where 标签 -->
<select id="findByWhere" resultType="com.qcby.model.User">select * from user <where><if test="username != null and username != ''">and username like #{username}</if><if test="sex != null and sex != ''">and sex = #{sex}</if></where>
</select>

动态 SQL 语句之 foreach 标签：

需求一：

需求的 SQL 语句：select * from user where id = 1 or id = 2 or id = 3

在 User类中添加属性：

public class User implements Serializable {private static final long serialVersionUID = 525400707336671154L;private Integer id;private String username;private Date birthday;private String sex;private String address;private List<Integer> ids;public List<Integer> getIds() {return ids;}public void setIds(List<Integer> ids) {this.ids = ids;}public Integer getId() {return id;}public void setId(Integer id) {this.id = id;}public String getUsername() {return username;}public void setUsername(String username) {this.username = username;}public Date getBirthday() {return birthday;}public void setBirthday(Date birthday) {this.birthday = birthday;}public String getSex() {return sex;}public void setSex(String sex) {this.sex = sex;}public String getAddress() {return address;}public void setAddress(String address) {this.address = address;}@Overridepublic String toString() {return "User{" +"id=" + id +", username='" + username + '\'' +", birthday=" + birthday +", sex='" + sex + '\'' +", address='" + address + '\'' +", ids=" + ids +'}';}
}

UserMapper 接口中添加方法：

//动态 SQL  foreach 循环
public List<User> findByIds(User user);

编写配置文件：

<!-- 动态 SQL  foreach 循环 -->
<select id="findByIds" resultType="com.qcby.model.User">select * from user<where><foreach collection="ids" open="id=" separator="or id =" item="i">#{i}</foreach></where>
</select>

编写测试方法：

@Test
public void FindByIds() throws IOException {//加载配置文件InputStream inputStream = Resources.getResourceAsStream("SqlMapConfig.xml");//创建工厂对象SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(inputStream);//创建 SqlSession 对象SqlSession session = factory.openSession();User user = new User();List<Integer> ids = new ArrayList<>();ids.add(1);ids.add(2);ids.add(44);user.setIds(ids);//查询UserMapper userMapper = session.getMapper(UserMapper.class);List<User> byIds = userMapper.findByIds(user);for(User u: byIds){System.out.println(u);}//关闭资源session.close();inputStream.close();
}

需求二：

需求 SQL： selelct * from user where id in (1，2，3)；

编写配置文件：

<select id="findByIdsIn" parameterType="com.qcby.model.User" resultType="com.qcby.model.User">select * from user<where><foreach collection="ids" open="id in (" separator="," close=")" item="i">#{i}</foreach></where>
</select>

提取公用的 SQL 语句：

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapperPUBLIC "-//mybatis.org//DTD Mapper 3.0//EN""http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.qcby.mapper.UserMapper"><!-- 提取公共的 SQL --><sql id="findAllSQL">select * from user</sql><!-- 编写 sql 语句 --><select id="findAll" parameterType="com.qcby.model.User">/* 引用公共 SQL */<include refid="findAllSQL"></include></select><!-- 使用 where 关键字 --><select id="findByWhere" parameterType="com.qcby.model.User" resultType="com.qcby.model.User"><include refid="findAllSQL"/><where><if test="username != null and username != ''">username = #{username}</if><if test="sex != null and sex != ''">and sex = #{sex}</if></where></select><!-- 使用 foreach 语句--><select id="findByIds" parameterType="com.qcby.model.User" resultType="com.qcby.model.User"><include refid="findAllSQL"></include><where><foreach collection="ids" open="id in (" separator="," close=")" item="i">#{i}</foreach></where></select></mapper>