Mybatis中@select注解联合查询

前言

在项目中经常会使用到一些简单的联合查询获取对应的数据信息，我们常规都是会根据对应的mapper接口写对应的mapper.xml的来通过对应的业务方法来调用获取，针对这一点本人感觉有点繁琐，就对@select注解联合查询进行探索和尝试，并将自己总结的分享给大家，有不到之处，敬请大家批评指正！！！

一.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: 8888spring:  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: 123456mybatis:  type-aliases-package: com.songwp.snowflake.entity  mapper-locations: classpath:mybatis/mapper/*.xml  configuration:    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl

三.数据库测试表

-- ------------------------------ 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 '父级部门ID',  `parent_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '父级部门名称',  `status` int(5) DEFAULT 0 COMMENT '部门状态：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', '集团本部', 'GS-001', '集团本部', 0);INSERT INTO `sys_dept` VALUES ('6271dd03e426400b9fd001bae9074efc', '4117460f-20f7-47e7-bf8a-507a32880c06', '财务部门', 'GS-003', '集团本部', 0);INSERT INTO `sys_dept` VALUES ('f33503159a084e73b4e1313932cc9629', '4117460f-20f7-47e7-bf8a-507a32880c06', '研发部门', 'GS-002', '集团本部', 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 '真实姓名',  `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 '联系电话',  `address` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '住址',  `status` int(5) DEFAULT 0 COMMENT '用户状态：0-正常  1-冻结  2- 已注销',  `birthday` date DEFAULT NULL COMMENT '生日',  `create_time` datetime DEFAULT NULL COMMENT '添加时间',  `create_user` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '创建人',  `update_time` datetime DEFAULT NULL COMMENT ' 上海干部培训学校 www.utibetganxun.com 修改时间',  `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

假设我想用户名(username)为“z's”和密码(password)为“123456”的条件下用户的信息和对应部门的信息为例：

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中结果执行如下：

五.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标签，只作用于方法上面。下面看一下@Select注解的源码介绍：@Retention(RetentionPolicy.RUNTIME)@Target(ElementType.METHOD)public @interface Select{    String[] value();}从上述可以看到两点信息：（1）@Select注解只能修饰方法（2）@Select注解的值是字符数组。所以，@Select注解的用法是这样的：@Select({ "select * from xxx", "select * from yyy" })Person selectPersonById(Integer id);虽然@Select注解的值是字符数组，但是真正生效的应该是最后那条SQL语句。这一点请大家要留意一下。2、@Select注解动态SQL拼写普通的字符串值，只能实现变量的替换功能，如下所示，@Select("select * from t_person where id = #{id}")Person selectPersonById(Integer id);
如果要想实现复杂的逻辑判断，则需要使用标签，如下所示：@Select("<script> select * from t_person where id = #{id} <when test='address !=null'> and address = #{address} </when> </script>")Person selectPersonById(Integer id);其实，标签并非是@Select注解专用的，其他的注解，例如@Insert，@Update等等，都可以使用的。

六.业务层service接口

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

七.业务实现类的方法

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

八.控制器controller方法

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

九.Postman接口调用如下

[    {        "password": "123456",        "address": "陕西西安",        "gender": 0,        "phone": "13888888888",        "nickname": "张三",        "dept_name": "集团本部",        "dept_status": 0,        "dept_id": "5a8f893eedef4eafbc66feded3541c0f",        "username": "zs"    },    {        "password": "123456",        "address": "陕西西安",        "gender": 0,        "phone": "13888888888",        "nickname": "张三",        "dept_name": "财务部门",        "dept_status": 0,        "dept_id": "6271dd03e426400b9fd001bae9074efc",        "username": "zs"    },    {        "password": "123456",        "address": "陕西西安",        "gender": 0,        "phone": "13888888888",        "nickname": "张三",        "dept_name": "研发部门",        "dept_status": 0,        "dept_id": "f33503159a084e73b4e1313932cc9629",        "username": "zs"    }]

如下图所示：