mysql 动态select mysql 动态关联表如何设计

转载

陌陌香阁 2024-06-15 16:26:49

文章标签 mysql 动态select mysql when otherwise xml SQL List 文章分类 MySQL 数据库

一、前言

关于动态SQL，官方文档有很详细的总结。

一句话，拼接SQL很麻烦，甚至会因为缺少空格出错。利用动态SQL可以摆脱这种痛苦。

版本相关：

MySQL 8.0.19

MyBatis 3.5.5

二、搭建环境

2.1 创建表并插入数据

Create Table `blog`(
`id` varchar(50) not null comment '博客id',
`title` varchar(100) not null comment '博客标题',
`author` varchar(30) not null comment '博客作者',
`create_time` datetime not null comment '创建时间',
`views` int(30) not null comment '浏览量'
)Engine=Innodb default charset = utf8;
insert into `blog`(`id`,`title`,`author`,`create_time`,`views`)
values
('97560394a5414655a571df20792c86cc','Mybatis入门-01-第一个程序','Hanxi','2020-09-10 00:31',25),
('9e328886846c4107a45ade2ed7259600','Mybatis入门-02-增删改查及配置(属性、别名、映射器)','Duzhuan','2020-09-11 14:08',5),
('57d837764f714efbbff4fcd335f2ddb1','日志工厂 ','Duzhuan','2020-09-11 22:49',12),
('f35f55cb414f4d3aadd75b8021e1b34e','Mybatis入门-04-多对一 ','Duzhuan','2020-09-19 20:49',5),
('c6f82f2bc929417c83736c28b307aa19','Mybatis入门-05-一对多','Duzhuan','2020-09-20 08:50',2);

2.2 依赖

对于不需要使用日志的，可以JUnit下面的关于日志的依赖删除。

xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
4.0.0
com.duzhuan
MyBaitsLearn
pom
1.0-SNAPSHOT
11
11
mysql
mysql-connector-java
8.0.19
org.mybatis
mybatis
3.5.5
junit
junit
4.12
test
org.slf4j
slf4j-api
1.7.25
org.apache.logging.log4j
log4j-slf4j-impl
2.12.1
test
org.apache.logging.log4j
log4j-api
2.12.1
org.apache.logging.log4j
log4j-core
2.12.1
src/main/resource/
**/*.properties
**/*.xml
true
src/main/java/
**/*.properties
**/*.xml
true

2.3 mybatis配置文件

路径

mysql 动态select mysql 动态关联表如何设计_mysql 动态select

文件

mybatis-config.xml

请按需要设置里的日志，不需要日志可以删除及其子标签；

请按需要设置别名，即；

请按照自己的Maven路径设置好。

同时关于mapUnderscoreToCamelCase,具体可看官方文档:

设置名

描述

有效值

默认值

mapUnderscoreToCamelCase

是否开启驼峰命名自动映射，即从经典数据库列名 A_COLUMN 映射到经典 Java 属性名 aColumn。

true | false

False

/p>

PUBLIC "-//mybatis.org//DTD Config 3.0//EN"

"http://mybatis.org/dtd/mybatis-3-config.dtd">

db.properties

自行设置好JDBC配置，以下仅供参考：

driver = com.mysql.jdbc.Driver

url = jdbc:mysql://localhost:3306/mybatis?useSSL=true&useUnicode=true&characterEncoding=UTF8&serverTimezone=UTC

username = root

password = qq123456

log4j2.xml

日志相关的配置，这里由于是SLF4J日志门面+LOG4J实现，因此在mybatis-config.xml中设置了SLF4J，具体看Mybatis入门-03-日志工厂。

./logs

filePattern="logs/$${date:yyyy-MM}/web-%d{MM-dd-yyyy}-%i.log.gz">

三、创建实体类

路径

mysql 动态select mysql 动态关联表如何设计_SQL_02

代码

Blog.java
package com.duzhuan.pojo;
import java.util.Date;
/**
* @Autord: HuangDekai
* @Date: 2020/9/20 10:30
* @Version: 1.0
* @since: jdk11
*/
public class Blog {
private String id;
private String title;
private String author;
private Date createTime;
private int views;
public Blog() {
}
public Blog(String id, String title, String author, Date createTime, int views) {
this.id = id;
this.title = title;
this.author = author;
this.createTime = createTime;
this.views = views;
}
public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}
public String getTitle() {
return title;
}
public void setTitle(String title) {
this.title = title;
}
public String getAuthor() {
return author;
}
public void setAuthor(String author) {
this.author = author;
}
public Date getCreateTime() {
return createTime;
}
public void setCreateTime(Date createTime) {
this.createTime = createTime;
}
public int getViews() {
return views;
}
public void setViews(int views) {
this.views = views;
}
@Override
public String toString() {
return "Blog{" +
"id='" + id + '\'' +
", title='" + title + '\'' +
", author='" + author + '\'' +
", createTime=" + createTime +
", views=" + views +
'}';
}
}

四、创建常用工具类

路径

mysql 动态select mysql 动态关联表如何设计_mysql when otherwise_03

代码

MyBatisUtils
package com.huangdekai.utils;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import java.io.IOException;
import java.io.InputStream;
/**
* @Autord: HuangDekai
* @Date: 2020/9/20 10:36
* @Version: 1.0
* @since: jdk11
*/
public class MyBatisUtils {
private static SqlSessionFactory sqlSessionFactory;
static {
try {
String config = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(config);
sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
} catch (IOException e) {
e.printStackTrace();
}
}
public static SqlSession getSqlSession(){
return sqlSessionFactory.openSession();
}
}
IdUtils
package com.duzhuan.utils;
import java.util.UUID;
/**
* @Autord: HuangDekai
* @Date: 2020/9/20 12:00
* @Version: 1.0
* @since: jdk11
*/
public class IdUtils {
public static String getId(){
return UUID.randomUUID().toString().replace("-","");
}
}

五、Mapper

路径

mysql 动态select mysql 动态关联表如何设计_SQL_04

六、IF标签

BlogMapper
package com.duzhuan.dao;
import com.duzhuan.pojo.Blog;
import java.util.List;
/**
* @Autord: HuangDekai
* @Date: 2020/9/20 10:58
* @Version: 1.0
* @since: jdk11
*/
public interface BlogMapper {
List getBlogList(Blog blog);
}
BlogMapper.xml
/p>
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
select * from mybatis.blog where 1=1
and `title` like concat('%',#{title},'%')
and `author` = #{author}
and `views` >= #{views}

只是增加了使用if语句

test里放的是判断语句，多嘴一句，里面的如author != null中的author是Blog实体类的属性名，而不是数据库的字段名。

实质上， select * from mybatiswhere 1=1中where 1=1并不是一种规范的写法，甚至说不应该出现这种写法，这里仅仅用于说明的使用，后面会使用where标签解决要使用where 1=1这种问题。

测试类

mysql 动态select mysql 动态关联表如何设计_mysql when otherwise_05

package com.duzhuan.dao;
import com.duzhuan.pojo.Blog;
import com.duzhuan.utils.MyBatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import java.util.List;
/**
* @Autord: HuangDekai
* @Date: 2020/9/20 15:11
* @Version: 1.0
* @since: jdk11
*/
public class BlogMapperTest {
@Test
public void getBlogListTest(){
SqlSession sqlSession = MyBatisUtils.getSqlSession();
BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
Blog blog1 = new Blog(null,"Mybatis",null,null,5);
List blogs = mapper.getBlogList(blog1);
for (Blog blog : blogs) {
System.out.println(blog);
}
sqlSession.close();
}
}

结果：

mysql 动态select mysql 动态关联表如何设计_xml_06

七、where标签

如果注意的话，可以看到上面的SQL语句有一个十分多余的东西，where 1=1。

但是如果想要完成上面的功能，不用where 1=1这样类似的句子还不行，MyBatis的解决方法就是使用where标签，实质上和在SQL里使用where仅仅有一点点细微差别。

这方面官方文档-动态SQL中就写得十分翔实。

这里就对BlogMapper.xml做一点修改，尝试使用标签去替代原来的句子：

/p>
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
select * from mybatis.blog
and `title` like concat('%',#{title},'%')
and `author` = #{author}
and `views` >= #{views}

结果：

mysql 动态select mysql 动态关联表如何设计_mysql when otherwise_07

没错，真的只有一点点修改而已。

这是由于的特性，下面是官方文档的说法：

where 元素只会在子元素返回任何内容的情况下才插入 “WHERE” 子句。

而且，若子句的开头为 “AND” 或 “OR”，where 元素也会将它们去除。

八、choose-when-otherwise标签

这方面官方文档-动态SQL中写得十分翔实。

有时候，我们不想使用所有的条件，而只是想从多个条件中选择一个使用。针对这种情况，MyBatis 提供了 choose 元素，它有点像 Java 中的 switch 语句。

这里就对BlogMapper和BlogMapper.xml做一点添加，尝试使得：

有title时候返回搜索title的数据

有author无title时候返回搜索author的数据

仅有views时候返回大于等于views的数据，views默认输入0

BlogMapper中添加方法：

List getBlogListByChoose(Blog blog);

BlogMapper.xml中在根标签中添加：

select * from mybatis.blog

`title` like concat('%',#{title},'%')

`author` like concat('%',#{author},'%')

`views` >= #{views}

测试样例：

@Test
public void getBlogListByChooseTest(){
SqlSession sqlSession = MyBatisUtils.getSqlSession();
BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
Blog blog1 = new Blog(null, "日志", "hanxi", null, 10);
Blog blog2 = new Blog(null, null, "hanxi", null, 10);
Blog blog3 = new Blog(null, null, null, null, 10);
List blogListByChoose1 = mapper.getBlogListByChoose(blog1);
List blogListByChoose2 = mapper.getBlogListByChoose(blog2);
List blogListByChoose3 = mapper.getBlogListByChoose(blog3);
System.out.println("blog1------->"+blogListByChoose1);
System.out.println("blog2------->"+blogListByChoose2);
System.out.println("blog3------->"+blogListByChoose3);
sqlSession.close();
}

输出结果：

mysql 动态select mysql 动态关联表如何设计_SQL_08

九、set标签

上面的都是查询语句，那么动态的插入语句呢？

update Author
username=#{username},
password=#{password},
email=#{email},
bio=#{bio}
where id=#{id}

这是官方文档的一个例子。

可以看到，如果不使用标签，一旦bio=#{bio}不成立而前面的SQL有成立的，那么就会多一个逗号，导致出错。

对于set，官方文档中有解释：

set 元素会动态地在行首插入 SET 关键字，并会删掉额外的逗号(这些逗号是在使用条件语句给列赋值时引入的)。

set使用方法与where类似，可以自行增加样例。

十、SQL标签

假如有很多个SQL，而且很多重复的，那么为了实现SQL的复用，就可以用到标签。

BlogMapper.xml:

将getBlogList的标签内的东西移出，放入标签内：

/p>
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
and `title` like concat('%',#{title},'%')
and `author` = #{author}
and `views` >= #{views}
select * from mybatis.blog
select * from mybatis.blog
`title` like concat('%',#{title},'%')
`author` like concat('%',#{author},'%')
`views` >= #{views}

再用原来对这个方法的测试案例测试(BlogMapperTest的getBlogListTest方法)：

mysql 动态select mysql 动态关联表如何设计_List_09