MyBatis |
您所在的位置：网站首页 › mybatis联表 › MyBatis |
MyBatis
|
MyBatis-Plus多表联查的方法(动态查询和静态查询)
目录建库建表依赖配置代码测试1.静态查询2.动态查询 1.不传条件2.传条件建库建表
DROP DATABASE IF EXISTS mp;
CREATE DATABASE mp DEFAULT CHARACTER SET utf8;
USE mp;
DROP TABLE IF EXISTS `t_user`;
DROP TABLE IF EXISTS `t_blog`;
SET NAMES utf8mb4;
CREATE TABLE `t_user`
(
`id` BIGINT(0) NOT NULL AUTO_INCREMENT,
`user_name` VARCHAR(64) NOT NULL COMMENT '用户名（不能重复）',
`nick_name` VARCHAR(64) NOT NULL COMMENT '昵称（可以重复）',
`email` VARCHAR(64) COMMENT '邮箱',
`create_time` DATETIME(0) NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`update_time` DATETIME(0) NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
`deleted_flag` BIGINT(0) NOT NULL DEFAULT 0 COMMENT '0：未删除 其他：已删除',
PRIMARY KEY (`id`) USING BTREE,
UNIQUE KEY `index_user_name_deleted_flag`(`user_name`, `deleted_flag`),
KEY `index_create_time`(`create_time`)
) ENGINE = InnoDB COMMENT = '用户';
CREATE TABLE `t_blog`
`id` BIGINT(0) NOT NULL AUTO_INCREMENT,
`user_id` BIGINT(0) NOT NULL,
`user_name` VARCHAR(64) NOT NULL,
`title` VARCHAR(256) CHARACTER SET utf8mb4 NOT NULL COMMENT '标题',
`description` VARCHAR(256) CHARACTER SET utf8mb4 NOT NULL COMMENT '摘要',
`content` LONGTEXT CHARACTER SET utf8mb4 NOT NULL COMMENT '内容',
KEY `index_user_id`(`user_id`),
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COMMENT = '博客';
INSERT INTO `t_user` VALUES (1, 'knife', '刀刃', '[email protected]', '2021-01-23 09:33:36', '2021-01-23 09:33:36', 0);
INSERT INTO `t_user` VALUES (2, 'sky', '天蓝', '[email protected]', '2021-01-24 18:12:21', '2021-01-24 18:12:21', 0);
INSERT INTO `t_blog` VALUES (1, 1, 'knife', 'Java中枚举的用法',
'本文介绍Java的枚举类的使用',
'2021-01-23 11:33:36', '2021-01-23 11:33:36', 0);
INSERT INTO `t_blog` VALUES (2, 1, 'knife', 'Java中泛型的用法',
'本文介绍Java的泛型的使用。',
'2021-01-28 23:37:37', '2021-01-28 23:37:37', 0);
INSERT INTO `t_blog` VALUES (3, 1, 'knife', 'Java的HashMap的原理',
'本文介绍Java的HashMap的原理。',
'2021-05-28 09:06:06', '2021-05-28 09:06:06', 0);
INSERT INTO `t_blog` VALUES (4, 1, 'knife', 'Java中BigDecimal的用法',
'本文介绍Java的BigDecimal的使用。',
'2021-06-24 20:36:54', '2021-06-24 20:36:54', 0);
INSERT INTO `t_blog` VALUES (5, 1, 'knife', 'Java中反射的用法',
'本文介绍Java的反射的使用。',
'2021-10-28 22:24:18', '2021-10-28 22:24:18', 0);
INSERT INTO `t_blog` VALUES (6, 2, 'sky', 'Vue-cli的使用',
'Vue-cli是Vue的一个脚手架工具',
'Vue-cli可以用来创建vue项目',
'2021-02-23 11:34:36', '2021-02-25 14:33:36', 0);
INSERT INTO `t_blog` VALUES (7, 2, 'sky', 'Vuex的用法',
'Vuex是vue用于共享变量的插件',
'一般使用vuex来共享变量',
'2021-03-28 23:37:37', '2021-03-28 23:37:37', 0);
依赖
pom.xml 4.0.0 com.example MyBatis-Plus_Multiple 0.0.1-SNAPSHOT jar MyBatis-Plus_Multiple Demo project for Spring Boot org.springframework.boot spring-boot-starter-parent 2.3.12.RELEASE UTF-8 UTF-8 1.8 org.springframework.boot spring-boot-starter-web mysql mysql-connector-java runtime spring-boot-starter-test test com.baomidou mybatis-plus-boot-starter 3.5.1 org.projectlombok lombok com.github.xiaoymin knife4j-spring-boot-starter 3.0.3 org.apache.maven.plugins maven-compiler-plugin 8 8 配置application.yml spring: datasource: driver-class-name: com.mysql.cj.jdbc.Driver url: jdbc:mysql://127.0.0.1:3306/mp?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=utf8&useSSL=false password: 222333 username: root #mybatis-plus配置控制台打印完整带参数SQL语句 mybatis-plus: configuration: log-impl: org.apache.ibatis.logging.stdout.StdOutImplMyBatis-Plus分页插件配置 package com.example.demo.config; import com.baomidou.mybatisplus.annotation.DbType; import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor; import org.springframework.context.annotation.Bean; import org.springframework.context.annotation.Configuration; import com.baomidou.mybatisplus.extension.plugins.inner.PaginationInnerInterceptor; @Configuration public class MyBatisPlusConfig { /** * 分页插件 */ @Bean public MybatisPlusInterceptor mybatisPlusInterceptor() { MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor(); interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.MYSQL)); return interceptor; } } 代码VO package com.example.demo.business.blog.vo; import lombok.Data; import java.time.LocalDateTime; @Data public class BlogVO { private Long id; private Long userId; private String userName; /** * 标题 */ private String title; * 摘要 private String description; * 内容 private String content; * 创建时间 private LocalDateTime createTime; * 修改时间 private LocalDateTime updateTime; * 昵称（这个是t_user的字段） private String nickName; }Mapper package com.example.demo.business.blog.mapper; import com.baomidou.mybatisplus.core.conditions.Wrapper; import com.baomidou.mybatisplus.core.mapper.BaseMapper; import com.baomidou.mybatisplus.core.metadata.IPage; import com.example.demo.business.blog.entity.Blog; import com.example.demo.business.blog.vo.BlogVO; import org.apache.ibatis.annotations.Param; import org.apache.ibatis.annotations.Select; import org.springframework.stereotype.Repository; import java.util.List; @Repository public interface BlogMapper extends BaseMapper { /** * 静态查询 */ @Select("SELECT t_user.user_name " + " FROM t_blog, t_user " + " WHERE t_blog.id = #{id} " + " AND t_blog.user_id = t_user.id") String findUserNameByBlogId(@Param("id") Long id); * 动态查询 @Select("SELECT * " + " ${ew.customSqlSegment} ") IPage findBlog(IPage page, @Param("ew") Wrapper wrapper); }Controller package com.example.demo.business.blog.controller; import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper; import com.baomidou.mybatisplus.core.metadata.IPage; import com.baomidou.mybatisplus.extension.plugins.pagination.Page; import com.example.demo.business.blog.mapper.BlogMapper; import com.example.demo.business.blog.vo.BlogVO; import io.swagger.annotations.Api; import io.swagger.annotations.ApiOperation; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.util.StringUtils; import org.springframework.web.bind.annotation.GetMapping; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.RestController; @Api(tags = "自定义SQL") @RestController @RequestMapping("/blog") public class BlogController { @Autowired private BlogMapper blogMapper; @ApiOperation("静态查询") @GetMapping("staticQuery") public String staticQuery() { return blogMapper.findUserNameByBlogId(1L); } @ApiOperation("动态查询") @GetMapping("dynamicQuery") public IPage dynamicQuery(Page page, String nickName, String title) { QueryWrapper queryWrapper = new QueryWrapper(); queryWrapper.like(StringUtils.hasText(nickName), "t_user.nick_name", nickName); queryWrapper.like(StringUtils.hasText(title), "t_blog.title", title); queryWrapper.eq("t_blog.deleted_flag", 0); queryWrapper.eq("t_user.deleted_flag", 0); queryWrapper.apply("t_blog.user_id = t_user.id"); return blogMapper.findBlog(page, queryWrapper); } 测试访问knife4j页面：http://localhost:8080/doc.html
结果：（可以查到所有数据）
后端输出 Creating a new SqlSessionSqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@60bbb9ec] was not registered for synchronization because synchronization is not activeJDBC Connection [HikariProxyConnection@1853643659 wrapping com.mysql.cj.jdbc.ConnectionImpl@6a43d29c] will not be managed by Spring==> Preparing: SELECT COUNT(*) AS total FROM t_blog, t_user WHERE (t_blog.deleted_flag = ? AND t_user.deleted_flag = ? AND t_blog.user_id = t_user.id)==> Parameters: 0(Integer), 0(Integer) |
【本文地址】
今日新闻 |
推荐新闻 |