被一位广州回来的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