com.github.ferstl:spring-jdbc-oracle

A variant of Spring's JdbcTemplate that uses Oracle Update Batching. If Spring's classic JdbcTemplate is used in combination with an Oracle DB, the `batchUpdate()` methods won't return the number of affected rows. Instead, these methods do always return an array containing -2 (`Statement#SUCCESS_NO_INFO`) in each element. In order to get the number of affected rows during a batch INSERT/UPDATE/DELETE, it is required to use [Oracle Update Batching](http://docs.oracle.com/cd/B28359_01/java.111/b31224/oraperf.htm#autoId2).

License

License

GroupId

GroupId

com.github.ferstl
ArtifactId

ArtifactId

spring-jdbc-oracle
Last Version

Last Version

2.0.0
Release Date

Release Date

Type

Type

jar
Description

Description

A variant of Spring's JdbcTemplate that uses Oracle Update Batching. If Spring's classic JdbcTemplate is used in combination with an Oracle DB, the `batchUpdate()` methods won't return the number of affected rows. Instead, these methods do always return an array containing -2 (`Statement#SUCCESS_NO_INFO`) in each element. In order to get the number of affected rows during a batch INSERT/UPDATE/DELETE, it is required to use [Oracle Update Batching](http://docs.oracle.com/cd/B28359_01/java.111/b31224/oraperf.htm#autoId2).

Download spring-jdbc-oracle

How to add to project

<!-- https://jarcasting.com/artifacts/com.github.ferstl/spring-jdbc-oracle/ -->
<dependency>
    <groupId>com.github.ferstl</groupId>
    <artifactId>spring-jdbc-oracle</artifactId>
    <version>2.0.0</version>
</dependency>
// https://jarcasting.com/artifacts/com.github.ferstl/spring-jdbc-oracle/
implementation 'com.github.ferstl:spring-jdbc-oracle:2.0.0'
// https://jarcasting.com/artifacts/com.github.ferstl/spring-jdbc-oracle/
implementation ("com.github.ferstl:spring-jdbc-oracle:2.0.0")
'com.github.ferstl:spring-jdbc-oracle:jar:2.0.0'
<dependency org="com.github.ferstl" name="spring-jdbc-oracle" rev="2.0.0">
  <artifact name="spring-jdbc-oracle" type="jar" />
</dependency>
@Grapes(
@Grab(group='com.github.ferstl', module='spring-jdbc-oracle', version='2.0.0')
)
libraryDependencies += "com.github.ferstl" % "spring-jdbc-oracle" % "2.0.0"
[com.github.ferstl/spring-jdbc-oracle "2.0.0"]

Dependencies

compile (3)

Group / Artifact Type Version
com.github.ferstl : spring-jdbc-oracle-ojdbc Optional jar 2.0.0
org.springframework : spring-context jar 5.0.7.RELEASE
org.springframework : spring-jdbc jar 5.0.7.RELEASE

test (4)

Group / Artifact Type Version
junit : junit jar 4.12
org.hamcrest : java-hamcrest jar 2.0.0.0
org.mockito : mockito-core jar 2.13.0
org.springframework : spring-test jar 5.0.7.RELEASE

Project Modules

There are no modules declared in this project.

spring-jdbc-oracle

- Oracle specific extensions for spring-jdbc

Maven Central javadoc

This project offers extensions to spring-jdbc that allow using the following Oracle specific features:

  • Native named parameters
  • Arrays
  • Explicit statement caching

How to use spring-jdbc-oracle

Maven Dependencies

Binaries and source code of this project are available on Maven Central, so no further repository configuration is required. The maven setup looks like this:

    <!-- Dependency containing the OracleJdbcTemplate -->
    <dependency>
      <groupId>com.github.ferstl</groupId>
      <artifactId>spring-jdbc-oracle</artifactId>
      <version>2.0.0</version>
    </dependency>

Named Parameter Support

Oracle natively supports named parameters yet Spring's NamedParameterJdbcTemplate still ends up parsing and rewriting the query. This is unnecessary when proprietary Oracle vendor extensions are used.

OracleNamedParameterJdbcTemplate is a subclass of Spring's classic NamedParameterJdbcTemplate which overwrites the methods

  • int update(String, SqlParameterSource, KeyHolder, String[])
  • int[] batchUpdate(String, SqlParameterSource[])
  • PreparedStatementCreator.getPreparedStatementCreator(String, SqlParameterSource)

The classic NamedParameterJdbcTemplate contains a few more methods but all of them end up calling one of the three methods mentioned above.

Usage of the OracleNamedParameterJdbcTemplate

The OracleNamedParameterJdbcTemplate is a replacement for Spring's NamedParameterJdbcTemplate which works only on Oracle databases. You can use the OracleNamedParameterJdbcTemplate in almost the same way. The only difference is that collections are not supported, instead arrays with SqlOracleArrayValue have to be used:

    @Bean
    DataSource dataSource() {
      // Create a DataSource for your Oracle DB.
      // ...
    }
    
    @Bean
    public NamedParameterJdbcOperations namedParameterJdbcOperations() {
      return new OracleNamedParameterJdbcTemplate(dataSource());
    }

Array Parameter Support

Array support in Oracle is different from other databases in that Oracle does not support creating arrays from an element type, instead a new array type has to be created. This means that vendor extensions to JDBC have to be used to create java.sql.Array instances.

Arrays are a good replacement for dynamic IN lists.

this.jdbcOperations.query("SELECT * FROM some_table WHERE id IN (?)",
    rowMapper,
    new SqlOracleArrayValue("CUSTOM_ARRAY_TYPE", ids);

SqlOracleArrayValue can be used with either the standard JdbcTemplate or the OracleNamedParameterJdbcTemplate.

UUID Support

UuidOracleData and UuidOracleDataFactory allow reading and writing java.util.UUID objects as RAW(16). This is preferred over VARCHAR2(32) or VARCHAR2(36) because it is much more efficient.

Explicit Statement Caching

Besides the implicit statement caching, which caches a certain number of the most recently used statements, OJDBC also offers an explicit statement caching API that only caches statements upon request giving the user more control.

The CachedPreparedStatementCreator creates a PreparedStatement that will be cached under a predetermined key.

this.jdbcOperations.query(new CachedPreparedStatementCreator(cacheKey, SQL), rowMapper);

At the moment explicit statement caching can not be used with OracleNamedParameterJdbcTemplate because NamedParameterJdbcOperations does not offer any methods that take a PreparedStatementCreator.

Connection Pools

The project has been tested with these connection pools:

There are also integration tests working with these pools. See below for further details about running these tests.

How to run the Integration Tests

  1. Follow the instructions of Oracle Database on Docker to build a 19.3.0-se2 Docker image using ./buildDockerImage.sh -v 19.3.0 -s.
  2. Run spring-jdbc-oracle-integrationtests/src/test/resources/run_oracle.sh. If the image already rests run docker start spring-jdbc-oracle. Wait for a long time for the image to start.
  3. Once Docker image is started, run the integrations tests , e.g mvn integration-test.

Versions

Version
2.0.0
1.0.0
0.9.0