Postgresql – JPA, EclipseLink, PostgreSQL and schemas

By | October 22, 2023

I want to develop a Java EE application with JPA (EclipseLink implementation) and PostgreSQL as database.

I chose to have one database and multiples schemas instead of having multiples databases and one schema per database.

So, in my persistence.xml I have something like that :

<persistence-unit name="00" transaction-type="JTA">

    <provider>org.eclipse.persistence.jpa.PersistenceProvider</provider>
    <jta-data-source>todo</jta-data-source>

    <class>...</class>
    <class>...</class>

    <properties>
        <property name="javax.persistence.target-database" value="PostgreSQL" />
        <property name="javax.persistence.jdbc.driver" value="org.postgresql.Driver" />
        <property name="javax.persistence.jdbc.url" value="jdbc:postgresql://localhost:5432/testdb;create=true" />
        <property name="javax.persistence.jdbc.user" value="user" />
        <property name="javax.persistence.jdbc.password" value="userpwd" />
    </properties>

</persistence-unit>

<persistence-unit name="01" transaction-type="JTA">

    <provider>org.eclipse.persistence.jpa.PersistenceProvider</provider>
    <jta-data-source>todo</jta-data-source>

    <class>...</class>
    <class>...</class>

    <properties>
        <property name="javax.persistence.target-database" value="PostgreSQL" />
        <property name="javax.persistence.jdbc.driver" value="org.postgresql.Driver" />
        <property name="javax.persistence.jdbc.url" value="jdbc:postgresql://localhost:5432/testdb;create=true" />
        <property name="javax.persistence.jdbc.user" value="user" />
        <property name="javax.persistence.jdbc.password" value="userpwd" />
    </properties>

</persistence-unit>

I can easily reference persistence unit :

EntityManagerFactory emf = Persistence.createEntityManagerFactory("01");
EntityManager em = emf.createEntityManager();

but how can I store information about schemas in persistence.xml ? I want to access schema 00 or 01 in testdb database.

I probably could change schema via native SQL directly but is there a way to bind schema to persistence unit ?

Thank you

Best Solution

This same question was asked and answered here:
JPA – EclipseLink – How to change default schema

You can override the schema for the entire persistence unit using an orm.xml file, or define it within each table annotation or xml element as needed.

Category: Uncategorized