数据库
sync_data库
CREATE DATABASE syncsync_data_data;
CREATE TABLE `maa_layout` (
`layout_id` varchar(50) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT '布局ID',
`layout_name` varchar(50) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT '布局名称',
`created_date` char(8) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT '创建时间',
`modified_date` char(8) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT '修改操作时间',
`layout_value` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT '布局详情',
`is_del` tinyint NOT NULL DEFAULT '0' COMMENT '是否删除',
`user_id` varchar(50) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT '用户ID',
PRIMARY KEY (`layout_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='测试';
INSERT INTO `maa_layout` VALUES ('12', '测试', '20210506', '20210506', 0x3132333132333132333132, '0', '张三');
sync_data1库
CREATE DATABASE sync_data1;
CREATE TABLE `maa_layout` (
`layout_id` varchar(50) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT '布局ID',
`layout_name` varchar(50) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT '布局名称',
`created_date` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
`modified_date` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP COMMENT '修改操作时间',
`layout_value` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT '布局详情',
`is_del` tinyint(1) NOT NULL DEFAULT '0' COMMENT '是否删除',
`user_id` varchar(50) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT '用户ID',
PRIMARY KEY (`layout_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='测试2';
INSERT INTO `maa_layout` VALUES ('13', '测试啊啊', '0000-00-00 00:00:00', '0000-00-00 00:00:00', 0x31C2B73233313233313233, '0', 'lifei');
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>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.4.5</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<groupId>com.example</groupId>
<artifactId>demo15</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>demo15</name>
<description>Demo project for Spring Boot</description>
<properties>
<java.version>1.8</java.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.1.4</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>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>1.1.20</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.19</version>
<scope>runtime</scope>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
<configuration>
<excludes>
<exclude>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
</exclude>
</excludes>
</configuration>
</plugin>
</plugins>
</build>
</project>
application.yml
primary:
datasource:
# TEST环境
url: jdbc:mysql://localhost:3306/sync_data?serverTimezone=UTC&&useUnicode=true&&characterEncoding=utf8
username: root
password: root
driverClassName: com.mysql.cj.jdbc.Driver
secondary:
datasource:
# TEST环境
url: jdbc:mysql://localhost:3306/sync_data1?serverTimezone=UTC&&useUnicode=true&&characterEncoding=utf8
username: root
password: root
driverClassName: com.mysql.cj.jdbc.Driver
config配置
PrimaryDataSourceConfig
import com.alibaba.druid.pool.DruidDataSource;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
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.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import javax.sql.DataSource;
@Configuration
@MapperScan(basePackages = PrimaryDataSourceConfig.PACKAGE, sqlSessionFactoryRef = "primarySqlSessionFactory")
public class PrimaryDataSourceConfig {
static final String PACKAGE = "com.mapper.primary";
static final String MAPPER_LOCATION = "classpath:mapper/primary/*.xml";
@Value("${primary.datasource.url}")
private String url;
@Value("${primary.datasource.username}")
private String user;
@Value("${primary.datasource.password}")
private String password;
@Value("${primary.datasource.driverClassName}")
private String driverClass;
@Bean(name = "primaryDataSource")
@Primary
public DataSource primaryDataSource() {
DruidDataSource dataSource = new DruidDataSource();
dataSource.setDriverClassName(driverClass);
dataSource.setUrl(url);
dataSource.setUsername(user);
dataSource.setPassword(password);
return dataSource;
}
@Bean(name = "primaryTransactionManager")
@Primary
public DataSourceTransactionManager primaryTransactionManager() {
return new DataSourceTransactionManager(primaryDataSource());
}
@Bean(name = "primarySqlSessionFactory")
@Primary
public SqlSessionFactory primarySqlSessionFactory(@Qualifier("primaryDataSource") DataSource primaryDataSource)
throws Exception {
final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
sessionFactory.setDataSource(primaryDataSource);
sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver()
.getResources(PrimaryDataSourceConfig.MAPPER_LOCATION));
return sessionFactory.getObject();
}
}
SecondaryDataSourceConfig
import com.alibaba.druid.pool.DruidDataSource;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
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.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import javax.sql.DataSource;
@Configuration
@MapperScan(basePackages = SecondaryDataSourceConfig.PACKAGE, sqlSessionFactoryRef = "secondarySqlSessionFactory")
public class SecondaryDataSourceConfig {
static final String PACKAGE = "com.mapper.secondary";
static final String MAPPER_LOCATION = "classpath:mapper/secondary/*.xml";
@Value("${secondary.datasource.url}")
private String url;
@Value("${secondary.datasource.username}")
private String user;
@Value("${secondary.datasource.password}")
private String password;
@Value("${secondary.datasource.driverClassName}")
private String driverClass;
@Bean(name = "secondaryDataSource")
public DataSource secondaryDataSource() {
DruidDataSource dataSource = new DruidDataSource();
dataSource.setDriverClassName(driverClass);
dataSource.setUrl(url);
dataSource.setUsername(user);
dataSource.setPassword(password);
return dataSource;
}
@Bean(name = "secondaryTransactionManager")
public DataSourceTransactionManager secondaryTransactionManager() {
return new DataSourceTransactionManager(secondaryDataSource());
}
@Bean(name = "secondarySqlSessionFactory")
public SqlSessionFactory secondarySqlSessionFactory(@Qualifier("secondaryDataSource") DataSource secondaryDataSource)
throws Exception {
final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
sessionFactory.setDataSource(secondaryDataSource);
sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver()
.getResources(SecondaryDataSourceConfig.MAPPER_LOCATION));
return sessionFactory.getObject();
}
}
pojo
/**
* @author hy
* @description:
* @date 2021/05/06
*/
@Data
@Builder
@AllArgsConstructor
@NoArgsConstructor
public class MaaLayout {
/**
* ID
*/
private String layoutId;
/**
* 布局名称
*/
private String layoutName;
/**
* 创建时间
*/
private String createdDate;
/**
* 修改操作时间
*/
private String modifiedDate;
/**
* 布局详情
*/
private String layoutValue;
/**
* 是否删除
*/
private Byte isDel;
/**
* 用户ID
*/
private String userId;
}
mapper文件
primary
@Mapper //可写可不写 SqlSessionFactory通过setMapperLocations方法指定了扫描的xml文件的位置
public interface MaaLayoutMapper {
int deleteByPrimaryKey(String layoutId);
int insert(MaaLayout record);
int insertSelective(MaaLayout record);
MaaLayout selectByPrimaryKey(String layoutId);
int updateByPrimaryKeySelective(MaaLayout record);
int updateByPrimaryKey(MaaLayout record);
}
secondary
import tk.mybatis.mapper.annotation.RegisterMapper;
import java.util.Date;
import java.util.List;
@RegisterMapper //可写可不写 SqlSessionFactory通过setMapperLocations方法指定了扫描的xml文件的位置,但写@Mapper 报错
public interface MaaLayoutMapper2 {
int deleteByPrimaryKey(String layoutId);
int insert(MaaLayout record);
int insertSelective(MaaLayout record);
MaaLayout selectByPrimaryKey(String layoutId);
int updateByPrimaryKeySelective(MaaLayout record);
int updateByPrimaryKey(MaaLayout record);
}
xml
primary 和secondary共用
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.mapper.primary.MaaLayoutMapper">
<resultMap id="BaseResultMap" type="com.pojo.MaaLayout">
<!--@mbg.generated-->
<!--@Table maa_layout-->
<id column="layout_id" jdbcType="VARCHAR" property="layoutId" />
<result column="layout_name" jdbcType="VARCHAR" property="layoutName" />
<result column="created_date" jdbcType="CHAR" property="createdDate" />
<result column="modified_date" jdbcType="CHAR" property="modifiedDate" />
<result column="layout_value" jdbcType="LONGVARCHAR" property="layoutValue" />
<result column="is_del" jdbcType="TINYINT" property="isDel" />
<result column="user_id" jdbcType="VARCHAR" property="userId" />
</resultMap>
<sql id="Base_Column_List">
<!--@mbg.generated-->
layout_id, layout_name, created_date, modified_date, layout_value, is_del, user_id
</sql>
<select id="selectByPrimaryKey" parameterType="java.lang.String" resultMap="BaseResultMap">
<!--@mbg.generated-->
select
<include refid="Base_Column_List" />
from maa_layout
where layout_id = #{layoutId,jdbcType=VARCHAR}
</select>
<delete id="deleteByPrimaryKey" parameterType="java.lang.String">
<!--@mbg.generated-->
delete from maa_layout
where layout_id = #{layoutId,jdbcType=VARCHAR}
</delete>
<insert id="insert" parameterType="com.pojo.MaaLayout">
<!--@mbg.generated-->
insert into maa_layout (layout_id, layout_name, created_date,
modified_date, layout_value, is_del,
user_id)
values (#{layoutId,jdbcType=VARCHAR}, #{layoutName,jdbcType=VARCHAR}, #{createdDate,jdbcType=CHAR},
#{modifiedDate,jdbcType=CHAR}, #{layoutValue,jdbcType=LONGVARCHAR}, #{isDel,jdbcType=TINYINT},
#{userId,jdbcType=VARCHAR})
</insert>
<insert id="insertSelective" parameterType="com.pojo.MaaLayout">
<!--@mbg.generated-->
insert into maa_layout
<trim prefix="(" suffix=")" suffixOverrides=",">
<if test="layoutId != null">
layout_id,
</if>
<if test="layoutName != null">
layout_name,
</if>
<if test="createdDate != null">
created_date,
</if>
<if test="modifiedDate != null">
modified_date,
</if>
<if test="layoutValue != null">
layout_value,
</if>
<if test="isDel != null">
is_del,
</if>
<if test="userId != null">
user_id,
</if>
</trim>
<trim prefix="values (" suffix=")" suffixOverrides=",">
<if test="layoutId != null">
#{layoutId,jdbcType=VARCHAR},
</if>
<if test="layoutName != null">
#{layoutName,jdbcType=VARCHAR},
</if>
<if test="createdDate != null">
#{createdDate,jdbcType=CHAR},
</if>
<if test="modifiedDate != null">
#{modifiedDate,jdbcType=CHAR},
</if>
<if test="layoutValue != null">
#{layoutValue,jdbcType=LONGVARCHAR},
</if>
<if test="isDel != null">
#{isDel,jdbcType=TINYINT},
</if>
<if test="userId != null">
#{userId,jdbcType=VARCHAR},
</if>
</trim>
</insert>
<update id="updateByPrimaryKeySelective" parameterType="com.pojo.MaaLayout">
<!--@mbg.generated-->
update maa_layout
<set>
<if test="layoutName != null">
layout_name = #{layoutName,jdbcType=VARCHAR},
</if>
<if test="createdDate != null">
created_date = #{createdDate,jdbcType=CHAR},
</if>
<if test="modifiedDate != null">
modified_date = #{modifiedDate,jdbcType=CHAR},
</if>
<if test="layoutValue != null">
layout_value = #{layoutValue,jdbcType=LONGVARCHAR},
</if>
<if test="isDel != null">
is_del = #{isDel,jdbcType=TINYINT},
</if>
<if test="userId != null">
user_id = #{userId,jdbcType=VARCHAR},
</if>
</set>
where layout_id = #{layoutId,jdbcType=VARCHAR}
</update>
<update id="updateByPrimaryKey" parameterType="com.pojo.MaaLayout">
<!--@mbg.generated-->
update maa_layout
set layout_name = #{layoutName,jdbcType=VARCHAR},
created_date = #{createdDate,jdbcType=CHAR},
modified_date = #{modifiedDate,jdbcType=CHAR},
layout_value = #{layoutValue,jdbcType=LONGVARCHAR},
is_del = #{isDel,jdbcType=TINYINT},
user_id = #{userId,jdbcType=VARCHAR}
where layout_id = #{layoutId,jdbcType=VARCHAR}
</update>
</mapper>
service
/**
* @author hy
* @description:
* @date 2021/05/06
*/
public interface TestService {
MaaLayout test(String code);
MaaLayout test2(String code);
}
Impl
/**
* @author hy
* @description:
* @date 2021/05/06
*/
@Service
public class TestServiceImpl implements TestService{
@Autowired
private MaaLayoutMapper layoutMapper;
@Autowired
private MaaLayoutMapper2 layoutMapper2;
@Override
public MaaLayout test(String code){
MaaLayout maaLayout = layoutMapper.selectByPrimaryKey(code);
return maaLayout;
}
@Override
public MaaLayout test2(String code){
MaaLayout maaLayout = layoutMapper2.selectByPrimaryKey(code);
return maaLayout;
}
}
Controller
/**
* @author hy
* @description:
* @date 2021/05/06
*/
@RestController
public class TestController {
@Autowired
public TestService testService;
@RequestMapping("test")
@ResponseBody
public MaaLayout test(String code){
return testService.test(code);
}
@RequestMapping("test2")
@ResponseBody
public MaaLayout test2(String code){
return testService.test2(code);
}
}
项目结构
演示
注意
@configurationproperties读取的是yml配置文件,配置数据必须放在application.yml中,否则读不到(也就是说多个yml时失效)