Welcome to Software Development on Codidact!

Will you help us build our independent community of developers helping developers? We're small and trying to grow. We welcome questions about all aspects of software development, from design to code to QA and more. Got questions? Got answers? Got code you'd like someone to review? Please join us.

Post History

66%

+2 −0

Q&A SQL scripts referenced in persistence configuration must have each statement on its own line

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 ...

0 answers · posted 3mo ago by ɯıpɐʌ‭ · edited 3mo ago by ɯıpɐʌ‭

Question java sql jpa jakarta-ee jakarta-persistence

#2: Post edited by

ɯıpɐʌ‭ · 2024-09-23T14:25:52Z (3 months ago)
Udate the question to include examples of SQL scripts

Copy Link

Raw

Markdown

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
<?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>
```
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`.
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?

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
<?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>
```
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:
```sql
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
```
```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?

#1: Initial revision by

ɯıpɐʌ‭ · 2024-09-22T21:50:44Z (3 months ago)

Copy Link

Raw

Markdown

SQL scripts referenced in persistence configuration must have each statement on its own line

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
<?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`.

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?

java sql jpa jakarta-ee jakarta-persistence

Communities

Post History