guide oracle
This section contains hints for those who use Oracle RDBMS. If you use a different persistence technology you can simply ignore it. Besides general hints about the driver there are tips for more tight integration with other Oracle features or products. However, if you work for a project where Oracle RDBMS is settled and not going to be replaced (you are in a vendor lock-in anyway), you might want to use even more from Oracle technology to take advantage from a closer integration.
For local development you should setup Oracle XE (eXpress Edition). You need an oracle account, then you can download it from here.
The most comfortable way to run it as needed is using docker. You can build your own docker image from the downloaded RPM using the instructions and dockerfile from oracle. (In case the build of the docker-image fails reproducibly and you want to give up with the Dockerfiles from Oracle you can also try this inofficial docker-oracle-xe solution).
To connect to your local XE database you need to use xe as the SID of your main database that can not be changed. The hostname should be localhost and the port is by default 1521 if you did not remap it with docker to something else. However, starting with XE 18c you need to be aware that oracle introduced a multi-tenant architecture. Hence xe refers to the root CDB while you typically want to connect to the PDB (pluggable database) and XE ships with exactly one of this called xepdb1. To connect with SQL Developer switch Connection Type from Basic to Advanced and enter the Custom JDBC URL like e.g.
jdbc:oracle:thin:@//localhost:1521/xepdb1The oracle JDBC driver is not available in maven central. Depending on the Oracle DB version and the Java version, you can use either the 11g/ojdbc6, 12c/ojdbc7, or 12c/ojdbc8 version of the driver. Oracle JDBC drivers usually are backward and forward compatible so you should be able to use the 12c/ojdbc8 driver with an 11g DB etc. As a rule of thumb, use the 12c/ojdbc8 driver unless you must use Java7. All JDBC drivers can be downloaded without registration: 11g/ojdbc6, 12c/ojdbc7, and 12c/ojdbc8. Your project should use a maven repository server such as nexus or artifactory. Your dependency for the oracle driver should look as follows (use artifactId "ojdbc6" or "ojdbc7" for the older drivers):
<dependency>
<groupId>com.oracle</groupId>
<artifactId>ojdbc8</artifactId>
<version>${oracle.driver.version}</version>
</dependency>In order to boost performance JDBC connections should be pooled and reused. If you are using Oracle RDBMS and do not plan to change that you can use the Oracle specific connection pool "Universal Connection Pool (UCP)" that is perfectly integrated with the Oracle driver. According to the documentation, UCP can even be used to manage third party data sources. The 11g version of UCP can be downloaded without registration here, the 12c version of UCP is available at the same download locations as the 12c JDBC driver (see above). As a rule of thumb, use the version that is the same as the JDBC driver version. Again, you have to upload the artefact manually to your maven repository. The dependency should look like this:
<dependency>
<groupId>com.oracle</groupId>
<artifactId>ucp</artifactId>
<version>${oracle.ucp.version}</version>
</dependency>Configuration is done via application.properties like this (example):
#Oracle UCP
# Datasource for accessing the database
spring.datasource.url=jdbc:oracle:thin:@192.168.58.2:1521:xe
spring.jpa.database-platform=org.hibernate.dialect.Oracle12cDialect
spring.datasource.user=MyUser
spring.datasource.password=ThisIsMyPassword
spring.datasource.driver-class-name=oracle.jdbc.OracleDriver
spring.datasource.schema=MySchema
spring.datasource.type=oracle.ucp.jdbc.PoolDataSourceImpl
spring.datasource.factory=oracle.ucp.jdbc.PoolDataSourceFactory
spring.datasource.factory-method=getPoolDataSource
spring.datasource.connectionFactoryClassName=oracle.jdbc.pool.OracleDataSource
spring.datasource.validateConnectionOnBorrow=true
spring.datasource.connectionPoolName=MyPool
spring.datasource.jmx-enabled=true
# Optional: Set the log level to INTERNAL_ERROR, SEVERE, WARNING, INFO, CONFIG, FINE, TRACE_10, FINER, TRACE_20, TRACE_30, or FINEST
# logging.level.oracle.ucp=INTERNAL_ERROR
# Optional: activate tracing
# logging.level.oracle.ucp.jdbc.oracle.OracleUniversalPooledConnection=TRACE
#Optional: Configures pool size manually
#spring.datasource.minPoolSize=10
#spring.datasource.maxPoolSize=40
#spring.datasource.initialPoolSize=20
Resources: FAQ, developer’s guide, Java API Reference. For an in-depth discussion on how to use JDBC and UCP, see the Oracle documentation Connection Management Strategies for Java Applications using JDBC and UCP.
Note: there is a bug in UCP 12.1.0.2 that results in the creation of thousands of java.lang.Timer threads over hours or days of system uptime (see article on stackoverflow). Also, Oracle has a strange bug fixing / patching policy: instead of producing a fixed version 12.1.0.3 or 12.1.0.2.x, Oracle publishes collections of *.class files that must be manually patched into the ucp.jar! Therefore, use the newest versions only.
In case you want to do messaging based on JMS you might consider the Oracle JMS also called Oracle Streams Advanced Queuing, or Oracle Advanced Queuing, or OAQ or AQ for short. OAQ is a JMS provider based on the Oracle RDBMS and included in the DB product for no extra fee. OAQ has some features that exceed the JMS standard like a retention time (i.e. a built-in backup mechanism that allows to make messages "unread" within a configurable period of time so that these messages do not have to be resent by the sending application). Also, OAQ messages are stored in relational tables so they can easily be process messages within the same technical transaction. They also might be observed by a test driver in a system test scenario.
This is possible only due to the fact that OAQ queues and RDBMS tables actually reside in the same database and quite straightforward to setup if you are using an Oracle connection pool.
If you are working with an application server or use a different connection pool, things get more complicated. Spring offers a (now deprecated project)Spring Data JDBC Extension in order to process OAQ messages within the same technical transaction even when using an application server.
Oracle sells commercial products and receives licence fees for them. This includes access to a support organization. Therefore, at an early stage of your project, prepare for contacting oracle support in case of technical problems. You will need the Oracle support ID of your customer [i.e. the legal entity who pays the licence fee and runs the RDBMS] and your customer must grant you permission to use it in a service request - it is not legal to use a your own support ID in a customer-related project. Your customer pays for that service anyway, so use it in case of a problem!
Software components like the JDBC driver or the UCP may be available without a registration or fee but they are protected by the Oracle Technology Network (OTN) License Agreement. The most important aspect of this licence agreement is the fact that an IT service provider is not allowed to simply download the Oracle software component, bundle it in a software artefact and deliver it to the customer. Instead, the Oracle software component must be (from a legal point of view) provided by the owner of the Oracle DB licence (i.e. your customer). This can be achieved in two ways: Advise your customer to install the Oracle software component in the application server as a library that can be used by your custom built system. Or, in cases where this is not feasible, e.g. in a OpenShift environment where the IT service provider delivers complete Docker images, you must advise your customer to (legally, i.e. documented in a written form) provide the Oracle software component to you, i.e. you don’t download the software component from the Oracle site but receive it from your customer.
This documentation is licensed under the Creative Commons License (Attribution-NoDerivatives 4.0 International).
