Liquibase无法使用Spring Boot获取种子数据 [英] Liquibase not picking up seed data with Spring Boot

问题描述

我正在使用Spring-Boot 1.2.1和Liquibase来创建H2(测试)和PostgreSQL(QA& Production)数据库.创建数据库时，我要播种一些表.但是，尽管同时尝试了dataLoad和sqlFile，也没有插入任何内容.我的sql文件只是一堆插入语句，例如:

I am using Spring-Boot 1.2.1, and Liquibase to create both H2 (testing) and PostgreSQL (QA & Production) databases. I have a couple of tables that I want to seed when the db is created. However, despite trying both dataLoad and sqlFile, nothing is getting inserted. My sql file is just a bunch of insert statements such as:

INSERT INTO state (Name, Code) VALUES('Alabama','AL');
INSERT INTO state (Name, Code) VALUES('Alaska','AK');

这是我相关的changelog-master.xml:

Here is my relevant changelog-master.xml:

    xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.3.xsd"
    objectQuotingStrategy="QUOTE_ONLY_RESERVED_WORDS">

...
<changeSet id="3" author="me">
        <createTable tableName="STATE">
            <column name="code" type="VARCHAR(10)">
                <constraints primaryKey="true"/>
            </column>
            <column name="name" type="VARCHAR(100)"/>
        </createTable>

         <sqlFile dbms="h2, PostgreSQL"
                 encoding="utf8"
                 endDelimiter="\nGO"
                 path="src/main/resources/db/changelog/data/states.sql"
                 relativeToChangelogFile="true"
                 splitStatements="true"
                 stripComments="true"/>
    </changeSet>

这是我的项目结构:

Here is my project structure:

启动spring-boot应用程序时，可以看到创建了State表，但是其中有零行.我还尝试了退出变更集3并使用它:

When I startup my spring-boot app, I can see that the State table is created, but it has zero rows in it. I also tried taking the out of changeset 3 and using this:

  <changeSet id="4" author="me">
            <loadData file="data/state.csv" tablename="STATE" schemaName="edentalmanager" relativeToChangelogFile="true">
            <column name="name" type="VARCHAR(100)"/>
            <column name="code" type="VARCHAR(10)"/>
            </loadData>
        </changeSet>

csv文件基本上是:

The csv file is basically:

Alabama,AL
Alaska,AK
...

我在控制台日志中看不到任何Liquibase试图创建数据或将数据插入表中的消息.我也没有收到任何异常或错误消息.

I dont' see any messages in the console logs that Liquibase is trying to create or insert the data into the table. Nor do I get any exceptions or error messages.

更新: 如果我将state.sql复制为/resources/data.sql，则spring-boot会选择文件并执行sql.不幸的是，这意味着每次我启动时，它将尝试再次插入这些值，从而导致启动异常(重复的键冲突).但是，我宁愿Liquibase而不是依赖单个文件，而是将它们作为变更集的一部分作为数据执行需要改变.

UPDATE: If I copy off the state.sql as /resources/data.sql then spring-boot picks up the file and executes the sql just fine. Unfortunately, this means every time I startup, it will try and insert those values again, causing startup exceptions (duplicate key violations) But, rather than rely on a single file, I would prefer Liquibase to execute them as part of the changeset as data needs change.

推荐答案

我认为您的变更集3中存在两个问题:

I think there are two issues in your changeset 3:

1. path属性的值定向到不存在的资源.当您定义relativeToChangelogFile="true"时，Liquibase将寻找classpath:/db/changelog/src/main/resources/.../states.sql.正确的路径应为path="../data/states.sql".
2. 如果给定路径不正确，则Liquibase应该引发异常.如果您一无所获，则Liquibase会因为其他条件而决定不执行该部分.这些条件之一可能是dbms属性.您的变更集应与H2数据库一起使用.由于错误的类型名称，它不适用于PostgreSQL数据库.尝试使用dbms="h2, postgresql".

1. The value of the path property directs to a not existing resource. When you define relativeToChangelogFile="true" then Liquibase will look for classpath:/db/changelog/src/main/resources/.../states.sql. The correct path should be path="../data/states.sql".
2. If the given path is not correct Liquibase should throw an exception. If you didn't get one means Liquibase decided to not execute that part because of other conditions. One of those conditions could be the dbms property. Your changeset should work with a H2 database. It should not work with a PostgreSQL database because of a wrong type name. Try dbms="h2, postgresql" instead.

这些更改之后，我根据您的项目结构得到了一个填充表.

After those changes I got a filled table based on your project structure.

这篇关于Liquibase无法使用Spring Boot获取种子数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！