What’s wrong with my Hibernate configuration?

Question

I´ve an application with Spring + JSF + PrimeFaces + Hibernate + MySQL, so I was told that I needed to migrate the database from MySQL to Oracle, and I did it. So when I was testing the app I found that some queries created by hibernate weren´t working.

For example my app had a query like this:

select U.ID from usuarios U and with mysql it works, but with oracle 11g it tells me that it can´t find the table or view. So I test it with sql developer and the only way to make it work was to change it to:

select U.ID from "ROOT"."usuarios" U

So now how should I configure hibernate so it creates queries like my second query instead of my first query??

Here is my hibernate configuration:

    <bean id="DataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close">
    <property name="driverClass" value="oracle.jdbc.driver.OracleDriver" /> 
    <property name="jdbcUrl" value="jdbc:oracle:thin:@127.0.0.1:1521:xe" />
    <property name="user" value="user" />   
    <property name="password" value="pass" />   
    <property name="maxPoolSize" value="50" />
    <property name="maxStatements" value="0" />
    <property name="minPoolSize" value="5" /> 
</bean>    
<bean id="SessionFactory" class="org.springframework.orm.hibernate4.LocalSessionFactoryBean">
    <property name="dataSource">
        <ref bean="dataSource" />
    </property>
    <property name="annotatedClasses">
        <list>
            <value>com.proximate.model.Usuarios</value>
        </list>
    </property>
    <property name="hibernateProperties">
        <props>
            <prop key="hibernate.dialect">org.hibernate.dialect.Oracle10gDialect</prop>
            <prop key="hibernate.show_sql">true</prop>
            <!--prop key="hibernate.use_sql_comments">true</prop-->
            <prop key="hibernate.format_sql">true</prop>                         
        </props>
    </property>
</bean>

Perhaps this will help? http://stackoverflow.com/questions/2353594/default-schema-in-oracle-connection-url — Enno Shioji, Dec 25 '13 at 22:10

score 1 · Accepted Answer · answered Dec 25 '13 at 22:53

1

You can login with the user that the schema belongs to, in this case "ROOT", that way you wont need to type the name of the schema before the table name.

answered Dec 25 '13 at 22:53

Sinisha Mihajlovski

1,811
1
20
34

besides that I need to add the quoetes in my entity bean like this: @Entity @Table(name = "\"usuarios\"") public class Usuarios implements Serializable { – linker85 Dec 26 '13 at 00:02
I think you have created the table in a wrong way. Oracle table names by default are case insensitive. If you recreate the tables to be case insensitive, you can loose the quotes in your entity. – Sinisha Mihajlovski Dec 26 '13 at 12:42

Marcel Stör · Answer 2 · 2013-12-25T22:54:17.810

0

You need to set hibernate.default_schema (docs) if username != schema owner.

<bean id="SessionFactory" class="org.springframework.orm.hibernate4.LocalSessionFactoryBean">
    ...
    </property>
    <property name="hibernateProperties">
        <props>
            ...
            <prop key="hibernate.default_schema">your-schema-name</prop>
        </props>
    </property>
</bean>

edited Dec 25 '13 at 22:54

answered Dec 25 '13 at 22:47

Marcel Stör

22,695
19
92
198

i already try it and in my query I get select U.ID from ROOT.usuarios U but it still can´t find the table – linker85 Dec 25 '13 at 22:57
Isn't that exactly the query you mentioned in the question above (i.e. the one that is supposed to work)? If it is and it doesn't work with Hibernate then that's because the user in the configured data source doesn't have access. – Marcel Stör Dec 25 '13 at 23:09
I connected with the user ROOT as sinisa229 mihajlovski suggested now I don´t need to do "root"."usuarios", just "usuarios" but the query won´t find the table if I don´t use double quotes. – linker85 Dec 25 '13 at 23:43
Yes, as I said in my answer you only need that if username != schema owner. The fact that the quotes seem to be necessary is really odd. In Oracle [" are not allowed in table names](http://docs.oracle.com/cd/E13085_01/doc/timesten.1121/e13070/names.htm) so believe the data base configuration is strange... – Marcel Stör Dec 26 '13 at 08:28
indeed its weird because after checking the tables they were in lower case between quoetes so I took the quoetes and put them in upper case and that did the trick. – linker85 Dec 26 '13 at 19:10

What’s wrong with my Hibernate configuration?

2 Answers2