存储过程

 先在MYSQL中执行存储过程，如下：

DELIMITER $$
CREATE PROCEDURE proc_1()
BEGIN
    INSERT INTO pubinfo VALUES(NULL,'舒舒出版社',1);
END$$
DELIMITER;
##############################################
DELIMITER $$
CREATE PROCEDURE proc_2(pname VARCHAR(20))
BEGIN
    INSERT INTO pubinfo VALUES(NULL,pname,1);
END$$
DELIMITER;
##############################################
DELIMITER $$
CREATE PROCEDURE proc_3(pname VARCHAR(20),OUT allcount INT)
BEGIN
    INSERT INTO pubinfo VALUES(NULL,pname,1);
    SELECT COUNT(pubid) INTO allcount FROM pubinfo;
END$$
DELIMITER;

1. 导包（mybatis，MySQL），加上resources那段话

<dependency>
  <groupId>junit</groupId>
  <artifactId>junit</artifactId>
  <version>4.11</version>
  <scope>test</scope>
</dependency>
<dependency>
  <groupId>mysql</groupId>
  <artifactId>mysql-connector-java</artifactId>
  <version>8.0.17</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.mybatis/mybatis -->
<dependency>
  <groupId>org.mybatis</groupId>
  <artifactId>mybatis</artifactId>
  <version>3.5.2</version>
</dependency>

<resources>
  <resource>
    <directory>src/main/java</directory>
    <includes>
      <include>**/*.xml</include>
    </includes>
  </resource>
</resources>

 

2.创建resources文件夹，新建config.xml文件（之前的）

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" 
   "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
   <settings>
      <setting name="logImpl" value="STDOUT_LOGGING" />
   </settings>
   <environments default="development">
      <environment id="development">
         <transactionManager type="JDBC" />
         <!-- 配置数据库连接信息 -->
         <dataSource type="POOLED">
            <property name="driver" value="com.mysql.cj.jdbc.Driver" />
            <property name="url" value="jdbc:mysql://localhost:3306/sys?characterEncoding=utf8&amp;useSSL=false&amp;serverTimezone=UTC&amp;rewriteBatchedStatements=true" />
            <property name="username" value="root" />
            <property name="password" value="a12345 />
         </dataSource>
      </environment>
   </environments>
   <mappers>
      <mapper resource="mapper/UserMapper.xml" />
   </mappers>
</configuration>

3.写mapper包

ProcMapper

package mapper;

import java.util.Map;

public interface ProcMapper {
    void proc1();//无参数过程调用
    void proc2(String pname); //含一个参数调用
    void proc3(Map<String,Object> map);//含两个参数调用(mybatis不能传两个参数）

 

 ProMapper.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="mapper.ProcMapper"><!--记得在config.xml中添加xml文件-->
    <insert id="proc1" statementType="CALLABLE">  <!--statementType="CALLABLE"说明这是个存储过程-->
        {call proc_1} <!--调用存储过程，proc_1为存储过程的名字-->
    </insert>
    <insert id="proc2" statementType="CALLABLE">
        {call proc_2(
        #{pname,jdbcType=VARCHAR,mode=IN}  <!--输入-->
        )}
    </insert>

    <parameterMap id="map1" type="java.util.Map">
        <!--property-->
        <parameter property="pname" jdbcType="VARCHAR" mode="IN" /> <!--输入(IN)参数-->
        <parameter property="allcount" jdbcType="INTEGER" mode="OUT" /> <!--输出(OUT)参数-->
    </parameterMap>
    <insert id="proc3" statementType="CALLABLE" parameterMap="map1">
        {call proc_3(?,?)}
    </insert>
</mapper>

4.Test类

 

package test;

import mapper.ProcMapper;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import java.io.InputStream;
import java.util.HashMap;
import java.util.Map;

public class Test {
    public static void a(){
        InputStream in = Test.class.getClassLoader().getResourceAsStream("config.xml");
        SqlSessionFactory ssf = new SqlSessionFactoryBuilder().build(in);
        SqlSession session = ssf.openSession();
        ProcMapper pm = session.getMapper(ProcMapper.class);
        pm.proc1();
        session.commit();
    }

    public static void b(){
        InputStream in = Test.class.getClassLoader().getResourceAsStream("config.xml");
        SqlSessionFactory ssf = new SqlSessionFactoryBuilder().build(in);
        SqlSession session = ssf.openSession();
        ProcMapper pm = session.getMapper(ProcMapper.class);
        String pname = "bbbb";
        pm.proc2(pname);
        session.commit();
    }
    public static void c(){
        InputStream in = Test.class.getClassLoader().getResourceAsStream("config.xml");
        SqlSessionFactory ssf = new SqlSessionFactoryBuilder().build(in);
        SqlSession session = ssf.openSession();
        ProcMapper pm = session.getMapper(ProcMapper.class);
        Map<String,Object> map = new HashMap<>();
        map.put("pname","ddd");
        map.put("allcount",null);
        pm.proc3(map);
        System.out.println(map);
        session.commit();
    }
    public static void main(String[] args) {
        c();
    }
}

运行结果：

 

数据库表结果：