使用shardingjdbc需要修改mybatis的資料來源。原來是mybatis直接引用dataSource，現在需要將dataSource注入到shardingjdbc，然後在將shardingjdbc的shardingDataSource注入給mybatis
<!--資料來源-->
<bean name="dataSource" class="com.alibaba.druid.pool.DruidDataSource"
init-method="init" destroy-method="close">
<property name="url" value="${db.master.url}" />
<property name="username" value="${db.master.user}" />
<property name="password" value="${db.master.password}" />
</bean>
複製程式碼對apply_orde，apply_device這兩張表進行分表，按createtime進行分表，每週分一張表
<!--shardingjdbc的規則-->
<sharding:standard-strategy id="orderTableShardingStrategy" sharding-column="createtime" range-algorithm-class="com.dsp.common.algorithm.RangleTableShardingAlgorithm" precise-algorithm-class="com.dsp.common.algorithm.PreciseTableShardingAlgorithm"/>
<sharding:standard-strategy id="applyDeviceTableShardingStrategy" sharding-column="createtime" range-algorithm-class="com.dsp.common.algorithm.RangleTableShardingAlgorithm" precise-algorithm-class="com.dsp.common.algorithm.PreciseTableShardingAlgorithm"/>
<sharding:data-source id="shardingDataSource">
<sharding:sharding-rule data-source-names="dataSource">
<sharding:table-rules>
<sharding:table-rule logic-table="apply_order" actual-data-nodes="dataSource.apply_order_2017${1..54}" table-strategy-ref="orderTableShardingStrategy" />
<sharding:table-rule logic-table="apply_device" actual-data-nodes="dataSource.apply_device_2017${1..54}" table-strategy-ref="applyDeviceTableShardingStrategy" />
</sharding:table-rules>
<sharding:binding-table-rules>
<sharding:binding-table-rule logic-tables="apply_order,apply_device"/>
</sharding:binding-table-rules>
</sharding:sharding-rule>
<sharding:props>
<prop key="sql.show">true</prop>
</sharding:props>
</sharding:data-source>
複製程式碼<!--mybatis部分配置-->
<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
<property name="dataSource" ref="shardingDataSource"/>
<property name="mapperLocations" value="classpath*:mapper/*.xml"/>
</bean>
<bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
<property name="dataSource" ref="shardingDataSource" />
</bean>
複製程式碼/**
* 精確的查詢，用於=，in等精確查詢
*/
public final class PreciseTableShardingAlgorithm implements PreciseShardingAlgorithm<Date> {
Logger logger = LoggerFactory.getLogger(PreciseTableShardingAlgorithm.class);
public String doSharding(final Collection<String> availableTargetNames, final PreciseShardingValue<Date> shardingValue) {
SimpleDateFormat sdf = new SimpleDateFormat("yyyy");
String format = sdf.format(shardingValue.getValue());
Integer week = getWeek(shardingValue.getValue());
String tableName = format+week;
for (String each : availableTargetNames) {
if (each.endsWith(tableName)) {
logger.info("精確查詢返回的表名："+each);
return each;
}
}
throw new UnsupportedOperationException();
}
/**
* 獲取當前日期的週數
* @param date
* @return
*/
private Integer getWeek(Date date){
Calendar instance = Calendar.getInstance();
instance.setFirstDayOfWeek(Calendar.MONDAY);
instance.setTime(date);
int weekNum = instance.get(Calendar.WEEK_OF_YEAR);
//如果為12月份
if((instance.get(Calendar.MONTH)+1)==12){
instance.add(Calendar.WEEK_OF_YEAR,-1);
int weekNumBefore = instance.get(Calendar.WEEK_OF_YEAR);
if(weekNum<weekNumBefore){
return weekNumBefore+1;
}
}
return weekNum;
}
}
複製程式碼/**
* 範圍查詢分表
*/
public final class RangleTableShardingAlgorithm implements RangeShardingAlgorithm<Date> {
Logger logger = LoggerFactory.getLogger(RangleTableShardingAlgorithm.class);
public Collection<String> doSharding(Collection<String> collection, RangeShardingValue<Date> rangeShardingValue) {
Collection<String> result = new LinkedHashSet<String>(collection.size());
Range<Date> valueRange = rangeShardingValue.getValueRange();
Date beginDate = valueRange.lowerEndpoint();
Date endDate = valueRange.upperEndpoint();
SimpleDateFormat sdfYear = new SimpleDateFormat("yyyy");
Integer beginDateYear = Integer.valueOf(sdfYear.format(beginDate));
Integer endDateYear = Integer.valueOf(sdfYear.format(endDate));
Integer beginWeek = getWeek(beginDate);
Integer endWeek = getWeek(endDate);
//年份相等
if(beginDateYear.equals(endDateYear)){
//比較月份
for (int i = beginWeek;i<=endWeek;i++){
for (String tableName :collection){
if(tableName.endsWith(beginDateYear+""+i)){
result.add(tableName);
}
}
}
}else{
//年份不等,遍歷年份
for (int i = beginDateYear;i<=endDateYear;i++){
//遍歷月份
if(i==endDateYear){
for (int j = beginWeek;j<=endWeek;j++){
for (String tableName :collection){
if(tableName.endsWith(i+""+j)){
result.add(tableName);
}
}
}
}else{
for (int j = beginWeek;j<=12;j++){
for (String tableName :collection){
if(tableName.endsWith(i+""+j)){
result.add(tableName);
}
}
}
}
}
}
logger.info("範圍查詢返回的表名："+result);
return result;
}
/**
* 獲取當前日期的週數
* @param date
* @return
*/
private Integer getWeek(Date date){
Calendar instance = Calendar.getInstance();
instance.setFirstDayOfWeek(Calendar.MONDAY);
instance.setTime(date);
int weekNum = instance.get(Calendar.WEEK_OF_YEAR);
//如果為12月份
if((instance.get(Calendar.MONTH)+1)==12){
instance.add(Calendar.WEEK_OF_YEAR,-1);
int weekNumBefore = instance.get(Calendar.WEEK_OF_YEAR);
if(weekNum<weekNumBefore){
return weekNumBefore+1;
}
}
return weekNum;
}
}
複製程式碼需要注意的地方 使用
<context:property-placeholder location="classpath*:application_local.properties" ignore-unresolvable="true"/>
複製程式碼不要使用
<bean
class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
<property name="locations">
<list>
<value>classpath*:application_local.properties</value>
</list>
</property>
</bean>
複製程式碼不要將dataSource和shardingjdbc的規則放在一起，因為dataSource裡面的${}和shardingjdbc裡面的${}解析的時候用的不是一個工具。