Tibero and Oracle compatibility issues. Part 2. Java Application Development


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


    Spring Roo 2.0.0.RC2 came out last year. This is a java framework that can significantly reduce the time for developing prototypes of Spring MVC 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 THYMELEAF HTML page template engine, 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, MYSQLGRESASE, POST, ORSEBLE,


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


    Things are even more complicated 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 the Oracle database.



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


    Briefly describe the installation of Spring Roo. To work, Spring Roo 2.0.0.RC2 requires java 8 and a framework for automating and building Apache Maven projects, preferably the latest version. We take Apache Maven from 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 the executable catalogs of the frameworks in the path environment variable: C: \ app \ maven \ bin and C: \ app \ roo \ bin. On this, the installation of the necessary frameworks can be considered complete. After restarting the user session, we check the operability of the frameworks.


    In the C: \ app directory, create the project directory. We go into the C: \ app \ project directory 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 begin to create a project on Spring Roo. We will test the connection to the database specified in the title of the article using the hello project example from the Spring Roo documentation.


    In the project directory, create the hello directory where our project will be located. We go into the hello directory and type the roo command. If Spring Roo is installed correctly, then we should see the Spring Roo console prompt:


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

    Now, in the Spring Roo console, you can enter commands to create the 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 database name. If we defined the Oracle database, and not HYPERSONIC_IN_MEMORY, as in the documentation, you need to make additional manual settings for the hello project, and 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 driver version.


    In 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:


    com.oracleojdbc712.1.0.2.0jar

    And in the dependencies section:


    com.oracleojdbc7

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


    spring.datasource.driver-class-name=oracle.jdbc.OracleDriver
    spring.datasource.url=jdbc\:oracle\:thin\:@//\:1521/
    spring.datasource.password=<пароль>
    spring.datasource.username=<пользователь>
    spring.jpa.hibernate.ddl-auto=create-drop

    Because the application is a test application, so as not to clog the database with temporary tables, the last line configures hibernate to create tables at application startup and delete them at shutdown.


    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

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


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


    You can execute the application with the command:


    mvn spring-boot:run

    After which we will see the result of the application:



    Now consider what settings you need to make for the application to work with the Tibero database.


    First, you need to download and put the Tibero driver into the local maven jdbc repository:


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

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


    com.tmax.tiberotibero6-jdbc6.0jar

    And in dependencies:


    com.tmax.tiberotibero6-jdbc

    Make changes to the project application.properties settings:


    spring.datasource.driver-class-name=com.tmax.tibero.jdbc.TbDriver
    spring.datasource.url=jdbc\:tibero\:thin\:@\::
    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 dialect of the database. Tibero dialect not in hibernate library. However, it is known that Tibero database metadata is almost completely consistent with Oracle metadata, which allows you to specify the dialect of Oracle in the application settings. Hibernate, in this case, will work with the Tibero database as if it were an Oracle database.


    You can verify that the modified application is working as before with the command:


    mvn spring-boot:run

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


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


    You can request the version of the hibernate dialect you need at the Russian representative office of TmaxSoft.


    Let's take a closer look at installing a dialect library.


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


    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:


    com.tmax.tiberohibernate-tibero-dialect6.0jar

    And in dependencies:


    com.tmax.tiberohibernate-tibero-dialect

    Add a dialect class to the application.properties of the project:


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

    We verify the operability of the modified application:


    mvn spring-boot:run

    Now the application works with the 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.


    Also popular now: