Mysql 读写分离java实现

最新推荐文章于 2024-05-05 08:44:24 发布

置顶 Pine Chown

最新推荐文章于 2024-05-05 08:44:24 发布

阅读量2k

点赞数 1

分类专栏：后端文章标签：读写分离 Mysql主备双机

本文链接：https://blog.csdn.net/u012397222/article/details/88310674

版权

后端专栏收录该内容

20 篇文章 0 订阅

订阅专栏

日常系统开发中，数据库往往是大多数系统最终性能瓶颈，最终业务操作都在数据库中完成，除了一些热点数据我们往往会把它放入缓存数据库中，提高系统查询效率，但是当数据量大，Redis往往不能解决所有的问题，所以我们会对数据库做一个双机读写操作，同时也提供了数据的一个备份操作。同时我们的业务往往都是读多写少，这样我们更加有必要做一个数据库的读写分离。好了，话不多说，直接上代码：

代码工程是基于Spring Boot 2.0及以上的

POM依赖

<?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 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.chown.pine</groupId>
    <artifactId>master-slave-database</artifactId>
    <version>1.0</version>
    <packaging>jar</packaging>

    <name>master-slave-database</name>

    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <maven.compiler.source>1.8</maven.compiler.source>
        <maven.compiler.target>1.8</maven.compiler.target>
        <mybatis.spring.version>1.3.2</mybatis.spring.version>
        <ali.durid.version>1.1.9</ali.durid.version>
        <mybatis.version>3.4.6</mybatis.version>
        <guava.version>19.0</guava.version>
    </properties>

    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.0.1.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
            <exclusions>
                <exclusion>
                    <artifactId>spring-boot-starter-logging</artifactId>
                    <groupId>org.springframework.boot</groupId>
                </exclusion>
            </exclusions>
        </dependency>
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>${mybatis.version}</version>
        </dependency>
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis-spring</artifactId>
            <version>${mybatis.spring.version}</version>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>${ali.durid.version}</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>
        <dependency>
            <groupId>com.google.guava</groupId>
            <artifactId>guava</artifactId>
            <version>${guava.version}</version>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.16.18</version>
            <scope>provided</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-aop</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
        </dependency>
    </dependencies>
</project>

数据库配置文件（mybatis.properties）

# 主数据源，默认的
spring.datasource.type=com.alibaba.druid.pool.DruidDataSource
spring.datasource.driverClassName=com.mysql.jdbc.Driver
spring.datasource.url=jdbc:mysql://192.168.0.11:21000/tland?autoReconnect=true&useSSL=false&useUnicode=true&characterEncoding=utf-8
spring.datasource.username=tland
spring.datasource.password=quanteng@2018

# 初始化大小，最小，最大
spring.datasource.initialSize=5
spring.datasource.minIdle=5
spring.datasource.maxActive=20
# 配置获取连接等待超时的时间
spring.datasource.maxWait=60000
# 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
spring.datasource.timeBetweenEvictionRunsMillis=60000
# 配置一个连接在池中最小生存的时间，单位是毫秒
spring.datasource.minEvictableIdleTimeMillis=300000
spring.datasource.validationQuery=SELECT 1 FROM DUAL
spring.datasource.testWhileIdle=true
spring.datasource.testOnBorrow=false
spring.datasource.testOnReturn=false
# 打开PSCache，并且指定每个连接上PSCache的大小
spring.datasource.poolPreparedStatements=true
spring.datasource.maxPoolPreparedStatementPerConnectionSize=20

# 从数据源
spring.slave.type=com.alibaba.druid.pool.DruidDataSource
spring.slave.driver-class-name=com.mysql.jdbc.Driver
spring.slave.url=jdbc:mysql://192.168.0.11:21000/tland_dev?autoReconnect=true&useSSL=false&useUnicode=true&characterEncoding=utf-8
spring.slave.username=tland
spring.slave.password=quanteng@2018
spring.slave.initialSize=5
spring.slave.minIdle=5
spring.slave.maxActive=20
# 配置获取连接等待超时的时间
spring.slave.maxWait=60000
# 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
spring.slave.timeBetweenEvictionRunsMillis=60000
# 配置一个连接在池中最小生存的时间，单位是毫秒
spring.slave.minEvictableIdleTimeMillis=300000
spring.slave.validationQuery=SELECT 1 FROM DUAL
spring.slave.testWhileIdle=true
spring.slave.testOnBorrow=false
spring.slave.testOnReturn=false
# 打开PSCache，并且指定每个连接上PSCache的大小
spring.slave.poolPreparedStatements=true
spring.slave.maxPoolPreparedStatementPerConnectionSize=20

spring.datasource.connectionProperties=druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
# 合并多个DruidDataSource的监控数据
spring.datasource.useGlobalDataSourceStat=true

# 配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙
spring.slave.filters=stat,wall,logback

# 通过connectProperties属性来打开mergeSql功能；慢SQL记录
spring.slave.connectionProperties=druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
# 合并多个DruidDataSource的监控数据
spring.slave.useGlobalDataSourceStat=true

项目属性配置文件

server.port=18023
readatasource.readSize=1

数据源配置

package com.chown.pine.config;

import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.context.annotation.PropertySource;

import javax.sql.DataSource;

/**
 * <数据源配置>
 *
 * @author zping
 * @version 2019/3/1 0001
 * @see [相关类/方法]
 * @since [产品/模块版本]
 */
@Configuration
@PropertySource ("classpath:mybatis.properties")
public class DataSourceConfiguration
{

	/**
	 * 数据源类型：Druid、Jdbc、JNI
	 */
	@Value ("${spring.datasource.type}")
	private Class<? extends DataSource> dataSourceType;

	/**
	 * 主数据源 : 负责数据写操作
	 *
	 * @return
	 */
	@Bean (name = "writeDataSource", destroyMethod = "close", initMethod = "init")
	@Primary
	@ConfigurationProperties (prefix = "spring.datasource")
	public DataSource writeDataSource ()
	{
		System.out.println ("-------------------- writeDataSource init ---------------------");
		return DataSourceBuilder.create ().type (dataSourceType).build ();
	}

	/**
	 * 备数据库 ：负责数据读操作
	 *
	 * @return
	 */
	@Bean (name = "readDataSource")
	@ConfigurationProperties (prefix = "spring.slave")
	public DataSource readDataSourceOne ()
	{
		System.out.println ("-------------------- readDataSourceOne init ---------------------");
		return DataSourceBuilder.create ().type (dataSourceType).build ();
	}
}

动态数据源配置

package com.chown.pine.config;

import com.chown.pine.cons.DataSourceType;
import com.chown.pine.context.DataSourceContextHolder;
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;

/**
 * <多数据源切换>
 *
 * @author zping
 * @version 2019/3/1 0001
 * @see [相关类/方法]
 * @since [产品/模块版本]
 */
public class MyAbstractRoutingDataSource extends AbstractRoutingDataSource
{

	/**
	 * 数据源
	 */
	private final int dataSourceNumber;

	public MyAbstractRoutingDataSource (int dataSourceNumber)
	{
		this.dataSourceNumber = dataSourceNumber;
	}

	@Override
	protected Object determineCurrentLookupKey ()
	{
		String typeKey = DataSourceContextHolder.getJdbcType ();
		if (typeKey.equals (DataSourceType.write.getType ()))
		{
			return DataSourceType.write.getType ();
		}
		else
		{
			return DataSourceType.read.getType ();
		}
	}

}

MyBatis配置初始化

package com.chown.pine.config;

import com.chown.pine.cons.DataSourceType;
import com.google.common.collect.Maps;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.autoconfigure.condition.ConditionalOnClass;
import org.springframework.boot.autoconfigure.condition.ConditionalOnMissingBean;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Import;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.core.io.support.ResourcePatternResolver;
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
import org.springframework.transaction.annotation.EnableTransactionManagement;

import javax.annotation.Resource;
import javax.sql.DataSource;
import java.util.Map;

/**
 * <数据库配置>
 *
 * @author zping
 * @version 2019/3/4 0004
 * @see [相关类/方法]
 * @since [产品/模块版本]
 */

@Configuration
@ConditionalOnClass ({ EnableTransactionManagement.class })
@Import ({ DataSourceConfiguration.class })
@MapperScan (basePackages = { "com.chown.pine.mapper" })
public class MybatisConfiguration
{
	@Value ("${spring.datasource.type}")
	private Class<? extends DataSource> dataSourceType;

	@Value ("${readatasource.readSize}")
	private String dataSourceSize;

	@Resource (name = "writeDataSource")
	private DataSource dataSource;

	@Resource (name = "readDataSource")
	private DataSource readDataSources;

	/**
	 * mybatis配置文件路径
	 */
	private static final String MYBATIS_PATH = "classpath:/mybatis-config.xml";

	/**
	 * mybatis映射文件路径
	 */
	private static final String MAPPER_PATH = "classpath:/mapper/*.xml";

	@Bean
	@ConditionalOnMissingBean
	public SqlSessionFactory sqlSessionFactory () throws Exception
	{
		SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean ();
		sqlSessionFactoryBean.setDataSource (roundRobinDataSouceProxy ());
		sqlSessionFactoryBean.setTypeAliasesPackage ("com.chown.pine.entity");
		//sqlSessionFactoryBean.getObject ().getConfiguration ().setMapUnderscoreToCamelCase (true);

		ResourcePatternResolver resolver = new PathMatchingResourcePatternResolver ();

		try
		{
			/*扫描mybatis配置文件*/
			sqlSessionFactoryBean.setConfigLocation (resolver.getResource (MYBATIS_PATH));
			sqlSessionFactoryBean.setMapperLocations (resolver.getResources (MAPPER_PATH));
			return sqlSessionFactoryBean.getObject ();
		}
		catch (Exception e)
		{
			System.out.println ("init database error." + e.getMessage ());
		}

		return sqlSessionFactoryBean.getObject ();
	}

	/**
	 * 有多少个数据源就要配置多少个bean
	 *
	 * @return
	 */
	@Bean
	public AbstractRoutingDataSource roundRobinDataSouceProxy ()
	{
		int size = Integer.parseInt (dataSourceSize);
		MyAbstractRoutingDataSource proxy = new MyAbstractRoutingDataSource (size);
		Map<Object, Object> targetDataSources = Maps.newHashMap ();
		// DataSource writeDataSource = SpringContextHolder.getBean("writeDataSource");
		targetDataSources.put (DataSourceType.write.getType (), dataSource);
		targetDataSources.put (DataSourceType.read.getType (), readDataSources);
		//多个读数据库时
		/*for (int i = 0; i < size; i++)
		{
			targetDataSources.put (i, readDataSources.get (i));
		}*/
		proxy.setDefaultTargetDataSource (dataSource);
		proxy.setTargetDataSources (targetDataSources);
		return proxy;
	}
}

主数据源配置事务

package com.chown.pine.config;

import org.springframework.boot.autoconfigure.jdbc.DataSourceTransactionManagerAutoConfiguration;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.transaction.annotation.EnableTransactionManagement;

import javax.annotation.Resource;
import javax.sql.DataSource;

/**
 * <主数据源配置事务>
 *
 * @author zping
 * @version 2019/3/4 0004
 * @see [相关类/方法]
 * @since [产品/模块版本]
 */
@Configuration
@EnableTransactionManagement
public class DataSourceTransactionManager extends DataSourceTransactionManagerAutoConfiguration
{
	/**
	 * 自定义事务
	 * MyBatis自动参与到spring事务管理中，无需额外配置，
	 * 只要org.mybatis.spring.SqlSessionFactoryBean引用的数据源与DataSourceTransactionManager引用的数据源一致即可，
	 * 否则事务管理会不起作用。
	 *
	 * @return
	 */
	@Resource (name = "writeDataSource")
	private DataSource dataSource;

	@Bean (name = "transactionManager")
	public org.springframework.jdbc.datasource.DataSourceTransactionManager transactionManagers ()
	{
		System.out.println ("-------------------- transactionManager init ---------------------");
		return new org.springframework.jdbc.datasource.DataSourceTransactionManager (dataSource);
	}
}

数据源枚举定义

package com.chown.pine.cons;

import lombok.Getter;

/**
 * <数据源类型枚举定义>
 *
 * @author zping
 * @version 2019/3/1 0001
 * @see [相关类/方法]
 * @since [产品/模块版本]
 */
public enum DataSourceType
{
	read ("read", "从数据源"),
	write ("write", "主数据源");

	@Getter
	private String type;

	@Getter
	private String name;

	DataSourceType (String type, String name)
	{
		this.type = type;
		this.name = name;
	}
}

本地线程变量

package com.chown.pine.context;

import com.chown.pine.cons.DataSourceType;

/**
 * <本地线程全局变量>
 *
 * @author zping
 * @version 2019/3/1 0001
 * @see [相关类/方法]
 * @since [产品/模块版本]
 */
public class DataSourceContextHolder
{
	/**
	 * 本地现场变量
	 */
	private static final ThreadLocal<String> local = new ThreadLocal<String> ();

	/**
	 * 获取本地变量
	 *
	 * @return
	 */
	public static ThreadLocal<String> getLocal ()
	{
		return local;
	}

	/**
	 * 读可能是多个库
	 */
	public static void read ()
	{
		System.out.println ("reader reader reader ...... ");
		local.set (DataSourceType.read.getType ());
	}

	/**
	 * 写只有一个库
	 */
	public static void write ()
	{
		System.out.println ("write write write ...... ");
		local.set (DataSourceType.write.getType ());
	}

	/**
	 * 获取JDBC类型
	 *
	 * @return
	 */
	public static String getJdbcType ()
	{
		return local.get ();
	}
}

数据库AOP切面定义

package com.chown.pine.aspect;

import com.chown.pine.context.DataSourceContextHolder;
import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Before;
import org.springframework.stereotype.Component;

/**
 * <数据库数据切面操作>
 *
 * @author zping
 * @version 2019/3/5 0005
 * @see [相关类/方法]
 * @since [产品/模块版本]
 */
@Aspect
@Component
public class DataOperateAop
{
	/**
	 * 数据查询切面
	 */
	@Before ("execution(* com.chown.pine.mapper..*.get*(..)) || execution(* com.chown.pine.mapper..*.query*(..))")
	public void setReadDataSourceType ()
	{
		DataSourceContextHolder.read ();
		System.out.println ("dataSource切换到：Read");
	}

	/**
	 * 数据写切面
	 */
	@Before ("execution(* com.chown.pine.mapper..*.add*(..)) || execution(* com.chown.pine.mapper..*.update*(..))"
			+ "|| execution(* com.chown.pine.mapper..*.del*(..))")
	public void setWriteDataSourceType ()
	{
		DataSourceContextHolder.write ();
		System.out.println ("dataSource切换到：write");
	}
}

至此，主要代码都在上面，只有Mybatis中数据操作Mapper和XML文件配置需要自己写！！！

Github项目地址

Pine Chown

关注

1
点赞
踩
13

收藏

觉得还不错? 一键收藏
0
评论
Mysql 读写分离java实现

日常系统开发中，数据库往往是大多数系统最终性能瓶颈，最终业务操作都在数据库中完成，除了一些热点数据我们往往会把它放入缓存数据库中，提高系统查询效率，但是当数据量大，Redis往往不能解决所有的问题，所以我们会对数据库做一个双机读写操作，同时也提供了数据的一个备份操作。同时我们的业务往往都是读多写少，这样我们更加有必要做一个数据库的读写分离。好了，话不多说，直接上代码：代码工程是基于Spring ...
复制链接

扫一扫

专栏目录