JPA – Oracle views and synonyms with hibernate

abril 26, 2017

We have a bunch of tables per office, each on its own schema in an Oracle Database.

For simplicity, we have created views on this tables and synonyms to access this views from one single schema. Hibernate supports mapping entities to synonyms, so there’s nothing more we need to do on our entities. But we still want to be able to add and modify underlying data.

To achieve this we had to create views and triggers like so

SET DEFINE OFF;
CREATE OR REPLACE FORCE VIEW CENTRAL.VPARTES
(
   SID_PARTE,
   ...
)
AS
   SELECT 'OF001' SID_OFICINA,
          T.SID_PARTE,
          ...
     FROM OFICINA001.TPARTES T
    WHERE 1 = 1 AND T.NESTATUS IN (1, 2)
   UNION ALL
   SELECT 'OF002' SID_OFICINA,
          T.SID_PARTE,
          ...
     FROM OFICINA002.TPARTES T
    WHERE 1 = 1 AND T.NESTATUS IN (1, 2);


CREATE OR REPLACE TRIGGER CENTRAL.TG_VPARTES_UPD
INSTEAD OF UPDATE
ON CENTRAL.VPARTES
FOR EACH ROW
BEGIN
IF :OLD.SID_OFICINA = 'OF001' THEN
UPDATE OFICINA001.TPARTES
SET    
       SESTATUS       = :NEW.SESTATUS,
       ...
WHERE  1 = 1
AND SID_PARTE        = :NEW.SID_PARTE ;
ELSIF :OLD.SID_OFICINA = 'OF002' THEN 
UPDATE OFICINA002.TPARTES
SET    
       SESTATUS       = :NEW.SESTATUS,
       ...
WHERE  1 = 1
AND SID_PARTE        = :NEW.SID_PARTE ;
END IF;
END;
/

for the INSERT it would be kind of the same.

If you happen to use JHipster, to allow the usage of synonyms while passing the liquibase validation we need to add a hibernate property on our corresponding application-{profile}.yml or disable liquibase altogether.

spring:
    profiles:
        include: no-liquibase
...
    jpa:
        database-platform: org.hibernate.dialect.Oracle12cDialect
        database: ORACLE
        show-sql: false
        properties:
            ...
            hibernate.synonyms: true

sources:
http://stackoverflow.com/questions/6531729/updatable-view-oracle
http://stackoverflow.com/questions/8817253/jpa-entiy-on-synonym-instead-of-table

Anuncios

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: