# MyBatis-Plus 主要紀錄應用的方式，詳細內容我想別人寫的比我還要清楚，這邊就附上我學習時的網站 連結：[Here](https://juejin.cn/post/6966116536784273422) ### 使用心得 * 支援 Lambda 表達式，應用各式查詢、修改等方法 * 支援 ActiveRecord 形式使用，僅需繼承 Model 就可以使用 * 自動注入基本 CURD * 內建程式產生器(Service\[impl]、Mapper\[xml]、Entity....) * 內建SQL效能分析插件(雖然3.5.2已移除,改用第三方工具(p6spy)) * 內建基於 Mybatis 物理分頁插件加以優化，使用更方便 * 更專心於業務邏輯程式開發 ### 內建程式產生器 ```java // 官方文件: 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 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 ```java LambdaQueryWrapper wrapper = Wrappers.lambdaQuery() .eq(Objects.nonNull(merchantId), Merchant::getId, merchantId) .eq(Objects.nonNull(lastTradingTime), Merchant::getLastTradingTime, lastTradingTime) .last("for update"); ``` #### Update 第一種 ```java 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.lambdaQuery() .eq(OrderInfo::getOrderNo, order.getOrderNo()); orderService.update(order, wrapper); ``` 第二種 ```java LambdaUpdateWrapper updateWrapper = Wrappers.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; ``` 第三種 -> 我自己用最多是這種 ```java 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 應用 #### 第一種 ```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 ``` ```java // LambdaQueryWrapper 組合 LambdaQueryWrapper queryWrapper = Wrappers.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); ``` ```java // mapper.java IPage findAll(IPage page, @Param(Constants.WRAPPER) Wrapper queryWrapper); ``` ```xml // mapper.xml ``` #### 第二種 ```sql // 與第一種相似做法,差別在於會再用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)) ``` ```java // LambdaQuery 組合 new Page(req.getPageNum(), req.getPageSize()), Wrappers.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](https://github.com/yulichang/mybatis-plus-join) ```java // 需要在 Service & Mapper 都加上相應的實作 // interface service -> MPJBaseService // serviceImpl -> MPJBaseServiceImpl // interface mapper -> MPJBaseMapper gameInfoService.selectJoinListPage( new Page<>(req.getPageNum(), req.getPageSize()), GameInfoRes.class, new MPJLambdaWrapper() .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 // 結果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 ```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` 的配置 ```yaml 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) ``` --- # Agent Instructions: Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question. Perform an HTTP GET request on the current page URL with the `ask` query parameter: ``` GET https://xu-min-chang.gitbook.io/caster-develop-note/java/mybatis-plus.md?ask= ``` The question should be specific, self-contained, and written in natural language. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.