Java call SQL Server stored procedures detailed _java

This article describes how to use Java to invoke SQL Server stored procedures, detailed 5 different kinds of storage. Please see below for details

1, the use of stored procedures with no parameters

When using the JDBC driver to invoke stored procedures without parameters, you must use the call SQL escape sequence. The syntax for the call escape sequence with no parameters is as follows:

Copy Code code as follows:

{Call Procedure-name}

As an example, the following stored procedures are created in the SQL Server AdventureWorks sample database:

Copy Code code as follows:

CREATE PROCEDURE Getcontactformalnames
As
BEGIN
SELECT Top Title + ' + FirstName + ' + LastName as Formalname
From Person.Contact
End

This stored procedure returns a single result set that contains a column of data (consisting of the salutation, name, and last name of the first 10 contacts in the Person.Contact table).

In the following instance, this function is passed an open connection to the AdventureWorks sample database, and then the getcontactformalnames stored procedure is invoked using the ExecuteQuery method.

Copy Code code as follows:

public static void Executesprocnoparams (Connection con) ... {
Try ... {
Statement stmt = Con.createstatement ();
ResultSet rs = stmt.executequery ("{call dbo.") Getcontactformalnames} ");

while (Rs.next ()) ... {
System.out.println (rs.getstring ("Formalname"));
}
Rs.close ();
Stmt.close ();
}
catch (Exception e) ... {
E.printstacktrace ();
}
}

2. Using stored procedures with input parameters

When you use the JDBC driver to invoke a stored procedure with parameters, you must use the call SQL escape sequence in conjunction with the Preparecall method of the Sqlserverconnection class. The syntax for the call escape sequence with the in parameter is as follows:

Copy Code code as follows:

{Call procedure-name[([Parameter][,[parameter]] ...)]}

When constructing a call escape sequence, use the? (question mark) character to specify the in parameter. This character acts as a placeholder for the parameter value to pass to the stored procedure. You can specify a value for a parameter by using one of the setter methods of the Sqlserverpreparedstatement class. The setter method that can be used is determined by the data type of the in parameter.

When passing a value to a setter method, you need to specify not only the actual value to be used in the parameter, but also the ordinal position of the parameter in the stored procedure. For example, if the stored procedure contains a single in parameter, its ordinal value is 1. If the stored procedure contains two parameters, the first ordinal value is 1, and the second ordinal value is 2.

As an instance of how to invoke a stored procedure that contains the in parameter, use the uspgetemployeemanagers stored procedure in the SQL Server AdventureWorks sample database. This stored procedure accepts a single input parameter named EmployeeID, which is an integer value, and then returns a recursive list of employees and their managers based on the specified EmployeeID. The following is the Java code that invokes this stored procedure:

Copy Code code as follows:

public static void Executesprocinparams (Connection con) ... {
Try ... {
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 ("LastName") + "," + rs.getstring ("FirstName"));
System.out.println ("MANAGER:");
System.out.println (rs.getstring ("managerlastname") + "," + rs.getstring ("Managerfirstname"));
System.out.println ();
}
Rs.close ();
Pstmt.close ();
}
catch (Exception e) ... {
E.printstacktrace ();
}
}

3. Using stored procedures with output parameters

When invoking this type of stored procedure using the JDBC driver, you must use the call SQL escape sequence in conjunction with the Preparecall method of the Sqlserverconnection class. The syntax for the call escape sequence with out parameters is as follows:

Copy Code code as follows:

{Call procedure-name[([Parameter][,[parameter]] ...)]}

When constructing a call escape sequence, use the? (question mark) character to specify an out parameter. This character acts as a placeholder for the value of the parameter to be returned from the stored procedure. To specify a value for the out parameter, you must specify the data type of each parameter by using the Registeroutparameter method of the Sqlservercallablestatement class before running the stored procedure.

The value specified for the out parameter using the Registeroutparameter method must be one of the JDBC data types contained by Java.sql.Types, and it is mapped to one of the cost SQL Server data types. For more information about JDBC and SQL Server data types, see Understanding JDBC Driver data types.

When you pass a value to the Registeroutparameter method for an out parameter, you must specify not only the data type that you want to use for this parameter, but also the ordinal position of the parameter or the name of the parameter in the stored procedure. For example, if a stored procedure contains a single out parameter, its ordinal value is 1, and if the stored procedure contains two parameters, the first ordinal value is 1, and the second ordinal value is 2.

As an instance, the following stored procedure is created in the SQL Server AdventureWorks sample database: Based on the specified integer in parameter (employeeID), the stored procedure also returns a single integer out parameter (managerid). The value returned according to the Employeeid,out parameter contained in the HumanResources.Employee table is ManagerID.

In the following instance, the function is passed an open connection to the AdventureWorks sample database, and then the Getimmediatemanager stored procedure is called using the Execute method:

Copy Code code as follows:

public static void Executestoredprocedure (Connection con) ... {
Try ... {
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 (Exception e) ... {
E.printstacktrace ();
}
}

This example uses the ordinal position to identify the parameter. Alternatively, you can use the name of the parameter (not its ordinal position) to identify this parameter. The following code example modifies the previous example to show how to use named parameters in a Java application. Note that these parameter names correspond to the parameter names in the definition of the stored procedure: 11x16create PROCEDURE Getimmediatemanager

Copy Code code as follows:

@employeeID INT,
@managerID INT OUTPUT
As
BEGIN
SELECT @managerID = ManagerID
From HumanResources.Employee
WHERE EmployeeID = @employeeID
End

The stored procedure may return an update count and multiple result sets. Microsoft SQL Server Driver JDBC follows the JDBC 3.0 specification, which stipulates that multiple result sets and update counts should be retrieved before retrieving out parameters. That is, the application should retrieve all the ResultSet objects and update counts first, and then use the Callablestatement.getter method to retrieve the out parameters. Otherwise, ResultSet objects and update counts that have not been retrieved will be lost when the out parameter is retrieved.

4. Use stored procedures with return status

When invoking this stored procedure using the JDBC driver, you must use the call SQL escape sequence in conjunction with the Preparecall method of the Sqlserverconnection class. The syntax for the call escape sequence that returns the status parameter is as follows:

Copy Code code as follows:

{[? =]call procedure-name[([Parameter][,[parameter]] ...)]}

When constructing a call escape sequence, use the? (question mark) character to specify the return status parameter. This character acts as a placeholder for the value of the parameter to be returned from the stored procedure. To specify a value for the return status parameter, you must specify the data type of the parameter using the Registeroutparameter method of the Sqlservercallablestatement class before executing the stored procedure.

In addition, when passing a return status parameter value to the Registeroutparameter method, you need to specify not only the data type of the parameter you want to use, but also the ordinal position of the parameter in the stored procedure. For a return status parameter, its ordinal position is always 1 because it is always the first parameter when the stored procedure is invoked. Although the Sqlservercallablestatement class supports using the name of the parameter to indicate a specific parameter, you can only use the ordinal position number of the parameter for the return status argument.

As an example, the following stored procedures are created in the SQL Server AdventureWorks sample database:

Copy Code code as follows:

CREATE PROCEDURE checkcontactcity
(@cityName CHAR (50))
As
BEGIN
IF (SELECT COUNT (*)
From Person.Address
WHERE city = @cityName) > 1)
Return 1
ELSE
return 0
End

The stored procedure returns a status value of 1 or 0, depending on whether the city specified by the CityName parameter can be found in table person.address.

In the following instance, the function is passed an open connection to the AdventureWorks sample database, and then the checkcontactcity stored procedure is called using the Execute method:

Copy Code code as follows:

public static void Executestoredprocedure (Connection con) ... {
Try ... {
CallableStatement cstmt = Con.preparecall ("{? = Call dbo. Checkcontactcity (?)} ");
Cstmt.registeroutparameter (1, Java.sql.Types.INTEGER);
Cstmt.setstring (2, "Atlanta");
Cstmt.execute ();
System.out.println ("Return STATUS:" + cstmt.getint (1));
}
Cstmt.close ();
catch (Exception e) ... {
E.printstacktrace ();
}
}

5. Use stored procedures with update Count

After you build a call to a stored procedure using the Sqlservercallablestatement class, you can use either the Execute or the Executeupdate method to invoke the stored procedure. The Executeupdate method returns an int value that contains the number of rows affected by this stored procedure, but the Execute method does not return this value. If you use the Execute method and you want to count the number of rows affected, you can call the Getupdatecount method after you run the stored procedure.

As an example, create the following tables and stored procedures in the SQL Server AdventureWorks sample database:

Copy Code code as follows:

CREATE TABLE TestTable
(Col1 int IDENTITY,
Col2 varchar (50),
Col3 int);

CREATE PROCEDURE updatetesttable
@Col2 varchar (50),
@Col3 int
As
BEGIN
UPDATE TestTable
SET Col2 = @Col2, Col3 = @Col3
End;

In the following instance, this function is passed an open connection to the AdventureWorks sample database and the updatetesttable stored procedure is called using the Execute method, and then the Getupdatecount method is used to return the count of rows affected by the stored procedure.

Copy Code code as follows:

public static void Executeupdatestoredprocedure (Connection con) ... {
Try ... {
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 ("ROWS Affected:" + count);
}
catch (Exception e) ... {
E.printstacktrace ();