淺談MyBatis3 DynamicSql風格語法使用指南

Posted on by

主要演示DynamicSql風格代碼如何使用，基本能應對大部分使用場景。DynamicSql基本介紹點我查看。

本文主要沿著增、刪、改、查的思路進行介紹，盡量涵蓋日常使用所需。

我這裡還是要推薦一下大傢看官方文檔，盡量有問題先找官方文檔教程，除非寫的跟屎一樣，但大概率不會。

本次使用的是mybatis-dynamic-sql1.2.1版本

<!-- 集成mybatis -->
<dependency>
 <groupId>org.mybatis.spring.boot</groupId>
 <artifactId>mybatis-spring-boot-starter</artifactId>
 <version>2.1.3</version>
</dependency>
<dependency>
 <groupId>mysql</groupId>
 <artifactId>mysql-connector-java</artifactId>
 <version>8.0.22</version>
</dependency>
<!-- MyBatis 生成器 -->
<dependency>
 <groupId>org.mybatis.generator</groupId>
 <artifactId>mybatis-generator-core</artifactId>
 <version>1.4.0</version>
</dependency>
<!-- MyBatis 動態SQL支持 -->
<dependency>
 <groupId>org.mybatis.dynamic-sql</groupId>
 <artifactId>mybatis-dynamic-sql</artifactId>
 <version>1.2.1</version>
</dependency>

查

查詢我盡量貼上SQL語句對照著java代碼，方便讀者閱讀和理解。

而且基本都實際運行過，確保沒有問題。

查詢指定列

SELECT 
 id,label,value 
FROM 
 sys_dict
import static com.twj.spirngbasics.server.mapper.SysDictDynamicSqlSupport.*; //註意導入對應DynamicSqlSupport包的靜態屬性

SelectStatementProvider selectStatement = SqlBuilder.select(id, label, value)
  .from(sysDict)
  .build()
  .render(RenderingStrategies.MYBATIS3);
List<SysDict> test = sysDictMapper.selectMany(selectStatement);

下面完全等價於上面代碼，推薦上方寫法，代碼更整潔。

SelectStatementProvider selectStatement = SqlBuilder.select(SysDictDynamicSqlSupport.id, SysDictDynamicSqlSupport.label, SysDictDynamicSqlSupport.value)
  .from(SysDictDynamicSqlSupport.sysDict)
  .build()
  .render(RenderingStrategies.MYBATIS3);
List<SysDict> list = sysDictMapper.selectMany(selectStatement);

可以看到DynamicSql的使用結構完全與sql語句一樣，真香。

查詢所有列

SELECT 
 id,label,value,sort.......
FROM 
 sys_dict
 

SelectStatementProvider selectStatement = SqlBuilder.select(SysDictMapper.selectList)
  .from(SysDictDynamicSqlSupport.sysDict)
  .build()
  .render(RenderingStrategies.MYBATIS3);
List<SysDict> list = sysDictMapper.selectMany(selectStatement);

條件查詢

SELECT
 * 
FROM
 sys_dict 
WHERE
 label = '男' 
 OR label = '女' 
ORDER BY
 `value` ASC

SelectStatementProvider selectStatement = SqlBuilder.select(SysDictMapper.selectList)
  .from(SysDictDynamicSqlSupport.sysDict)
  .where(label, isEqualTo("男"))
  .or(label,isEqualTo("女"))
  .orderBy(value)
  .build()
  .render(RenderingStrategies.MYBATIS3);
List<SysDict> list = sysDictMapper.selectMany(selectStatement);

java這裡稍微註意一下，isEqualTo的包引用路徑是在org.mybatis.dynamic.sql.SqlBuilder包下，可以像之前一樣import static org.mybatis.dynamic.sql.SqlBuilder.*;引入所有靜態方法。

排序：

  • 升序：默認MySQL可以不加ASC即為升序排序，DynamicSql也是如此，指定列即可；
  • 降序：調用descending()即可，以上方例子為例，原orderBy(value)改為orderBy(value.descending())即可。
SELECT
 * 
FROM
 sys_dict 
WHERE
 label IN ( '女', '男' ) 
ORDER BY
 `value`
SelectStatementProvider selectStatement = SqlBuilder.select(SysDictMapper.selectList)
  .from(SysDictDynamicSqlSupport.sysDict)
  .where(label, isIn("女", "男"))
  .orderBy(value)
  .build()
  .render(RenderingStrategies.MYBATIS3);
List<SysDict> list = sysDictMapper.selectMany(selectStatement);

where條件查詢還有很多我就不一一例舉瞭，我這裡有一張官方偷來的表格：

Condition Example Result
Between where(foo, isBetween(x).and(y)) where foo between ? and ?
Equals where(foo, isEqualTo(x)) where foo = ?
Greater Than where(foo, isGreaterThan(x)) where foo > ?
Greater Than or Equals where(foo, isGreaterThanOrEqualTo(x)) where foo >= ?
In where(foo, isIn(x, y)) where foo in (?,?)
In (case insensitive) where(foo, isInCaseInsensitive(x, y)) where upper(foo) in (?,?) (the framework will transform the values for x and y to upper case)
Less Than where(foo, isLessThan(x)) where foo < ?
Less Than or Equals where(foo, isLessThanOrEqualTo(x)) where foo <= ?
Like where(foo, isLike(x)) where foo like ? (the framework DOES NOT add the SQL wild cards to the value – you will need to do that yourself)
Like (case insensitive) where(foo, isLikeCaseInsensitive(x)) where upper(foo) like ? (the framework DOES NOT add the SQL wild cards to the value – you will need to do that yourself, the framework will transform the value of x to upper case)
Not Between where(foo, isNotBetween(x).and(y)) where foo not between ? and ?
Not Equals where(foo, isNotEqualTo(x)) where foo <> ?
Not In where(foo, isNotIn(x, y)) where foo not in (?,?)
Not In (case insensitive) where(foo, isNotInCaseInsensitive(x, y)) where upper(foo) not in (?,?) (the framework will transform the values for x and y to upper case)
Not Like where(foo, isLike(x)) where foo not like ? (the framework DOES NOT add the SQL wild cards to the value – you will need to do that yourself)
Not Like (case insensitive) where(foo, isNotLikeCaseInsensitive(x)) where upper(foo) not like ? (the framework DOES NOT add the SQL wild cards to the value – you will need to do that yourself, the framework will transform the value of x to upper case)
Not Null where(foo, isNotNull()) where foo is not null
Null where(foo, isNull()) where foo is null

子查詢

SELECT
 * 
FROM
 user_resource 
WHERE
 id IN (
 SELECT
 resource_id 
 FROM
 user_role_resource 
 WHERE
 role_id = '1' 
 )

SelectStatementProvider selectStatement = SqlBuilder.select(userResourceMapper.selectList)
  .from(UserResourceDynamicSqlSupport.userResource)
  .where(UserResourceDynamicSqlSupport.id, isIn(
    select(UserRoleResourceDynamicSqlSupport.resourceId)
      .from(UserRoleResourceDynamicSqlSupport.userRoleResource)
      .where(UserRoleResourceDynamicSqlSupport.roleId, isEqualTo("1"))))
  .build()
  .render(RenderingStrategies.MYBATIS3);
List<UserResource> list = userResourceMapper.selectMany(selectStatement);

子查詢還有很多，我這裡又有一張官方偷來的表格：

Condition Example Result
Equals where(foo, isEqualTo(select(bar).from(table2).where(bar, isEqualTo(x))) where foo = (select bar from table2 where bar = ?)
Greater Than where(foo, isGreaterThan(select(bar).from(table2).where(bar, isEqualTo(x))) where foo > (select bar from table2 where bar = ?)
Greater Than or Equals where(foo, isGreaterThanOrEqualTo(select(bar).from(table2).where(bar, isEqualTo(x))) where foo >= (select bar from table2 where bar = ?)
In where(foo, isIn(select(bar).from(table2).where(bar, isLessThan(x))) where foo in (select bar from table2 where bar < ?)
Less Than where(foo, isLessThan(select(bar).from(table2).where(bar, isEqualTo(x))) where foo < (select bar from table2 where bar = ?)
Less Than or Equals where(foo, isLessThanOrEqualTo(select(bar).from(table2).where(bar, isEqualTo(x))) where foo <= (select bar from table2 where bar = ?)
Not Equals where(foo, isNotEqualTo(select(bar).from(table2).where(bar, isEqualTo(x))) where foo <> (select bar from table2 where bar = ?)
Not In where(foo, isNotIn(select(bar).from(table2).where(bar, isLessThan(x))) where foo not in (select bar from table2 where bar < ?)

根據業務邏輯添加條件

詳細看代碼

QueryExpressionDSL<SelectModel>.QueryExpressionWhereBuilder builder = SqlBuilder.select(SysDictMapper.selectList)
  .from(SysDictDynamicSqlSupport.sysDict)
  .where();

if (x)
 builder.where(label, isIn("女", "男"));

if (y)
 builder.where(row,...);

SelectStatementProvider selectStatement = builder.build().render(RenderingStrategies.MYBATIS3);

List<SysDict> list = sysDictMapper.selectMany(selectStatement);

連接查詢

有前面的基礎，連接查詢其實異曲同工，我這裡直接貼上官方示例代碼：

SelectStatementProvider selectStatement = select(orderMaster.orderId, orderDate, orderDetail.lineNumber, orderDetail.description, orderDetail.quantity)
   .from(orderMaster, "om")
   .join(orderDetail, "od").on(orderMaster.orderId, equalTo(orderDetail.orderId))
   .build()
   .render(RenderingStrategies.MYBATIS3);

目前支持四種連接類型：

  • .join(…) 內連接
  • .leftJoin(…) 左外連接
  • .rightJoin(…) 右外連接
  • .fullJoin(…) 全連接

增

新增這裡就不附上SQL語句瞭

新增一條

SysDict sysDict = new SysDict();
sysDict.setLabel("測試");
sysDict.setValue("0");
sysDict.setType("test");
sysDict.setSort(0);
sysDict.setDescription("測試");
sysDict.insert("SYSTEM");
int row = sysDictMapper.insert(sysDict);
System.out.println("成功插入條數：" + row);

批量新增

List<SysDict> list = new ArrayList<>();
for (int i = 1; i < 10; i++) {
 SysDict sysDict = new SysDict();
 sysDict.setLabel("測試");
 sysDict.setValue(String.valueOf(i));
 sysDict.setType("test");
 sysDict.setSort(i);
 sysDict.setDescription("測試");
 sysDict.insert("SYSTEM");
 list.add(sysDict);
}
MultiRowInsertStatementProvider<SysDict> multiRowInsert = SqlBuilder.insertMultiple(list)
  .into(SysDictDynamicSqlSupport.sysDict)
  .map(id).toProperty("id")
  .map(createdBy).toProperty("createdBy")
  .map(createdTime).toProperty("createdTime")
  .map(updateBy).toProperty("updateBy")
  .map(updateTime).toProperty("updateTime")
  .map(dele).toProperty("dele")
  .map(remake).toProperty("remake")
  .map(spare1).toProperty("spare1")
  .map(value).toProperty("value")
  .map(label).toProperty("label")
  .map(type).toProperty("type")
  .map(description).toProperty("description")
  .map(sort).toProperty("sort")
  .build()
  .render(RenderingStrategies.MYBATIS3);

int rows = sysDictMapper.insertMultiple(multiRowInsert);
System.out.println("成功插入條數：" + rows);

批量新增這裡需要註意的是map的添加，也可以不加，但我在使用過程中出現過不加map導致批量新增出現某些必填字段明明賦值瞭數據庫卻報沒有不能為空，猜測應該是轉換成sql語句時into與value沒有一一對應，加上map就沒問題瞭。

PS：.map可以直接從xxxDictMapper.insert()中copy過來。

刪

//根據主鍵刪除
sysDictMapper.deleteByPrimaryKey("");

//條件刪除
DeleteStatementProvider deleteStatement = deleteFrom(SysDictDynamicSqlSupport.sysDict)
  .where(SysDictDynamicSqlSupport.type, isEqualTo("test"))
  .build()
  .render(RenderingStrategies.MYBATIS3);
sysDictMapper.delete(deleteStatement);

改

常用的簡單更新主要是下面兩種：

//根據主鍵對所有屬性進行更新
sysDictMapper.updateByPrimaryKey(sysDict);
//根據主鍵對不為null的屬性進行更新
sysDictMapper.updateByPrimaryKeySelective(sysDict);

復雜一點點的：

UpdateStatementProvider updateStatement = update(SysDictDynamicSqlSupport.sysDict)
  .set(remake).equalToNull()
  .where(type, isEqualTo("test"))
  .build()
  .render(RenderingStrategies.MYBATIS3);

int rows = sysDictMapper.update(updateStatement);
System.out.println("成功更新條數：" + rows);

註意set方法，常用的方法有以下：

  • set(column).equalToNull() 將對應列更新為null；
  • set(column).equalTo(T value)將對應列更新為value；
  • set(column).equalToWhenPresent(T value)如果value不能null的話更新列；
  • set(column).equalTo(BasicColumn rightColumn)將一列的值設置為另一列的值，還可以對其加，減等操作。

到此這篇關於淺談MyBatis3 DynamicSql風格語法使用指南的文章就介紹到這瞭,更多相關MyBatis3 DynamicSql風格內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet！

推薦閱讀：