在 JPA 中使用 PostgreSQL 种子文件时出现异常

Question

我正在使用带有JPA的Spring Boot 2，我把它留给Hibernate从我的实体创建我的数据库，这工作正常。现在我想添加一个 data.sql 文件，它将为我的数据库提供种子。我按如下方式配置了 JPA：

spring.jpa.properties.hibernate.hbm2ddl.import_files=data.sql

但是我在执行种子 SQL 时遇到问题。在文件中，我定义了几个函数，最后我正在执行它们：

CREATE OR REPLACE FUNCTION insert_timeout_configuration() RETURNS bigint AS $$
DECLARE created_id bigint;
BEGIN
INSERT INTO timeout_configuration (id, version, timeout)
VALUES (nextval('my_sequence'), 0, 300)
RETURNING id INTO created_id;
return created_id;
END;
$$ language plpgsql;
CREATE OR REPLACE FUNCTION insert_url_configuration() RETURNS bigint AS $$
DECLARE created_id bigint;
BEGIN
INSERT INTO url_configuration (id, version, my_url)
VALUES (nextval('my_sequence'), 0,'http://localhost:8080/')
RETURNING id INTO created_id;
return created_id;
END;
$$ language plpgsql;
DO $$
INSERT INTO global_configuration(id, version, name, timeout_configuration_id, url_configuration_id)
VALUES (nextval('my_sequence'), 0, 'My global config', insert_timeout_configuration(), insert_url_configuration());
-- do some other code 
END
$$;
drop function insert_timeout_configuration();
drop function insert_url_configuration();

如果我在 PostgreSQL 控制台中执行相同的代码以从文件中读取，它可以正常工作。但是如果我通过 Spring 运行它，我会不断得到以下内容：

org.postgresql.util.PSQLException: Unterminated dollar quote started at position 0 in SQL $$ language plpgsql. Expected terminating $$
at org.postgresql.core.Parser.checkParsePosition(Parser.java:1273) ~    [postgresql-42.2.4.jar:42.2.4]
at org.postgresql.core.Parser.parseSql(Parser.java:1172) ~[postgresql-    42.2.4.jar:42.2.4]
at org.postgresql.core.Parser.replaceProcessing(Parser.java:1124) ~    [postgresql-42.2.4.jar:42.2.4]
at     org.postgresql.core.CachedQueryCreateAction.create(CachedQueryCreateAction.java:41) ~[postgresql-42.2.4.jar:42.2.4]
at org.postgresql.core.QueryExecutorBase.createQueryByKey(QueryExecutorBase.java:314) ~[postgresql-42.2.4.jar:42.2.4]
at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:285) ~[postgresql-42.2.4.jar:42.2.4]
at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:270) ~    [postgresql-42.2.4.jar:42.2.4]
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:266) ~    [postgresql-42.2.4.jar:42.2.4]
at com.zaxxer.hikari.pool.ProxyStatement.execute(ProxyStatement.java:95) ~    [HikariCP-2.7.9.jar:?]
at         com.zaxxer.hikari.pool.HikariProxyStatement.execute(HikariProxyStatement.java) ~    [HikariCP-2.7.9.jar:?]
at org.hibernate.tool.schema.internal.exec.GenerationTargetToDatabase.accept(Generat    ionTargetToDatabase.java:54) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]
... 33 more
[DEBUG] 2018-09-07 21:09:43.325 [main] SQL - CREATE OR REPLACE FUNCTION insert_url_configuration() RETURNS bigint AS $$
Hibernate: CREATE OR REPLACE FUNCTION insert_url_configuration() RETURNS bigint AS $$
[WARN ] 2018-09-07 21:09:43.325 [main] ExceptionHandlerLoggedImpl - GenerationTarget encountered exception accepting command : Error executing DDL 
via JDBC Statement

我正在使用PostgreSQL 9.5和Spring Boot 2.0.3。我读到函数定义中的分隔符$$无法正确解析，但我找不到解决这个问题的方法。我尝试用简单的''代替$$，并在任何地方转义单引号，但这也没有用。

Answer 1

问题不在于语法，因为语法可以完美地与flyway一起使用或直接在PostgreSQL CLI中使用。问题出在 Hibernate 上，特别是解析导入文件。Hibernate的工作方式是它单独执行文件中的每个表达式，而不是将整个内容作为单个表达式执行。我试图将所有函数定义放在一行中，它起作用了，但它不可读。所以我发现 Hibernate 有一个配置告诉它表达式可以是多行的，但是在多行中使用时，$$分隔符仍然无法识别。

因此，解决方案是使用分隔符定义命令'然后在需要的地方使用额外的'转义单引号。

解决方案是将spring.jpa.properties.hibernate.hbm2ddl.import_files_sql_extractor设置为使用org.hibernate.tool.hbm2ddl.MultipleLinesSqlCommandExtractor.MultipleLinesSqlCommandExtractor 从多行中提取 SQL 表达式，并在出现分号时停止。这就是表达的结尾。通过将函数的主体包装在单引号字符串中，Hibernate会将该包装视为一行。

数据.sql

CREATE OR REPLACE FUNCTION insert_timeout_configuration() RETURNS bigint AS '
DECLARE created_id bigint;
BEGIN
INSERT INTO timeout_configuration (id, version, timeout)
VALUES (nextval(''my_sequence''), 0, 300)
RETURNING id INTO created_id;
return created_id;
END;
' language plpgsql;
CREATE OR REPLACE FUNCTION insert_url_configuration() RETURNS bigint AS '
DECLARE created_id bigint;
BEGIN
INSERT INTO url_configuration (id, version, my_url)
VALUES (nextval(''my_sequence''), 0,''http://localhost:8080/'')
RETURNING id INTO created_id;
return created_id;
END;
' language plpgsql;
DO '
INSERT INTO global_configuration(id, version, name, timeout_configuration_id, url_configuration_id)
VALUES (nextval(''my_sequence''), 0, ''My global config'', insert_timeout_configuration(), insert_url_configuration());
-- do some other code 
END
';
drop function insert_timeout_configuration();
drop function insert_url_configuration();

我必须始终牢记要转义表达式中的单引号，但现在我可以拥有一个更易于人类阅读的种子文件。