前言:
在业务比较复杂的情况下,我们通常需要去拼接SQL语句来完成相关操作,有过这方面开发经验的同学,一定可以体会到大量的手动拼接SQL有多么痛苦,而且出错率很高。没关系,MyBatis有一个非常方便且强大的功能就是动态SQL,使用动态SQL,可以摆脱手动拼SQL的痛苦,接下来我们就一起来学习如何使用MyBatis动态SQL。
代码:
我们通过对user对象的操作来举例说明。
User实体类
package com.southwind.entity;
import java.util.List;
public class User{
private int id;
private String username;
private String password;
private int age;
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = 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 int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
@Override
public String toString() {
return "User [id=" + id + ", username=" + username + ", password="
+ password + ", age=" + age + "]";
}
}
config.xml
<?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>
<!-- 设置settings -->
<settings>
<!-- 打印SQL -->
<setting name="logImpl" value="STDOUT_LOGGING"/>
</settings>
<!-- 实体类设置别名 -->
<typeAliases>
<typeAlias type="com.southwind.entity.User" alias="user"/>
</typeAliases>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC" />
<dataSource type="POOLED">
<property name="driver" value="com.mysql.jdbc.Driver" />
<property name="url" value="jdbc:mysql://localhost:3306/mybatis?useUnicode=true&characterEncoding=UTF-8" />
<property name="username" value="root" />
<property name="password" value="root" />
</dataSource>
</environment>
</environments>
<mappers>
<mapper resource="com/southwind/dao/UserDAO.xml"/>
</mappers>
</configuration>
UserDAO
package com.southwind.dao;
import com.southwind.entity.User;
public interface UserDAO {
public User get(User user);
}
UserDAO.xml
<?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.southwind.dao.UserDAO">
<select id="get" parameterType="user" resultType="user">
select * from t_user where id = #{id} and username = #{username}
and password = #{password} and age = #{age}
</select>
</mapper>
测试:
package com.southwind.test;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import com.southwind.dao.UserDAO;
import com.southwind.entity.User;
public class Test {
public static void main(String[] args) {
InputStream is = Test.class.getClassLoader().getResourceAsStream("config.xml");
SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
SqlSessionFactory sqlSessionFactory = sqlSessionFactoryBuilder.build(is);
SqlSession sqlSession = sqlSessionFactory.openSession();
UserDAO userDAO = sqlSession.getMapper(UserDAO.class);
//创建参数
User user = new User();
user.setId(1);
user.setUsername("张三");
user.setPassword("123");
user.setAge(33);
User user2 = userDAO.get(user);
System.out.println(user2);
}
}
数据库表记录
很显然,参数user的属性完全匹配数据库第一条记录,是可以查询出结果的。
现在对代码进行修改,去掉user的password属性赋值操作,通过id,username,age三个字段去匹配,理想的结果是同样可以查询出数据库的第一条记录。
package com.southwind.test;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import com.southwind.dao.UserDAO;
import com.southwind.entity.User;
public class Test {
public static void main(String[] args) {
InputStream is = Test.class.getClassLoader().getResourceAsStream("config.xml");
SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
SqlSessionFactory sqlSessionFactory = sqlSessionFactoryBuilder.build(is);
SqlSession sqlSession = sqlSessionFactory.openSession();
UserDAO userDAO = sqlSession.getMapper(UserDAO.class);
//创建参数
User user = new User();
user.setId(1);
user.setUsername("张三");
user.setAge(33);
User user2 = userDAO.get(user);
System.out.println(user2);
}
}
但是结果为null,什么原因呢?因为SQL语句where条件使用的是and关键字进行连接,所有条件必须同时满足,我们可以看到此时的SQL语句:
"select * from t_user where id = 1 and username = "张三" and password = null and age = 33;"
显然这条SQL语句是查询不出任何结果的。
现在针对这种情况进行优化,判断user对象,如果password属性值不为null,则SQL语句添加password的判断,如果password属性为null,则不添加。
我们可以使用动态SQL来完成上述操作。
if标签
<select id="get" parameterType="user" resultType="user">
select * from t_user where
<if test="id!=0">
id = #{id}
</if>
<if test="username!=null">
and username = #{username}
</if>
<if test="password!=null">
and password = #{password}
</if>
<if test="age!=0">
and age = #{age}
</if>
</select>
结构非常清晰,就是一个if流程控制。
再次测试:
可以看到,成功查询出数据库第一条记录。
但是这种方式存在一个漏洞,对代码做如下修改,去掉id属性的赋值。
package com.southwind.test;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import com.southwind.dao.UserDAO;
import com.southwind.entity.User;
public class Test {
public static void main(String[] args) {
InputStream is = Test.class.getClassLoader().getResourceAsStream("config.xml");
SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
SqlSessionFactory sqlSessionFactory = sqlSessionFactoryBuilder.build(is);
SqlSession sqlSession = sqlSessionFactory.openSession();
UserDAO userDAO = sqlSession.getMapper(UserDAO.class);
//创建参数
User user = new User();
user.setUsername("张三");
user.setPassword("123");
user.setAge(33);
User user2 = userDAO.get(user);
System.out.println(user2);
}
}
程序直接报错了,并且是SQL语句的错误。为什么会这样呢?我们来分析动态SQL代码,现在没有给id赋值,即id==0,所以"id=#{id}"这段代码不会添加到SQL语句中,那么最终拼接好的动态SQL是这样的:"select * from t_user where and username = ? and password = ? and age = ?;"
where后面直接跟and,很明显的语法错误。
所以此时的代码还不够智能,应该根据user参数的属性值自动决定是否要添加and关键字,即当"id = #{id}"不出现在SQL语句中时,"and password = #{password}"应该自动删除and关键字,如何做到呢?添加where标签即可。
where标签
<select id="get" parameterType="user" resultType="user">
select * from t_user
<where>
<if test="id!=0">
id = #{id}
</if>
<if test="username!=null">
and username = #{username}
</if>
<if test="password!=null">
and password = #{password}
</if>
<if test="age!=0">
and age = #{age}
</if>
</where>
</select>
测试:
查询成功,SQL语句自动删除了不需要的and关键字,所以一般if标签和where标签会组合起来使用。
choose,when标签
choose,when标签和if标签用法很类似。
<select id="get" parameterType="user" resultType="user">
select * from t_user
<where>
<choose>
<when test="id!=0">
id = #{id}
</when>
<when test="username!=null">
and username = #{username}
</when>
<when test="password!=null">
and password = #{password}
</when>
<when test="age!=0">
and age = #{age}
</when>
</choose>
</where>
</select>
trim标签
trim标签中的prefix和suffix属性会被用于生成实际的SQL语句,会和标签内部的语句拼接。如果语句的前面或后面遇到prefixOverrides或suffixOverrides属性中指定的值,MyBatis会自动将它们删除。在指定多个值的时候,别忘了每个值后面都要有一个空格,保证不会和后面的SQL连接在一起。
<select id="get" parameterType="user" resultType="user">
select * from t_user
<trim prefix="where" prefixOverrides="and">
<if test="id!=0">
id = #{id}
</if>
<if test="username!=null">
and username = #{username}
</if>
<if test="password!=null">
and password = #{password}
</if>
<if test="age!=0">
and age = #{age}
</if>
</trim>
</select>
set标签
set标签用于update操作,会自动根据参数选择生成SQL语句。
UserDAO.xml
<update id="update" parameterType="user">
update t_user
<set>
<if test="username!=null">
username = #{username},
</if>
<if test="password!=null">
password = #{password},
</if>
<if test="age!=0">
age = #{age}
</if>
</set>
where id = #{id}
</update>
UserDAO
package com.southwind.dao;
import com.southwind.entity.User;
public interface UserDAO {
public int update(User user);
}
测试:
package com.southwind.test;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import com.southwind.dao.UserDAO;
import com.southwind.entity.User;
public class Test {
public static void main(String[] args) {
InputStream is = Test.class.getClassLoader().getResourceAsStream("config.xml");
SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
SqlSessionFactory sqlSessionFactory = sqlSessionFactoryBuilder.build(is);
SqlSession sqlSession = sqlSessionFactory.openSession();
UserDAO userDAO = sqlSession.getMapper(UserDAO.class);
User user = new User();
user.setId(1);
user.setUsername("大明明");
user.setAge(22);
System.out.println(userDAO.update(user));
sqlSession.commit();
}
}
可以看到,user参数只设置了username和age属性,所以动态生成的SQL语句就没有包含对password的修改。
foreach标签
foreach标签可以迭代生成一系列值,这个标签主要用于SQL的in语句。
修改User实体类,添加一个List<Integer>类型的属性ids。
package com.southwind.entity;
import java.util.List;
public class User{
private int id;
private String username;
private String password;
private int age;
private List<Integer> ids;
public List<Integer> getIds() {
return ids;
}
public void setIds(List<Integer> ids) {
this.ids = ids;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = 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 int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
@Override
public String toString() {
return "User [id=" + id + ", username=" + username + ", password="
+ password + ", age=" + age + "]";
}
}
UserDAO.xml
<select id="get" parameterType="user" resultType="user">
select * from t_user
<where>
<foreach collection="ids" open="id in (" close=")" item="id" separator=",">
#{id}
</foreach>
</where>
</select>
测试:
package com.southwind.test;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import com.southwind.dao.UserDAO;
import com.southwind.entity.User;
public class Test {
public static void main(String[] args) {
InputStream is = Test.class.getClassLoader().getResourceAsStream("config.xml");
SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
SqlSessionFactory sqlSessionFactory = sqlSessionFactoryBuilder.build(is);
SqlSession sqlSession = sqlSessionFactory.openSession();
UserDAO userDAO = sqlSession.getMapper(UserDAO.class);
User user = new User();
List<Integer> ids = new ArrayList<Integer>();
ids.add(1);
ids.add(2);
ids.add(3);
ids.add(4);
ids.add(5);
user.setIds(ids);
List<User> list = userDAO.get2(user);
for(User item : list){
System.out.println(item);
}
}
}
可以看到,根据user参数的ids属性中id的个数,动态生成了SQL语句,查询出结果。