官方文档

Oracle 官方的 JDBC 开发者指南：

数据库版本 指南链接
Oracle 11g(11.2) Database JDBC Developer’s Guide - Oracle 11g(11.2)
Oracle 12c(12.1) Database JDBC Developer’s Guide - Oracle 12c(12.1)
Oracle 12c(12.2) JDBC Developer’s Guide - Oracle 12c(12.2)
Oracle 18c Oracle Database JDBC Developer’s Guide, Release 18c
Oracle 19c Oracle Database JDBC Developer’s Guide, 19c
Oracle 21c Oracle Database JDBC Developer’s Guide and Reference, 21c
Oracle 23ai Oracle Database JDBC Developer’s Guide, 23ai

JDBC 驱动和数据库版本匹配(兼容)关系

JDBC 驱动和 Oracle 数据库版本的匹配关系见下表，Oracle 官方建议[1] JDBC 驱动的版本最好大于等于数据库版本，从而更好地使用驱动最新的功能。

该表是博主综合多篇 Oracle 官方资料整理出来的，详细的信息来源可以参考下边的信息来源部分。

横轴：数据库版本
纵轴：JDBC 驱动版本		 23.3 21.x 19.x 18.3 12.2 and 12.1 11.2.0.4 10g 9i
JDBC 23.3 支持 支持 支持 不支持 不支持 不支持 不支持 不支持
JDBC 21.x 支持 支持 支持 曾经支持 曾经支持 曾经支持 不支持 不支持
JDBC 19.x 支持 支持 支持 曾经支持 曾经支持 曾经支持 不支持 不支持
JDBC 18.x 不支持 曾经支持 曾经支持 曾经支持 曾经支持 曾经支持 不支持 不支持
JDBC 12.2 and 12.1 不支持 曾经支持 曾经支持 曾经支持 曾经支持 曾经支持 不支持 不支持
JDBC 11.2.0.4 不支持 曾经支持 曾经支持 曾经支持 曾经支持 曾经支持 曾经支持 曾经支持

支持就是官方认证的支持关系。

曾经支持用官方的话来说是

曾经支持指的是曾经受支持的组合，但其中一个发行版不再受到任何 Premier Support、Primary Error Correct、Extended Support 和 Extended Maintenance Support 的支持，也无法进行修复。

——Oracle JDBC 常见问题解答 | Oracle 中国

说的通俗点就是对应版本的驱动或者数据库不维护了，有 BUG 估计也不会修复了，建议不要使用。不过这里博主友情提示，如果在使用支持的驱动和数据库遇到问题的时候，不妨切换到曾经支持的驱动去试一试。

不支持代表 Oracle 已经在官方文档上声明对应驱动和数据库之间不兼容了。

上边的表格也称作 JDBC 和数据库互操作性矩阵（Interoperability Matrix）或认证矩阵（Certification Matrix）[2]。

信息来源
Oralce 11g(11.2)

The JDBC drivers are certified to work with the currently supported versions of Oracle Database. For example, the JDBC Thin drivers in Oracle Database 11g Release 2 (11.2) are certified to work with the 10.2.x, 10.1.x, 9.2.x, and 9.0.1.x Oracle Database releases. However, they are not certified to work with older, unsupported database releases, such as 8.0.x and 7.x.

——Version Compatibility for Oracle JDBC Drivers - Database JDBC Developer’s Guide - Oracle 11g(11.2)

Oralce 12c(12.1)

Oracle Database 12c Release 1 (12.1) JDBC drivers are certified with supported Oracle Database releases (11.x.0.x). However, they are not certified to work with older, unsupported database releases, such as 10.2.x, 10.1.x, 9.2.x, and 9.0.1.x.

——Version Compatibility for Oracle JDBC Drivers - Database JDBC Developer’s Guide - Oracle 12c(12.1)

Oralce 12c(12.2)

Oracle Database 12c Release 2 (12.2.0.1) JDBC drivers are certified with supported Oracle Database releases (11.x.0.x). However, they are not certified to work with older, unsupported database releases, such as 10.2.x, 10.1.x, 9.2.x, and 9.0.1.x.

Note: If you want to use Fast Connection Failover mechanism, then use the 10.2 JDBC driver with Oracle database 10.2. If the database is 10.1, then use 10.1 JDBC driver.

——Version Compatibility for Oracle JDBC Drivers - Database JDBC Developer’s Guide - Oracle 12c(12.2)

Oralce 18c

Oracle Database 12c Release 2 (12.2.0.1) JDBC drivers are certified with supported Oracle Database releases (11.x.0.x). However, they are not certified to work with older, unsupported database releases, such as 10.2.x, 10.1.x, 9.2.x, and 9.0.1.x.

Note: If you want to use Fast Connection Failover mechanism, then use the 10.2 JDBC driver with Oracle database 10.2. If the database is 10.1, then use 10.1 JDBC driver.

——Version Compatibility for Oracle JDBC Drivers - JDBC Developer’s Guide - Oracle 18c

Oralce 19c

The following table describes the JDBC and Oracle Database interoperability matrix or the certification matrix:

JDBC Driver VersionDatabase 19.xDatabase 18.3Database 12.2 and 12.1Database 11.2.0.4
JDBC 19.xYesYesYesYes
JDBC 18.3YesYesYesYes
JDBC 12.2 and 12.1YesYesYesYes
JDBC 11.2.0.4YesYesYesYes

——Version Compatibility for Oracle JDBC Drivers - JDBC Developer’s Guide - Oracle 19c

Oralce 21c

Oracle Database Release 21c JDBC drivers are certified with all supported Oracle Database releases (21c, 19c, 18c, 12c, and 11g Release 2). However, they are not certified to work with older, unsupported database releases, such as 10g and 9i.

The following table describes the JDBC and Oracle Database interoperability matrix or the certification matrix:

JDBC Driver VersionDatabase 21.xDatabase 19.xDatabase 18.3Database 12.2 and 12.1Database 11.2.0.4
JDBC 21.xYesYesYesYesYes
JDBC 19.xYesYesYesYesYes
JDBC 18.3YesYesYesYesYes
JDBC 12.2 and 12.1YesYesYesYesYes
JDBC 11.2.0.4YesYesYesYesYes

——RDBMS and JDK Version Compatibility for Oracle JDBC Drivers - JDBC Developer’s Guide and Reference - Oracle 21c

Oralce 23ai

Oracle Database Release 23ai JDBC drivers are certified with all the supported Oracle Database releases (23ai, 21c, and 19c).

The following table describes the JDBC and Oracle Database interoperability matrix or the certification matrix:

JDBC Driver VersionDatabase 23.xDatabase 21.xDatabase 19.x
JDBC 23YesYesYes
JDBC 21.xYesYesYes
JDBC 19.xYesYesYes

——RDBMS and JDK Version Compatibility for Oracle JDBC Drivers - JDBC Developer’s Guide - Oracle 23ai

Oracle JDBC FAQ

What is the JDBC and RDBMS interoperability matrix or the certification matrix?

Please refer to the table that covers the JDBC driver interoperability matrix for the supported Oracle database versions. The recommendation is that JDBC driver version should always be either same as or higher than the Oracle database version in order to leverage the latest capabilities of the driver.

Interoperability MatrixDatabase 23.3Database 21.xDatabase 19.xDatabase 18.3Database 12.2 and 12.1
JDBC 23.3YesYesYesNoNo
JDBC 21.xYesYesYesWasWas
JDBC 19.xYesYesYesWasWas
JDBC 18.xNoWasWasWasWas
JDBC 12.2 and 12.1NoWasWasWasWas

Was: Was a supported combination but one of the releases is no longer covered by any of Premier Support, Primary Error Correct support, Extended Support, nor Extended Maintenance Support. Fixes are no longer possible.

——Oracle JDBC Frequently Asked Questions

Oracle Database Release 21c JDBC drivers are certified with all supported Oracle Database releases (21c, 19c, 18c, 12c, and 11g Release 2). However, they are not certified to work with older, unsupported database releases, such as 10g and 9i.

The following table describes the JDBC and Oracle Database interoperability matrix or the certification matrix:

Oracle JDBC Drivers are always compliant to the latest JDK version for every new release. For some versions, JDBC drivers support multiple JDK versions. The following table describes the release-specific JDBC JAR files and supported JDK versions for various Oracle Database versions:

Oracle Database Version Release-Specific JDBC JAR File with Supported JDK Version
21.x ojdbc11.jar with JDK 11, JDK 12, JDK 13, JDK 14 and JDK 15
ojdbc8.jar with JDK 8, JDK 11, JDK 12, JDK 13, JDK 14 and JDK 15
19.x ojdbc10.jar with JDK 10, JDK 11ojdbc8.jar with JDK 8, JDK 9, JDK 11
18.3 ojdbc8.jar with JDK 8, JDK 9, JDK 10, JDK 11
12.2 or 12cR2 ojdbc8.jar with JDK 8
12.1 or 12cR1 ojdbc7.jar with JDK 7, JDK 8ojdbc6.jar with JDK 6
11.2 or 11gR2 ojdbc6.jar with JDK 6, JDK 7, JDK 8ojdbc5.jar with JDK 5

OJDBC版本区别 ojdbc14.jar，ojdbc5.jar和ojdbc6.jar的区别ojdbc14和ojdbc6-CSDN博客

版本 JDK JAR包名称
Oracle 10g JDK 1.2 and JDK 1.3 classes12.jar
Oracle 10g JDK 1.4 and 1.5 ojdbc14.jar
Oracle 11g JDK1.5 ojdbc5.jar
Oracle 11g JDK1.6 ojdbc6.jar

注意，ojdbc14.jar 是适配 JDK 1.4 的版本。

JDBC URL 相关

Data Sources and URLs

Troubleshooting【有一些和 URL 相关的注意事项】

常用属性

oracle.jdbc.ReadTimeout

java - oracle.jdbc.ReadTimeout vs. Connection.getNetworkTimeout vs. Statement.setQueryTimeout - Stack Overflow

目前已知

（1）JDBC 连接没有可以指定客户端时区的参数。

JDBC API

在网上没有找到在线版的 Javadoc，不过可以在 Maven 仓库下载到：Maven Repository: com.oracle.database.jdbc » ojdbc8 » 19.8.0.0

点击【Files】中的 【View All】就可以下载。

PreparedStatement 数字类型写入问题

PreparedStatement 有一个 void setObject(int parameterIndex, Object x, int targetSqlType, int scaleOrLength) 方法，如果指定了 targetSqlType 为 Types.NUMERIC，那么当插入的 Object 是一个 String 且是小数的时候，会报错。

如果一个字段在目标表中是 NUMBER 类型，但是使用 JDBC 插入数据的时候，数字是使用字符串表示的，那么当这个字符串形式的数字是整形的时候可以插入成功，但是当这个字符串类型的数字为小数时，会插入失败。报错大概是：

1
2
3
4
5
6
7
8
9
10
11
Caused by: java.lang.NumberFormatException: For input string: "9."
     at java.lang.NumberFormatException.forInputString(NumberFormatException.java:65)
     at java.lang.Integer.parseInt(Integer.java:580)
     at java.lang.Integer.parseInt(Integer.java:615)
     at oracle.sql.NUMBER.toBytes(NUMBER.java:1938)
     at oracle.sql.NUMBER.(NUMBER.java:289)
     at oracle.jdbc.driver.OraclePreparedStatement.setObjectCritical(OraclePreparedStatement.java:7742)
     at oracle.jdbc.driver.OraclePreparedStatement.setObjectInternal(OraclePreparedStatement.java:7581)
     at oracle.jdbc.driver.OraclePreparedStatement.setObject(OraclePreparedStatement.java:7554)
     at oracle.jdbc.driver.OraclePreparedStatementWrapper.setObject(OraclePreparedStatementWrapper.java:1032)
     at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.setObject(HikariProxyPreparedStatement.java)

原因是 Oracle JDBC 在底层会对字符串格式的数字进行类型转换，但是只有整形的可以转换成功，字符串类型的就会失败。解决方法就是如果数字是小数，那么就不要把 tart 设置为 Types.NUMERIC，而是设置为不在 Types 中的一个值，这样就可以插入成功。

报错：ORA-00933: SQL 命令未正确结束

可能的原因1：SQL 末尾带上了分号（;），去掉之后就正常了。

Statement 超时设置

  1. java - oracle.jdbc.ReadTimeout vs. Connection.getNetworkTimeout vs. Statement.setQueryTimeout - Stack Overflow

其他

Oracle 杀死执行的SQL语句_每天进步一点点 时间会让你成为巨人-CSDN博客

依赖 JAR

使用 JDBC 连接 Oracle 数据库，默认只需要 ojdbc*.jar 这个 JAR，部分情况下还需要其他 JAR。

  • 当数据库编码是 UCS2,ASCII, ISO_LATIN_1, UTF8 and AL32UTF8 以外的编码，且客户端使用 ADT 时，必须加上 orai18n.jar。
  • ojdbc*.jar - all the classes to support basic functionality for the Thin and OCI drivers
  • ojdbc*_g.jar - same as ojdbc*.jar except compiled with the -g option to include debugging information and with java.util.logging calls included.
  • ojdbc*dms.jar - same as ojdbc*.jar except includes code to support Oracle Dynamic Monitoring Service (DMS). Also includes some JDBC logging support. This file can only be used when dms.jar is also in the classpath. The dms.jar file is not shipped as part of the RDBMS product. It is only available as part of the Oracle Application Server product.
  • ojdbc*dms_g.jar - same as ojdbc*dms.jar except compiled with the -g option to include debugging information and with full JDBC logging support.
  • orai18n.jar - contains the configuration information to support all Oracle character sets in Advanced Data Types (objects). If the database character set is one other than UCS2,ASCII, ISO_LATIN_1, UTF8 and AL32UTF8 and the application uses ADTs, then you must include this file in your classpath.

——java - Which Oracle jdbc jars are required - Stack Overflow

如果不加，会报错：

1
不支持的字符集 (在类路径中添加 orai18n.jar): ZHS16GBK

或

1
Non supported character set (add orai18n.jar in your classpath)

还有一种说法：

  • CHAR or VARCHAR data members of object and collection for the character sets US7ASCII, WE8DEC, WE8ISO8859P1, WE8MSWIN1252, and UTF8.

To use any other character sets in CHAR or VARCHAR data members of objects or collections, you must include orai18n.jar in the CLASSPATH of your application.

——12 Globalization Support

关于批量写入

java - How to select optimal batch size in JDBC? - Stack Overflow

Performance Extensions

常见问题

获取主键速度慢

使用 JDBC 的 DatabaseMetaData#getColumns 获取字段信息的时候较慢。

JDBC 驱动中原始的 SQL：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
declare
  in_owner varchar2(256) := null;
  in_name varchar2(256) := null;
  in_column varchar2(256) := null;
  xyzzy SYS_REFCURSOR;
begin
  in_owner := ?;
  in_name := ?;
  in_column := ?;
open xyzzy for
SELECT NULL AS table_cat,
       t.owner AS table_schem,
       t.table_name AS table_name,
       t.column_name AS column_name,
  DECODE(substr(t.data_type, 1, 9), 
    'TIMESTAMP', 
      DECODE(substr(t.data_type, 10, 1), 
        '(', 
          DECODE(substr(t.data_type, 19, 5), 
            'LOCAL', -102, 'TIME ', -101, 93), 
        DECODE(substr(t.data_type, 16, 5), 
          'LOCAL', -102, 'TIME ', -101, 93)), 
    'INTERVAL ', 
      DECODE(substr(t.data_type, 10, 3), 
       'DAY', -104, 'YEA', -103), 
    DECODE(t.data_type, 
      'BINARY_DOUBLE', 101, 
      'BINARY_FLOAT', 100, 
      'BFILE', -13, 
      'BLOB', 2004, 
      'CHAR', 1, 
      'CLOB', 2005, 
      'COLLECTION', 2003, 
      'DATE', 93, 
      'FLOAT', 6, 
      'LONG', -1, 
      'LONG RAW', -4, 
      'NCHAR', -15, 
      'NCLOB', 2011, 
      'NUMBER', 2, 
      'NVARCHAR', -9, 
      'NVARCHAR2', -9, 
      'OBJECT', 2002, 
      'OPAQUE/XMLTYPE', 2009, 
      'RAW', -3, 
      'REF', 2006, 
      'ROWID', -8, 
      'SQLXML', 2009, 
      'UROWID', -8, 
      'VARCHAR2', 12, 
      'VARRAY', 2003, 
      'XMLTYPE', 2009, 
      DECODE((SELECT a.typecode 
        FROM ALL_TYPES a 
        WHERE a.type_name = t.data_type
       AND ((a.owner IS NULL AND 
t.data_type_owner IS NULL)
         OR (a.owner = t.data_type_owner))
        ), 
        'OBJECT', 2002, 
        'COLLECTION', 2003, 1111))) 
 AS data_type,
       t.data_type AS type_name,
       DECODE (t.data_precision,                null, DECODE(t.data_type,                        'NUMBER', DECODE(t.data_scale,                                    null, 0                                   , 38),        DECODE (t.data_type,                'CHAR', t.char_length,                'VARCHAR', t.char_length,                'VARCHAR2', t.char_length,                'NVARCHAR2', t.char_length,                'NCHAR', t.char_length,                'NUMBER', 0,                t.data_length)                           ),         t.data_precision)
              AS column_size,
       0 AS buffer_length,
       DECODE (t.data_type,                'NUMBER', DECODE(t.data_precision,                                 null, DECODE(t.data_scale,                                              null, -127                                             , t.data_scale),                                  t.data_scale),                t.data_scale) AS decimal_digits,
       10 AS num_prec_radix,
       DECODE (t.nullable, 'N', 0, 1) AS nullable,
       NULL AS remarks,
       t.data_default AS column_def,
       0 AS sql_data_type,
       0 AS sql_datetime_sub,
       t.data_length AS char_octet_length,
       t.column_id AS ordinal_position,
       DECODE (t.nullable, 'N', 'NO', 'YES') AS is_nullable,
       null as SCOPE_CATALOG,
       null as SCOPE_SCHEMA,
       null as SCOPE_TABLE,
       null as SOURCE_DATA_TYPE,
       'NO' as IS_AUTOINCREMENT,
       t.virtual_column as IS_GENERATEDCOLUMN
FROM all_tab_cols t
WHERE t.owner LIKE in_owner ESCAPE '/'
  AND t.table_name LIKE in_name ESCAPE '/'
  AND t.column_name LIKE in_column ESCAPE '/'
  AND t.user_generated = 'YES'
ORDER BY table_schem, table_name, ordinal_position
; 
 ? := xyzzy;
 end;

以湖南盐业 EAS1104 模式做测试：

JDBC 驱动的 SQL，对应 databaseMetaData.getColumns

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
SELECT NULL                                 AS table_cat,
       t.owner                              AS table_schem,
       t.table_name                         AS table_name,
       t.column_name                        AS column_name,
       DECODE(substr(t.data_type, 1, 9),
              'TIMESTAMP',
              DECODE(substr(t.data_type, 10, 1),
                     '(',
                     DECODE(substr(t.data_type, 19, 5),
                            'LOCAL', -102, 'TIME ', -101, 93),
                     DECODE(substr(t.data_type, 16, 5),
                            'LOCAL', -102, 'TIME ', -101, 93)),
              'INTERVAL ',
              DECODE(substr(t.data_type, 10, 3),
                     'DAY', -104, 'YEA', -103),
              DECODE(t.data_type,
                     'BINARY_DOUBLE', 101,
                     'BINARY_FLOAT', 100,
                     'BFILE', -13,
                     'BLOB', 2004,
                     'CHAR', 1,
                     'CLOB', 2005,
                     'COLLECTION', 2003,
                     'DATE', 93,
                     'FLOAT', 6,
                     'LONG', -1,
                     'LONG RAW', -4,
                     'NCHAR', -15,
                     'NCLOB', 2011,
                     'NUMBER', 2,
                     'NVARCHAR', -9,
                     'NVARCHAR2', -9,
                     'OBJECT', 2002,
                     'OPAQUE/XMLTYPE', 2009,
                     'RAW', -3,
                     'REF', 2006,
                     'ROWID', -8,
                     'SQLXML', 2009,
                     'UROWID', -8,
                     'VARCHAR2', 12,
                     'VARRAY', 2003,
                     'XMLTYPE', 2009,
                     DECODE((SELECT a.typecode
                             FROM ALL_TYPES a
                             WHERE a.type_name = t.data_type
                               AND ((a.owner IS NULL AND
                                     t.data_type_owner IS NULL)
                                 OR (a.owner = t.data_type_owner))
                            ),
                            'OBJECT', 2002,
                            'COLLECTION', 2003, 1111)))
                                            AS data_type,
       t.data_type                          AS type_name,
       DECODE(t.data_precision, null, DECODE(t.data_type, 'NUMBER', DECODE(t.data_scale, null, 0, 38),
                                             DECODE(t.data_type, 'CHAR', t.char_length, 'VARCHAR',
                                                    t.char_length, 'VARCHAR2', t.char_length, 'NVARCHAR2',
                                                    t.char_length, 'NCHAR', t.char_length, 'NUMBER', 0,
                                                    t.data_length)), t.data_precision)
                                            AS column_size,
       0                                    AS buffer_length,
       DECODE(t.data_type, 'NUMBER',
              DECODE(t.data_precision, null, DECODE(t.data_scale, null, -127, t.data_scale), t.data_scale),
              t.data_scale)                 AS decimal_digits,
       10                                   AS num_prec_radix,
       DECODE(t.nullable, 'N', 0, 1)        AS nullable,
       NULL                                 AS remarks,
       t.data_default                       AS column_def,
       0                                    AS sql_data_type,
       0                                    AS sql_datetime_sub,
       t.data_length                        AS char_octet_length,
       t.column_id                          AS ordinal_position,
       DECODE(t.nullable, 'N', 'NO', 'YES') AS is_nullable,
       null                                 as SCOPE_CATALOG,
       null                                 as SCOPE_SCHEMA,
       null                                 as SCOPE_TABLE,
       null                                 as SOURCE_DATA_TYPE,
       'NO'                                 as IS_AUTOINCREMENT,
       t.virtual_column                     as IS_GENERATEDCOLUMN
FROM all_tab_cols t
WHERE t.owner LIKE 'EAS1104' ESCAPE '/'
    AND t.table_name LIKE '%' ESCAPE '/'
        AND t.column_name LIKE '%' ESCAPE '/'
            AND t.user_generated = 'YES'
ORDER BY table_schem, table_name, ordinal_position;

SQL 总行数：216554 行

取前 500 行：执行：4 s 408 ms，传输数据：11 s 698 ms

速度慢的原因估计有：

（1）WHERE 条件中大量使用 LIKE 子句。

（2）上方有对应于 ALL_TYPES 的子查询，如果数据库中的表数量很多，那么子查询也会极大影响性能。

自己构造 SQL，修改了一下 WHERE 条件，避免 LIKE 的使用，把 owner、table_name 使用 IN 子句精准限制范围。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
SELECT NULL                                 AS table_cat,
       t.owner                              AS table_schem,
       t.table_name                         AS table_name,
       t.column_name                        AS column_name,
       DECODE(substr(t.data_type, 1, 9),
              'TIMESTAMP',
              DECODE(substr(t.data_type, 10, 1),
                     '(',
                     DECODE(substr(t.data_type, 19, 5),
                            'LOCAL', -102, 'TIME ', -101, 93),
                     DECODE(substr(t.data_type, 16, 5),
                            'LOCAL', -102, 'TIME ', -101, 93)),
              'INTERVAL ',
              DECODE(substr(t.data_type, 10, 3),
                     'DAY', -104, 'YEA', -103),
              DECODE(t.data_type,
                     'BINARY_DOUBLE', 101,
                     'BINARY_FLOAT', 100,
                     'BFILE', -13,
                     'BLOB', 2004,
                     'CHAR', 1,
                     'CLOB', 2005,
                     'COLLECTION', 2003,
                     'DATE', 93,
                     'FLOAT', 6,
                     'LONG', -1,
                     'LONG RAW', -4,
                     'NCHAR', -15,
                     'NCLOB', 2011,
                     'NUMBER', 2,
                     'NVARCHAR', -9,
                     'NVARCHAR2', -9,
                     'OBJECT', 2002,
                     'OPAQUE/XMLTYPE', 2009,
                     'RAW', -3,
                     'REF', 2006,
                     'ROWID', -8,
                     'SQLXML', 2009,
                     'UROWID', -8,
                     'VARCHAR2', 12,
                     'VARRAY', 2003,
                     'XMLTYPE', 2009,
                     DECODE((SELECT a.typecode
                             FROM ALL_TYPES a
                             WHERE a.type_name = t.data_type
                               AND ((a.owner IS NULL AND
                                     t.data_type_owner IS NULL)
                                 OR (a.owner = t.data_type_owner))
                            ),
                            'OBJECT', 2002,
                            'COLLECTION', 2003, 1111)))
                                            AS data_type,
       t.data_type                          AS type_name,
       DECODE(t.data_precision, null, DECODE(t.data_type, 'NUMBER', DECODE(t.data_scale, null, 0, 38),
                                             DECODE(t.data_type, 'CHAR', t.char_length, 'VARCHAR',
                                                    t.char_length, 'VARCHAR2', t.char_length, 'NVARCHAR2',
                                                    t.char_length, 'NCHAR', t.char_length, 'NUMBER', 0,
                                                    t.data_length)), t.data_precision)
                                            AS column_size,
       DECODE(t.nullable, 'N', 0, 1)        AS nullable,
       NULL                                 AS remarks,
       t.column_id                          AS ordinal_position,
       DECODE(t.nullable, 'N', 'NO', 'YES') AS is_nullable
FROM all_tab_cols t
WHERE t.owner IN ('EAS1104')
  AND t.table_name IN (
                       'T_AR_OTHERBILL',
                       'T_AR_OTHERBILLENTRY',
                       'T_BD_MATERIAL'
    )
  AND t.user_generated = 'YES'
ORDER BY table_schem, table_name, ordinal_position;

结果总数：12715 列

取前 500 行：执行：512ms，传输数据：155ms

PS：本来是希望通过自己构造 SQL 加快查询系统元数据的速度，但是发现驱动中处理的逻辑比较复杂，不同版本驱动可能还有所修改，所以这项工作没有做。

另外，如果获取注释和 SYNONYM 也可能出现性能问题：

The DatabaseMetaData calls getTables and getColumns are extremely slow if the REMARKS column has to be reported as this necessitates an expensive outer join so by default the JDBC driver does not report the REMARKS columns.

Similar to setRemarksReporting, getColumns performs extremely slow if information about synonyms has to be included, because it neccessitates an outer join so, by default, the JDBC driver will not report information about synonyms.

——OracleConnection (Oracle Database JDBC Java API Reference)

其他资料：

java - JDBC with Spring slow metadata fetch Oracle - Stack Overflow【同样分析了 Oracle 的 JDBC 驱动说明了一下速度慢的原因。】

Oracle 12c 获取主键速度特别慢的问题

使用 JDBC 的 DatabaseMetaData.getPrimaryKeys() 获取主键，速度特别慢，根本原因是查询 ALL_CONSTRAINTS、ALL_CONS_COLUMNS 这几个表比较慢。

网上有这些说法：

  1. 不仅仅是 12c，很多版本都有这个问题
  2. 仅仅从查询角度优化空间较小，可能需要调整数据库配置

Oracle 12c - Slow query to tables ALL_CONS_COLUMNS and ALL_CONSTRAINTS - Database Administrators Stack Exchange

oracle12c - Queries on user_cons_columns much slower in Oracle 12c - Stack Overflow

sql - Oracle 11g : Meta data query very slow - Stack Overflow

Query in oracle too slow · Issue #5878 · dbeaver/dbeaver

How to get primary key column in Oracle? - Stack Overflow

Slow query fetching Oracle primary / unique constraints on large schemas · Issue #8462 · jOOQ/jOOQ

尝试了一下 这个回答 建议的 materialize hint，但是并没有什么用，甚至更慢了。

关于 ALL_TAB_COLUMN、ALL_CONS_COLUMNS：

ALL_TAB_COLUMNS

ALL_CONS_COLUMNS

sql - How to view all the Metadata of columns of a table in oracle database? - Stack Overflows

java - JDBC with Spring slow metadata fetch Oracle - Stack Overflow

网络传输压缩

从 Oracle Database 12c Release 2 (12.2.0.1) 开始，JDBC Thin 驱动开始支持网络传输压缩。

使用方法：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
OracleDataSource ds = new OracleDataSource();
Properties prop = new Properties();
prop.setProperty("user","user1");
prop.setProperty("password",<password>);

// Enabling Network Compression
prop.setProperty("oracle.net.networkCompression","on");

//Optional configuration for setting the client compression threshold.
prop.setProperty("oracle.net.networkCompressionThreshold","1024");

ds.setConnectionProperties(prop);
ds.setURL(url);
Connection conn = ds.getConnection();

Data Sources and URLs【8.1.10 章节】

Advanced Network Compression

Advanced Network Compression – A Lessor-Known Feature of Advanced Compression

客户端设置 PROGRAM 名称

关键：

1
props.put("v$session.program", "Test");

示例：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
class SetProgram
{
  public static void main (String args [])
       throws SQLException
  {
    // Load the Oracle JDBC driver
    DriverManager.registerDriver(new oracle.jdbc.OracleDriver());

    java.util.Properties props = new java.util.Properties();
    props.put("v$session.program", "Test");

    // Connect to the database
    Connection conn =
      DriverManager.getConnection ("jdbc:oracle:thin:user/tiger@localhost:1521:xe",
				   props);

    // Create a Statement
    Statement stmt = conn.createStatement ();

    // Select the PROGRAM field from the V$SESSION table
    ResultSet rset = stmt.executeQuery ("select program from v$session");

    // Iterate through the result
    while (rset.next ())
      System.out.println (rset.getString (1));
  }
}

Specifying a program name in Oracle JDBC connections | magpiebrain

注意，连接建立之后，就不能再修改 PROGRAM 了，下边的方式无效：

1
2
connection.setClientInfo("OCSID.PROGRAM","verycool");
connection.setClientInfo("v$session.program","look here");

但是可以设置 CLIENT_IDENTIFIER、CLIENT_INFO 等字段

1
connection.setClientInfo("OCSID.CLIENTID","test1");

参考资料

  1. Oracle JDBC Frequently Asked Questions
  2. Getting Started - JDBC Developer’s Guide and Reference - Oracle 21c
  3. Getting Started - JDBC Developer’s Guide and Reference - Oracle 23ai
  4. oracle.jdbc.ReadTimeout

脚注

  1. 1.The recommendation is that JDBC driver version should always be either same as or higher than the Oracle database version in order to leverage the latest capabilities of the driver. ——Oracle JDBC Frequently Asked Questions ↩
  2. 2.什么是 JDBC 和 RDBMS 互操作性矩阵或认证矩阵？——Oracle JDBC 常见问题解答 | Oracle 中国 ↩

快来参与讨论吧~