JPA/Hibernate LocalSessionFactory mapping of Oracl

Question

I'm struggling with mapping Oracle synonyms into JPA Entities.

As a background I'll say, that I need to make it possible either on tomcat 7 server as well as on JBoss EAP 6.4. Below, you'll find a snippet of my persistence configuration.

1. DataSource lookup ( jndi found, datasource bound)

@Bean
public DataSource datasource() throws NamingException{
    Context initCtx = new InitialContext();
    Context envCtx = (Context) initCtx.lookup("java:comp/env");
    DataSource ds = (DataSource) envCtx.lookup("jdbc/PAMGODW_DS");

    return ds;
}

2. LocalSessionFactoryBean (seems to work):

@Bean
public LocalSessionFactoryBean sessionFactory() throws NamingException, IOException{
    Properties props = new Properties();
    props.put("hibernate.dialect", "org.hibernate.dialect.Oracle10gDialect");
    props.put("hibernate.synonyms", "true");
    props.put("hibernate.connection.includeSynonyms", "true");


    LocalSessionFactoryBean f = new LocalSessionFactoryBean();
    f.setDataSource(datasource());
    f.setPackagesToScan(new String[]{"com.roche.nrimini.pam.domain.entity", "com.roche.nrimini.pam.domain.service", "com.roche.nrimini.pam.domain.repository"});
    f.setHibernateProperties(props);
    f.setJtaTransactionManager(jtaTransactionManager());
    f.setAnnotatedPackages(new String[]{"com.roche.nrimini.pam.domain.entity", "com.roche.nrimini.pam.domain.service", "com.roche.nrimini.pam.domain.repository"});
    f.afterPropertiesSet();
    System.out.println("LOCALSESSIONFACTORY BEAN");

    return f;}

3. JtaTransactionManager (seems to be fine as well):

@Bean
public TransactionManager jtaTransactionManager(){
    JtaTransactionManager manager = new JtaTransactionManager();

    return manager.getTransactionManager();
}

4. An Entity, @Table(name="V_TABLE_SYN") actually points to a Oracle synonym:

@Entity
@Table(name = "V_TABLE_SYN")
public class SomeSynonym implements Serializable {

// @Id and @Column mappings here

What I get, when I try to query for Entity objects is ORA-00942 Table or view does not exist.

In JBoss environment, my Datasource config in standalone.xml is like that:

<datasource jta="false" jndi-name="java:jboss/PAMGODW_DS" pool-name="PAMGODW_DS" enabled="true" use-ccm="false">
                <connection-url>jdbc:oracle:thin:@someserver/some_service</connection-url>
                <driver-class>oracle.jdbc.OracleDriver</driver-class>
                <connection-property name="includeSynonyms">
                    true
                </connection-property>
                <driver>oracle</driver>
                <pool>
                    <min-pool-size>0</min-pool-size>
                    <max-pool-size>10</max-pool-size>
                </pool>
                <security>
                    <user-name>some_user</user-name>
                    <password>a_pass</password>
                </security>
                <validation>
                    <validate-on-match>false</validate-on-match>
                    <background-validation>false</background-validation>
                </validation>
                <statement>
                    <share-prepared-statements>false</share-prepared-statements>
                </statement>
            </datasource>

In Tomcat, I got :

<Resource name="jdbc/SOME_DS" auth="Container"
          type="javax.sql.DataSource" driverClassName="oracle.jdbc.OracleDriver"
          url="jdbc:oracle:thin:@some_server/some_service"
          username="some_user" password="a_pass" maxActive="20" maxIdle="10"
          maxWait="-1">

I've heard and read about include.synonyms property, but it seems not to work, and in Tomcat env, I got no clue how to set it.

Please help, maybe some of you have had similar issue.

Answer 1

I managed to make it work. Maybe someone will find it useful. It seems that beside the fact that oracle synonyms can't be validated, setting hibernate.hbm2ddl.auto property is mandatory. As you can see in previous post, I haven't had this property set at all. Cheers!