MyBatis-动态SQL

星哥玩云

发布于 2022-09-14 21:30:18

5820

发布于 2022-09-14 21:30:18

文章被收录于专栏：开源部署开源部署

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>

本文参与腾讯云自媒体分享计划，分享自作者个人站点/博客。

如有侵权请联系 cloudcommunity@tencent.com 删除

java

sql

xml

本文分享自作者个人站点/博客前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

本文参与腾讯云自媒体分享计划，欢迎热爱写作的你一起参与！

java

sql

xml

登录后参与评论

0 条评论

热度