欢迎来到尧图网

客户服务 关于我们

您的位置：首页 > 汽车 > 时评 > mybatis-plus集成和关联表查询示例，免写sql

mybatis-plus集成和关联表查询示例，免写sql

2024/10/25 4:27:11 来源：https://blog.csdn.net/weixin_44006009/article/details/141162694  浏览:  次   关键词：mybatis-plus集成和关联表查询示例，免写sql
  1. 加入mybatis-plus依赖包
<!--        mybatis-plus依赖--><dependency><groupId>com.baomidou</groupId><artifactId>mybatis-plus</artifactId><version>3.5.5</version></dependency>
<!--        mybatis-plus启动依赖--><dependency><groupId>com.baomidou</groupId><artifactId>mybatis-plus-boot-starter</artifactId><version>3.5.5</version></dependency>
<!--        mybatis-plus关联查询启动依赖--><dependency><groupId>com.github.yulichang</groupId><artifactId>mybatis-plus-join</artifactId><version>1.4.10</version></dependency>
<!--        mybatis-plus需使用lombok--><dependency><groupId>org.projectlombok</groupId><artifactId>lombok</artifactId><version>1.18.4</version><scope>provided</scope></dependency>

2.对应的serviceImpl实现层需要继承相关对应MPJBaseServiceImpl<项目mapper,项目实体类>
在这里插入图片描述
3.对应的mapper层需要继承相关对应的 MPJBaseMapper
在这里插入图片描述
4.直接使用代码示例：
serviceImpl业务实现层
在这里插入图片描述

MPJLambdaWrapper<WithdrawCashMa> mpjLambdaWrapper = new MPJLambdaWrapper();mpjLambdaWrapper.selectAll(WithdrawCashMa.class)// 查询主表所有属性.selectAs(AgentUser::getName, WithdrawCashMa::getBrandName)// 将子表属性映射到主表属性.selectAs(AgentUserSign::getRealName, WithdrawCashMa::getFeeName)// 将子表属性映射到主表属性.leftJoin(AgentUser.class,AgentUser::getId,WithdrawCashMa::getUid)// 左连接子表.leftJoin(AgentUserSign.class, AgentUserSign::getUid,WithdrawCashMa::getUid)// 左连接子表.eqIfExists(WithdrawCashMa::getUid,2)// 查询条件.orderByDesc(WithdrawCashMa::getCreateTime);// 排序字段Page page = new Page(1,2);List<WithdrawCashMa> mapIPage = withdrawCashMaMapper.selectJoinList(mpjLambdaWrapper);

mapper层：
在这里插入图片描述

default List<WithdrawCashMa> selectJoinList(MPJLambdaWrapper<WithdrawCashMa> mpjLambdaWrapper){return selectJoinList(WithdrawCashMa.class, mpjLambdaWrapper);}

添加mybatis自动装配插件，分页等等：
MybatisPlusInnerInterceptorAutoConfiguration:

package com.jinchaoyun.manager.config;import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;
import com.baomidou.mybatisplus.extension.plugins.inner.InnerInterceptor;
import org.springframework.boot.autoconfigure.condition.ConditionalOnBean;
import org.springframework.boot.autoconfigure.condition.ConditionalOnMissingBean;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;import java.util.List;/*** @author * @since 3.5.4*/
@Configuration
public class MybatisPlusInnerInterceptorAutoConfiguration {@Beanpublic MybatisPlusInterceptor defaultMybatisPlusInterceptor(List<InnerInterceptor> innerInterceptorList) {MybatisPlusInterceptor plusInterceptor = new MybatisPlusInterceptor();plusInterceptor.setInterceptors(innerInterceptorList);PaginationInnerInterceptor paginationInnerInterceptor = new PaginationInnerInterceptor(DbType.MYSQL);plusInterceptor.addInnerInterceptor(paginationInnerInterceptor);return plusInterceptor;}}

SqlConstants：

package com.jinchaoyun.manager.util;import com.baomidou.mybatisplus.annotation.DbType;/*** SQL相关常量类** @author */
public class SqlConstants {/*** 数据库的类型*/public static DbType DB_TYPE;public static void init(DbType dbType) {DB_TYPE = dbType;}}

如果Mapper层需要更多的lamda表达式方法，需要添加：
在这里插入图片描述
LambdaQueryWrapperX：

package com.jinchaoyun.manager.mapper.query;import cn.hutool.core.util.ArrayUtil;
import cn.hutool.core.util.ObjectUtil;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.toolkit.support.SFunction;
import org.apache.commons.lang3.ArrayUtils;
import org.springframework.util.StringUtils;import java.util.Collection;/*** 拓展 MyBatis Plus QueryWrapper 类，主要增加如下功能：* <p>* 1. 拼接条件的方法，增加 xxxIfPresent 方法，用于判断值不存在的时候，不要拼接到条件中。** @param <T> 数据类型*/
public class LambdaQueryWrapperX<T> extends LambdaQueryWrapper<T> {public LambdaQueryWrapperX<T> likeIfPresent(SFunction<T, ?> column, String val) {if (StringUtils.hasText(val)) {return (LambdaQueryWrapperX<T>) super.like(column, val);}return this;}public LambdaQueryWrapperX<T> inIfPresent(SFunction<T, ?> column, Collection<?> values) {if (ObjectUtil.isAllNotEmpty(values) && !ArrayUtil.isEmpty(values)) {return (LambdaQueryWrapperX<T>) super.in(column, values);}return this;}public LambdaQueryWrapperX<T> inIfPresent(SFunction<T, ?> column, Object... values) {if (ObjectUtil.isAllNotEmpty(values) && !ArrayUtil.isEmpty(values)) {return (LambdaQueryWrapperX<T>) super.in(column, values);}return this;}public LambdaQueryWrapperX<T> eqIfPresent(SFunction<T, ?> column, Object val) {if (ObjectUtil.isNotEmpty(val)) {return (LambdaQueryWrapperX<T>) super.eq(column, val);}return this;}public LambdaQueryWrapperX<T> neIfPresent(SFunction<T, ?> column, Object val) {if (ObjectUtil.isNotEmpty(val)) {return (LambdaQueryWrapperX<T>) super.ne(column, val);}return this;}public LambdaQueryWrapperX<T> gtIfPresent(SFunction<T, ?> column, Object val) {if (val != null) {return (LambdaQueryWrapperX<T>) super.gt(column, val);}return this;}public LambdaQueryWrapperX<T> geIfPresent(SFunction<T, ?> column, Object val) {if (val != null) {return (LambdaQueryWrapperX<T>) super.ge(column, val);}return this;}public LambdaQueryWrapperX<T> ltIfPresent(SFunction<T, ?> column, Object val) {if (val != null) {return (LambdaQueryWrapperX<T>) super.lt(column, val);}return this;}public LambdaQueryWrapperX<T> leIfPresent(SFunction<T, ?> column, Object val) {if (val != null) {return (LambdaQueryWrapperX<T>) super.le(column, val);}return this;}public LambdaQueryWrapperX<T> betweenIfPresent(SFunction<T, ?> column, Object val1, Object val2) {if (val1 != null && val2 != null) {return (LambdaQueryWrapperX<T>) super.between(column, val1, val2);}if (val1 != null) {return (LambdaQueryWrapperX<T>) ge(column, val1);}if (val2 != null) {return (LambdaQueryWrapperX<T>) le(column, val2);}return this;}public LambdaQueryWrapperX<T> betweenIfPresent(SFunction<T, ?> column, Object[] values) {Object val1 = ArrayUtils.get(values, 0);Object val2 = ArrayUtils.get(values, 1);return betweenIfPresent(column, val1, val2);}// ========== 重写父类方法，方便链式调用 ==========@Overridepublic LambdaQueryWrapperX<T> eq(boolean condition, SFunction<T, ?> column, Object val) {super.eq(condition, column, val);return this;}@Overridepublic LambdaQueryWrapperX<T> eq(SFunction<T, ?> column, Object val) {super.eq(column, val);return this;}@Overridepublic LambdaQueryWrapperX<T> orderByDesc(SFunction<T, ?> column) {super.orderByDesc(true, column);return this;}@Overridepublic LambdaQueryWrapperX<T> last(String lastSql) {super.last(lastSql);return this;}@Overridepublic LambdaQueryWrapperX<T> in(SFunction<T, ?> column, Collection<?> coll) {super.in(column, coll);return this;}}

MPJLambdaWrapperX：

package com.jinchaoyun.manager.mapper.query;import cn.hutool.core.util.ArrayUtil;
import cn.hutool.core.util.ObjectUtil;
import com.baomidou.mybatisplus.core.toolkit.support.SFunction;
import com.github.yulichang.toolkit.MPJWrappers;
import com.github.yulichang.wrapper.MPJLambdaWrapper;
import org.apache.commons.lang3.ArrayUtils;
import org.springframework.util.StringUtils;import java.util.Collection;
import java.util.function.Consumer;/*** 拓展 MyBatis Plus Join QueryWrapper 类，主要增加如下功能：* <p>* 1. 拼接条件的方法，增加 xxxIfPresent 方法，用于判断值不存在的时候，不要拼接到条件中。** @param <T> 数据类型*/
public class MPJLambdaWrapperX<T> extends MPJLambdaWrapper<T> {public MPJLambdaWrapperX<T> likeIfPresent(SFunction<T, ?> column, String val) {MPJWrappers.lambdaJoin().like(column, val);if (StringUtils.hasText(val)) {return (MPJLambdaWrapperX<T>) super.like(column, val);}return this;}public MPJLambdaWrapperX<T> inIfPresent(SFunction<T, ?> column, Collection<?> values) {if (ObjectUtil.isAllNotEmpty(values) && !ArrayUtil.isEmpty(values)) {return (MPJLambdaWrapperX<T>) super.in(column, values);}return this;}public MPJLambdaWrapperX<T> inIfPresent(SFunction<T, ?> column, Object... values) {if (ObjectUtil.isAllNotEmpty(values) && !ArrayUtil.isEmpty(values)) {return (MPJLambdaWrapperX<T>) super.in(column, values);}return this;}public MPJLambdaWrapperX<T> eqIfPresent(SFunction<T, ?> column, Object val) {if (ObjectUtil.isNotEmpty(val)) {return (MPJLambdaWrapperX<T>) super.eq(column, val);}return this;}public MPJLambdaWrapperX<T> neIfPresent(SFunction<T, ?> column, Object val) {if (ObjectUtil.isNotEmpty(val)) {return (MPJLambdaWrapperX<T>) super.ne(column, val);}return this;}public MPJLambdaWrapperX<T> gtIfPresent(SFunction<T, ?> column, Object val) {if (val != null) {return (MPJLambdaWrapperX<T>) super.gt(column, val);}return this;}public MPJLambdaWrapperX<T> geIfPresent(SFunction<T, ?> column, Object val) {if (val != null) {return (MPJLambdaWrapperX<T>) super.ge(column, val);}return this;}public MPJLambdaWrapperX<T> ltIfPresent(SFunction<T, ?> column, Object val) {if (val != null) {return (MPJLambdaWrapperX<T>) super.lt(column, val);}return this;}public MPJLambdaWrapperX<T> leIfPresent(SFunction<T, ?> column, Object val) {if (val != null) {return (MPJLambdaWrapperX<T>) super.le(column, val);}return this;}public MPJLambdaWrapperX<T> betweenIfPresent(SFunction<T, ?> column, Object val1, Object val2) {if (val1 != null && val2 != null) {return (MPJLambdaWrapperX<T>) super.between(column, val1, val2);}if (val1 != null) {return (MPJLambdaWrapperX<T>) ge(column, val1);}if (val2 != null) {return (MPJLambdaWrapperX<T>) le(column, val2);}return this;}public MPJLambdaWrapperX<T> betweenIfPresent(SFunction<T, ?> column, Object[] values) {Object val1 = ArrayUtils.get(values, 0);Object val2 = ArrayUtils.get(values, 1);return betweenIfPresent(column, val1, val2);}// ========== 重写父类方法，方便链式调用 ==========@Overridepublic <X> MPJLambdaWrapperX<T> eq(boolean condition, SFunction<X, ?> column, Object val) {super.eq(condition, column, val);return this;}@Overridepublic <X> MPJLambdaWrapperX<T> eq(SFunction<X, ?> column, Object val) {super.eq(column, val);return this;}@Overridepublic <X> MPJLambdaWrapperX<T> orderByDesc(SFunction<X, ?> column) {//noinspection uncheckedsuper.orderByDesc(true, column);return this;}@Overridepublic MPJLambdaWrapperX<T> last(String lastSql) {super.last(lastSql);return this;}@Overridepublic <X> MPJLambdaWrapperX<T> in(SFunction<X, ?> column, Collection<?> coll) {super.in(column, coll);return this;}@Overridepublic MPJLambdaWrapperX<T> selectAll(Class<?> clazz) {super.selectAll(clazz);return this;}@Overridepublic MPJLambdaWrapperX<T> selectAll(Class<?> clazz, String prefix) {super.selectAll(clazz, prefix);return this;}@Overridepublic <S> MPJLambdaWrapperX<T> selectAs(SFunction<S, ?> column, String alias) {super.selectAs(column, alias);return this;}@Overridepublic <E> MPJLambdaWrapperX<T> selectAs(String column, SFunction<E, ?> alias) {super.selectAs(column, alias);return this;}@Overridepublic <S, X> MPJLambdaWrapperX<T> selectAs(SFunction<S, ?> column, SFunction<X, ?> alias) {super.selectAs(column, alias);return this;}@Overridepublic <E, X> MPJLambdaWrapperX<T> selectAs(String index, SFunction<E, ?> column, SFunction<X, ?> alias) {super.selectAs(index, column, alias);return this;}@Overridepublic <E> MPJLambdaWrapperX<T> selectAsClass(Class<E> source, Class<?> tag) {super.selectAsClass(source, tag);return this;}@Overridepublic <E, F> MPJLambdaWrapperX<T> selectSub(Class<E> clazz, Consumer<MPJLambdaWrapper<E>> consumer, SFunction<F, ?> alias) {super.selectSub(clazz, consumer, alias);return this;}@Overridepublic <E, F> MPJLambdaWrapperX<T> selectSub(Class<E> clazz, String st, Consumer<MPJLambdaWrapper<E>> consumer, SFunction<F, ?> alias) {super.selectSub(clazz, st, consumer, alias);return this;}@Overridepublic <S> MPJLambdaWrapperX<T> selectCount(SFunction<S, ?> column) {super.selectCount(column);return this;}@Overridepublic MPJLambdaWrapperX<T> selectCount(Object column, String alias) {super.selectCount(column, alias);return this;}@Overridepublic <X> MPJLambdaWrapperX<T> selectCount(Object column, SFunction<X, ?> alias) {super.selectCount(column, alias);return this;}@Overridepublic <S, X> MPJLambdaWrapperX<T> selectCount(SFunction<S, ?> column, String alias) {super.selectCount(column, alias);return this;}@Overridepublic <S, X> MPJLambdaWrapperX<T> selectCount(SFunction<S, ?> column, SFunction<X, ?> alias) {super.selectCount(column, alias);return this;}@Overridepublic <S> MPJLambdaWrapperX<T> selectSum(SFunction<S, ?> column) {super.selectSum(column);return this;}@Overridepublic <S, X> MPJLambdaWrapperX<T> selectSum(SFunction<S, ?> column, String alias) {super.selectSum(column, alias);return this;}@Overridepublic <S, X> MPJLambdaWrapperX<T> selectSum(SFunction<S, ?> column, SFunction<X, ?> alias) {super.selectSum(column, alias);return this;}@Overridepublic <S> MPJLambdaWrapperX<T> selectMax(SFunction<S, ?> column) {super.selectMax(column);return this;}@Overridepublic <S, X> MPJLambdaWrapperX<T> selectMax(SFunction<S, ?> column, String alias) {super.selectMax(column, alias);return this;}@Overridepublic <S, X> MPJLambdaWrapperX<T> selectMax(SFunction<S, ?> column, SFunction<X, ?> alias) {super.selectMax(column, alias);return this;}@Overridepublic <S> MPJLambdaWrapperX<T> selectMin(SFunction<S, ?> column) {super.selectMin(column);return this;}@Overridepublic <S, X> MPJLambdaWrapperX<T> selectMin(SFunction<S, ?> column, String alias) {super.selectMin(column, alias);return this;}@Overridepublic <S, X> MPJLambdaWrapperX<T> selectMin(SFunction<S, ?> column, SFunction<X, ?> alias) {super.selectMin(column, alias);return this;}@Overridepublic <S> MPJLambdaWrapperX<T> selectAvg(SFunction<S, ?> column) {super.selectAvg(column);return this;}@Overridepublic <S, X> MPJLambdaWrapperX<T> selectAvg(SFunction<S, ?> column, String alias) {super.selectAvg(column, alias);return this;}@Overridepublic <S, X> MPJLambdaWrapperX<T> selectAvg(SFunction<S, ?> column, SFunction<X, ?> alias) {super.selectAvg(column, alias);return this;}@Overridepublic <S> MPJLambdaWrapperX<T> selectLen(SFunction<S, ?> column) {super.selectLen(column);return this;}@Overridepublic <S, X> MPJLambdaWrapperX<T> selectLen(SFunction<S, ?> column, String alias) {super.selectLen(column, alias);return this;}@Overridepublic <S, X> MPJLambdaWrapperX<T> selectLen(SFunction<S, ?> column, SFunction<X, ?> alias) {super.selectLen(column, alias);return this;}}

QueryWrapperX：

package com.jinchaoyun.manager.mapper.query;import cn.hutool.core.lang.Assert;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.toolkit.ArrayUtils;
import com.baomidou.mybatisplus.core.toolkit.CollectionUtils;
import com.jinchaoyun.manager.util.SqlConstants;
import org.springframework.util.StringUtils;import java.util.Collection;/*** 拓展 MyBatis Plus QueryWrapper 类，主要增加如下功能：** 1. 拼接条件的方法，增加 xxxIfPresent 方法，用于判断值不存在的时候，不要拼接到条件中。** @param <T> 数据类型*/
public class QueryWrapperX<T> extends QueryWrapper<T> {public QueryWrapperX<T> likeIfPresent(String column, String val) {if (StringUtils.hasText(val)) {return (QueryWrapperX<T>) super.like(column, val);}return this;}public QueryWrapperX<T> inIfPresent(String column, Collection<?> values) {if (!CollectionUtils.isEmpty(values)) {return (QueryWrapperX<T>) super.in(column, values);}return this;}public QueryWrapperX<T> inIfPresent(String column, Object... values) {if (!ArrayUtils.isEmpty(values)) {return (QueryWrapperX<T>) super.in(column, values);}return this;}public QueryWrapperX<T> eqIfPresent(String column, Object val) {if (val != null) {return (QueryWrapperX<T>) super.eq(column, val);}return this;}public QueryWrapperX<T> neIfPresent(String column, Object val) {if (val != null) {return (QueryWrapperX<T>) super.ne(column, val);}return this;}public QueryWrapperX<T> gtIfPresent(String column, Object val) {if (val != null) {return (QueryWrapperX<T>) super.gt(column, val);}return this;}public QueryWrapperX<T> geIfPresent(String column, Object val) {if (val != null) {return (QueryWrapperX<T>) super.ge(column, val);}return this;}public QueryWrapperX<T> ltIfPresent(String column, Object val) {if (val != null) {return (QueryWrapperX<T>) super.lt(column, val);}return this;}public QueryWrapperX<T> leIfPresent(String column, Object val) {if (val != null) {return (QueryWrapperX<T>) super.le(column, val);}return this;}public QueryWrapperX<T> betweenIfPresent(String column, Object val1, Object val2) {if (val1 != null && val2 != null) {return (QueryWrapperX<T>) super.between(column, val1, val2);}if (val1 != null) {return (QueryWrapperX<T>) ge(column, val1);}if (val2 != null) {return (QueryWrapperX<T>) le(column, val2);}return this;}public QueryWrapperX<T> betweenIfPresent(String column, Object[] values) {if (values!= null && values.length != 0 && values[0] != null && values[1] != null) {return (QueryWrapperX<T>) super.between(column, values[0], values[1]);}if (values!= null && values.length != 0 && values[0] != null) {return (QueryWrapperX<T>) ge(column, values[0]);}if (values!= null && values.length != 0 && values[1] != null) {return (QueryWrapperX<T>) le(column, values[1]);}return this;}// ========== 重写父类方法，方便链式调用 ==========@Overridepublic QueryWrapperX<T> eq(boolean condition, String column, Object val) {super.eq(condition, column, val);return this;}@Overridepublic QueryWrapperX<T> eq(String column, Object val) {super.eq(column, val);return this;}@Overridepublic QueryWrapperX<T> orderByDesc(String column) {super.orderByDesc(true, column);return this;}@Overridepublic QueryWrapperX<T> last(String lastSql) {super.last(lastSql);return this;}@Overridepublic QueryWrapperX<T> in(String column, Collection<?> coll) {super.in(column, coll);return this;}/*** 设置只返回最后一条** 不是完美解，需要在思考下。如果使用多数据源，并且数据源是多种类型时，可能会存在问题：实现之返回一条的语法不同** @return this*/public QueryWrapperX<T> limitN(int n) {Assert.notNull(SqlConstants.DB_TYPE, "获取不到数据库的类型");switch (SqlConstants.DB_TYPE) {case ORACLE:case ORACLE_12C:super.le("ROWNUM", n);break;case SQL_SERVER:case SQL_SERVER2005:super.select("TOP " + n + " *"); // 由于 SQL Server 是通过 SELECT TOP 1 实现限制一条，所以只好使用 * 查询剩余字段break;default:super.last("LIMIT " + n);}return this;}}

版权声明:

本网仅为发布的内容提供存储空间，不对发表、转载的内容提供任何形式的保证。凡本网注明“来源：XXX网络”的作品，均转载自其它媒体，著作权归作者所有，商业转载请联系作者获得授权，非商业转载请注明出处。

我们尊重并感谢每一位作者，均已注明文章来源和作者。如因作品内容、版权或其它问题，请及时与我们联系，联系邮箱：809451989@qq.com，投稿邮箱：809451989@qq.com

相关资讯

热文排行

最新新闻

推荐新闻

热搜词

nginx 隐藏版本号与WEB服务器信息 webGIS无论在B端还是可视化大屏上，应用都十分普及。 Robot Framework命令和Tag运用 JavaWeb合集16-JWT令牌验证 Spring Boot植物健康系统：绿色科技的创新力量 顺序表(一)(数据结构)