How to set up default schema name in JPA configuration?

Question

I found that in hibernate config file we could set up parameter hibernate.default_schema:

<hibernate-configuration> 
   <session-factory>
      ...
      <property name="hibernate.default_schema">myschema</property>
      ...
   </session-factory>
</hibernate-configuration>

Now I'm using JPA and I want to do the same. Otherwise I have to add parameter schema to each @Table annotation like:

@Entity
@Table (name = "projectcategory", schema = "SCHEMANAME")
public class Category implements Serializable { ... }

As I understand this parameter should be somewhere in this part of configuration:

<bean id="domainEntityManagerFactory" class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
    <property name="persistenceUnitName" value="JiraManager"/>
    <property name="dataSource" ref="domainDataSource"/>
    <property name="jpaVendorAdapter">
        <bean class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter">
            <property name="generateDdl" value="false"/>
            <property name="showSql" value="false"/>
            <property name="databasePlatform" value="${hibernate.dialect}"/>
        </bean>
    </property>
</bean>

<bean id="domainDataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close">
    <property name="driverClass" value="${db.driver}" />
    <property name="jdbcUrl" value="${datasource.url}" />
    <property name="user" value="${datasource.username}" />
    <property name="password" value="${datasource.password}" />
    <property name="initialPoolSize" value="5"/>
    <property name="minPoolSize" value="5"/>
    <property name="maxPoolSize" value="15"/>
    <property name="checkoutTimeout" value="10000"/>
    <property name="maxStatements" value="150"/>
    <property name="testConnectionOnCheckin" value="true"/>
    <property name="idleConnectionTestPeriod" value="50"/>
</bean>

... but I can't find its name in google. Any ideas?

score 90 · Accepted Answer · answered Apr 29 '10 at 13:11

90

Don't know of JPA property for this either. But you could just add the Hibernate property (assuming you use Hibernate as provider) as

...

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

...

Hibernate should pick that up

answered Apr 29 '10 at 13:11

bert

7,566
3
31
47

4

Should be added to `` section in persistent unit in `persistence.xml`. – Roman Apr 29 '10 at 13:51
2

Sorry, did not see that you have no persistence.xml but use Spring to configure Hibernate/JPA. I would try to add this under the HibernateJpaVendorAdapter as a property. Perhaps even without the "hibernate." in front of the name. – bert Apr 29 '10 at 13:51
1

The problem here is I can't use a property as in the Spring files, as ${jdbc.schema} or similar. – Chexpir Apr 03 '14 at 10:35
in @formula elements, hibernate is not including the shema, and makes the query fails in sqlserver https://stackoverflow.com/questions/51088034/hibernate-formula-doesnt-include-schema – Alfredo M Jun 29 '18 at 21:39

score 37 · Answer 2 · edited May 23 '17 at 12:03

Just to save time of people who come to the post (like me, who looking for Spring config type and want you schema name be set by an external source (property file)). The configuration will work for you is

<bean id="domainEntityManagerFactory" class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
    <property name="persistenceUnitName" value="JiraManager"/>
    <property name="dataSource" ref="domainDataSource"/>
    <property name="jpaVendorAdapter">
        <bean class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter">
            <property name="generateDdl" value="false"/>
            <property name="showSql" value="false"/>
            <property name="databasePlatform" value="${hibernate.dialect}"/>
        </bean>
    </property>
    <property name="jpaProperties">
       <props>
            <prop key="hibernate.hbm2ddl.auto">none</prop>
            <prop key="hibernate.default_schema">${yourSchema}</prop>
       </props>
</property>

</bean>

Ps : For the hibernate.hdm2ddl.auto, you could look in the post Hibernate hbm2ddl.auto possible values and what they do? I have used to set create-update,because it is convenient. However, in production, I think it is better to take control of the ddl, so I take whatever ddl generate first time, save it, rather than let it automatically create and update.

score 31 · Answer 3 · answered May 11 '17 at 09:12

31

For others who use spring-boot, java based configuration,

I set the schema value in application.properties

spring.jpa.properties.hibernate.dialect=...
spring.jpa.properties.hibernate.default_schema=...

answered May 11 '17 at 09:12

WengKit Lei

432
4
4

score 30 · Answer 4 · edited Nov 26 '21 at 14:04

In order to avoid hardcoding schema in JPA Entity Java Classes we used orm.xml mapping file in Java EE application deployed in OracleApplicationServer10 (OC4J,Orion). It lays in model.jar/META-INF/ as well as persistence.xml. Mapping file orm.xml is referenced from peresistence.xml with tag

...
  <persistence-unit name="MySchemaPU"  transaction-type="JTA">
    <provider>...</provider>
    <mapping-file>META-INF/orm.xml</mapping-file>
...

File orm.xml content is cited below:

<?xml version="1.0" encoding="UTF-8"?>
<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 http://java.sun.com/xml/ns/persistence/orm_1_0.xsd"
                 version="1.0">

  <persistence-unit-metadata>
    <persistence-unit-defaults>
      <schema>myschema</schema>
    </persistence-unit-defaults>
  </persistence-unit-metadata>
</entity-mappings>

I'd prefer this solution over the others as it is standard and not implementation-specific. — Isaac, Feb 26 '14 at 19:21
I got parseexceptino - needed to remove provider element from persistence-unit META-INF/orm.xml ... — JJ Roman, Dec 11 '14 at 16:09

score 16 · Answer 5 · answered Jun 09 '19 at 16:31

For those who uses last versions of spring boot will help this:

.properties:

spring.jpa.properties.hibernate.default_schema=<name of your schema>

.yml:

spring:
    jpa:
        properties:
            hibernate:
                default_schema: <name of your schema>

score 5 · Answer 6 · answered Dec 05 '19 at 10:52

5

I had to set the value in '' and ""

spring:
   jpa:
     properties:
       hibernate:
          default_schema: '"schema"'

answered Dec 05 '19 at 10:52

Chris

5,109
3
19
40

score 3 · Answer 7 · answered Sep 25 '19 at 21:36

3

Use this

@Table (name = "Test", schema = "\"schema\"")

insteade of @Table (name = "Test", schema = "schema")

If you are on postgresql the request is :

SELECT * FROM "schema".test

not :

SELECT * FROM schema.test

PS: Test is a table

answered Sep 25 '19 at 21:36

Kader

39
1

score 2 · Answer 8 · answered Aug 12 '20 at 06:40

2

If you are using (org.springframework.jdbc.datasource.DriverManagerDataSource) in ApplicationContext.xml to specify Database details then use below simple property to specify the schema.

<property name="schema" value="schemaName" />

answered Aug 12 '20 at 06:40

Mahaveer Jangir

597
7
15

How to set up default schema name in JPA configuration?

8 Answers8

Linked

Related