如何在H2中定义Oracle Package Procedure进行测试 [英] How to define Oracle Package Procedure in H2 for Testing

问题描述

我正在测试一个将数据读/写到Oracle DB的Spring Boot应用程序.该Oracle DB具有Oracle软件包以及这些软件包中的过程.在某个时候，spring boot应用程序通过实体存储库调用此过程，如下所示:

I am testing an spring boot application that reads/writes data to an Oracle DB. This Oracle DB has Oracle packages and in those packages procedures. At some point, the spring boot application calls this procedure via a Entity Repository as follows

@Repository
public interface StudentRepository extends JpaRepository<Student, String> {

@Modifying
@Query(value = "begin sch1.STUDENT_PACKAGE.Set_Grades_To_A('A'); end;", nativeQuery = true)
public void setStudentGradeToA();
}

因此，它使用本机查询来调用sch1模式的STUDENT_PACKAGE包中的过程Set_GradesToA.

So, it uses a native query to make the call to to a procedure Set_GradesToA in the STUDENT_PACKAGE package of the sch1 schema.

我目前正在测试Spring Boot应用程序的功能，并且否它与Oracle数据库之间的集成.因此，我决定暂时使用内存数据库(H2)(带有Oracle兼容性选项)替换Oracle DB. 但是我该如何伪造这些Java打包过程?

I am currently testing the functionality of the Spring Boot application and NOT the integration between it and the Oracle database. Therefore, I have decided to use an in-memory database (H2) (with the Oracle compatibility option) to replace the Oracle DB for now. BUT how can I fake out these java package procedures?

我尝试如下在schema.sql(或data.sql)中创建别名:

I have tried creating an alias in my schema.sql (or data.sql) as follows:

CREATE SCHEMA if not exists sch1;
CREATE ALIAS sch1.STUDENT_PACKAGE AS $$ void Set_Grades_To_A(String s) { new String(s); } $$;

我真的不在乎Set_Grades_To_A过程中的内容是什么，我在乎的是如何定义它.

I really don't care what is inside the Set_Grades_To_A procedure what I care about is how to define it.

如上所述创建别名时，仍然出现语法错误.

When I create the alias as above, I'm still getting a Syntax Error.

Caused by: org.h2.jdbc.JdbcSQLException: Syntax error in SQL statement "BEGIN SCH1[*].STUDENT_PACKAGE.Set_Grades_To_A('A'); END; "; SQL statement:
begin sch1.STUDENT_PACKAGE.Set_Grades_To_A('A'); end; [42000-197]
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:357)
    at org.h2.message.DbException.get(DbException.java:179)
    at org.h2.message.DbException.get(DbException.java:155)
    at org.h2.message.DbException.getSyntaxError(DbException.java:203)

我想我有两个问题:

1. 如何在模式sch1中伪造Oracle程序包中的存储过程?

1. How can I fake out a stored procedure inside an Oracle package in the schema sch1?

为什么会出现上述语法错误?

Why am I getting the Syntax Error above?

推荐答案

这就是我所做的.

问题2:要回答此问题，我必须如下更改本机查询

Question #2: To answer this question I had to change the native query as follows

@Repository
public interface StudentRepository extends JpaRepository<Student, String> {

@Modifying
@Query(value = "call sch1.STUDENT_PACKAGE.Set_Grades_To_A('A')", nativeQuery = true)
public void setStudentGradeToA();
}

问题#1:涉及到三件事.现在，如上所述，我已经更改了本机查询，但又遇到了另一个错误:

Question #1: Three things are involved to answer this. Now that I had changed the native query as above I got a different error:

Caused by: org.h2.jdbc.JdbcSQLException: Database "sch1" not found; SQL statement:
call sch1.STUDENT_PACKAGE.Set_Grades_To_A('A') [90013-197]
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:357)
    at org.h2.message.DbException.get(DbException.java:179)
    at org.h2.message.DbException.get(DbException.java:155)

它正在寻找一个名为sch1的数据库.似乎用于在H2中调用存储过程的模式是database.schema.procedure_name.因为我不在乎该过程实际上是什么，所以我可以通过创建一个名为sch1的数据库，一个名为STUDENT_PACKAGE的模式和过程名称为Set_Grades_To_A

It was looking for a database called sch1. It seems like the pattern used to call a stored procedure in H2 is database.schema.procedure_name. Since I don't care what that procedure actually does I was able to fake this out by creating a database called sch1 a schema called STUDENT_PACKAGE and the procedure name Set_Grades_To_A

要创建内存数据库，必须在application.properties文件中设置以下属性spring.datasource.url.

To create the in memory database, you have to set the following property spring.datasource.url in the application.properties file.

1. 按照spring.datasource.url=jdbc:h2:mem:sch1;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE;MODE=Oracle;INIT=CREATE SCHEMA IF NOT EXISTS first_schema的方式创建sch1数据库.请注意，数据库名称为sch1

1. Create the sch1 database as follows spring.datasource.url=jdbc:h2:mem:sch1;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE;MODE=Oracle;INIT=CREATE SCHEMA IF NOT EXISTS first_schema. Notice the database name is sch1

通过将此\\;CREATE SCHEMA IF NOT EXISTS STUDENT_PACKAGE添加到spring.datasource.url的末尾来创建STUDENT_PACKAGE模式.这将添加另一个名为STUDENT_PACKAGE的架构.该属性应如下所示:spring.datasource.url=jdbc:h2:mem:sch1;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE;MODE=Oracle;INIT=CREATE SCHEMA IF NOT EXISTS first_schema\\;CREATE SCHEMA IF NOT EXISTS STUDENT_PACKAGE

Create the STUDENT_PACKAGE schema by adding this \\;CREATE SCHEMA IF NOT EXISTS STUDENT_PACKAGE to the end of the spring.datasource.url. This adds a second schema called STUDENT_PACKAGE. The property should look like this spring.datasource.url=jdbc:h2:mem:sch1;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE;MODE=Oracle;INIT=CREATE SCHEMA IF NOT EXISTS first_schema\\;CREATE SCHEMA IF NOT EXISTS STUDENT_PACKAGE

通过将其添加到schema.sql CREATE ALIAS STUDENT_PACKAGE.Set_Grades_To_A AS $$ void setGradesToA(String s) { new StringBuilder(s).reverse().toString(); } $$;

Create a the Set_Grades_To_A stored procedure by adding this to your schema.sql CREATE ALIAS STUDENT_PACKAGE.Set_Grades_To_A AS $$ void setGradesToA(String s) { new StringBuilder(s).reverse().toString(); } $$;

这篇关于如何在H2中定义Oracle Package Procedure进行测试的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！