这个例子使用 Oracle 的 SYS_REFCURSOR 输出参数,不是 MySQL 存储过程,也不是 MyBatis 的流式 Cursor
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中创建带游标的存储过程

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