Java calls the stored procedure
1. Use stored procedures without parameters
When you use a jdbc driver to call a stored procedure without parameters, you must use the call SQL escape sequence. The syntax for the call escape sequence without parameters is as follows:
{Call procedure-name} is used as an instance. In the SQL server 2005 adventureworks sample database tutorial, create the following stored procedure:
The following is a reference clip:
Create procedure getcontactformalnames
As
Begin
Select top 10 title + ''+ firstname +'' + lastname as formalname
From person. contact
End
This stored procedure returns a single result set containing a column of data (composed of the names, names, and surnames of the first 10 contacts in the person. contact table ).
In the following example, the open connection of the adventureworks sample database will be passed to this function, and the getcontactformalnames stored procedure will be called using the executequery method.
The following is a reference clip:
Public static void executesprocnoparams (connection con )...{
Try ...{
Statement stmt = con. createstatement ();
Resultset rs = stmt.exe cutequery ("{call dbo. getcontactformalnames }");
While (rs. next ())...{
System. out. println (rs. getstring ("formalname "));
}
Rs. close ();
Stmt. close ();
}
Catch (exception e )...{
E. printstacktrace ();
}
}
2. Use stored procedures with input parameters
When you use a jdbc driver to call 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: {call procedure-name [([parameter] [, [parameter]...)]} when constructing the call escape sequence, use? (Question mark) character to specify the in parameter. This character acts as a placeholder for the parameter values to be passed to the stored procedure. You can use one of the setter methods of the sqlserverpreparedstatement class to specify a value for the parameter. The applicable setter method is determined by the data type of the in parameter.
When passing a value to the setter method, you must 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 a stored procedure contains a single in parameter, the ordinal value is 1. If the stored procedure contains two parameters, the first ordinal value is 1 and the second ordinal value is 2. Use the uspgetemployeemanagers stored procedure in the SQL server 2005 adventureworks sample database as an example of how to call a stored procedure containing the in parameter. This stored procedure accepts a single input parameter named employeeid (which is an integer) and returns a recursive list of employees and their managers based on the specified employeeid. The following is the java code that calls this stored procedure:
Public static void executesprocinparams (connection con )...{
Try ...{
Preparedstatement pstutorial tmt = con. preparestatement ("{call dbo. uspgetemployeemanagers (?)} ");
Pstmt. setint (1, 50 );
Resultset rs = pstmt.exe cutequery ();
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 ();
}
}