Tibero and Oracle compatibility issues. Part 2. Java application development

We continue the series of articles of database application developers - Part 1. Conditional compilation of PL / SQL . This article will cover the topic of using Tibero in Java applications using JDBC and Hibernate, as well as the Spring Roo framework.

Last year Spring Roo 2.0.0.RC2 was released. This is a java framework that significantly reduces the time for developing Spring MVC prototype applications that interact with databases (DB), SOAP and RESTful web services. It is also important that the latest version of the framework creates Spring Boot applications and uses the HTML template engine THYMELEAF, i.e. Unlike the previous version, Spring Roo 1.3.2 uses the latest technologies for developing java applications.

"Out of the box" Spring Roo 2.0.0 works with the following (DB): DB2_400, DB2_EXPRESS_C, DERBY_CLIENT, DERBY_EMBEDDED, FIREBIRD, H2_IN_MEMORY, HYPERSONIC_IN_MEMORY, HYPERSONIC_PERSISTENT, MSSQL, MYSQL, ORACLE, POSTGRES, SYBASE.

The ability to work Spring Roo not only with open source, but also with commercial databases significantly expands its area of ​​application. True with one caveat, commercial database drivers are not available in open repositories, they must be downloaded independently and placed in the local maven repository.

Even more difficult is the situation for commercial databases that are not provided out of the box in Spring Roo, for example, the Tibero database, which has recently been used as a replacement for Oracle databases.

In our article, we show that working with such databases is not much more difficult than with commercial databases the settings for which are contained in Spring Roo.

Briefly describe the installation of Spring Roo. Spring Roo 2.0.0.RC2 requires java 8 and a framework to automate and build Apache Maven projects, preferably the latest version. Apache Maven we take from the site maven.apache.org. Spring Roo 2.0.0.RC2 from projects.spring.io/spring-roo.

Both frameworks are downloaded as zip archives and deployed, for example for windows, to the C: \ app \ maven and C: \ app \ roo directory, respectively. It is necessary to register paths to executable directories of frameworks in the path environment variable: C: \ app \ maven \ bin and C: \ app \ roo \ bin. At this installation of the necessary frameworks can be considered complete. After restarting the user session, we check the performance of the frameworks.

In the C: \ app directory, create the project directory. Go to the directory C: \ app \ project and type the command: mvn - version. If the Apache Maven framework is installed correctly, then we will see the version of the framework itself and java:

Apache Maven 3.5.0 (...) Maven home: C:\app\maven\bin\.. Java version: 1.8.0_121, vendor: Oracle Corporation Java home: C:\Program Files\Java\jdk1.8.0_121\jre Default locale: ru_RU, platform encoding: Cp1251 OS name: "windows 7", version: "6.1", arch: "amd64", family: "windows" 

After installing the necessary frameworks, you can start creating a project on Spring Roo. We will test the connection to the database specified in the article title using the example of the hello project from the Spring Roo documentation.

In the project directory, create a directory hello, where our project will be located. Go to the hello directory and type the roo command. If Spring Roo is installed correctly, then we should see the invitation from the Spring Roo console:

  _ ___ _ __ _ __(_)_ __ __ _ _ __ ___ ___ / __| '_ \| '__| | '_ \ / _` | | '__/ _ \ / _ \ \__ \ |_) | | | | | | | (_| | | | | (_) | (_) | |___/ .__/|_| |_|_| |_|\__, | |_| \___/ \___/ |_| |___/ 2.0.0.RC2 Welcome to Spring Roo. For assistance press TAB or type "hint" then hit ENTER. roo> 

Now in the console of Spring Roo you can enter commands to create a project:

 project setup --topLevelPackage com.foo jpa setup --provider HIBERNATE --database ORACLE 

The first line defines the name of the application package, the second defines the ORM provider and the name of the database. If we have defined the Oracle database, not HYPERSONIC_IN_MEMORY, as in the documentation, it is necessary to make additional manual configuration of the project hello, then return to the roo script commands.

The first thing to do is download the Oracle jdbc driver: ojdbc7.jar and load it into the local maven repository:

 mvn install:install-file -Dfile=ojdbc7.jar -DgroupId=com.oracle -DartifactId=ojdbc7 -Dversion=12.1.0.2.0 -Dpackaging=jar 

Next, in the pom.xml project file, replace the dependency formed by roo with your own, which takes into account the jdbc version of the driver.

In the Spring Roo projects, the dependency for the jdbc driver needs to be written in two sections of the pom.xml file. In the dependencyManagement section:

 <dependency> <groupId>com.oracle</groupId> <artifactId>ojdbc7</artifactId> <version>12.1.0.2.0</version> <type>jar</type> </dependency> 

And in the dependencies section:

 <dependency> <groupId>com.oracle</groupId> <artifactId>ojdbc7</artifactId> </dependency> 

Then make the settings in the project application.properties. Specify driver class name, connection string, database user name and password:

 spring.datasource.driver-class-name=oracle.jdbc.OracleDriver spring.datasource.url=jdbc\:oracle\:thin\:@//<ip>\:1521/<name > spring.datasource.password=<> spring.datasource.username=<> spring.jpa.hibernate.ddl-auto=create-drop 

Since a test application, so as not to clutter the database with temporary tables, the last line configures hibernate to create tables at the start of the application and delete them on completion.

After making these changes to the project, you can continue to create the hello application from the Spring Roo command line:

 entity jpa --class ~.domain.Timer field string --fieldName message --notNull repository jpa --all service --all web mvc setup web mvc view setup --type THYMELEAF web mvc controller --all --responseType THYMELEAF web mvc controller --all --pathPrefix /api 

Briefly describe the action of these commands. The first two lines create the Timer entity with one message field, which cannot be empty. Next, create a repository for all entities and services. In this application, one entity - Timer. The last lines set up the web part of the application, created on the basis of Spring MVC and templates THYMELEAF.

Everything, a simple application that allows you to view and edit the entity Timer, saving data in an Oracle database created.

You can execute the application with the command:

 mvn spring-boot:run 

Then we will see the result of the application:

Let us now consider what settings need to be done in order for the application to work with the Tibero database.

First, you need to download and place the Tibero driver in the maven jdbc local repository:

 mvn install:install-file -Dfile=tibero6-jdbc.jar -DgroupId=com.tmax.tibero -DartifactId=tibero6-jdbc -Dversion=6.0 -Dpackaging=jar 

Next, replace the Oracle jdbc driver with Tibero in the dependenciesManagement section pom.xml dependencies:

 <dependency> <groupId>com.tmax.tibero</groupId> <artifactId>tibero6-jdbc</artifactId> <version>6.0</version> <type>jar</type> </dependency> 

And in dependencies:

 <dependency> <groupId>com.tmax.tibero</groupId> <artifactId>tibero6-jdbc</artifactId> </dependency> 

Make changes to the project application.properties settings:

 spring.datasource.driver-class-name=com.tmax.tibero.jdbc.TbDriver spring.datasource.url=jdbc\:tibero\:thin\:@<ip>\:<port>:<dbname> spring.datasource.password=<> spring.datasource.username=<> spring.jpa.hibernate.ddl-auto=create-drop spring.jpa.database-platform=org.hibernate.dialect.Oracle10gDialect 

The last line of the configuration indicates the hibernate database dialect. Tibero dialect is not in the hibernate library. However, it is known that the metadata of the Tibero database almost completely corresponds to the Oracle metadata, which allows you to specify the Oracle dialect in the application settings. Hibernate, in this case, will work with the Tibero database as if it were an Oracle database.

You can make sure that the modified application works as before with the command:

 mvn spring-boot:run 

The application now works with a commercial Tibero database using the Oracle dialect.

Another way to customize the hibernate dialect is to load a special dialect library. For example, for Tibero, this is hibernate-tibero-dialect.jar. The version of the dialect depends on the version of hibernate for which the library is intended.

You can request the version of the hibernate dialect required by you in the Russian representative office of TmaxSoft.

Consider the installation of the dialect library.

First, you need to download and place in the local maven repository the hibernate dialect library for Tibero:

 mvn install:install-file -Dfile=hibernate-tibero-dialect.jar -DgroupId=com.tmax.tibero -DartifactId=hibernate-tibero-dialect -Dversion=6.0 -Dpackaging=jar 

Then add the dialect library to pom.xml, the dependencyManagement section:

 <dependency> <groupId>com.tmax.tibero</groupId> <artifactId>hibernate-tibero-dialect</artifactId> <version>6.0</version> <type>jar</type> </dependency> 

And in dependencies:

 <dependency> <groupId>com.tmax.tibero</groupId> <artifactId>hibernate-tibero-dialect</artifactId> </dependency> 

Introduce the dialect class in the application.properties of the project:

 spring.jpa.database-platform=org.hibernate.dialect.TiberoDialect 

We are convinced of the performance of the modified application:

 mvn spring-boot:run 

The application now works with a commercial Tibero database using the hibernate dialect library for Tibero.

Continue to follow our publications, in which we try to share with you the experience of using Tibero.