[Transfer] Java calling SQL stored procedure input and output parameters

2010-11-09  来源:本站原创  分类:Database  人气:167 

1 Use a stored procedure without parameters

Using the JDBC driver calls the stored procedure without parameters, you must use the call SQL escape sequence. Call with no arguments The syntax of the escape sequence is as follows:
{Call procedure-name}
As an example, in SQL Server 2005 AdventureWorks sample database, create the following stored procedure:

SQL code:
CREATE PROCEDURE GetContactFormalNames ASBEGIN SELECT TOP 10 Title + '' + FirstName + '' + LastName AS FormalName FROM Person.Contact END

This stored procedure returns a single result set that contains a column of data (from the first ten contacts Person.Contact table of address, name and surname form.)

In the following example, this function will be to pass an open connection to AdventureWorks sample database, and then use the executeQuery method calls GetContactFormalNames stored procedures.

Java code:

Method One:

Statement stmt = con.createStatement (); ResultSet rs = stmt.executeQuery ("{call dbo.GetContactFormalNames}"); while (rs.next ()) {System.out.println (rs.getString ("FormalName")) ;}

Method Two:

CallableStatement callstat = con.prepareCall ("{call dbo.GetContactFormalNames}"); ResultSet rs = callstat.executeQuery (); while (rs.next ()) {System.out.println (rs.getString (2));}

2 Use a stored procedure with input parameters

Using the JDBC driver calls the stored procedure with parameters, must be combined with methods used prepareCall SQLServerConnection class call SQL escape sequence. IN parameters with a call escape sequence syntax is as follows:
{Call procedure-name [([parameter] [, [parameter ]]...)]}

Construction call escape sequence, use? (Question mark) character to specify the IN parameter. This character acts as a stored procedure to pass to the placeholders for the parameter values. You can use one of SQLServerPreparedStatement setter for the class parameter specifies the value. Setter method can be used by the IN parameter data type.
Setter methods to pass values, not only need to specify the parameters used in the actual values, you must also specify the stored procedure parameters in ordinal position. For example, if the stored procedure contains a single IN parameter, its ordinal value of 1. If the stored procedure contains two parameters, the first order value of 1, the second sequence value is 2.
IN parameters as how to call a stored procedure that contains an instance, the use of SQL Server 2005 AdventureWorks sample database uspGetEmployeeManagers stored procedure. This stored procedure accepts a single input parameter named EmployeeID (which is an integer value), and then return on the specified EmployeeID recursive list of employees and their managers. The following are calling this stored procedure Java code:

Java code:
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 ();
}

3 Use a stored procedure with output parameters using the JDBC driver <br /> call these stored procedures must be combined with methods used prepareCall SQLServerConnection class call SQL escape sequence. Call with OUT parameters in the syntax of the escape sequence is as follows:
{Call procedure-name [([parameter] [, [parameter ]]...)]}

Construction call escape sequence, use? (Question mark) character to specify the OUT parameter. This character acts as a stored procedure from the parameter values returned by a placeholder. To specify a value for the OUT parameter must be used before running the stored procedure method registerOutParameter SQLServerCallableStatement class data type of the specified parameters.

RegisterOutParameter method using the specified OUT parameter value must be included in JDBC java.sql.Types data types, and it was always map to SQL Server data types. For JDBC and SQL Server data types for more information, see Understanding JDBC Driver data types.

For the OUT parameters when you pass a value to the registerOutParameter method, not only have to specify the data type to use for this parameter, and must specify this parameter stored procedure or the ordinal position of the name of this parameter. For example, if the stored procedure contains a single OUT parameter, its ordinal value of 1; If the stored procedure contains two parameters, the first order value of 1, the second sequence value is 2.
As an example, in SQL Server 2005 AdventureWorks sample database, create the following stored procedure: IN integer according to the specified parameters (employeeID), the stored procedure has OUT parameter returns a single integer (managerID). According to HumanResources.Employee table contains the EmployeeID, OUT parameter returned a value of ManagerID.

In the following example, this function will be to pass an open connection to AdventureWorks sample database, and then use the execute method call GetImmediateManager stored procedure:

Java code:
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));

This example uses the ordinal position to identify the parameters. Alternatively, you can use the parameter's name (rather than its ordinal position) to identify this parameter. The following code example changes the previous example to illustrate how Java applications use named parameters. Please note that these parameter names correspond to the definition of the stored procedure parameter names:

SQL code:
CREATE PROCEDURE GetImmediateManager @ employeeID INT, @ managerID INT OUTPUT ASBEGIN SELECT @ managerID = ManagerID FROM HumanResources.Employee WHERE EmployeeID = @ employeeIDEND

Stored procedure may return multiple result sets and update counts. Microsoft SQL Server 2005 JDBC Driver follows the JDBC 3.0 specification, this specification should be retrieved before OUT parameters to retrieve multiple result sets and update counts. In other words, the application should retrieve all ResultSet objects and update counts, and then use CallableStatement.getter method to retrieve OUT parameters. Otherwise, retrieve OUT parameters, it was not retrieve ResultSet objects and update the count will be lost.

4 Using a stored procedure with return status

Using the JDBC driver calls this stored procedure, must be combined with methods used prepareCall SQLServerConnection class call SQL escape sequence. Return status parameter of the call escape sequence syntax is as follows:
{[?=] Call procedure-name [([parameter] [, [parameter ]]...)]}

Construction call escape sequence, use? (Question mark) character to specify the return status parameter. This character acts as a stored procedure from the parameter values returned by a placeholder. To specify a value for the return status parameter must be used prior to executing the stored procedure method registerOutParameter SQLServerCallableStatement class data type of the specified parameters.

In addition, the return status is passed to the registerOutParameter method parameter values, not only need to specify the data type of the parameters used must also specify the stored procedure parameters in ordinal position. For the return status parameter, its ordinal position is always 1, because it always is to call stored procedure of the first parameter. Although SQLServerCallableStatement class supports the use of parameter names to indicate specific parameters, but you can only use the parameters of the state parameter to return the ordinal position number.
As an example, in SQL Server 2005 AdventureWorks sample database, create the following stored procedure:

SQL code:
CREATE PROCEDURE CheckContactCity (@ cityName CHAR (50)) ASBEGIN IF ((SELECT COUNT (*) FROM Person.Address WHERE City = @ cityName)> 1) RETURN 1ELSE RETURN 0END

The stored procedure returns a status value of 1 or 0, depending on whether it can be found in Table Person.Address cityName parameter specifies the city.

In the following example, this function will be to pass an open connection to AdventureWorks sample database, and then use the execute method call CheckContactCity stored procedure:

Java code:
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));

5 using a stored procedure with the updated count

Construction of classes using SQLServerCallableStatement call the stored procedure, you can use the execute or executeUpdate method to call any of this stored procedure. executeUpdate method returns an int value that contains the stored procedures by this number of rows affected, but the execute method does not return this value. If you use the execute method, and want the number of rows affected count, you can run the stored procedure call getUpdateCount method.

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

SQL code:
CREATE TABLE TestTable (Col1 int IDENTITY, Col2 varchar (50), Col3 int); CREATE PROCEDURE UpdateTestTable @ Col2 varchar (50), @ Col3 intASBEGIN UPDATE TestTable SET Col2 = @ Col2, Col3 = @ Col3END;

In the following example, this function will be to pass an open connection to AdventureWorks sample database, and use the execute method call UpdateTestTable stored procedure, then use the stored procedure getUpdateCount method returns affected by the row count.

Java code:
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);

相关文章
  • [Transfer] Java calling SQL stored procedure input and output parameters 2010-11-09

    1 Use a stored procedure without parameters Using the JDBC driver calls the stored procedure without parameters, you must use the call SQL escape sequence. Call with no arguments The syntax of the escape sequence is as follows: {Call procedure-name}

  • JAVA calling Oracle stored procedure (transfer) 2010-12-10

    In large database systems, there are two very important functions, that is, stored procedures and triggers. Whether it is in the database system stored procedures or triggers, through SQL statements and control flow statements to complete the set. Re

  • JAVA calling DB2 stored procedure 2008-05-28

    Search java call db2 (version 8.2) Stored Procedures and few articles to look at, so I decided to write point, after the novice to use, I hope that my article be useful to them and they can find my articles, Amen 1. JAVA call db2 stored procedure mos

  • JAVA calling Oracle stored procedure (to) 2010-12-10

    In large database systems, there are two very important functions, that is, stored procedures and triggers. Whether in the database system stored procedure or trigger, is through SQL statements and control flow statements to complete the set. In cont

  • JAVA Oracle stored procedure input parameter in the LIST 2010-11-28

    Today, they want nothing to do over the weekend to try a combination of JAVA and ORACLE extent, the results reflect the combination of these two is really very powerful weapon, never been done before, or since. Hey ~ ~ JAVA for the weekend to help my

  • Reprinted eyejava: DB2 sql stored procedure based 2010-04-23

    Basic concepts: Stored procedure or stored procedure, will generally be referred to as procedure. To learn the basis figure out another idea: routine, this general translation as "routine" >> Routine: there is server-side, according to app

  • READ: Distributed DBA: SQL stored procedure master 2011-05-09

    Distributed DBA: SQL stored procedure master Roger E. Sanders , consulting firm Systems Engineer, EMC Corporation Description: To become a certified DB2 9.5 SQL stored procedures developers should ensure that you understand all aspects of the creatio

  • JAVA and ORACLE stored procedure 2010-07-19

    In large database systems, there are two very important functions, that is, stored procedures and triggers. Whether in the database system stored procedure or trigger, is through SQL statements and control flow statements to complete the set. In cont

  • java page SqlServer stored procedure call, return multiple result sets 2011-06-17

    Stored procedure: USE [Db_8za8za_2] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Description: <Description,, Universal paging stored procedure > -- ==========================================

  • mysqldb calling a stored procedure return value question 2010-06-23

    Using mysqldb of callproc Calling a stored procedure, the return value is the input parameter : The question now is how to obtain the stored procedure itself returns the value set by the Stored procedure is as follows: CREATE PROCEDURE test_stuff.get

  • VBA calling a stored procedure 2010-08-21

    VBA calling a stored procedure Has not found a good way to reference the value of the stored procedure to return to the method, that I add data to go to a lot of detours, most recently, a lot of information in the inspection, finally found an instanc

  • The most complete java call oracle stored procedure. Function 2010-11-24

    java call oracle stored procedures, functions, under the following situations: 1.java call stored procedure with parameters 2.java call a stored procedure without parameters 3.java call the function with parameter 4.java function call without paramet

  • java call oracle stored procedure return set 2011-06-08

    java call oracle stored procedure return set 1, pl / sql stored procedures CREATE OR REPLACE PROCEDURE retCursor(ret_cursor OUT SYS_REFCURSOR) IS ret_cursor_value SYS_REFCURSOR; BEGIN OPEN ret_cursor_value FOR SELECT * FROM emp; ret_cursor := ret_cur

  • Advanced into the use of SQL stored procedure (zt) 2011-05-25

    Advanced into the SQL stored procedure used Script 2010-07-20 20:00:41 read 226 comments 0 Word Count: Subscribe ===================================== ===== SQL stored procedure used by advanced injection ====== =====================================

  • oracle with input and output parameters of the call [Reserved] 2011-10-10

    Table test, the following is a test data : select * from test; id info ---------------------- 1 Test data 1 2 Test data 2 3 Test data 3 4 Test data 4 5 Test data 5 6 Test data 6 7 Test data 7 8 Test data 8 9 Test data 9 10 Test data 10 11 Test data 1

  • Calling oracle stored procedure with java Summary 2009-07-18

    1: no return value stored procedure Stored procedure as follows: CREATE OR REPLACE PROCEDURE TESTA (PARA1 IN VARCHAR2, PARA2 IN VARCHAR2) AS BEGIN INSERT INTO HYQ.B_ID (I_ID, I_NAME) VALUES (PARA1, PARA2); END TESTA; Then, when called inside the java

  • Oracle stored procedure PL / Sql stored procedure call (transfer) 2011-08-01

    Summary record of the stored procedure call syntax with Java program mode A stored procedure First, we establish a simple test stored procedure table create table xuesheng (id integer, xing_ming varchar2 (25), yu_wen number, shu_xue number); insert i

  • [Transfer] SQL stored procedure import and export 2010-10-12

    Recent study of SQL stored procedures, and here I use in my store after another encounter in the course of problems and solutions posted online, please prawn correction. Export Import SqlServer stored procedure Open the "Enterprise Manager", sel

  • spring calling Oracle stored procedure and returns a complete example of the result set 2010-06-12

    This is a summary of previous call to Oracle stored procedure using the spring, and with the cursor result set to return a complete example and I hope you can help. 1. Create a table: Java code create table TEST_USERS ( USER_ID VARCHAR2 (10) not null

  • Java call Oracle stored procedure 2010-12-03

    1, the stored procedure is stored in the database and perform the procedure on the database side, it has two types. A similar SELECT query to retrieve the data, the data can be retrieved in the form of a data set returned to the customer. Another sim