Shulou(Shulou.com)06/01 Report--

This article "mybatis how to call mysql stored procedures" is not quite understood by most people except programmers. Today, in order to make you better understand "how mybatis calls mysql stored procedures", Xiaobian summed up the following content, which has a certain reference value. The detailed steps are clear and the details are handled properly. I hope you can get something through this article. Let's take a look at the specific content.

Receive a return value

Use Map to receive the return parameter, and put the output parameter in the incoming param

Create a tabl

DROP TABLE IF EXISTS `demo`; CREATE TABLE `demo` (`id` int (11) NOT NULL AUTO_INCREMENT, `name` varchar (255th) DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=32 DEFAULT CHARSET=utf8;-Records of demo-- INSERT INTO `demo` VALUES ('1benchmark,' test')

Create a stored procedure

DROP PROCEDURE IF EXISTS pro;CREATE PROCEDURE pro (IN userId INT, OUT userCount INT) BEGIN DECLARE user_name VARCHAR (64); SELECT NAME FROM demo WHERE id = userId INTO user_name;INSERT INTO demo (NAME) VALUES (user_name); SELECT COUNT (*) FROM demo INTO userCount; END

Mapper.xml

CALL pro (# {userId,mode=IN}, # {userCount,mode=OUT,jdbcType=INTEGER})

Service

@ Override public void procedureTest () {Map param = new HashMap (); param.put ("userId", "1"); demoMapper.procedureTest (param); System.out.println ("output result:" + param.get ("userCount"));}

Output result

Parameter names are not necessarily the same as stored procedures, only related to passing parameters. The results can also be obtained using the following figure configuration.

System.out.println ("output result is:" + param.get ("userCount_2"))

Note:

1. The parameters of the stored procedure are independent of the name, but only related to the order.

2. The output parameter of the stored procedure can only be obtained through the passed map.

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

4. The return result of the stored procedure needs to use? = call procName (,?) You need to specify that the corresponding mode is of type OUT

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

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

Stored procedures are divided into three main categories:

1. Return the stored procedure of the recordset: the result of execution is a recordset, for example, retrieving records from the database that meet one or more conditions.

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

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

2. Receive list result set

Receive using list

@ Override public void procedureTest () {Map param = new HashMap (); param.put ("userId", "1"); List list = demoMapper.procedureTest (param); System.out.println ("output result: + param.get (" userCount_2 ")); System.out.println (" output set: "+ list.size ()) } CALL pro (# {userId,mode=IN}, # {userCount_2,mode=OUT,jdbcType=INTEGER}); III. Return multiple result sets

4. The second configuration can also be CALL s_generatePlan_new (# {organizationId, mode=IN,jdbcType=VARCHAR}, # {gradeId, mode=IN,jdbcType=VARCHAR}, # {semesterId, mode=IN,jdbcType=VARCHAR}, # {className, mode=IN,jdbcType=VARCHAR}, # {employeeCode, mode=IN,jdbcType=VARCHAR}, # {courseName, mode=IN,jdbcType=VARCHAR}, # {classroomName, mode=IN,jdbcType=VARCHAR}, # {approverId, mode=IN,jdbcType=VARCHAR}, # {createBy, mode=IN,jdbcType=VARCHAR}, # {total,mode=OUT, jdbcType=VARCHAR}). I hope you have a certain understanding of the key issue of "how to call mysql stored procedures in mybatis". The specific usage still needs to be understood by hands-on experiments. Try it quickly. If you want to read more articles about relevant knowledge points, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.