组团学

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>

需要 登录 才可以提问哦