SB+MyCat mysql主从2 多数据源

######mycat######

1、server.xml
###schemas:TESTDB
<user name="root" defaultAccount="true">
　　<property name="password">root</property>
　　<property name="schemas">TESTDB</property>

　　　　<!-- 表级 DML 权限设置 -->
　　　　<!--
　　<privileges check="false">
　　　　<schema name="TESTDB" dml="0110" >
　　　　　　<table name="tb01" dml="0000"></table>
　　　　　　<table name="tb02" dml="1111"></table>
　　　　</schema>
　　</privileges>
　　-->
</user>

<user name="user">
　　<property name="password">user</property>
　　<property name="schemas">TESTDB</property>
　　<property name="readOnly">true</property>
</user>

 

 

2、schema.xml
###schemas:TESTDB
###database:TESTDB
###dataNode:dn1
###dataHost:localhost1

<mycat:schema xmlns:mycat="http://io.mycat/">

　　<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">

　　</schema>
　　<dataNode name="dn1" dataHost="localhost1" database="TESTDB" />
　　<dataHost name="localhost1" maxCon="1000" minCon="100" balance="2"
　　　　writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
　　　　<heartbeat>select user()</heartbeat>
　　　　<writeHost host="hostM1" url="localhost:3306" user="root" password="root">
　　　　　　<!--<readHost host="hostS2" url="localhost:3307" user="root" password="root" />-->
　　　　</writeHost>
　　　　<writeHost host="hostM2" url="localhost:3307" user="root" password="root">
　　　　　　<!--<readHost host="hostS2" url="localhost:3307" user="root" password="root" />-->
　　　　</writeHost>
　　</dataHost>
</mycat:schema>

 

 

######springboot####################################################################################
http://localhost:8080/addOne
http://localhost:8080/findAllOne
http://localhost:8080/findAllTwo

 

1、pom.xml

<dependency>
　　<groupId>org.springframework.boot</groupId>
　　<artifactId>spring-boot-starter-data-jpa</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>

 

 

 

2、application.properties

# 应用名称
spring.application.name=mycat2
# 应用服务 WEB 访问端口
server.port=8080

#spring.datasource.primary.jdbc-url=jdbc:mysql://localhost:3306/study?useUnicode=true&characterEncoding=utf-8&useSSL=true

 

# 数据库驱动one：
spring.datasource.one.driver-class-name=com.mysql.cj.jdbc.Driver
# 数据源名称
#spring.datasource.name=defaultDataSource
# 数据库连接地址
spring.datasource.one.jdbc-url=jdbc:mysql://localhost:3306/TESTDB?serverTimezone=UTC
# 数据库用户名&密码：
spring.datasource.one.username=root
spring.datasource.one.password=root

 

 

# 数据库驱动two：
spring.datasource.two.driver-class-name=com.mysql.cj.jdbc.Driver
# 数据源名称
#spring.datasource.name=defaultDataSource
# 数据库连接地址
spring.datasource.two.jdbc-url=jdbc:mysql://localhost:3307/TESTDB?serverTimezone=UTC
# 数据库用户名&密码：
spring.datasource.two.username=root
spring.datasource.two.password=root

 

 

#jpa
spring.jpa.hibernate.ddl-auto=update
spring.jpa.show-sql=false
#spring.jpa.database=mysql
#spring.jpa.database-platform=org.hibernate.dialect.MySQLDialect
#sring.jpa.database-platform=org.hibernate.dialect.MariaDBDialect
spring.jpa.hibernate.one-dialect=org.hibernate.dialect.MariaDBDialect
spring.jpa.hibernate.two-dialect=org.hibernate.dialect.MariaDBDialect

 

 

 

 

 

3、com.sb.mycat2.one
Entity:TestOne

package com.sb.mycat2.one;

import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity
@Table(name = "test")
public class TestOne {

@Id
@GeneratedValue
private Integer id;
private String name;

public TestOne() {
}
public TestOne(String name) {
　　this.name = name;
}
public Integer getId() {
　　return id;
}

public void setId(Integer id) {
　　this.id = id;
}

public String getName() {
　　return name;
}

public void setName(String name) {
　　this.name = name;
}

@Override
public String toString() {
　　return "Test{" +
　　　　"id=" + id +
　　　　", name='" + name + '\'' +
　　'}';
}

 

 

}

 

 

 

 

TestRepository:TestOneRepository

package com.sb.mycat2.one;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;
import java.util.List;
@Repository
public interface TestOneRepository extends JpaRepository<TestOne,Integer> {

　　public List<TestOne> findAll();
}

 

 

4、com.sb.mycat2.two
Entity:TesTwo

package com.sb.mycat2.two;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity
@Table(name = "test")
public class TestTwo {

@Id
@GeneratedValue
private Integer id;
private String name;

public TestTwo() {
}
public TestTwo(String name) {
　　this.name = name;
}
public Integer getId() {
　　return id;
}

public void setId(Integer id) {
　　this.id = id;
}

public String getName() {
　　return name;
}

public void setName(String name) {
　　this.name = name;
}

@Override
public String toString() {
return "Test{" +
　　"id=" + id +
　　", name='" + name + '\'' +
　　'}';
}

 

 

}

 

TestRepository:TestTwoRepository

package com.sb.mycat2.two;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;
import java.util.List;

@Repository
public interface TestTwoRepository extends JpaRepository<TestTwo,Integer> {

　　public List<TestTwo> findAll();
}

 

 

5、config
1)、DataSourceConfig
package com.sb.mycat2.config;

import org.springframework.beans.factory.annotation.Qualifier;
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 javax.sql.DataSource;

@Configuration
public class DataSourceConfig {

 

 

/**
* 第一个数据连接，默认优先级最高
* @return
*/
@Primary
@Bean(name = "oneDataSource")
@Qualifier("oneDataSource")
@ConfigurationProperties(prefix="spring.datasource.one")
public DataSource oneDataSource() {
　　return DataSourceBuilder.create().build();
}

 

 

 

/**
* 第二个数据源
* @return
*/
@Bean(name = "twoDataSource")
@Qualifier("twoDataSource")
@ConfigurationProperties(prefix="spring.datasource.two")

public DataSource twoDataSource() {
　　return DataSourceBuilder.create().build();
}

 

}

2)、ConfigOne.............................................
package com.sb.mycat2.config;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.orm.jpa.EntityManagerFactoryBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.core.env.Environment;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
import org.springframework.orm.jpa.JpaTransactionManager;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;
import javax.persistence.EntityManager;
import javax.sql.DataSource;
import java.util.HashMap;
import java.util.Map;

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(entityManagerFactoryRef="entityManagerFactoryOne",transactionManagerRef="transactionManagerOne",basePackages= { "com.sb.mycat2.one" })
public class ConfigOne {

@Autowired
private Environment env;

@Autowired
@Qualifier("oneDataSource")
private DataSource oneDataSource;

 

@Primary
@Bean(name = "entityManagerOne")
public EntityManager entityManager(EntityManagerFactoryBuilder builder) {
　　return entityManagerFactoryOne(builder).getObject().createEntityManager();
}

 

@Primary
@Bean(name = "entityManagerFactoryOne")
public LocalContainerEntityManagerFactoryBean entityManagerFactoryOne(EntityManagerFactoryBuilder builder) {
　　return builder.dataSource(oneDataSource).properties(getVendorProperties()).packages("com.sb.mycat2.one").persistenceUnit("onePersistenceUnit").build();
}

private Map<String, String> getVendorProperties() {
　　Map<String, String> jpaProperties = new HashMap<>(16);
　　jpaProperties.put("hibernate.hbm2ddl.auto", "update");
　　jpaProperties.put("hibernate.show_sql", env.getProperty("spring.jpa.show-sql"));
　　jpaProperties.put("hibernate.format_sql", env.getProperty("spring.jpa.hibernate.format_sql"));
　　jpaProperties.put("hibernate.dialect", env.getProperty("spring.jpa.hibernate.one-dialect"));
　　jpaProperties.put("hibernate.current_session_context_class", "org.springframework.orm.hibernate5.SpringSessionContext");
　　return jpaProperties;
}

@Primary
@Bean(name = "transactionManagerOne")
public PlatformTransactionManager transactionManagerOne(EntityManagerFactoryBuilder builder) {
　　return new JpaTransactionManager(entityManagerFactoryOne(builder).getObject());
}

 

 

}

3)、ConfigTwo.............................................

package com.sb.mycat2.config;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.orm.jpa.EntityManagerFactoryBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.env.Environment;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
import org.springframework.orm.jpa.JpaTransactionManager;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;
import javax.persistence.EntityManager;
import javax.sql.DataSource;
import java.util.HashMap;
import java.util.Map;

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(entityManagerFactoryRef="entityManagerFactoryTwo",transactionManagerRef="transactionManagerTwo",basePackages= { "com.sb.mycat2.two" })
public class ConfigTwo {

 

@Autowired
private Environment env;

 

 

@Autowired
@Qualifier("twoDataSource")
private DataSource twoDataSource;

@Bean(name = "entityManagerTwo")
public EntityManager entityManager(EntityManagerFactoryBuilder builder) {
　　　　return entityManagerFactoryTwo(builder).getObject().createEntityManager();
}

@Bean(name = "entityManagerFactoryTwo")
public LocalContainerEntityManagerFactoryBean entityManagerFactoryTwo(EntityManagerFactoryBuilder builder) {
　　return builder.dataSource(twoDataSource).properties(getVendorProperties()).packages("com.sb.mycat2.two").persistenceUnit("twoPersistenceUnit").build();
}

private Map<String, String> getVendorProperties() {
　　Map<String, String> jpaProperties = new HashMap<>(16);
　　jpaProperties.put("hibernate.hbm2ddl.auto", "update");
　　jpaProperties.put("hibernate.show_sql", env.getProperty("spring.jpa.show-sql"));
　　jpaProperties.put("hibernate.format_sql", env.getProperty("spring.jpa.hibernate.format_sql"));
　　jpaProperties.put("hibernate.dialect", env.getProperty("spring.jpa.hibernate.two-dialect"));
　　jpaProperties.put("hibernate.current_session_context_class", "org.springframework.orm.hibernate5.SpringSessionContext");
　　return jpaProperties;
}

 

@Bean(name = "transactionManagerTwo")
public PlatformTransactionManager transactionManagerTwo(EntityManagerFactoryBuilder builder) {
　　return new JpaTransactionManager(entityManagerFactoryTwo(builder).getObject());
}

 

 

 

 

 

 

 

6、Controller:TestController

package com.sb.mycat2;

import com.sb.mycat2.one.TestOne;
import com.sb.mycat2.one.TestOneRepository;
import com.sb.mycat2.two.TestTwo;
import com.sb.mycat2.two.TestTwoRepository;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Sort;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RestController;
import java.util.List;

@RestController
public class TestController {

/*@Autowired
private TestRepository testRepository;*/

@Autowired
private TestOneRepository testOneRepository;
@Autowired
private TestTwoRepository testTwoRepository;

 

 

/* @GetMapping("/findAll")
public List<Test> findAll(){
return testRepository.findAll();
}

@PostMapping("/add")
public Test addTest(Test test){
return testRepository.save(test);
}*/

 

 

@GetMapping("/findAllOne")
public List<TestOne> findAllOne(){
　　return testOneRepository.findAll();
}

@PostMapping("/addOne")
public TestOne addTest(TestOne testOne){
　　return testOneRepository.save(testOne);
}

@GetMapping("/findAllTwo")
public List<TestTwo> findAllTwo(){
　　Sort.Order order=new Sort.Order(Sort.Direction.DESC,"id");
　　Sort sort1=Sort.by(order);
　　return testTwoRepository.findAll(sort1);
}

 

 

}

two