Spring Boot 結合 Sharding-Jdbc做分庫分表示例

對於一些大系統，數據庫數據量很大，業務量特別大的時候，而我們的數據庫及表的對於大數據量的時候，處理的性能就不容樂觀，這個時候我們就需要對我們的數據和表做分庫分表處理了。一般分庫分表都會採用數據庫中間件，像Mycat這種中間件，它幫我們做數據源，路由映射控制。而今天介紹的Sharding-Jdbc是一個java的應用程序包，所以兩者一比較，中間件肯定更加重量級一些。

添加POM依賴：

<project> xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
 <modelversion>4.0.0/<modelversion>
 <groupid>com.chown.pine.sharding/<groupid>
 <artifactid>chown-sharding-jdbc/<artifactid>
 <version>1.0/<version>
 <name>chown-sharding-jdbc/<name>
 http://www.example.com
 <properties>
 <project.build.sourceencoding>UTF-8/<project.build.sourceencoding>
 <maven.compiler.source>1.8/<maven.compiler.source>
 <maven.compiler.target>1.8/<maven.compiler.target>
 <mybatis.version>3.4.6/<mybatis.version>
 <mybatis.spring.version>1.3.2/<mybatis.spring.version>
 /<properties>
 <parent>
 <groupid>org.springframework.boot/<groupid>
 <artifactid>spring-boot-starter-parent/<artifactid>
 <version>2.0.1.RELEASE/<version>
 <relativepath> 
 /<parent>
 <dependencies>
 <dependency>
 <groupid>org.springframework.boot/<groupid>
 <artifactid>spring-boot-starter-web/<artifactid>
 <exclusions>
 <exclusion>
 <artifactid>spring-boot-starter-logging/<artifactid>
 <groupid>org.springframework.boot/<groupid>
 /<exclusion>
 /<exclusions>
 /<dependency>
 <dependency>
 <groupid>com.dangdang/<groupid>
 <artifactid>sharding-jdbc-core/<artifactid>
 <version>1.5.4.1/<version>
 /<dependency>
 <dependency>
 <groupid>com.alibaba/<groupid>
 <artifactid>druid/<artifactid>
 <version>1.1.12/<version> 

 /<dependency>
 <dependency>
 <groupid>mysql/<groupid>
 <artifactid>mysql-connector-java/<artifactid>
 /<dependency>
 <dependency>
 <groupid>org.springframework.boot/<groupid>
 <artifactid>spring-boot-starter-jdbc/<artifactid>
 /<dependency>
 <dependency>
 <groupid>org.springframework.boot/<groupid>
 <artifactid>spring-boot-starter-test/<artifactid>
 <scope>test/<scope>
 /<dependency>
 <dependency>
 <groupid>org.projectlombok/<groupid>
 <artifactid>lombok/<artifactid>
 /<dependency>
 <dependency>
 <groupid>org.mybatis/<groupid>
 <artifactid>mybatis/<artifactid>
 <version>${mybatis.version}/<version>
 /<dependency>
 <dependency>
 <groupid>org.mybatis/<groupid>
 <artifactid>mybatis-spring/<artifactid>
 <version>${mybatis.spring.version}/<version>
 /<dependency>
 /<dependencies>
/<project>

YAML配置

# 公鑰
public:
 key: MFwwDQYJKoZIhvcNAQEBBQADSwAwSAJBAJZtfAfaggxBRhuaJeE9KMSIqPpAxbUQRTAjzoSaVqt/8wqd3crYeS2Ebxt2fWHDOQSTpPZfl1+whyMMEtq5vcMCAwEAAQ==
spring:
 datasource:
 # 數據庫數據源 1
 ds0:
 username: test01
 # ;config.decrypt=true;config.decrypt.key=${public.key} U4A5Uv3UHPLtjk1dgw1/+WNP3pIlc8lBDYswjwQ2zS+3NKij1NsFzggxKudLuuerc2Wcgnj4P60VrqQzh7VISQ==
 password: U4A5Uv3UHPLtjk1dgw1/+WNP3pIlc8lBDYswjwQ2zS+3NKij1NsFzggxKudLuuerc2Wcgnj4P60VrqQzh7VISQ==
 url: jdbc:mysql://192.168.0.11:21000/t_db_1?autoReconnect=true&useSSL=false&useUnicode=true&characterEncoding=utf-8
 type: com.alibaba.druid.pool.DruidDataSource
 connection-properties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000;config.decrypt=true;config.decrypt.key=${public.key}
 # 數據庫數據源 2
 ds1: 

 username: test02
 password: U4A5Uv3UHPLtjk1dgw1/+WNP3pIlc8lBDYswjwQ2zS+3NKij1NsFzggxKudLuuerc2Wcgnj4P60VrqQzh7VISQ==
 url: jdbc:mysql://192.168.0.11:21000/t_db_2?autoReconnect=true&useSSL=false&useUnicode=true&characterEncoding=utf-8
 type: com.alibaba.druid.pool.DruidDataSource
 connection-properties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000;config.decrypt=true;config.decrypt.key=${public.key}

Druid數據庫連接屬性配置（datasource.properties）：

#初始化時建立物理連接的個數。初始化發生在顯示調用init方法，或者第一次getConnection時
druid.initialSize=1
#最小連接池數量
druid.minIdle=1
#最大連接池數量 default : 8
druid.maxActive=50
#申請連接時執行validationQuery檢測連接是否有效，做了這個配置會降低性能。
druid.testOnBorrow=true
#獲取連接時最大等待時間，單位毫秒
druid.maxWait=60000
# 有兩個含義：
# 1) Destroy線程會檢測連接的間隔時間
# 2) testWhileIdle的判斷依據，詳細看testWhileIdle屬性的說明
druid.timeBetweenEvictionRunsMillis=60000
druid.minEvictableIdleTimeMillis=300000
#用來檢測連接是否有效的sql，要求是一個查詢語句。
druid.validationQuery=SELECT 1 FROM DUAL
#建議配置為true，不影響性能，並且保證安全性。申請連接的時候檢測，如果空閒時間大於timeBetweenEvictionRunsMillis 執行validationQuery檢測連接是否有效。
druid.testWhileIdle=true
#歸還連接時執行validationQuery檢測連接是否有效，做了這個配置會降低性能 

druid.testOnReturn=false
#是否緩存preparedStatement，也就是PSCache。PSCache對支持遊標的數據庫性能提升巨大，比如說oracle。在mysql5.5以下的版本中沒有PSCache功能，建議關閉掉。
druid.poolPreparedStatements=false
druid.maxPoolPreparedStatementPerConnectionSize=20
#屬性類型是字符串，通過別名的方式配置擴展插件，常用的插件有：
# 監控統計用的filter:stat
# 日誌用的filter:log4j
# 防禦sql注入的filter:wall
druid.filters=stat,wall,config

數據源DataSource配置：

package com.chown.pine.sharding.config;
import com.alibaba.druid.pool.DruidDataSource;
import com.dangdang.ddframe.rdb.sharding.api.ShardingDataSourceFactory;
import com.dangdang.ddframe.rdb.sharding.api.rule.BindingTableRule;
import com.dangdang.ddframe.rdb.sharding.api.rule.DataSourceRule;
import com.dangdang.ddframe.rdb.sharding.api.rule.ShardingRule;
import com.dangdang.ddframe.rdb.sharding.api.rule.TableRule;
import com.dangdang.ddframe.rdb.sharding.api.strategy.database.DatabaseShardingStrategy;
import com.dangdang.ddframe.rdb.sharding.api.strategy.table.TableShardingStrategy;
import lombok.Getter;
import lombok.Setter;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.PropertySource;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.core.io.support.ResourcePatternResolver;
import javax.sql.DataSource;
import java.sql.SQLException;
import java.util.*;
/**
 * 
 *
 * @author zping 

 * @version 2018/11/22 0022
 * @see [相關類/方法]
 * @since [產品/模塊版本]
 */
@Getter
@Setter
@Configuration
@PropertySource (value = { "classpath:/datasource.properties" }, encoding = "UTF-8")
@ConfigurationProperties (prefix = "druid")
public class DataSourceConfig
{
\t/**
\t * 實體類包路徑
\t */
\tprivate static final String ENTITY_SCAN_PATH = "com.chown.pine.sharding.database.entity";
\t/**
\t * mybatis配置文件路徑
\t */
\tprivate static final String MYBATIS_CONFIG_PATH = "classpath:/mybatis-config.xml";
\t/**
\t * mybatis映射文件路徑
\t */
\tprivate static final String MAPPER_PATH = "classpath:/mapper/*.xml";
\t/**
\t * 心跳檢測SQL
\t */
\tprivate String validationQuery;
\tprivate Boolean testWhileIdle;
\tprivate Boolean testOnBorrow;
\tprivate Boolean testOnReturn;
\tprivate Boolean poolPreparedStatements;
\t/**
\t * 最大等待數
\t */
\tprivate Integer maxWait;
\t/**
\t * 最大活躍數
\t */
\tprivate Integer maxActive;
\t/**
\t * 最小空閒數
\t */
\tprivate Integer minIdle;
\t/**
\t * 初始化連接數 

\t */
\tprivate Integer initialSize;
\t/**
\t * 配置過濾器(注意：如果使用了Druid的密碼加密，一定要配置過濾器config，要不然他無法解密數據庫密碼)
\t */
\tprivate String filters;
\tprivate Integer timeBetweenEvictionRunsMillis;
\tprivate Integer minEvictableIdleTimeMillis;
\tprivate Integer maxPoolPreparedStatementPerConnectionSize;
\t/**
\t * 配置數據源0，數據源的名稱最好要有一定的規則，方便配置分庫的計算規則
\t *
\t * @return
\t */
\t@Bean (name = "dataSource0")
\t@ConfigurationProperties (prefix = "spring.datasource.ds0")
\tpublic DruidDataSource dataSource0 ()
\t{
\t\tDruidDataSource dataSource0 = new DruidDataSource ();
\t\tdataSource0.setMinIdle (minIdle);
\t\tdataSource0.setMaxActive (maxActive);
\t\tdataSource0.setInitialSize (initialSize);
\t\tdataSource0.setMinEvictableIdleTimeMillis (minEvictableIdleTimeMillis);
\t\tdataSource0.setTimeBetweenConnectErrorMillis (timeBetweenEvictionRunsMillis);
\t\tdataSource0.setMaxPoolPreparedStatementPerConnectionSize (maxPoolPreparedStatementPerConnectionSize);
\t\tdataSource0.setMaxWait (maxWait);
\t\tdataSource0.setTestWhileIdle (testWhileIdle);
\t\tdataSource0.setTestOnReturn (testOnReturn);
\t\tdataSource0.setTestOnBorrow (testOnBorrow);
\t\tdataSource0.setValidationQuery (validationQuery);
\t\tdataSource0.setPoolPreparedStatements (poolPreparedStatements);
\t\ttry
\t\t{
\t\t\tdataSource0.setFilters (filters);
\t\t}
\t\tcatch (SQLException e)
\t\t{
\t\t\te.printStackTrace ();
\t\t}
\t\treturn dataSource0;
\t}
\t/**
\t * 配置數據源1，數據源的名稱最好要有一定的規則，方便配置分庫的計算規則 

\t *
\t * @return
\t */
\t@Bean (name = "dataSource1")
\t@ConfigurationProperties (prefix = "spring.datasource.ds1")
\tpublic DruidDataSource dataSource1 ()
\t{
\t\tDruidDataSource dataSource1 = new DruidDataSource ();
\t\tdataSource1.setInitialSize (initialSize);
\t\tdataSource1.setMaxActive (maxActive);
\t\tdataSource1.setMinIdle (minIdle);
\t\tdataSource1.setTimeBetweenConnectErrorMillis (timeBetweenEvictionRunsMillis);
\t\tdataSource1.setMinEvictableIdleTimeMillis (minEvictableIdleTimeMillis);
\t\tdataSource1.setTestOnBorrow (testOnBorrow);
\t\tdataSource1.setTestOnReturn (testOnReturn);
\t\tdataSource1.setTestWhileIdle (testWhileIdle);
\t\tdataSource1.setMaxWait (maxWait);
\t\tdataSource1.setPoolPreparedStatements (poolPreparedStatements);
\t\tdataSource1.setValidationQuery (validationQuery);
\t\ttry
\t\t{
\t\t\tdataSource1.setFilters (filters);
\t\t}
\t\tcatch (SQLException e)
\t\t{
\t\t\te.printStackTrace ();
\t\t}
\t\treturn dataSource1;
\t}
\t/**
\t * 配置數據源規則，即將多個數據源交給sharding-jdbc管理，並且可以設置默認的數據源，
\t * 當表沒有配置分庫規則時會使用默認的數據源
\t *
\t * @return
\t */
\t@Bean
\tpublic DataSourceRule dataSourceRule (@Qualifier ("dataSource0") DruidDataSource dataSource0,
\t\t\t@Qualifier ("dataSource1") DruidDataSource dataSource1)
\t{
\t\t//設置分庫映射
\t\tMap<string> dataSourceMap = new HashMap<> ();
\t\tdataSourceMap.put ("dataSource0", dataSource0);
\t\tdataSourceMap.put ("dataSource1", dataSource1);
\t\t//設置默認庫，兩個庫以上時必須設置默認庫。默認庫的數據源名稱必須是dataSourceMap的key之一 

\t\treturn new DataSourceRule (dataSourceMap, "dataSource0");
\t}
\t/**
\t * 配置數據源策略和表策略，具體策略需要自己實現
\t *
\t * @param dataSourceRule
\t * @return
\t */
\t@Bean
\tpublic ShardingRule configShardingRule (DataSourceRule dataSourceRule)
\t{
\t\t//配置分庫分表策略
\t\tTableRule orderTableRule = TableRule.builder ("t_order")
\t\t\t\t//設置物理表
\t\t\t\t.actualTables (Arrays.asList ("t_order_0", "t_order_1"))
\t\t\t\t//設置分表策略
\t\t\t\t.tableShardingStrategy (new TableShardingStrategy ("order_id", new ModuloTableShardingAlgorithm ()))
\t\t\t\t//設置數據源
\t\t\t\t.dataSourceRule (dataSourceRule).build ();
\t\t//綁定表策略，在查詢時會使用主表策略計算路由的數據源，因此需要約定綁定表策略的表的規則需要一致，可以一定程度提高效率
\t\tList<bindingtablerule> bindingTableRules = new ArrayList<> ();
\t\tbindingTableRules.add (new BindingTableRule (Arrays.asList (orderTableRule)));
\t\t//構建分庫分表策略規則
\t\treturn ShardingRule.builder ()
\t\t\t\t//設置數據源策略規則
\t\t\t\t.dataSourceRule (dataSourceRule)
\t\t\t\t//設置分表策略規則
\t\t\t\t.tableRules (Arrays.asList (orderTableRule))
\t\t\t\t//綁定分表策略
\t\t\t\t.bindingTableRules (bindingTableRules)
\t\t\t\t//設置分庫策略
\t\t\t\t.databaseShardingStrategy (
\t\t\t\t\t\tnew DatabaseShardingStrategy ("user_id", new ModuloDatabaseShardingAlgorithm ()))
\t\t\t\t//分表策略
\t\t\t\t.tableShardingStrategy (new TableShardingStrategy ("order_id", new ModuloTableShardingAlgorithm ()))
\t\t\t\t.build (); 

\t}
\t/**
\t * 創建sharding-jdbc的數據源DataSource, MyBatisAutoConfiguration會使用此數據源
\t *
\t * @param shardingRule 分庫分表策略配置
\t * @return
\t * @throws SQLException
\t */
\t@Bean (name = "dataSource")
\tpublic DataSource shardingDataSource (ShardingRule shardingRule) throws SQLException
\t{
\t\treturn ShardingDataSourceFactory.createDataSource (shardingRule);
\t}
\t/**
\t * 創建SQLSessionFactory工廠對象
\t *
\t * @param dataSource
\t * @return
\t */
\t@Bean (name = "sqlSessionFactory")
\tpublic SqlSessionFactory sqlSessionFactory (DataSource dataSource)
\t{
\t\tSqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean ();
\t\tsqlSessionFactoryBean.setDataSource (dataSource);
\t\t/*掃描DOMAIN包，可以使用別名*/
\t\tsqlSessionFactoryBean.setTypeAliasesPackage (ENTITY_SCAN_PATH);
\t\tResourcePatternResolver resolver = new PathMatchingResourcePatternResolver ();
\t\ttry
\t\t{
\t\t\t/*掃描mybatis配置文件*/
\t\t\tsqlSessionFactoryBean.setConfigLocation (resolver.getResource (MYBATIS_CONFIG_PATH));
\t\t\tsqlSessionFactoryBean.setMapperLocations (resolver.getResources (MAPPER_PATH));
\t\t\treturn sqlSessionFactoryBean.getObject ();
\t\t}
\t\tcatch (Exception e)
\t\t{
\t\t\te.printStackTrace ();
\t\t\treturn null;
\t\t}
\t}
\t/**
\t * 創建SqlSessionTemplate模板對象
\t *
\t * @param sqlSessionFactory
\t * @return
\t */ 

\t@Bean
\tpublic SqlSessionTemplate sqlSessionTemplate (SqlSessionFactory sqlSessionFactory)
\t{
\t\treturn new SqlSessionTemplate (sqlSessionFactory);
\t}
}
/<bindingtablerule>/<string>

MyBatias配置掃描Mapper：

實現Sharding-Jdbc簡單分庫策略配置：

package com.chown.pine.sharding.config;
import com.dangdang.ddframe.rdb.sharding.api.ShardingValue;
import com.dangdang.ddframe.rdb.sharding.api.strategy.database.SingleKeyDatabaseShardingAlgorithm;
import com.google.common.collect.Range;
import java.util.Collection;
import java.util.LinkedHashSet;
/**
 * 
 *
 * @author zping
 * @version 2018/11/22 0022
 * @see [相關類/方法]
 * @since [產品/模塊版本]
 */
public class ModuloDatabaseShardingAlgorithm implements SingleKeyDatabaseShardingAlgorithm<long>
{
\t/**
\t * 鍵值分庫策略處理
\t *
\t * @param databaseNames 數據庫名稱集合
\t * @param shardingValue 分庫策略配置字段值(如：user_id)
\t * @return
\t */
\t@Override
\tpublic String doEqualSharding (Collection<string> databaseNames, ShardingValue<long> shardingValue)
\t{
\t\tfor (String databaseName : databaseNames)
\t\t{
\t\t\tif (databaseName.endsWith (shardingValue.getValue () % 2 + ""))
\t\t\t{
\t\t\t\treturn databaseName;
\t\t\t}
\t\t}
\t\tthrow new IllegalArgumentException ();
\t}
\t/**
\t * In 查詢處理策略
\t *
\t * @param databaseNames
\t * @param shardingValue
\t * @return 

\t */
\t@Override
\tpublic Collection<string> doInSharding (Collection<string> databaseNames, ShardingValue<long> shardingValue)
\t{
\t\tCollection<string> result = new LinkedHashSet<> (databaseNames.size ());
\t\tfor (Long tablesShardingValue : shardingValue.getValues ())
\t\t{
\t\t\tfor (String tableName : databaseNames)
\t\t\t{
\t\t\t\tif (tableName.endsWith (tablesShardingValue % 2 + ""))
\t\t\t\t{
\t\t\t\t\tresult.add (tableName);
\t\t\t\t}
\t\t\t}
\t\t}
\t\treturn result;
\t}
\t/**
\t * Between方式查詢處理策略
\t *
\t * @param databaseNames 數據庫集合
\t * @param shardingValue 分庫鍵值邏輯值集合
\t * @return
\t */
\t@Override
\tpublic Collection<string> doBetweenSharding (Collection<string> databaseNames, ShardingValue<long> shardingValue)
\t{
\t\tCollection<string> result = new LinkedHashSet<> (databaseNames.size ());
\t\tRange<long> range = (Range<long>) shardingValue.getValueRange ();
\t\tfor (Long i = range.lowerEndpoint (); i <= range.upperEndpoint (); i++)
\t\t{
\t\t\tfor (String databaseName : databaseNames)
\t\t\t{
\t\t\t\tif (databaseName.endsWith (i % 2 + ""))
\t\t\t\t{
\t\t\t\t\tresult.add (databaseName);
\t\t\t\t}
\t\t\t}
\t\t}
\t\treturn result;
\t}
}
/<long>/<long>/<string>/<long>/<string>/<string>/<string>/<long>/<string>/<string>/<long>/<string>/<long>

配置Sharding-Jdbc分表策略：

package com.chown.pine.sharding.config;
import com.dangdang.ddframe.rdb.sharding.api.ShardingValue; 

import com.dangdang.ddframe.rdb.sharding.api.strategy.table.SingleKeyTableShardingAlgorithm;
import com.google.common.collect.Range;
import java.util.Collection;
import java.util.LinkedHashSet;
/**
 * 
 *
 * @author zping
 * @version 2018/11/22 0022
 * @see [相關類/方法]
 * @since [產品/模塊版本]
 */
public class ModuloTableShardingAlgorithm implements SingleKeyTableShardingAlgorithm<long>
{
\t@Override
\tpublic String doEqualSharding (Collection<string> tableNames, ShardingValue<long> shardingValue)
\t{
\t\tfor (String tableName : tableNames)
\t\t{
\t\t\tif (tableName.endsWith (shardingValue.getValue () % 2 + ""))
\t\t\t{
\t\t\t\treturn tableName;
\t\t\t}
\t\t}
\t\tthrow new IllegalArgumentException ();
\t}
\t@Override
\tpublic Collection<string> doInSharding (Collection<string> tableNames, ShardingValue<long> shardingValue)
\t{
\t\tCollection<string> result = new LinkedHashSet<> (tableNames.size ());
\t\tfor (Long value : shardingValue.getValues ())
\t\t{
\t\t\tfor (String tableName : tableNames)
\t\t\t{
\t\t\t\tif (tableName.endsWith (value % 2 + ""))
\t\t\t\t{
\t\t\t\t\tresult.add (tableName);
\t\t\t\t}
\t\t\t}
\t\t}
\t\treturn result;
\t}
\t@Override
\tpublic Collection<string> doBetweenSharding (Collection<string> tableNames, ShardingValue<long> shardingValue)
\t{
\t\tCollection<string> result = new LinkedHashSet<> (tableNames.size ());
\t\tRange<long> range = (Range<long>) shardingValue.getValueRange ();
\t\tfor (Long i = range.lowerEndpoint (); i <= range.upperEndpoint (); i++)
\t\t{ 

\t\t\tfor (String each : tableNames)
\t\t\t{
\t\t\t\tif (each.endsWith (i % 2 + ""))
\t\t\t\t{
\t\t\t\t\tresult.add (each);
\t\t\t\t}
\t\t\t}
\t\t}
\t\treturn result;
\t}
}
/<long>/<long>/<string>/<long>/<string>/<string>/<string>/<long>/<string>/<string>/<long>/<string>/<long>

Mapper實現：

package com.chown.pine.sharding.database.mapper;
import com.chown.pine.sharding.database.entity.Order;
import java.util.List;
/**
 *  
 *
 * @author zping
 * @version 2018/11/23 0023
 * @see [相關類/方法]
 * @since [產品/模塊版本]
 */
public interface OrderMapper
{
\t/**
\t * 創建Order對象
\t *
\t * @param order
\t * @return
\t */
\tint createOrder (final Order order);
\t/**
\t * 查詢所有數據
\t *
\t * @return
\t */
\tList<order> queryAll ();
}
/<order>

MyBatis編寫XML文件：

 

br> "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper>
 
 <insert>
 INSERT INTO t_order
 (order_id, user_id, userName, passWord, user_sex)
 VALUES
 (#{order_id}, #{user_id}, #{userName}, #{passWord}, #{userSex})
 /<insert>
 
 <select>
 select * from t_order;
 /<select>
/<mapper>

單元測試編寫：

package com.chown.pine.sharding;
import com.chown.pine.sharding.boot.BootstrapApplication;
import com.chown.pine.sharding.database.entity.Order;
import com.chown.pine.sharding.database.mapper.OrderMapper;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;
import java.util.List;
/**
 *  
 *
 * @author zping
 * @version 2018/11/23 0023
 * @see [相關類/方法]
 * @since [產品/模塊版本]
 */
@RunWith (SpringRunner.class)
@SpringBootTest (classes = BootstrapApplication.class)
public class BootstrapApplicationTest
{
\t@Autowired
\tprotected OrderMapper orderMapper;
\t/**
\t * 程序啟動測試類
\t */ 

\t@Test
\tpublic void contentLoad ()
\t{
\t\t/*Order order = new Order ();
\t\torder.setOrder_id (1000003l);
\t\torder.setUser_id (1000003l);
\t\torder.setNick_name ("zping");
\t\torder.setPassWord ("12334");
\t\torder.setUserName ("Pine Chown");
\t\torder.setUserSex ("MAN");
\t\torderMapper.createOrder (order);*/
\t\tList<order> orders = orderMapper.queryAll ();
\t\tSystem.out.println (orders);
\t}
}
/<order>

數據庫創建SQL：

/*
Navicat MySQL Data Transfer
Source Server : test01
Source Server Version : 50636
Source Host : 192.168.0.11:21000
Source Database : t_db_1
Target Server Type : MYSQL
Target Server Version : 50636
File Encoding : 65001
Date: 2018-12-06 16:29:55
*/
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for t_order_0
-- ----------------------------
DROP TABLE IF EXISTS `t_order_0`;
CREATE TABLE `t_order_0` (
 `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主鍵id',
 `order_id` varchar(32) DEFAULT NULL COMMENT '順序編號',
 `user_id` varchar(32) DEFAULT NULL COMMENT '用戶編號',
 `userName` varchar(32) DEFAULT NULL COMMENT '用戶名',
 `passWord` varchar(32) DEFAULT NULL COMMENT '密碼',
 `user_sex` varchar(32) DEFAULT NULL,
 `nick_name` varchar(32) DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=29 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Table structure for t_order_1
-- ---------------------------- 

DROP TABLE IF EXISTS `t_order_1`;
CREATE TABLE `t_order_1` (
 `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主鍵id',
 `order_id` varchar(32) DEFAULT NULL COMMENT '順序編號',
 `user_id` varchar(32) DEFAULT NULL COMMENT '用戶編號',
 `userName` varchar(32) DEFAULT NULL COMMENT '用戶名',
 `passWord` varchar(32) DEFAULT NULL COMMENT '密碼',
 `user_sex` varchar(32) DEFAULT NULL,
 `nick_name` varchar(32) DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=29 DEFAULT CHARSET=utf8;
/*
Navicat MySQL Data Transfer
Source Server : test02
Source Server Version : 50636
Source Host : 192.168.0.11:21000
Source Database : t_db_2
Target Server Type : MYSQL
Target Server Version : 50636
File Encoding : 65001
Date: 2018-12-06 16:30:02
*/
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for t_order_0
-- ----------------------------
DROP TABLE IF EXISTS `t_order_0`;
CREATE TABLE `t_order_0` (
 `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主鍵id',
 `order_id` varchar(32) DEFAULT NULL COMMENT '順序編號',
 `user_id` varchar(32) DEFAULT NULL COMMENT '用戶編號',
 `userName` varchar(32) DEFAULT NULL COMMENT '用戶名',
 `passWord` varchar(32) DEFAULT NULL COMMENT '密碼',
 `user_sex` varchar(32) DEFAULT NULL,
 `nick_name` varchar(32) DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=29 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Table structure for t_order_1
-- ----------------------------
DROP TABLE IF EXISTS `t_order_1`;
CREATE TABLE `t_order_1` (
 `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主鍵id',
 `order_id` varchar(32) DEFAULT NULL COMMENT '順序編號',
 `user_id` varchar(32) DEFAULT NULL COMMENT '用戶編號',
 `userName` varchar(32) DEFAULT NULL COMMENT '用戶名', 

 `passWord` varchar(32) DEFAULT NULL COMMENT '密碼',
 `user_sex` varchar(32) DEFAULT NULL,
 `nick_name` varchar(32) DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=29 DEFAULT CHARSET=utf8;