使用Spring JDBC用脚本初始化数据库失败

Question

我正在尝试初始化我的数据库的视图和以下75.3规则使用Spring JDBC初始化数据库。

使用Spring JDBC初始化数据库 Spring JDBC有一个数据源初始化器特性。Spring Boot默认启用它，并从标准位置模式加载SQL。SQL和数据。SQL(在类路径的根目录中)。此外，Spring Boot将加载模式-${platform}。SQL和数据-${platform}。SQL文件(如果存在)，其中platform是spring.datasource的值。平台，例如，您可以选择将其设置为数据库的供应商名称(hsqldb, h2, oracle, mysql, postgresql等)。Spring Boot默认启用Spring JDBC初始化器的快速故障特性，因此如果脚本导致异常，应用程序将无法启动。可以通过设置spring.datasource.schema和spring.datasource来更改脚本位置。如果spring.datasource.initialize=false，则两个位置都不会被处理。

这一节说，如果我放一个schema-postgresql.sql，它应该用文件包含的脚本初始化我的数据库。

不幸的是，脚本以以下错误

结束

由:org.postgresql.util.PSQLException:语法错误在输入SQL状态的结束位置:169在org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2310) ~[postgresql-9.4.1209.jre7.jar:9.4.1209.jre7]在org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2023) ~[postgresql-9.4.1209.jre7.jar:9.4.1209.jre7](QueryExecutorImpl.java:217) ~[postgresql-9.4.1209.jre7.jar:9.4.1209.jre7]postgresql.jdbc. pgstatement .execute(PgStatement.java:421) ~[postgresql-9.4.1209.jre7.jar:9.4.1209.jre7]postgresql.jdbc. pgstatement . executewithflags (PgStatement.java:318) ~[postgresql-9.4.120 .jre7.jar:9.4.120 .jre7]postgresql.jdbc. pgstatement .execute(PgStatement.java:310) ~[postgresql-9.4.1209.jre7.jar:9.4.1209.jre7][spring-jdbc-4.3.2.RELEASE.jar:4.3.2.RELEASE]…64个常用帧省略

然而，如果我从pgAdminIII运行这个脚本，没有任何错误，并且创建了相应规则的视图，没有任何问题。

我在这里做错了什么?

这是我的Spring Boot示例来复制它的结构。

src/主/java/com/example/model/Person.java

package com.example.model;
import java.io.Serializable;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
@Entity
public class Person implements Serializable {
    private static final long serialVersionUID = 1334414548362400146L;
    @Id
    private long id;
    @Column(nullable = false, length = 100)
    private String name = "";
    @Column(nullable = false, length = 100)
    private String surname = "";
}

src/主/java/com/example/model/PersonRole.java

package com.example.model;
import java.io.Serializable;
import javax.persistence.EmbeddedId;
import javax.persistence.Entity;
@Entity
public class PersonRole implements Serializable {
    private static final long serialVersionUID = -3953147119216643027L;
    @EmbeddedId
     private PersonRoleKey primaryKey;
}

src/主/java/com/example/model/PersonRoleKey.java

package com.example.model;
import java.io.Serializable;
import javax.persistence.Embeddable;
import javax.persistence.EnumType;
import javax.persistence.Enumerated;
import javax.persistence.ForeignKey;
import javax.persistence.ManyToOne;
import javax.persistence.PrimaryKeyJoinColumn;
@Embeddable
public class PersonRoleKey implements Serializable {
    private static final long serialVersionUID = 2105526364632711640L;
    @ManyToOne(optional = false)
    @PrimaryKeyJoinColumn(foreignKey = @ForeignKey(name = "person_fk"))
    private Person person;
    @Enumerated(EnumType.STRING)
    private Role role;
}

src/主/java/com/example/model/Role.java

package com.example.model;
public enum Role {
    ADMIN, USER;
}

src/主/java/com/example/DemoApplication.java

package com.example;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
@SpringBootApplication
public class DemoApplication {
    public static void main(String[] args) {
        SpringApplication.run(DemoApplication.class, args);
    }
}

src/主/资源/application.properties

#Database configuration
spring.datasource.url: jdbc:postgresql://localhost:5432/postgres
spring.datasource.driverClassName: org.postgresql.Driver
spring.datasource.username: postgres
spring.datasource.password: postgres
spring.datasource.platform: postgresql
spring.datasource.continue-on-error: false
spring.jpa.properties.hibernate.dialect: org.hibernate.dialect.PostgreSQLDialect
spring.jpa.properties.hibernate.format_sql: true
spring.jpa.generate-ddl: true
spring.jpa.hibernate.ddl-auto: update
#default means org.hibernate.boot.model.naming.ImplicitNamingStrategyJpaCompliantImpl
spring.jpa.properties.hibernate.implicit_naming_strategy: default
spring.jpa.hibernate.naming.physical-strategy: org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl
#spring.jpa.properties.hibernate.implicit_naming_strategy=org.hibernate.boot.model.naming.ImplicitNamingStrategyLegacyHbmImpl
spring.jpa.properties.hibernate.auto_quote_keyword: true
spring.jpa.show-sql: false

src/主/资源/schema-postgresql.sql

CREATE OR REPLACE VIEW v_peoples_roles AS
 SELECT p.id,
    p.name,
    p.surname,
    pr.role
   FROM (person p
     JOIN personrole pr ON ((p.id = pr.person_id)));
CREATE OR REPLACE RULE insert_v_peoples_roles AS
    ON INSERT TO v_peoples_roles DO INSTEAD ( INSERT INTO person (id, name, surname)
  VALUES (new.id, new.name, new.surname);
 INSERT INTO personrole (person_id, role)
  VALUES (new.id, new.role);
);

pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <groupId>com.example</groupId>
    <artifactId>demo</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <packaging>jar</packaging>
    <name>demo</name>
    <description>Demo project for Spring Boot</description>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>1.4.0.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <properties>
        <hibernate.version>5.2.2.Final</hibernate.version>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
        <java.version>1.8</java.version>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>
        <dependency>
            <groupId>org.postgresql</groupId>
            <artifactId>postgresql</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
    </dependencies>
    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>

</project>

完整的代码可以从这里下载。

<标题>编辑

根据poz的评论，我将schema-postgresql.sql更改为

CREATE OR REPLACE VIEW v_peoples_roles AS
 SELECT p.id,
    p.name,
    p.surname,
    pr.role
   FROM (person p
     JOIN personrole pr ON ((p.id = pr.person_id)));
CREATE OR REPLACE FUNCTION insert_into_v_people_roles() RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO person (id, name, surname) VALUES (new.id, new.name, new.surname);
  INSERT INTO personrole (person_id, role) VALUES (new.id, new.role);
  RETURN new;
END;
$$ LANGUAGE plpgsql;
DROP TRIGGER IF EXISTS insert_v_peoples_roles ON v_peoples_roles;
CREATE TRIGGER insert_v_peoples_roles INSTEAD OF INSERT ON v_peoples_roles FOR EACH ROW EXECUTE PROCEDURE insert_into_v_people_roles();

但是它产生了另一个错误，和预测的完全一样。

错误:$$ BEGIN INSERT INTO person (id, name，姓)VALUES (new。Id, new.name, new.姓)"

Answer 1

由于pozs还没有发布自己的答案，一段时间过去了，我自己做了。

将CREATE RULE改为INSTEAD OF触发并将$$ -引用改为' -引用解决了问题。唯一的问题是，我必须转义函数定义中的所有撇号。不过也没那么痛苦。