Java programming calls the stored procedure to get the new record id number of the implementation method

This article illustrates an example of how to obtain a new record id number in a Java programming call stored procedure. Share with you for your reference, as follows:

As for the ms SQL server2000 stored procedure, the main purpose is to insert a record in the table test and then get the id number of the newly added record.

Test table three fields:

ID: automatic growth
Yhm: username string type
Kl: code     String type

So how do you call this stored procedure in a Java program to get the id number of the newly added record

The stored procedure is as follows:

CREATE PROCEDURE yh_insert
@yhm varchar(50),@kl varchar(50)
AS
begin
set nocount on
 insert into test(yhm,kl) values(@yhm,@kl)
set nocount off
select newid=@@identity
end
GO

Solutions:

Method to execute sp in the query analyzer

declare @id int
exec sp_yh_insert 'tetstst','111111',@id output
select @id

Modify the sp as follows: use the output parameter to store the resulting new Id

CREATE PROCEDURE sp_yh_insert
@yhm varchar(50),@kl varchar(50),@id int output
AS
begin
set nocount on
 insert into test(yhm,kl) values(@yhm,@kl)
set nocount off
--select newid=@@identity
select @id=@@identity -- The key 
end
GO

The Java program is as follows:

public String call_sp_insert_jh(String yhm,String kl)throws Exception
{
  String strFlag = "";
  String strString = "";
  Connection conn = null;
  try
   {
    conn = db.getConnection();
    //CallableStatement proc = conn.prepareCall(strSql);
    CallableStatement  proc=conn.prepareCall("{call sp_yh_insert(?,?,?)}");
    proc.setString(1, " I'm often hungry "); //Assign a value to the first input parameter
    proc.setString(2, "1111111"); //Assign a value to the second input parameter
    proc.registerOutParameter(3,Types.INTEGER); //Processing output parameters
    proc.execute(); //Execute sp
    int id = proc.getInt(3);//Gets the value of the return value
    strString=Integer.toString(id);
    strFlag=strString ;
   }
  catch (SQLException e)
   {
     System.out.println("proc execute error"+strString);
    }
  finally
   {
     //Close the database join
     try
      {
      conn.close();
      }
     catch(Exception sqle)
      {
      //When a new exception is generated, a new program exception is thrown
      //throw new Exception("[DBBean.executeQuery(sql,tname)]","10");
      System.out.println(" Make a mistake ");
      }
    }
  return strFlag;
}

I hope this article has been helpful to you in Java programming.