问题描述
我正在将Spring Boot 2与JPA一起使用，然后将其留给Hibernate从我的实体中创建数据库，效果很好.现在，我想添加一个data.sql文件，该文件将为我的数据库添加种子.我将JPA配置如下:
I am using Spring Boot 2 with JPA, and I leave it to Hibernate to create my database from my entities, which works fine. Now I want to add a data.sql file which will seed my database. I configured JPA as follows:
spring.jpa.properties.hibernate.hbm2ddl.import_files=data.sql
但是执行种子SQL时遇到问题.在文件中，我定义了几个函数，最后我执行了它们:
However I have a problem when executing the seed SQL. In the file I have defined a couple of functions, and at the end I am executing them:
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运行它，则会得到以下信息:
If I execute the same code in a PostgreSQL console to read from the file it works fine. But if I run it via Spring, I keep getting the following:
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.我读到函数定义中的定界符$$无法正确解析，但是我找不到解决方法.我尝试使用''代替$$并在所有位置转义单引号，但是那也不起作用.
I am using PostgreSQL 9.5 and Spring Boot 2.0.3. I read that the delimiter $$ in the function definition cannot be parsed correctly, but I cannot find how to solve this. I tried instead of $$ to have it with simple '' and escape the single-quote everywhere, but that didn't work either.
推荐答案
问题不在于语法，因为该语法与flyway完全兼容，或者直接在PostgreSQL CLI中使用.问题出在Hibernate，特别是解析导入文件. Hibernate的工作方式是单独执行文件中的每个表达式，而不是将整个内容作为单个表达式执行.我试图将所有函数定义放在一行中，并且可以正常工作，但是它不可读.因此，我发现Hibernate有一个配置可以告诉它表达式可以是多行的，但是$$分隔符在多行中使用时仍然无法识别.
The problem was not the syntax, because the syntax was perfectly working with flyway or directly in PostgreSQL CLI. The problem was with Hibernate, specifically with parsing the import file. The way Hibernate works is that it executes each expression from the files individually, not the whole content as a single expression. I tried to put all function definitions in one line and it worked, but it was not readable. So I found that there is a configuration for Hibernate to tell it that expressions can be multi-lined, but the $$ delimiter was still unrecognized when used in multi-line.
因此解决方案是使用'分隔符定义命令，然后在需要的地方使用附加的'转义单引号.
So the solution was to define the command with ' delimiter and then escape the single quotes where needed with an additional '.
解决方案是将spring.jpa.properties.hibernate.hbm2ddl.import_files_sql_extractor设置为使用org.hibernate.tool.hbm2ddl.MultipleLinesSqlCommandExtractor. MultipleLinesSqlCommandExtractor从多行提取SQL表达式，并在出现分号时停止.到此为止.通过将函数的主体包装在单引号字符串中，Hibernate将把该包装视为单行.
The solution is to set the spring.jpa.properties.hibernate.hbm2ddl.import_files_sql_extractor to use org.hibernate.tool.hbm2ddl.MultipleLinesSqlCommandExtractor. MultipleLinesSqlCommandExtractor extracts the SQL expression from multiple lines, and stops when a semicolon is present. That is the end of the expression. By wrapping the body of the function in single-quote string, Hibernate will treat that wrapping as a single line.
data.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();
我必须始终谨记要在表达式中转义单引号，但是现在我可以拥有一个更易于理解的种子文件了.
I have to always keep in mind to escape the single-quotes in the expressions, but now I can have a more human-readable seed file.
这篇关于在PostgreSQL中使用种子文件时，JPA中的异常的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！