MyBatis-Plus
Last updated
Last updated
主要紀錄應用的方式,詳細內容我想別人寫的比我還要清楚,這邊就附上我學習時的網站
連結:
支援 Lambda 表達式,應用各式查詢、修改等方法
支援 ActiveRecord 形式使用,僅需繼承 Model 就可以使用
自動注入基本 CURD
內建程式產生器(Service[impl]、Mapper[xml]、Entity....)
內建SQL效能分析插件(雖然3.5.2已移除,改用第三方工具(p6spy))
內建基於 Mybatis 物理分頁插件加以優化,使用更方便
更專心於業務邏輯程式開發
// 官方文件: https://baomidou.com/
// 數據源配置
final DataSourceConfig.Builder DATA_SOURCE_CONFIG =
new DataSourceConfig
.Builder("jdbc:mysql://localhost:3306/game_system", "root", "1qaz2wsx")
.typeConvert(new MySqlTypeConvert());
// 檔案輸出位置
final String outputDir = "./S_Core/src/test/java";
// table list
final List<String> tables = Arrays.asList("member_trans_log");
// 自動生成策略配置
FastAutoGenerator
.create("jdbc:mysql://localhost:3306/game_system", "root", "1qaz2wsx")
// .create(DATA_SOURCE_CONFIG) // 兩種方法都可以 使用 DataSourceConfig 可以有較彈性設定(Ex.選擇db種類)
.globalConfig(builder ->
builder.author("Caster") // 設定作者
.enableSwagger() // entity 補上 swagger annotation
.outputDir(outputDir) // 檔案輸出位置
.disableOpenDir() // 不打開 folder
.fileOverride() // 覆蓋文件 3.5.3 已棄用
.build())
.packageConfig(
builder ->
builder.parent("com.longxiang")
.xml("xml") // 預設會在 mapper.xml 重新配置位置.
.build())
.strategyConfig(
builder ->
builder.addInclude(tables)
.addTablePrefix("") // 設置過濾表前綴 ex. t_user -> "t_"
.controllerBuilder() // enable controller策略配置
.enableRestStyle() // 設置restful風格 => @RestController
.enableHyphenStyle() // url中駝峰轉連字符
.serviceBuilder()
.formatServiceFileName("%sService") // 取消 Service 前面的I
.entityBuilder()
.enableActiveRecord()
.enableLombok() // 啟用 lombok(getter & setter)
.enableChainModel() // 啟用 lombok鏈式操作 => @Accessors(chain = true)
.disableSerialVersionUID()
.build())
.templateConfig(
builder ->
builder.disable(TemplateType.CONTROLLER) // 禁用模板, 這樣就不會生成 controller.
.build())
.templateEngine(new FreemarkerTemplateEngine()) // 使用Freemarker引擎模板,默認的是Velocity引擎模板
.execute(); //執行
LambdaQueryWrapper wrapper = Wrappers.<Merchant>lambdaQuery()
.eq(Objects.nonNull(merchantId), Merchant::getId, merchantId)
.eq(Objects.nonNull(lastTradingTime), Merchant::getLastTradingTime, lastTradingTime)
.last("for update");
第一種
OrderInfo order = new OrderInfo();
order.setOrderNo(orderNo);
order.setPaymentWayId(view.getOutPaymentWayId());
order.setPaymentWayName(view.getOutPaymentWayDesc());
order.setMerchantId(view.getMerchantId());
order.setMerchantName(view.getMerchantName());
order.setForthPaymentId(view.getForthPaymentId());
order.setForthPaymentName(view.getForthPaymentName());
LambdaQueryWrapper wrapper = Wrappers.<OrderInfo>lambdaQuery()
.eq(OrderInfo::getOrderNo, order.getOrderNo());
orderService.update(order, wrapper);
第二種
LambdaUpdateWrapper updateWrapper = Wrappers.<OrderInfo>lambdaUpdate()
.eq(OrderInfo::getOrderNo, orderNo)
.set(OrderInfo::getPaymentWayId, view.getOutPaymentWayId())
.set(OrderInfo::getPaymentWayName, view.getOutPaymentWayDesc())
.set(OrderInfo::getMerchantId, view.getMerchantId())
.set(OrderInfo::getMerchantName, view.getMerchantName())
.set(OrderInfo::getForthPaymentId, view.getForthPaymentId())
.set(OrderInfo::getForthPaymentName, view.getForthPaymentName());
orderService().update(updateWrapper);
第一種
// 預期的SQL
select pm.*,
tom.display_name to_Member_Name,
tom.picture_url to_picture_url,
fromm.display_name from_Member_Name,
fromm.picture_url from_picture_url
from platform_mail pm
left join member tom on tom.id = pm.to_member_id
left join member fromm on fromm.id = pm.from_member_id
WHERE (to_member_id = 5)
ORDER BY id DESC;
第三種 -> 我自己用最多是這種
orderService.lambdaUpdate()
.eq(OrderInfo::getOrderNo, orderNo)
.set(OrderInfo::getPaymentWayId, view.getOutPaymentWayId())
.set(OrderInfo::getPaymentWayName, view.getOutPaymentWayDesc())
.set(OrderInfo::getMerchantId, view.getMerchantId())
.set(OrderInfo::getMerchantName, view.getMerchantName())
.set(OrderInfo::getForthPaymentId, view.getForthPaymentId())
.set(OrderInfo::getForthPaymentName, view.getForthPaymentName())
.update();
// 自己撰寫SQL, 再透過Mapper 去呼叫.
// 預期結果SQL
select pm.*,
tom.display_name to_Member_Name,
tom.picture_url to_picture_url,
fromm.display_name from_Member_Name,
fromm.picture_url from_picture_url
from platform_mail pm
left join member tom on tom.id = pm.to_member_id
left join member fromm on fromm.id = pm.from_member_id
WHERE ((to_member_id = 5 OR from_member_id = 5))
ORDER BY id DESC
// LambdaQueryWrapper 組合
LambdaQueryWrapper<PlatformMail> queryWrapper = Wrappers.<PlatformMail>lambdaQuery()
.eq(CommonOnOffStatus.isOn(req.getType()), PlatformMail::getFromMemberId, currentUser.getId())
.eq(CommonOnOffStatus.isOff(req.getType()), PlatformMail::getToMemberId, currentUser.getId())
.and(req.getType().equals("2"),
o -> o.eq(PlatformMail::getToMemberId, currentUser.getId()).or().eq(PlatformMail::getFromMemberId, currentUser.getId()))
.orderByDesc(PlatformMail::getId);
// mapper.java
IPage<PlatformMailView> findAll(IPage<PlatformMailView> page, @Param(Constants.WRAPPER) Wrapper<PlatformMail> queryWrapper);
// mapper.xml
<select id="findAll" resultMap="baseResultMap">
select pm.*, tom.display_name to_Member_Name, tom.picture_url to_picture_url,
fromm.display_name from_Member_Name, fromm.picture_url from_picture_url
from platform_mail pm
left join member tom on tom.id = pm.to_member_id
left join member fromm on fromm.id = pm.from_member_id
${ew.customSqlSegment}
</select>
// 與第一種相似做法,差別在於會再用sub query 包裝 sql
// 這樣能避免不同table 有相同欄位 在設定條件時會出現衝突 Ex. id 這種欄位
// 預期結果SQL
SELECT mgi.*, tom.display_name to_Member_Name, fromm.display_name from_Member_Name
FROM member_gift_info mgi
LEFT JOIN member tom ON tom.id = mgi.to_member_id
LEFT JOIN member fromm ON fromm.id = mgi.from_member_id) AS result
WHERE (from_member_id = 5 AND status IN ('0', '4')
// 產出結果
SELECT *
FROM (SELECT mgi.*, tom.display_name to_Member_Name, fromm.display_name from_Member_Name
FROM member_gift_info mgi
LEFT JOIN member tom ON tom.id = mgi.to_member_id
LEFT JOIN member fromm ON fromm.id = mgi.from_member_id) AS result
WHERE (status IN ('1', '2', '3') AND (to_member_id = 5 OR from_member_id = 5))
// LambdaQuery 組合
new Page(req.getPageNum(), req.getPageSize()), Wrappers.<MemberGiftInfo>lambdaQuery()
.eq(req.getType().equals("0"), MemberGiftInfo::getFromMemberId, user.getId())
.eq(req.getType().equals("1"), MemberGiftInfo::getToMemberId, user.getId())
.in(CommonOnOffStatus.isOn(req.getIsSearchHistoryRecord()), MemberGiftInfo::getStatus,
GiftInfoStatus.CANCEL.getCode(), GiftInfoStatus.RECEIVER_CONFIRM.getCode())
.in(CommonOnOffStatus.isOff(req.getIsSearchHistoryRecord()), MemberGiftInfo::getStatus,
GiftInfoStatus.SEND_GIFT.getCode(), GiftInfoStatus.RECEIVER_CHECK.getCode(), GiftInfoStatus.SENDER_CONFIRM.getCode())
.and(req.getType().equals("2"),
o -> o.eq(MemberGiftInfo::getToMemberId, user.getId()).or().eq(MemberGiftInfo::getFromMemberId, user.getId()))
.orderByDesc(MemberGiftInfo::getId)
// 需要在 Service & Mapper 都加上相應的實作
// interface service -> MPJBaseService<E>
// serviceImpl -> MPJBaseServiceImpl<M, E>
// interface mapper -> MPJBaseMapper<E>
gameInfoService.selectJoinListPage(
new Page<>(req.getPageNum(), req.getPageSize()), GameInfoRes.class,
new MPJLambdaWrapper<GameInfoRes>()
.selectAll(GameInfo.class)
.selectAs(MemberFavourite::getGameId, GameInfoRes::getGameId)
.leftJoin(MemberFavourite.class, on -> on.eq(MemberFavourite::getGameId, GameInfo::getId).eq(MemberFavourite::getMemberId, user.getId()))
.eq(Objects.nonNull(GameCategory.getInstanceOf(req.getCategory())) && !req.getCategory().equals("-1"), GameInfo::getCategory, GameCategory.getInstanceOf(req.getCategory()).getCode())
.eq(GameInfo::getStatus, CommonOnOffStatus.ON.getCode())
.orderByDesc(GameInfo::getSort))
// 結果SQL
SELECT t.id,
t.company_id,
t.out_game_id,
t.lobby_id,
t.edition,
t.enable,
t.status,
t.group_id,
t.category,
t.name,
t.sort,
t1.game_id AS gameId
FROM game_info t
LEFT JOIN member_favourite t1 ON (t1.game_id = t.id AND t1.member_id = 5)
WHERE (t.status = '1')
ORDER BY t.sort DESC
LIMIT 5
使用 第一及第二種做法,還是擺脫不了自己撰寫SQL去實現Join的做法(若是有調整欄位,不容易在編譯期發現錯誤),若是使用第三種mybatis-plus-join
套件,會大大減少撰寫SQL繁瑣問題,亦可避免調整欄位導致的錯誤皆可在編譯期就會發生錯誤,雖說會有一些學習成本但結果是美好的,值得花一些時間去嘗試了解。
# 參考網路上基Gi本配置 放在 resource層
modulelist=com.baomidou.mybatisplus.extension.p6spy.MybatisPlusLogFactory,com.p6spy.engine.outage.P6OutageFactory
# 自定義日志打印
logMessageFormat=com.baomidou.mybatisplus.extension.p6spy.P6SpyLogger
#日志輸出到控制台
appender=com.baomidou.mybatisplus.extension.p6spy.StdoutLogger
# 使用日志系統記錄 sql
#appender=com.p6spy.engine.spy.appender.Slf4JLogger
# 設置 p6spy driver 代理deregisterdrivers=true
# 取消JDBC URL前綴
useprefix=true
# 配置記錄 Log 例外,可去掉的結果集有error,info,batch,debug,statement,commit,rollback,result,resultset.
excludecategories=info,debug,result,commit,resultset
# 日期格式
dateformat=yyyy-MM-dd HH:mm:ss
# 實際驅動可多個
#driverlist=org.h2.Driver
# 是否開啟慢SQL記錄
outagedetection=true
# 慢SQL記錄標準 2 秒
outagedetectioninterval=2
注意 p6spy
的配置
spring:
datasource:
# 原本基本連線設定是下面, 但要使用分析工具, 要做一些代理的調整.
# driver-class-name: com.mysql.cj.jdbc.Driver
# url: jdbc:mysql://localhost:3306/your_schema
# Mybatis-plus 性能分析工具 url 增加 p6spy 代理分析 sql
driver-class-name: com.p6spy.engine.spy.P6SpyDriver
url: jdbc:p6spy:mysql://localhost:3306/your_schema
select
語句, 並且查看 console
內容 Consume Time:4 ms 2022-07-22 16:16:41
Execute SQL:SELECT * FROM you_table WHERE (id = 2)