SpringBoot-mybatisplus-@select用法

mybatisplus查询本生已经挺丰富，但有的时候还是想自己写sql语句，怎么写？这时候就需要使用@select来实现，具体用法如下：

1、数据准备

CREATE TABLE XY_DIC_BLOCK_T
(
  BLOCK_ID         VARCHAR2(10 BYTE),
  BLOCK_NAME       VARCHAR2(200 BYTE),
  PARENT_BLOCK_ID  VARCHAR2(10 BYTE),
  BLOCK_LEVEL      NUMBER(4),
  ICO               VARCHAR2(100 BYTE),
  ORDER_NUM        NUMBER(10,2)
);

Insert into XY_DIC_BLOCK_T (BLOCK_ID, BLOCK_NAME, BLOCK_LEVEL, ORDER_NUM) Values ('1', '后端开发', 1, 1);
Insert into XY_DIC_BLOCK_T (BLOCK_ID, BLOCK_NAME, BLOCK_LEVEL, ORDER_NUM) Values ('2', '前端开发', 1, 2);
COMMIT;

2、pom.xml文件

<dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
            <version>2.1.17.RELEASE</version>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <version>2.1.17.RELEASE</version>
            <scope>test</scope>
            <exclusions>
                <exclusion>
                    <groupId>org.junit.vintage</groupId>
                    <artifactId>junit-vintage-engine</artifactId>
                </exclusion>
            </exclusions>
        </dependency>

        <!-- 集成mybatis-plus -->
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.2.0</version>
        </dependency>

        <!-- oracle驱动 -->
        <dependency>
            <groupId>com.oracle</groupId>
            <artifactId>ojdbc6</artifactId>
            <version>11.2.0.3</version>
        </dependency>
    </dependencies>

3、配置文件

4、工程结构

5、源码

package com.example.demo.controller;


import com.example.demo.domain.Block;
import com.example.demo.mapper.BlockMapper;
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.ResponseBody;
import org.springframework.web.bind.annotation.RestController;


import java.util.List;

@RestController
@RequestMapping("/hello")
public class HelloController {

    @Autowired
    private BlockMapper blockMapper;

    @GetMapping("/list")
    @ResponseBody
    public List<Block> list() {
        //直接调用BaseMapper封装好的CRUD方法，就可实现无条件查询数据
        List<Block> list = blockMapper.selectList(null);
        return list;
    }

    @GetMapping("/sqlList")
    @ResponseBody
    public List<Block> sqlList(String level) {
        //调用没有参数的sql语句
        List<Block> list = blockMapper.sqlMany();
        return list;
    }

    @GetMapping("/sqlParmList")
    @ResponseBody
    public List<Block> sqlParmList(String level) {
        //调用有参数的sql语句，参数不能为null
        List<Block> list = blockMapper.sqlManyParm(level);
        return list;
    }

    @GetMapping("/sqlParmListNull")
    @ResponseBody
    public List<Block> sqlParmListNull(String level) {
        //调用有参数的sql语句，参数可以为null
        List<Block> list = blockMapper.sqlManyParmNull(level);
        return list;
    }

    @GetMapping("/sqlManyObject")
    @ResponseBody
    public List<Block> sqlManyObject(Block block) {
        //调用有参数的sql语句，参数以对象形式传递
        List<Block> list = blockMapper.sqlManyObject(block);
        return list;
    }
}

package com.example.demo.domain;


import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;


@TableName(value = "XY_DIC_BLOCK_T")
public class Block {
    private static final long serialVersionUID = 1L;

    @TableId
    private String blockId;

    private String blockName;

    private String parentBlockId;

    private String blockLevel;

    private int orderNum;

    public String getBlockId() {
        return blockId;
    }

    public void setBlockId(String blockId) {
        this.blockId = blockId;
    }

    public String getBlockName() {
        return blockName;
    }

    public void setBlockName(String blockName) {
        this.blockName = blockName;
    }

    public String getParentBlockId() {
        return parentBlockId;
    }

    public void setParentBlockId(String parentBlockId) {
        this.parentBlockId = parentBlockId;
    }

    public String getBlockLevel() {
        return blockLevel;
    }

    public void setBlockLevel(String blockLevel) {
        this.blockLevel = blockLevel;
    }

    public int getOrderNum() {
        return orderNum;
    }

    public void setOrderNum(int orderNum) {
        this.orderNum = orderNum;
    }

    @Override
    public String toString() {
        return "Block{" +
                "blockId='" + blockId + '\'' +
                ", blockName='" + blockName + '\'' +
                ", parentBlockId='" + parentBlockId + '\'' +
                ", blockLevel='" + blockLevel + '\'' +
                ", orderNum=" + orderNum +
                '}';
    }
}

package com.example.demo.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.example.demo.domain.Block;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface BlockMapper extends BaseMapper<Block> {

    @Select("select BLOCK_ID,BLOCK_NAME,PARENT_BLOCK_ID,BLOCK_LEVEL,ORDER_NUM from XY_DIC_BLOCK_T where block_level=1 " )
    public List<Block> sqlMany();

    @Select("select BLOCK_ID,BLOCK_NAME,PARENT_BLOCK_ID,BLOCK_LEVEL,ORDER_NUM from XY_DIC_BLOCK_T where block_level=#{level}"    )
    public List<Block> sqlManyParm(String level);

    @Select("<script> select BLOCK_ID,BLOCK_NAME,PARENT_BLOCK_ID,BLOCK_LEVEL,ORDER_NUM from XY_DIC_BLOCK_T where 1=1 " +
            "<if test='level != null'>" + " and block_level=#{level} " + "</if>" +
            "</script>")
    public List<Block> sqlManyParmNull(String level);

    @Select("<script> select BLOCK_ID,BLOCK_NAME,PARENT_BLOCK_ID,BLOCK_LEVEL,ORDER_NUM from XY_DIC_BLOCK_T where 1=1 " +
            "<if test='item.blockLevel != null'>" + " and block_level=#{item.blockLevel} " + "</if>" +
            "</script>")
    public List<Block> sqlManyObject(@Param("item") Block block);
}

package com.example.demo;

import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
@MapperScan("com.example.demo.mapper")
public class DemoApplication {

    public static void main(String[] args) {
        SpringApplication.run(DemoApplication.class, args);
    }

}