- Oracle specific extensions for spring-jdbc
This project offers extensions to spring-jdbc that allow using the following Oracle specific features:
- Native named parameters
- Arrays
- Explicit statement caching
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>
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.
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 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
.
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.
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
.
RollbackSingleConnectionDataSource
is like SingleConnectionDataSource
but calls Connection#rollback()
before calling Connection#close()
to avoid commits in Oracle, see spring-framework#27249.
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.
- 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
. - Run
spring-jdbc-oracle-integrationtests/src/test/resources/run_oracle.sh
. If the image already rests rundocker start spring-jdbc-oracle
. Wait for a long time for the image to start. - Once Docker image is started, run the integrations tests , e.g
mvn integration-test
.