Spring und Stored Procedure mit User-defined Types (Tutorial)

Dieser Beitrag ist Teil einer (Tutorial-) Serie über die Einführung in das Spring Framework und beschreibt die Nutzung von Stored Procedures mit User-definied Types (UDT) und dem Spring Framework.

Die Struktur des Projektes

Die verwendeten Frameworks und Werkzeuge sind hier beschrieben. In diesem Beispiel wird mit Spring JDBC auf eine Stored Procedure zugegriffen. Dabei werden benutzerdefinierte strukturierte Datentypen, sogenannte User-defined Types (UDT), verwendet. Diese komplexen SQL-Typen sind mit der Version 2.1 zum JDBC Standard hinzugekommen. In diesem Beitrag wird gezeigt, wie mit Spring JDBC das Mapping dieser UDTs auf Java-Klassen durchgeführt werden kann.

Im folgendem Bild sind die benötigten Bibliotheken dargestellt.

Die benötigten Bibliotheken (Dependencies) für das Projekt "test-spring-jdbc"

Die benötigten Bibliotheken (Dependencies) für das Projekt 'test-spring-jdbc' (© Frank Rahn)

Die Literaturempfehlungen für dieses Beispiel

Die User-defined Types (UDT)

Die benutzerdefinierten Datentypen (user-defined Types) wurden im SQL-Standard 2003 (ANSI/ISO/IEC 9075, SQL3) aufgenommen und zum JDBC Standard mit der Version 2.1 hinzugefügt. Die folgende Ziele wurden mit den benutzerdefinierten Datentypen verfolgt:

  • Komplexe Datentypen
    Mit den komplexen Datentypen sollen Objekte der realen Welt abgebildet werden. Dazu wurden Konzepte (Strukturierung, Kapselung, …) aus der Objektorientierung übernommen.
  • Strenge Typisierung
    Durch die strenge Typisierung sollen semantische unsinnige Vergleich vermieden werden, die nur aufgrund von gleichen Datentypen möglich waren (z. B. INTEGER, Vergleich oder Zuweisung von Gehalt und Hausnummer oder Kundennummer). Dazu konnten mit den DISTINCT-Datentypen getypte und benannte Varianten der Standarddatentypen erzeugt werden.

Die benutzerdefinierten Datentypen werden insbesondere bei den Stored Procedures / Functions zum Typisieren der Parameter verwendet.

Zusätzlich können diese benutzerdefinierten Datentypen in Tabellen verwendet werden.

Das Anlegen und Einrichten der Datenbank für dieses Beispiel

Das Anlegen einer Datenbank und das Erzeugen der Stored Procedure wurden in einzelne Beiträge ausgelagert.

Den Oracle-JDBC-Treiber im lokalen Maven Repository zu Verfügung stellen

Der Oracle-Treiber für JDBC in der benötigten Oracle Version 11g ist im Central Maven Repository nicht vorhanden. Daher sind folgende Schritte notwendig:

  1. Download des Treibers von Oracle in der Version 11.2.0.3 ( ojdbc6.jar für JDK 1.6)
    Ist nur über eine Anmeldung bzw. Registrierung bei Oracle möglich!
  2. Kopieren der Datei in das Verzeichnis src/oracle
  3. Ausführen des Skripts install.sh

Das Skript installiert den Treiber unter der Group-Id com.oracle und der Artifact-Id ojdbc6 im lokalen Maven Repository.

In folgendem Ausschnitt der pom.xml wird die Referenzierung des Treibers dargestellt.

Die Schnittstelle der Stored Procedure „searchPersons“

Der folgende verallgemeinerte SQL-Code-Ausschnitt zeigt wie die Schnittstelle der Stored Procedure aussieht. Die Prozedur nimmt die zwei Parameter p_num und p_user entgegen und liefert ein Liste (Array) von Personen zurück. Die Liste hat die Länge p_num und wird mit den Daten des aktuellen Benutzers p_user aufgefüllt.

Wie konkret die Stored Procedure in einer Datenbank erzeugt wird, wird Datenbank-spezifisch in den beiden folgenden Beiträgen beschrieben.

Die Entitäten

Die Stored Procedure benötigt zwei Entitäten s_User und s_Person. Hier zunächst die verallgemeinerte SQL-Definition der beiden benutzerdefinierten Datentypen.

Diese beiden benutzerdefinierten Datentypen werden in einfache Java-Klassen überführt.

Für die Entität User werden die Datentypen CHAR und VARCHAR unabhängig ihrer Länge auf die Java-Klasse String abgebildet.

Für die Entität s_Person werden die Datentypen BIGINT auf den Java-Type long und DECIMAL auf die Java-Klasse java.math.BigDecimal abgebildet.

Das Datenzugriffsobjekt

Entsprechend der Architektur aus dem Beitrag Spring mit JPA und Hibernate aus dieser (Tutorial-) Serie, wird zunächst die Schnittstelle des Datenzugriffsobjekts für die Stored Procedure searchPersons definiert.

In der folgenden Implementierung dieses Datenzugriffsobjekts sind alle Informationen über die Stored Procedure enthalten.

  • In der Zeile 25 bis 26 werden die SQL-Namen der Stored Procedure und der drei SQL-Parameter definiert.

  • Die Mapper in Zeile 41 und 44 sind für die Konvertierung der benutzerdefinierten Datentypen in die entsprechenden Java-Klassen zuständig. Die Implementierung der Mapper wird im Anschluss beschrieben.
  • Die Spring-Klasse SimpleJdbcCall aus Zeile 46 stellt Funktionen für die Durchführung von Stored Procedures mit einem java.sql.CallableStatement aus dem JDBC Standard bereit. Diese Klasse hat die Eigenschaften multi-thread (nebenläufig) und stateless (zustandslos).
    Zusätzlich besitzt sie ein Fluent Interface.
  • In der Zeile 54 wird der SimpleJdbcCall erzeugt und mit der javax.sql.DataSource initialisiert. Zusätzlich wird der SQL-Name der Procedure gesetzt.
  • In der Zeile 56 wird ein spezieller SqlParameter für den benutzerdefinierten Datentypen P_USER des aktuellen Benutzers über den entsprechenden Mapper als Eingabewert ( false) gesetzt.
  • In der Zeile 57 wird ein spezieller SqlParameter für den benutzerdefinierten Datentypen P_PERSONS der Liste von Personen über den entsprechenden Mapper als Rückgabewert ( true) gesetzt.

Im folgendem Code-Abschnitt wird die Methode searchPersons dargestellt – sie entspricht der Stored Procedure.

  • In den Zeilen 68 bis 70 wird eine Map mit den Eingabedaten für die Stored Procedure gefüllt.
  • In der Zeile 70 wird, über einen Mapper, die Instanz der Java-Klasse user in eine benutzerdefinierten Datentyp ( java.sql.Struct) konvertiert.
  • In der Zeile 72 wird, über die Instanz der Spring-Klasse SimpleJdbcCall die Stored Procedure ausgeführt.
  • In der Zeile 74 wird, aus der Map mit den Rückgabedaten, das Ergebnis abgeholt. Das Ergebnis muss nicht mehr konvertiert werden, da beim Initialisieren dieses Datenbankzugriffsobjekts mit dem SqlParameter für die Personen ein Mapper für Rückgabe dieses Types registriert worden ist. Näheres bei der Beschreibung der Mapper weiter unten.

Die Mapper

Im folgendem Abschnitt werden die Mapper zwischen den Java-Klassen ( UserObject) und den zugehörigen benutzerdefinierten Datentypen ( JdbcType: Struct, Array, Blob, Clob, ...) beschrieben.

Zunächst wird ein abstrakter Mapper SqlParameterMapper erstellt. Diese abstrakte Klasse wird mit den beiden generischen Typparametern UserObject und JdbcType definiert. Zusätzlich werden einige Funktionalitäten des Spring Framework (Spring JDBC) verwendet:

  • org.springframework.jdbc.core.SqlReturnType
    Diese Schnittstelle wird für das Abrufen von benutzerdefinierten Datentypen aus dem Ergebnis einer Datenbankaktion verwendet.
  • org.springframework.jdbc.core.SqlTypeValue
    Diese Schnittstelle wird für das Setzen von benutzerdefinierten Datentypen in die Parameter einer Datenbankaktion verwendet.
  • org.springframework.jdbc.core.SqlParameter
    Diese Klasse wird zur Definition von Eingabeparametern von Datenbankaktionen verwendet.
  • org.springframework.jdbc.core.SqlOutParameter
    Diese Klasse wird zur Definition von Rückgabeparametern von Datenbankaktionen verwendet.

Im folgendem Code-Ausschnitt wird die Definition des abstrakten Mappers beschrieben.

  • In der Zeile 20 wird der abstrakte Mapper SqlParameterMapper mit den generischen Typparametern UserObject und JdbcType definiert (siehe oben). Zusätzlich implementiert dieser Mapper die Schnittstelle SqlReturnType.
  • In der Zeile 47 wird die abstrakte Methode definiert, die im konkrete Mapper den benutzerdefinierten Datentyp in eine Java-Klasse konvertiert.
  • In der Zeile 78 wird die abstrakte Methode definiert, die im konkrete Mapper die Instanz der Java-Klasse in den benutzerdefinierten Datentyp konvertiert. Dazu wird eine Instanz der Datanbankverbindung ( Connection) benötigt. Über diese Connection werden die Datentypen erzeugt ( createStruct(), createArrayOf(), createBlob(), …) und an die Datenbankverbindung gebunden.
  • In der Zeile 87 wird eine abstrakte Methode für die Erzeugung einer Beschreibung des SQL Parameters für den benutzerdefinierten Datentyp definiert.
  • Ab der Zeile 28 wird die Methode getTypevalue() der Schnittstelle SqlReturnType implementiert. Diese Methode wird von Spring JDBC aufgerufen, wenn eine Datensatz ( ROW) aus dem Ergebnis ( CURSOR) gelesen und ein komplexer Datentyp erwartet wird.
  • In der Zeile 32 wird ein benutzerdefinierten Datentyp von der Datenbank gelesen. Der Parameter paramIndex gibt die Nummer der Spalte, beginnend bei 1 für die erste Spalte, an.
  • In der Zeile 38 wird die Methode aufgerufen, die aus dem benutzerdefinierten Datentyp eine Instanz der Java-Klasse erzeugt.
  • Ab der Zeile 55 wird die Methode createSqltypeValue() implementiert. Diese Methode liefert eine Instanz der Schnittstelle SqlTypeValue. Diese Instanz nutzt das Spring Framework, wenn es das java.sql.PreparedStatement mit den Eingabeparametern bestückt. Dazu wird die abstrakte Klasse AbstarctSqlTypeValue des Spring Frameworks erweitert. Diese Methode wird im StandardSearchPersonsDAO in der Zeile 70 verwendet und die erzeugte Instanz wird, wenn das Spring Framework das java.sql.PreparedStatement erzeugt hat, bei jdbcCall.execute() aufgerufen.
  • Die Implementierung in Zeile 63 deligiert die Verarbeitung aus der Instanz SqlTypeValue an die Methode createSqlValue(). Diese Methode erzeugt den benutzerdefinierten Datentyp.

Die Java-Klasse de.rahn.jdbc.call.entity.User wird durch die JDBC-Klasse java.sql.Struct auf den benutzerdefinierten Datentyp S_USER abgebildet.

  • In der Zeile 20 wird dieser konkrete Mapper mit der Java-Klassen User und der JDBC-Klasse Struct parametrisiert.
  • In der Zeile 31 wird ein neuer User aus einem Datensatz erzeugt.
  • In der Zeile 33 wird aus dem Datentyp die Werte ausgelesen. Die Datenbank-spezifische Implementierung der JDBC-Klasse kann an dieser Stelle einen Zugriff auf die Datenbank durchführen.
  • In den Zeilen 34 bis 36 werden die Werte des Datentypes in den neuen User geschrieben. In dieser Implementierung wird dazu die Technik der Instance Initializer (seit Java 1.1; Java SE 7 Edition of Java Language Specification: §8.6. Instance Initializers) verwendet.
  • In den Zeilen 50 bis 53 wird mit Hilfe einer bestehenden Datenbankverbindung eine Instanz der JDBC-Klasse S_USER erzeugt und mit den Werten aus dem User gefüllt.
  • In den Zeilen 63 bis 67 wird die Beschreibung des SQL Parameters für diese benutzerdefinierten Datentypen angelegt. Sie besteht aus der JDBC-Typnummer, den Namen des benutzerdefinierten Datentyps und dem Parameternamen bei der Verwendung in einer Eingabe- bzw. Rückgabeliste. Bei einem Rückgabeparameter wird eine Instanz diese Klasse als Handler für die Erstellung des Ergebnisses registriert.
    Ein Beispiel der Verwendung findet sich im StandardSearchPersonsDAO in Zeile 57.

Die Java-Klasse de.rahn.jdbc.call.entity.Person wird, wie die Klasse User, auf die JDBC-Klasse Struct abgebildet. Der restliche Aufbau der Klasse ähnelt der Klasse User.

Die Liste der Java-Klasse de.rahn.jdbc.call.entity.Person wird auf die JDBC-Klasse java.sql.Array abgebildet.

  • In den Zeilen 31, 48 und 65: Dieser Mapper verwendet für das Behandeln einer Person den spezifischen Mapper für die Person.
  • In der Zeile 39 werden die gelesenen Werte aus der Datenbank aus der JDBC-Klasse gelesen.
  • In der Zeile 68 wird der benutzerdefinierten Datentyp für ein Liste von Personen erzeugt.

Das Arbeiten mit Stored Procedure mit User-defined Types wurden in diesem Beitrag trotz der Verwendung von Spring JDBC sehr nahe an JDBC und Oracle beschrieben.

Mittlerweile gibt es auch eine Implementierung aus dem Spring Data Projekt:

Die Anwendung mit Logging

Die Anwendung ist wie in dem Beispiel Spring an einem einfachem Beispiel aufgebaut. Es ändert sich nur die Anwendung, die XML Konfiguration de/rahn/app/application.xml und der Starter bleiben gleich. Die Änderung an der Application sind hier dargestellt.

Die XML Konfigurationen zum Einstieg in die Anwendung muß um die Datenbank- und die Transaktionsdefinitionen erweitert werden.

  • In der Zeile 21 und 22 wird auf die XML Konfiguration mit den Datenbank- und die Transaktionsdefinitionen verwiesen.
  • In der Zeile 23 wird auf die XML Konfiguration des JDBC-Calls verwiesen.

Die XML Konfiguration des JDBC-Calls.

In der folgenden XML Konfiguration wird die Datenbankverbindung definiert.

  • In der Zeilen 19 bis 24 werden die Definitionen für den Datenbankzugriff vorgenommen. Dabei wird der JDBC Datenbanktreiber über Properties konfiguriert.
  • In der Zeile 28 oder 32 wird die jeweilige Properties-Datei durch einen PropertyPlaceholderConfigurer geladen und der Spring Konfiguration zu Verfügung gestellt. Damit können die Properties am Datenbanktreiber ersetzt werden.
  • In den Zeilen 26 und 30 wird die Anweisung zum Laden der Properties jeweils einem Profile zu geordnet. Dadurch kann beim Programmstart mit setzen eines System-Properties -Dspring.profiles.active="Oracle" gesteuert werden, welche Datenbank genutzt wird.

In der folgenden XML Konfiguration wird nur die Transaktionsdefinitionen vorgenommen.

  • In Zeile 19 wird ein Transaktionmanager definiert, der die Transaktion der JDBC Datenbank verwendet.

In der folgenden Klasse wurde eine Erweiterung vorgenommen, damit immer das Profile für die Oracle Datenbank aktiv ist.

  • In der Zeile 17 wird der ClassPathXmlApplicationContext mit dem letztem Parameter angewiesen, die Konfiguration noch nicht zu verarbeitet.
  • In der Zeile 19 bis 20 wird über das Environment das aktive Profil gesetzt.
  • In der Zeile 21 wird der ClassPathXmlApplicationContext angewiesen die Konfiguration zu verarbeiten.

In der folgenden Properties-Datei werden die Datenbank-spezifischen Einstellungen vorgenommen. Für jede Datenbank ist eine eigene Datei anzulegen. Bitte dementsprechend anpassen!

Nachfolgend wird noch die Konsolenausgabe dargestellt, wenn die Anwendung ausgeführt wird.

Der Quellcode und Download des Beispiels

Quellcode ansehen bei GitHub:
Spring und JDBC

Download einer ZIP-Datei von GitHub:
Spring und JDBC

Die Maven Befehle

Eclipse Konfiguration neu erzeugen: $ mvn eclipse:clean eclipse:eclipse

Anwendung bauen: $ mvn clean install

Anwendung ausführen: $ mvn exec:java

Update 29.02.2014: Unverständliche Exception

Falls die Exception AbstractMethodError auftritt, liegt es meistens an eine falsche Zuordnung der benutzerdefinierten Datentypen im JDBC (z. B. java.sql.Struct zu java.sql.Array oder VARCHAR).

Diesen Fehler kann simuliert werden, in dem in der Test-Klasse OracleConnectionTest folgende Änderung vorgenommen wird.

Die Änderung hab ich im Branch mismatch-exception in GitHub hochgestellt.

Der Quellcode und Download des Updates

Quellcode ansehen bei GitHub:
Spring und JDBC (Update von 28.02.2014)

Download einer ZIP-Datei von GitHub:
Spring und JDBC (Update von 28.02.2014)

Frank Rahn

Frank Rahn ist Softwarearchitekt. Er unterstützt bei der Konzeption von Softwarearchitekturen mit Java-Technologie. Folge Sie ihm auf Facebook, Twitter oder Google+.

Benötigen Sie Unterstützung? Kontaktieren Sie ihn.

Hat Ihnen dieser Beitrag gefallen? Wir würden uns über Ihren Kommentar freuen! Bitte verwenden Sie Ihren bürgerlichen Namen und eine E-Mail-Adresse mit Gravatar.

Letzte Artikel von Frank Rahn (Alle anzeigen)

4 Kommentare

Trackbacks & Pingbacks

  1. […] mit User-defined Types (UDT) in einer Oracle Database programmiert wird. Im übergeordneten Beitrag Spring und Stored Procedure mit User-defined Types wird das Aufrufen dieser Stored Procedure aus Java heraus gezeigt. Im Beitrag Stored Procedure mit […]

Dein Kommentar

Want to join the discussion?
Feel free to contribute!

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.