JAVA calls stored procedures

Transferred from: http://blog.csdn.net/java2000_net/article/details/2311956

This article is longer and contains the following sections

2. Using stored procedures with no parameters
4. Using a stored procedure with input parameters
6. Using a stored procedure with output parameters
8. Using a stored procedure with a return state
9. using a stored procedure with an update count
   

   1 using stored procedures with no parameters

   When you invoke a stored procedure without parameters using the JDBC driver, you must use the call SQL escape sequence. The syntax for a call escape sequence without parameters is as follows:
   {Call Procedure-name}
   As an instance, create the following stored procedure in the SQL Server 2005 AdventureWorks Sample database:

   CREATE PROCEDURE getcontactformalnames asbegin    Ten Title + ' + FirstName + ' + LastName as formalname from    person.contactend

   This stored procedure returns a single result set that contains a column of data (consisting of the salutation, name, and last names of the first 10 contacts in the Person.Contact table).
   
   In the following instance, an open connection to the AdventureWorks sample database is passed to this function, and then using theExecuteQueryThe Getcontactformalnames method invokes the stored procedure.
   

    Public Static void executesprocnoparams (Connection con) {   try  {      = con.createstatement ();       = 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 a stored procedure with input parameters

   when you invoke a stored procedure with parameters using the JDBC driver, you must combine sqlserverconnection Class of Preparecall method uses the call SQL escape sequence. The syntax for a call escape sequence with an in parameter is 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 use one of the setter methods of the Sqlserverpreparedstatement class to specify a value for the parameter. 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 a stored procedure contains a single in parameter, its ordinal value is 1. If a 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 an in parameter, use the SQL Server 2005 AdventureWorks uspGetEmployeeManagers stored procedure in the sample database. This stored procedure takes 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 calls this stored procedure:

   
   

    Public Static voidexecutesprocinparams (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 a stored procedure with output parameters

   When you call such a stored procedure using the JDBC driver, you must combinesqlserverconnectionClass ofPreparecallMethod uses the call SQL escape sequence. The syntax for a call escape sequence with an out parameter is as follows:
   {Call procedure-name[([Parameter][,[parameter]] ...)}
   
   When constructing a call escape sequence, use the? (question mark) character to specify the 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 an out parameter, you must use it before running the stored proceduresqlservercallablestatementClass ofRegisteroutparameterMETHOD specifies the data type of each parameter.
   
   UseRegisteroutparameterThe value specified by the method for the out parameter must be one of the JDBC data types that java.sql.Types contains, and it is also mapped to one of the cost-of-SQL Server data types. For more information about JDBC and SQL Server data types, seeUnderstanding the JDBC driver data type。
   
   When you give an out parameter to theRegisteroutparametermethod to pass a value, you must not only specify the data type to use for this parameter, but you must specify 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, create the following stored procedure in the SQL Server 2005 AdventureWorks Sample database:According to the specified integer in parameter (employeeID), the stored procedure also returns a single integer out parameter (managerid). The value returned in the Employeeid,out parameter contained in the HumanResources.Employee table is ManagerID.
   
   
   In the following instance, an open connection to the AdventureWorks sample database is passed to this function, and then using theExecuteMethod calls the Getimmediatemanager stored procedure:
   

    Public Static void executestoredprocedure (Connection con) {   try  {      = 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 an ordinal position to identify the parameter. Alternatively, you can use the name of the parameter, rather than 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:
   

    create  procedure   Getimmediatemanager   @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 2005 JDBC Driver follows the JDBC 3.0 specification, which specifies that multiple result sets and update counts should be retrieved before retrieving out parameters. That is, the application should retrieve all ResultSet objects and update counts first, and then use the callablestatement.getter method to retrieve the out parameters. Otherwise, when the out parameter is retrieved, the ResultSet object and the update count that have not been retrieved are lost.

   4 using a stored procedure with a return state

   When you call this stored procedure using the JDBC driver, you must combinesqlserverconnectionClass ofPreparecallMethod uses the call SQL escape sequence. The syntax for a call escape sequence that returns a status parameter is 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 use it before executing the stored proceduresqlservercallablestatementClass ofRegisteroutparametermethod to specify the data type of the parameter.
   
   In addition, toRegisteroutparameterWhen a method passes a return status parameter value, you need to specify not only the data type of the parameter 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 a stored procedure is called. AlthoughsqlservercallablestatementThe class supports using the name of the parameter to indicate a specific parameter, but you can only use the ordinal position number of the parameter for the return status parameter.
   As an instance, create the following stored procedure in the SQL Server 2005 AdventureWorks Sample database:
   

   CREATE PROCEDUREcheckcontactcity (@cityName CHAR( -)) asBEGIN   IF((SELECT COUNT(*)    fromperson.addressWHERECity= @cityName)> 1)   RETURN 1ELSE   RETURN 0END

   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 open connection to the AdventureWorks sample database is passed to this function, and then the checkcontactcity stored procedure is invoked using the execute method:
   

    Public Static void executestoredprocedure (Connection con) {   try  {      = 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 using a stored procedure with update count

   After you use the Sqlservercallablestatement class to build a call to a stored procedure, 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 instance, create the following tables and stored procedures in the SQL Server 2005 AdventureWorks Sample database:
   

   CREATE TABLEtesttable (Col1int IDENTITY, Col2varchar( -), Col3int);CREATE PROCEDUREupdatetesttable@Col2 varchar( -),   @Col3 int asBEGIN   UPDATEtesttableSETCol2= @Col2, Col3= @Col3END;

   In the following instance, the open connection to the AdventureWorks sample database is passed to this function and is used with theExecuteMethod calls the updatetesttable stored procedure and then uses theGetupdatecountmethod returns the Count of rows affected by the stored procedure.
   

    Public Static void executeupdatestoredprocedure (Connection con) {   try  {      = Con.preparecall ("{Call Dbo. Updatetesttable (?,?)} " );      Cstmt.setstring (1, "A");      Cstmt.setint (2, +);      Cstmt.execute ();       int count = cstmt.getupdatecount ();      Cstmt.close ();      System.out.println ("ROWS affected:" + count);   }    Catch (Exception e) {      e.printstacktrace ();   }}

JAVA calls stored procedures