OJDBC 7为什么不将CHAR数据类型映射到Java字符串? [英] Why doesn't OJDBC 7 map the CHAR data type to a Java String?

问题描述

OJDBC的工作之一是将Oracle数据类型映射到Java类型.

One of the jobs of OJDBC is to map Oracle data types to Java types.

但是，我们注意到如果提供CHAR数据类型，则不会将其映射到java.lang.String.显示此行为的版本是: OJDBC7 v12.1.0.2 和 OJDBC6 v12.1.0.1 .确实，旧版本确实将CHAR数据类型映射到:java.lang.String.

However, we noticed that if we give a CHAR data type, it is not mapped to java.lang.String. The versions showing this behavior are: OJDBC7 v12.1.0.2 and OJDBC6 v12.1.0.1. The older versions did indeed map the CHAR data type to: java.lang.String.

在深入研究时，我们发现OJDBC的oracle.jdbc.driver包中有一个类:StructMetaData，用于实现Oracle数据类型到Java类型的映射.其中有一个方法:"getColumnClassName(int arg0)"值得关注.我们注意到，对于OJDBC v7，映射到java.lang.String的情况如下:

On digging deeper, we discovered that there is a class: StructMetaData within the oracle.jdbc.driver package of OJDBC that implements the Oracle data type to Java Type mapping. There is a method within it: 'getColumnClassName(int arg0)' that is worthy of attention. We noticed that for OJDBC v7, the cases mapped to java.lang.String are as follows:

    int arg1 = this.getColumnType(arg0);
    switch (arg1) {
    case -104:
        return "oracle.sql.INTERVALDS";
    case -103:
        return "oracle.sql.INTERVALYM";
    case -102:
        return "oracle.sql.TIMESTAMPLTZ";
    case -101:
        return "oracle.sql.TIMESTAMPTZ";
    case -15:
    case -9:
    case 12:
        return "java.lang.String";
     ...

但是，在较旧的OJDBC实现中，它看起来像这样:

However, within older OJDBC implementations, it looked like this:

    int arg1 = this.getColumnType(arg0);
    switch (arg1) {
    case -104:
        return "oracle.sql.INTERVALDS";
    case -103:
        return "oracle.sql.INTERVALYM";
    case -102:
        return "oracle.sql.TIMESTAMPLTZ";
    case -101:
        return "oracle.sql.TIMESTAMPTZ";
    case -15:
    case -9:
    case 1:
    case 12:
        return "java.lang.String";
    ...

在后一种情况下，还有一个映射到java.lang.String的情况. '情况1'.在上面显示的第一个代码段中，此案例1"未映射到java.lang.String.

There is an additional case mapped to java.lang.String in the latter case viz. 'case 1'. This 'case 1' is not mapped to java.lang.String in the first code snippet shown above.

深入研究时，此案例1"将映射到同一StructMetaData类的getColumnTypeName(int arg0 )方法内的CHAR:

On looking deeper, this 'case 1' is mapped to CHAR within the getColumnTypeName(int arg0 ) method of the same StructMetaData class:

public String getColumnTypeName(int arg0) throws SQLException {
    int arg1 = this.getColumnType(arg0);
    int arg2 = this.getValidColumnIndex(arg0);
    switch (arg1) {
    case -104:
        return "INTERVALDS";
    case -103:
        return "INTERVALYM";
    case -102:
        return "TIMESTAMP WITH LOCAL TIME ZONE";
    case -101:
        return "TIMESTAMP WITH TIME ZONE";
    case -15:
        return "NCHAR";
    case -13:
        return "BFILE";
    case -9:
        return "NVARCHAR";
    case -2:
        return "RAW";
    case 1:
        return "CHAR";
 ...

因此，如果我们使用OJDBC 7或OJDBC6 v12.1.0.1并指定CHAR作为列的数据类型，则以下代码将在调用此列的索引时返回null:

Because of this, if we use OJDBC 7 or OJDBC6 v12.1.0.1 and specify CHAR as the data type for a column, the following code returns null on invocation for this column's index:

 for (int i = 1; i <= resultSetMetaData.getColumnCount(); i++) {
     ...
     resultSetMetaData.getColumnClassName(columnIndex)
     ...

如果替换旧版本的OJDBC jar(例如:11.2.0.3)，则相同的代码将返回:java.lang.String.这是错误还是已被设计清除? 有人遇到过同样的问题吗?

If I replace an older version of the OJDBC jar (for example: 11.2.0.3), then the same code returns: java.lang.String. Is this a bug or was it removed by design? Has anybody faced the same issue before?

推荐答案

好收获...！

它看起来确实像个虫子；也许唯一的反对意见是，甲骨文将对它进行巨大的忽视.

It really looks like a bug; maybe the only argument against is that it would be an incredibly huge overlook by Oracle.

专业错误:

• 向后兼容性问题. jdbc驱动程序的更新将破坏明确依赖CHAR声明
的现有应用程序
• 关于JPublisher的此注释数据类型和Java-to-Java类型映射描述了从12c开始的映射

• backward compatibility problem. Updates of the jdbc driver would disrupt existing applications that are explicitly relying on the CHAR declaration
• This note about JPublisher Data Type and Java-to-Java Type Mappings describes the mapping as of 12c

(请注意与CHAR类型相关的第一行，它映射到java.lang.String)

(note the first row, related to the CHAR type, which maps to java.lang.String)

| SQL and PL/SQL Data Type | Oracle Mapping   | JDBC Mapping
|------------------------- |------------------|-----------------------------------------------
| CHAR, CHARACTER, LONG,   |                  | 
|STRING, VARCHAR, VARCHAR2 | oracle.sql.CHAR  | java.lang.String
| NCHAR, NVARCHAR2         | oracle.sql.NCHAR | oracle.sql.NString 
| NCLOB                    | oracle.sql.NCLOB | oracle.sql.NCLOB 

针对错误:

• 一个奇怪的假设可能是Oracle试图从支持的类型中排除CHAR.的确，CHAR类型确实比VARCHAR2没有任何好处，并且有一些缺点使其成为无选择.在过去，我喜欢这样一个事实，即"CHAR"会与其他开发人员交流，您打算使用预定义且始终固定长度的项目来定义项目，但是它甚至没有强制执行此操作(如果您填写，则仅填充字符串)值太短). 如果您有兴趣，可以在专家Oracle数据库体系结构-Oracle Database 9i， 10克，和|托马斯·凯特|专门针对该主题的Apress .您可以在咨询中阅读摘录汤姆"Char Vs Varchar" ，作者引用了他自己的书:

• A fancy hypothesis could argue that Oracle is trying to dismiss CHARs from the supported types. Indeed, the CHAR type has really no benefit over the VARCHAR2, and a few downsides make it a no-choice. In the past I loved the fact that a "CHAR" communicates to other developers your intention to define an item with a predefined and invariably fixed length, but it doesn't even enforce this (it just pads the string, if you fill it with too a short value). If you are interested, there is a section in the excellent book Expert Oracle Database Architecture - Oracle Database 9i, 10g, and | Thomas Kyte | Apress dedicated to the topic. You can read an excerpt in Ask Tom "Char Vs Varchar", at the point where the author quotes his own book:

CHAR/NCHAR实际上只不过是VARCHAR2/NVARCHAR2而已，这使我认为实际上只考虑两种字符串类型，即VARCHAR2和NVARCHAR2.我从未在任何应用程序中找到过用于CHAR类型的用途.由于CHAR类型始终将空白字符串填充为固定宽度，因此我们很快发现它在表段和任何索引段中都消耗了最大的存储空间.那将很糟糕，但是要避免使用CHAR/NCHAR类型还有另一个重要原因:它们在需要检索此信息的应用程序中造成混乱(许多存储后无法查找"其数据).其原因与字符串比较的规则和执行它们的严格性有关. ....

The fact that a CHAR/NCHAR is really nothing more than a VARCHAR2/NVARCHAR2 in disguise makes me of the opinion that there are really only two character string types to ever consider, namely VARCHAR2 and NVARCHAR2. I have never found a use for the CHAR type in any application. Since a CHAR type always blank pads the resulting string out to a fixed width, we discover rapidly that it consumes maximum storage both in the table segment and any index segments. That would be bad enough, but there is another important reason to avoid CHAR/NCHAR types: they create confusion in applications that need to retrieve this information (many cannot "find" their data after storing it). The reason for this relates to the rules of character string comparison and the strictness with which they are performed. ....

[然后在同一篇文章中有一个很好的例子；非常值得一读]

[then a great example follows in the same post; it is very well worth reading]

CHAR不好的事实当然不能证明Oracle在没有明确通知的情况下破坏现有应用程序是正确的.因此，假设它是一个错误的说法显然更明智.

The fact that CHAR are no good, of course doesn't justify Oracle from breaking existing applications with no clear notice; so the hypothesis that it is a bug is clearly more sensible.

鉴于从理论上讲它没有缺点，作为一种极端的解决方法，您可以更改所有涉及的表以将其CHAR类型重新定义为VARCHAR2(如果您有权这样做，并且是可行的).

Given that theoretically it wouldn't have downsides, as an extreme workaround you might alter all involved tables to redefine their CHAR types as VARCHAR2 (if you are entitled to do so, and it is feasible).

这篇关于OJDBC 7为什么不将CHAR数据类型映射到Java字符串?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！