How to change the default postgresql schema in persistence.xml in a java application

Question

I am developing an application and I have a certain difficulty to change the default scheme of the application. I am using Postgresql in a Java application with JPA and Hibernate as implementation.

persistence.xml

<persistence-unit name="comunicaVisual" transaction-type="RESOURCE_LOCAL">
    <provider>org.hibernate.ejb.HibernatePersistence</provider>
    <class>entys.Pessoa</class>

    <exclude-unlisted-classes>true</exclude-unlisted-classes>

    <properties>
        <property name="javax.persistence.jdbc.url" value="jdbc:postgresql://localhost/postgres" />
        <property name="javax.persistence.jdbc.driver" value="org.postgresql.Driver" />
        <property name="javax.persistence.jdbc.user" value="postgres" />
        <property name="javax.persistence.jdbc.password" value="1" />

        <property name="hibernate.hbm2ddl.auto" value="update" />
        <property name="hibernate.show_sql" value="true" />
        <property name="hibernate.format_sql" value="true" />
        <property name="hibernate.dialect" value="org.hibernate.dialect.PostgreSQLDialect" />

        <property name="hibernate.cache.use_query_cache" value="true" />
        <property name="hibernate.cache.region.factory_class"
            value="org.hibernate.cache.ehcache.EhCacheRegionFactory" />
    </properties>
</persistence-unit>

I want to put the application instead of the scheme public pattern placed as another name for example

<property name="javax.persistence.jdbc.user" value="postgres/dremcom_drem" />

but I’m not succeeding with the attempts in that way.

Answer 1

I think I’ve got to configure the property that handles schema selection

would be something like

<property name="hibernate.default_schema" value="myschema"/>

Depending on the case, eventually you may need to make this setting using the tag
persistence-Unit-Metadata. Take a look at this other issue here from the same OS that talks about it: https://stackoverflow.com/questions/3211138/jpa-eclipselink-how-to-change-default-schema

In this case your procedure would be something like this:

1) Create a file called Orm.xml, save it in the same folder where your persistence.xml is and add in it the following content

<entity-mappings xmlns="http://java.sun.com/xml/ns/persistence/orm"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://java.sun.com/xml/ns/persistence/orm orm_2_0.xsd"
version="2.0">
    <persistence-unit-metadata>
        <persistence-unit-defaults>
            <schema>NOME_DO_SEU_SCHEMA</schema>
        </persistence-unit-defaults>
    </persistence-unit-metadata>   
</entity-mappings>

2) Modify the content of your persistence.xml to reference Orm.xml, more or less like this:

<persistence-unit name="comunicaVisual" transaction-type="RESOURCE_LOCAL">

 <mapping-file>orm.xml</mapping-file>   
     <provider>org.hibernate.ejb.HibernatePersistence</provider>
 <class>entys.Pessoa</class>
     <!--- restante das configuraçoes seguem aqui -->

</persistence-unit>

3) Deploy and test the application again

One last hypothesis would be to set the schema name via Annotations in the entity itself, but this is a little unnecessary in case your application only accesses a database

@Entity
@Table(name = "sua-tablea", schema = "nome-do-schema")