springboot2+JPA集成sharing-jdbc实现单库分表1、⽤户表增长到⼀定程度，我们假设对user表进⾏分库操作，user为表的逻辑名，实际表名为user_${0..3}，即
user_0,user_1,user_2,user_3,
⾸先创建四张表：
CREATE TABLE `user_0` (
`id` bigint(64) NOT NULL,
`city` varchar(20) NOT NULL,
`name` varchar(20) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE `user_1` (
`id` bigint(64) NOT NULL,
`city` varchar(20) NOT NULL,
`name` varchar(20) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE `user_2` (
`id` bigint(64) NOT NULL,
`city` varchar(20) NOT NULL,
`name` varchar(20) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE `user_3` (
`id` bigint(64) NOT NULL,
`city` varchar(20) NOT NULL,
`name` varchar(20) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
2、引⼊依赖，我需要以下依赖，三个组件+⼀个mysql连接驱动
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-core</artifactId>
<version>4.1.1</version>
</dependency>
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
<version>4.1.1</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
3、基本代码
1、model类：User.java
@Data
@Entity
@Table(name="user")
public class User {
@Id
private Long id;
@Column(updatable = false)
private String city;
private String name;
}
2、dao类：UserRepository.java
demo.shardingJdbcTest.dao;
demo.shardingJdbcTest.domain.User;
import org.springframework.pository.JpaRepository;
import java.util.List;
public interface UserRepository extends JpaRepository<User,Long> { List<User> findUserByCity(String city);
List<User> findUserByNameLike(String name);
}
3、测试类型
@Test
public void testUserRepository(){
User user1 = new User();
user1.setId(1L);
user1.setCity("常⼭");
user1.setName("赵⼦龙");
userRepository.save(user1);
User user2 = new User();
user2.setId(2L);
user2.setCity("⼭东");
user2.setName("诸葛亮");
userRepository.save(user2);
User user3 = new User();
user3.setId(3L);
user3.setCity("⼭西");
user3.setName("黄忠");
userRepository.save(user3);
User user4 = new User();
user4.setId(4L);
user4.setCity("东北");
user4.setName("马超");
userRepository.save(user4);
User user5 = new User();
user5.setId(5L);
user5.setCity("长德");
user5.setName("关云长");
userRepository.save(user5);
User user6 = new User();
user6.setId(6L);
user6.setCity("长德");
user6.setName("张翼德");
userRepository.save(user6);
}
@Test
public void testUserDao(){
// String city = "长德";
// List<User> userList = userRepository.findUserByCity(city);
String keyword = "黄";
List<User> userList = userDao.queryByName(keyword,1,10);
userList.forEach(x -> System.out.Name()));springboot推荐算法
}
4、配置⽂件:(通过代码也可以看出我们使⽤city字段对⽤户进⾏分表的)
hibernate.SQL=DEBUG
spring.jpa.database=mysql
spring.jpa.show-sql=true
spring.jpa.hibernate.ddl-auto=none
#sharing-jdbc-datasource
spring.shardingsphere.datasource.names=db
spring.shardingsphere.pe=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.db.sql.cj.jdbc.Driver
spring.shardingsphere.datasource.db.jdbc-url=jdbc:mysql://host:3306/db?serverTimezone=GMT%2b8&useUnicode=true&autoReconnect=true&characterEncodin spring.shardingsphere.datasource.db.username=root
spring.shardingsphere.datasource.db.password=XXXXXX
#分表策略
spring.shardingsphere.sharding.tables.user.actual-data-nodes=db.user_${0..3}
spring.shardingsphere.sharding.tables.user.table-strategy.standard.sharding-column=city
spring.shardingsphere.sharding.tables.user.table-strategy.standard.demo.shardingJdbcTest.UserSharingAlgorithm spring.shardingsphere.props.sql.show=true
上⾯配置之后，我们使⽤了新的sharing-jdbc-datasource替换了原来的jdbc-datasource，同时配置了User表的分表策略，还有上述配置
还应⽤了⼀个分表算法：
UserSharingAlgorithm.java，这个是最正规代码，我们要实现sharing-jbdc给我提供的接⼝：PreciseShardingAlgorithm类，具体实现如下：
demo.shardingJdbcTest;
slf4j.Slf4j;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingValue;
import java.util.Collection;
/**
* 分表算法
*/
@Slf4j
public class UserSharingAlgorithm implements PreciseShardingAlgorithm<String> {
@Override
public String doSharding(Collection<String> collection, PreciseShardingValue<String> preciseShardingValue) {
log.debug("分表算法参数 {},{}",collection,preciseShardingValue);
int hash = HashUtils.rsHash(String.Value()));
return "user_" + (hash % 4);
}
}
其实很简单，就先对city进⾏rehash，然后取模，四张表就是除4，Hash算法⼤家可以⾃⾏google，我也提供⼀个，以免⼤家阅读不顺
畅，如下：
* RS算法hash
* @param str 字符串
* @return hash值
*/
public static int rsHash(String str) {
int b = 378551;
int a = 63689;
int hash = 0;
for (int i = 0; i < str.length(); i++) {
hash = hash * a + str.charAt(i);
a = a * b;
}
return hash & 0x7FFFFFFF;
}
⼀切进本完成，运⾏测试：
可以看到数据被分别插⼊到不同表中，相同city的数据被放到了同⼀张表中。
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论