java mysql sharding_springmvc+mybatis+shardingsphere（shardingjdbc）实现数据库（mysql）读写分离架构...

在存在大量读操作的场景，可以采用数据库读写分离的机制来加快查询速度。

mysql本身就支持多服务实现读写分离，而springmvc要实现可以自己写读写分离的代码实现，其基本原理就是采用aop原理，拦截特定的自定义注解方法，通过不同的参数调用不同的数据源，这个网上有很多例子。

如果是已经存在的系统改造或者自己不想写代码，也可以采用已经成熟的框架，本人使用的是sharding-sphere的sharding-jdbc，官网是：http://shardingjdbc.io/。

首先，要在springmvc项目的pom文件中添加包，代码如下：

io.shardingsphere

sharding-jdbc-spring-boot-starter

3.0.0.M1

io.shardingsphere

sharding-jdbc-spring-namespace

3.0.0.M1

然后，准备好三个数据库，一个主库两个从库，新增回加入主库，查询回随机走从库，如果mysql配置了读写分离机制，会自动把主库的数据同步到从库。创建脚本如下：-- 主库

CREATE DATABASE `master`;

CREATE TABLE `t_order` (

`order_id` int(11) NOT NULL,

`user_id` int(11) NOT NULL,

`business_id` int(4) DEFAULT NULL,

PRIMARY KEY (`order_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `t_order` VALUES (1,1,112);

-- 从库1

CREATE DATABASE `slave_1` ;

CREATE TABLE `t_order` (

`order_id` int(11) NOT NULL,

`user_id` int(11) NOT NULL,

`business_id` int(4) DEFAULT NULL,

PRIMARY KEY (`order_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;

INSERT INTO `t_order` VALUES (2,2,112);

-- 从库2

CREATE DATABASE `slave_2` ;

CREATE TABLE `t_order` (

`order_id` int(11) NOT NULL,

`user_id` int(11) NOT NULL,

`business_id` int(4) DEFAULT NULL,

PRIMARY KEY (`order_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `t_order` VALUES (3,3,112)；

数据库信息配置文件db.properties配置如下：#master

jdbc.master.driverClassName=com.mysql.jdbc.Driver

jdbc.master.url=jdbc:mysql://localhost:3306/master?useUnicode=true&characterEncoding=utf8

jdbc.master.username=root

jdbc.master.password=123456

#slave1

jdbc.slave1.driverClassName=com.mysql.jdbc.Driver

jdbc.slave1.url=jdbc:mysql://localhost:3306/slave_1?useUnicode=true&characterEncoding=utf8

jdbc.slave1.username=root

jdbc.slave1.password=123456

#slave2

jdbc.slave2.driverClassName=com.mysql.jdbc.Driver

jdbc.slave2.url=jdbc:mysql://localhost:3306/slave_2?useUnicode=true&characterEncoding=utf8

jdbc.slave2.username=root

jdbc.slave2.password=123456

spring配置文件，配置数据源代码如下：

xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

xmlns:context="http://www.springframework.org/schema/context"

xmlns:tx="http://www.springframework.org/schema/tx"

xmlns:mongo="http://www.springframework.org/schema/data/mongo"

xmlns:rabbit="http://www.springframework.org/schema/rabbit"

xmlns:master-slave="http://shardingsphere.io/schema/shardingsphere/masterslave"

xmlns:aop="http://www.springframework.org/schema/aop"

xsi:schemaLocation="http://www.springframework.org/schema/beans

http://www.springframework.org/schema/beans/spring-beans.xsd

http://www.springframework.org/schema/aop

http://www.springframework.org/schema/aop/spring-aop-3.2.xsd

http://www.springframework.org/schema/context

http://www.springframework.org/schema/context/spring-context.xsd

http://www.springframework.org/schema/tx

http://www.springframework.org/schema/tx/spring-tx.xsd

http://www.springframework.org/schema/data/mongo

http://www.springframework.org/schema/data/mongo/spring-mongo-1.5.xsd

http://www.springframework.org/schema/rabbit

http://www.springframework.org/schema/rabbit/spring-rabbit-1.6.xsd

http://shardingsphere.io/schema/shardingsphere/masterslave

http://shardingsphere.io/schema/shardingsphere/masterslave/master-slave.xsd">

classpath*:property/*.properties

true

SELECT 1 FROM DUAL

true

SELECT 1 FROM DUAL

true

SELECT 1 FROM DUAL

classpath*:mybatis/mapper/**/*Mapper.xml

model文件UserModel.java代码如下：package com.shan.ssm.model;

public class UserModel {

private Integer orderId;

private Integer userId;

private Integer businessId;

public Integer getOrderId() {

return orderId;

}

public void setOrderId(Integer orderId) {

this.orderId = orderId;

}

public Integer getUserId() {

return userId;

}

public void setUserId(Integer userId) {

this.userId = userId;

}

public Integer getBusinessId() {

return businessId;

}

public void setBusinessId(Integer businessId) {

this.businessId = businessId;

}

}

ShardingJdbcController.java代码如下：package com.shan.ssm.controller;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.stereotype.Controller;

import org.springframework.web.bind.annotation.RequestMapping;

import org.springframework.web.bind.annotation.ResponseBody;

import com.shan.ssm.service.IShardingJdbcService;

@Controller

@RequestMapping("sjc")

public class ShardingJdbcController {

@Autowired

private IShardingJdbcService iShardingJdbcService;

@RequestMapping("/insert")

@ResponseBody

public String insert() {

String msg = iShardingJdbcService.insert();

return msg;

}

@RequestMapping("/list")

@ResponseBody

public String list() {

String msg = iShardingJdbcService.list();

return msg;

}

}

IShardingJdbcService.java接口代码如下：package com.shan.ssm.service;

public interface IShardingJdbcService {

public String insert();

public String list();

}

ShardingJdbcService.java实现代码如下：package com.shan.ssm.service.impl;

import java.util.List;

import java.util.Map;

import javax.annotation.Resource;

import org.apache.commons.lang3.RandomUtils;

import org.apache.ibatis.session.SqlSession;

import org.springframework.stereotype.Service;

import com.alibaba.fastjson.JSONObject;

import com.shan.ssm.model.UserModel;

import com.shan.ssm.service.IShardingJdbcService;

@Service

public class ShardingJdbcService implements IShardingJdbcService {

@Resource

private SqlSession sqlSession;

@Override

public String insert() {

UserModel um = new UserModel();

um.setOrderId(RandomUtils.nextInt(10, 10000));

um.setUserId(RandomUtils.nextInt(10, 10000));

um.setBusinessId(RandomUtils.nextInt(10, 10000));

int num = sqlSession.insert(ShardingJdbcService.class.getName() + ".insert", um);

JSONObject obj = new JSONObject();

if (num >= 0) {

obj.put("code", 0);

obj.put("msg", "成功");

} else {

obj.put("code", 1);

obj.put("msg", "失败");

}

return obj.toJSONString();

}

@Override

public String list() {

List> list= sqlSession.selectList(ShardingJdbcService.class.getName() + ".list");

JSONObject obj = new JSONObject();

obj.put("list", list);

return obj.toJSONString();

}

}

mybatis的mapper文件ShardingJdbcServiceMapper.xml代码如下：<?xml version="1.0" encoding="UTF-8" ?>

INSERT INTO t_order

(order_id,

user_id,

business_id)

VALUES

(#{orderId},

#{userId},

#{businessId}

)

SELECT * FROM t_order

启动程序后，测试插入，发现只会往主库中插入数据，如图：

查询也是随机调用的两个从库，如图：