Mybatis中@select注解聯(lián)合查詢

前言

在項(xiàng)目中經(jīng)常會(huì)使用到一些簡(jiǎn)單的聯(lián)合查詢獲取對(duì)應(yīng)的數(shù)據(jù)信息，我們常規(guī)都是會(huì)根據(jù)對(duì)應(yīng)的mapper接口寫對(duì)應(yīng)的mapper.xml的來通過對(duì)應(yīng)的業(yè)務(wù)方法來調(diào)用獲取，針對(duì)這一點(diǎn)本人感覺有點(diǎn)繁瑣，就對(duì)@select注解聯(lián)合查詢進(jìn)行探索和嘗試，并將自己總結(jié)的分享給大家，有不到之處，敬請(qǐng)大家批評(píng)指正！！！

pom.xml所用到依賴如下

　　　　<dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.2.0</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.38</version>
        </dependency>

application.yml的配置如下：

server:
  port: 8888

spring:
  datasource:
    driver-class-name: com.mysql.jdbc.Driver
    url: jdbc:mysql://localhost:3306/project?useUnicode=true&characterEncoding=UTF8&zeroDateTimeBehavior=convertToNull&useSSL=false
    username: root
    password: 123456
mybatis:
  type-aliases-package: com.songwp.snowflake.entity
  mapper-locations: classpath:mybatis/mapper/*.xml
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl

數(shù)據(jù)庫(kù)測(cè)試表

-- ----------------------------
-- Table structure for sys_dept
-- 部門表
-- ----------------------------
DROP TABLE IF EXISTS `sys_dept`;
CREATE TABLE `sys_dept`  (
  `id` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '部門ID',
  `user_id` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '用戶ID',
  `dept_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '部門名稱',
  `parent_id` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '父級(jí)部門ID',
  `parent_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '父級(jí)部門名稱',
  `status` int(5) DEFAULT 0 COMMENT '部門狀態(tài)：0-正常  1-禁用',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Compact;

-- ----------------------------
-- Records of sys_dept
-- ----------------------------
INSERT INTO `sys_dept` VALUES ('5a8f893eedef4eafbc66feded3541c0f', '4117460f-20f7-47e7-bf8a-507a32880c06', '集團(tuán)本部', 'GS-001', '集團(tuán)本部', 0);
INSERT INTO `sys_dept` VALUES ('6271dd03e426400b9fd001bae9074efc', '4117460f-20f7-47e7-bf8a-507a32880c06', '財(cái)務(wù)部門', 'GS-003', '集團(tuán)本部', 0);
INSERT INTO `sys_dept` VALUES ('f33503159a084e73b4e1313932cc9629', '4117460f-20f7-47e7-bf8a-507a32880c06', '研發(fā)部門', 'GS-002', '集團(tuán)本部', 0);

-- ----------------------------
-- Table structure for sys_user
-- 用戶表
-- ----------------------------
DROP TABLE IF EXISTS `sys_user`;
CREATE TABLE `sys_user`  (
  `id` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '主鍵ID',
  `username` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '用戶名',
  `nickname` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '真實(shí)姓名',
  `password` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '密碼',
  `gender` int(5) DEFAULT 1 COMMENT '性別：0-女  1-男',
  `age` int(5) DEFAULT NULL COMMENT '年齡',
  `phone` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '聯(lián)系電話',
  `address` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '住址',
  `status` int(5) DEFAULT 0 COMMENT '用戶狀態(tài)：0-正常  1-凍結(jié)  2- 已注銷',
  `birthday` date DEFAULT NULL COMMENT '生日',
  `create_time` datetime DEFAULT NULL COMMENT '添加時(shí)間',
  `create_user` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '創(chuàng)建人',
  `update_time` datetime DEFAULT NULL COMMENT '修改時(shí)間',
  `update_user` varchar(0) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '修改人',
  `remark` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '備注',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Compact;

-- ----------------------------
-- Records of sys_user
-- ----------------------------
INSERT INTO `sys_user` VALUES ('4117460f-20f7-47e7-bf8a-507a32880c06', 'zs', '張三', '123456', 0, 25, '13888888888', '陜西西安', 0, '1996-05-07', '2022-03-28 10:17:54', '張三', NULL, NULL, NULL);
INSERT INTO `sys_user` VALUES ('fb962a7f-3a61-4312-820c-9e67eefaa74a', 'zll', '趙老六', '123456', 1, 28, '13666666666', '陜西西安', 0, '1992-05-07', '2022-03-28 10:14:45', '趙老六', NULL, NULL, NULL);

@select注解中SQL的寫法

假設(shè)我想用戶名(username)為“zs”和密碼(password)為“123456”的條件下用戶的信息和對(duì)應(yīng)部門的信息為例：

SELECT
    u.username,
    u.PASSWORD,
    u.nickname,
    u.phone,
    u.gender,
    u.address,
    d.id AS dept_id,
    d.dept_name,
    d.STATUS AS dept_status 
FROM
    sys_user u
    INNER JOIN sys_dept d ON u.id = d.user_id 
WHERE
    u.username = 'zs' 
    AND u.PASSWORD = '123456'

Navicat中結(jié)果執(zhí)行如下：

mapper接口

@Select("<script> SELECT " +
            "u.username," +
            "u.password," +
            "u.nickname," +
            "u.phone," +
            "u.gender," +
            "u.address," +
            "d.id as dept_id," +
            "d.dept_name," +
            "d.status as dept_status " +
            "from" +
            " sys_user u" +
            " inner join sys_dept d on u.id = d.user_id " +
            " where u.username = #{username} <when test='password !=null'> " +
            " and u.password = #{password} </when> </script>")
    List<Map> getByParmsMap(String username, String password);

1、@Select注解基本用法
@Select注解的目的是為了取代xml中的select標(biāo)簽，只作用于方法上面。下面看一下@Select注解的源碼介紹：

@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.METHOD)
public @interface Select
{
    String[] value();
}

從上述可以看到兩點(diǎn)信息：

（1）@Select注解只能修飾方法
（2）@Select注解的值是字符數(shù)組。

所以，@Select注解的用法是這樣的：

@Select({ "select * from xxx", "select * from yyy" })
Person selectPersonById(Integer id);

雖然@Select注解的值是字符數(shù)組，但是真正生效的應(yīng)該是最后那條SQL語(yǔ)句。這一點(diǎn)請(qǐng)大家要留意一下。

2、@Select注解動(dòng)態(tài)SQL拼寫
普通的字符串值，只能實(shí)現(xiàn)變量的替換功能，如下所示，

@Select("select * from t_person where id = #{id}")
Person selectPersonById(Integer id);

如果要想實(shí)現(xiàn)復(fù)雜的邏輯判斷，則需要使用標(biāo)簽，如下所示：

@Select("<script> select * from t_person where id = #{id} 
<when test='address !=null'> and address = #{address} 
</when> </script>")
Person selectPersonById(Integer id);

其實(shí)，標(biāo)簽并非是@Select注解專用的，其他的注解，例如@Insert，@Update等等，都可以使用的。

業(yè)務(wù)層service接口

List<Map> getByParmsMap(String username, String password);

業(yè)務(wù)實(shí)現(xiàn)類的方法

public List<Map> getByParmsMap(String username, String password) {
        return userMapper.getByParmsMap(username,password);
 }

控制器controller方法

@RequestMapping(value = {"/getByParmsMap"}, method = RequestMethod.GET)
@ResponseBody
public List<Map> getByParmsMap(@RequestParam("username")String username,@RequestParam("password")String password){
    return userService.getByParmsMap(username,password);
}

Postman接口調(diào)用如下

[
    {
        "password": "123456",
        "address": "陜西西安",
        "gender": 0,
        "phone": "13888888888",
        "nickname": "張三",
        "dept_name": "集團(tuán)本部",
        "dept_status": 0,
        "dept_id": "5a8f893eedef4eafbc66feded3541c0f",
        "username": "zs"
    },
    {
        "password": "123456",
        "address": "陜西西安",
        "gender": 0,
        "phone": "13888888888",
        "nickname": "張三",
        "dept_name": "財(cái)務(wù)部門",
        "dept_status": 0,
        "dept_id": "6271dd03e426400b9fd001bae9074efc",
        "username": "zs"
    },
    {
        "password": "123456",
        "address": "陜西西安",
        "gender": 0,
        "phone": "13888888888",
        "nickname": "張三",
        "dept_name": "研發(fā)部門",
        "dept_status": 0,
        "dept_id": "f33503159a084e73b4e1313932cc9629",
        "username": "zs"
    }
]

如下圖所示：

posted @ 2022-04-01 11:46 [奮斗] 閱讀(1179) 評(píng)論(0) 收藏舉報(bào)

刷新頁(yè)面返回頂部

奮斗

青春由磨礪而出彩，人生因奮斗而升華

Mybatis中@select注解聯(lián)合查詢

前言

pom.xml所用到依賴如下

application.yml的配置如下：

數(shù)據(jù)庫(kù)測(cè)試表

@select注解中SQL的寫法

mapper接口

業(yè)務(wù)層service接口

業(yè)務(wù)實(shí)現(xiàn)類的方法

控制器controller方法

Postman接口調(diào)用如下

公告

奮 斗

青春由磨礪而出彩，人生因奮斗而升華

Mybatis中@select注解聯(lián)合查詢

前言

pom.xml所用到依賴如下

application.yml的配置如下：

數(shù)據(jù)庫(kù)測(cè)試表

@select注解中SQL的寫法

mapper接口

業(yè)務(wù)層service接口

業(yè)務(wù)實(shí)現(xiàn)類的方法

控制器controller方法

Postman接口調(diào)用如下

公告

奮斗