Java calls SQL Server stored procedures

1. Calling normal stored procedures
(1) Creating a stored procedure
CREATE Procedure [dbo]. [Getcontactlistbyname]/* Get contact information by 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 '% ' [e-mail protected]+ '% ' 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.executequery ();
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, you do not need to write parentheses, as
CallableStatement Callablestatement=connection.preparecall ("{Call Getallcontactgroup}");

2. Call a stored procedure that contains a return value and an output parameter
(1) Creating a stored procedure
Use [AddressList]
GO
CREATE PROCEDURE [dbo]. [Getgroupbyid]/* Get group information by group number */
@GroupName nvarchar output,/* Export parameter */
@Memo nvarchar output,/* Export parameter */
@id int
As
BEGIN
Select @GroupName =groupname, @Memo =memo from Contactgroup where [email protected]
If @ @Error <>0
RETURN-1/* return value */
Else
return 0/* Returns 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.execute ();
//Get return value
int Returnvalue=callablestatement.getint (1);
//Get output parameters
String groupname=callablestatement.getstring (2);
//Get 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 a stored procedure that contains input and output parameters.
(1) Creating a stored procedure
Use [AddressList]
GO
SET ANSI_NULLS on
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo]. [Test]
@GroupName nvarchar () output
As
BEGIN
Select @GroupName =groupname from Contactgroup where GroupName like '% ' [email protected]+ '% '
END
(2) Java code
callablestatement Callablestatement=connection.preparecall ("{Call Test (?)}");
callablestatement.setstring (1, name);
callablestatement.registeroutparameter (1, Types.varchar);
Callablestatement.execute ();
String string=callablestatement.getstring (1);
System.out.println (string);

Java calls SQL Server stored procedures