1. Introduce dependencies

In the project pom. XML file, introduce the sharding-JDBC dependency:

<dependency>
  	<groupId>org.apache.shardingsphere</groupId>
  	<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
  	<version>4.0.0 - RC1</version>
</dependency>
Copy the code

2. Add the read/write separation configuration

The bootstrap.yml project adds the read/write separation configuration. Here is the dev environment configuration:

spring:
  shardingsphere:
    datasource:
      names: master, slave
      master:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://localhost:3306/testdb
        username: ' '
        password: ' '
      slave:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://localhost:3306/testdb1
        username: ' '
        password: ' '
    masterslave:
      name: ms
      master-data-source-name: master
      slave-data-source-names: slave
Copy the code

3. Demo effect

3.1. Create a test table

The dev environment actually turns off master/slave synchronization, which is demonstrated by manually modifying data from the slave library

CREATE TABLE `t1` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL DEFAULT 'testname'.`age` int(10) unsigned NOT NULL DEFAULT '30',
  PRIMARY KEY (`id`))ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- Data inserted by the primary library
INSERT INTO `testdb`.`t1`(`id`.`name`.`age`) VALUES (1.'wangao'.10);

-- Data inserted from the library
INSERT INTO `testdb1`.`t1`(`id`.`name`.`age`) VALUES (1.'wangao'.20);
Copy the code

3.2. Write the database access interface

@Entity
@Table(name = "t1")
@Data
public class User {

    @Id
    @Column(name = "id")
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;
    
    @Column(name = "name")
    private String name;
    
    @Column(name = "age")
    private Integer age;
    
}
Copy the code
public interface UserDao extends JpaRepository<User.Integer> {}Copy the code

3.3. Save data

@Test
public void base(a) {
  	User user = new User();
  	user.setName("wangao");
  	user.setAge(10);
  	user = userDao.save(user);
  	System.out.println(user);
}
Copy the code

The save method returns an object with an incremented ID as normal:

New data inserted in primary database:

No data in slave library (master/slave synchronization off) :

3.4. Read data

Select * from database where id = 1 and age = 20

@Test
public void readSlave(a) {
 		User user = userDao.findById(1).get();
  	System.out.println(user);
}
Copy the code

3.5. Force reads from the primary library

Some cases require forced reads from the primary library. Select * from primary database where id = 1 and age = 10;

@Test
public void readMaster(a) {
  	HintManager manager = HintManager.getInstance();
  	manager.setMasterRouteOnly();
  	User user = userDao.findById(1).get();
  	System.out.println(user);
  	// Remember to close
  	manager.close();
}
Copy the code

3.6. Pay special attention

The official document on read-write separation has this to say:

Note that the default configuration is spring.jpa.open-in-view=true. In this case, a Restful request is connected to the same database, and if there is a write operation, subsequent read operations are read from the main library

@RestController
public class UserController {
    
    private final UserDao userDao;
    
    public UserController(UserDao userDao) {
        this.userDao = userDao;
    }
    
    @GetMapping("api/user/{id}")
    public User get(@PathVariable int id) {
      	// Write
        User user = new User();
        user.setName(Oreo);
        user.setAge(30);
        user = userDao.save(user);
        System.out.println(user);
        // Perform the read operation
        return userDao.findById(id).orElse(null); }}Copy the code

If you change the configuration to spring.jpa.open-in-view=false, each operation database connection is fetched from the connection pool, so that read operations are read from the slave library:

4, reference

More details can be found on the Sharding-JDBC website:

Shardingsphere.apache.org/document/cu…