Oracle Jdbc Get Generated Keys

13.12.2020
50 Comments

This chapter provides an overview of the Oracle implementation of Java Database Connectivity (JDBC), covering the following topics:

Overview of JDBC

JDBC is a Java standard that provides the interface for connecting from Java to relational databases. The JDBC standard is defined by Sun Microsystems and implemented through the standard java.sql interfaces. This allows individual providers to implement and extend the standard with their own JDBC drivers.

JDBC is based on the X/Open SQL Call Level Interface (CLI). JDBC 4.0 complies with the SQL 2003 standard.

Overview of the Oracle JDBC Drivers

In addition to supporting the standard JDBC application programming interfaces (APIs), Oracle drivers have extensions to support Oracle-specific data types and to enhance performance.

When a column name or column index array is used, Oracle JDBC drivers can identify which columns contain auto-generated keys that you want to retrieve. However, when the Statement.RETURNGENERATEDKEYS integer flag is used, Oracle JDBC drivers cannot identify these columns. Jun 24, 2013  Hi, I'm trying to get the primary key generated using ResultSet.getGeneratedKeys method on Oracle 11g using JDBC code. I tried ojdbc14.jar. Dec 19, 2004  The method Statement.getGeneratedKeys, which can be called to retrieve the value of such a key, returns a ResultSet object with a column for each automatically generated key. A flag indicating that any auto generated columns should be returned is passed to the methods execute, executeUpdate or prepareStatement when the statement is executed. If this Statement object did not generate any keys, an empty ResultSet object is returned. Note:If the columns which represent the auto-generated keys were not specified, the JDBC driver implementation will determine the columns which best represent the auto-generated keys.

How to Retrieve Automatically Generated Keys in JDBC? You can retrieve automatically generated keys (also called auto-generated keys or auto increment) from a table using JDBC 3.0 methods getGeneratedKeys.The getGeneratedKeys provide a standard way to make auto-generated or identity column values available to an application that is updating a database table without a requiring a query. Aug 23, 2013 Oracle Java JDBC: Get Primary Key of Inserted record by Viral Patel August 23, 2013 Here is a small write-up which should help those who still write plain Java JDBC code. Also, in JDBC 4.0 Sun added the rowid feature that allows you to get a unique handle on a row. The feature is supported by Oracle and DB2. For sql server you will probably need a third party driver such as this one.

Oracle provides the following JDBC drivers:

  • Thin driver

    It is a pure Java driver used on the client side, without an Oracle client installation. It can be used with both applets and applications.

  • Oracle Call Interface (OCI) driver

    It is used on the client-side with an Oracle client installation. It can be used only with applications.

  • Server-side Thin driver

    It is functionally similar to the client-side Thin driver. However, it is used for code that runs on the database server and needs to access another session either on the same server or on a remote server on any tier.

  • Server-side internal driver

    It is used for code that runs on the database server and accesses the same session. That is, the code runs and accesses data from a single Oracle session.

Figure 1-1 illustrates the architecture of the Oracle JDBC drivers and the Oracle Database.

Figure 1-1 Architecture of the Oracle JDBC Drivers and Oracle Database


Description of 'Figure 1-1 Architecture of the Oracle JDBC Drivers and Oracle Database'

This section covers the following topics:

Common Features of Oracle JDBC Drivers

The server-side and client-side Oracle JDBC drivers provide the same basic functionality.

The JDBC Thin and OCI drivers support the following versions of Java Development Kit (JDK): 1.2.x, 1.3.x and 1.4.x. The server-side Thin driver and server-side internal driver support JDK 1.4.1. All the JDBC drivers support the following standards and features:

  • Same syntax and APIs

  • Same Oracle extensions

  • Full support for multithreaded applications

Oracle JDBC drivers implement the standard java.sql interfaces. You can access the Oracle-specific features, in addition to the standard features, by using the oracle.jdbc package.

JDBC Thin Driver

The JDBC Thin driver is a pure Java, Type IV driver that can be used in applications and applets. It is platform-independent and does not require any additional Oracle software on the client side. The JDBC Thin driver communicates with the server using SQL*Net to access the Oracle Database.

The JDBC Thin driver allows a direct connection to the database by providing an implementation of SQL*Net on top of Java sockets. The driver supports the TCP/IP protocol and requires a TNS listener on the TCP/IP sockets on the database server.

See Also:

Chapter 6, 'Features Specific to JDBC Thin'

JDBC OCI Driver

The JDBC OCI driver is a Type II driver used with Java applications. It requires an Oracle client installation and, therefore, is Oracle platform-specific. It supports all installed Oracle Net adapters, including IPC, named pipes, TCP/IP, and Internetwork Packet Exchange/Sequenced Packet Exchange (IPX/SPX).

The JDBC OCI driver, written in a combination of Java and C, converts JDBC invocations to calls to OCI, using native methods to call C-entry points. These calls communicate with the database using SQL*Net.

The JDBC OCI driver uses the OCI libraries, C-entry points, Oracle Net, core libraries, and other necessary files on the client computer where it is installed.

OCI is an API that enables you to create applications that use the native procedures or function calls of a third-generation language to access Oracle Database and control all phases of the SQL statement processing.

See Also:

Chapter 7, 'Features Specific to JDBC OCI'

JDBC Server-Side Thin Driver

The JDBC server-side Thin driver offers the same functionality as the JDBC Thin driver that runs on the client side. However, the JDBC server-side Thin driven runs inside the Oracle Database and accesses a remote database or a different session on the same database.

This driver is useful in the following scenarios:

  • Accessing a remote database server from an Oracle Database instance acting as a middle tier

  • Accessing an Oracle Database session from inside another, such as from a Java stored procedure

The use of JDBC Thin driver from a client application or from inside a server does not affect the code.

See Also:

Chapter 6, 'Features Specific to JDBC Thin'

Permission for the Server-Side Thin Driver

The JDBC server-side Thin driver opens a socket for its connection to the database. Because Oracle Database enforces the Java security model, a check is performed for a SocketPermission object.

To use the JDBC server-side Thin driver, the connecting user must be granted the appropriate permission. The following is an example of how the permission can be granted for the user SCOTT:

Note that JDBCTHIN in the grant_permission call must be in uppercase. The asterisk (*) is a pattern. You can restrict the user by granting permission to connect to only specific computers or ports.

JDBC Server-Side Internal Driver

The JDBC server-side internal driver supports any Java code that runs inside the Oracle Database, such as in a Java stored procedures or Enterprise JavaBean (EJB), and must access the same database. It lets the Java virtual machine (JVM) to communicate directly with the SQL engine. The driver supports only JDK 1.4.1.

The JDBC server-side internal driver, the Oracle JVM, the database, and the SQL engine all run within the same address space, and therefore, the issue of network round trips is irrelevant. The programs access the SQL engine by using function calls.

Note:

The server-side internal driver does not support the cancel and

Oracle Jdbc Thin Driver

setQueryTimeout methods of the Statement class.

The JDBC server-side internal driver is fully consistent with the client-side drivers and supports the same features and extensions.

Choosing the Appropriate Driver

Consider the following when choosing a JDBC driver for your application or applet:

  • In general, unless you need OCI-specific features, such as support for non-TCP/IP networks, use the JDBC Thin driver.

  • If you want maximum portability and performance, then use the JDBC Thin driver. You can connect to the Oracle Database from either an application or an applet using the JDBC Thin driver.

  • If you want to use Lightweight Directory Access Protocol (LDAP) over Secure Sockets Layer (SSL), then use the JDBC Thin driver.

  • If you are writing a client application for an Oracle client environment and need OCI-driver-specific features, such as support for non-TCP/IP networks, then use the JDBC OCI driver.

  • If you are writing an applet, then you must use the JDBC Thin driver.

  • For code that runs in the database server acting as a middle tier, use the JDBC server-side Thin driver.

  • If your code will run inside the target database server, then use the JDBC server-side internal driver to access that server.

Feature Differences Between JDBC OCI and Thin Drivers

Table 1-1 lists the features that are specific either to the JDBC OCI or JDBC Thin driver in Oracle Database 10g release 2 (10.2).

Table 1-1 Feature Differences Between JDBC OCI and JDBC Thin Drivers

JDBC OCI DriverJDBC Thin Driver

OCI connection pooling

Support for applets

OCI optimized fetch

Default support for Native XA

Client-side object cache

Transparent Application Failover (TAF)

OCI Instant Client

Instant Client Light (English)

Strong authentication; Kerberos, PKI certificates


Notes:

  • The OCI optimized fetch and client-side object cache features are internal to the JDBC OCI driver and are not applicable to the JDBC Thin driver.

  • Most JDBC OCI driver features are not available in the JDBC Thin driver because they are inherited from OCI.

See Also:

Overview of Application and Applet Functionality

This section compares and contrasts the basic functionality of JDBC applications and applets. It also introduces the Oracle extensions that can be used by application and applet programmers. This sections covers the following topics:

Applet Basics

You can use only the Oracle JDBC Thin driver for an applet.

Applets and Security

Oracle Jdbc Get Generated Keys List

An applet can open network connections only to the host computer from which it was downloaded. Therefore, an applet can connect only to databases on the originating computer. If you want your applet to connect to a database running on a different computer, then you have the following options:

  • Use the Oracle Connection Manager on the host computer. The applet can connect to the Connection Manager, which connects to a database on another computer.

  • Use signed applets, which can request socket connection privileges to other computers.

Your applet can take advantage of the data encryption and integrity checksum features of the Oracle Advanced Security option.

Applets and Firewalls

An applet can connect to a database through a firewall.

See Also:

'Using Applets with Firewalls'

Packaging and Deploying Applets

To package and deploy an applet, you must place the JDBC Thin driver classes and the applet classes in the same .zip file.

Oracle Extensions

A number of Oracle extensions are available, which can be used by Oracle JDBC application and applet programmers. These extensions include:

Oracle jdbc get generated keys 2017

  • Type extensions, such as ROWID and REF CURSOR types

  • Wrapper classes for SQL types provided by the oracle.sql package

  • Support for custom Java classes to map to user-defined types

  • Extended large object (LOB) support

  • Extended connection, statement, and result set functionality

  • Performance enhancements

See Also:

Chapter 5, 'Oracle Extensions' and Chapter 25, 'Performance Extensions'

Server-Side Basics

By using the JDBC server-side internal driver, code that runs in the Oracle Database, such as Java stored procedures or EJBs, can access the database in which it runs.

Session and Transaction Context

The JDBC server-side internal driver operates within a default session and default transaction context.

Oracle Jdbc Get Generated Keys Download

See Also:

'Session and Transaction Context'.

Connecting to the Database

The JDBC server-side internal driver uses a default connection to the database. You connect to the database with the OracleDataSource.getconnection method.

Environments and Support

This section provides a brief discussion of the following topics:

Supported JDK and JDBC Versions

In Oracle Database 10g release 2 (10.2), all the JDBC drivers are compatible with JDK 1.2.x and later. JDK 1.0.x and JDK 1.1.x are no longer supported, and therefore, the classes111.zip, classes111.jar, classes111_g.zip, classes111_g.jar, and nls_charset11.zip files are no longer provided.

See Also:

'Compatibilities for Oracle JDBC Drivers'

JNI and Java Environments

The JDBC OCI driver uses the standard Java Native Interface (JNI) to call OCI C libraries. You can use the JDBC OCI driver with JVMs other than that of Sun Microsystems, in particular, with Microsoft and IBM JVMs.

JDBC and IDEs

The Oracle JDeveloper Suite provides developers with a single, integrated set of products to build, debug, and deploy component-based database applications for the Internet. The Oracle JDeveloper environment contains integrated support for JDBC, including the JDBC Thin driver and the native OCI driver. The database component of Oracle JDeveloper uses the JDBC drivers to manage the connection between the application running on the client and the server.

Oracle Jdbc Example

Changes At This Release

The Oracle implementation JDBC provides many enhancements in Oracle Database 10g. This section gives an overview of these enhancements. It is divided into the following sections:

New Features

Oracle Database 10g release 2 (10.2) supports the following new features:

  • DML returning

    The JDBC OCI and the JDBC Thin drivers support the data manipulation language (DML) returning feature. DML returning enables you to retrieve auto-generated keys along with other columns or values that your application may use.

  • JSR 114 RowSets

    Oracle Database 10g release 2 (10.2) provides support for all the RowSet implementations defined in the JDBC RowSet Implementations Specification (JSR-114). In particular, Oracle Database 10g release 2 (10.2) provides support for WebRowSet, FilteredRowSet, and JoinRowSet.

    See Also:

    Chapter 20, 'JDBC RowSets'

  • NCHAR literal support

    Oracle Database 10g release 2 (10.2) provides the NCHAR literal support. For further information, refer to the Note in 'NCHAR, NVARCHAR2, NCLOB and the defaultNChar Property'.

  • Proxy authentication

    In this release of Oracle Database, the proxy authentication feature is supported by both the JDBC Thin and JDBC OCI drivers.

  • Result set holdability

    Oracle JDBC drivers provide support for result set holdability, which is a feature of the JDBC 3.0 standard. This feature enables applications to decide whether the ResultSet objects should be open or closed, when an implicit or explicit commit operation is performed.

    See Also:

    'Result Set Holdability'

  • Retrieval of auto-generated keys

    Oracle Database 10g release 2 (10.2) provides support for the retrieval of auto-generated keys JDBC 3.0 standard feature. This feature enables you to retrieve values that are generated by the database.

  • Run-time connection load balancing

    The Oracle JDBC Thin and JDBC OCI drivers support the run-time connection load balancing feature. This feature enables routing of work requests to a database instance that offers the best performance, minimizing the need to relocate work.

    See Also:

    Chapter 24, ' Run-Time Connection Load Balancing'

  • SSL support

    Oracle Database 10g release 2 (10.2) provides support for the Secure Sockets Layer (SSL) Protocol.

  • XAConnection caching

    The implicit connection caching feature supports the caching of XA connections. This feature is supported by both the JDBC Thin and JDBC OCI drivers.

    See Also:

    Chapter 23, 'Implicit Connection Caching'

Desupported Features

No feature has been desupported in Oracle Database 10g release 2 (10.2). That is, Oracle Database 10g release 2 (10.2) supports all features that were supported in Oracle Database 10g release 1 (10.1).

Interface Changes

There are some changes in the setString, setCharacterStream, setAsciiStream, setBytes, and setBinaryStream methods of PreparedStatement in Oracle Database 10g release 2 (10.2).

There are three way to bind data for input:

  • Direct binding where the data itself is placed in a bind buffer

  • Stream binding where the data is streamed

  • LOB binding where a temporary lob is created, the data placed in the LOB using the LOB APIs, and the bytes of the LOB locator are placed in the bind buffer

The three kinds of binding have some differences in performance and have an impact on batching. Direct binding is fast and batching is fine. Stream binding is slower, may require multiple round trips, and turns batching off. LOB binding is very slow and requires many round trips. Batching works, but might be a bad idea. They also have different size limits, depending on the type of the SQL statement.

Jdbc

Oracle Jdbc Get Generated Keys Free

For SQL parameters, the length of normal parameter types, such as RAW and VARCHAR2, is fixed by the size of the target column. For PL/SQL parameters, the size is limited to a fixed number of bytes, which is 32512.

The original behavior of the APIs were:

  • setString: Direct bind of characters

  • setCharacterStream: Stream bind of characters

  • setAsciiStream: Stream bind of bytes

  • setBytes: Direct bind of bytes

  • setBinaryStream: Stream bind of bytes

In Oracle Database 10g release 2 (10.2), automatic switching between binding modes, based on the data size and on the type of the SQL statement is provided.

setBytes and setBinaryStream

For SQL, direct bind is used for size up to 2000 and stream bind for larger

For PL/SQL direct bind is used for size up to 32512 and lob bind is used for larger.

setString, setCharacterStream, and setAsciiStream

For SQL, direct bind is used up to 32766 Java characters and stream bind is used for larger. This is independent of character set.

For PL/SQL, you must be careful about the byte size of the character data in the database character set or the national character set depending on the setting of the form of use parameter. Direct bind is used for data where the byte length is less than 32512 and LOB bind for larger.

For fixed length character sets, multiply the length of the Java character data by the fixed character size in bytes and compare that to 32512. For variable length character sets, there are three cases based on the Java character length, as follows:

  • If character length is less than 32512 divided by the maximum character size, then direct bind is used.

  • If character length is greater than 32512 divided by the minimum character size, then LOB bind is used.

  • If character length is in between and if the actual length of the converted bytes is less than 32512, then direct bind is used, else LOB bind is used.

Note:

When a PL/SQL procedure is embedded in a SQL statement, the binding action is different. Refer to 'Data Interface for LOBs' for more information.

Feature List

Table 1-2 lists the features and the versions in which they were first supported for each of the three Oracle JDBC drivers: server-side internal driver, JDBC OCI driver, and JDBC Thin driver.

Table 1-2 Feature List

FeatureServer-Side InternalJDBC OCIJDBC Thin

JDK 1.0

7.2.2

7.2.2

JDBC 1.0.2

7.2.2

7.2.2

JDK 1.1.1

8.0.6

8.0.6

JDBC 1.22 (No new features; just minor revisions)

8.0.6

8.0.6

defineColumnType

8.0.6

8.0.6

Row Prefetch

8.0.6

8.0.6

Oracle Batching

8.0.6

8.0.6

JNI Native Interface

8.1.6

JDK 1.2

9.0.1

8.1.6

8.1.6

JDBC 2.0 SQL3 Types (BLOB, CLOB, Struct, Array, REF)

8.1.5

8.1.5

8.1.5

Native LOB

8.1.6

9.2.0

Index-By Tables

10.2.0

8.1.6

10.1.0

JDBC 2.0 Scrollable ResultSets

8.1.6

8.1.6

8.1.6

JDBC 2.0 Updatable ResultSets

8.1.6

8.1.6

8.1.6

JDBC 2.0 Standard Batching

8.1.6

8.1.6

8.1.6

JDBC 2.0 Connection Pooling

NA

8.1.6

8.1.6

JDBC 2.0 XA

8.1.6

8.1.6

8.1.6

Server-side Thin driver

8.1.6

NA

NA

JDBC 2.0 RowSets

9.0.1

9.0.1

Implicit Statement Caching

8.1.7

8.1.7

8.1.7

Explicit Statement Caching

8.1.7

8.1.7

8.1.7

Temporary LOBs

9.0.1

9.0.1

9.0.1

Object Type Inheritance

9.0.1

9.0.1

9.0.1

Multilevel Collections

9.0.1

9.0.1

9.0.1

oracle.jdbc Interfaces

9.0.1

9.0.1

9.0.1

Native XA

9.0.1

10.1.0

OCI Connection Pooling

NA

9.0.1

NA

TAF

NA

9.0.1

NA

NLS Support

9.0.1

9.0.1

9.0.1

JDK 1.3

9.2.0

9.2.0

9.2.0

JDK 1.4

10.1.0

9.2.0

9.2.0

JDBC 3.0 Savepoints

9.2.0

9.2.0

9.2.0

New Statement Caching API

9.2.0

9.2.0

9.2.0

ConnectionCacheImpl connection cache

NA

8.1.7

8.1.7

Implicit Connection Cache

NA

10.1.0

10.1.0

Fast Connection Failover

10.1.0.3

10.1.0.3

Connection Wrapping

9.2.0

9.2.0

DMS

9.2.0

9.2.0

Service Names in URLs

9.2.0

10.2.0

JDBC 3.0 Connection Pooling Properties

NA

10.1.0

10.1.0

JDBC 3.0 Updatable BLOB, CLOB, REF

10.1.0

10.1.0

10.1.0

JDBC 3.0 Multiple Open ResultSets

10.1.0

10.1.0

10.1.0

JDBC 3.0 Parameter Metadata

10.1.0

10.1.0

10.1.0

JDBC 3.0 Set/Get Stored Procedures Parameters by Name

10.1.0

10.1.0

10.1.0

JDBC 3.0 Statement Pooling

10.1.0

10.1.0

10.1.0

Set Statement Parameters By Name

10.1.0

10.1.0

10.1.0

End-to-End Tracing

10.1.0

10.1.0

Web RowSet

10.1.0

10.1.0

JDK 1.5

10.2.0

10.2.0

Proxy Authentication

10.2.0

10.1.0

JDBC 3.0 Auto Generated Keys

10.2.0

10.2.0

JDBC 3.0 Holdable Cursors

10.2.0

10.2.0

10.2.0

JDBC 3.0 Local/Global Transaction Switching

9.2.0

9.2.0

9.2.0

Run-time Connection Load Balancing

NA

10.2.0

10.2.0

Extended setXXX and getXXX for LOBs

10.2.0

10.2.0

XA Connection Cache /cd-key-call-of-duty-2-generator.html.

NA

10.2.0

10.2.0

DML Returning

10.2.0

10.2.0

JSR 114 RowSets

10.2.0

10.2.0

SSL Encryption

Ethereum wallet private key barcode generator. 9.2.0

10.2.0

SSL Authentication

9.2.0

Radius Authentication

10.2.0


Notes:

  • In the table, NA implies that the feature is not applicable for the corresponding Oracle JDBC driver.

  • The ConnectionCacheImpl connection cache feature is deprecated in Oracle Database 10g. Implicit Connection Cache replaces this in Oracle Database 10g.