淺談MyBatis3 DynamicSql風(fēng)格語(yǔ)法使用指南

更新時(shí)間：2021年03月05日 11:22:40 作者：LeArn淡然

這篇文章主要介紹了淺談MyBatis3 DynamicSql風(fēng)格語(yǔ)法使用指南，文中通過示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧

<!-- 集成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 動(dòng)態(tài)SQL支持 -->
<dependency>
 <groupId>org.mybatis.dynamic-sql</groupId>
 <artifactId>mybatis-dynamic-sql</artifactId>
 <version>1.2.1</version>
</dependency>

查

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

而且基本都實(shí)際運(yùn)行過，確保沒有問題。

查詢指定列

SELECT 
 id,label,value 
FROM 
 sys_dict

import static com.twj.spirngbasics.server.mapper.SysDictDynamicSqlSupport.*; //注意導(dǎo)入對(duì)應(yīng)DynamicSqlSupport包的靜態(tài)屬性

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

下面完全等價(jià)于上面代碼，推薦上方寫法，代碼更整潔。

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

可以看到DynamicSql的使用結(jié)構(gòu)完全與sql語(yǔ)句一樣，真香。

查詢所有列

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.*;引入所有靜態(tài)方法。

排序：

升序：默認(rèn)MySQL可以不加ASC即為升序排序，DynamicSql也是如此，指定列即可；
降序：調(diào)用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 < ?)

根據(jù)業(yè)務(wù)邏輯添加條件

詳細(xì)看代碼

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

連接查詢

有前面的基礎(chǔ)，連接查詢其實(shí)異曲同工，我這里直接貼上官方示例代碼：

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(...) 內(nèi)連接
.leftJoin(...) 左外連接
.rightJoin(...) 右外連接
.fullJoin(...) 全連接

增

新增這里就不附上SQL語(yǔ)句了

新增一條

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

批量新增

List<SysDict> list = new ArrayList<>();
for (int i = 1; i < 10; i++) {
 SysDict sysDict = new SysDict();
 sysDict.setLabel("測(cè)試");
 sysDict.setValue(String.valueOf(i));
 sysDict.setType("test");
 sysDict.setSort(i);
 sysDict.setDescription("測(cè)試");
 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("成功插入條數(shù)：" + rows);

批量新增這里需要注意的是map的添加，也可以不加，但我在使用過程中出現(xiàn)過不加map導(dǎo)致批量新增出現(xiàn)某些必填字段明明賦值了數(shù)據(jù)庫(kù)卻報(bào)沒有不能為空，猜測(cè)應(yīng)該是轉(zhuǎn)換成sql語(yǔ)句時(shí)into與value沒有一一對(duì)應(yīng)，加上map就沒問題了。

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

刪

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

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

改

常用的簡(jiǎn)單更新主要是下面兩種：

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

復(fù)雜一點(diǎn)點(diǎn)的：

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

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

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

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

到此這篇關(guān)于淺談MyBatis3 DynamicSql風(fēng)格語(yǔ)法使用指南的文章就介紹到這了,更多相關(guān)MyBatis3 DynamicSql風(fēng)格內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: