【SSM - MyBatis篇10】動態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