Focus On Oracle

Installing, Backup & Recovery, Performance Tuning,
Troubleshooting, Upgrading, Patching

Oracle Engineered System


当前位置: 首页 » 技术文章 » Oracle 12c+

Getting started with Oracle JDBC

Oracle JDBC是连接Orace数据库的驱动程序,截止目前Oracle的数据库的最新版是Oracle Database 18C。数据库在与时俱进,你的程序也该升级了,下面是从Oracle MOS中转载过来的,大家可以看看该怎么和你的数据库匹配,比如要连接你相应版本的数据库,你需要用那个版本的jdbc。


Introducing Oracle JDBC drivers

What are Oracle JDBC drivers ?

JDBC is an API that allows Java programs to access any tabular data source. In order to interact with an Oracle Database from a Java program using JDBC interface, Oracle provides its own Oracle JDBC drivers.

What types of JDBC drivers does Oracle provide?

Oracle supplies 4 driver types:

JDBC Thin client-side driver (Thin driver):
The Oracle JDBC Thin driver is a JDBC Type 4 driver(written entirely in Java). The JDBC Thin driver uses Java Sockets to connect directly to the Oracle Server. The JDBC Thin driver provides its own implementation of a TCP/IP version of Oracle's SQL*Net/Net8. As it is written entirely in Java, this driver is platform-independent.The Thin driver is for client-side use and does not require Oracle software on the client side.
JDBC OCI client-side driver (OCI driver):
Oracle's JDBC OCI driver provides a JDBC Type 2 driver (native-API, partly Java) implementation of the JDBC interfaces using OCI (Oracle Call Interface) to interact with an Oracle database. As the JDBC OCI driver uses native methods to call C entry points in the OCI library, it is platform-specific. It also requires an Oracle client (or Instant Client) installation including Net8.Because the JDBC OCI driver interfaces to Oracle databases through OCI, it is compatible with all Oracle database versions, and supports all installed Net8 adapters, including IPC, named pipes, TCP/IP, and SPX/IPX among others.
JDBC server-side Internal driver (KPRB driver):
This JDBC Type 2 driver can be used by Java code that runs inside the oracle database server JVM, such as Java stored procedures. The JDBC server driver allows Java programs to communicate with the internal SQL engine inside Oracle and access the underlying database objects.
JDBC Thin server-side driver:
This JDBC Type 4 driver allows Java program running inside an Oracle database server JVM to access remote databases.

What are the Oracle JDBC versions ?

The Oracle JDBC driver is shipped with the Oracle database server.
Starting with 9.2.0.1  database version, the Oracle JDBC driver versions are :

JDBC 9.2.0.X
JDBC 10.1.0.X
JDBC 10.2.0.X
JDBC 11.1.0.X
JDBC 11.2.0.X
JDBC 12.1.0.X
JDBC 12.2.0.x

The supported JDBC drivers versions are :  12.2, 12.1 , and 11.2 .

What are the main Oracle JDBC driver Java classes libraries ?

The basic Java libraries to include in the CLASSPATH of your application are ojdbc8.jar, ojdbc7.jar, ojdbc6.jar, ojdbc5.jar, ojdbc14.jar or  classes12.jar.

ojdbc8.jar
Java classes when using the JDBC Thin and OCI client-side driver - with Java 8.0 VM.
ojdbc7.jar
Java classes when using the JDBC Thin and OCI client-side driver - with Java 7.0 VM.
ojdbc6.jar
Java classes when using the JDBC Thin and OCI client-side driver - with Java 6.0 VM.
ojdbc5.jar
Java classes when using the JDBC Thin and OCI client-side driver - with Java 5.0 VM.
ojdbc14.jar
Java classes when using the JDBC Thin and OCI client-side driver - with Java 1.4 or 5.0 VM. With Java 5.0 VM, you can use this library if the JDBC version is 10.2.
classes12.jar
Same as ojdbc14.jar except for use with with Java 1.2 or 1.3 VM.
classes12.zip
Same as classes12.jar except in zip format. This file will almost certainly not be available in future releases. You should use classes12.jar instead.

If your application is using Advanced Data Types (objects) either the nls_charset12.jar or orai18n.jar libraries have to be added in your application CLASSPATH.

nls_charset12.jar
Classes to support all Oracle character sets in Advanced Data Types (objects) when using Java 1.2, 1.3 or 1.4 VM. Not used with Oracle 10.1.0 and later. Use orai18n.jar instead.
nls_charset12.zip
Same as nls_charset12.jar except in zip format.
orai18n.jar
Same as nls_charset12.jar but used with Oracle 10.1.0 and later. The orai18n.jar library is not delivered as part of the JDBC drivers but located in [ORACLE_HOME]/jlib. This library is available on the JDBC download page

If your application is using the SQL/XML database data type, that is, java.sql.SQLXML, xdb6.jar library  has to be added in your application CLASSPATH.

xdb6.jar
Classes to support standard JDBC 4.x java.sql.SQLXML interface (Java SE 6 & Java SE 7).

What is the JDBC OCI native shared library ?

The native JDBC OCI library is :

On Unix, libocijdbc<N>.so
On Windows, ocijdbc<N>.dll (Windows) where

N is 9 for JDBC 9.x.x drivers
N is 10 for JDBC 10.x.x drivers
N is 11 for JDBC 11.x.x drivers
N is 12 for JDBC 12.x.x drivers

This shared library will call the OCI interface to access the database. Hence, when using the JDBC OCI driver, the JDBC shared library must be found via the environment variable PATH on Windows, or LD_LIBRARY_PATH on Solaris.
The OCI shared library should also match the type of JVM being used to host the java program. So,  a 32 bit (/64 bit) JVM requires a 32 bit (/64 bit)  OCI shared library.

Which JDBC driver version goes with which JDK version ?

From JDBC FAQ on Oracle Technology Network (OTN), the OCI or Thin JDBC drivers versions support the following Javasoft's JDK versions :

JDBC Version

JDK version

JDBC File Name

12.2.0

8.x

ojdbc8.jar

12.1.0

  8.x  
7.x
6.x

ojdbc7.jar
ojdbc7.jar
ojdbc6.jar

11.2.0

     8.x **
     7.x **
6.x
5.x

ojdbc6.jar
ojdbc6.jar
ojdbc6.jar
ojdbc5.jar

11.1.0

6.x
5.x

ojdbc6.jar
ojdbc5.jar

10.2.0

1.2.x
1.3.x
1.4.x
5.x

classes12.jar
classes12.jar
ojdbc14.jar
ojdbc14.jar

10.1.0

1.2.x
1.3.x
1.4.x

classes12.jar
classes12.jar
ojdbc14.jar

9.2.0

1.1.x
1.2.x
1.3.x
1.4.x

classes111.zip*
classes12.zip*
classes12.zip*
ojdbc14.jar

* As of version 9.2.0.7.0 these ZIP-files are replaced by JAR-files.

** Only Oracle JDBC driver 11.2.0.3  (and greater)  versions support JDK 7 and JDK  8.

EXAMPLE:
- if JDK version is 6.0.x, for 11.2 JDBC version, the JDBC library to use is the ojdbc6.jar file.
- if JDK version is 5.0.x, for 10.2 JDBC version, the JDBC library to use is the ojdbc14.jar file.

Notes
- The 11g JDBC drivers are certified with JDK 5 or JDK 6 only.
- In Oracle Database 12c Release 2 (12.2.0.1), Oracle JDBC drivers no longer support JDK 7 or earlier versions.
- Note also this Product Obsolescence Desupport Notice, concerning the desupport of the classes12.jar:
     Note 335754.1 : JDBC 10.2 - classes12.jar , oracle.jdbc.driver, and OracleConnectionCacheImpl

Since Server Thin Driver and Server Internal Driver are running within the Oracle Server, those drivers support the Oracle JVM version:

Oracle 9i R1 supports J2SE 1.2
Oracle 9i R2 supports J2SE 1.3
Oracle 10g supports J2SE 1.4
Oracle 11g supports J2SE 1.5

Which JDBC driver version is certified to connect to which database version?

For the correct information always check the JDBC Developer's Guide and Reference for the JDBC version you are using. In the JDBC Developers Guide and Reference 11g Release 1 (11.1), it is stated:

Backward Compatibility
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.
Forward Compatibility
Existing and supported JDBC drivers are certified to work with Oracle Database 12c Release 1 (12.1).
Although the main features will work it can happen that new features of a database version
cannot be used by older drivers. Therefore, it is recommended to use at least the same version
for the JDBC Driver as the version of the database

Also, you can check the interoperability support between Oracle Client (Oracle JDBC driver) version and Oracle Server version by referring to the following note:
Note 207303.1 : Client / Server / Interoperability Support Between Different Oracle Versions

Here is a table giving which JDBC drivers can access which Oracle database :

JDBC drivers

Oracle database

12.2.0

12.2.0
12.1.0
11.2.0

12.1.0

12.2.0
12.1.0
11.2.0
11.1.0

11.2.0

12.2.0
12.1.0
11.2.0
11.1.0
10.2.0
10.1.0
9.2.0

11.1.0

12.1.0
11.2.0
11.1.0
10.2.0
10.1.0
9.2.0

10.2.0

12.1.0
11.2.0
11.1.0
10.2.0
10.1.0
9.2.0

10.1

11.2.0
11.1.0
10.2.0
10.1.0
9.2.0

9.2.0

11.2.0
11.1.0
10.2.0
10.1.0
9.2.0

 

How to get the Java JDBC libraries ?

The Java JDBC libraries can be obtained in three ways:

1. After installing an Oracle client or server.
    Those files are located in <ORACLE_HOME>/jdbc/lib directory.
2. After installing the Oracle Instant Client.

3. Downloading the Jar files available on the JDBC download page.

Note: The Java JDBC libraries are platform independent. So, you can get those libraries from any <ORACLE_HOME>/jdbc/lib directory or JDBC download page  and use them on any platform with the JDBC Thin and OCI  Driver Type.

Moreover, Oracle JDBC driver can be embedded in  Oracle products such as  Fusion Middleware products.  In this case,  for Oracle Jdbc driver certification or upgrade questions, you might refer to documentation related to those Oracle products.

How to install the JDBC driver ?

JDBC Thin driver
Store the Java JDBC libraries and set the classpath with the adequate libraries.
JDBC OCI driver
Install Oracle client or server related to the JDBC driver.
Starting with 10.1.0, you can also install the Oracle Instant Client (which is a subset of the Oracle client) as an alternative to the Oracle Client. Store the JDBC Java libraries and set the CLASSPATH with the adequate libraries.
Note that the Oracle client must have exactly the same version as the JDBC driver used.
KPRB driver
With the Java option in the database, the JDBC driver is already available.
Thin server-side driver
With the Java option in the database, the JDBC driver is already available.

Where can you find JDBC driver resources as documentation, Javadoc and samples?

Related to a JDBC driver version,  the following resources are available :

JDBC Developers Guide and Reference
which can be accessible from Oracle's online documentation.

As example, Oracle Database JDBC Developer's Guide and Reference 11g Release 2 (11.2)

or Oracle Database 

JDBC Developer's Guide and Reference 12c Release 1 (12.2)

Readme.txt
After installing JDBC, within the <ORACLE HOME>\jdbc directory is a Readme.txt  file that contains information such as:

New Features
Major Bug Fixes
Driver Versions
Setting Up Your Environment
Some Useful Hints

JDBC Javadoc
The JDBC api can be found in <ORACLE_HOME>\jdbc\doc as javadoc.zip
JDBC demos
Samples illustrating the different JDBC features which can be delivered in <ORACLE_HOME>/jdbc/demo directory as demo.tar or demo.zip.

The JDBC javadoc and demos Readme files are also available on the JDBC download page. More information can also be found on the Oracle JDBC page.

Which Oracle JDBC driver can you start with ?

The 10g JDBC Thin driver supports almost the same features as the JDBC OCI driver (few exceptions as TAF feature only supported by the Oracle JDBC OCI driver) . It requires JDBC Java libraries only (whereas the JDBC OCI driver  requires a full Oracle or Instant Client installation). Since 10g JDBC Thin driver version, the Oracle JDBC driver is using a faster protocol (OCI/TTC 8) to access the Oracle database. One constraint with JDBC Thin Driver is to use TCP/IP protocol to access the database server.

Hence, a good option is to start with  the  (10g or)  11g R2  or 12c R1 JDBC Thin driver.

Running a JDBC application

How to setup environment to run a JDBC application ?

In order to run a client-side JDBC Program (OCI or Thin type), the Oracle JDBC Java libraries have to be added to your CLASSPATH.
If you are using the Oracle JDBC OCI driver, it requires an Oracle client installation of the exact same version as the JDBC driver. Depending on your platform where your JDBC program is running, add to the PATH (windows), LD_LIBRARY_PATH (unix) or LIBPATH (aix) the directory where the Oracle JDBC OCI shared libraries are stored. 
Besides, on 64 bit platforms you might have a choice as to whether you use a 32 bit JVM - that is that the java executable is 32 bit, or a 64 bit JDK - that is that the java executable is 64 bit.
If using 64 bit JVM with the Oracle JDBC OCI driver, the JDBC OCI shared libraries have to be 64 bit libraries.
Similarly, if using 32 bit JVM with the Oracle JDBC OCI driver, the JDBC OCI shared libraries have to be 32 bit libraries. 

Here are sample settings for using 10.2 JDBC driver :

On Windows platforms:

- Add [ORACLE_HOME]\jdbc\lib\classes12.jar to your CLASSPATH if you use JDK 1.2 or 1.3.
  Add [ORACLE_HOME]\jdbc\lib\ojdbc14.jar to your CLASSPATH if you use JDK 1.4.
- Add [ORACLE_HOME]\jlib\orai18n.jar to your CLASSPATH if needed.
- Add [ORACLE_HOME]\bin to your PATH if you are using the JDBC OCI driver.

On Solaris Unix:

- Add [ORACLE_HOME]/jdbc/lib/classes12.jar to your CLASSPATH if you use JDK 1.2 or 1.3.
  Add [ORACLE_HOME]/jdbc/lib/ojdbc14.jar to your CLASSPATH if you use JDK 1.4.
- Add [ORACLE_HOME]/jlib/orai18n.jar to your CLASSPATH if needed.
- Add [ORACLE_HOME]/lib to your LD_LIBRARY_PATH if you use the JDBC OCI driver. 

On solaris 64 bit platform with 64 bit oracle installation:
- Add $ORACLE_HOME/lib32 to your LD_LIBRARY_PATH if using 32-bit JVM to run the Oracle JDBC OCI driver.
- Add $ORACLE_HOME/lib to your LD_LIBRARY_PATH if using 64-bit JVM to run the Oracle JDBC OCI driver.

Which Oracle JDBC packages can you import ?

You can import the following packages:

oracle.jdbc.*;
oracle.jdbc.pool.*;
oracle.sql.*;

But you should not import the package oracle.jdbc.driver.*.  It has been deprecated since 9iR1 and will be de-supported.
You can refer to this Product Obsolescence Desupport Notice:
Note 335754.1 : JDBC 10.2 - classes12.jar , oracle.jdbc.driver, and OracleConnectionCacheImpl

How to get a JDBC connection  using Oracle JDBC driver API ?

Initially, after registering the oracle JDBC driver, a JDBC connection can be opened by invoking method getConnection() of  the class java.sql.DriverManager:

...
DriverManager.registerDriver(new oracle.jdbc.OracleDriver() );
Connection conn = DriverManager.getConnection( dbUrl, dbUser, dbPassword);
...

Using the getConnection method of OracleDataSource class allows you more flexibility:

...
ds = new oracle.jdbc.pool.OracleDataSource();
ds.setURL(dbUrl);
Connection conn = ds.getConnection(dbUser,dbPassword);
...

From 10g JDBC driver, this  allows you to use the Implicit Connection caching mechanism.

Starting from Oracle Database 11g Release 2 (11.2),  Implicit Connection caching feature has been deprecated. And, Oracle recommends to use Universal Connection Pool (UCP) for JDBC.

What is the Oracle JDBC connection URL ?

The connection URL defines the address of the Oracle database server to connect:
            jdbc:oracle:<drivertype>:<username/password>@<database>
According to the Oracle driver type, the form of the URL is the following :

oci type
jdbc:oracle:oci:@<database>

where database can be:

·  an entry name defined in the tnsnames.ora
This might require that you  set the TNS_ADMIN environment variable.
·  a descriptor that specifies the location of a listener and the service name
·  //<host>[:<port>]/<service>
where
<host> is the host name or the ip address of the Oracle database server
<port> is the port number of the Oracle listener
<service> is Oracle service name
of the database.
Thin type
jdbc:oracle:thin:@<database>

where database can be:

·  a descriptor that specifies the location of a listener and the service name
·   //<host>[:<port>]/<service>
where
<host> is the host name or the IP address of the Oracle database server
<port> is the port number of the Oracle listener
<service> is Oracle service name
of the database.
kprb type : jdbc:oracle:kprb:@

Can a JDBC connection be shared by two Java threads simultaneously ?

Only one thread should access a JDBC connection and related objects at a time:

"Avoid allowing multiple threads to access a connection simultaneously. If multiple threads must share a connection, use a disciplined begin-using/end-using protocol."
(Coding Tips from JDBC Developers Guide and Reference)

Join the Java Development MOS Community forum for general discussions, questions, best practices, and other valuable information on: Oracle JDeveloper and ADF, Oracle WebLogic - JEE Programming (EJB, JMS etc), Oracle JDBC, Oracle Web Services (incl. DBWS Callout Utility), Oracle Web Services Manager (OWSM), Oracle Service Registry (OSR), Oracle Toplink (EclipseLink), Sun NetBeans IDE / Java Studio Creator & Java Studio Enterprise, OC4J, KODO.

References

http://www.oracle.com/technetwork/database/features/jdbc/index-091264.html

http://www.oracle.com/technetwork/database/application-development/jdbc/overview/index.html

http://www.oracle.com/technetwork/database/application-development/jdbc/documentation/doc-3320539.html


关键词:jdbc oracle 

相关文章

Oracle数据库备份云服务
Oracle RAC and Third Party Cloud
Open Source Languages and Oracle Database
Oracle SQL Developer Command Line (SQLcl)
Oracle Database Multilingual Engine(MLE)
Database Features A-Z
Oracle JET
Do migration with Oracle SQL Developer
Oracle JDBC/UCP FAQ
Getting started with Oracle JDBC
How to generate AWR on PDB and ADG(12.2 afterwards)
Oracle ZDLRA (Zero Data Lost Recovery Appliance) Features

联系我们

韩朝阳



    Phone:186-9589-1286

    E-mail:ohsdba@qq.com

    微  信: ohsdba


电话(186-9589-1286)

QQ(375349564)

微信(ohsdba)


常用链接

Oracle
eDelivery
My Oracle Support

Exadata 12.1 Document

Exadata 12.2 Document

Oracle 11gR2 Document

Oracle 12cR1 Document

Oracle 12cR2 Document

GoldenGate 12c

Oracle Fusion Middleware

Mysql Document

PostgreSQL

Python

github

sourceforge



Top
Cell186-9589-1286
QQ 375349564
E-mail ohsdba@qq.com