【SSM - MyBatis篇10】動態SQL語句案例實現
2020-10-01 08:00:10
文章目錄
- 案例描述
- 準備條件
- 動態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">
<!--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=‘專案經理’}]
