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

待我称王封你为后i 2022-12-11 03:15 156阅读 0赞

### 文章目录 ###

*   *  案例描述
     *   *  准备条件
         *   *  1. 创建数据库表customer
             *  2. 创建javabean对象
             *  3. 创建dao层接口
             *  4. 创建连接数据库的属性文件db.properties(键值对形式)
             *  5. spring整合MyBatis，核心配置文件applicationContext.xml
             *  6. 创建测试类，加载核心配置文件
         *  动态SQL语句测试
         *   *  1. 测试getCustomerByNameAndJob()不用动态sql实现查询
             *  2. 通过if标签实现查询
             *  3. 通过choose标签查询
             *  4. where标签查询
             *  5. trim标签查询
             *  6. set用于更新
             *  7. trim+set实现更新数据
             *  8. foreach标签查询
             *  9. bind标签
             *  10. 通过foreach和insert，实现foreach循环批量插入
             *  代码地址：\[https://github.com/strive-xgf/SSM/commit/0ca1f098051de82289d59e67aa2a5c88aacffcff\](https://github.com/strive-xgf/SSM/commit/0ca1f098051de82289d59e67aa2a5c88aacffcff)

## 案例描述 ##

创建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">
    
    
        
        <context:property-placeholder location="classpath*:db.properties" />
    
        
        <context:component-scan base-package="com.xgf.dynamic_sql"/>
    
        
        <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>
    
        
        
        <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
            <property name="dataSource" ref="dataSource" />
        </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));
        }

<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));
        }

<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));
        }

<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));
        }

<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));
        }

<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条，更新成功)  
> ![在这里插入图片描述][20200928220141512.png_pic_center]

#### 7. trim+set实现更新数据 ####

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

<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**(更新数据一条，更新成功)  
> ![在这里插入图片描述][2020092822054184.png_pic_center]

#### 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));
    
        }

<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));
        }

<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));
        }

<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语句，实现循环批量出入成功)  
> ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNTQyNTM0_size_16_color_FFFFFF_t_70_pic_center]  
> 运行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_github.com_strive-xgf_SSM_commit_0ca1f098051de82289d59e67aa2a5c88aacffcff] ####

[20200928220141512.png_pic_center]: /images/20221123/8b0ff90ba2a3433fad168365f4c9db34.png
[2020092822054184.png_pic_center]: /images/20221123/83e2143bfa6d4057942c0b35369b3014.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNTQyNTM0_size_16_color_FFFFFF_t_70_pic_center]: /images/20221123/dc544bce7f9b42e7a061f3e71d0511e1.png
[https_github.com_strive-xgf_SSM_commit_0ca1f098051de82289d59e67aa2a5c88aacffcff]: https://github.com/strive-xgf/SSM/commit/0ca1f098051de82289d59e67aa2a5c88aacffcff