充实你的上网体验:书签管理插件的必备利器
966
2023-03-09
Spring Boot + Mybatis
前段时间写了一篇基于mybatis实现的多数据源博客。感觉不是很好,这次打算加入git,来搭建一个基于Mybatis-Plus的多数据源项目
Mybatis-Plus就是香
前言:该项目分为master数据源与local数据源。假定master数据源为线上数据库,local为本地数据库。后续我们将通过xxl-job的方式,将线上(master)中的数据同步到本地(local)中
项目git地址 抽时间把项目提交到git仓库,方便大家直接克隆
sql文件已置于项目中,数据库使用的mysql
创建项目
我这里使用的idea,正常创建spMxyjsFSring boot项目就行了。
sql
-- 创建master数据库
CREATE DATABASE db_master;
USE db_master;
-- 用户表
CREATE TABLE users(
id INT PRIMARY KEY AUTO_INCREMENT COMMENT '主键id',
username VARCHAR(20) NOT NULL COMMENT '用户名',
pwd VARCHAR(50) NOT NULL COMMENT '密码',
phone CHAR(11) NOT NULL COMMENT '手机号',
email VARCHAR(50) COMMENT '邮箱',
gender CHAR(1) COMMENT '性别 1-->男 0-->女',
age INT COMMENT '年龄',
created_time TIMESTAMP NULL DEFAULT NULL COMMENT '创建时间',
updated_time TIMESTAMP NULL DEFAULT NULL COMMENT '修改时间',
deleted_time TIMESTAMP NULL DEFAULT NULL COMMENT '删除时间',
type INT DEFAULT 1 COMMENT '状态 1-->启用 2-->禁用 3-->软删除'
)ENGINE=INNODB;
INSERT INTO users(username,pwd,phone,email,gender,age) VALUES('supperAdmin','admin123','13000000000','super@admin.com',1,20);
INSERT INTO users(username,pwd,phone,email,gender,age) VALUES('admin','admin','13200840033','admin@163.com',0,14);
INSERT MxyjSFSINTO users(username,pwd,phone,email,gender,age) VALUES('wanggang','wanggang','13880443322','wang@gang-',1,36);
INSERT INTO users(username,pwd,phone,email,gender,age) VALUES('xiaobao','xiaobao','18736450102','xiao@bao.com',0,22);
INSERT INTO users(username,pwd,phone,email,gender,age) VALUES('zhaoxing','zhaoxing','18966004400','zhao@xing.com',1,29);
INSERT INTO users(username,pwd,phone,email,gender,age) VALUES('chenyun','chenyun','13987613540','chen@yun.com',1,25);
INSERT INTO users(username,pwd,phone,email,gender,age) VALUES('ouyangruixue','ouyangruixue','15344773322','ouyang@163.com',0,24);
-- 创建local数据库
CREATE DATABASE db_local;
USE db_local;
-- 本地库用户表
CREATE TABLE users(
id INT PRIMARY KEY AUTO_INCREMENT COMMENT '主键id',
username VARCHAR(20) NOT NULL COMMENT '用户名',
pwd VARCHAR(50) NOT NULL COMMENT '密码',
phone CHAR(11) NOT NULL COMMENT '手机号',
email VARCHAR(50) COMMENT '邮箱',
gender CHAR(1) COMMENT '性别 1-->男 0-->女',
age INT COMMENT '年龄',
created_time TIMESTAMP NULL DEFAULT NULL COMMENT '创建时间',
updated_time TIMESTAMP NULL DEFAULT NULL COMMENT '修改时间',
deleted_time TIMESTAMP NULL DEFAULT NULL COMMENT '删除时间',
type INT DEFAULT 1 COMMENT '状态 1-->启用 2-->禁用 3-->软删除'
)ENGINE=INNODB;
-- 本地库测试表
CREATE TABLE local_test(
id INT PRIMARY KEY AUTO_INCREMENT,
str VARCHAR(20)
)ENGINE=INNODB;
INSERT INTO local_test(str) VALUES ('Hello');
INSERT INTO local_test(str) VALUES ('World');
INSERT INTO local_test(str) VALUES ('Mysql');
INSERT INTO local_test(str) VALUES ('^.^');
pom文件
目前只引入了lombok、mybatis-plus、mysql相关的依赖
application.yml
properties删除,yml创建。其实无所谓yml还是properties
server:
port: 8001
spring:
datasource:
master:
url: jdbc:mysql://localhost:3306/db_master?useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=Asia/Shanghai&allowMultiQueries=true
username: root
password: 123456
driver-class-name: com.mysql.cj.jdbc.Driver
local:
url: jdbc:mysql://localhost:3306/db_local?useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=Asia/Shanghai&allowMultiQueries=true
username: root
password: 123456
driver-class-name: com.mysql.cj.jdbc.Driver
mybatis-plus:
mapper-locations: classpath:mappers/*/*Mapper.xml
#实体扫描,多个package用逗号或者分号分隔
typeAliasesPackage: com.niuniu.sys.module
check-config-location: true
configuration:
#是否开启自动驼峰命名规则(camel case)映射
map-underscore-to-camel-case: true
#全局地开启或关闭配置文件中的所有映射器已经配置的任何缓存
cache-enabled: false
call-setters-on-nulls: true
#配置JdbcTypeForNull, oracle数据库必须配置
jdbc-type-for-null: 'null'
#MyBatis 自动映射时未知列或未知属性处理策略 NONE:不做任何处理 (默认值), WARNING:以日志的形式打印相关警告信息, FAILING:当作映射失败处理,并抛出异常和详细信息
auto-mapping-unknown-column-behavior: warning
global-config:
banner: false
db-config:
#主键类型 0:"数据库ID自增", 1:"未设置主键类型",2:"用户输入ID (该类型可以通过自己注册自动填充插件进行填充)", 3:"全局唯一ID (idWorker), 4:全局唯一ID (UUID), 5:字符串全局唯一ID (idWorker MxyjSFS的字符串表示)";
id-type: auto
#字段验证策略 IGNORED:"忽略判断", NOT_NULL:"非NULL判断", NOT_EMPTY:"非空判断", DEFAULT 默认的,一般只用于注解里(1. 在全局里代表 NOT_NULL,2. 在注解里代表 跟随全局)
field-strategy: NOT_EMPTY
#数据库大写下划线转换
capital-mode: true
#逻辑删除值
logic-delete-value: 0
#逻辑未删除值
logic-not-delete-value: 1
pagehelper: #pagehelper分页插件
helperDialect: mysql #设置数据库方言
reasonable: true
supportMethodsArguments: true
params: count=countSql
目录结构
由于该项目分为两个数据源。在各个分层中,你将分别见到master、local。
备注:由于users表在两个数据源中完全相同,将Users实体置于model层公用
关键代码
在config包中,我们需要对master、local的数据源进行配置,使其可以通过不同的请求,自动切换数据源
另外 由于该项目使用mybatis-plus,在创建SqlSessionFactory的时候,请使用MybatisSqlSessionFactoryBean进行bean对象的创建
local数据源
package com.demo.config;
import com.baomidou.mybatisplus.extension.spring.MybatisSqlSessionFactoryBean;
import com.zaxxer.hikari.HikariDataSource;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.ClassPathResource;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import javax.sql.DataSource;
@Configuration
@MapperScan(basePackages = "com.demo.dao.local",
sqlSessionFactoryRef = "localSqlSessionFactory")
public class LocalDataSourceConfig {
@Value("${spring.datasource.local.driver-class-name}")
private String driverClassName;
@Value("${spring.datasource.local.url}")
private String url;
@Value("${spring.datasource.local.username}")
private String username;
@Value("${spring.datasource.local.password}")
private String password;
@Bean(name = "localDataSource")
public DataSource localDataSource() {
HikariDataSource dataSource = new HikariDataSource();
dataSource.setUsername(username);
dataSource.setPassword(password);
dataSource.setJdbcUrl(url);
dataSource.setDriverClassName(driverClassName);
dataSource.setMaximumPoolSize(10);
dataSource.setMinimumIdle(5);
dataSource.setPoolName("localDataSourcePool");
return dataSource;
}
/**
* local数据源
*/
@Bean(name = "localSqlSessionFactory")
public SqlSessionFactory sqlSessionFactory(@Qualifier("localDataSource") DataSource dataSource) throws Exception {
MybatisSqlSessionFactoryBean bean = new MybatisSqlSessionFactoryBean();
bean.setDataSource(dataSource);
// 设置Mybatis全局配置路径
bean.setConfigLocation(new ClassPathResource("mybatis-config.xml"));
bean.setMapperLocations(
new PathMatchingResourcePatternResolver().getResources("classpath*:mappers/local/*.xml"));
return bean.getObject();
}
@Bean(name = "localTransactionManager")
public DataSourceTransactionManager transactionManager(@Qualifier("localDataSource") DataSource dataSource) {
return new DataSourceTransactionManager(dataSource);
}
@Bean(name = "localSqlSessionTemplate")
public SqlSessionTemplate testSqlSessionTemplate(
@Qualifier("localSqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
return new SqlSessionTemplate(sqlSessionFactory);
}
}
master数据源
package com.demo.config;
import com.baomidou.mybatisplus.extension.spring.MybatisSqlSessionFactoryBean;
import com.zaxxer.hikari.HikariDataSource;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.core.io.ClassPathResource;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import javax.sql.DataSource;
@Configuration
@MapperScan(basePackages = "com.demo.dao.master",
sqlSessionFactoryRef = "masterSqlSessionFactory")
public class MasterDataSourceConfig {
@Value("${spring.datasource.master.driver-class-name}")
private String driverClassName;
@Value("${spring.datasource.master.url}")
private String url;
@Value("${spring.datasource.master.username}")
private String username;
@Value("${spring.datasource.master.password}")
private String password;
@Bean(name = "masterDataSource")
@Primary
public DataSource masterDataSource() {
HikariDataSource dataSource = new HikariDataSource();
dataSource.setUsername(username);
dataSource.setPassword(password);
dataSource.setJdbcUrl(url);
dataSource.setDriverClassName(driverClassName);
dataSource.setMaximumPoolSize(10);
dataSource.setMinimumIdle(5);
dataSource.setPoolName("masterDataSource");
return dataSource;
}
/**
* master数据源
*/
@Bean(name = "masterSqlSessionFactory")
@Primary
public SqlSessionFactory sqlSessionFactory(@Qualifier("masterDataSource") DataSource dataSource) throws Exception {
MybatisSqlSessionFactoryBean bean = new MybatisSqlSessionFactoryBean();
bean.setDataSource(dataSource);
bean.setConfigLocation(new ClassPathResource("mybatis-config.xml"));
bean.setMapperLocations(
new PathMatchingResourcePatternResolver().getResources("classpath*:mappers/master/*.xml"));
return bean.getObject();
}
@Bean(name = "masterTransactionManager")
@Primary
public DataSourceTransactionManager transactionManager(@Qualifier("masterDataSource") DataSource dataSource) {
return new DataSourceTransactionManager(dataSource);
}
@Bean(name = "masterSqlSessionTemplate")
@Primary
public SqlSessionTemplate testSqlSessionTemplate(
@Qualifier("masterSqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
return new SqlSessionTemplate(sqlSessionFactory);
}
}
部分代码
Users实体类
package com.demo.model;
import lombok.Data;
import java.util.Date;
@Data
public class Users {
private Integer id;
private String username;
private String pwd;
private String phone;
private String email;
private Integer gender;
private Integer age;
private Date created_time;
private Date updated_time;
private Date deleted_time;
private Integer type;
}
UsersMapper
package com.demo.dao.master;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.demo.model.Users;
import org.apache.ibatis.annotations.Mapper;
@Mapper
public interface UsersMapper extends BaseMapper
}
UsersService
package com.demo.service.master;
import com.baomidou.mybatisplus.extension.service.IService;
import com.demo.model.Users;
public interface UsersService extends IService
}
UsersServiceImpl
package com.demo.service.master.impl;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.demo.dao.master.UsersMapper;
import com.demo.model.Users;
import com.demo.service.master.UsersService;
import org.springframework.stereotype.Service;
@Service
public class UsersServiceImpl extends ServiceImpl
}
UsersController
package com.demo.controller.master;
import com.demo.model.Users;
import com.demo.service.master.UsersService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframewMxyjSFSork.web.bind.annotation.RestController;
import java.util.List;
@RestController
@RequestMapping("/master")
public class UsersController {
@Autowired
private UsersService userssService;
@RequestMapping("/users")
public List
return userssService.list();
}
}
最终的项目结构
运行结果
至此Spring Boot + Mybatis-Plus已经完成了多数据源的实现。
最后再写一点我遇到的坑吧
1、master与local中不要出现同名的文件
2、数据源配置中SqlSessionFactoryBean替换为MybatisSqlSessionFactoryBean
下一篇将会围绕xxl-job任务调度中心,介绍如何通过xxl-job实现本地库与线上库的定时同步
版权声明:本文内容由网络用户投稿,版权归原作者所有,本站不拥有其著作权,亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容,请联系我们jiasou666@gmail.com 处理,核实后本网站将在24小时内删除侵权内容。
发表评论
暂时没有评论,来抢沙发吧~