This is an automated email from the ASF dual-hosted git repository.
duanzhengqiang 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 22d8526 Add example for sql hint (#13899)
22d8526 is described below
commit 22d8526ce6fc627dcf74899479fd8485f6f52e27
Author: tuichenchuxin <[email protected]>
AuthorDate: Thu Dec 2 16:00:49 2021 +0800
Add example for sql hint (#13899)
* add example for sql hint
* add example for sql hint
* add example for sql hint
---
.../raw/jdbc/ShardingSQLCommentHintRawExample.java | 59 ++++++++++++++++++++
.../META-INF/sharding-sql-comment-hint.yaml | 62 ++++++++++++++++++++++
.../example/proxy/hint/HintType.java | 2 +-
.../example/proxy/hint/ProxyHintExample.java | 20 +++++--
.../resources/META-INF/sql-hint-data-source.yaml | 21 ++++++++
.../conf/config-sql-hint-data-source.yaml | 55 +++++++++++++++++++
.../src/main/resources/conf/server.yaml | 1 +
7 files changed, 214 insertions(+), 6 deletions(-)
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/ShardingSQLCommentHintRawExample.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/ShardingSQLCommentHintRawExample.java
new file mode 100644
index 0000000..2813e85
--- /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/ShardingSQLCommentHintRawExample.java
@@ -0,0 +1,59 @@
+/*
+ * 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.driver.api.yaml.YamlShardingSphereDataSourceFactory;
+import org.apache.shardingsphere.example.core.api.service.ExampleService;
+import org.apache.shardingsphere.example.core.jdbc.service.OrderServiceImpl;
+
+import javax.sql.DataSource;
+import java.io.File;
+import java.io.IOException;
+import java.sql.Connection;
+import java.sql.SQLException;
+import java.sql.Statement;
+
+public final class ShardingSQLCommentHintRawExample {
+
+ public static void main(final String[] args) throws SQLException,
IOException {
+ DataSource dataSource =
YamlShardingSphereDataSourceFactory.createDataSource(getFile());
+ ExampleService exampleService = getExampleService(dataSource);
+ exampleService.initEnvironment();
+ processWithHintValue(dataSource);
+ exampleService.cleanEnvironment();
+ }
+
+ private static File getFile() {
+ return new
File(ShardingSQLCommentHintRawExample.class.getResource("/META-INF/sharding-sql-comment-hint.yaml").getFile());
+ }
+
+ private static ExampleService getExampleService(final DataSource
dataSource) {
+ return new OrderServiceImpl(dataSource);
+ }
+
+ private static void processWithHintValue(final DataSource dataSource)
throws SQLException {
+ try (Connection connection = dataSource.getConnection();
+ Statement statement = connection.createStatement()) {
+ statement.execute("/* sql hint: dataSourceName=ds_1 */select *
from t_order");
+ statement.execute("/* sql hint: dataSourceName=ds_1 */SELECT i.*
FROM t_order o, t_order_item i WHERE o.order_id = i.order_id");
+ statement.execute("/* sql hint: dataSourceName=ds_1 */select *
from t_order_item");
+ statement.execute("/* sql hint: dataSourceName=ds_1 */INSERT INTO
t_order (user_id, address_id, status) VALUES (1, 1, 'init')");
+ }
+ }
+}
+
diff --git
a/examples/shardingsphere-jdbc-example/single-feature-example/sharding-example/sharding-raw-jdbc-example/src/main/resources/META-INF/sharding-sql-comment-hint.yaml
b/examples/shardingsphere-jdbc-example/single-feature-example/sharding-example/sharding-raw-jdbc-example/src/main/resources/META-INF/sharding-sql-comment-hint.yaml
new file mode 100644
index 0000000..34b131c
--- /dev/null
+++
b/examples/shardingsphere-jdbc-example/single-feature-example/sharding-example/sharding-raw-jdbc-example/src/main/resources/META-INF/sharding-sql-comment-hint.yaml
@@ -0,0 +1,62 @@
+#
+# 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_0:
+ dataSourceClassName: com.zaxxer.hikari.HikariDataSource
+ driverClassName: com.mysql.jdbc.Driver
+ jdbcUrl:
jdbc:mysql://localhost:3306/demo_ds_0?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=UTF-8
+ username: root
+ password:
+ ds_1:
+ dataSourceClassName: com.zaxxer.hikari.HikariDataSource
+ driverClassName: com.mysql.jdbc.Driver
+ jdbcUrl:
jdbc:mysql://localhost:3306/demo_ds_1?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=UTF-8
+ username: root
+ password:
+
+rules:
+- !SHARDING
+ tables:
+ t_order:
+ actualDataNodes: ds_${0..1}.t_order
+ keyGenerateStrategy:
+ column: order_id
+ keyGeneratorName: snowflake
+ t_order_item:
+ actualDataNodes: ds_${0..1}.t_order_item
+ keyGenerateStrategy:
+ column: order_item_id
+ keyGeneratorName: snowflake
+ bindingTables:
+ - t_order,t_order_item
+ broadcastTables:
+ - t_address
+
+ defaultDatabaseStrategy:
+ none:
+ defaultTableStrategy:
+ none:
+
+ keyGenerators:
+ snowflake:
+ type: SNOWFLAKE
+ props:
+ worker-id: 123
+props:
+ sql-show: true
+ sql-comment-parse-enabled: true
diff --git
a/examples/shardingsphere-proxy-example/shardingsphere-proxy-hint-example/src/main/java/org/apache/shardingsphere/example/proxy/hint/HintType.java
b/examples/shardingsphere-proxy-example/shardingsphere-proxy-hint-example/src/main/java/org/apache/shardingsphere/example/proxy/hint/HintType.java
index d28102b..d1ddf0d 100644
---
a/examples/shardingsphere-proxy-example/shardingsphere-proxy-hint-example/src/main/java/org/apache/shardingsphere/example/proxy/hint/HintType.java
+++
b/examples/shardingsphere-proxy-example/shardingsphere-proxy-hint-example/src/main/java/org/apache/shardingsphere/example/proxy/hint/HintType.java
@@ -19,5 +19,5 @@ package org.apache.shardingsphere.example.proxy.hint;
public enum HintType {
- DATABASE_ONLY, DATABASE_TABLES, WRITE_ONLY
+ DATABASE_ONLY, DATABASE_TABLES, WRITE_ONLY, SQL_HINT_DATASOURCE
}
diff --git
a/examples/shardingsphere-proxy-example/shardingsphere-proxy-hint-example/src/main/java/org/apache/shardingsphere/example/proxy/hint/ProxyHintExample.java
b/examples/shardingsphere-proxy-example/shardingsphere-proxy-hint-example/src/main/java/org/apache/shardingsphere/example/proxy/hint/ProxyHintExample.java
index 3bbbbf2..32bc9df 100644
---
a/examples/shardingsphere-proxy-example/shardingsphere-proxy-hint-example/src/main/java/org/apache/shardingsphere/example/proxy/hint/ProxyHintExample.java
+++
b/examples/shardingsphere-proxy-example/shardingsphere-proxy-hint-example/src/main/java/org/apache/shardingsphere/example/proxy/hint/ProxyHintExample.java
@@ -33,6 +33,7 @@ public final class ProxyHintExample {
private static final HintType TYPE = HintType.DATABASE_TABLES;
// private static final HintType TYPE = HintType.DATABASE_ONLY;
// private static final HintType TYPE = HintType.WRITE_ONLY;
+// private static final HintType TYPE = HintType.SQL_HINT_DATASOURCE;
public static void main(final String[] args) throws SQLException,
IOException {
DataSource dataSource = getDataSource();
@@ -50,6 +51,8 @@ public final class ProxyHintExample {
return
YamlDataSourceFactory.createDataSource(getFile("/META-INF/hint-databases-only.yaml"));
case WRITE_ONLY:
return
YamlDataSourceFactory.createDataSource(getFile("/META-INF/hint-write-only.yaml"));
+ case SQL_HINT_DATASOURCE:
+ return
YamlDataSourceFactory.createDataSource(getFile("/META-INF/sql-hint-data-source.yaml"));
default:
throw new UnsupportedOperationException("unsupported type");
}
@@ -66,11 +69,18 @@ public final class ProxyHintExample {
private static void processWithHintValue(final DataSource dataSource)
throws SQLException {
try (Connection connection = dataSource.getConnection();
Statement statement = connection.createStatement()) {
- setHintValue(statement);
- statement.execute("select * from t_order");
- statement.execute("SELECT i.* FROM t_order o, t_order_item i WHERE
o.order_id = i.order_id");
- statement.execute("select * from t_order_item");
- statement.execute("INSERT INTO t_order (user_id, address_id,
status) VALUES (1, 1, 'init')");
+ if (TYPE == HintType.SQL_HINT_DATASOURCE) {
+ statement.execute("/* sql hint: dataSourceName=ds_1 */select *
from t_order");
+ statement.execute("/* sql hint: dataSourceName=ds_1 */SELECT
i.* FROM t_order o, t_order_item i WHERE o.order_id = i.order_id");
+ statement.execute("/* sql hint: dataSourceName=ds_1 */select *
from t_order_item");
+ statement.execute("/* sql hint: dataSourceName=ds_1 */INSERT
INTO t_order (user_id, address_id, status) VALUES (1, 1, 'init')");
+ } else {
+ setHintValue(statement);
+ statement.execute("select * from t_order");
+ statement.execute("SELECT i.* FROM t_order o, t_order_item i
WHERE o.order_id = i.order_id");
+ statement.execute("select * from t_order_item");
+ statement.execute("INSERT INTO t_order (user_id, address_id,
status) VALUES (1, 1, 'init')");
+ }
}
}
diff --git
a/examples/shardingsphere-proxy-example/shardingsphere-proxy-hint-example/src/main/resources/META-INF/sql-hint-data-source.yaml
b/examples/shardingsphere-proxy-example/shardingsphere-proxy-hint-example/src/main/resources/META-INF/sql-hint-data-source.yaml
new file mode 100644
index 0000000..67b432b
--- /dev/null
+++
b/examples/shardingsphere-proxy-example/shardingsphere-proxy-hint-example/src/main/resources/META-INF/sql-hint-data-source.yaml
@@ -0,0 +1,21 @@
+#
+# 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.
+#
+
+driverClassName: com.mysql.jdbc.Driver
+jdbcUrl: jdbc:mysql://localhost:3307/sql_hint
+username: root
+password: root
diff --git
a/examples/shardingsphere-proxy-example/shardingsphere-proxy-hint-example/src/main/resources/conf/config-sql-hint-data-source.yaml
b/examples/shardingsphere-proxy-example/shardingsphere-proxy-hint-example/src/main/resources/conf/config-sql-hint-data-source.yaml
new file mode 100644
index 0000000..da46f3d
--- /dev/null
+++
b/examples/shardingsphere-proxy-example/shardingsphere-proxy-hint-example/src/main/resources/conf/config-sql-hint-data-source.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.
+#
+
+schemaName: sql_hint
+
+dataSources:
+ ds_0:
+ url:
jdbc:mysql://localhost:3306/demo_ds_0?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=UTF-8
+ username: root
+ password:
+ connectionTimeoutMilliseconds: 30000
+ idleTimeoutMilliseconds: 60000
+ maxLifetimeMilliseconds: 1800000
+ maxPoolSize: 50
+ minPoolSize: 1
+ ds_1:
+ url:
jdbc:mysql://localhost:3306/demo_ds_1?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=UTF-8
+ username: root
+ password:
+ connectionTimeoutMilliseconds: 30000
+ idleTimeoutMilliseconds: 60000
+ maxLifetimeMilliseconds: 1800000
+ maxPoolSize: 50
+ minPoolSize: 1
+
+rules:
+- !SHARDING
+ tables:
+ t_order:
+ actualDataNodes: ds_${0..1}.t_order
+ t_order_item:
+ actualDataNodes: ds_${0..1}.t_order_item
+ bindingTables:
+ - t_order,t_order_item
+ broadcastTables:
+ - t_address
+
+ defaultDatabaseStrategy:
+ none:
+ defaultTableStrategy:
+ none:
diff --git
a/examples/shardingsphere-proxy-example/shardingsphere-proxy-hint-example/src/main/resources/conf/server.yaml
b/examples/shardingsphere-proxy-example/shardingsphere-proxy-hint-example/src/main/resources/conf/server.yaml
index 99cf1f4..3694db9 100644
---
a/examples/shardingsphere-proxy-example/shardingsphere-proxy-hint-example/src/main/resources/conf/server.yaml
+++
b/examples/shardingsphere-proxy-example/shardingsphere-proxy-hint-example/src/main/resources/conf/server.yaml
@@ -50,3 +50,4 @@ props:
proxy-hint-enabled: true
sql-show: true
check-table-metadata-enabled: false
+ sql-comment-parse-enabled: true
