Stored procedures are used in MyBatis

Small knowledge, big challenge! This article is participating in the creation activity of “Essential Tips for Programmers”

Stored procedures are used in MyBatis

MySQL stored procedure (); MySQL stored procedure (); We used MyBatis for most of our development, so this article will focus on how to use stored procedures in MyBatis.

When using stored procedure in MyBatis, the two main points are: how should we pass in the input parameters needed when calling the stored procedure? How should we get the output parameters after executing the stored procedure?

Call a normal stored procedure

Data preparation

Prepare two database tables, user and Accounts

CREATE TABLE 'user' (' id 'int(11) NOT NULL AUTO_INCREMENT,' username 'varchar(32) NOT NULL COMMENT' user ', 'birthday' datetime DEFAULT NULL COMMENT 'birthday ',' sex 'char(1) DEFAULT NULL COMMENT' sex ', 'address' varchar(256) DEFAULT NULL COMMENT 'address ', PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=78 DEFAULT CHARSET=utf8Copy the code

CREATE TABLE `accounts` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` char(100) NOT NULL,
  `money` float DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8
Copy the code

Create a normal stored procedure in SqlYog named test1

DELIMITER $$

CREATE
    PROCEDURE `students`.`test1`()
    BEGIN
	SELECT * FROM USER;
    END$$

DELIMITER ;
Copy the code

Configure MyBatis here to skip, directly on the Dao layer write interface methods, and write in XML to call the STORED procedure SQL

Dao methods

Public interface UserMapper extends BaseMapper<User> {List<User> test1(); }Copy the code

Xml mapping file

<? The XML version = "1.0" encoding = "utf-8"? > <! DOCTYPE mapper PUBLIC "- / / mybatis.org//DTD mapper / 3.0 / EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" > < mapper namespace="com.example.mp.mapper.UserMapper"> <! Since the result of our query returns multiple records, <resultMap ID ="BaseResultMap" type="com.example.mp.pojo.User"> < ID column=" ID "Property =" ID "></ ID >  <result column="sex" property="sex"></result> <result property="birthday" column="birthday"></result> <result property="username" column="username"></result> <result property="address" column="address"></result> </resultMap> <! Call test1 --> <select id="test1" statementType="CALLABLE" resultMap="BaseResultMap" > call test1(); </select> </mapper>Copy the code

🚦❗ three optional values of statementType:

CALLABLE: executes stored procedures

PREPARED (default) : preprocess, precompile

SQL STATEMENT: ${}

⚒ Similarly, if multiple result sets are returned (for example, if a stored procedure queries all the data of two tables, two different result sets are returned), the corresponding resultMap of the configuration SELECT label should be configured as follows:

<! <resultMap ID ="BaseResultMap" type="com.example.mp.pojo.User"> <id column=" ID "Property ="id"></ ID >< result column="sex" property="sex"></result> <result property="birthday" column="birthday"></result> <result property="username" column="username"></result> <result property="address" column="address"></result> </resultMap> <! Result set 2 - - - > < resultMap id = "resultMap2" type = "com. Example. Mp. Pojo. Accounts" > < id column = "id" property = "id" > < / id > < the result column="name" property="name"></result> <result column="money" property="money"></result> </resultMap> <select id="test1" statementType="CALLABLE" resultMap="BaseResultMap,resultMap2" > call test1(); </select>Copy the code

test

🚗 returns a single result set:

@Test void test() { List<User> users = userMapper.test1(); for (User user : users) { System.out.println(user.toString()); }}Copy the code

Console output:

🚈 returns multiple result sets:

Corresponding Dao interface method

List<List<? >> test1();Copy the code

@Test
void test() {
    List<List<?>> lists = userMapper.test1();
    for (List<?> list : lists) {
        for (Object o : list) {
            System.out.println(o);
        }
    }
}
Copy the code

Console output

Calls the stored procedure that takes the parameter

Create a stored procedure In the In model that uses the function of inserting new data into the accounts table

DELIMITER $$

USE `students`$$

DROP PROCEDURE IF EXISTS `test2`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `test2`(IN id INT,IN NAME VARCHAR(20),IN money DOUBLE)
BEGIN
	INSERT INTO accounts (id,NAME,money) VALUES(id,NAME,money);
    END$$

DELIMITER ;
Copy the code

The Dao interface

void test2(Accounts accounts);
Copy the code

XML mapping file

<insert id="test2" statementType="CALLABLE" parameterType="com.example.mp.pojo.Accounts">
    call test2(
    #{id,mode=IN,jdbcType=INTEGER},
    #{name,mode=IN,jdbcType=VARCHAR},
    #{money,mode=IN,jdbcType=DOUBLE}
    )
</insert>
Copy the code

test

@test void Test () {Accounts ac = new Accounts(5, "Programmer", 1000.0); userMapper.test2(ac); }Copy the code

Console output

Call a stored procedure with an output parameter

Create an OUT model stored procedure that uses the function of getting the number of records in the table Accounts

DELIMITER $$ USE `students`$$ DROP PROCEDURE IF EXISTS `test3`$$ CREATE DEFINER=`root`@`localhost` PROCEDURE `test3`(OUT  num INTEGER) BEGIN SELECT COUNT(*) INTO num FROM accounts; END$$ DELIMITER ;Copy the code

Dao methods

void test3(HashMap<String, Integer> map);
Copy the code

XML mapping file

<! <parameterMap id="pmap" type="java.util.Map"> <parameter Property ="num" mode="OUT" jdbcType="INTEGER"></parameter> </parameterMap> <select id="test3" parameterMap="pmap" statementType="CALLABLE"> call test3(?) ; </select>Copy the code

test

@Test
void test() {
    HashMap<String, Integer> map = new HashMap<>();
    map.put("num",-1);
    userMapper.test3(map);
    System.out.println(map.get("num"));
}
Copy the code

Console output

Calls a stored procedure that has both outgoing and incoming parameters

Create an OUT model stored procedure that uses the ability to add a record to the table Accounts and query the number of records in the table

DELIMITER $$

USE `students`$$

DROP PROCEDURE IF EXISTS `test4`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `test4`(IN id INTEGER,IN NAME VARCHAR(20),IN money INTEGER, OUT num INTEGER)
BEGIN
	INSERT INTO accounts (id,NAME,money) VALUES(id,NAME,money);
	SELECT COUNT(*) INTO num FROM accounts;
    END$$

DELIMITER ;
Copy the code

Dao methods

void test4(HashMap<String, Object> map);
Copy the code

XML mapping file

<parameterMap id="pmap2" type="java.util.Map"> <parameter property="id" mode="IN" jdbcType="INTEGER"></parameter> <parameter property="name" mode="IN" jdbcType="VARCHAR"></parameter> <parameter property="money" mode="IN" jdbcType="INTEGER"></parameter> <parameter property="num" mode="OUT" jdbcType="INTEGER"></parameter> </parameterMap> <insert id="test4" parameterMap="pmap2" statementType="CALLABLE"> call test4(? ,? ,? ,?) </insert>Copy the code

test

@Test void test() { HashMap<String, Object> map = new HashMap<>(); map.put("id",6); map.put("name","demo"); map.put("money",120); map.put("num",-1); userMapper.test4(map); System.out.println(map.get("num")); }}Copy the code

Console output

🏁 the above is a simple introduction to the use of stored procedures in MyBatis, if there is any error, please leave a message to correct, if you think this article is helpful to you then click a like 👍 😋😻 👍

Stored procedures are used in MyBatis