[shardingsphere] branch master updated: Faqs to remind user properly configure(case sensitive) tableName in oracle (#16609)

[du...@apache.org]

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 0eec1b6a92b Faqs to remind user properly configure(case sensitive) tableName in oracle (#16609)
0eec1b6a92b is described below

commit 0eec1b6a92b0153fa591131e0bd6e209689e8875
Author: cheese8 <yi...@163.com>
AuthorDate: Fri Apr 8 09:12:25 2022 +0800

    Faqs to remind user properly configure(case sensitive) tableName in oracle (#16609)
    
    * Update OracleTableMetaDataLoader.java
    
    * Update OracleTableMetaDataLoader.java
    
    * add faqs
    
    * revert the warn message
    
    * Update OracleTableMetaDataLoader.java
---
 docs/document/content/reference/faq/_index.cn.md | 27 ++++++++++++++++++++++++
 docs/document/content/reference/faq/_index.en.md | 27 ++++++++++++++++++++++++
 2 files changed, 54 insertions(+)

diff --git a/docs/document/content/reference/faq/_index.cn.md b/docs/document/content/reference/faq/_index.cn.md
index c79190dd67a..d1645e75b77 100644
--- a/docs/document/content/reference/faq/_index.cn.md
+++ b/docs/document/content/reference/faq/_index.cn.md
@@ -386,3 +386,30 @@ Caused by: java.lang.NullPointerException: Inline sharding algorithm expression
 	... 
 ```
 从异常堆栈中分析可知： `AbstractAlgorithmProvidedBeanRegistry.registerBean` 方法调用 `PropertyUtil.containPropertyPrefix(environment, prefix)` 方法判断指定前缀 `prefix` 的配置是否存在，而 `PropertyUtil.containPropertyPrefix(environment, prefix)` 方法，在 Spring Boot 2.x 环境下使用了 Binder，不规范的属性名称（如：驼峰或下划线等）会导致属性设置不生效。
+
+## [ShardingSphere-JDBC] Oracle 表名、字段名配置大小写在加载 `metadata` 元数据时结果不正确？
+
+回答：
+
+需要注意，Oracle 表名和字段名，默认元数据都是大写，除非建表语句中带双引号，如 `CREATE TABLE "TableName"("Id" number)` 元数据为双引号中内容，可参考以下SQL查看元数据的具体情况：
+```
+SELECT OWNER, TABLE_NAME, COLUMN_NAME, DATA_TYPE FROM ALL_TAB_COLUMNS WHERE TABLE_NAME IN ('TableName') 
+```
+
+ShardingSphere 使用 `OracleTableMetaDataLoader` 对 Oracle 元数据进行加载，配置时需确保表名、字段名的大小写配置与数据库中的一致。
+
+ShardingSphere 查询元数据关键SQL:
+```
+    private String getTableMetaDataSQL(final Collection<String> tables, final DatabaseMetaData metaData) throws SQLException {
+        StringBuilder stringBuilder = new StringBuilder(28);
+        if (versionContainsIdentityColumn(metaData)) {
+            stringBuilder.append(", IDENTITY_COLUMN");
+        }
+        if (versionContainsCollation(metaData)) {
+            stringBuilder.append(", COLLATION");
+        }
+        String collation = stringBuilder.toString();
+        return tables.isEmpty() ? String.format(TABLE_META_DATA_SQL, collation)
+                : String.format(TABLE_META_DATA_SQL_IN_TABLES, collation, tables.stream().map(each -> String.format("'%s'", each)).collect(Collectors.joining(",")));
+    }
+```
\ No newline at end of file
diff --git a/docs/document/content/reference/faq/_index.en.md b/docs/document/content/reference/faq/_index.en.md
index c709e8c1102..ddf28add665 100644
--- a/docs/document/content/reference/faq/_index.en.md
+++ b/docs/document/content/reference/faq/_index.en.md
@@ -391,3 +391,30 @@ Caused by: java.lang.NullPointerException: Inline sharding algorithm expression
 	... 
 ```
 From the exception stack, the `AbstractAlgorithmProvidedBeanRegistry.registerBean` method calls `PropertyUtil.containPropertyPrefix (environment, prefix)` , and `PropertyUtil.containPropertyPrefix (environment, prefix)` determines that the configuration of the specified prefix does not exist, while the method uses Binder in an unsatisfied property name (such as camelcase or underscore) causing property settings does not to take effect.
+
+## [ShardingSphere-JDBC] The tableName and columnName configured in yaml or properties leading incorrect result when loading Oracle metadata？
+
+Answer：
+
+Note that, in Oracle's metadata, the tableName and columnName is default UPPERCASE, while double-quoted such as `CREATE TABLE "TableName"("Id" number)` the tableName and columnName is the actual content double-quoted, refer to the following SQL for the reality in metadata:
+```
+SELECT OWNER, TABLE_NAME, COLUMN_NAME, DATA_TYPE FROM ALL_TAB_COLUMNS WHERE TABLE_NAME IN ('TableName') 
+```
+
+The ShardingSphere uses the `OracleTableMetaDataLoader` to load the metadata, keep the tableName and columnName in the yaml or properties consistent with the metadata.
+
+The ShardingSphere assembled the SQL using the following code:
+```
+    private String getTableMetaDataSQL(final Collection<String> tables, final DatabaseMetaData metaData) throws SQLException {
+        StringBuilder stringBuilder = new StringBuilder(28);
+        if (versionContainsIdentityColumn(metaData)) {
+            stringBuilder.append(", IDENTITY_COLUMN");
+        }
+        if (versionContainsCollation(metaData)) {
+            stringBuilder.append(", COLLATION");
+        }
+        String collation = stringBuilder.toString();
+        return tables.isEmpty() ? String.format(TABLE_META_DATA_SQL, collation)
+                : String.format(TABLE_META_DATA_SQL_IN_TABLES, collation, tables.stream().map(each -> String.format("'%s'", each)).collect(Collectors.joining(",")));
+    }
+```
\ No newline at end of file