MyBatis-Plus

主要紀錄應用的方式，詳細內容我想別人寫的比我還要清楚，這邊就附上我學習時的網站

連結：Here

使用心得

• 支援 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(); //執行

業務實戰應用

Select For Update

LambdaQueryWrapper wrapper = Wrappers.<Merchant>lambdaQuery()
		.eq(Objects.nonNull(merchantId), Merchant::getId, merchantId)
		.eq(Objects.nonNull(lastTradingTime), Merchant::getLastTradingTime, lastTradingTime)
		.last("for update");

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

Join Table 應用

第一種

// 預期的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();

Join Table 應用

第一種

// 自己撰寫ＳＱＬ, 再透過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)

第三種 -使用 mybatis-plus-join

作者GitHub

// 需要在 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

Join Table結論

使用 第一及第二種做法，還是擺脫不了自己撰寫SQL去實現Join的做法(若是有調整欄位，不容易在編譯期發現錯誤)，若是使用第三種mybatis-plus-join套件，會大大減少撰寫SQL繁瑣問題，亦可避免調整欄位導致的錯誤皆可在編譯期就會發生錯誤，雖說會有一些學習成本但結果是美好的，值得花一些時間去嘗試了解。

效能分析工具

spy.properties

# 參考網路上基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

datasource connect settings

注意 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)