Der Autor dieses Artikels stellt vor, wie man die gespeicherten Prozeduren von SQL Server über Java aufruft, und erläutert ausführlich fünf verschiedene Speichertypen. Weitere Informationen finden Sie weiter unten
1. Verwenden Sie eine gespeicherte Prozedur ohne Parameter
Wenn Sie einen JDBC-Treiber verwenden, um eine gespeicherte Prozedur ohne Parameter aufzurufen, müssen Sie die Call-SQL-Escape-Sequenz verwenden. Die Syntax für die Call-Escape-Sequenz ohne Parameter lautet wie folgt:
Kopieren Sie den Codecode wie folgt:
{call procedure-name}
Erstellen Sie als Beispiel die folgende gespeicherte Prozedur in der SQL Server 2005 AdventureWorks-Beispieldatenbank:
Kopieren Sie den Codecode wie folgt:
PROZEDUR ERSTELLEN GetContactFormalNames
ALS
BEGINNEN
SELECT TOP 10 Title + ' ' + FirstName + ' ' + LastName AS FormalName
VON Person.Kontakt
ENDE
Diese gespeicherte Prozedur gibt einen einzelnen Ergebnissatz zurück, der eine Datenspalte enthält, die aus dem Titel, dem Vornamen und dem Nachnamen der ersten zehn Kontakte in der Tabelle Person.Contact besteht.
Im folgenden Beispiel wird dieser Funktion eine offene Verbindung zur AdventureWorks-Beispieldatenbank übergeben und anschließend wird die gespeicherte Prozedur „GetContactFormalNames“ mithilfe der Methode „executeQuery“ aufgerufen.
Kopieren Sie den Codecode wie folgt:
public static voidexecuteSprocNoParams(Connection con) ...{
versuchen...{
Anweisung stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("{call dbo.GetContactFormalNames}");
while (rs.next()) ...{
System.out.println(rs.getString("FormalName"));
}
rs.close();
stmt.close();
}
Catch (Ausnahme e) ...{
e.printStackTrace();
}
}
2. Verwenden Sie eine gespeicherte Prozedur mit Eingabeparametern
Wenn Sie einen JDBC-Treiber verwenden, um eine gespeicherte Prozedur mit Parametern aufzurufen, müssen Sie die Call-SQL-Escape-Sequenz in Verbindung mit der PrepareCall-Methode der SQLServerConnection-Klasse verwenden. Die Syntax für eine Aufruf-Escape-Sequenz mit einem IN-Parameter lautet wie folgt:
Kopieren Sie den Codecode wie folgt:
{call procedure-name[([parameter][,[parameter]]...)]}
Verwenden Sie beim Erstellen einer Aufruf-Escape-Sequenz das Zeichen „?“ (Fragezeichen), um den IN-Parameter anzugeben. Dieses Zeichen dient als Platzhalter für den Parameterwert, der an die gespeicherte Prozedur übergeben werden soll. Sie können eine der Setter-Methoden der SQLServerPreparedStatement-Klasse verwenden, um Werte für Parameter anzugeben. Die verfügbaren Setter-Methoden werden durch den Datentyp des IN-Parameters bestimmt.
Wenn Sie einen Wert an eine Setter-Methode übergeben, müssen Sie nicht nur den tatsächlichen Wert angeben, der im Parameter verwendet werden soll, sondern auch die Ordnungsposition des Parameters innerhalb der gespeicherten Prozedur. Wenn eine gespeicherte Prozedur beispielsweise einen einzelnen IN-Parameter enthält, ist ihr Ordnungswert 1. Wenn die gespeicherte Prozedur zwei Parameter enthält, ist der erste Ordnungswert 1 und der zweite Ordnungswert 2.
Als Beispiel für den Aufruf einer gespeicherten Prozedur, die einen IN-Parameter enthält, verwenden Sie die gespeicherte Prozedur uspGetEmployeeManagers in der SQL Server 2005 AdventureWorks-Beispieldatenbank. Diese gespeicherte Prozedur akzeptiert einen einzelnen Eingabeparameter namens „EmployeeID“, der ein ganzzahliger Wert ist, und gibt eine rekursive Liste von Mitarbeitern und ihren Managern basierend auf der angegebenen EmployeeID zurück. Hier ist der Java-Code, der diese gespeicherte Prozedur aufruft:
Kopieren Sie den Codecode wie folgt:
public static voidexecuteSprocInParams(Connection con) ...{
versuchen...{
PreparedStatement pstmt = con.prepareStatement("{call dbo.uspGetEmployeeManagers(?)}");
pstmt.setInt(1, 50);
ResultSet rs = pstmt.executeQuery();
while (rs.next()) ...{
System.out.println("EMPLOYEE:");
System.out.println(rs.getString("Nachname") + ", " + rs.getString("Vorname"));
System.out.println("MANAGER:");
System.out.println(rs.getString("ManagerLastName") + ", " + rs.getString("ManagerFirstName"));
System.out.println();
}
rs.close();
pstmt.close();
}
Catch (Ausnahme e) ...{
e.printStackTrace();
}
}
3. Verwenden Sie eine gespeicherte Prozedur mit Ausgabeparametern
Wenn Sie solche gespeicherten Prozeduren mit einem JDBC-Treiber aufrufen, müssen Sie die Call-SQL-Escape-Sequenz in Verbindung mit der PrepareCall-Methode der SQLServerConnection-Klasse verwenden. Die Syntax für eine Aufruf-Escape-Sequenz mit einem OUT-Parameter lautet wie folgt:
Kopieren Sie den Codecode wie folgt:
{call procedure-name[([parameter][,[parameter]]...)]}
Verwenden Sie beim Erstellen einer Aufruf-Escape-Sequenz das Zeichen „?“ (Fragezeichen), um den OUT-Parameter anzugeben. Dieses Zeichen dient als Platzhalter für den Parameterwert, der von dieser gespeicherten Prozedur zurückgegeben werden soll. Um Werte für OUT-Parameter anzugeben, müssen Sie die Methode registerOutParameter der Klasse SQLServerCallableStatement verwenden, um den Datentyp jedes Parameters anzugeben, bevor Sie die gespeicherte Prozedur ausführen.
Der mit der Methode registerOutParameter für den OUT-Parameter angegebene Wert muss einer der in java.sql.Types enthaltenen JDBC-Datentypen sein, der wiederum einem der nativen SQL Server-Datentypen zugeordnet ist. Weitere Informationen zu JDBC- und SQL Server-Datentypen finden Sie unter Grundlegendes zu JDBC-Treiberdatentypen.
Wenn Sie einen Wert für einen OUT-Parameter an die Methode „registerOutParameter“ übergeben, müssen Sie nicht nur den für den Parameter zu verwendenden Datentyp angeben, sondern auch die Ordnungsposition des Parameters oder den Namen des Parameters in der gespeicherten Prozedur. Wenn eine gespeicherte Prozedur beispielsweise einen einzelnen OUT-Parameter enthält, ist ihr Ordnungswert 1; wenn eine gespeicherte Prozedur zwei Parameter enthält, ist der erste Ordnungswert 1 und der zweite Ordnungswert 2.
Erstellen Sie als Beispiel die folgende gespeicherte Prozedur in der SQL Server 2005 AdventureWorks-Beispieldatenbank: Basierend auf dem angegebenen ganzzahligen IN-Parameter (employeeID) gibt diese gespeicherte Prozedur auch einen einzelnen ganzzahligen OUT-Parameter (managerID) zurück. Basierend auf der EmployeeID, die in der Tabelle HumanResources.Employee enthalten ist, ist der im OUT-Parameter zurückgegebene Wert ManagerID.
Im folgenden Beispiel wird dieser Funktion eine offene Verbindung zur AdventureWorks-Beispieldatenbank übergeben und anschließend wird die gespeicherte Prozedur „GetImmediateManager“ mithilfe der Methode „execute“ aufgerufen:
Kopieren Sie den Codecode wie folgt:
public static voidexecuteStoredProcedure(Connection con) ...{
versuchen...{
CallableStatement cstmt = con.prepareCall("{call dbo.GetImmediateManager(?, ?)}");
cstmt.setInt(1, 5);
cstmt.registerOutParameter(2, java.sql.Types.INTEGER);
cstmt.execute();
System.out.println("MANAGER ID: " + cstmt.getInt(2));
}
Catch (Ausnahme e) ...{
e.printStackTrace();
}
}
In diesem Beispiel wird die Ordnungsposition zur Identifizierung von Parametern verwendet. Alternativ kann der Parameter anhand seines Namens und nicht anhand seiner Ordnungsposition identifiziert werden. Das folgende Codebeispiel modifiziert das vorherige Beispiel, um zu veranschaulichen, wie benannte Parameter in einer Java-Anwendung verwendet werden. Beachten Sie, dass diese Parameternamen den Parameternamen in der Definition der gespeicherten Prozedur entsprechen: 11x16CREATE PROCEDURE GetImmediateManager
Kopieren Sie den Codecode wie folgt:
@employeeID INT,
@managerID INT OUTPUT
ALS
BEGINNEN
SELECT @managerID = ManagerID
VON HumanResources.Employee
WHERE EmployeeID = @employeeID
ENDE
Gespeicherte Prozeduren können Aktualisierungszahlen und mehrere Ergebnismengen zurückgeben. Der Microsoft SQL Server 2005 JDBC-Treiber entspricht der JDBC 3.0-Spezifikation, die besagt, dass mehrere Ergebnismengen und Aktualisierungszahlen abgerufen werden sollten, bevor OUT-Parameter abgerufen werden. Das heißt, die Anwendung sollte zuerst alle ResultSet-Objekte abrufen und die Anzahl aktualisieren und dann die CallableStatement.getter-Methode verwenden, um die OUT-Parameter abzurufen. Andernfalls gehen beim Abrufen des OUT-Parameters das ResultSet-Objekt und die Aktualisierungsanzahl verloren, die noch nicht abgerufen wurden.
4. Verwenden Sie gespeicherte Prozeduren mit Rückgabestatus
Wenn Sie eine solche gespeicherte Prozedur mit einem JDBC-Treiber aufrufen, müssen Sie die Call-SQL-Escape-Sequenz in Verbindung mit der PrepareCall-Methode der SQLServerConnection-Klasse verwenden. Die Syntax für eine Aufruf-Escape-Sequenz, die einen Statusparameter zurückgibt, lautet wie folgt:
Kopieren Sie den Codecode wie folgt:
{[?=]call procedure-name[([parameter][,[parameter]]...)]}
Verwenden Sie beim Erstellen einer Aufruf-Escape-Sequenz das Zeichen „?“ (Fragezeichen), um den Rückgabestatusparameter anzugeben. Dieses Zeichen dient als Platzhalter für den Parameterwert, der von dieser gespeicherten Prozedur zurückgegeben werden soll. Um einen Wert für einen Rückgabestatusparameter anzugeben, müssen Sie den Datentyp des Parameters mithilfe der Methode „registerOutParameter“ der Klasse „SQLServerCallableStatement“ angeben, bevor Sie die gespeicherte Prozedur ausführen.
Darüber hinaus müssen Sie beim Übergeben des Parameterwerts „Rückgabestatus“ an die Methode „registerOutParameter“ nicht nur den Datentyp des zu verwendenden Parameters angeben, sondern auch die Ordnungsposition des Parameters in der gespeicherten Prozedur. Die Ordnungsposition des Rückgabestatusparameters ist immer 1, da er beim Aufruf der gespeicherten Prozedur immer der erste Parameter ist. Obwohl die SQLServerCallableStatement-Klasse die Verwendung des Parameternamens zur Angabe eines bestimmten Parameters unterstützt, können Sie für Rückgabestatusparameter nur die Ordnungspositionsnummer des Parameters verwenden.
Erstellen Sie als Beispiel die folgende gespeicherte Prozedur in der SQL Server 2005 AdventureWorks-Beispieldatenbank:
Kopieren Sie den Codecode wie folgt:
PROZEDUR ERSTELLEN CheckContactCity
(@cityName CHAR(50))
ALS
BEGINNEN
WENN ((ZÄHLER AUSWÄHLEN(*)
VON Person.Adresse
WO Stadt = @cityName) > 1)
RÜCKKEHR 1
ANDERS
RÜCKKEHR 0
ENDE
Die gespeicherte Prozedur gibt einen Statuswert von 1 oder 0 zurück, je nachdem, ob die durch den Parameter „cityName“ angegebene Stadt in der Tabelle „Person.Address“ gefunden werden kann.
Im folgenden Beispiel wird dieser Funktion eine offene Verbindung zur AdventureWorks-Beispieldatenbank übergeben und anschließend wird die gespeicherte Prozedur CheckContactCity mithilfe der Methode „execute“ aufgerufen:
Kopieren Sie den Codecode wie folgt:
public static voidexecuteStoredProcedure(Connection con) ...{
versuchen...{
CallableStatement cstmt = con.prepareCall("{? = call dbo.CheckContactCity(?)}");
cstmt.registerOutParameter(1, java.sql.Types.INTEGER);
cstmt.setString(2, "Atlanta");
cstmt.execute();
System.out.println("RÜCKKEHRSTATUS: " + cstmt.getInt(1));
}
cstmt.close();
Catch (Ausnahme e) ...{
e.printStackTrace();
}
}
5. Verwenden Sie eine gespeicherte Prozedur mit einer Aktualisierungsanzahl
Nachdem Sie die SQLServerCallableStatement-Klasse zum Erstellen eines Aufrufs einer gespeicherten Prozedur verwendet haben, können Sie entweder die Methoden „execute“ oder „executeUpdate“ verwenden, um die gespeicherte Prozedur aufzurufen. Die Methode „executeUpdate“ gibt einen int-Wert zurück, der die Anzahl der von dieser gespeicherten Prozedur betroffenen Zeilen enthält, die Methode „execute“ gibt diesen Wert jedoch nicht zurück. Wenn Sie die Methode „execute“ verwenden und die Anzahl der betroffenen Zeilen ermitteln möchten, können Sie die Methode „getUpdateCount“ aufrufen, nachdem Sie die gespeicherte Prozedur ausgeführt haben.
Erstellen Sie beispielsweise die folgenden Tabellen und gespeicherten Prozeduren in der SQL Server 2005 AdventureWorks-Beispieldatenbank:
Kopieren Sie den Codecode wie folgt:
TABELLE ERSTELLEN TestTable
(Col1 int IDENTITY,
Col2 varchar(50),
Col3 int);
PROZEDUR ERSTELLEN UpdateTestTable
@Col2 varchar(50),
@Col3 int
ALS
BEGINNEN
Testtabelle aktualisieren
SET Col2 = @Col2, Col3 = @Col3
ENDE;
Im folgenden Beispiel wird dieser Funktion eine offene Verbindung zur AdventureWorks-Beispieldatenbank übergeben, sie verwendet die Methode „execute“, um die gespeicherte Prozedur „UpdateTestTable“ aufzurufen, und verwendet dann die Methode „getUpdateCount“, um die Anzahl der von der gespeicherten Prozedur betroffenen Zeilen zurückzugeben.
Kopieren Sie den Codecode wie folgt:
public static voidexecuteUpdateStoredProcedure(Connection con) ...{
versuchen...{
CallableStatement cstmt = con.prepareCall("{call dbo.UpdateTestTable(?, ?)}");
cstmt.setString(1, "A");
cstmt.setInt(2, 100);
cstmt.execute();
int count = cstmt.getUpdateCount();
cstmt.close();
System.out.println("BETROFFENE ZEILEN: " + count);
}
Catch (Ausnahme e) ...{
e.printStackTrace();