33、Mybatis进阶:MyBatis调用带游标的存储过程

补齐 Oracle SYS_REFCURSOR 过程、MyBatis 映射和调用入口,明确本轮只完成编译与 Mapper 参数检查。

简明结论

Oracle REF CURSOR、MyBatis 流式 Cursor 与 MySQL 存储过程不能混用验证结论;映射解析通过不代表过程执行成功。

这个例子使用 Oracle 的 SYS_REFCURSOR 输出参数,不是 MySQL 存储过程,也不是 MyBatis 的流式 Cursor 查询。需要分别确认数据库过程、JDBC 驱动对游标的支持,以及 MyBatis 的 OUT 参数映射。

Oracle 过程与确定的分页条件

以下为单独测试 schema 准备的完整过程。边界从 1 开始且包含 end,按唯一 id 排序;不要把这里的 start/end 当作从零开始的 OFFSET/页大小。SQL 文件未在本轮 Oracle 数据库中执行。

CREATE TABLE phase6_employee (id NUMBER PRIMARY KEY, name VARCHAR2(30));
INSERT INTO phase6_employee VALUES (1,'Ada');
INSERT INTO phase6_employee VALUES (2,'Lin');
COMMIT;
CREATE OR REPLACE PROCEDURE phase6_page (
  p_start IN NUMBER, p_end IN NUMBER, p_total OUT NUMBER, p_rows OUT SYS_REFCURSOR
) AS
BEGIN
  IF p_start < 1 OR p_end < p_start OR p_start IS NULL OR p_end IS NULL THEN
    RAISE_APPLICATION_ERROR(-20001, 'invalid inclusive page bounds');
  END IF;
  SELECT COUNT(*) INTO p_total FROM phase6_employee;
  OPEN p_rows FOR
    SELECT id,name FROM (
      SELECT id,name,ROW_NUMBER() OVER (ORDER BY id) AS rn FROM phase6_employee
    ) WHERE rn BETWEEN p_start AND p_end ORDER BY rn;
END;
/

OUT 游标的映射

将文件放入 src/test/resources/OracleMapper.xml。CALLABLE 指定调用语句;rows 为 OUT、CURSOR、ResultSet,并引用 PageEmp。旧片段把 LAST_NAME 和 EMAIL 都映射到 email 的写法不能代表正确字段关系,这里用独立的 ID/NAME 测试表明确映射。

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="oraclePage">
  <resultMap id="PageEmp" type="map"><id column="ID" property="id"/><result column="NAME" property="name"/></resultMap>
  <select id="page" parameterType="map" statementType="CALLABLE">
    {call phase6_page(
      #{start,mode=IN,jdbcType=INTEGER},
      #{end,mode=IN,jdbcType=INTEGER},
      #{total,mode=OUT,jdbcType=INTEGER},
      #{rows,mode=OUT,jdbcType=CURSOR,javaType=java.sql.ResultSet,resultMap=PageEmp}
    )}
  </select>
</mapper>

已执行的检查是什么

复用共享 POM,将测试放入 src/test/java/site。该测试实际解析 Mapper 并核对参数元数据,不连接 Oracle,也不执行 PL/SQL。

package site;

import java.util.Map;
import org.apache.ibatis.builder.xml.XMLMapperBuilder;
import org.apache.ibatis.mapping.ParameterMode;
import org.apache.ibatis.mapping.StatementType;
import org.apache.ibatis.session.Configuration;
import org.apache.ibatis.type.JdbcType;
import org.junit.jupiter.api.Test;
import static org.junit.jupiter.api.Assertions.*;

class OracleMappingTest {
  @Test void checksCallableMappingWithoutOracleExecution() throws Exception {
    var config = new Configuration();
    try (var xml = getClass().getResourceAsStream("/OracleMapper.xml")) {
      new XMLMapperBuilder(xml,config,"OracleMapper.xml",config.getSqlFragments()).parse();
    }
    var statement = config.getMappedStatement("oraclePage.page");
    assertEquals(StatementType.CALLABLE,statement.getStatementType());
    var parameters = statement.getBoundSql(Map.of("start",1,"end",2)).getParameterMappings();
    assertEquals(4,parameters.size());
    assertEquals(ParameterMode.OUT,parameters.get(3).getMode());
    assertEquals(JdbcType.CURSOR,parameters.get(3).getJdbcType());
    assertEquals(java.sql.ResultSet.class,parameters.get(3).getJavaType());
    assertEquals("PageEmp",parameters.get(3).getResultMapId());
    assertTrue(config.hasResultMap(parameters.get(3).getResultMapId()));
    System.out.println("callable-mapping=passed; out-cursor=checked; oracle-executed=false");
  }
}
mvn -Dtest=OracleMappingTest test

本次输出 callable-mapping=passed; out-cursor=checked; oracle-executed=false。这证明 XML 能被固定版本 MyBatis 解析,不能据此宣称过程已编译、驱动已绑定或分页结果已正确读取。

留给 Oracle 测试环境的调用入口

下面入口也已编译,但未运行。需要单独配置与数据库版本兼容的 Oracle JDBC 驱动,并在自己的测试 schema 完成前面的 SQL;通过 PHASE6_ORACLE_URL、PHASE6_ORACLE_USER、PHASE6_ORACLE_PASSWORD 提供连接参数,不在源码中写凭据。

package site;

import java.util.HashMap;
import java.util.List;
import org.apache.ibatis.builder.xml.XMLMapperBuilder;
import org.apache.ibatis.datasource.unpooled.UnpooledDataSource;
import org.apache.ibatis.mapping.Environment;
import org.apache.ibatis.session.Configuration;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.apache.ibatis.transaction.jdbc.JdbcTransactionFactory;

// Compiled in this project; Oracle connection and procedure execution require a separate test schema and JDBC driver.
public class OraclePageExample {
  static String required(String name) {
    String value=System.getenv(name);
    if(value==null || value.isBlank()) throw new IllegalArgumentException("Missing environment variable: "+name);
    return value;
  }
  public static void main(String[] args) throws Exception {
    var source=new UnpooledDataSource("oracle.jdbc.OracleDriver",required("PHASE6_ORACLE_URL"),
        required("PHASE6_ORACLE_USER"),required("PHASE6_ORACLE_PASSWORD"));
    var config=new Configuration(new Environment("oracle-test",new JdbcTransactionFactory(),source));
    try(var xml=OraclePageExample.class.getResourceAsStream("/OracleMapper.xml")) {
      new XMLMapperBuilder(xml,config,"OracleMapper.xml",config.getSqlFragments()).parse();
    }
    var parameters=new HashMap<String,Object>();parameters.put("start",1);parameters.put("end",2);
    try(var session=new SqlSessionFactoryBuilder().build(config).openSession()) {
      session.selectOne("oraclePage.page",parameters);
      var rows=(List<?>)parameters.get("rows");
      System.out.println("total="+parameters.get("total")+"; returned="+(rows==null?0:rows.size()));
    }
  }
}

在该环境中应核对总数、返回行数、边界越界与空结果。COUNT 与游标 SELECT 是两个查询,并发修改下是否共享一致视图还取决于事务条件;本轮没有隔离级别或并发测试,也没有提供模拟的 Oracle 成功输出。

下一步阅读

原资料片段(未重建原环境)

下方保留原资料的上下文、示例与截图;本轮验证只对应上方新增文件,未将旧运行结果记为本轮结果。

本文内容来自尚硅谷http://www.gulixueyuan.com/course/43/task/1235/show的任务86与任务85

1创建JUnit

	/**
	 * oracle分页:
	 * 		借助rownum:行号;子查询;
	 * 存储过程包装分页逻辑
	 * @throws IOException 
	 */
	@Test
	public void testProcedure() throws IOException{
		SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
		SqlSession openSession = sqlSessionFactory.openSession();
		try{
			EmployeeMapper mapper = openSession.getMapper(EmployeeMapper.class);
			OraclePage page = new OraclePage();
			page.setStart(1);
			page.setEnd(5);
			mapper.getPageByProcedure(page);
			
			System.out.println("总记录数:"+page.getCount());
			System.out.println("查出的数据:"+page.getEmps().size());
			System.out.println("查出的数据:"+page.getEmps());
		}finally{
			openSession.close();
		}
		
	}
	

2dao

package com.atguigu.mybatis.dao;

import java.util.List;

import com.atguigu.mybatis.bean.Employee;
import com.atguigu.mybatis.bean.OraclePage;

public interface EmployeeMapper {
	
	public Employee getEmpById(Integer id);
	
	public List<Employee> getEmps();
	
	public Long addEmp(Employee employee);
	
	public void getPageByProcedure(OraclePage page);
}

3bean

package com.atguigu.mybatis.bean;

import java.util.List;

/**
 * 封装分页查询数据
 * @author lfy
 *
 */
public class OraclePage {
	
	private int start;
	private int end;
	private int count;
	private List<Employee> emps;
	
	public int getStart() {
		return start;
	}
	public void setStart(int start) {
		this.start = start;
	}
	public int getEnd() {
		return end;
	}
	public void setEnd(int end) {
		this.end = end;
	}
	public int getCount() {
		return count;
	}
	public void setCount(int count) {
		this.count = count;
	}
	public List<Employee> getEmps() {
		return emps;
	}
	public void setEmps(List<Employee> emps) {
		this.emps = emps;
	}
	
	

}

4xml

#{emps,mode=OUT,jdbcType=CURSOR,javaType=ResultSet,resultMap=PageEmp}中mode代表在存储过程中的参数类型为输出参数,jdbcType:代表着是游标类型,javaType:设置返回的结果集类型为ResultSet,resultMap:结果集的映射关系处理
	<!-- public void getPageByProcedure();
	1、使用select标签定义调用存储过程
	2、statementType="CALLABLE":表示要调用存储过程
	3、{call procedure_name(params)}
	-->
	<select id="getPageByProcedure" statementType="CALLABLE" databaseId="oracle">
		{call hello_test(
			#{start,mode=IN,jdbcType=INTEGER},
			#{end,mode=IN,jdbcType=INTEGER},
			#{count,mode=OUT,jdbcType=INTEGER},
			#{emps,mode=OUT,jdbcType=CURSOR,javaType=ResultSet,resultMap=PageEmp}
		)}
	</select>
	<resultMap type="com.atguigu.mybatis.bean.Employee" id="PageEmp">
		<id column="EMPLOYEE_ID" property="id"/>
		<result column="LAST_NAME" property="lastName"/>
		<result column="EMAIL" property="email"/>
	</resultMap>

5oracle中创建带游标的存储过程

 

版权声明:本文不是「本站」原创文章,版权归原作者所有 | 原文地址: