MyBatis-动态SQL
阅读 (325337)1、MyBatis动态sql
1.1、什么是动态sql
Mybatis 的映射文件中,前面我们的 SQL 都是比较简单的,有些时候业务逻辑复杂时,我们的 SQL 是动态变化的。
在实际应用开发过程中,我们往往需要写复杂的 SQL 语句,需要拼接,而拼接SQL语句又稍微不注意,由于引号,空格等缺失可能都会导致错误。
Mybatis提供了动态SQL,也就是可以根据用户提供的参数,动态决定查询语句依赖的查询条件或SQL语句的内容。
1.2、开发前的准备
新建项目:mb003
完成基础配置
2、if标签-上
if 标签通常用于 WHERE 语句、UPDATE 语句、INSERT 语句中,通过判断参数值来决定是否使用某个查询条件、判断是否更新某一个字段、判断是否插入某个字段的值。
2.1、需求分析
多条件查询:根据用户名和住址查询用户信息
2.2、案例实现
2.2.1、编写IUserDao.java文件
package com.tianyi.dao;
import com.tianyi.javabean.QueryUser;
import com.tianyi.javabean.User;
import java.util.List;
public interface IUserDao {
List<User> findByUsers(User user);
}
2.2.2、编写IUserDao.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.tianyi.dao.IUserDao">
<select id="findByUsers" resultType="com.tianyi.javabean.User" parameterType="com.tianyi.javabean.User">
select * from user where 1=1
<if test="username!=null and username !=''">
and username like #{username}
</if>
<if test="address!=null and address !=''">
and address like #{address}
</if>
</select>
</mapper>
3、if标签-下
3.1、案例实现
3.1.1、where 1=1作用
A、where 的条件为永真
select * from table1 where 1=1
与
select * from table1
完全没有区别,甚至还有其他许多写法,1<>2,‘a’=’a’,‘a’<>’b’,
其目的就只有一个,where 的条件为永真,得到的结果就是未加约束条件的。
在SQL注入时会用到这个,例如
select * from table1 where name=’lala’
给强行加上
select * from table1 where name=’lala’ or 1=1
这就又变成了无约束的查询了。
B、在后台写不定数量的查询条件下,便于规范语句,增加灵活性
例如一个查询可能有name,age,class约束,也可能没有,那该如何处理呢?
以java为例:
String sql = “select * from table a”;
前台传来的过滤条件中name, age, class 不一定都存在
B1、在不使用where 1=1的情况下
if(params.containsKey("name")){
String key = params.get("name").toString();
sql+="where a.name='"+key +"'";
}
if(params.containsKey("age")){
String key = params.get("age").toString();
sql+="where a.age='"+key +"'";
}
if(params.containsKey("class")){
String key = params.get("class ").toString();
sql+="where a.class ='"+key +"'";
}
这样同时存在两个属性及以上就会发生冲突
B2、当时用where 1=1 的时候
String sql = “select * from table a where 1=1”;
if(params.containsKey("name")){
String key = params.get("name").toString();
sql+=" and a.name='"+key +"'";
}
if(params.containsKey("age")){
String key = params.get("age").toString();
sql+=" and a.age='"+key +"'";
}
if(params.containsKey("class ")){
String key = params.get("class ").toString();
sql+=" and a.class ='"+key +"'";
}
这样同时存在两个属性及以上时就不会发生冲突
3.1.2、编写MbTest.java文件
package com.tianyi.test;
import com.tianyi.dao.IUserDao;
import com.tianyi.javabean.QueryUser;
import com.tianyi.javabean.User;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import java.io.InputStream;
import java.util.Date;
import java.util.List;
public class MbTest {
private InputStream in ;
private SqlSessionFactory factory;
private SqlSession session;
private IUserDao userDao;
@Test
public void findByUsers(){
User u=new User();
u.setUsername("王%");
u.setAddress("%昌平%");
List<User> users=userDao.findByUsers(u);
for(User user: users){
System.out.println(user);
}
}
@Before
//在测试方法执行之前执行
public void init()throws Exception {
//1.读取配置文件
in = Resources.getResourceAsStream("SqlMapConfig.xml");
//2.创建构建者对象
SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
//3.创建 SqlSession 工厂对象
factory = builder.build(in);
// 4.创建 SqlSession 对象
session = factory.openSession(true);
//5.创建 Dao 的代理对象
userDao = session.getMapper(IUserDao.class);
}
@After//在测试方法执行完成之后执行
public void destroy() throws Exception{
//session.commit();
//7.释放资源
session.close();
in.close();
}
}
4、where标签
为了简化上面where 1=1的条件拼装,我们可以采用
4.1、需求分析
多条件查询:根据用户名和住址查询用户信息
4.2、案例实现
4.2.1、修改IUserDao.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.tianyi.dao.IUserDao">
<select id="findByUsers" resultType="com.tianyi.javabean.User" parameterType="com.tianyi.javabean.User">
select * from user
<where>
<if test="username!=null and username !=''">
and username like #{username}
</if>
<if test="address!=null and address !=''">
and address like #{address}
</if>
</where>
</select>
</mapper>
5、choose 标签
有时候我们并不想应用所有的条件,而只是想从多个选项中选择一个。MyBatis 提供了 choose 元素,按顺序判断 when 中的条件出否成立,如果有一个成立,则 choose 结束。当 choose 中所有 when的条件都不满则时,则执行 otherwise 中的 sql。类似于 Java 的 switch 语句,choose 为 switch,when 为 case,otherwise 则为 default。
if 是与(and)的关系,而 choose 是或(or)的关系。
5.1、需求分析
多条件查询:根据用户名、住址查询、性别查询用户信息
5.2、案例实现
5.2.1、修改IUserDao.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.tianyi.dao.IUserDao">
<select id="findByUsers" resultType="com.tianyi.javabean.User" parameterType="com.tianyi.javabean.User">
select * from user
<where>
<choose>
<when test="username!=null and username !=''">
and username like #{username}
</when>
<when test="address!=null and address !=''">
and address like #{address}
</when>
<otherwise>
and sex="男"
<otherwise>
</choose>
</where>
</select>
</mapper>
6、foreach标签
foreach 标签主要用于构建 in 条件,可在 sql 中对集合进行迭代。也常用到批量删除、添加等操作中。
collection:collection 属性的值有三个分别是 list、array、map 三种,分别对应的参数类型为:List、数组、map 集合。 item :表示在迭代过程中每一个元素的别名 index :表示在迭代过程中每次迭代到的位置(下标) open :前缀 close :后缀 separator :分隔符,表示迭代时每个元素之间以什么分隔
6.1、需求分析
范围查询时:根据多个id(1,5,15)查询用户信息
6.2、案例实现
6.2.1、List封装参数id,编写IdsList.java文件
package com.tianyi.javabean;
import java.io.Serializable;
import java.util.List;
public class IdsList implements Serializable {
private List<Integer> ids;
public List<Integer> getIds() {
return ids;
}
public void setIds(List<Integer> ids) {
this.ids = ids;
}
}
6.2.2、编写IUserDao.java文件
package com.tianyi.dao;
import com.tianyi.javabean.IdsList;
import com.tianyi.javabean.QueryUser;
import com.tianyi.javabean.User;
import java.util.List;
public interface IUserDao {
List<User> findByIds(IdsList ids);
}
6.2.3、编写IUserDao.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.tianyi.dao.IUserDao">
<select id="findByIds" resultType="com.tianyi.javabean.User" parameterType="com.tianyi.javabean.IdsList">
select * from user
<where>
<if test="ids!=null and ids.size()>0 ">
<foreach collection="ids" open="id in (" close=")" separator="," item="ids" >
#{ids}
</foreach>
</if>
</where>
</select>
</mapper>
6.2.4、编写MbTest.java文件
package com.tianyi.test;
import com.tianyi.dao.IUserDao;
import com.tianyi.javabean.IdsList;
import com.tianyi.javabean.QueryUser;
import com.tianyi.javabean.User;
import com.tianyi.javabean.User_old;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
public class MbTest {
private InputStream in ;
private SqlSessionFactory factory;
private SqlSession session;
private IUserDao userDao;
@Test
public void findByIds(){
IdsList ids =new IdsList();
List<Integer> id =new ArrayList();
id.add(14);
id.add(17);
id.add(19);
ids.setIds(id);
List<User> users=userDao.findByIds(ids);
for(User user: users){
System.out.println(user.getId()+":"+user.getUsername());
}
}
@Before
//在测试方法执行之前执行
public void init()throws Exception {
//1.读取配置文件
in = Resources.getResourceAsStream("SqlMapConfig.xml");
//2.创建构建者对象
SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
//3.创建 SqlSession 工厂对象
factory = builder.build(in);
// 4.创建 SqlSession 对象
session = factory.openSession(true);
//5.创建 Dao 的代理对象
userDao = session.getMapper(IUserDao.class);
}
@After//在测试方法执行完成之后执行
public void destroy() throws Exception{
//session.commit();
//7.释放资源
session.close();
in.close();
}
}
7、set标签
没有使用 if 标签时,如果有一个参数为 null,都会导致错误。当在 update 语句中使用 if 标签时,如果最后的 if 没有执行,则或导致逗号多余错误。使用 set 标签可以将动态的配置 set关键字,和剔除追加到条件末尾的任何不相关的逗号。
7.1、需求分析
修改我们的指定记录
7.2、案例实现
7.2.1、编写IUserDao.java
import java.util.List;
public interface IUserDao {
int updateUser(User u);
}
7.2.2、编写IUserDao.xml
<update id="updateUser" parameterType="com.tianyi.javabean.User">
update user set username=#{username},birthday=#{birthday},sex=#{sex},address=#{address} where id=#{id}
</update>
if标签
<update id="updateUser" parameterType="com.tianyi.javabean.User">
update user set
<if test="username!=null and username!='' ">
username=#{username},
</if>
<if test="birthday.toString()!=null and birthday.toString()!='' ">
birthday=#{birthday},
</if>
<if test="sex!=null and sex!='' ">
sex=#{sex},
</if>
<if test="address!=null and address!='' ">
address=#{address}
</if>
where id=#{id}
</update>
set+if标签
<update id="updateUser" parameterType="User">
update user
<set>
<if test="username!=null and username!='' ">
username=#{username},
</if>
<if test="birthday.toString()!=null and birthday.toString()!='' ">
birthday=#{birthday},
</if>
<if test="sex!=null and sex!='' ">
sex=#{sex},
</if>
<if test="address!=null and address!='' ">
address=#{address}
</if>
</set>
where id=#{id}
</update>
7.2.3、编写MbTest.java
package com.tianyi.test;
import com.tianyi.dao.IUserDao;
import com.tianyi.javabean.IdsList;
import com.tianyi.javabean.QueryUser;
import com.tianyi.javabean.User;
import com.tianyi.javabean.User_old;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
public class MbTest {
private InputStream in ;
private SqlSessionFactory factory;
private SqlSession session;
private IUserDao userDao;
@Test
public void updateUser(){
User u=userDao.findById(11);
u.setUsername("赵六");
userDao.updateUser(u);
}
@Before
//在测试方法执行之前执行
public void init()throws Exception {
//1.读取配置文件
in = Resources.getResourceAsStream("SqlMapConfig.xml");
//2.创建构建者对象
SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
//3.创建 SqlSession 工厂对象
factory = builder.build(in);
// 4.创建 SqlSession 对象
session = factory.openSession(true);
//5.创建 Dao 的代理对象
userDao = session.getMapper(IUserDao.class);
}
@After//在测试方法执行完成之后执行
public void destroy() throws Exception{
//session.commit();
//7.释放资源
session.close();
in.close();
}
}
8、trim标签
trim标记是一个格式化的标记,主要用于拼接sql的条件语句(前缀或后缀的添加或忽略),可以完成set或者是where标记的功能。
prefix:在trim标签内sql语句加上前缀
suffix:在trim标签内sql语句加上后缀
prefixOverrides:指定去除多余的前缀内容,如:prefixOverrides=“AND | OR”,去除trim标签内sql语句多余的前缀"and"或者"or"。
suffixOverrides:指定去除多余的后缀内容。
8.1、需求分析-1
修改我们的指定记录
8.2、案例实现
8.2.1、编写IUserDao.xml文件
<update id="updateUser" parameterType="User">
update user
<trim prefix="set" suffixOverrides=",">
<if test="username!=null and username!='' ">
username=#{username},
</if>
<if test="birthday.toString()!=null and birthday.toString()!='' ">
birthday=#{birthday},
</if>
<if test="sex!=null and sex!='' ">
sex=#{sex},
</if>
<if test="address!=null and address!='' ">
address=#{address},
</if>
</trim>
where id=#{id}
</update>
8.3、需求分析-2
完成我们表的记录的添加
8.4、案例实现
8.4.1、编写IUserDao.java
import java.util.List;
public interface IUserDao {
int addUser(User u);
}
8.4.2、编写IUserDao.xml
<insert id="addUser" parameterType="com.tyschool.mb002.javabean.User">
<selectKey keyColumn="id" keyProperty="id" resultType="int">
select last_insert_id()
</selectKey>
insert into user(username,birthday,sex,address)values(#{username},#{birthday},#{sex},#{address})
</insert>
<insert id="addUser" parameterType="User">
<selectKey keyColumn="id" keyProperty="id" resultType="int">
select last_insert_id()
</selectKey>
insert into user
<trim prefix="(" suffix=")" suffixOverrides=",">
<if test="username!=null and username!='' ">
username,
</if>
<if test="birthday.toString()!=null and birthday.toString()!='' ">
birthday,
</if>
<if test="sex!=null and sex!='' ">
sex,
</if>
<if test="address!=null and address!='' ">
address
</if>
</trim>
values
<trim prefix="(" suffix=")" suffixOverrides=",">
<if test="username!=null and username!='' ">
#{username},
</if>
<if test="birthday.toString()!=null and birthday.toString()!='' ">
#{birthday},
</if>
<if test="sex!=null and sex!='' ">
#{sex},
</if>
<if test="address!=null and address!='' ">
#{address}
</if>
</trim>
</insert>
8.4.3、编写MbTest.java
package com.tianyi.test;
import com.tianyi.dao.IUserDao;
import com.tianyi.javabean.IdsList;
import com.tianyi.javabean.QueryUser;
import com.tianyi.javabean.User;
import com.tianyi.javabean.User_old;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
public class MbTest {
private InputStream in ;
private SqlSessionFactory factory;
private SqlSession session;
private IUserDao userDao;
@Test
public void addUser(){
User u=new User();
u.setUsername("李四");
u.setBirthday(new Date());
u.setSex("男");
u.setAddress("北京海淀");
userDao.addUser(u);
}
@Before
//在测试方法执行之前执行
public void init()throws Exception {
//1.读取配置文件
in = Resources.getResourceAsStream("SqlMapConfig.xml");
//2.创建构建者对象
SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
//3.创建 SqlSession 工厂对象
factory = builder.build(in);
// 4.创建 SqlSession 对象
session = factory.openSession(true);
//5.创建 Dao 的代理对象
userDao = session.getMapper(IUserDao.class);
}
@After//在测试方法执行完成之后执行
public void destroy() throws Exception{
//session.commit();
//7.释放资源
session.close();
in.close();
}
}
9、sql标签与include标签
include标签可以将Sql 中可将重复的 sql 提取出来,最终达到 sql 重用的目的。
9.1、sql标签声明
<sql id="sqlSelectAll">
select * from user
</sql>
9.2、include标签提取
<select id="findByIds" resultType="com.tianyi.javabean.User_old" parameterType="com.tianyi.javabean.IdsList">
<include refid="sqlSelectAll"></include>
<where>
<if test="ids!=null and ids.size()>0 ">
<foreach collection="ids" open="id in (" close=")" separator="," item="ids" >
#{ids}
</foreach>
</if>
</where>
</select>