动态的SQL

于 2020-06-27 00:12:41 发布
阅读量340 收藏
点赞数
分类专栏： spring 文章标签： spring
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/qq_45378285/article/details/106975632
版权

动态的SQL

作用

  • 在使用JDBC或者其他类似框架进行数据库开发时，经常需要根据需求手动拼装SQL,这是较为繁琐的，所以mybatis提供的对于SQL语句动态组装的功能，解决了这个问题。
  • 通过OGNL的表达式来完成动态SQL
  • 主要元素
  • if标签：判断语句，用户单条件分支判断
  • choose(when,otherwise):相当于Java中的switch…case…default语句，用于多条件分支判断。
  • where,trim,set:辅助元素，用于处理一些SQL拼装，特殊字符的处理。
  • foreach标签:循环语句，常用于in语句等列举条件中，
  • bind标签：从OGNL表达式中创建一个变量，并将其绑定上下文中，常用于模糊查询的SQL中。

if标签

Customer

package com.yzb.chapter08.example;
/*
* 客户持久化层
* */
public class Customer {
    private Integer id;
    private String username;
    private String jobs;
    private String phone;

    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 String getJobs() {
        return jobs;
    }

    public void setJobs(String jobs) {
        this.jobs = jobs;
    }

    public String getPhone() {
        return phone;
    }

    public void setPhone(String phone) {
        this.phone = phone;
    }

    @Override
    public String toString() {
        return "Customer{" +
                "id=" + id +
                ", username='" + username + '\'' +
                ", jobs='" + jobs + '\'' +
                ", phone='" + phone + '\'' +
                '}';
    }
}

MyBatisUtils

package com.yzb.chapter08.example;

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 java.io.IOException;
import java.io.InputStream;

/*
* 工具类
* */
public class MyBatisUtils {
    /*
    * 初始化sqlSessionFactory对象
    * */
    private static SqlSessionFactory sqlSessionFactory = null;
    static {
        try {
            //使用mybatis提供resources类加载MyBatis的配置文件
            InputStream resourceAsStream = Resources.getResourceAsStream("mybatis-config.xml");
            //创建SqlSessionFactory工厂
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    //获取SqlSession对象的方法
    public static SqlSession getSession(){
        return sqlSessionFactory.openSession();
    }
}

CustomerMapper.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">
<!-- namespace表示命名空间 -->
<mapper namespace="com.yzb.chapter08.CustomerMapper">
    <!--<if>元素的使用-->
    <select id="findCustomerByNameAndByJobs" resultType="com.yzb.chapter08.example.Customer"
            parameterType="com.yzb.chapter08.example.Customer">
        select * from t_customer where 1=1
        <if test="username !=null and username !='' ">
            and username like concat('%',#{username},'%')
        </if>
        <if test="jobs != null and jobs !=''">
            and jobs = #{jobs}
        </if>
    </select>

</mapper>

mybatis-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>
    <!--1.配置环境 ，默认的环境id为mysql-->
    <environments default="mysql">
        <!--1.2.配置id为mysql的数据库环境 -->
        <environment id="mysql">
            <!-- 使用JDBC的事务管理 -->
            <transactionManager type="JDBC" />
            <!--数据库连接池 -->
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.jdbc.Driver" />
                <property name="url"
                          value="jdbc:mysql://localhost:3306/spring" />
                <property name="username" value="root" />
                <property name="password" value="root" />
            </dataSource>
        </environment>
    </environments>
    <!--2.配置Mapper的位置 -->
    <mappers>
        <mapper resource="com/yzb/chapter08/example/CustomerMapper.xml" />
    </mappers>
</configuration>

Test

package com.yzb.chapter08.example;

import org.apache.ibatis.session.SqlSession;

import java.util.List;

public class Test {
    public static void main(String[] args) {
        /*获得sqlSession的对象*/
        SqlSession sqlSession = MyBatisUtils.getSession();
        //创建一个Customer的对象
        Customer customer = new Customer();
        customer.setUsername("j");
        customer.setJobs("456");
        List<Customer> customers = sqlSession.selectList("com.yzb.chapter08.CustomerMapper.findCustomerByNameAndByJobs", customer);
        for (Customer customer1 : customers) {
            System.out.println(customer1);
        }
        //关闭sqlSession的对象
        sqlSession.close();
    }
}

choose(when,otherwise)的使用

CustomerMapper.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">
<!-- namespace表示命名空间 -->
<mapper namespace="com.yzb.chapter08.CustomerMapper">
    <!--<if>元素的使用-->
    <select id="findCustomerByNameAndByJobs" resultType="com.yzb.chapter08.example.Customer"
            parameterType="com.yzb.chapter08.example.Customer">
        select * from t_customer where 1=1
        <if test="username !=null and username !='' ">
            and username like concat('%',#{username},'%')
        </if>
        <if test="jobs != null and jobs !=''">
            and jobs = #{jobs}
        </if>
    </select>
    <!--<choose>(<when><otherwise>)的使用-->
    <select id="findCustomerByNameOrByJobs" resultType="com.yzb.chapter08.example.Customer"
            parameterType="com.yzb.chapter08.example.Customer">
        select * from t_customer where 1=1
        <choose>
            <when test="username != null and username !=''">
                and username like concat('%',#{username},'%')
            </when>
            <when test="jobs!=null and jobs!=''">
                and jobs = #{jobs}
            </when>
            <otherwise>
                and phone is not null
            </otherwise>
        </choose>
    </select>

</mapper>

Test1

package com.yzb.chapter08.example;

import org.apache.ibatis.session.SqlSession;

import java.util.List;

public class Test1 {
    public static void main(String[] args) {
        /*获得sqlSession的对象*/
        SqlSession sqlSession = MyBatisUtils.getSession();
        //创建一个Customer的对象
        Customer customer = new Customer();
        customer.setUsername("j");
        customer.setJobs("456");
        List<Customer> customers = sqlSession.selectList("com.yzb.chapter08.CustomerMapper.findCustomerByNameOrByJobs", customer);
        for (Customer customer1 : customers) {
            System.out.println(customer1);
        }
        //关闭sqlSession的对象
        sqlSession.close();
    }
}

where和trim元素

上面的sql的语句中，后面都需要加入1=1的条件，如果去掉则会出现sql语句的语法错误，在mybatis的动态SQL中也对这个问题进行了解决，
可以使用这两个元素

<where> 
会自动判断SQL语句，只有<where>内的条件成立时，才会在拼接SQL中加入where的关键字，否则不会添加，还会去除多余的"AND"或“OR”
<trim>
会除去特殊的字符串，它的prefix的属性代表语句的前缀，prefixOverrides属性表示需要去除的那些特殊的字符串，功能和<where>基本等效的

<?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">
<!-- namespace表示命名空间 -->
<mapper namespace="com.yzb.chapter08.CustomerMapper">
    <!--<if>元素的使用-->
    <select id="findCustomerByNameAndByJobs" resultType="com.yzb.chapter08.example.Customer"
            parameterType="com.yzb.chapter08.example.Customer">
        select * from t_customer
        <where>
        <if test="username !=null and username !='' ">
            and username like concat('%',#{username},'%')
        </if>
        <if test="jobs != null and jobs !=''">
            and jobs = #{jobs}
        </if>
    </where>
    </select>
    <!--<choose>(<when><otherwise>)的使用-->
    <select id="findCustomerByNameOrByJobs" resultType="com.yzb.chapter08.example.Customer"
            parameterType="com.yzb.chapter08.example.Customer">
        select * from t_customer
        <trim prefix="where" prefixOverrides="and">
        <choose>
            <when test="username != null and username !=''">
                and username like concat('%',#{username},'%')
            </when>
            <when test="jobs!=null and jobs!=''">
                and jobs = #{jobs}
            </when>
            <otherwise>
                and phone is not null
            </otherwise>
        </choose>
        </trim>
    </select>

</mapper>

set标签

当进行更新用户的时候，必须写上持久层对象的所有的属性，为了解决这个问题，使用set标签，当更改一个属性的时候也行。

CustomerMapper.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">
<!-- namespace表示命名空间 -->
<mapper namespace="com.yzb.chapter08.CustomerMapper">
    <!--<if>元素的使用-->
    <select id="findCustomerByNameAndByJobs" resultType="com.yzb.chapter08.example.Customer"
            parameterType="com.yzb.chapter08.example.Customer">
        select * from t_customer
        <where>
        <if test="username !=null and username !='' ">
            and username like concat('%',#{username},'%')
        </if>
        <if test="jobs != null and jobs !=''">
            and jobs = #{jobs}
        </if>
    </where>
    </select>
    <!--<choose>(<when><otherwise>)的使用-->
    <select id="findCustomerByNameOrByJobs" resultType="com.yzb.chapter08.example.Customer"
            parameterType="com.yzb.chapter08.example.Customer">
        select * from t_customer
        <trim prefix="where" prefixOverrides="and">
        <choose>
            <when test="username != null and username !=''">
                and username like concat('%',#{username},'%')
            </when>
            <when test="jobs!=null and jobs!=''">
                and jobs = #{jobs}
            </when>
            <otherwise>
                and phone is not null
            </otherwise>
        </choose>
        </trim>
    </select>
    <!--<set>元素的使用-->
    <update id="updateCustomer" parameterType="com.yzb.chapter08.example.Customer">
        update t_customer
        <set>/*会自动将最后一个逗号去掉*/
            <if test="username != null and username !=''">
                username = #{username},
            </if>
          <if test="jobs !=null and jobs != ''">
              jobs = #{jobs},
          </if>
          <if test="phone != null and phone !=''">
              phone = #{phone},
          </if>
        </set>
          where id = #{id}
    </update>

</mapper>

Test2

package com.yzb.chapter08.example;

import org.apache.ibatis.session.SqlSession;

import java.util.List;

public class Test2 {
    public static void main(String[] args) {
        /*获得sqlSession的对象*/
        SqlSession sqlSession = MyBatisUtils.getSession();
        //创建一个Customer的对象
        Customer customer = new Customer();
        customer.setId(3);
        customer.setJobs("11");
        //执行SqlSession对象的update()的更新方法，返回的是受影响的行数
        int update = sqlSession.update("com.yzb.chapter08.CustomerMapper.updateCustomer", customer);
        //对返回结果进行判断，看更新操作是否成功
       if(update>0){
           System.out.println("修改成功");
       }else {
           System.out.println("修改失败");
       }
       //提交事务
        sqlSession.commit();
        //关闭sqlSession的对象
        sqlSession.close();
    }
}

foreach

为了解决多条数据的查询，可以使用foreach标签

  • 标签中的属性
  • item；配置的是当前循环的元素
  • index:配置的是当前元素在集合的位置下标
  • collection:配置的list是传递过来的参数类型（首字母小写），它可以是一个array,list,(或collection).Map集合的键，POJO包装类中数组或集合类型的属性名。
  • open和close:配置的是以什么符号将这些集合元素包装起来
  • separator:配置的是各个元素的间隔符
  • @@@注意collection属性
  • 如果传入的是单参数且参数类型是一个数组或者List的时候，collection属性值分别是array和list(或collection)
  • 如果传入的是多个参数的时候，就需要将他们封装成一个Map了，当然单个参数也可以封装成Map集合，这个时候collection属性值就是Map的键了。
  • 如果传入的参数是POJO包装类的时候，collection属性值就为该包装类中需要进行遍历的数组或集合的属性名。

CustomerMapper.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">
<!-- namespace表示命名空间 -->
<mapper namespace="com.yzb.chapter08.CustomerMapper">
    <!--<if>元素的使用-->
    <select id="findCustomerByNameAndByJobs" resultType="com.yzb.chapter08.example.Customer"
            parameterType="com.yzb.chapter08.example.Customer">
        select * from t_customer
        <where>
        <if test="username !=null and username !='' ">
            and username like concat('%',#{username},'%')
        </if>
        <if test="jobs != null and jobs !=''">
            and jobs = #{jobs}
        </if>
    </where>
    </select>
    <!--<choose>(<when><otherwise>)的使用-->
    <select id="findCustomerByNameOrByJobs" resultType="com.yzb.chapter08.example.Customer"
            parameterType="com.yzb.chapter08.example.Customer">
        select * from t_customer
        <trim prefix="where" prefixOverrides="and">
        <choose>
            <when test="username != null and username !=''">
                and username like concat('%',#{username},'%')
            </when>
            <when test="jobs!=null and jobs!=''">
                and jobs = #{jobs}
            </when>
            <otherwise>
                and phone is not null
            </otherwise>
        </choose>
        </trim>
    </select>
    <!--<set>元素的使用-->
    <update id="updateCustomer" parameterType="com.yzb.chapter08.example.Customer">
        update t_customer
        <set>/*会自动将最后一个逗号去掉*/
            <if test="username != null and username !=''">
                username = #{username},
            </if>
          <if test="jobs !=null and jobs != ''">
              jobs = #{jobs},
          </if>
          <if test="phone != null and phone !=''">
              phone = #{phone},
          </if>
        </set>
          where id = #{id}
    </update>
    <!--<foreach>标签的使用-->
    <select id="findCustomerByMoreId" parameterType="List" resultType="com.yzb.chapter08.example.Customer">
        select * from t_customer where id in
        <foreach collection="list" index="index" item="id" open="(" separator="," close=")">
            #{id}
        </foreach>
    </select>

</mapper>

Test3

package com.yzb.chapter08.example;

import org.apache.ibatis.session.SqlSession;

import java.util.ArrayList;
import java.util.List;

public class Test3 {
    public static void main(String[] args) {
        /*获得sqlSession的对象*/
        SqlSession sqlSession = MyBatisUtils.getSession();
        //创建一个数组,封装id
        List<Integer> list = new ArrayList<Integer>();
        list.add(1);
        list.add(2);
        //执行方法，返回结果集
        List<Customer> customers = sqlSession.selectList("com.yzb.chapter08.CustomerMapper.findCustomerByMoreId", list);
        //遍历结果集
        for (Customer customer1 : customers) {
            System.out.println(customer1);
        }
        //关闭sqlSession的对象
        sqlSession.close();
    }
}

bind 标签的使用

select * from t_customer where username like '%${value}%'
如果使用“${}”进行字符串的拼接，则无法防止SQL注入问题
如果改用concat函数进行拼接，则只针对MySql数据库有效
改用“||”进行字符串拼接，则只针对Oracle数据库有效
这样根据不同的数据库，就要进行不同的更改，有些麻烦，也不利于项目的移植性，为了解决这个问题，可以使用<bind>元素解决这个问题

bind元素可以通过OGNL表达式来创建一个上下文变量，

CustomerMapper.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">
<!-- namespace表示命名空间 -->
<mapper namespace="com.yzb.chapter08.CustomerMapper">
    <!--<if>元素的使用-->
    <select id="findCustomerByNameAndByJobs" resultType="com.yzb.chapter08.example.Customer"
            parameterType="com.yzb.chapter08.example.Customer">
        select * from t_customer
        <where>
        <if test="username !=null and username !='' ">
            and username like concat('%',#{username},'%')
        </if>
        <if test="jobs != null and jobs !=''">
            and jobs = #{jobs}
        </if>
    </where>
    </select>
    <!--<choose>(<when><otherwise>)的使用-->
    <select id="findCustomerByNameOrByJobs" resultType="com.yzb.chapter08.example.Customer"
            parameterType="com.yzb.chapter08.example.Customer">
        select * from t_customer
        <trim prefix="where" prefixOverrides="and">
        <choose>
            <when test="username != null and username !=''">
                and username like concat('%',#{username},'%')
            </when>
            <when test="jobs!=null and jobs!=''">
                and jobs = #{jobs}
            </when>
            <otherwise>
                and phone is not null
            </otherwise>
        </choose>
        </trim>
    </select>
    <!--<set>元素的使用-->
    <update id="updateCustomer" parameterType="com.yzb.chapter08.example.Customer">
        update t_customer
        <set>/*会自动将最后一个逗号去掉*/
            <if test="username != null and username !=''">
                username = #{username},
            </if>
          <if test="jobs !=null and jobs != ''">
              jobs = #{jobs},
          </if>
          <if test="phone != null and phone !=''">
              phone = #{phone},
          </if>
        </set>
          where id = #{id}
    </update>
    <!--<foreach>标签的使用-->
    <select id="findCustomerByMoreId" parameterType="List" resultType="com.yzb.chapter08.example.Customer">
        select * from t_customer where id in
        <foreach collection="list" index="index" item="id" open="(" separator="," close=")">
            #{id}
        </foreach>
    </select>
    <!--<bind>元素的使用-->
    <select id="findCustomerByName" resultType="com.yzb.chapter08.example.Customer"
            parameterType="com.yzb.chapter08.example.Customer">
          /*_paramter.getUsername()表示传递进来的参数，也可以直接写成对应参数的变量名*/
        <bind name="pattern_name" value="'%'+_parameter.getUsername()+'%'"></bind>
        /*需要的地方直接引用bind元素的name属性值即可*/
        select * from t_customer where username like #{pattern_name}
    </select>

</mapper>

Test4

package com.yzb.chapter08.example;

import org.apache.ibatis.session.SqlSession;

import java.util.List;

public class Test4 {
    public static void main(String[] args) {
        /*获得sqlSession的对象*/
        SqlSession sqlSession = MyBatisUtils.getSession();
        //创建一个Customer的对象
        Customer customer = new Customer();
        customer.setUsername("j");
        List<Customer> customers = sqlSession.selectList("com.yzb.chapter08.CustomerMapper.findCustomerByName", customer);
        for (Customer customer1 : customers) {
            System.out.println(customer1);
        }
        //关闭sqlSession的对象
        sqlSession.close();
    }
}
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值