mybatis(七): MyBatis 调用存储过程

MyBatis 调用存储过程

1. 创建表和存储过程

   CREATE TABLE `country` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `countryname` varchar(255) DEFAULT NULL,
      `countrycode` varchar(255) DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
   

   --新增country存储过程
   DELIMITER $$
   CREATE
       PROCEDURE `mybatis`.`insert_country`(OUT id INT,IN countryname VARCHAR(255),IN countrycode VARCHAR(255))
       
       BEGIN
   	INSERT INTO country (countryname,countrycode) VALUES(countryname,countrycode);
   	SET id = LAST_INSERT_ID();
       END$$
   
   DELIMITER ;
   --删除country存储过程
   DELIMITER $$
   CREATE
       /*[DEFINER = { user | CURRENT_USER }]*/
       PROCEDURE `mybatis`.`delete_country`(IN sid INT)
       BEGIN
   	DELETE FROM country WHERE id=sid;
       END$$
   
   DELIMITER ;
   --修改country存储过程
   DELIMITER $$
   CREATE
       PROCEDURE `mybatis`.`update_country`(IN sid INT,IN countryname VARCHAR(255),IN countrycode VARCHAR(255))
       BEGIN
   	UPDATE country SET countryname=countryname,countrycode=countrycode WHERE id=sid;
       END$$
   DELIMITER ;
   --查询country存储过程
   DELIMITER $$
   CREATE 
   	PROCEDURE `mybatis`.`select_country`(IN sid INT)
   BEGIN
   	SELECT id,countryname,countrycode FROM country WHERE id = sid;
       END$$
   DELIMITER ;
   

2. CountryMapper.xml

   <?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.zm.mapper.CountryMapper">
       <!--设置 statementType="CALLABLE" -->
       <!--mode=IN|OUT-->
       <select id="getCountryById" resultType="com.zm.entity.Country" parameterType="Long" statementType="CALLABLE" >
           {call select_country(#{id,mode=IN})}
       </select>
       <!--id中  jdbcType=INTEGER 不能省略。否者报错org.apache.ibatis.executor.ExecutorException: The JDBC Type must be specified for output parameter.  Parameter: id-->
       <insert id="insertCountry" parameterType="com.zm.entity.Country" statementType="CALLABLE"
               useGeneratedKeys="true" keyColumn="id" keyProperty="id"  >
           {call insert_country(#{id,mode=OUT,jdbcType=INTEGER},#{countryname,mode=IN},#{countrycode,mode=IN})}
       </insert>
   
       <update id="updateCountryById" parameterType="com.zm.entity.Country" statementType="CALLABLE">
           {call update_country(#{id,mode=IN},#{countryname,mode=IN},#{countrycode,mode=IN})}
       </update>
   
       <delete id="deleteCountryById" parameterType="Long" statementType="CALLABLE">
           {call delete_country(#{id,mode=IN})}
       </delete>
   
   </mapper>
   

3. 测试类

    //查询
       @Test
       public void test4() {
           SqlSession session = sqlSessionFactory.openSession();
           CountryMapper mapper = session.getMapper(CountryMapper.class);
           Country country = mapper.getCountryById(1L);
           System.out.println(country);
       }
       //插入
       @Test
       public void test5() {
           SqlSession session = sqlSessionFactory.openSession();
           CountryMapper mapper = session.getMapper(CountryMapper.class);
           Country country = new Country();
           country.setCountryname("韩国");
           country.setCountrycode("KR");
   
           int i = mapper.insertCountry(country);
           System.out.println("insert key id is:"+country.getId());
   
           session.commit();
   
       }
       //修改
       @Test
       public void test6() {
           SqlSession session = sqlSessionFactory.openSession();
           CountryMapper mapper = session.getMapper(CountryMapper.class);
           Country country = new Country();
           country.setId(9L);
           country.setCountryname("韩国1");
           country.setCountrycode("KR1");
   
           int i = mapper.updateCountryById(country);
           System.out.println("影响条数："+i);
           session.commit();
       }
       //删除
       @Test
       public void test7() {
           SqlSession session = sqlSessionFactory.openSession();
           CountryMapper mapper = session.getMapper(CountryMapper.class);
           Country country = new Country();
   
           int i = mapper.deleteCountryById(6L);
           System.out.println("影响条数："+i);
           session.commit();
       }