Mybatis 43_调用存储过程

调用存储过程

调用存储过程时只要在定义SQL语句的地方写成
“{call 存储过程(参数)}”
调用存储过程时，Mapper定义的SQL语句只是简单的调用存储过程，
通常无法保证存储过程返回的列名（列别名）与实体对象的属性同名，因此通常需要使用<resultMap…/>来定义结果映射。

项目0507调用存储过程

配置文件

<?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的唯一标识 -->	
<mapper namespace="org.itcheng.app.dao.NewsMapper">

	<select id="findNewsByProc" resultMap="newsMap">
		<!-- mode指定参数的传参数模式，支持IN\OUT\INOUT -->
		{call p_get_news_by_id(#{a, mode=IN})}
	</select>
	
	<resultMap type="news" id="newsMap">
		<id column="news_id" property="id"/>
		<result column="news_title" property="title"/>
		<result column="news_content" property="content"/>
	</resultMap>
	
</mapper>

主类

package lee;

import java.io.IOException;
import java.io.InputStream;
import java.util.List;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.itcheng.app.dao.NewsMapper;

public class NewsManager
{
	// SqlSessionFactory应该是应用级别
	private static SqlSessionFactory sqlSessionFactory;
	public static void main(String[] args) throws IOException
	{
		String resource = "mybatis-config.xml";
		InputStream inputStream = Resources.getResourceAsStream(resource);
		// 1. 创建SqlSessionFactory
		sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
		// 2. 打开SqlSession
		SqlSession sqlSession = sqlSessionFactory.openSession();
		
		// 查询消息
		selectNews(sqlSession);		
		/* 结果
		 * class com.sun.proxy.$Proxy17
		 * DEBUG [main] org.itcheng.app.dao.NewsMapper.findNewsByProc ==>  Preparing: {call p_get_news_by_id(?)} 
		 * DEBUG [main] org.itcheng.app.dao.NewsMapper.findNewsByProc ==> Parameters: 1(Integer)
		 * DEBUG [main] org.itcheng.app.dao.NewsMapper.findNewsByProc <==      Total: 5
		 * DEBUG [main] org.itcheng.app.dao.NewsMapper.findNewsByProc <==    Updates: 0
		 * [org.itcheng.app.domain.News@6f603e89, org.itcheng.app.domain.News@2756c0a7, org.itcheng.app.domain.News@350ec41e, org.itcheng.app.domain.News@69637b10, org.itcheng.app.domain.News@71984c3]
		 * */
	}
	
	public static void selectNews(SqlSession sqlSession)
	{
		// 此处的NewsMapper只是一个接口
		// MyBatis会使用JDK的动态代理为Mapper接口生成实现类
		NewsMapper newsMapper = sqlSession.getMapper(NewsMapper.class);
		System.out.println(newsMapper.getClass());
		List<?> list = newsMapper.findNewsByProc(1);
		
		System.out.println(list);
		
		// 4. 提交事务
		sqlSession.commit();
		// 5. 关闭资源
		sqlSession.close();	
	}
}

存储过程

drop procedure if exists p_get_news_by_id;
delimiter $$
create procedure p_get_news_by_id(v_id integer)
begin
	select * from news_inf where news_id > v_id;
end
$$
delimiter ;

项目0508调用存储过程_注解

主类

package lee;

import java.io.IOException;
import java.io.InputStream;
import java.util.List;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.itcheng.app.dao.NewsMapper;

public class NewsManager
{
	// SqlSessionFactory应该是应用级别
	private static SqlSessionFactory sqlSessionFactory;
	public static void main(String[] args) throws IOException
	{
		String resource = "mybatis-config.xml";
		InputStream inputStream = Resources.getResourceAsStream(resource);
		// 1. 创建SqlSessionFactory
		sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
		// 2. 打开SqlSession
		SqlSession sqlSession = sqlSessionFactory.openSession();
		
		// 查询消息
		selectNews(sqlSession);	
		/* 结果
		 * class com.sun.proxy.$Proxy23
		 * DEBUG [main] org.itcheng.app.dao.NewsMapper.findNewsByProc ==>  Preparing: {call p_get_news_by_id(?)} 
		 * DEBUG [main] org.itcheng.app.dao.NewsMapper.findNewsByProc ==> Parameters: 1(Integer)
		 * DEBUG [main] org.itcheng.app.dao.NewsMapper.findNewsByProc <==      Total: 5
		 * DEBUG [main] org.itcheng.app.dao.NewsMapper.findNewsByProc <==    Updates: 0
		 * [org.itcheng.app.domain.News@5b11a194, org.itcheng.app.domain.News@37bd68c3, org.itcheng.app.domain.News@60f7cc1d, org.itcheng.app.domain.News@11eadcba, org.itcheng.app.domain.News@4721d212]
		 * */
	}
	
	public static void selectNews(SqlSession sqlSession)
	{
		// 此处的NewsMapper只是一个接口
		// MyBatis会使用JDK的动态代理为Mapper接口生成实现类
		NewsMapper newsMapper = sqlSession.getMapper(NewsMapper.class);
		System.out.println(newsMapper.getClass());
		List<?> list = newsMapper.findNewsByProc(1);
		
		System.out.println(list);
		
		// 4. 提交事务
		sqlSession.commit();
		// 5. 关闭资源
		sqlSession.close();	
	}
}

接口文件

package org.itcheng.app.dao;

import java.util.List;

import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;
import org.itcheng.app.domain.News;

// Mapper组件相当于DAO组件
public interface NewsMapper
{
	@Select("{call p_get_news_by_id(#{a, mode=IN})}")
	@Results({
		@Result(column = "news_id", property="id", id = true),
		@Result(column = "news_title", property = "title"),
		@Result(column = "news_content", property = "content")
	})
	List<News> findNewsByProc(Integer id);
}

调用传出参数的存储过程

对于传出参数、传入/传出参数，SQL语句中#{}必须使用mode来指定参数的传参模式

---

                 传入参数      |           传出参数         |       传入/传出参数
 参数是否有值     必须有值      |          可以没有值        |          必须有值
 参数为直接量       可以        |          不行（必须是变量）|      不行（必须是变量）

▲ 注意点：
1. 必须为参数在#{}用mode指定OUT模式。
2. 传出参数要求必须指定jdbcType
3. 定义调用存储过程的SQL语句的<insert…/>等元素必须指定statementType=“CALLABLE”
告诉MyBatis使用CallableStatement去执行这条SQL语句
如果使用注解，添加 @Options(statementType = StatementType.CALLABLE) 即可

项目0509传出参数

主类

package lee;

import java.io.IOException;
import java.io.InputStream;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.itcheng.app.dao.NewsMapper;
import org.itcheng.app.domain.News;

public class NewsManager
{
	// SqlSessionFactory应该是应用级别
	private static SqlSessionFactory sqlSessionFactory;
	public static void main(String[] args) throws IOException
	{
		String resource = "mybatis-config.xml";
		InputStream inputStream = Resources.getResourceAsStream(resource);
		// 1. 创建SqlSessionFactory
		sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
		// 2. 打开SqlSession
		SqlSession sqlSession = sqlSessionFactory.openSession();
		
		// 查询消息
		 saveNews(sqlSession);	
		 /* 结果
		  * class com.sun.proxy.$Proxy17
		  * DEBUG [main] org.itcheng.app.dao.NewsMapper.saveNews ==>  Preparing: {call p_insert_news(?, ?, ?)} 
		  * DEBUG [main] org.itcheng.app.dao.NewsMapper.saveNews ==> Parameters: 测试标题(String), 简单的内容20231128(String)
		  * DEBUG [main] org.itcheng.app.dao.NewsMapper.saveNews <==    Updates: 1
		  * 12
		  * */
	}
	
	public static void saveNews(SqlSession sqlSession)
	{
		// 此处的NewsMapper只是一个接口
		// MyBatis会使用JDK的动态代理为Mapper接口生成实现类
		NewsMapper newsMapper = sqlSession.getMapper(NewsMapper.class);
		System.out.println(newsMapper.getClass());
		
		News news = new News(null, "测试标题", "简单的内容20231128");
		newsMapper.saveNews(news);  // 调用了带传出参数的存储过程
		// 传出参数传给了news对象的id属性，因此id属性就保存了传出参数的值
		System.out.println(news.getId());
	
		// 4. 提交事务
		sqlSession.commit();
		// 5. 关闭资源
		sqlSession.close();	
	}
}

映射文件

<?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的唯一标识 -->	
<mapper namespace="org.itcheng.app.dao.NewsMapper">
	<!-- 属性statementType 
			可选 STATEMENT，PREPARED 或 CALLABLE三个值
			这会让 MyBatis 分别使用 Statement，PreparedStatement 或 CallableStatement，默认值：PREPARED。
	        Statement 对应的是'STATEMENT'会引起sql注入
	        PreparedStatement对应的是'PREPARED'无法调用存储过程
	        CallableStatement对应的是'CALLABLE'可以调用存储过程
	 -->
	<insert id="saveNews" statementType="CALLABLE">
		<!-- 调用存储过程，SQL语句放在{}里定义
		第一个参数是传出参数，因此第一个参数必须指定mode，并且传出参数需要指定类型
		  -->
		{call p_insert_news(#{id, mode=OUT, jdbcType=INTEGER}, #{title}, #{content})}
	</insert>
	
</mapper>

存储过程

drop procedure if exists p_insert_news;
delimiter $$
create procedure p_insert_news
(out v_id integer, v_title varchar(255), v_content varchar(255))
begin
	insert into news_inf
	values (null, v_title, v_content);
	-- last_insert_id是MySQL的内置函数，用于获取自增长主键的值
	set v_id = last_insert_id();
end
$$
delimiter ;

项目0510传出参数_注解

主类

package lee;

import java.io.IOException;
import java.io.InputStream;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.itcheng.app.dao.NewsMapper;
import org.itcheng.app.domain.News;

public class NewsManager
{
	// SqlSessionFactory应该是应用级别
	private static SqlSessionFactory sqlSessionFactory;
	public static void main(String[] args) throws IOException
	{
		String resource = "mybatis-config.xml";
		InputStream inputStream = Resources.getResourceAsStream(resource);
		// 1. 创建SqlSessionFactory
		sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
		// 2. 打开SqlSession
		SqlSession sqlSession = sqlSessionFactory.openSession();
		
		// 查询消息
		saveNews(sqlSession);		
		/* 结果
		 * class com.sun.proxy.$Proxy19
		 * DEBUG [main] org.itcheng.app.dao.NewsMapper.saveNews ==>  Preparing: {call p_insert_news(?, ?, ?)} 
		 * DEBUG [main] org.itcheng.app.dao.NewsMapper.saveNews ==> Parameters: 测试标题20231128(String), 简单的内容(String)
		 * DEBUG [main] org.itcheng.app.dao.NewsMapper.saveNews <==    Updates: 1
		 * 13
		 * */
	}
	
	public static void saveNews(SqlSession sqlSession)
	{
		// 此处的NewsMapper只是一个接口
		// MyBatis会使用JDK的动态代理为Mapper接口生成实现类
		NewsMapper newsMapper = sqlSession.getMapper(NewsMapper.class);
		System.out.println(newsMapper.getClass());
		
		News news = new News(null, "测试标题20231128", "简单的内容");
		newsMapper.saveNews(news);  // 调用了带传出参数的存储过程
		// 传出参数传给了news对象的id属性，因此id属性就保存了传出参数的值
		System.out.println(news.getId());
	
		// 4. 提交事务
		sqlSession.commit();
		// 5. 关闭资源
		sqlSession.close();	
	}
}

接口类

package org.itcheng.app.dao;

import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Options;
import org.apache.ibatis.mapping.StatementType;
import org.itcheng.app.domain.News;

// Mapper组件相当于DAO组件
public interface NewsMapper
{
	@Insert("{call p_insert_news(#{id, mode=OUT, jdbcType=INTEGER}, #{title}, #{content})}")
	// 告诉程序使用CallableStatement来执行SQL语句
	@Options(statementType = StatementType.CALLABLE)
	int saveNews(News news);
}