先在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;
- 导包(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&useSSL=false&serverTimezone=UTC&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();
}
}
运行结果:
数据库表结果: