【SSM - MyBatis篇10】动态SQL语句案例实现

文章目录

• • 案例描述
  • • 准备条件
    • 动态SQL语句测试

案例描述

  创建customer用户表，有三个属性字段，id、username、job，通过动态SQL语句对其进行增删查改。

准备条件

1. 创建数据库表customer

-- ----------------------------
-- customer表
-- ----------------------------
DROP TABLE IF EXISTS `customer`;
CREATE TABLE `customer` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(50) DEFAULT NULL,
  `job` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8;

-- ----------------------------
-- 初始数据
-- ----------------------------
INSERT INTO `customer` VALUES ('1', '张三', '程序员');
INSERT INTO `customer` VALUES ('2', '李四', '项目经理');
INSERT INTO `customer` VALUES ('3', '王五', '测试员');
INSERT INTO `customer` VALUES ('4', '赵六', '开发人员');

2. 创建javabean对象

public class Customer {

    private Integer id;         //id主键自动增长
    private String username;    //用户名
    private String job;         //工作
    //省略getter/setter方法
}

3. 创建dao层接口

public interface CustomerMapper {

//    不用标签，普通方法测试- 通过name和job查询Customer
    List<Customer> getCustomerByNameAndJob(Customer customer);
//    通过if标签实现查询
    List<Customer> getCustomerByNameAndJobForIf(Customer customer);
//    通过choose标签实现查询
    List<Customer> getCustomerByNameAndJobForChoose(Customer customer);
//    where标签查询
    List<Customer> getCustomerByNameAndJobForWhere(Customer customer);
//    trim标签
    List<Customer> getCustomerByNameAndJobForTrim(Customer customer);
//    set标签更新数据
    int updateForSet(Customer customer);
//  通过trim+set实现更新数据，添加忽略前后缀实现
    int updateForTrim(Customer customer);
//   通过foreach标签查询 - 参数是list集合id (相当于select * from tableName where id in {1,2,3} 查询结果为集合)
    List<Customer> getCustomerByIdsForeach(List<Integer> ids);
//  bind标签，解决可移植性问题 ，解决不同数据库拼接函数或连接符号的不同(防止sql注入)
    List<Customer> getCustomerByNameAndJobForBind(Customer customer);
//  通过foreach和insert，实现foreach循环批量插入
    boolean insertCustomerList(List<Customer> customerList);
}

4. 创建连接数据库的属性文件db.properties(键值对形式)

jdbc.driver = com.mysql.jdbc.Driver
jdbc.url = jdbc:mysql://localhost:3306/mybatis?useUnicode=true&characterEncoding=UTF-8&useSSL=false
jdbc.username = root
jdbc.password = 861221293

5. spring整合MyBatis，核心配置文件applicationContext.xml

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:context="http://www.springframework.org/schema/context"
       xmlns:mybatis="http://mybatis.org/schema/mybatis-spring" xmlns:tx="http://www.springframework.org/schema/tx"
       xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd http://mybatis.org/schema/mybatis-spring http://mybatis.org/schema/mybatis-spring.xsd http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx.xsd">


    <!--1. 引入jdbc的属性文件，在配置中通过占位使用 -->
    <context:property-placeholder location="classpath*:db.properties" />

    <!--2. <context:component-scan>扫描包中注解所标注的类(@Component、@Service、@Controller、@Repository) -->
    <context:component-scan base-package="com.xgf.dynamic_sql"/>

    <!--3. 由spring管理    配置数据源数据库连接(从jdbc属性文件中读取参数) -->
    <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <property name="username" value="${jdbc.username}"/>
        <property name="password" value="${jdbc.password}"/>
        <property name="url" value="${jdbc.url}"/>
        <property name="driverClassName" value="${jdbc.driver}"/>
    </bean>

    <!--  通过spring来管理Mybatis的sqlSessionFactory对象创建  -->
    <!--4. 通过完全限定名匹配查找  创建SqlSessionFactoryBean  -->
    <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
        <property name="dataSource" ref="dataSource" />
    </bean>

    <!-- 5. mybatis提供的一个注解扫描标签（搜索映射器 Mapper 接口），通过自动扫描注解的机制，创建每个dao接口定义的bean  -->
    <mybatis:scan base-package="com.xgf.dynamic_sql.dao"/>

</beans>

6. 创建测试类，加载核心配置文件

public class CustomerMapperTest {

    private static ApplicationContext applicationContext = null;
    private static CustomerMapper customerMapper = null;
    private static Customer customer = new Customer("赵六","开发人员");//初始化customer数据

    //只加载一次  @BeforeClass@BeforeClass只在类中执行一次, 必须声明为public static
    @BeforeClass
    public static void init(){
        //加载配置文件
        applicationContext = new ClassPathXmlApplicationContext("com/xgf/dynamic_sql/config/applicationContext.xml");
        //获取bean的两种方式
        // 1.类名首字母小写
//        customerMapper = (CustomerMapper) applicationContext.getBean("customerMapper");
        // 2.类.class
        customerMapper = (CustomerMapper) applicationContext.getBean(CustomerMapper.class);
    }
}

动态SQL语句测试

1. 测试getCustomerByNameAndJob()不用动态sql实现查询

//    不用标签，普通方法测试- 通过name和job查询Customer
    @Test
    public void getCustomerByNameAndJob() {
        System.out.println(customerMapper.getCustomerByNameAndJob(customer));;
    }

<!--正常查询 多条件存在查询-->
    <select id="getCustomerByNameAndJob" parameterType="com.xgf.dynamic_sql.bean.Customer" resultType="com.xgf.dynamic_sql.bean.Customer">
        select id,username,job
        from customer
        where username like concat('%',#{username},'%') and job = #{job}
    </select>

运行sql：Preparing: select id,username,job from customer where username like concat(’%’,?,’%’) and job = ?
运行参数：Parameters: 赵六(String), 开发人员(String)
运行结果：[Customer{id=4, username=‘赵六’, job=‘开发人员’}]

2. 通过if标签实现查询

@Test
    public void getCustomerByNameAndJobForIf() {
        System.out.println(customerMapper.getCustomerByNameAndJobForIf(customer));
    }

<!-- if 查询，满足test条件就执行里面的代码，程序拼接and/or需要有一个where 1=1 的前提来拼接 -->
    <select id="getCustomerByNameAndJobForIf" parameterType="com.xgf.dynamic_sql.bean.Customer" resultType="com.xgf.dynamic_sql.bean.Customer">
        select id,username,job
        from customer
        where 1=1
        <if test="username!=null and username!=''">
            and username like concat('%',#{username},'%')
        </if>
        <if test="job!=null and job!=''">
            and job=#{job}
        </if>

    </select>

运行sql：Preparing: select id,username,job from customer where 1=1 and username like concat(’%’,?,’%’) and job=?
运行参数：Parameters: 赵六(String), 开发人员(String)
运行结果： [Customer{id=4, username=‘赵六’, job=‘开发人员’}]

3. 通过choose标签查询

//    通过choose标签实现查询 这里讲customer的job和username设为空值，然后choose中的when条件都不满足，执行otherwise
    @Test
    public void getCustomerByNameAndJobForChoose() {
        customer.setJob(null);
        customer.setUsername(null);
        System.out.println(customerMapper.getCustomerByNameAndJobForChoose(customer));
    }

<!-- choose 查询 满足一个条件，就忽略后面的条件，没有一个条件满足，就输出所有-->
    <select id="getCustomerByNameAndJobForChoose" parameterType="com.xgf.dynamic_sql.bean.Customer" resultType="com.xgf.dynamic_sql.bean.Customer">
        select id,username,job
        from customer
        where 1=1
        <choose>
            <when test="username!=null and username!=''">
                and username like concat('%',#{username},'%')
            </when>
            <when test="job!=null and job!=''">
                and job=#{job}
            </when>
            <otherwise>
                order by username desc
            </otherwise>
        </choose>
    </select>

运行的sql：Preparing: select id,username,job from customer where 1=1 order by username desc **
运行参数：(空值，没有参数)**
运行结果： [Customer{id=4, username=‘赵六’, job=‘开发人员’}, Customer{id=3, username=‘王五’, job=‘测试员’}, Customer{id=2, username=‘李四’, job=‘项目经理’}, Customer{id=1, username=‘张三’, job=‘程序员’}]
  因为条件都不满足，所以按照username的desc倒序输出所有customer(排序输出不能对汉字排序，但是会将汉字识别为拼音，进行字母排序)

4. where标签查询

//    where标签查询
    @Test
    public void getCustomerByNameAndJobForWhere() {
        System.out.println(customerMapper.getCustomerByNameAndJobForWhere(customer));
    }

<!--where标签，如果后面没有条件(条件都不满足)不加where，如果后面有条件，会自动增加一个where 
    会将多余的and、or去掉，会自动填充第一个缺失的and、or-->
    <select id="getCustomerByNameAndJobForWhere"
            parameterType="com.xgf.dynamic_sql.bean.Customer"
            resultType="com.xgf.dynamic_sql.bean.Customer" useCache="true">
        select id,username,job
        from customer
        <where>
            <if test="username!=null and username!=''">
                and username like concat('%',#{username},'%')
            </if>
            <if test="job!=null and job!=''">
                and job=#{job}
            </if>
        </where>

    </select>

运行sql：Preparing: select id,username,job from customer WHERE username like concat(’%’,?,’%’) and job=?
运行参数：Parameters: 赵六(String), 开发人员(String)
运行结果：[Customer{id=4, username=‘赵六’, job=‘开发人员’}]

5. trim标签查询

//trim标签查询
    @Test
    public void getCustomerByNameAndJobForTrim() {
        System.out.println(customerMapper.getCustomerByNameAndJobForTrim(customer));
    }

	<!--trim 可以添加前后缀(prefix和suffix) 覆盖前后缀(prefixOverrides和suffixOverrides)
        这里覆盖了前缀and|or，添加了前缀where
    -->
    <select id="getCustomerByNameAndJobForTrim" parameterType="com.xgf.dynamic_sql.bean.Customer" resultType="com.xgf.dynamic_sql.bean.Customer">
        select id,username,job
        from customer
        <trim prefix="where" prefixOverrides="and|or">
            <if test="username!=null and username!=''">
                or username like concat('%',#{username},'%')
            </if>
            <if test="job!=null and job!=''">
                and job=#{job}
            </if>
        </trim>

    </select>

运行sql：Preparing: select id,username,job from customer where username like concat(’%’,?,’%’) and job=?
运行参数：Parameters: 赵六(String), 开发人员(String)
运行结果：[Customer{id=4, username=‘赵六’, job=‘开发人员’}]

6. set用于更新

//set标签更新数据
    @Test
    public void updateForSet() {
        customer.setId(4);//前面初始化数据没有id
        customer.setUsername("赵六数据更新");
        customer.setJob("赵六当老板");
        System.out.println(customerMapper.updateForSet(customer));
    }

<!--set在动态update语句中，可以使用<set>元素动态更新列 ->
            set 元素可以用于动态包含需要更新的列，忽略其它不更新的列(如果其它列数据为null，就不更新)。
        (返回结果是整数，更新删除增加不需要写resultType)
    -->
    <update id="updateForSet" parameterType="com.xgf.dynamic_sql.bean.Customer" flushCache="false">
        update customer
        <set>
            <if test="username!=null and username!=''">
                username=#{username},
            </if>
            <if test="job!=null and job!=''">
                job=#{job},
            </if>
        </set>
        where id=#{id}
    </update>

运行sql：Preparing: update customer SET username=?, job=? where id=?
运行参数：Parameters: 赵六数据更新(String), 赵六当老板(String), 4(Integer)
运行结果：Updates: 1 (更新影响数据1条，更新成功)

7. trim+set实现更新数据

//通过trim+set实现更新数据，通过添加和忽略前后缀实现
    @Test
    public void updateForTrim() {
        customer.setId(4);//前面初始化数据没有id
        customer.setUsername("赵六通过trim更新");
        customer.setJob("赵六董事长了哟");
        System.out.println(customerMapper.updateForTrim(customer));
    }

<!--通过trim+set实现更新数据，通过添加和忽略前后缀实现  添加前缀set，忽略后缀的逗号,-->
    <update id="updateForTrim" parameterType="com.xgf.dynamic_sql.bean.Customer">
        update customer
        <trim prefix="set" suffixOverrides=",">
            <if test="username!=null and username!=''">
                username=#{username},
            </if>
            <if test="job!=null and job!=''">
                job=#{job},
            </if>
        </trim>
        where id=#{id}
    </update>

运行sql：Preparing: update customer set username=?, job=? where id=?
运行参数：Parameters: 赵六通过trim更新(String), 赵六董事长了哟(String), 4(Integer)
运行结果：Updates: 1(更新数据一条，更新成功)

8. foreach标签查询

//通过foreach标签查询 - 参数是list集合id (相当于select * from tableName where id in {1,2,3} 查询结果为集合)
    @Test
    public void getCustomerByIdsForeach() {
        ArrayList<Integer> idList = new ArrayList<>();
        idList.add(2);
        idList.add(3);
        idList.add(4);
        System.out.println(customerMapper.getCustomerByIdsForeach(idList));

    }

<!--foreach标签
        collection：传入的集合   item：循环出来的数据起别名   open：以什么开始 
        colose：以什么结束  separator： 分隔符 index： 循环计数器
        通过where标签和if标签 判断如果传入的列表为空，则后面的sql语句不添加，就查询所有customer
    -->
    <select id="getCustomerByIdsForeach" parameterType="list" resultType="com.xgf.dynamic_sql.bean.Customer">
        select id,username,job
        from customer
        <where>
            <if test="list!=null and list.size()>0">
                id in
                <foreach collection="list" item="id" open="(" close=")" separator="," index="index" >
                    #{id}
                </foreach>
            </if>
        </where>

    </select>

运行sql：Preparing: select id,username,job from customer WHERE id in ( ? , ? , ? )
运行参数：Parameters: 2(Integer), 3(Integer), 4(Integer)
运行结果：[Customer{id=2, username=‘李四’, job=‘项目经理’}, Customer{id=3, username=‘王五’, job=‘测试员’}, Customer{id=4, username=‘赵六通过trim更新’, job=‘赵六董事长了哟’}]

9. bind标签

//bind标签，解决可移植性问题 ，解决不同数据库拼接函数或连接符号的不同(防止sql注入)
    @Test
    public void getCustomerByNameAndJobForBind() {
        customer.setUsername("李四");
        customer.setJob("项目经理");
        System.out.println(customerMapper.getCustomerByNameAndJobForBind(customer));
    }

<!--  bind标签 解决可移植性问题  解决不同数据库拼接函数或连接符号的不同
            concat()数据库连接，将字符串拼接成一个字符串，是MySQL数据库独有的，在别的数据库中不能识别
            bind标签，name取名给其它地方调用，可以随便取， _parameter.getXXX()调用get方法
    -->
    <select id="getCustomerByNameAndJobForBind" parameterType="com.xgf.dynamic_sql.bean.Customer" resultType="com.xgf.dynamic_sql.bean.Customer">
        <if test="username!=null and username!=''">
            <bind name="pattern" value="'%' + _parameter.getUsername() + '%'" />
        </if>
        select id,username,job
        from customer
        <where>
            <if test="username!=null and username!=''">
                and username like #{pattern}
            </if>
            <if test="job!=null and job!=''">
                and job=#{job}
            </if>
        </where>

    </select>

运行sql：Preparing: select id,username,job from customer WHERE username like ? and job=?
运行参数：Parameters: %李四%(String), 项目经理(String)
运行结果：[Customer{id=2, username=‘李四’, job=‘项目经理’}]

10. 通过foreach和insert，实现foreach循环批量插入

//通过foreach和insert，实现foreach循环批量插入
    @Test
    public void insertCustomerList() {
        //customer表的主键id自动增长所以不需要赋值
        Customer customer1 = new Customer("钱一","大数据工程师");
        Customer customer2 = new Customer("孙二","AI工程师");
        Customer customer3 = new Customer("马三","运维工程师");

        ArrayList<Customer> customerList = new ArrayList<>();
        customerList.add(customer1);
        customerList.add(customer2);
        customerList.add(customer3);

        System.out.println(customerMapper.insertCustomerList(customerList));
    }

<!--foreach和insert共同使用  实现foreach循环批量插入
        item是每次从集合中取出一个对象的别名-->
    <insert id="insertCustomerList" parameterType="list">
        insert into customer(username,job) values
        <foreach collection="list" separator="," item="customer">
             (#{customer.username},#{customer.job})
         </foreach>
    </insert>

运行sql：Preparing: insert into customer(username,job) values (?,?) , (?,?) , (?,?)
运行参数：Parameters: 钱一(String), 大数据工程师(String), 孙二(String), AI工程师(String), 马三(String), 运维工程师(String)
运行结果：true(Updates: 3) (更新了三条sql语句，实现循环批量出入成功)

运行sql：Preparing: select id,username,job from customer WHERE username like ? and job=?
运行参数：Parameters: %李四%(String), 项目经理(String)
运行结果：[Customer{id=2, username=‘李四’, job=‘项目经理’}]

代码地址：https://github.com/strive-xgf/SSM/commit/0ca1f098051de82289d59e67aa2a5c88aacffcff

本文地址：https://blog.csdn.net/qq_40542534/article/details/108838091