This is an automated email from the ASF dual-hosted git repository.
jianglongtao pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/shardingsphere.git
The following commit(s) were added to refs/heads/master by this push:
new fcbd4d8adb9 Add sharding algorithm example. (#16868)
fcbd4d8adb9 is described below
commit fcbd4d8adb90aad8472babb22e5e3d0f07e72762
Author: yx9o <[email protected]>
AuthorDate: Sun Apr 17 13:56:54 2022 +0800
Add sharding algorithm example. (#16868)
* Add sharding algorithm example.
* Update.
* Update.
---
.../shardingsphere/example/type/ShardingType.java | 14 +--
.../core/api/entity/OrderStatisticsInfo.java} | 40 +++----
.../repository/OrderStatisticsInfoRepository.java} | 34 +-----
.../OrderStatisticsInfoRepositoryImpl.java | 118 +++++++++++++++++++++
.../service/OrderStatisticsInfoServiceImpl.java | 105 ++++++++++++++++++
...hardingRawYamlIntervalConfigurationExample.java | 37 +++++++
.../jdbc/factory/YamlRangeDataSourceFactory.java | 2 +
.../META-INF/sharding-databases-interval.yaml | 55 ++++++++++
8 files changed, 343 insertions(+), 62 deletions(-)
diff --git
a/examples/example-core/config-utility/src/main/java/org/apache/shardingsphere/example/type/ShardingType.java
b/examples/example-core/config-utility/src/main/java/org/apache/shardingsphere/example/type/ShardingType.java
index 705169390ee..ac96eb50870 100644
---
a/examples/example-core/config-utility/src/main/java/org/apache/shardingsphere/example/type/ShardingType.java
+++
b/examples/example-core/config-utility/src/main/java/org/apache/shardingsphere/example/type/ShardingType.java
@@ -28,9 +28,9 @@ public enum ShardingType {
SHARDING_SHADOW_DATABASES,
ENCRYPT_SHADOW,
-
+
READWRITE_SPLITTING,
-
+
READWRITE_SPLITTING_SHADOW,
SHARDING_READWRITE_SPLITTING,
@@ -42,10 +42,12 @@ public enum ShardingType {
SHADOW_DEFAULT_ALGORITHM,
SHARDING_AUTO_TABLES,
-
+
SHARDING_HINT_DATABASES_ONLY,
-
+
SHARDING_HINT_DATABASES_TABLES,
-
- READWRITE_SPLITTING_HINT
+
+ READWRITE_SPLITTING_HINT,
+
+ SHARDING_DATABASES_INTERVAL
}
diff --git
a/examples/example-core/config-utility/src/main/java/org/apache/shardingsphere/example/type/ShardingType.java
b/examples/example-core/example-api/src/main/java/org/apache/shardingsphere/example/core/api/entity/OrderStatisticsInfo.java
similarity index 62%
copy from
examples/example-core/config-utility/src/main/java/org/apache/shardingsphere/example/type/ShardingType.java
copy to
examples/example-core/example-api/src/main/java/org/apache/shardingsphere/example/core/api/entity/OrderStatisticsInfo.java
index 705169390ee..da222e8f644 100644
---
a/examples/example-core/config-utility/src/main/java/org/apache/shardingsphere/example/type/ShardingType.java
+++
b/examples/example-core/example-api/src/main/java/org/apache/shardingsphere/example/core/api/entity/OrderStatisticsInfo.java
@@ -15,37 +15,27 @@
* limitations under the License.
*/
-package org.apache.shardingsphere.example.type;
+package org.apache.shardingsphere.example.core.api.entity;
-public enum ShardingType {
-
- SHARDING_DATABASES,
-
- SHARDING_TABLES,
-
- SHARDING_DATABASES_AND_TABLES,
-
- SHARDING_SHADOW_DATABASES,
-
- ENCRYPT_SHADOW,
+import lombok.Getter;
+import lombok.Setter;
+import lombok.ToString;
- READWRITE_SPLITTING,
+import java.io.Serializable;
+import java.time.LocalDate;
- READWRITE_SPLITTING_SHADOW,
+@Getter
+@Setter
+@ToString
+public class OrderStatisticsInfo implements Serializable {
- SHARDING_READWRITE_SPLITTING,
+ private static final long serialVersionUID = -1770007969944794302L;
- ENCRYPT,
+ private Long id;
- SHADOW,
+ private Long userId;
- SHADOW_DEFAULT_ALGORITHM,
+ private LocalDate orderDate;
- SHARDING_AUTO_TABLES,
-
- SHARDING_HINT_DATABASES_ONLY,
-
- SHARDING_HINT_DATABASES_TABLES,
-
- READWRITE_SPLITTING_HINT
+ private int orderNum;
}
diff --git
a/examples/example-core/config-utility/src/main/java/org/apache/shardingsphere/example/type/ShardingType.java
b/examples/example-core/example-api/src/main/java/org/apache/shardingsphere/example/core/api/repository/OrderStatisticsInfoRepository.java
similarity index 60%
copy from
examples/example-core/config-utility/src/main/java/org/apache/shardingsphere/example/type/ShardingType.java
copy to
examples/example-core/example-api/src/main/java/org/apache/shardingsphere/example/core/api/repository/OrderStatisticsInfoRepository.java
index 705169390ee..7a044e72d92 100644
---
a/examples/example-core/config-utility/src/main/java/org/apache/shardingsphere/example/type/ShardingType.java
+++
b/examples/example-core/example-api/src/main/java/org/apache/shardingsphere/example/core/api/repository/OrderStatisticsInfoRepository.java
@@ -15,37 +15,9 @@
* limitations under the License.
*/
-package org.apache.shardingsphere.example.type;
+package org.apache.shardingsphere.example.core.api.repository;
-public enum ShardingType {
-
- SHARDING_DATABASES,
-
- SHARDING_TABLES,
-
- SHARDING_DATABASES_AND_TABLES,
-
- SHARDING_SHADOW_DATABASES,
-
- ENCRYPT_SHADOW,
+import org.apache.shardingsphere.example.core.api.entity.OrderStatisticsInfo;
- READWRITE_SPLITTING,
-
- READWRITE_SPLITTING_SHADOW,
-
- SHARDING_READWRITE_SPLITTING,
-
- ENCRYPT,
-
- SHADOW,
-
- SHADOW_DEFAULT_ALGORITHM,
-
- SHARDING_AUTO_TABLES,
-
- SHARDING_HINT_DATABASES_ONLY,
-
- SHARDING_HINT_DATABASES_TABLES,
-
- READWRITE_SPLITTING_HINT
+public interface OrderStatisticsInfoRepository extends
CommonRepository<OrderStatisticsInfo, Long> {
}
diff --git
a/examples/example-core/example-raw-jdbc/src/main/java/org/apache/shardingsphere/example/core/jdbc/repository/OrderStatisticsInfoRepositoryImpl.java
b/examples/example-core/example-raw-jdbc/src/main/java/org/apache/shardingsphere/example/core/jdbc/repository/OrderStatisticsInfoRepositoryImpl.java
new file mode 100644
index 00000000000..72797ce6573
--- /dev/null
+++
b/examples/example-core/example-raw-jdbc/src/main/java/org/apache/shardingsphere/example/core/jdbc/repository/OrderStatisticsInfoRepositoryImpl.java
@@ -0,0 +1,118 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package org.apache.shardingsphere.example.core.jdbc.repository;
+
+import org.apache.shardingsphere.example.core.api.entity.OrderStatisticsInfo;
+import
org.apache.shardingsphere.example.core.api.repository.OrderStatisticsInfoRepository;
+
+import javax.sql.DataSource;
+import java.sql.Connection;
+import java.sql.Date;
+import java.sql.PreparedStatement;
+import java.sql.ResultSet;
+import java.sql.SQLException;
+import java.sql.Statement;
+import java.util.LinkedList;
+import java.util.List;
+
+public class OrderStatisticsInfoRepositoryImpl implements
OrderStatisticsInfoRepository {
+
+ private final DataSource dataSource;
+
+ public OrderStatisticsInfoRepositoryImpl(final DataSource dataSource) {
+ this.dataSource = dataSource;
+ }
+
+ @Override
+ public void createTableIfNotExists() throws SQLException {
+ String sql = "CREATE TABLE IF NOT EXISTS order_statistics_info (id
BIGINT NOT NULL AUTO_INCREMENT, user_id BIGINT NOT NULL, order_date DATE NOT
NULL, order_num INT, PRIMARY KEY (id))";
+ try (Connection connection = dataSource.getConnection();
+ Statement statement = connection.createStatement()) {
+ statement.executeUpdate(sql);
+ }
+ }
+
+ @Override
+ public void dropTable() throws SQLException {
+ String sql = "DROP TABLE order_statistics_info";
+ try (Connection connection = dataSource.getConnection();
+ Statement statement = connection.createStatement()) {
+ statement.executeUpdate(sql);
+ }
+ }
+
+ @Override
+ public void truncateTable() throws SQLException {
+ String sql = "TRUNCATE TABLE order_statistics_info";
+ try (Connection connection = dataSource.getConnection();
+ Statement statement = connection.createStatement()) {
+ statement.executeUpdate(sql);
+ }
+ }
+
+ @Override
+ public Long insert(final OrderStatisticsInfo orderStatisticsInfo) throws
SQLException {
+ String sql = "INSERT INTO order_statistics_info (user_id, order_date,
order_num) VALUES (?, ?, ?)";
+ try (Connection connection = dataSource.getConnection();
+ PreparedStatement preparedStatement =
connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
+ preparedStatement.setLong(1, orderStatisticsInfo.getUserId());
+ preparedStatement.setDate(2,
Date.valueOf(orderStatisticsInfo.getOrderDate()));
+ preparedStatement.setInt(3, orderStatisticsInfo.getOrderNum());
+ preparedStatement.executeUpdate();
+ try (ResultSet resultSet = preparedStatement.getGeneratedKeys()) {
+ if (resultSet.next()) {
+ orderStatisticsInfo.setId(resultSet.getLong(1));
+ }
+ }
+ }
+ return orderStatisticsInfo.getId();
+ }
+
+ @Override
+ public void delete(final Long id) throws SQLException {
+ String sql = "DELETE FROM order_statistics_info WHERE id=?";
+ try (Connection connection = dataSource.getConnection();
+ PreparedStatement preparedStatement =
connection.prepareStatement(sql)) {
+ preparedStatement.setLong(1, id);
+ preparedStatement.executeUpdate();
+ }
+ }
+
+ @Override
+ public List<OrderStatisticsInfo> selectAll() throws SQLException {
+ String sql = "SELECT * FROM order_statistics_info";
+ return getOrderStatisticsInfos(sql);
+ }
+
+ protected List<OrderStatisticsInfo> getOrderStatisticsInfos(final String
sql) throws SQLException {
+ List<OrderStatisticsInfo> result = new LinkedList<>();
+ try (Connection connection = dataSource.getConnection();
+ PreparedStatement preparedStatement =
connection.prepareStatement(sql);
+ ResultSet resultSet = preparedStatement.executeQuery()) {
+ while (resultSet.next()) {
+ OrderStatisticsInfo orderStatisticsInfo = new
OrderStatisticsInfo();
+ orderStatisticsInfo.setId(resultSet.getLong(1));
+ orderStatisticsInfo.setUserId(resultSet.getLong(2));
+
orderStatisticsInfo.setOrderDate(resultSet.getDate(3).toLocalDate());
+ orderStatisticsInfo.setOrderNum(resultSet.getInt(4));
+ result.add(orderStatisticsInfo);
+ }
+ }
+ return result;
+ }
+}
diff --git
a/examples/example-core/example-raw-jdbc/src/main/java/org/apache/shardingsphere/example/core/jdbc/service/OrderStatisticsInfoServiceImpl.java
b/examples/example-core/example-raw-jdbc/src/main/java/org/apache/shardingsphere/example/core/jdbc/service/OrderStatisticsInfoServiceImpl.java
new file mode 100644
index 00000000000..d54b059a582
--- /dev/null
+++
b/examples/example-core/example-raw-jdbc/src/main/java/org/apache/shardingsphere/example/core/jdbc/service/OrderStatisticsInfoServiceImpl.java
@@ -0,0 +1,105 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package org.apache.shardingsphere.example.core.jdbc.service;
+
+import org.apache.shardingsphere.example.core.api.entity.OrderStatisticsInfo;
+import
org.apache.shardingsphere.example.core.api.repository.OrderStatisticsInfoRepository;
+import org.apache.shardingsphere.example.core.api.service.ExampleService;
+import
org.apache.shardingsphere.example.core.jdbc.repository.OrderStatisticsInfoRepositoryImpl;
+
+import javax.sql.DataSource;
+import java.sql.SQLException;
+import java.time.LocalDate;
+import java.util.ArrayList;
+import java.util.Collection;
+
+public final class OrderStatisticsInfoServiceImpl implements ExampleService {
+
+ private final OrderStatisticsInfoRepository orderStatisticsInfoRepository;
+
+ public OrderStatisticsInfoServiceImpl(final DataSource dataSource) {
+ orderStatisticsInfoRepository = new
OrderStatisticsInfoRepositoryImpl(dataSource);
+ }
+
+ @Override
+ public void initEnvironment() throws SQLException {
+ orderStatisticsInfoRepository.createTableIfNotExists();
+ orderStatisticsInfoRepository.truncateTable();
+ }
+
+ @Override
+ public void cleanEnvironment() throws SQLException {
+ orderStatisticsInfoRepository.dropTable();
+ }
+
+ @Override
+ public void processSuccess() throws SQLException {
+ System.out.println("-------------- Process Success Begin
---------------");
+ Collection<Long> ids = insertData();
+ printData();
+ deleteData(ids);
+ printData();
+ System.out.println("-------------- Process Success Finish
--------------");
+ }
+
+ @Override
+ public void processFailure() throws SQLException {
+ System.out.println("-------------- Process Failure Begin
---------------");
+ insertData();
+ System.out.println("-------------- Process Failure Finish
--------------");
+ throw new RuntimeException("Exception occur for transaction test.");
+ }
+
+ private Collection<Long> insertData() throws SQLException {
+ System.out.println("------------------- Insert Data
--------------------");
+ Collection<Long> result = new ArrayList<>(10);
+ for (int i = 1; i <= 10; i++) {
+ OrderStatisticsInfo orderStatisticsInfo =
insertOrderStatisticsInfo(i);
+ result.add(orderStatisticsInfo.getId());
+ }
+ return result;
+ }
+
+ private OrderStatisticsInfo insertOrderStatisticsInfo(final int i) throws
SQLException {
+ OrderStatisticsInfo result = new OrderStatisticsInfo();
+ result.setUserId(new Long(i));
+ if (i % 2 == 0) {
+ result.setOrderDate(LocalDate.now().plusYears(-1));
+ } else {
+ result.setOrderDate(LocalDate.now());
+ }
+ result.setOrderNum(i * 10);
+ orderStatisticsInfoRepository.insert(result);
+ return result;
+ }
+
+ private void deleteData(final Collection<Long> ids) throws SQLException {
+ System.out.println("-------------------- Delete Data
-------------------");
+ for (Long each : ids) {
+ orderStatisticsInfoRepository.delete(each);
+ }
+ }
+
+ @Override
+ public void printData() throws SQLException {
+ System.out.println("---------------- Print Order Data
------------------");
+ for (Object each : orderStatisticsInfoRepository.selectAll()) {
+ System.out.println(each);
+ }
+ }
+}
diff --git
a/examples/shardingsphere-jdbc-example/single-feature-example/sharding-example/sharding-raw-jdbc-example/src/main/java/org/apache/shardingsphere/example/sharding/raw/jdbc/ShardingRawYamlIntervalConfigurationExample.java
b/examples/shardingsphere-jdbc-example/single-feature-example/sharding-example/sharding-raw-jdbc-example/src/main/java/org/apache/shardingsphere/example/sharding/raw/jdbc/ShardingRawYamlIntervalConfigurationExample.java
new file mode 100644
index 00000000000..e36d860ebad
--- /dev/null
+++
b/examples/shardingsphere-jdbc-example/single-feature-example/sharding-example/sharding-raw-jdbc-example/src/main/java/org/apache/shardingsphere/example/sharding/raw/jdbc/ShardingRawYamlIntervalConfigurationExample.java
@@ -0,0 +1,37 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package org.apache.shardingsphere.example.sharding.raw.jdbc;
+
+import org.apache.shardingsphere.example.core.api.ExampleExecuteTemplate;
+import
org.apache.shardingsphere.example.core.jdbc.service.OrderStatisticsInfoServiceImpl;
+import
org.apache.shardingsphere.example.sharding.raw.jdbc.factory.YamlRangeDataSourceFactory;
+import org.apache.shardingsphere.example.type.ShardingType;
+
+import javax.sql.DataSource;
+import java.io.IOException;
+import java.sql.SQLException;
+
+public final class ShardingRawYamlIntervalConfigurationExample {
+
+ private static ShardingType shardingType =
ShardingType.SHARDING_DATABASES_INTERVAL;
+
+ public static void main(final String[] args) throws SQLException,
IOException {
+ DataSource dataSource =
YamlRangeDataSourceFactory.newInstance(shardingType);
+ ExampleExecuteTemplate.run(new
OrderStatisticsInfoServiceImpl(dataSource));
+ }
+}
diff --git
a/examples/shardingsphere-jdbc-example/single-feature-example/sharding-example/sharding-raw-jdbc-example/src/main/java/org/apache/shardingsphere/example/sharding/raw/jdbc/factory/YamlRangeDataSourceFactory.java
b/examples/shardingsphere-jdbc-example/single-feature-example/sharding-example/sharding-raw-jdbc-example/src/main/java/org/apache/shardingsphere/example/sharding/raw/jdbc/factory/YamlRangeDataSourceFactory.java
index 8f86aae6168..fc4e4f30c13 100644
---
a/examples/shardingsphere-jdbc-example/single-feature-example/sharding-example/sharding-raw-jdbc-example/src/main/java/org/apache/shardingsphere/example/sharding/raw/jdbc/factory/YamlRangeDataSourceFactory.java
+++
b/examples/shardingsphere-jdbc-example/single-feature-example/sharding-example/sharding-raw-jdbc-example/src/main/java/org/apache/shardingsphere/example/sharding/raw/jdbc/factory/YamlRangeDataSourceFactory.java
@@ -35,6 +35,8 @@ public final class YamlRangeDataSourceFactory {
return
YamlShardingSphereDataSourceFactory.createDataSource(getFile("/META-INF/sharding-tables-range.yaml"));
case SHARDING_DATABASES_AND_TABLES:
return
YamlShardingSphereDataSourceFactory.createDataSource(getFile("/META-INF/sharding-databases-tables-range.yaml"));
+ case SHARDING_DATABASES_INTERVAL:
+ return
YamlShardingSphereDataSourceFactory.createDataSource(getFile("/META-INF/sharding-databases-interval.yaml"));
default:
throw new UnsupportedOperationException(shardingType.name());
}
diff --git
a/examples/shardingsphere-jdbc-example/single-feature-example/sharding-example/sharding-raw-jdbc-example/src/main/resources/META-INF/sharding-databases-interval.yaml
b/examples/shardingsphere-jdbc-example/single-feature-example/sharding-example/sharding-raw-jdbc-example/src/main/resources/META-INF/sharding-databases-interval.yaml
new file mode 100644
index 00000000000..b7083402237
--- /dev/null
+++
b/examples/shardingsphere-jdbc-example/single-feature-example/sharding-example/sharding-raw-jdbc-example/src/main/resources/META-INF/sharding-databases-interval.yaml
@@ -0,0 +1,55 @@
+#
+# Licensed to the Apache Software Foundation (ASF) under one or more
+# contributor license agreements. See the NOTICE file distributed with
+# this work for additional information regarding copyright ownership.
+# The ASF licenses this file to You under the Apache License, Version 2.0
+# (the "License"); you may not use this file except in compliance with
+# the License. You may obtain a copy of the License at
+#
+# http://www.apache.org/licenses/LICENSE-2.0
+#
+# Unless required by applicable law or agreed to in writing, software
+# distributed under the License is distributed on an "AS IS" BASIS,
+# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+# See the License for the specific language governing permissions and
+# limitations under the License.
+#
+
+dataSources:
+ ds_statistics:
+ dataSourceClassName: com.zaxxer.hikari.HikariDataSource
+ driverClassName: com.mysql.jdbc.Driver
+ jdbcUrl:
jdbc:mysql://localhost:3306/demo_ds?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=UTF-8
+ username: root
+ password:
+
+rules:
+- !SHARDING
+ tables:
+ order_statistics_info:
+ actualDataNodes: ds_statistics.order_statistics_info_${2021..2022}
+ keyGenerateStrategy:
+ column: id
+ keyGeneratorName: snowflake
+ tableStrategy:
+ standard:
+ shardingColumn: order_date
+ shardingAlgorithmName: database-interval
+
+ shardingAlgorithms:
+ database-interval:
+ type: INTERVAL
+ props:
+ datetime-pattern: 'yyyy-MM-dd HH:mm:ss'
+ datetime-lower: '2021-01-01 00:00:00'
+ datetime-upper: '2025-01-01 00:00:00'
+ sharding-suffix-pattern: 'yyyy'
+ datetime-interval-amount: 1
+ datetime-interval-unit: 'YEARS'
+
+ keyGenerators:
+ snowflake:
+ type: SNOWFLAKE
+
+props:
+ sql-show: false
