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…