所有分类
  • 所有分类
  • 未分类

MyBatis-Plus–自定义SQL

简介

说明

本文用实例介绍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

结果

前端结果

{
  "records": [
    {
      "id": 3,
      "userName": "aa",
      "nickName": "昵称1",
      "email": "333@qq.com",
      "createTime": "2021-01-12T13:12:21",
      "updateTime": "2021-01-12T13:12:21",
      "deletedFlag": 0
    },
    {
      "id": 4,
      "userName": "bb",
      "nickName": "昵称2",
      "email": "444@qq.com",
      "createTime": "2021-02-11T18:12:21",
      "updateTime": "2021-02-11T18:12:21",
      "deletedFlag": 0
    },
    {
      "id": 5,
      "userName": "cc",
      "nickName": "昵称3",
      "email": "555@qq.com",
      "createTime": "2021-03-24T18:12:21",
      "updateTime": "2021-03-24T18:12:21",
      "deletedFlag": 0
    }
  ],
  "total": 3,
  "size": 10,
  "current": 1,
  "orders": [],
  "optimizeCountSql": true,
  "searchCount": true,
  "countId": null,
  "maxLimit": null,
  "pages": 1
}

后端结果

Creating a new SqlSession
SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@699ca44c] was not registered for synchronization because synchronization is not active
JDBC Connection [HikariProxyConnection@1734039311 wrapping com.mysql.cj.jdbc.ConnectionImpl@5280daae] will not be managed by Spring
==>  Preparing: SELECT COUNT(*) AS total FROM t_user WHERE (nick_name LIKE ?)
==> Parameters: %昵称%(String)
<==    Columns: total
<==        Row: 3
<==      Total: 1
==>  Preparing: SELECT * FROM t_user WHERE (nick_name LIKE ?) LIMIT ?
==> Parameters: %昵称%(String), 10(Long)
<==    Columns: id, user_name, nick_name, email, create_time, update_time, deleted_flag
<==        Row: 3, aa, 昵称1, 333@qq.com, 2021-01-12 13:12:21, 2021-01-12 13:12:21, 0
<==        Row: 4, bb, 昵称2, 444@qq.com, 2021-02-11 18:12:21, 2021-02-11 18:12:21, 0
<==        Row: 5, cc, 昵称3, 555@qq.com, 2021-03-24 18:12:21, 2021-03-24 18:12:21, 0
<==      Total: 3
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@699ca44c]

用法大全

代码

Mapper

package com.example.business.customsql.mapper;

import com.baomidou.mybatisplus.core.conditions.Wrapper;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.example.business.customsql.entity.User;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface UserMapper extends BaseMapper<User> {
    // 条件放到wrapper
    @Select("SELECT * FROM t_user ${ew.customSqlSegment}")
    List<User> testSelect1(@Param("ew") Wrapper wrapper);
    // 也可以这么写
    // List<User> testSelect1(@Param(Constants.WRAPPER) Wrapper wrapper);

    // 条件放到wrapper,指定表名
    @Select("SELECT * FROM ${table_name} ${ew.customSqlSegment}")
    List<User> testSelect2(@Param("table_name") String tableName, @Param("ew") Wrapper wrapper);

    // 条件放到wrapper,指定表名、所需字段
    @Select("SELECT ${ew.SqlSelect} FROM ${table_name} ${ew.customSqlSegment}")
    List<User> testSelect3(@Param("table_name") String tableName, @Param("ew") Wrapper wrapper);

    // 条件放到wrapper,指定某个字段,指定表名、所需字段
    @Select("SELECT ${ew.SqlSelect} FROM ${table_name} ${ew.customSqlSegment} AND age = #{age} ")
    List<User> testSelect4(@Param("table_name") String tableName,
                           @Param("age") Integer age, @Param("ew") Wrapper wrapper);
}

Controller

package com.example.business.customsql;

import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.extension.conditions.query.LambdaQueryChainWrapper;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.toolkit.Wrappers;
import com.example.business.customsql.entity.User;
import com.example.business.customsql.mapper.UserMapper;
import com.example.business.customsql.service.UserService;
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.Arrays;
import java.util.List;
import java.util.Map;

@RestController
@RequestMapping("customSQL")
public class CustomSQLController {
    @Autowired
    private UserMapper userMapper;
    
    @Autowired
    private UserService userService;

    @GetMapping("test1234")
    public void test1234() {
        
        // 写法1:Wrappers获取LambdaQueryWrapper(推荐)
        LambdaQueryWrapper<User> queryWrapper = Wrappers.lambdaQuery();
        queryWrapper
                .eq(User::getUserName, "Tony2")
                .select(User::getId, User::getAge);
    
        // 写法2:通过service获得lambdaQuery。
        // 这样写有的版本会报错:MybatisPlusException: can not use this method for "getCustomSqlSegment"
        // LambdaQueryChainWrapper<User> queryWrapper = userService
		// 			.lambdaQuery
		// 			.eq(User::getUserName, "Tony2")
        //             .select(User::getId, User::getAge)

        // 写法3:QueryWrapper
        // QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        // queryWrapper.eq("name", "Tony2").select("id", "age");
        
        List<User> users1 = userMapper.testSelect1(queryWrapper);
        List<User> users2 = userMapper.testSelect2("t_user", queryWrapper);
        List<User> users3 = userMapper.testSelect3("t_user", queryWrapper);
        List<User> users4 = userMapper.testSelect4("t_user", 22, queryWrapper);

        System.out.println("----------------------------------------------------------");
        System.out.println(users1);
        System.out.println(users2);
        System.out.println(users3);
        System.out.println(users4);
    }
}
 

测试

访问:http://localhost:8080/customSQL/test1234

结果

JDBC Connection [HikariProxyConnection@1693577632 wrapping com.mysql.cj.jdbc.ConnectionImpl@55539d0] will not be managed by Spring
==>  Preparing: SELECT * FROM t_user WHERE (user_name = ?)
==> Parameters: Tony2(String)
<==    Columns: id, user_name, age
<==        Row: 2, Tony2, 22
<==        Row: 3, Tony2, 22
<==        Row: 4, Tony2, 23
<==      Total: 3
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@17030d24]
Creating a new SqlSession
SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@425ed15b] was not registered for synchronization because synchronization is not active
JDBC Connection [HikariProxyConnection@243999369 wrapping com.mysql.cj.jdbc.ConnectionImpl@55539d0] will not be managed by Spring
==>  Preparing: SELECT * FROM t_user WHERE (user_name = ?)
==> Parameters: Tony2(String)
<==    Columns: id, user_name, age
<==        Row: 2, Tony2, 22
<==        Row: 3, Tony2, 22
<==        Row: 4, Tony2, 23
<==      Total: 3
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@425ed15b]
Creating a new SqlSession
SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@38c16ae] was not registered for synchronization because synchronization is not active
JDBC Connection [HikariProxyConnection@938269581 wrapping com.mysql.cj.jdbc.ConnectionImpl@55539d0] will not be managed by Spring
==>  Preparing: SELECT id,age FROM t_user WHERE (user_name = ?)
==> Parameters: Tony2(String)
<==    Columns: id, age
<==        Row: 2, 22
<==        Row: 3, 22
<==        Row: 4, 23
<==      Total: 3
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@38c16ae]
Creating a new SqlSession
SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@53f471bc] was not registered for synchronization because synchronization is not active
JDBC Connection [HikariProxyConnection@808334346 wrapping com.mysql.cj.jdbc.ConnectionImpl@55539d0] will not be managed by Spring
==>  Preparing: SELECT id,age FROM t_user WHERE (user_name = ?) AND age = ?
==> Parameters: Tony2(String), 22(Integer)
<==    Columns: id, age
<==        Row: 2, 22
<==        Row: 3, 22
<==      Total: 2
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@53f471bc]
----------------------------------------------------------
[User(id=2, userName=Tony2, age=22), User(id=3, userName=Tony2, age=22), User(id=4, userName=Tony2, age=23)]
[User(id=2, userName=Tony2, age=22), User(id=3, userName=Tony2, age=22), User(id=4, userName=Tony2, age=23)]
[User(id=2, userName=null, age=22), User(id=3, userName=null, age=22), User(id=4, userName=null, age=23)]
[User(id=2, userName=null, age=22), User(id=3, userName=null, age=22)]

不常用操作

代码

Mapper

package com.example.business.customsql.mapper;

import com.baomidou.mybatisplus.core.conditions.Wrapper;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.example.business.customsql.entity.User;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.Map;

@Repository
public interface UserMapper extends BaseMapper<User> {
    // 指定某个条件,指定表名
    @Select("SELECT * FROM ${table_name} WHERE age = #{age}")
    List<User> testSelect5(@Param("table_name") String tableName, @Param("age") Integer age);

    // 条件放到wrapper,指定表名、所需字段,返回map
    @Select("SELECT ${ew.SqlSelect} FROM ${table_name} ${ew.customSqlSegment}")
    List<Map<String, Object>> testSelect6(@Param("table_name") String tableName, @Param("ew") Wrapper wrapper);

    // 只取name这一列
    @Select("SELECT t_user.user_name FROM t_user ${ew.customSqlSegment}")
    List<String> testSelect7(@Param("ew") Wrapper wrapper);

    // 条件放到wrapper,指定表名,只获取一个。若有多个则报错
    @Select("SELECT * FROM ${table_name} ${ew.customSqlSegment}")
    User testSelect8(@Param("table_name") String tableName, @Param("ew") Wrapper wrapper);

    // 测试更新
    @Update("UPDATE ${table_name} SET ${ew.sqlSet} ${ew.customSqlSegment}")
    int testUpdate1(@Param("table_name") String tableName, @Param("ew") Wrapper wrapper);
}

Controller

package com.example.business.customsql;

import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.extension.conditions.query.LambdaQueryChainWrapper;
import com.baomidou.mybatisplus.core.toolkit.Wrappers;
import com.example.business.customsql.entity.User;
import com.example.business.customsql.mapper.UserMapper;
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.Arrays;
import java.util.List;
import java.util.Map;

@RestController
@RequestMapping("customSQL")
public class CustomSQLController {
    @Autowired
    private UserMapper userMapper;

    @GetMapping("test5678")
    public void test5678() {
        List<User> users1 = userMapper.testSelect5("t_user", 22);
        LambdaQueryWrapper<User> queryWrapper = Wrappers.lambdaQuery();
        queryWrapper
                .eq(User::getUserName, "Tony2")
                .eq(User::getAge, 22)
                .select(User::getUserName, User::getAge);

        // 写法2  有service时可以这么写,此写法推荐使用。
		// LambdaQueryChainWrapper<User> queryWrapper = userService
		// 			.lambdaQuery
		// 			.eq(User::getUserName, "Tony2")
        //          .eq(User::getAge, 22)
        //          .select(User::getId, User::getAge)

        // 写法3
        // QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        // queryWrapper.eq("user_name", "Tony2").eq("age", 22);

        List<Map<String, Object>> users2 = userMapper.testSelect6("t_user", queryWrapper);
        List<String> userNames = userMapper.testSelect7(queryWrapper);

        LambdaQueryWrapper<User> queryWrapper1 = Wrappers.lambdaQuery();
        queryWrapper1
                .eq(User::getUserName, "Tony1")
                .eq(User::getAge, 21)
                .select(User::getUserName, User::getAge);
        User user = userMapper.testSelect8("t_user", queryWrapper1);

        System.out.println("----------------------------------------------------------");
        System.out.println(users1);
        System.out.println(users2);
        System.out.println(userNames);
        System.out.println(user);
    }
}

测试

访问:http://localhost:8080/customSQL/test5678

结果

JDBC Connection [HikariProxyConnection@570520501 wrapping com.mysql.cj.jdbc.ConnectionImpl@54a994a6] will not be managed by Spring
==>  Preparing: SELECT * FROM t_user WHERE age = ?
==> Parameters: 22(Integer)
<==    Columns: id, user_name, age
<==        Row: 2, Tony2, 22
<==        Row: 3, Tony2, 22
<==      Total: 2
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@55b1e390]
Creating a new SqlSession
SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@3589afac] was not registered for synchronization because synchronization is not active
JDBC Connection [HikariProxyConnection@672917088 wrapping com.mysql.cj.jdbc.ConnectionImpl@54a994a6] will not be managed by Spring
==>  Preparing: SELECT user_name,age FROM t_user WHERE (user_name = ? AND age = ?)
==> Parameters: Tony2(String), 22(Integer)
<==    Columns: user_name, age
<==        Row: Tony2, 22
<==        Row: Tony2, 22
<==      Total: 2
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@3589afac]
Creating a new SqlSession
SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@61942f9f] was not registered for synchronization because synchronization is not active
JDBC Connection [HikariProxyConnection@1111642269 wrapping com.mysql.cj.jdbc.ConnectionImpl@54a994a6] will not be managed by Spring
==>  Preparing: SELECT t_user.user_name FROM t_user WHERE (user_name = ? AND age = ?)
==> Parameters: Tony2(String), 22(Integer)
<==    Columns: user_name
<==        Row: Tony2
<==        Row: Tony2
<==      Total: 2
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@61942f9f]
Creating a new SqlSession
SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@5cf8e912] was not registered for synchronization because synchronization is not active
JDBC Connection [HikariProxyConnection@622095606 wrapping com.mysql.cj.jdbc.ConnectionImpl@54a994a6] will not be managed by Spring
==>  Preparing: SELECT * FROM t_user WHERE (user_name = ? AND age = ?)
==> Parameters: Tony1(String), 21(Integer)
<==    Columns: id, user_name, age
<==        Row: 1, Tony1, 21
<==      Total: 1
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@5cf8e912]
----------------------------------------------------------
[User(id=2, userName=Tony2, age=22), User(id=3, userName=Tony2, age=22)]
[{user_name=Tony2, age=22}, {user_name=Tony2, age=22}]
[Tony2, Tony2]
User(id=1, userName=Tony1, age=21)

IN的坑

简介

Mybatis-Plus无法在自定义SQL中直接使用IN查询,无论传参类型是List、ArrayList、数组、可变参数。

解决方法:将IN查询放到Wrapper中,然后传入自定义SQL。

代码

Mapper

package com.example.business.customsql.mapper;

import com.baomidou.mybatisplus.core.conditions.Wrapper;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.example.business.customsql.entity.User;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface UserMapper extends BaseMapper<User> {
    // 错误做法。Mybatis-Plus不支持自定义SQL中使用IN,参数类型是List、ArrayList、数组、可变参数都不行
    @Select("SELECT t_user.user_name FROM t_user WHERE id IN (#{ids}) AND user_name = #{userName}")
    List<User> testIn1(@Param("ids") List<Long> ids, @Param("userName")String userName);

    // 错误做法。Mybatis-Plus不支持自定义SQL中使用IN,参数类型是List、ArrayList、数组、可变参数都不行
    @Select("SELECT t_user.user_name FROM t_user WHERE id IN (#{ids}) AND user_name = #{userName}")
    List<User> testIn2(@Param("userName") String userName, @Param("ids") Long... ids);

    @Select("SELECT t_user.user_name FROM t_user ${ew.customSqlSegment} AND user_name = #{userName}")
    List<User> testIn3(@Param("ew") Wrapper wrapper, @Param("userName")String userName);
}

Controller

package com.example.business.customsql;

import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.toolkit.Wrappers;
import com.example.business.customsql.entity.User;
import com.example.business.customsql.mapper.UserMapper;
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.Arrays;
import java.util.List;

@RestController
@RequestMapping("customSQL")
public class CustomSQLController {
    @Autowired
    private UserMapper userMapper;

    @GetMapping("testIn")
    public void testIn() {
        Long[] ids = new Long[]{1L, 2L, 3L};
        List<Long> idList = Arrays.asList(ids);
        List<User> users1 = userMapper.testIn1(idList, "Tony2");
        List<User> users2 = userMapper.testIn2("Tony2", ids);

        LambdaQueryWrapper<User> queryWrapper = Wrappers.lambdaQuery();
        queryWrapper.in(User::getId, idList);
        List<User> users3 = userMapper.testIn3(queryWrapper, "Tony2");

        System.out.println("----------------------------------------------------------");
        System.out.println(users1);
        System.out.println(users2);
        System.out.println(users3);
    }
}

测试

访问:http://localhost:8080/customSQL/testIn

结果

JDBC Connection [HikariProxyConnection@1841994020 wrapping com.mysql.cj.jdbc.ConnectionImpl@73e5ef65] will not be managed by Spring
==>  Preparing: SELECT t_user.user_name FROM t_user WHERE id IN (?) AND user_name = ?
==> Parameters: [1, 2, 3](ArrayList), Tony2(String)
<==      Total: 0
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@7b3d58a6]
Creating a new SqlSession
SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@6e65ce0e] was not registered for synchronization because synchronization is not active
JDBC Connection [HikariProxyConnection@676282173 wrapping com.mysql.cj.jdbc.ConnectionImpl@73e5ef65] will not be managed by Spring
==>  Preparing: SELECT t_user.user_name FROM t_user WHERE id IN (?) AND user_name = ?
==> Parameters: [Ljava.lang.Long;@e4762e9(Long[]), Tony2(String)
<==      Total: 0
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@6e65ce0e]
Creating a new SqlSession
SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@3eadc23b] was not registered for synchronization because synchronization is not active
JDBC Connection [HikariProxyConnection@1413127542 wrapping com.mysql.cj.jdbc.ConnectionImpl@73e5ef65] will not be managed by Spring
==>  Preparing: SELECT t_user.user_name FROM t_user WHERE (id IN (?,?,?)) AND user_name = ?
==> Parameters: 1(Long), 2(Long), 3(Long), Tony2(String)
<==    Columns: user_name
<==        Row: Tony2
<==        Row: Tony2
<==      Total: 2
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@3eadc23b]
----------------------------------------------------------
[]
[]
[User(id=null, userName=Tony2, age=null), User(id=null, userName=Tony2, age=null)]

动态查询

简介

代码里经常遇到动态查询(多条件查询)的情况,mybatis-plus在查单表的时候直接用代码拼接动态条件即可,但多表的时候就只能手写sql了。

本处示例如何手写sql来进行动态查询。本处示例单表自定义SQL动态查询,多表是一样的用法。

代码

mapper

package com.example.business.customsql.mapper;

import com.baomidou.mybatisplus.core.conditions.Wrapper;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.example.business.customsql.entity.User;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface UserMapper extends BaseMapper<User> {
    // ----------------- 测试动态查询 ---------------------------------------------------------------------
    @Select("SELECT * FROM t_user WHERE " +
            " IF(#{userName} IS NOT NULL, t_user.user_name = #{userName}, 1=1)" +
            " AND IF(#{age} IS NOT NULL, t_user.age = #{age}, 1=1)")
    List<User> testDynamic1(@Param("userName") String userName, @Param("age") Integer age);
}

controller

package com.example.business.customsql;

import com.baomidou.mybatisplus.core.toolkit.Wrappers;
import com.example.business.customsql.entity.User;
import com.example.business.customsql.mapper.UserMapper;
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;

@RestController
@RequestMapping("customSQL")
public class CustomSQLController {
    @Autowired
    private UserMapper userMapper;

    @GetMapping("testDynamic")
    public void testDynamic() {
        List<User> users = userMapper.testDynamic1(null, 22);
        System.out.println(users);
    }
}

测试

访问:http:localhost:8080/customSQL/testDynamic

后端结果

JDBC Connection [HikariProxyConnection@245966168 wrapping com.mysql.cj.jdbc.ConnectionImpl@7e2d6fd9] will not be managed by Spring
==>  Preparing: SELECT * FROM t_user WHERE IF(? IS NOT NULL, t_user.user_name = ?, 1=1) AND IF(? IS NOT NULL, t_user.age = ?, 1=1)
==> Parameters: null, null, 22(Integer), 22(Integer)
<==    Columns: id, user_name, age
<==        Row: 2, Tony2, 22
<==        Row: 3, Tony2, 22
<==      Total: 2
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@3e93302e]
[User(id=2, userName=Tony2, age=22), User(id=3, userName=Tony2, age=22)]
0

评论0

请先

显示验证码
没有账号?注册  忘记密码?

社交账号快速登录