This article describes the implementation method of obtaining the new record id number in the stored procedure of Java programming calls. Share it for your reference, as follows:
Regarding the stored procedure of msql server2000, the main function is to insert a record in the table test and then get the id number of the newly added record.
The test table has three fields:
ID: Automatic growth
yhm: User name string type
kl: Password string type
So how to call this stored procedure in a java program to achieve it and 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)ASbeginset nocount on insert into test(yhm,kl) values(@yhm,@kl)set nocount offs elect newid=@@identityendGO
Solution:
Methods of executing sp in query analyzer
declare @id intexec sp_yh_insert 'tetstst','111111',@id outputelect @id
Modify sp as follows: Use the output parameters to store the resulting new Id
CREATE PROCEDURE sp_yh_insert@yhm varchar(50),@kl varchar(50),@id int outputASbeginset nocount on insert into test(yhm,kl) values(@yhm,@k l)set nocount off--select newid=@@identityselect @id=@@identity --key endGO
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 { con n = db.getConnection(); //CallableStatement proc = conn.prepareCall(strSql ); CallableStatement proc=conn.prepareCall("{call sp_yh_insert(?,?,?)}"); proc.setString(1, "often outside the hunger"); // Assign the value of proc to the first input parameter .setString(2, "1111111"); // Assign the value to the second input parameter proc.registerOutParameter(3,Types.INTEGER); //Process the output parameter proc.execute(); //Execute sp int id = proc. getInt(3);//Get the return value of strString=Integer.toString(id); strFlag=strString ; } catch (SQLException e) { System.out.println("proc execute error"+ strString); } finally { //Close the database connection try { conn.close(); } catch(Exception sqle) { //Create a new exception, throw a new program exception // throw new Exception("[DBBean.executeQuery(sql,tname)]" ,"10"); System.out.println("Error"); } } return strFlag;}I hope this article will be helpful to everyone's Java programming.