Hibernate/Oracle und hierarchische denormalizierte daten

Hibernate und Oracle sind die Platzhirsche in jeweils eigenen Produktnischen.

Bei der Verwendung von diesen beiden Produkten können Problemen gelöst werden, für die Hibernate eigentlich nicht gedacht wurde, zum Beispiel – Umgang mit denormalizierten Tabellen.

Ich habe 3 Möglichkeiten gefunden wie man Hibernate und Oracle zum lesen/schreiben von hierarchischen denormalizierten Daten verwenden kann, die als dimensionale Tabellen in Data-Warehouse häufig zu finden sind.

  1. Descriminator Formula (JPA, nur lesend, keine OUTER JOINS, alle Datenbanken)
  2. VIEW’s (JPA, nur lesend, alle Datenbanken)
  3. VIEW’s mit INSTEAD OF Trigger (JPA, lesend und schreibend, nur Oracle)

Gleiche Techniken können auch für Zugriff auf beliebige denormalizierten Daten mit Hibernate/JPA benutzt werden.

Zum Test habe ich eine DIM_ORG Tabelle angeleg, die in 3-Normal Form, 3 Organisations-Hierachien (COUNTRY, BRANCH, STORE) enthalten soll. Ich bin davon ausgegangen, dass ID und entsprechender surrogat OBJECT_ID immer gleich sind, das sollte man mit CHECK CONSTRAINT prüfen. Ausserdem sollte man Orgranistaions-Hierarchie auf dem Datenbank Level prüfen (STORE muss immer in einem BRANCH liegen und BRANCH in einem COUNTRY).

CREATE TABLE DIM_ORG
(  ID NUMBER(10,0) NOT NULL ENABLE,
   COUNTRY_ID NUMBER(10, 0),
   COUNTRY_NAME VARCHAR(128 BYTE),
   BRANCH_ID NUMBER(10,0),
   BRANCH_NAME VARCHAR(128 BYTE),
   STORE_ID NUMBER(10,0),
   STORE_NAME VARCHAR(128 BYTE),
   CONSTRAINT "DIM_ORG_PK" PRIMARY KEY ("ID")
);

Tabelle enthält am Anfang folgende Werte:

ID COUNTRY_ID COUNTRY_NAME BRANCH_ID BRANCH_NAME STORE_ID STORE_NAME
1 1 germany
2 1 germany 2 Berlin
3 1 germany 2 Berlin 3 A1
4 1 germany 2 Berlin 4 A2
5 1 germany 2 Berlin 5 A3
6 1 germany 6 Munich
7 1 germany 6 Munich 7 X1
8 1 germany 6 Munich 8 X2
9 9 spain
10 9 spain 10 Madrid
11 9 spain 10 Madrid 11 M1

Jede der drei Lösungen ist vereinfacht dargestellt, vollständige Eclipse Projekte mit den Lösungen gibt es am Ende des Beitrags.

Descriminator Formula

@Entity
@Table(name="DIM_ORG")
@Immutable
@Inheritance(strategy=InheritanceType.SINGLE_TABLE)
@DiscriminatorFormula("(case " +
		"when store_id is not null then 'S' " +
		"when (branch_id is not null and store_id is null) then 'B' " +
		"when (country_id is not null and branch_id is null) then 'C' " +
		"else null end)")
@ForceDiscriminator
public class OrgEntity {
	public static final String ST_DESCRIMINATOR = "S";
	public static final String BR_DESCRIMINATOR = "B";
	public static final String C_DESCRIMINATOR = "C";

	@Id
	@Column(name = "id")
	private int id;

	public int getId() {return id;}
}
@Entity
@DiscriminatorValue(value = OrgEntity.C_DESCRIMINATOR)
public class Country extends OrgEntity {
	@Column(name = "country_name")
	private String name;

	@OneToMany(cascade=CascadeType.ALL, fetch=FetchType.EAGER)
	@JoinColumn(name = "COUNTRY_ID")
	private Set branches;

	public String getName() {return name;}
	public Set getBranches() {return branches;}
}

Um auf die Daten in der 3 Normal-Form lesend zugreifen, wird ein SINGLE_TABLE Inheritance verwendet mit @ForceDiscriminator Annotation, einzelne Klassen werden mit einem Oracle „CASE“ Ausdruck erkannt.

Vorteile:

  1. Keine Anpassungen in der Datenbank nötig.

Nachteile:

  1. Nur lesend!
  2. Durch die Verwendung von @ForceDiscriminator Annotation keine OUTER JOIN’s mehr möglich. Kann aber durch Caching gemildert werden.
  3. Nicht für jeden Datenbank Verwaltungs-System geeignet, wegen DiscriminatorFormula.

VIEW

CREATE OR REPLACE FORCE VIEW DIM_ORG_COUNTRY_VIEW (COUNTRY_ID, COUNTRY_NAME) AS 
select country_id, country_name 
from dim_org
where country_id is not null and branch_id is null;

CREATE OR REPLACE FORCE VIEW DIM_ORG_BRANCH_VIEW (COUNTRY_ID, BRANCH_ID, BRANCH_NAME) AS 
select country_id, branch_id, branch_name 
from dim_org
where branch_id is not null and store_id is null;
 
CREATE OR REPLACE FORCE VIEW DIM_ORG_STORE_VIEW (BRANCH_ID, STORE_ID, STORE_NAME) AS 
select distinct branch_id, store_id, store_name 
from dim_org
where store_id is not null;
@Entity
@Table(name = "DIM_ORG_COUNTRY_VIEW")
@Immutable
public class Country {
	@Id
	@Column(name = "country_id")
	private int id;

	@Column(name = "country_name")
	private String name;

	@OneToMany(cascade = CascadeType.ALL, fetch = FetchType.LAZY)
	@JoinColumn(name = "COUNTRY_ID")
	private Set branches;

	public int getId() {return id;}
	public String getName() {return name;}
	public Set getBranches() {return branches;}
}

Vorteile:

  1. OUTER JOIN’s möglich

Nachteile:

  1. Nur lesend!
  2. Definition und Anpassung von VIEW’s nötig.

VIEW + INSTEAD OF Trigger

CREATE OR REPLACE TRIGGER DIM_ORG_COUNTRY_VIEW_TRIGGER 
INSTEAD OF INSERT OR DELETE OR UPDATE ON DIM_ORG_COUNTRY_VIEW
BEGIN
    IF INSERTING THEN 
      INSERT INTO DIM_ORG(ID, COUNTRY_ID, COUNTRY_NAME, BRANCH_ID, BRANCH_NAME, STORE_ID, STORE_NAME)
      VALUES (:NEW.COUNTRY_ID, :NEW.COUNTRY_ID, :NEW.COUNTRY_NAME, null, null, null, null);
    END IF;
    
    IF UPDATING THEN 
      UPDATE DIM_ORG -- Update single country record
      SET ID = :NEW.COUNTRY_ID, COUNTRY_ID = :NEW.COUNTRY_ID, COUNTRY_NAME = :NEW.COUNTRY_NAME
      WHERE ID = :OLD.COUNTRY_ID;
  
      UPDATE DIM_ORG -- Update child records
      SET COUNTRY_ID = :NEW.COUNTRY_ID, COUNTRY_NAME = :NEW.COUNTRY_NAME
      WHERE COUNTRY_ID = :OLD.COUNTRY_ID;
    END IF;
    
    IF DELETING THEN 
      DELETE FROM DIM_ORG WHERE COUNTRY_ID = :OLD.COUNTRY_ID; -- delete from dim_org
    END IF;
END;

/
ALTER TRIGGER DIM_ORG_COUNTRY_VIEW_TRIGGER ENABLE;
 
CREATE OR REPLACE TRIGGER DIM_ORG_BRANCH_VIEW_TRIGGER 
INSTEAD OF INSERT OR DELETE OR UPDATE ON DIM_ORG_BRANCH_VIEW
BEGIN
    IF INSERTING THEN 
      INSERT INTO DIM_ORG(ID, COUNTRY_ID, COUNTRY_NAME, BRANCH_ID, BRANCH_NAME, STORE_ID, STORE_NAME)
      SELECT :NEW.BRANCH_ID, COUNTRY_ID, COUNTRY_NAME, :NEW.BRANCH_ID, :NEW.BRANCH_NAME, NULL, NULL
      FROM DIM_ORG WHERE ID = :NEW.COUNTRY_ID AND BRANCH_ID IS NULL;
    END IF;
    
    IF UPDATING THEN 
      UPDATE DIM_ORG -- Update single branch
      SET ID = :NEW.BRANCH_ID, 
          COUNTRY_ID = :NEW.COUNTRY_ID, 
          COUNTRY_NAME = (SELECT COUNTRY_NAME FROM DIM_ORG WHERE ID = :NEW.COUNTRY_ID),
          BRANCH_ID = :NEW.BRANCH_ID, 
          BRANCH_NAME = :NEW.BRANCH_NAME
      WHERE ID = :OLD.BRANCH_ID;  
      
      UPDATE DIM_ORG -- Update child records
      SET COUNTRY_ID =  :NEW.COUNTRY_ID),
          COUNTRY_NAME = (SELECT COUNTRY_NAME FROM DIM_ORG WHERE ID = :NEW.COUNTRY_ID),
          BRANCH_ID = :NEW.BRANCH_ID, 
          BRANCH_NAME = :NEW.BRANCH_NAME
      WHERE BRANCH_ID = :OLD.BRANCH_ID;      
    END IF;
    
    IF DELETING THEN 
      DELETE FROM DIM_ORG WHERE BRANCH_ID = :OLD.BRANCH_ID; -- delete from dim_org
    END IF;
END;

/
ALTER TRIGGER DIM_ORG_BRANCH_VIEW_TRIGGER ENABLE;
  
CREATE OR REPLACE TRIGGER DIM_ORG_STORE_VIEW_TRIGGER 
INSTEAD OF INSERT OR DELETE OR UPDATE ON DIM_ORG_STORE_VIEW
BEGIN
    IF INSERTING THEN 
      INSERT INTO DIM_ORG(ID, COUNTRY_ID, COUNTRY_NAME, BRANCH_ID, BRANCH_NAME, STORE_ID, STORE_NAME)
      SELECT :NEW.STORE_ID, COUNTRY_ID, COUNTRY_NAME, BRANCH_ID, BRANCH_NAME, :NEW.STORE_ID, :NEW.STORE_NAME
      FROM DIM_ORG WHERE BRANCH_ID = :NEW.BRANCH_ID AND STORE_ID IS NULL;
    END IF;
    
    IF UPDATING THEN 
      UPDATE DIM_ORG -- Update store
      SET ID = :NEW.STORE_ID, 
          COUNTRY_ID = (SELECT COUNTRY_ID FROM DIM_ORG WHERE ID = :NEW.BRANCH_ID),
          COUNTRY_NAME = (SELECT COUNTRY_NAME FROM DIM_ORG WHERE ID = :NEW.BRANCH_ID),
          BRANCH_ID = :NEW.BRANCH_ID, 
          BRANCH_NAME = (SELECT BRANCH_NAME FROM DIM_ORG WHERE ID = :NEW.BRANCH_ID), 
          STORE_ID = :NEW.STORE_ID, 
          STORE_NAME = :NEW.STORE_NAME
      WHERE ID = :OLD.STORE_ID;
    END IF;
    
    IF DELETING THEN 
      DELETE FROM DIM_ORG WHERE STORE_ID = :OLD.STORE_ID; -- delete from dim_org
    END IF;
END;

/
ALTER TRIGGER DIM_ORG_STORE_VIEW_TRIGGER ENABLE;

CREATE SEQUENCE  DIM_ORG_SEQ  MINVALUE 1 MAXVALUE 999999999999999999999999999 
INCREMENT BY 1 START WITH 20 NOCACHE ORDER NOCYCLE;
@Entity
@Table(name = "DIM_ORG_COUNTRY_VIEW")
public class Country {
	@Id
	@GeneratedValue(strategy=GenerationType.SEQUENCE, generator="SEQ_ORG")
	@SequenceGenerator(name="SEQ_ORG", sequenceName="DIM_ORG_SEQ", allocationSize=20)
	@Column(name = "COUNTRY_ID")
	private int id;

	@Column(name = "COUNTRY_NAME")
	private String name;

	@OneToMany(cascade = CascadeType.ALL, fetch = FetchType.EAGER)
	@JoinColumn(name = "COUNTRY_ID")
	private Set branches;	

	public int getId() {return id;}
	public void setId(int id) {this.id = id;}
	public String getName() {return name;}
	public void setName(String name) {this.name = name;}
	public Set getBranches() {return branches;}
	public void setBranches(Set branches) {this.branches = branches;}
}

Beim Ausführen von DML Operationen auf Views, wird ein Trigger ausgeführt, der diese DML Operationen umdekoriert und auf DIM_ORG Master-Tabelle anwendet. Dabei werden oft Daten aus der DIM_ORG Master-Tabelle benötigt, um die höher liegende Organisations-Hierarchien korrekt zu verwalten.

Vorteile:

  1. Transparenter zugriff auf Daten in 3 Normal-Form! Lesend und schreibend!
  2. OUTER JOIN’s möglich.

Nachteile:

  1. Definition und Anpassung von VIEW’s nötig.
  2. Beim komplexen Hierarchien, Verwaltung von Triggers wird sehr aufwändig und fehleranfällig.

Descriminator
View
View + INSTEAD OF Trigger

Veröffentlicht in Allgemein, Java, Oracle