1.數(shù)據(jù)庫(kù)表結(jié)構(gòu)
CREATE TABLE `t_user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`age` int(11) NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
2. 主要代碼
2.1.配置文件設(shè)置
sharding.jdbc.datasource.names=master,slave
# master主庫(kù)設(shè)置
sharding.jdbc.datasource.master.type=com.zaxxer.hikari.HikariDataSource
sharding.jdbc.datasource.master.driver-class-name=com.mysql.cj.jdbc.Driver
sharding.jdbc.datasource.master.jdbc-url=jdbc:mysql://ip:port/database
sharding.jdbc.datasource.master.username=
sharding.jdbc.datasource.master.password=
# Slave從庫(kù)設(shè)置
sharding.jdbc.datasource.slave.type=com.zaxxer.hikari.HikariDataSource
sharding.jdbc.datasource.slave.driver-class-name=com.mysql.cj.jdbc.Driver
sharding.jdbc.datasource.slave.jdbc-url=jdbc:mysql://ip:port/database
sharding.jdbc.datasource.slave.username=
sharding.jdbc.datasource.slave.password=
# 配置讀寫分離
sharding.jdbc.config.masterslave.name=ms
sharding.jdbc.config.masterslave.master-data-source-name=master
sharding.jdbc.config.masterslave.slave-data-source-names=slave
# 打印SQL商佑，測(cè)試時(shí)可以看到SQL查詢使用的DataSource
sharding.jdbc.config.props.sql.show=true
2.2.實(shí)體類
@Entity
@Data
@Table(name = "t_user")
public class UserDO {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
Integer id;
String name;
Integer age;
public UserDO() {
}
public UserDO(String name, Integer age) {
this.name = name;
this.age = age;
}
}
2.3.Repo配置
@Repository
public interface UserRepo extends JpaRepository<UserDO, Integer> {
@Transactional
@Modifying
@Query(value = "update UserDO set age = :age where name = :name")
void updateAge(@Param("name") String name, @Param("age") Integer age);
UserDO findByName(String name);
}
到此就配置完了，可以使用起來(lái)了。我寫了一個(gè)service+controller方便測(cè)試，代碼如下：
2.4.Service
@Service
public class UserService {
@Autowired
UserRepo userRepo;
public void testUpdate(String name, Integer age) {
userRepo.updateAge(name, age);
}
public Integer testSelect(String name) {
return userRepo.findByName(name).getAge();
}
}
2.5.Controller
@RestController
@Slf4j
public class TestController {
@Autowired
UserService userService;
@GetMapping("/test")
public Integer testInsert() {
log.info("start update");
userService.testUpdate("zhangsan", (int) (Math.random() * 100));
log.info("start select");
return userService.testSelect("zhangsan");
}
}
3.測(cè)試
controller中會(huì)先進(jìn)行寫操作(修改年齡)上真，然后進(jìn)行讀操作(讀取修改后的年齡)
訪問http://127.0.0.1:8080/test 后查看日志如下：
服務(wù)日志
可以看到update操作落在了master數(shù)據(jù)源上，select操作落在slave數(shù)據(jù)源上羹膳。
源碼地址：https://github.com/Grrui/springboot-demo/tree/main/springboot-shardingsphere
