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: 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
-
数据库测试表
-- ---------------------------- -- 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 '修改时间', `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)为“zs”和密码(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)
@ResponseBody
public 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" } ]
- 如下图所示：
古今成大事者，不唯有超世之才，必有坚韧不拔之志！
