创建函数-UncategorizedScriptException-ArrayIndexOutOfBoundsException [英] Create Function - UncategorizedScriptException - ArrayIndexOutOfBoundsException

问题描述

我是Spring Boot项目的新成员，该项目使用2个不同的属性文件来进行与数据库设置有关的2种不同的配置：

I am working as a new member on a Spring Boot Project that utilizes 2 different properties files for 2 different configurations related to DB settings:

• 生产模式：postgres SQL DB


• 开发模式：内存DB中的h2

由于我试图最小化两个脚本之间的差异，因此我开始编写函数，以处理与日期/时间处理相关的差异。

Since I'm trying to minimize differences in the two scripts, I have started writing functions that will take case of differences related to date/time handling.

一个示例是增加时间，因为postgres使用间隔，而h2使用类似于oracle的 date_add 函数。

One example is adding hours since postgres uses intervals and h2 uses the oracle similar date_add function.

不幸的是，我在控制台中无法正常使用函数创建语句。

配置/属性

spring.profiles.active=pre-prod

spring.datasource.url=jdbc:postgresql://localhost:5432/db
spring.datasource.username=postgres
spring.datasource.password=root
spring.datasource.driver-class-name=org.postgresql.Driver

spring.datasource.data=classpath:db/migration/postgres/db_functions.sql,classpath:db/migration/postgres/data.sql
spring.jpa.hibernate.ddl-auto=create

db_functions.sql

--Adds a cast to date to the specific statement
--for h2 simply make a wrapper
CREATE OR REPLACE FUNCTION db_pgres_cast_varchar_to_date(d VARCHAR ) RETURNS date AS $$
        BEGIN
                RETURN d::date;
        END;
$$ LANGUAGE plpgsql;

--Common function for h2 and vct to add hours
--References: http://stackoverflow.com/questions/9376350/postgresql-how-to-concat-interval-value-2-days
CREATE OR REPLACE FUNCTION db_add_hours(d timestamp, hours int) RETURNS timestamp AS $$
        BEGIN
                RETURN d +  (hours || ' hours')::interval;
        END;
$$ LANGUAGE plpgsql;

例外

Exception

NFO] org.springframework.scheduling.concurrent.ThreadPoolTaskExecutor Shutting down ExecutorService 'createTaskExecutor'
Exception in thread "main" org.springframework.jdbc.datasource.init.UncategorizedScriptException: Failed to execute database script from resource [class path resource [db/migration/postgres/vct_functions.sql]]; nested exception is java.lang.ArrayIndexOutOfBoundsException
    at org.springframework.jdbc.datasource.init.ScriptUtils.executeSqlScript(ScriptUtils.java:509)

更新

我开始调试，看到不是我的split / parser代码创建2条语句，而是我的脚本正在被解释作为6条SQL命令：

Update

I started debugging and see that instead of the split/parser code creating 2 statements, my scripts are being interpreted as 6 SQL commands:

问题似乎出在 splitSqlScript 的实现上，定义：

The problem seems to be with the implementation of splitSqlScript, definition:

public static void splitSqlScript(EncodedResource resource, String script, String separator, String commentPrefix,
            String blockCommentStartDelimiter, String blockCommentEndDelimiter, List<String> statements)
            throws ScriptException

https://www.codatlas.com/github.com/spring-projects/spring-framework/HEAD/spring-jdbc/src/main/ java / org / springframework / jdbc / datasource / init / ScriptUtils.java？line = 166

由于Spring的Spring Utils似乎是问题，因此我尝试使用不同的语法来创建函数：

Since Spring Utils of Spring seems to be the problem I am trying a different syntax to create my functions:

--Adds a cast to date to the specific statement
--for h2 simply a wrapper
CREATE OR REPLACE FUNCTION vct_pgres_cast_varchar_to_date(VARCHAR ) RETURNS date
    AS 'select $1::date;'
    LANGUAGE SQL
    RETURNS NULL ON NULL INPUT;


--Common function for h2 and vct to add hours
--References: http://stackoverflow.com/questions/9376350/postgresql-how-to-concat-interval-value-2-days

CREATE OR REPLACE FUNCTION vct_add_hours(timestamp, integer) RETURNS timestamp
    AS 'select $1 +  ($2 || '' hours'')::interval'
    LANGUAGE SQL
    RETURNS NULL ON NULL INPUT;

推荐答案

问题是spring的SplitSqlScript： https://www.codatlas.com/github.com/spring-projects/spring-framework/HEAD/spring-jdbc/src/main/java/org/springframework/jdbc/datasource /init/ScriptUtils.java?line=166

The problem is SplitSqlScript of spring: https://www.codatlas.com/github.com/spring-projects/spring-framework/HEAD/spring-jdbc/src/main/java/org/springframework/jdbc/datasource/init/ScriptUtils.java?line=166

我通过更改create函数语句的语法来避免 $$ 并将SQL语句括在引号中。

I worked through this by changing the syntax of my create function statement to avoid $$ and enclose the SQL statement within quotes.

例如

CREATE OR REPLACE FUNCTION vct_pgres_cast_varchar_to_date(VARCHAR ) RETURNS date
    AS 'select $1::date;'
    LANGUAGE SQL
    RETURNS NULL ON NULL INPUT;

这篇关于创建函数-UncategorizedScriptException-ArrayIndexOutOfBoundsException的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！