mybatis-plus

warrah

已于 2024-01-25 15:28:29 修改

阅读量984

点赞数 3

分类专栏：岁月云——java基础岁月云——Web系统最佳实践文章标签： mybatis mybatis-plus

于 2022-12-10 09:07:09 首次发布

本文链接：https://blog.csdn.net/warrah/article/details/128261347

版权

岁月云——Web系统最佳实践同时被 2 个专栏收录

117 篇文章 0 订阅

订阅专栏

岁月云——java基础

52 篇文章 0 订阅

订阅专栏

被一位广州回来的95后程序员鄙视了，他认为使用原生mybatis，不用mybatis-plus就是技术落后，而且代码还不能自动生成，担心后期找不到工作，认为武汉怎么还停留在过去式。DAO层用什么很重要吗，重要的是对DAO和代码生成的理解。
14年我就写了代码自动生成，这个很难吗，mybatis-plus他封装了很好，但并不是我们开发的。mybatis-plus贡献者是值得点赞，但是会mybatis-plus就引以为傲了？又不是原创者。
代码生成很简单，搞一个freemarker或者velocity模板就可以实现，如果你懂设计模式，可以写的更为高可用的，或者还可以写出现在流行的低代码平台，虽然我很鄙视低代码平台，虽然我是也是搞技术的，但站在自身角度，扼杀同行工程师的创意，站在市场角度，只能节约成本，不能带来实际利益，成为老板们的工具人，何苦为难同为码农的我们自己呢？
1 mybatis-plus单数据源

<version.mybatis>3.5.9</version.mybatis>
<version.mybatis-spring>2.0.6</version.mybatis-spring>
<mybatis-plus.version>3.5.1</mybatis-plus.version>
<dependency>
    <groupId>org.mybatis</groupId>
    <artifactId>mybatis</artifactId>
    <version>${version.mybatis}</version>
</dependency>
<dependency>
    <groupId>org.mybatis</groupId>
    <artifactId>mybatis-spring</artifactId>
    <version>${version.mybatis-spring}</version>
</dependency>
 <!--MyBatis Plus 依赖-->
 <dependency>
     <groupId>com.baomidou</groupId>
     <artifactId>mybatis-plus-boot-starter</artifactId>
     <version>${mybatis-plus.version}</version>
 </dependency>
 <!--MyBatis Plus 代码生成器-->
 <dependency>
     <groupId>com.baomidou</groupId>
     <artifactId>mybatis-plus-generator</artifactId>
     <version>${mybatis-plus.version}</version>
 </dependency>
 <!--Velocity模板引擎-->
 <dependency>
     <groupId>org.apache.velocity</groupId>
     <artifactId>velocity-engine-core</artifactId>
     <version>${velocity.version}</version>
 </dependency>

这是我的一位同事写的，关于达梦数据库的自动生成。

import com.baomidou.mybatisplus.generator.AutoGenerator;
import com.baomidou.mybatisplus.generator.config.*;
import com.baomidou.mybatisplus.generator.config.querys.DMQuery;
import com.baomidou.mybatisplus.generator.config.rules.DateType;
import com.baomidou.mybatisplus.generator.config.rules.NamingStrategy;
import com.baomidou.mybatisplus.generator.engine.VelocityTemplateEngine;

import java.util.*;

/**
 * MyBatisPlus代码生成器
 */
public class MyBatisPlusGenerator {

    public static void main(String[] args) {

        // 数据库连接信息
        String url = "jdbc:dm://127.0.0.1:5236/TEST?useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=Asia/Shanghai&useSSL=true&characterEncoding=UTF-8";
        String username = "SYSDBA";
        String password = "xxxx";

        // 需要生成代码的表
        List<String> tableNames = new ArrayList<>();
        tableNames.add("SYS_USER");
        tableNames.add("SYS_ROLE");
        tableNames.add("SYS_USER_ROLE");
        tableNames.add("SYS_PERMISSION");
        tableNames.add("SYS_ROLE_PERMISSION");


        // 项目根目录
        String projectPath = System.getProperty("user.dir");
        // 项目标识
        String productName = "demo";
        /**
         * 基本路径
         */
        String SRC_MAIN_JAVA = "/src/main/java/com/xxx/"+productName;

        /**
         * xml路径
         */
        String XML_PATH = projectPath + "/demo-data" + SRC_MAIN_JAVA + "/mapping";
        /**
         * entity路径
         */
        String ENTITY_PATH = projectPath + "/demo-data" + SRC_MAIN_JAVA + "/pojo";
        /**
         * mapper路径
         */
        String MAPPER_PATH = projectPath + "/demo-data" + SRC_MAIN_JAVA + "/dao";
        /**
         * service路径
         */
        String SERVICE_PATH = projectPath + "/demo-service" + SRC_MAIN_JAVA + "/service";
        /**
         * serviceImpl路径
         */
        String SERVICE_IMPL_PATH = projectPath + "/demo-service" + SRC_MAIN_JAVA + "/service/impl";
        /**
         * controller路径
         */
        String CONTROLLER_PATH = projectPath + "/demo-rest" + SRC_MAIN_JAVA + "/controller";
        Map<OutputFile, String> pathInfo = new HashMap<>(5);
        pathInfo.put(OutputFile.entity, ENTITY_PATH);
        pathInfo.put(OutputFile.mapper, MAPPER_PATH);
        pathInfo.put(OutputFile.service, SERVICE_PATH);
        pathInfo.put(OutputFile.serviceImpl, SERVICE_IMPL_PATH);
        pathInfo.put(OutputFile.controller, CONTROLLER_PATH);
        pathInfo.put(OutputFile.mapperXml, XML_PATH);

        // 代码生成器
        AutoGenerator autoGenerator = new AutoGenerator(initDataSourceConfig(url, username, password));
        autoGenerator.global(initGlobalConfig());
        autoGenerator.packageInfo(initPackageConfig(productName, pathInfo));
        autoGenerator.injection(initInjectionConfig());
        autoGenerator.template(initTemplateConfig());
        autoGenerator.strategy(initStrategyConfig(tableNames));
        autoGenerator.execute(new VelocityTemplateEngine());
    }

    /**
     * 初始化全局配置
     */
    private static GlobalConfig initGlobalConfig() {
        return new GlobalConfig.Builder()
                .disableOpenDir()
                .enableSwagger()
                .fileOverride()
                .dateType(DateType.ONLY_DATE)
                .author("xxx")
                .build();
    }

    /**
     * 初始化数据源配置
     */
    private static DataSourceConfig initDataSourceConfig(String url, String username, String password) {
        return new DataSourceConfig.Builder(url,username,password)
                .dbQuery(new DMQuery())
                .build();
    }

    /**
     * 初始化包配置
     */
    private static PackageConfig initPackageConfig(String productName, Map<OutputFile, String> pathInfo) {
        return new PackageConfig.Builder()
                .parent("com.xxx."+productName)
                .entity("pojo")
                .mapper("dao")
                .service("service")
                .serviceImpl("service.impl")
                .controller("controller")
                .pathInfo(pathInfo)
                .build();
    }

    /**
     * 初始化模板配置
     */
    private static TemplateConfig initTemplateConfig() {
        //可以对controller、service、entity模板进行配置
        return new TemplateConfig.Builder()
                .controller("templates/controller.java.vm")
                .build();
    }

    /**
     * 初始化策略配置
     */
    private static StrategyConfig initStrategyConfig(List<String> tableNames) {
        StrategyConfig.Builder builder = new StrategyConfig.Builder();
                builder.entityBuilder()
                .naming(NamingStrategy.underline_to_camel)
                .columnNaming(NamingStrategy.underline_to_camel)
                .enableLombok()
                .formatFileName("%s")
                .mapperBuilder()
                .enableBaseResultMap()
                .enableBaseColumnList()
                .formatMapperFileName("%sMapper")
                .formatXmlFileName("%sMapper")
                .serviceBuilder()
                .formatServiceFileName("%sService")
                .formatServiceImplFileName("%sServiceImpl")
                .controllerBuilder()
                .enableRestStyle()
                .formatFileName("%sController");
        builder.addInclude(tableNames);
        return builder.build();
    }

    /**
     * 初始化自定义配置
     */
    private static InjectionConfig initInjectionConfig() {
        // 自定义配置
        return new InjectionConfig.Builder().build();
    }

}

其实也没必要这么干，下载easycode插件，使用velocity，你想什么就可以生成。

分页插件

import com.baomidou.mybatisplus.annotation.DbType;
import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;
import com.baomidou.mybatisplus.extension.plugins.inner.PaginationInnerInterceptor;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

@Configuration
public class MyBatisConfig {
    @Bean
    public MybatisPlusInterceptor mybatisPlusInterceptor() {
        MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
        interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.MYSQL));
        return interceptor;
    }
}

配置

mybatis-plus:
  configuration:
    map-underscore-to-camel-case: true
    auto-mapping-behavior: full
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
  mapper-locations: classpath*:com/xxx/**/mapping/*.xml
  global-config:
    # 逻辑删除配置
    db-config:
      # 删除前
      logic-not-delete-value: 1
      # 删除后
      logic-delete-value: 0

对于非事务的操作，打印日志如下

JDBC Connection [HikariProxyConnection@1259852129 wrapping com.mysql.cj.jdbc.ConnectionImpl@4a46d6d5] will not be managed by Spring
==>  Preparing: SELECT COUNT(*) AS total FROM sj_hzyx
==> Parameters: 
<==    Columns: total
<==        Row: 0
<==      Total: 1
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@6f4cd704]

2 mybatis-plus联表查询

<mybatis-plus-join.version>1.3.11</mybatis-plus-join.version>
<dependency>
      <groupId>com.github.yulichang</groupId>
      <artifactId>mybatis-plus-join</artifactId>
      <version>${mybatis-plus-join.version}</version>
  </dependency>


@Data
public class SysUserDto  extends SysUser {

    @ApiModelProperty("角色")
    private Integer roleId;
    @ApiModelProperty("角色名称")
    private String roleName;
}

 @ApiOperation("分页查询列表")
    @PostMapping("find")
    public ResponseResultList<SysUserDto> find(SysUserCondDto condDto){
        IPage<SysUserDto> page = sysUserService.page(condDto);
        ResponseResultList<SysUserDto> response = new ResponseResultList<>(true, "操作成功");
        // 数据转换
        response.setData(page.getRecords());
        response.setTotalCount((int)page.getTotal());
        return response;
    }

@Override
    public IPage<SysUserDto> page(SysUserCondDto condDto) {
        MPJLambdaWrapper<SysUser> mpjLambdaWrapper = new MPJLambdaWrapper<>();
        mpjLambdaWrapper.selectAll(SysUser.class)
                .selectAs(SysRole::getId,SysUserDto::getRoleId)
                .selectAs(SysRole::getName,SysUserDto::getRoleName)
                .innerJoin(SysUserRole.class, SysUserRole::getUserId,SysUser::getId)
                .innerJoin(SysRole.class, SysRole::getId,SysUserRole::getRoleId);
        if (!CheckEmptyUtil.isEmpty(condDto.getName())){
            mpjLambdaWrapper.like(SysUser::getName,condDto.getName());
        }
        Page<SysUser> page = new Page<>();
        IPage<SysUserDto> sysUserDtoIPage = sysUserMapper.selectJoinPage(page,SysUserDto.class,mpjLambdaWrapper);
        return sysUserDtoIPage;
    }

3 单条件、联表、复杂对象查询
这里要参考 MyBatis-Plus-Join官网
虽然是一个联表查询，封装的确实一个复杂的对象，查询条件只需要从主表中查询。

解决方案

@Override
    public List<AccStaffInfoDto> select(Integer asId) {
        MPJLambdaWrapper<AccStaff> mpjLambdaWrapper = new MPJLambdaWrapper<>();
        mpjLambdaWrapper.selectAll(AccStaff.class)
                .selectCollection(AccStaffInfoDto::getInsuranceDtos,map->map
                    .id(AccStaffInsurance::getId)
                    .result(AccStaffInsurance::getStaffId)
                    .result(AccStaffInsurance::getInsuranceId)
                    .result(AccStaffInsurance::getBaseAmount)
                    .result(AccStaffInsurance::getCompanyRatio)
                    .result(AccStaffInsurance::getCompanyAmount)
                    .result(AccStaffInsurance::getPersonalRatio)
                    .result(AccStaffInsurance::getPersonalAmount)
                    .result(AccInsuranceSetting::getName,AccStaffInsuranceDto::getInsuranceName)
                    )
                    .leftJoin(AccStaffInsurance.class,AccStaffInsurance::getStaffId,AccStaff::getId)
                    .leftJoin(AccInsuranceSetting.class,on->on
                        .eq(AccInsuranceSetting::getId,AccStaffInsurance::getInsuranceId)
                        .eq(AccInsuranceSetting::getAsId,AccStaff::getAsId));
        mpjLambdaWrapper.eq(AccStaff::getAsId,asId);
        return accStaffService.selectJoinList(AccStaffInfoDto.class,mpjLambdaWrapper);
    }

注意版本,低版本的有些写法不支持。

<dependency>
                <groupId>com.github.yulichang</groupId>
                <artifactId>mybatis-plus-join-boot-starter</artifactId>
                <version>1.4.5</version>
            </dependency>

4 拼接的查询条件如何处理

 <select id="getStaffList" resultMap="BaseResultMap">
        SELECT
        <include refid="com.test.acc.salary.dao.AccStaffMapper.Base_Column_List">
        </include>
        FROM `acc_staff`
        WHERE as_id = #{asId,jdbcType=INTEGER} and `status` IN ('01','02')
        <if test="nameOrPhone!=null and nameOrPhone != '' ">
            and (`name` like concat('%', #{nameOrPhone}, '%') or phone like concat('%', #{nameOrPhone}, '%'))
        </if>
    </select>

这种单表的属于很简单的类型

LambdaQueryWrapper<AccStaff> queryWrapper = new LambdaQueryWrapper<>();
        queryWrapper.eq(AccStaff::getAsId,asId);
        List<String> statuses = Arrays.asList("01","02");
        queryWrapper.in(AccStaff::getStatus,statuses);
        queryWrapper.and(wrapper->wrapper.like(AccStaff::getCode,nameOrPhone).or().like(AccStaff::getName,nameOrPhone));
        return accStaffService.list(queryWrapper);

5 删除操作返回数量
service中调用删除方法，是被包裹了一层，因此需要使用mapper中的操作。
因为有的时候需要返回值，告诉用户成功删除了多少条数据，失败了多少条数据。

调整如下

   @Transactional
    @Override
    public int delete(Integer asId, List<Integer> ids, String approveStatus) {
        MPJLambdaWrapper<AccVoucher> mpjLambdaWrapper = new MPJLambdaWrapper<>();
        mpjLambdaWrapper.eq(AccVoucher::getAsId,asId);
        mpjLambdaWrapper.in(AccVoucher::getId,ids);
        mpjLambdaWrapper.eq(AccVoucher::getApproveStatus,approveStatus);
        return accVoucherMapper.delete(mpjLambdaWrapper);
    }

6 空值更新
mybatis默认如果null不会去更新，但实际业务会遇到一些情况，需要恢复某些字段为空值，那么怎么处理呢？

 @Transactional
    @Override
    public boolean updateMessage(AccTaxDeclare record) {
        LambdaUpdateWrapper<AccTaxDeclare> updateWrapper = new LambdaUpdateWrapper<>();
        updateWrapper.set(AccTaxDeclare::getMessage,null)
                .eq(AccTaxDeclare::getId,record.getId())
                .eq(AccTaxDeclare::getAsId,record.getAsId());
        return update(record,updateWrapper);
    }

7 取数别名
类似DATE_FORMAT(accInvoice.billing_date, '%Y-%m-%d') 这样的如何写呢？

 <select id="selectByExtExample" resultMap="accInvoiceDto">
        select accInvoiceVoucher.voucher_id,accVoucher.group_id,accVoucher.num,accVoucherGroup.name groupName,
        accInvoiceTemplate.name businessTypeName,accVoucher.period,
        DATE_FORMAT(accInvoice.billing_date, '%Y-%m-%d') billingDateStr,
        DATE_FORMAT(accInvoice.authentication_date, '%Y-%m-%d') authenticationDateStr,
        <include refid="com.whty.acc.invoice.dao.AccInvoiceMapper.Base_Column_List_Udf"/>,
        <include refid="com.whty.acc.invoice.dao.AccInvoiceDetailMapper.Base_Column_List_Udf"/>
        from acc_invoice accInvoice
        left join acc_invoice_detail accInvoiceDetail on accInvoice.id = accInvoiceDetail.invoice_id
        left join acc_invoice_voucher accInvoiceVoucher on accInvoice.id = accInvoiceVoucher.invoice_id
        left join acc_voucher accVoucher on accInvoiceVoucher.voucher_id = accVoucher.id
        and accInvoice.as_id = accVoucher.as_id
        left join acc_voucher_group accVoucherGroup on accVoucherGroup.id = accVoucher.group_id
        and accVoucherGroup.as_id = accInvoice.as_id
        left join acc_invoice_template accInvoiceTemplate on accInvoiceTemplate.as_id = accInvoice.as_id
        and accInvoiceTemplate.id = accInvoice.business_type
        <if test="ew.emptyOfWhere == false">
            ${ew.customSqlSegment}
        </if>
    </select>

解决方案

AccInvoiceListResp resp = new AccInvoiceListResp();
        MPJLambdaWrapper<AccInvoice> mpjLambdaWrapper = new MPJLambdaWrapper<>();
        mpjLambdaWrapper.selectAll(AccInvoice.class)
                .selectAs(AccVoucher::getId,AccInvoiceDto::getVoucherId)
                .selectAs(AccVoucher::getGroupId,AccInvoiceDto::getGroupId)
                .selectAs(AccVoucher::getNum,AccInvoiceDto::getNum)
                .selectAs(AccVoucherGroup::getName,AccInvoiceDto::getGroupName)
                .selectFunc(()->"DATE_FORMAT(%s,'%%Y-%%m-%%d')",AccInvoice::getBillingDate,AccInvoiceDto::getBillingDateStr)
                .selectFunc(()->"DATE_FORMAT(%s,'%%Y-%%m-%%d')",AccInvoice::getAuthenticationDate,AccInvoiceDto::getAuthenticationDateStr)
                .selectAs(AccVoucher::getId,AccInvoiceDto::getPeriod)
                .selectAs(AccInvoiceTemplate::getName,AccInvoiceDto::getBusinessTypeName)
                .selectCollection(AccInvoiceDetail.class,AccInvoiceDto::getAccInvoiceDetails)
                .leftJoin(AccInvoiceDetail.class,AccInvoiceDetail::getInvoiceId,AccInvoice::getId)
                .leftJoin(AccInvoiceVoucher.class,AccInvoiceVoucher::getInvoiceId,AccInvoice::getId)
                .leftJoin(AccVoucher.class,on->on.eq(AccInvoiceVoucher::getVoucherId,AccVoucher::getId).eq(AccInvoiceVoucher::getAsId,AccVoucher::getAsId))
                .leftJoin(AccVoucherGroup.class,on->on.eq(AccVoucherGroup::getAsId,AccVoucher::getAsId).eq(AccVoucherGroup::getId,AccVoucher::getGroupId))
                .leftJoin(AccInvoiceTemplate.class,on->on.eq(AccInvoiceTemplate::getAsId,AccInvoice::getAsId).eq(AccInvoiceTemplate::getId,AccInvoice::getBusinessType));
        mpjLambdaWrapper.eq(AccInvoice::getAsId,queryInvoiceReq.getAsId());
        mpjLambdaWrapper.eq(AccInvoice::getInvoiceType,invoiceType);
        mpjLambdaWrapper.between(AccInvoice::getBillingDate,queryInvoiceReq.getStartBillingDate(), queryInvoiceReq.getEndBillingDate());

8 使用ShardingSphere-SQL多了排序的问题
下面是个很简单的查询

LambdaQueryWrapper<AccIdTable> queryWrapper = new LambdaQueryWrapper<>();
		queryWrapper.select(AccIdTable::getAsId)
				.groupBy(AccIdTable::getAsId)
				.having(String.format("count(*)>%d", cn));
		List<AccIdTable> accIdTables = list(queryWrapper);
		return accIdTables;

转义为sql，可以看到,Logic SQL是正确的

11:54:18.718 INFO  ShardingSphere-SQL - Logic SQL: SELECT  
as_id
  FROM   acc_id_table   
  GROUP BY as_id HAVING count(*)>20

但是实际sql却多了一段ORDER BY as_id,导致数据查询失败。这个问题是什么原因造成的呢？应该是ShardingSphere-SQL不支持having把

ShardingSphere-SQL - Actual SQL: acc1 ::: SELECT  
as_id
  FROM   acc_id_table   
  GROUP BY as_id ORDER BY as_id ASC  HAVING count(*)>20

warrah

关注

3
点赞
踩
1

收藏

觉得还不错? 一键收藏
打赏
0
评论
mybatis-plus

mybatis-plus
复制链接

扫一扫

专栏目录

mybatis-plus

“相关推荐”对你有帮助么？