Everyone who has used mybatis knows that it is a project that was renamed after the acquisition of ibatis by Google, so it has also made a lot of upgrades. This article will introduce the difference between the two in calling stored procedures. ibatis has a special tag < procedure > which is no longer available in mybatis, but is distinguished by a parameter statementType="CALLABLE".

Stored procedures:

ALTER PROCEDURE [dbo].[Pro_Create_Number]
    @ChannelNo VARCHAR(10) ,--Channel number
    @Number VARCHAR(50) OUTPUT --Number to be output
AS
    BEGIN
    
    -- Eliminate 10,000 words
    Select @Number ;

    END;

ibatis configuration writing:

<parameterMap id="param" class="java.util.HashMap" > 
      <parameter property="channelNo" jdbcType="VARCHAR"  javaType="java.lang.String"  mode="IN"/>
      <parameter property="number" jdbcType="VARCHAR"  javaType="java.lang.String"  mode="OUT"/>  
</parameterMap>  
  
<procedure id="getNumber" parameterMap ="param" resultClass="java.lang.String">  
      {call [DBName].[dbo].[Pro_Create_Number](?,?)}
</procedure>

mybatis configuration writing:

<parameterMap id="param" class="java.util.HashMap" > 
      <parameter property="channelNo" jdbcType="VARCHAR"  javaType="java.lang.String"  mode="IN"/>
      <parameter property="number" jdbcType="VARCHAR"  javaType="java.lang.String"  mode="OUT"/>  
</parameterMap>  
  
<select id="getNumber" parameterMap="param" statementType="CALLABLE" resultType="java.lang.String">  
      {call [DBName].[dbo].[Pro_Create_Number](?,?)}
</select>

Java method calls:

public String getNumber(String channelNo) {
    Map<String, String> parmap = new HashMap<String, String>();
    parmap.put("channelNo", channelNo);
    String number = null;
    parmap.put("number", number);
    this.sqlSession.selectOne("getNumber", parmap);
    return parmap.get("number");
}

Using ibatis and mybatis to call stored procedures in the xml configuration can be clearly seen in the above code, not to mention here, through the above code we can get the corresponding result set, return parameters and output parameters. The return parameter and output parameter are placed in the incoming parmap, where Number is the output parameter of the stored procedure and the java variable number is the return parameter.

Matters needing attention:

1. The parameters and names of stored procedures are independent of order.

2. The output parameters of stored procedures can only be obtained by the incoming map

3. The result set returned by the stored procedure can be received directly by the returned map.

4. The return result of stored procedure needs to be received by the first parameter of call procName(?,?), and the corresponding mode needs to be specified as OUT type.

5. The corresponding data type of stored procedure is enumeration type, which needs to be capitalized, such as VARCHAR.

Output is the return value of the parameters in the stored procedure (output parameter), while ReturnValue is the return value of the stored procedure (using return keyword). A stored procedure can have any number of values returned by the parameters, but only one ReturnValue.

Stored procedures fall into three main categories:

1. Storage procedure returning a recordset: The result of execution is a recordset, for example, retrieving records from a database that meet one or more criteria.

2. A stored procedure that returns a value (also known as a scalar stored procedure) returns a value after execution, such as a function or command with a return value in a database.

3. Behavioral stored procedures, which are used to implement a function of the database without returning values, such as updating and deleting operations in the database.

Additionally, the stored procedure with return returns the value returned by return.

The stored procedure without return has a return value of 0 regardless of the execution result and no result set.

A stored procedure with output parameters returns the value returned by RETURN if it has RETURN. If SELECT outputs parameters, the value of output parameters appears, regardless of whether RETURN is present or not.