ziliangzeng edited a comment on issue #14670:
URL:
https://github.com/apache/shardingsphere/issues/14670#issuecomment-1009523229
```
dataSources:
ds_0:
dataSourceClassName: com.zaxxer.hikari.HikariDataSource
driverClassName: com.mysql.cj.jdbc.Driver
jdbcUrl:
jdbc:mysql://localhost:3306/ds0?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=UTF-8&allowPublicKeyRetrieval=true
username: root
password: 123456
connectionTimeout: 30000
idleTimeout: 30000
maximumPoolSize: 10
maxLifetime: 1800000
ds_1:
dataSourceClassName: com.zaxxer.hikari.HikariDataSource
driverClassName: com.mysql.cj.jdbc.Driver
jdbcUrl:
jdbc:mysql://localhost:3306/ds1?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=UTF-8&allowPublicKeyRetrieval=true
username: root
password: 123456
connectionTimeout: 30000
idleTimeout: 30000
maximumPoolSize: 10
maxLifetime: 1800000
rules:
- !SHARDING
tables:
cf_api_parameter:
actualDataNodes: ds_${0..1}.cf_api_parameter
package_service_test_instance:
actualDataNodes: ds_0.package_service_test_instance
databaseStrategy:
none:
broadcastTables:
[
t_order0
]
defaultDatabaseStrategy:
standard:
shardingColumn: order_id
shardingAlgorithmName: database_inline
defaultTableStrategy:
none:
shardingAlgorithms:
database_inline:
type: INLINE
props:
algorithm-expression: ds_${order_id % 2}
keyGenerators:
snowflake:
type: SNOWFLAKE
props:
worker-id: 123
props:
sql-show: true
```
```
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
DROP TABLE IF EXISTS `t_order0`;
CREATE TABLE `t_order0` (
`id` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
`blobtext` longblob NULL,
`name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL
DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci
ROW_FORMAT = Dynamic;
SET FOREIGN_KEY_CHECKS = 1;
```
```
InputStream in =
getClass().getClassLoader().getResourceAsStream("sharding.yaml");
String sql = "INSERT INTO t_order0 (id, blobtext, name) VALUES (?,
?, ?)";
confJdbcTemplate.update(sql, new Object[]{
1, in, "test"});
```
```
`DROP TABLE IF EXISTS `cf_api_parameter`;
CREATE TABLE `cf_api_parameter` (
`ID` bigint NOT NULL COMMENT '标识',
`API_ID` bigint NULL DEFAULT NULL COMMENT 'API标识',
`CODE` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT
NULL COMMENT '编码',
`NAME` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT
NULL COMMENT '名称',
`TYPE` varchar(8) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT
NULL COMMENT '类型 API-IN/API-OUT',
`IS_MUST` tinyint NOT NULL DEFAULT 0 COMMENT '是否必填 1-是，0-否',
`RULE_CODE` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL
DEFAULT NULL COMMENT '校验策略编码 校验策略编码',
`RULE_BIND` varchar(3072) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL
DEFAULT NULL COMMENT '校验策略参数 校验策略参数',
`RULE_EXP` varchar(1024) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL
DEFAULT NULL COMMENT '约束策略表达式',
`RELA_PROP_CODE` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin
NULL DEFAULT NULL COMMENT '关联属性编码',
`STATE` tinyint NOT NULL COMMENT '状态 1-有效，0-无效',
`REMARKS` varchar(1024) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL
DEFAULT NULL COMMENT '说明备注',
`CREATE_TIME` datetime NULL DEFAULT NULL COMMENT '创建时间',
`PARTITION_CODE` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin
NULL DEFAULT NULL COMMENT '分区编码',
`RELA_TYPE` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL
DEFAULT NULL,
`DATA_TYPE` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL
DEFAULT '-1' COMMENT '数据类型',
`OLD_VALUE_OPTION` int NULL DEFAULT NULL COMMENT '新旧值传递规则',
`PATH_CODE` varchar(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT
NULL DEFAULT '',
PRIMARY KEY (`ID`) USING BTREE,
INDEX `IDX_CAP_TYPE`(`TYPE`) USING BTREE,
INDEX `IDX_CAP_CODE`(`CODE`) USING BTREE,
INDEX `IDX_CAP_API_ID`(`API_ID`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin COMMENT =
'API参数表' ROW_FORMAT = Dynamic;
SET FOREIGN_KEY_CHECKS = 1;
```
```
DROP TABLE IF EXISTS `package_service_test_instance`;
CREATE TABLE `package_service_test_instance` (
`ID` bigint NOT NULL COMMENT '主键ID',
`CUST_ORDER_CODE` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin
NOT NULL COMMENT '客户订单编码/测试单号',
`SCENE_ID` bigint NOT NULL COMMENT '关联业务测试场景ID',
`CUST_ORDER_MESSAGE` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin
NOT NULL COMMENT '客户订单报文',
`CREATE_DATE` datetime NOT NULL COMMENT '创建时间',
`UPDATE_DATE` datetime NOT NULL COMMENT '更新时间',
`ACC_NBR` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL
COMMENT '业务号码',
`IS_PASS` tinyint NOT NULL COMMENT '是否通过',
`DESC` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL
COMMENT '结果描述',
`INFORM_ID` bigint NOT NULL COMMENT '关联业务包编码',
`OPERUSER` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL
DEFAULT NULL COMMENT '操作用户',
PRIMARY KEY (`ID`) USING BTREE,
INDEX `IDX_PSTI_INFORMID`(`INFORM_ID`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin COMMENT =
'业务测试单实例表，单片表' ROW_FORMAT = Dynamic;
SET FOREIGN_KEY_CHECKS = 1;
```
```
@Configuration
public class confige {
@Bean(name="confJdbcTemplate")
public JdbcTemplate jdbcTemplateToTelDB (){
JdbcTemplate jdbcTemplate = null;
File yamlFile = new File("src/main/resources/sharding.yaml");
DataSource dataSource = null;
try {
dataSource =
YamlShardingSphereDataSourceFactory.createDataSource(yamlFile);
jdbcTemplate = new JdbcTemplate(dataSource);
} catch (SQLException throwables) {
} catch (IOException e) {
e.printStackTrace();
}
return jdbcTemplate ;
}
}
```
@strongduanmu
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
