I'm currently working on a Java application using Jakarta Persistence with EclipseLink and PostgreSQL. While setting up the application to test the database layer, the persistence configuration is done in such way to automatically create and initialize the database:

<?xml version="1.0" encoding="UTF-8"?>
<persistence ...>
  <persistence-unit name="test">
    <properties>
      ...
      <property name="jakarta.persistence.schema-generation.create-script-source" value="database/schema.sql" />
      <property name="jakarta.persistence.schema-generation.database.action" value="create" />
      <property name="jakarta.persistence.sql-load-script-source" value="database/data.sql" />
      ...
    </properties>
  </persistence-unit>
</persistence>

<!-- $projectRoot/src/main/resources/META-INF/persistence.xml -->

The problem I'm facing is with both, schema.sql and data.sql scripts. There seems to be a formatting issue where every SQL statement must be on its own line for the scripts to be executed correctly — othewise they aren't executed. This has never been an issue with other tools I've used in the past, such as Liquibase and Flyway.

This is an example of the content that's found on both SQL scripts:

create table table_name_one (id uuid not null, ..., primary key (id));

create table table_name_two (id uuid not null, ..., primary key (id));

-- $projectRoot/src/main/resources/database/schema.sql

insert into table_name_one (id, ...) values (...);
insert into table_name_one (id, ...) values (...);

insert into table_name_two (id, ...) values (...);
insert into table_name_two (id, ...) values (...);

-- $projectRoot/src/test/resources/database/data.sql

Anyway, after finding the issue, I thought it was the JPA provider or the database, so I changed them (just to verify) to Hibernate and MySQL...but regardless, I noticed the exact same issue.

Is there a way to configure JPA to allow multi-line SQL statements in the SQL scripts?

posted 3 months ago

CC0

3mo ago

ɯıpɐʌ‭

154 reputation 4 3 18 2