Java calls SQL Server Stored Procedures, SQL stored procedures

Java calls SQL Server Stored Procedures, SQL stored procedures

1. Call common stored procedures
(1) create a stored procedure
CREATE Procedure [dbo]. [GetContactListByName]/* obtain the contact information based on the contact name */
@ Name nvarchar (50)
As
Begin
Select Contact. Id, Contact. Name, Phone, Email, QQ, GroupName from Contact, ContactGroup
Where Contact. GroupId = ContactGroup. Id and Name like '%' + @ Name + '%' order by Contact. Id desc
End
(2) Java code
Final String DRIVER_CLASS = "com. microsoft. sqlserver. jdbc. SQLServerDriver ";
Final String DATABASE_URL = "jdbc: sqlserver: // 127.0.0.1: 1433; DatabaseName = AddressList ";
Final String DATABASE_USRE = "sa ";
Final String DATABASE_PASSWORD = "1234 ";
Try {
Class. forName (DRIVER_CLASS );
Connection connection = DriverManager. getConnection (DATABASE_URL, DATABASE_USRE, DATABASE_PASSWORD );
CallableStatement callableStatement = connection. prepareCall ("{call GetContactListByName (?)} ");
CallableStatement. setString (1, name );
ResultSet resultSet=callableStatement.exe cuteQuery ();
While (resultSet. next ()){
Int id = resultSet. getInt (1 );
String string = resultSet. getString (2 );
System. out. println (id + "," + string );
}
} Catch (Exception e ){
// TODO: handle exception
E. printStackTrace ();
}
Note: If the stored procedure has no parameters, no parentheses are required, as shown in figure
CallableStatement callableStatement = connection. prepareCall ("{call GetAllContactGroup }");

2. Call the stored procedure that contains the returned value and Output Parameters
(1) create a stored procedure
USE [AddressList]
GO
Create procedure [dbo]. [GetGroupById]/* obtain group information by group ID */
@ GroupName nvarchar (50) OUTPUT,/* OUTPUT parameter */
@ Memo nvarchar (200) OUTPUT,/* OUTPUT parameter */
@ Id int
AS
BEGIN
Select @ GroupName = GroupName, @ Memo = Memo from ContactGroup where id = @ id
If @ Error <> 0
RETURN-1/* RETURN value */
Else
RETURN 0/* RETURN value */
END
(2) Java code
CallableStatement callableStatement = connection. prepareCall ("{? = Call GetGroupById (?,?,?)} ");
// Return Value
CallableStatement. registerOutParameter (1, Types. INTEGER );
// Output parameters
CallableStatement. registerOutParameter (2, Types. VARCHAR );
// Output parameters
CallableStatement. registerOutParameter (3, Types. VARCHAR );
// Input parameters
CallableStatement. setInt (4, 2 );
CallableStatement.exe cute ();
// Obtain the returned value
Int returnValue = callableStatement. getInt (1 );
// Obtain output parameters
String groupName = callableStatement. getString (2 );
// Obtain output parameters
String memo = callableStatement. getString (3 );
System. out. println (returnValue );
System. out. println (groupName );
System. out. println (memo );
} Catch (Exception e ){
// TODO: handle exception
E. printStackTrace ();
}
3. Call the stored procedure that contains input and output parameters.
(1) create a stored procedure
USE [AddressList]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create procedure [dbo]. [test]
@ GroupName nvarchar (50) output
AS
BEGIN
Select @ GroupName = GroupName from ContactGroup where GroupName like '%' + @ GroupName + '%'
END
(2) Java code
CallableStatement callableStatement = connection. prepareCall ("{call test (?)} ");
CallableStatement. setString (1, name );
CallableStatement. registerOutParameter (1, Types. VARCHAR );
CallableStatement.exe cute ();
String string = callableStatement. getString (1 );
System. out. println (string );