原文网址:MyBatis-Plus--自定义SQL_IT利刃出鞘的博客-CSDN博客
简介
说明
本文用实例介绍MybatisPlus如何自定义SQL。
使用
自定义的sql使用Wrapper对版本有要求:mybatis-plus版本需要大于或等于3.0.7。
值 | 含义 |
ew.customSqlSegment | 条件构造器(Wrapper) |
ew.sqlSet | 所设置的列 |
ew.sqlSelect | 要select的列 |
特殊语句
操作 | 实例 | 说明 |
TRUNCATE | @Update("TRUNCATE xxx") | 不要用@Delete |
ALTER | @Update("ALTER xxx") | |
DELETE | @Delete("DELETE xxx") |
源码
包里边定义好的常量。我们可以直接用这些常量。
mybatis-plus-core-3.3.2.jar\com\baomidou\mybatisplus\core\toolkit\Constants.class
package com.baomidou.mybatisplus.core.toolkit;
public interface Constants extends StringPool {
String MYBATIS_PLUS = "mybatis-plus";
String MD5 = "MD5";
String AES = "AES";
String AES_CBC_CIPHER = "AES/CBC/PKCS5Padding";
String ENTITY = "et";
String ENTITY_DOT = "et.";
String WRAPPER = "ew";
String WRAPPER_DOT = "ew.";
String WRAPPER_ENTITY = "ew.entity";
String WRAPPER_SQLSEGMENT = "ew.sqlSegment";
String WRAPPER_EMPTYOFNORMAL = "ew.emptyOfNormal";
String WRAPPER_NONEMPTYOFNORMAL = "ew.nonEmptyOfNormal";
String WRAPPER_NONEMPTYOFENTITY = "ew.nonEmptyOfEntity";
String WRAPPER_EMPTYOFWHERE = "ew.emptyOfWhere";
String WRAPPER_NONEMPTYOFWHERE = "ew.nonEmptyOfWhere";
String WRAPPER_ENTITY_DOT = "ew.entity.";
String U_WRAPPER_SQL_SET = "ew.sqlSet";
String Q_WRAPPER_SQL_SELECT = "ew.sqlSelect";
String Q_WRAPPER_SQL_COMMENT = "ew.sqlComment";
String Q_WRAPPER_SQL_FIRST = "ew.sqlFirst";
String COLUMN_MAP = "cm";
String COLUMN_MAP_IS_EMPTY = "cm.isEmpty";
String COLLECTION = "coll";
String WHERE = "WHERE";
String MP_OPTLOCK_INTERCEPTOR = "oli";
String MP_OPTLOCK_VERSION_ORIGINAL = "MP_OPTLOCK_VERSION_ORIGINAL";
String MP_OPTLOCK_VERSION_COLUMN = "MP_OPTLOCK_VERSION_COLUMN";
String MP_OPTLOCK_ET_ORIGINAL = "MP_OPTLOCK_ET_ORIGINAL";
String WRAPPER_PARAM = "MPGENVAL";
String WRAPPER_PARAM_FORMAT = "#{%s.paramNameValuePairs.%s}";
}
分页
代码
Mapper
package com.example.demo.user.mapper;
import com.baomidou.mybatisplus.core.conditions.Wrapper;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.core.toolkit.Constants;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.example.demo.user.entity.User;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Repository;
@Repository
public interface UserMapper extends BaseMapper<User> {
// 自定义SQL的分页
@Select("SELECT * FROM t_user ${ew.customSqlSegment}")
IPage<User> findUser(IPage<User> page, @Param(Constants.WRAPPER) Wrapper wrapper);
}
Controller
package com.example.demo.user.controller;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.core.toolkit.Wrappers;
import com.baomidou.mybatisplus.extension.conditions.query.LambdaQueryChainWrapper;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.example.demo.user.entity.User;
import com.example.demo.user.mapper.UserMapper;
import com.example.demo.user.service.UserService;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import java.util.List;
@Api(tags = "分页")
@RestController
@RequestMapping("page")
public class PageController {
// 我为了简单直接注入mapper,项目中controller要通过service调mapper
@Autowired
private UserMapper userMapper;
@ApiOperation("自定义SQL")
@GetMapping("customSQL")
public IPage<User> customSQLPage(Page<User> page) {
LambdaQueryWrapper<User> wrapper = Wrappers.lambdaQuery();
wrapper.like(User::getNickName, "昵称");
// 这样写会报错:MybatisPlusException: can not use this method for "getCustomSqlSegment"
// LambdaQueryChainWrapper<User> wrapper = userService.lambdaQuery()
// .eq(User::getUserName, "sky");
return userMapper.findUser(page, wrapper);
}
}
测试
访问:http://localhost:8080/page/customSQL
上边是文章的部分内容,为便于维护,全文已转移到此网址:MyBatis-Plus-自定义SQL - 自学精灵