Springboot + mybatisPLus 多数据源
参考: https://www.bilibili.com/video/BV17E411N7KN?p=14
mybatisPLus 是mybatis的升级版，mybatis的功能都继承了都可以同样的使用
目录
MyBatisPlusConfig
package com.config;
import com.baomidou.mybatisplus.core.injector.ISqlInjector;
import com.baomidou.mybatisplus.extension.injector.LogicSqlInjector;
import com.baomidou.mybatisplus.extension.plugins.PaginationInterceptor;
import com.baomidou.mybatisplus.extension.plugins.PerformanceInterceptor;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Profile;
@Configuration
@MapperScan("com.mapper")
public class MyBatisPlusConfig {
//分页插件
@Bean
public PaginationInterceptor paginationInterceptor() {
return new PaginationInterceptor();
}
//逻辑删除插件
@Bean
public ISqlInjector sqlInjector() {
return new LogicSqlInjector();
}
// sql执行效率插件
@Bean
@Profile({"dev","test"}) //设置dev、test环境开启
public PerformanceInterceptor PerformanceInterceptor(){
PerformanceInterceptor PerformanceInterceptor = new PerformanceInterceptor();
PerformanceInterceptor.setMaxTime(1111);//设置sql执行的最大时间,如果超过了则不执行，抛异常
PerformanceInterceptor.setFormat(true); //开启sql格式化
return PerformanceInterceptor;
}
}
UserMapper
package com.mapper;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.pojo.User;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Repository;
import java.util.List;
@Repository
public interface UserMapper extends BaseMapper<User> {
@Select("select * from user,user2 where user.id=user2.id")
List<User> getUserById3();
@Select("DELETE FROM User WHERE id = 3")
List<User> getUserById2();
}
User
帅爷说如果使用联表查这里里面的字段就要自定义去加
package com.pojo;
import com.baomidou.mybatisplus.annotation.TableLogic;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
@Data
@AllArgsConstructor
@NoArgsConstructor
public class User {
private int id;
private String message;
@TableLogic() //逻辑删除
private Integer deleted;
}
MyBatisPlusApplication
package com;
import org.apache.ibatis.annotations.Mapper;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
@MapperScan("com.mapper")
@SpringBootApplication
public class MyBatisPlusApplication {
public static void main(String[] args) {
SpringApplication.run(MyBatisPlusApplication.class, args);
}
}
application.yml
### 应用名称
##spring.application.name=Mybatis_7_20
##下面这些内容是为了让MyBatis映射
##指定Mybatis的Mapper文件
#mybatis:
# mapper-locations: classpath:/mapper/*xml
##指定Mybatis的实体目录
# type-aliases-package: com.entity
spring:
#设置开发环境
profiles:
active: dev
## 数据库驱动：
# 第一个数据库
datasource:
driver-class-name: com.mysql.cj.jdbc.Driver
# 数据库连接地址
url: jdbc:mysql://localhost:3306/datademo?serverTimezone=UTC
# 数据库用户名&密码：
username: root
password: 669988
#配置日志
mybatis-plus:
configuration:
log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
#逻辑删除
global-config:
db-config:
logic-delete-value: 1 # 逻辑已删除值(默认为 1)
logic-not-delete-value: 0 # 逻辑未删除值(默认为 0)
# 应用服务 WEB 访问端口
server:
port: 8180
MyBatisPlusApplicationTests-------(单元测试)
package com;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.mapper.UserMapper;
import com.pojo.User;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import java.util.List;
@SpringBootTest
class MyBatisPlusApplicationTests {
@Autowired
private UserMapper userMapper;
@Test
// 查询所有
void contextLoads() {
List user = userMapper.getUserById3();
user.forEach(System.out::println);
}
@Test
// 查询所有
void contextLoads3() {
List<User> user = userMapper.selectList(null);
user.forEach(System.out::println);
}
//
// @Test
//// 带id条件查询
// void testselectid() {
// User user = userMapper.selectById(1);
// System.out.println(user);
// }
//
// @Test
//// in条件查询
// void testselectidList() {
// List<User> user = userMapper.selectBatchIds(Arrays.asList(1,2,3,4,5,6));
// user.forEach(System.out::println);
// }
//
// @Test
//// 按条件查询
// public void testselectidBatchids() {
// HashMap<String,Object> map=new HashMap<>();
// map.put("id",2);
// map.put("message","你好");
// List<User> user =userMapper.selectByMap(map);
// user.forEach(System.out::println);
// }
//
// // 插入
// @Test
// public void testInsert() {
// User user=new User();
// user.setMessage("kaibin666");
// int result=userMapper.insert(user);
// System.out.println(result); //受影响的行数
// System.out.println(user);
// }
// // 更新
// @Test
// public void testUpdata() {
// User user=new User();
//// user.setId(6);
// user.setMessage("hahah");
// int result=userMapper.updateById(user);
// System.out.println(result); //受影响的行数
// System.out.println(user);
// }
//
// 测试分页查询
@Test
public void testPage() {
// 参数一:第几页
// 参数二:每页条数
Page<User> page=new Page<>(2,5);
// 查询所有
userMapper.selectPage(page,null);
// 获取第2页的5条信息展示
page.getRecords().forEach(System.out::println);
// 获取查询语句的总条数
System.out.println(page.getTotal());
}
//
// //测试删除
// @Test
// public void testDeleteById(){
// int result=userMapper.deleteById(4);
// System.out.println(result); //受影响的行数
// }
//
// //测试id批量删除
// @Test
// public void testDeleteBatchId(){
// int result=userMapper.deleteBatchIds(Arrays.asList(1,2,3));
// System.out.println(result); //受影响的行数
// }
//
// //测试map条件删除
// @Test
// public void testDeleteMap(){
// HashMap<String,Object> map=new HashMap<>();
// map.put("message","你好");
// List<User> result=userMapper.selectByMap(map);
// System.out.println(result); //受影响的行数
// }
//
}
WrapperTest
package com;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.mapper.UserMapper;
import com.pojo.User;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import java.util.List;
@SpringBootTest
public class WrapperTest {
@Autowired
private UserMapper userMapper;
// @Test
//// 查询 message 和 id 不为空的 信息
// void contextLoads() {
// QueryWrapper<User> wrapper =new QueryWrapper<>();
// wrapper
// .isNotNull("message")
// .isNotNull("id");
// userMapper.selectList(wrapper).forEach(System.out::println);
// }
//
@Test
// 查询 message =张三的
void contextLoads2() {
QueryWrapper<User> wrapper =new QueryWrapper<>();
wrapper
.eq("message","kaibin");
userMapper.selectList(wrapper).forEach(System.out::println);
userMapper.selectCount(wrapper); //查询数据统计条数
}
//
// 测试分页查询
// 测试分页查询
@Test
public void testPage() {
// 参数一:第几页
// 参数二:每页条数
Page<User> page=new Page<>(2,5);
// 查询所有
// QueryWrapper<User> weapper=new QueryWrapper<>();
// weapper.select("select id from user");
userMapper.selectPage(page,null);
// 获取第2页的5条信息展示
page.getRecords().forEach(System.out::println);
// 获取查询语句的总条数
System.out.println(page.getTotal());
}
//
@Test
public void test02() {
QueryWrapper<User> weapper=new QueryWrapper<>();
// weapper.inSql("id","select id from user");
weapper.select("id");
List<Object> objects=userMapper.selectObjs(weapper);
objects.forEach(System.out::println);
}
}
pom.xml
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.example</groupId>
<artifactId>MyBatis_Plus</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>MyBatis_Plus</name>
<description>Demo project for Spring Boot</description>
<properties>
<java.version>1.8</java.version>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
<project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
<spring-boot.version>2.3.7.RELEASE</spring-boot.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.0.5</version>
</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>
<exclusions>
<exclusion>
<groupId>org.junit.vintage</groupId>
<artifactId>junit-vintage-engine</artifactId>
</exclusion>
</exclusions>
</dependency>
</dependencies>
<dependencyManagement>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-dependencies</artifactId>
<version>${spring-boot.version}</version>
<type>pom</type>
<scope>import</scope>
</dependency>
</dependencies>
</dependencyManagement>
<build>
<plugins>
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-compiler-plugin</artifactId>
<version>3.8.1</version>
<configuration>
<source>1.8</source>
<target>1.8</target>
<encoding>UTF-8</encoding>
</configuration>
</plugin>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
<version>2.3.7.RELEASE</version>
<configuration>
<mainClass>com.MyBatisPlusApplication</mainClass>
</configuration>
<executions>
<execution>
<id>repackage</id>
<goals>
<goal>repackage</goal>
</goals>
</execution>
</executions>
</plugin>
</plugins>
</build>
</project>
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
如何连接2个数据库呢，修改上面的文件
参考: https://mp.baomidou.com/guide/dynamic-datasource.html#%E6%96%87%E6%A1%A3-documentation
pom.xml
(注意 在添加
dynamic-datasource-spring-boot-starter (@DS注解要这个) 的jar包时 他其实是导入了多个jar文件，要保证不要有和自己pom.xml里面有相同的jar引入，我弄的时候就是因为有mybatis-plus-boot-starter 重复了 且版本不一样 导致一直报错 这个包要用 3.0.5 左右才支持sql日志
)
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.example</groupId>
<artifactId>MyBatis_Plus</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>MyBatis_Plus</name>
<description>Demo project for Spring Boot</description>
<properties>
<java.version>1.8</java.version>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
<project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
<spring-boot.version>2.3.7.RELEASE</spring-boot.version>
</properties>
<dependencies>
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>dynamic-datasource-spring-boot-starter</artifactId>
<version>3.1.0</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
<!-- <dependency>-->
<!-- <groupId>com.baomidou</groupId>-->
<!-- <artifactId>mybatis-plus-boot-starter</artifactId>-->
<!-- <version>3.0.5</version>-->
<!-- </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>
<exclusions>
<exclusion>
<groupId>org.junit.vintage</groupId>
<artifactId>junit-vintage-engine</artifactId>
</exclusion>
</exclusions>
</dependency>
</dependencies>
<dependencyManagement>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-dependencies</artifactId>
<version>${spring-boot.version}</version>
<type>pom</type>
<scope>import</scope>
</dependency>
</dependencies>
</dependencyManagement>
<build>
<plugins>
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-compiler-plugin</artifactId>
<version>3.8.1</version>
<configuration>
<source>1.8</source>
<target>1.8</target>
<encoding>UTF-8</encoding>
</configuration>
</plugin>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
<version>2.3.7.RELEASE</version>
<configuration>
<mainClass>com.MyBatisPlusApplication</mainClass>
</configuration>
<executions>
<execution>
<id>repackage</id>
<goals>
<goal>repackage</goal>
</goals>
</execution>
</executions>
</plugin>
</plugins>
</build>
</project>s
这份貌似可以
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.example</groupId>
<artifactId>ZuJieDemo</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>ZuJieDemo</name>
<description>Demo project for Spring Boot</description>
<properties>
<java.version>1.8</java.version>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
<project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
<spring-boot.version>2.3.7.RELEASE</spring-boot.version>
</properties>
<dependencies>
<!-- swagger-->
<dependency>
<groupId>io.springfox</groupId>
<artifactId>springfox-swagger2</artifactId>
<version>2.9.2</version>
</dependency>
<dependency>
<groupId>io.springfox</groupId>
<artifactId>springfox-swagger-ui</artifactId>
<version>2.9.2</version>
</dependency>
<!-- postman 复制下来的第三方请求福围问卷的 请求-->
<dependency>
<groupId>com.mashape.unirest</groupId>
<artifactId>unirest-java</artifactId>
<version>1.4.9</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.4</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>1.2.59</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>dynamic-datasource-spring-boot-starter</artifactId>
<version>3.1.0</version>
</dependency>
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.0.5</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</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>
<exclusions>
<exclusion>
<groupId>org.junit.vintage</groupId>
<artifactId>junit-vintage-engine</artifactId>
</exclusion>
</exclusions>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<scope>test</scope>
</dependency>
<dependency>
<groupId>net.java.dev.jna</groupId>
<artifactId>jna-platform</artifactId>
<version>5.5.0</version>
</dependency>
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.5.6</version>
</dependency>
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis-spring</artifactId>
<version>2.0.6</version>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-test</artifactId>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-test</artifactId>
</dependency>
</dependencies>
<dependencyManagement>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-dependencies</artifactId>
<version>${spring-boot.version}</version>
<type>pom</type>
<scope>import</scope>
</dependency>
</dependencies>
</dependencyManagement>
<build>
<plugins>
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-compiler-plugin</artifactId>
<version>3.8.1</version>
<configuration>
<source>1.8</source>
<target>1.8</target>
<encoding>UTF-8</encoding>
</configuration>
</plugin>
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-deploy-plugin</artifactId>
<version>2.8.2</version>
<configuration>
<skip>true</skip>
</configuration>
</plugin>
</plugins>
</build>
</project>
application.yml
### 应用名称
##spring.application.name=Mybatis_7_20
##下面这些内容是为了让MyBatis映射
##指定Mybatis的Mapper文件
#mybatis:
# mapper-locations: classpath:/mapper/*xml
##指定Mybatis的实体目录
# type-aliases-package: com.entity
spring:
#设置开发环境
profiles:
active: dev
datasource:
dynamic:
primary: master #设置默认的数据源或者数据源组,默认值即为master
strict: false #严格匹配数据源,默认false. true未匹配到指定数据源时抛异常,false使用默认数据源
datasource:
slave_1:
# 数据库连接地址
url: jdbc:mysql://localhost:3306/datademo?serverTimezone=UTC
# 数据库用户名&密码：
username: root
password: 669988
driver-class-name: com.mysql.jdbc.Driver # 3.2.0开始支持SPI可省略此配置
master:
url: jdbc:mysql://rm-bp999999.mysql.rds.aliyuncs.com/edu_xxxxx
# 数据库用户名&密码：
username: testxxxxx
password: xxxxxx
driver-class-name: com.mysql.jdbc.Driver
#配置日志
mybatis-plus:
configuration:
log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
#逻辑删除
global-config:
db-config:
logic-delete-value: 1 # 逻辑已删除值(默认为 1)
logic-not-delete-value: 0 # 逻辑未删除值(默认为 0)
# 应用服务 WEB 访问端口
server:
port: 8180
UserMapper
package com.mapper; import com.baomidou.dynamic.datasource.annotation.DS; import com.baomidou.mybatisplus.core.mapper.BaseMapper; import com.pojo.User; import org.apache.ibatis.annotations.Select; import org.springframework.stereotype.Repository; import java.util.List; @Repository public interface UserMapper extends BaseMapper<User> { @DS("slave") #控制要使用那个数据库 @Select("select id from user") List<User> getUserById3(); @Select("DELETE FROM User WHERE id = 3") List<User> getUserById2(); }
这两种注解的区别在于：
1、使用@mapper后，不需要在spring配置中设置扫描地址，通过mapper.xml里面的namespace属性对应相关的mapper类，spring将动态的生成Bean后注入到ServiceImpl中。
2、@repository则需要在Spring中配置扫描包地址，然后生成dao层的bean，之后被注入到ServiceImpl中
后计:
这里@DS选择数据源，可以在server上标，在server层里面选择执行的数据源
后计:
数据库配置文件可以不指定库名 jdbc:mysql://rm-bp999999.mysql.rds.aliyuncs.com 在sql语句里面加上表名即可。 也可以在 实体类里面制定库名.表名 @TableName(value = "center.customer")
后计:
注意配置文件 mybatis 和 mybatis-plus 的区分
后记:
执行了sql后 其实会返回 一个int类型的
浙公网安备 33010602011771号