java分库分表实战代码
时间: 2023-10-04 07:07:37 浏览: 148
Java分库分表实战案例程序
分库分表是一种常见的水平扩展数据库的方式,可以提高数据库的性能和可扩展性。下面是一个基于Java的分库分表实战代码示例:
1. 配置数据源
在配置文件中配置多个数据源,每个数据源对应一个数据库实例,例如:
```properties
# 数据源1
db1.url=jdbc:mysql://localhost:3306/db1?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&failOverReadOnly=false&maxReconnects=10
db1.username=root
db1.password=root
# 数据源2
db2.url=jdbc:mysql://localhost:3306/db2?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&failOverReadOnly=false&maxReconnects=10
db2.username=root
db2.password=root
```
2. 配置MyBatis
使用MyBatis作为数据访问层框架,并配置分页插件和多数据源支持插件,例如:
```xml
<!-- 分页插件 -->
<plugins>
<plugin interceptor="com.github.pagehelper.PageInterceptor">
<property name="helperDialect" value="mysql"/>
<property name="reasonable" value="true"/>
<property name="supportMethodsArguments" value="true"/>
<property name="params" value="pageNum=pageNumKey;pageSize=pageSizeKey;"/>
</plugin>
<!-- 多数据源支持插件 -->
<plugin interceptor="com.github.mybatis.multidatasource.autoconfigure.DataSourceInterceptor">
<property name="targetDataSources">
<map key-type="java.lang.String">
<entry key="db1" value-ref="db1DataSource"/>
<entry key="db2" value-ref="db2DataSource"/>
</map>
</property>
</plugin>
</plugins>
<!-- 数据源1 -->
<bean id="db1DataSource" class="com.alibaba.druid.pool.DruidDataSource" init-method="init" destroy-method="close">
<property name="driverClassName" value="${db1.driverClassName}"/>
<property name="url" value="${db1.url}"/>
<property name="username" value="${db1.username}"/>
<property name="password" value="${db1.password}"/>
<property name="initialSize" value="5"/>
<property name="minIdle" value="5"/>
<property name="maxActive" value="20"/>
<property name="maxWait" value="60000"/>
<property name="poolPreparedStatements" value="true"/>
<property name="maxPoolPreparedStatementPerConnectionSize" value="20"/>
<property name="validationQuery" value="SELECT 1 FROM DUAL"/>
<property name="testWhileIdle" value="true"/>
<property name="testOnBorrow" value="false"/>
<property name="testOnReturn" value="false"/>
<property name="filters" value="stat,wall"/>
</bean>
<!-- 数据源2 -->
<bean id="db2DataSource" class="com.alibaba.druid.pool.DruidDataSource" init-method="init" destroy-method="close">
<property name="driverClassName" value="${db2.driverClassName}"/>
<property name="url" value="${db2.url}"/>
<property name="username" value="${db2.username}"/>
<property name="password" value="${db2.password}"/>
<property name="initialSize" value="5"/>
<property name="minIdle" value="5"/>
<property name="maxActive" value="20"/>
<property name="maxWait" value="60000"/>
<property name="poolPreparedStatements" value="true"/>
<property name="maxPoolPreparedStatementPerConnectionSize" value="20"/>
<property name="validationQuery" value="SELECT 1 FROM DUAL"/>
<property name="testWhileIdle" value="true"/>
<property name="testOnBorrow" value="false"/>
<property name="testOnReturn" value="false"/>
<property name="filters" value="stat,wall"/>
</bean>
```
3. 配置分库分表规则
使用Sharding-JDBC作为分库分表框架,并配置分库分表规则,例如:
```yaml
spring:
shardingsphere:
datasource:
names: ds1,ds2
ds1:
url: jdbc:mysql://localhost:3306/ds1?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&failOverReadOnly=false&maxReconnects=10
username: root
password: root
type: com.alibaba.druid.pool.DruidDataSource
ds2:
url: jdbc:mysql://localhost:3306/ds2?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&failOverReadOnly=false&maxReconnects=10
username: root
password: root
type: com.alibaba.druid.pool.DruidDataSource
sharding:
tables:
user:
actualDataNodes: ds${1..2}.user_${0..1}
tableStrategy:
standard:
shardingColumn: id
shardingAlgorithmName: userTableShardingAlgorithm
keyGenerateStrategy:
column: id
keyGeneratorName: snowflake
bindingTables: user
defaultDatabaseStrategy:
inline:
shardingColumn: id
algorithmExpression: ds${id % 2 + 1}
shardingAlgorithms:
userTableShardingAlgorithm:
type: INLINE
props:
algorithmExpression: user_${id % 2}
```
4. 编写代码
在代码中使用MyBatis访问数据库,并使用Sharding-JDBC进行分库分表,例如:
```java
@Service
public class UserServiceImpl implements UserService {
@Autowired
private UserMapper userMapper;
@Override
public List<User> listUsers(int pageNum, int pageSize) {
PageHelper.startPage(pageNum, pageSize);
return userMapper.listUsers();
}
@Override
@Transactional
public void addUser(User user) {
userMapper.addUser(user);
}
}
```
以上就是一个基于Java的分库分表实战代码示例,具体实现方式可能因框架和需求不同而有所不同。
阅读全文