jhipster – Using Oracle Database

mayo 7, 2015

UPDATE: as michaelisvy noted in the comments below, there is now support for Oracle in JHipster. See here: http://jhipster.github.io/using_oracle.html

Jhipster doesn’t give the option to use an Oracle Database yet. Probably because most projects use an open source, or at least free, database.
We need to make some changes to the initial code generated by Jhipster in order to use an oracle database.

First, we need to include a dependency on the Oracle JDBC driver in the pom.xml file.
Before doing this we need to install the library in our Maven Repository Manager (artifactory, nexus, etc) or locally by downloading the ojdbc6.jar and installing it with the command mvn install:install-file -DgroupId=com.oracle -DartifactId=ojdbc6 -Dversion=11.2.0.2.0 -Dpackaging=jar -Dfile=ojdbc6.jar -DgeneratePom=true

<dependency>
            <groupId>com.oracle</groupId>
            <artifactId>ojdbc6</artifactId>
            <version>11.2.0.2.0</version>
        </dependency>

and then we need to configure the datasource and jpa section in the application-dev.yml file

spring:
    profiles:
        active: dev
    datasource:
        driverClassName: oracle.jdbc.OracleDriver
        dataSourceClassName: oracle.jdbc.pool.OracleDataSource
        # read from env vars SPRING_DATASOURCE_*
        url:
        username:
        password:

    jpa:
        database-platform: org.hibernate.dialect.Oracle10gDialect
        database: ORACLE
        openInView: false
        ...

at last we need to change the initial schema (src/main/resources/config/liquibase/changelog/00000000000000_initial_schema.xml). Shorten index names to a maximum of 30 characters, add ‘sysdate’ for value of ‘now’ property, restrict length of varchar columns to 4000 instead of 4096, set ‘autoIncrement’ on columns to false and use sequences instead (with triggers) -In Oracle 12c, you can now specify the CURRVAL and NEXTVAL sequence pseudocolumns as default values for a column-

Here is my 00000000000000_initial_schema.xml file

<?xml version="1.0" encoding="utf-8"?>
<databaseChangeLog
    xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.1.xsd">

    <property name="now" value="now()" dbms="mysql,h2"/>
    <property name="now" value="current_timestamp" dbms="postgresql"/>
    <property name="now" value="sysdate" dbms="oracle"/>

    <property name="autoIncrement" value="true" dbms="mysql"/>
    <property name="autoIncrement" value="false" dbms="oracle"/>

    <changeSet id="00000000000000" author="jhipster">
        <createSequence sequenceName="hibernate_sequence" startValue="1000" incrementBy="1"/>
    </changeSet>

    <!--
        JHipster core tables.
        The initial schema has the '00000000000001' id, so that it is over-written if we re-generate it.
    -->
    <changeSet id="00000000000001" author="jhipster">
        <createTable tableName="T_USER">
            <column name="id" type="bigint" autoIncrement="${autoIncrement}">
                <constraints primaryKey="true" nullable="false"/>
            </column>
            <column name="login" type="varchar(50)">
                <constraints unique="true" nullable="false"/>
            </column>
            <column name="password" type="varchar(100)"/>
            <column name="first_name" type="varchar(50)"/>
            <column name="last_name" type="varchar(50)"/>
            <column name="email" type="varchar(100)">
                <constraints unique="true"/>
            </column>
            <column name="activated" type="boolean" valueBoolean="false">
                <constraints nullable="false" />
            </column>
            <column name="lang_key" type="varchar(5)"/>
            <column name="activation_key" type="varchar(20)"/>
            <column name="created_by" type="varchar(50)">
                <constraints nullable="false"/>
            </column>
            <column name="created_date" type="timestamp" defaultValueDate="${now}">
                <constraints nullable="false"/>
            </column>
            <column name="last_modified_by" type="varchar(50)"/>
            <column name="last_modified_date" type="timestamp"/>
        </createTable>

        <createTable tableName="T_AUTHORITY">
            <column name="name" type="varchar(50)">
                <constraints primaryKey="true" nullable="false"/>
            </column>
        </createTable>

        <createTable tableName="T_USER_AUTHORITY">
            <column name="user_id" type="bigint">
                <constraints nullable="false"/>
            </column>
            <column name="authority_name" type="varchar(50)">
                <constraints nullable="false"/>
            </column>
        </createTable>

        <addPrimaryKey columnNames="user_id, authority_name" tableName="T_USER_AUTHORITY"/>

        <addForeignKeyConstraint baseColumnNames="authority_name"
                                 baseTableName="T_USER_AUTHORITY"
                                 constraintName="fk_authority_name"
                                 referencedColumnNames="name"
                                 referencedTableName="T_AUTHORITY"/>

        <addForeignKeyConstraint baseColumnNames="user_id"
                                 baseTableName="T_USER_AUTHORITY"
                                 constraintName="fk_user_id"
                                 referencedColumnNames="id"
                                 referencedTableName="T_USER"/>

        <loadData encoding="UTF-8"
                  file="config/liquibase/users.csv"
                  separator=";"
                  tableName="T_USER">
            <column name="activated" type="boolean"/>
            <column name="created_date" type="timestamp"/>
        </loadData>
        <dropDefaultValue tableName="T_USER" columnName="created_date" columnDataType="datetime"/>

        <loadData encoding="UTF-8"
                  file="config/liquibase/authorities.csv"
                  separator=";"
                  tableName="T_AUTHORITY"/>

        <loadData encoding="UTF-8"
                  file="config/liquibase/users_authorities.csv"
                  separator=";"
                  tableName="T_USER_AUTHORITY"/>

        <createTable tableName="T_PERSISTENT_AUDIT_EVENT">
            <column name="event_id" type="bigint" autoIncrement="${autoIncrement}">
                <constraints primaryKey="true" nullable="false"/>
            </column>
            <column name="principal" type="varchar(255)">
                <constraints nullable="false" />
            </column>
            <column name="event_date" type="timestamp"/>
            <column name="event_type" type="varchar(255)"/>
        </createTable>

        <createTable tableName="T_PERSISTENT_AUDIT_EVENT_DATA">
            <column name="event_id" type="bigint">
                <constraints nullable="false"/>
            </column>
            <column name="name" type="varchar(255)">
                <constraints nullable="false"/>
            </column>
            <column name="value" type="varchar(255)"/>
        </createTable>
        <addPrimaryKey columnNames="event_id, name" tableName="T_PERSISTENT_AUDIT_EVENT_DATA"/>

        <createIndex indexName="idx_persistent_audit_event"
                     tableName="T_PERSISTENT_AUDIT_EVENT"
                     unique="false">
            <column name="principal" type="varchar(255)"/>
            <column name="event_date" type="timestamp"/>
        </createIndex>

        <createIndex indexName="idx_audit_event_data"
                     tableName="T_PERSISTENT_AUDIT_EVENT_DATA"
                     unique="false">
            <column name="event_id" type="bigint"/>
        </createIndex>

        <addForeignKeyConstraint baseColumnNames="event_id"
                                 baseTableName="T_PERSISTENT_AUDIT_EVENT_DATA"
                                 constraintName="FK_event_audit_event_data"
                                 referencedColumnNames="event_id"
                                 referencedTableName="T_PERSISTENT_AUDIT_EVENT"/>
    </changeSet>

    <!-- Manage the OAuth authentication -->
    <changeSet id="2" author="jhipster">
        <createTable tableName="oauth_client_details">
            <column name="client_id" type="VARCHAR(256)">
                <constraints nullable="false" primaryKey="true"/>
            </column>
            <column name="resource_ids" type="VARCHAR(256)"/>
            <column name="client_secret" type="VARCHAR(256)"/>
            <column name="scope" type="VARCHAR(256)"/>
            <column name="authorized_grant_types" type="VARCHAR(256)"/>
            <column name="web_server_redirect_uri" type="VARCHAR(256)"/>
            <column name="authorities" type="VARCHAR(256)"/>
            <column name="access_token_validity" type="INT"/>
            <column name="refresh_token_validity" type="INT"/>
            <column name="additional_information" type="VARCHAR(4000)"/>
            <column name="autoapprove" type="VARCHAR(4000)"/>
        </createTable>

        <createTable tableName="oauth_client_token">
            <column name="token_id" type="VARCHAR(256)"/>
            <column name="token" type="BLOB"/>
            <column name="authentication_id" type="VARCHAR(256)"/>
            <column name="user_name" type="VARCHAR(256)"/>
            <column name="client_id" type="VARCHAR(256)"/>
        </createTable>

        <addForeignKeyConstraint baseColumnNames="user_name"
            baseTableName="oauth_client_token"
            constraintName="fk_oauth_client_token_user"
            referencedColumnNames="login"
            referencedTableName="T_USER"/>

        <createTable tableName="oauth_access_token">
            <column name="token_id" type="VARCHAR(256)"/>
            <column name="token" type="BLOB"/>
            <column name="authentication_id" type="VARCHAR(256)"/>
            <column name="user_name" type="VARCHAR(256)"/>
            <column name="client_id" type="VARCHAR(256)"/>
            <column name="authentication" type="BLOB"/>
            <column name="refresh_token" type="VARCHAR(256)"/>
        </createTable>

        <addForeignKeyConstraint baseColumnNames="user_name"
            baseTableName="oauth_access_token"
            constraintName="fk_oauth_access_token_user"
            referencedColumnNames="login"
            referencedTableName="T_USER"/>

        <createTable tableName="oauth_refresh_token">
            <column name="token_id" type="VARCHAR(256)"/>
            <column name="token" type="BLOB"/>
            <column name="authentication" type="BLOB"/>
        </createTable>

        <createTable tableName="oauth_code">
            <column name="code" type="VARCHAR(256)"/>
        </createTable>

        <createTable tableName="oauth_approvals">
            <column name="userId" type="VARCHAR(256)"/>
            <column name="clientId" type="VARCHAR(256)"/>
            <column name="scope" type="VARCHAR(256)"/>
            <column name="status" type="VARCHAR(256)"/>
            <column name="expiresAt" type="timestamp"/>
            <column name="lastModifiedAt" type="timestamp"/>
        </createTable>
    </changeSet>

    <changeSet id="3" author="cirovladimir" dbms="oracle">
        <createSequence sequenceName="SQ_USER" startValue="1000" incrementBy="1"/>
        <createSequence sequenceName="SQ_PERSISTENT_AUDIT_EVENT" startValue="1000" incrementBy="1"/>

        <createProcedure>
            CREATE OR REPLACE TRIGGER tg_user_next_id
            BEFORE INSERT
            ON t_user
            FOR EACH ROW
            DECLARE
            v_id t_user.id%TYPE;
            BEGIN
            -- Select a new value from the sequence into a local variable.
            -- This step is optional. You can directly select into :new.qname_id
            SELECT sq_user.nextval INTO v_id FROM DUAL;
            :new.id := v_id;
            END tg_user_next_id;
        </createProcedure>
        <createProcedure>
            CREATE OR REPLACE TRIGGER tg_persistent_audit_event
            BEFORE INSERT
            ON t_persistent_audit_event
            FOR EACH ROW
            DECLARE
            BEGIN
            -- Select a new value from the sequence into a local variable.
            -- This step is optional. You can directly select into :new.qname_id
            SELECT sq_user.nextval INTO :new.event_id FROM DUAL;
            END tg_persistent_audit_event;
        </createProcedure>
        <rollback>
            DROP TRIGGER tg_user_next_id;
            DROP TRIGGER tg_persistent_audit_event;
            DROP SEQUENCE sq_user;
            DROP SEQUENCE sq_persistent_audit_event;
        </rollback>
    </changeSet>
</databaseChangeLog>

one last hicup, the password column from T_USER table is created in lowercase and Oracle RDBMS is case sensitive so it throws an error when you attempt to login.

[ERROR] org.hibernate.engine.jdbc.spi.SqlExceptionHelper - ORA-00904: "USER0_"."PASSWORD": invalid identifier

I just renamed the column identifier to uppercase and it worked. I didn’t want the hassle of renaming the column in the JPA classes and what not.

I generated the app with JHipster 2.6.0 and apparently is working fine, but I haven’t tested it thoroughly.

sources:
http://stackoverflow.com/questions/23408248/jhipster-oracle-database-connectivity
https://github.com/jhipster/generator-jhipster/pull/705
http://forum.liquibase.org/topic/sequence-value-while-inserting-the-data-in-table
http://www.liquibase.org/documentation
http://stackoverflow.com/questions/10613846/create-table-with-sequence-nextval-in-oracle
http://www.liquibase.org/tutorial-using-oracle

Anuncios

2 comentarios to “jhipster – Using Oracle Database”

  1. michaelisvy Says:

    Just a quick update: there is now support for Oracle in JHipster. See here: http://jhipster.github.io/using_oracle.html


Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s

A %d blogueros les gusta esto: