How to stop fully qualified table names in Hibernate/spring

[sshah]

Hello,

All of my sql statements being generated by hibernate are being qualified by the schema or database name. Is there anyway to prevent this? I have updated the hibernate maps to exclude this, but they are still being qualified so I am not sure where this information is being set, but i need to prevent this. I have pasted one of the maps and my context.xml file below.

thanks for your help



<hibernate-mapping default-lazy="false">
<class name="diamelle.common.service.ServiceConfig" table="service_config">
<comment></comment>
<id name="serviceConfigId" type="string">
<column name="SERVICE_CONFIG_ID" length="20" />
<generator class="assigned" />
</id>
<many-to-one name="service" class="diamelle.common.service.Service" fetch="select">
<column name="SERVICE_ID" length="20">
<comment></comment>
</column>
</many-to-one>
<property name="name" type="string">
<column name="NAME" length="40">
<comment></comment>
</column>
</property>
<property name="value" type="string">
<column name="VALUE" length="40">
<comment></comment>
</column>
</property>
</class>
</hibernate-mapping>



Resulting output



07:48:30,953 INFO [STDOUT] Hibernate: select servicecon0_.SERVICE_ID as SERVICE
2_1_, servicecon0_.SERVICE_CONFIG_ID as SERVICE1_1_, servicecon0_.SERVICE_CONFIG
_ID as SERVICE1_9_0_, servicecon0_.SERVICE_ID as SERVICE2_9_0_, servicecon0_.NAM
E as NAME9_0_, servicecon0_.VALUE as VALUE9_0_ from mysql.service_config service
con0_ where servicecon0_.SERVICE_ID=?


context.xml


<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:aop="http://www.springframework.org/schema/aop"
xmlns:tx="http://www.springframework.org/schema/tx"
xmlns:jee="http://www.springframework.org/schema/jee"
xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-2.0.xsd
http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-2.0.xsd
http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-2.0.xsd
http://www.springframework.org/schema/jee http://www.springframework.org/schema/jee/spring-jee-2.0.xsd">


<!-- Service Beans -->
<bean id="serviceManager" class="diamelle.common.service.ServiceMgr">
<constructor-arg ref="serviceDAO"></constructor-arg>
</bean>

<bean id="serviceDAO" class="diamelle.common.service.ServiceDAOBean">
<property name="sessionFactory" ref="sessionFactory"></property>
</bean>

<!-- User Beans -->
<!--
<bean id="userManager" class="diamelle.common.user.UserManager" >
<constructor-arg ref="userDAO"></constructor-arg>
</bean>
<bean id="userDAO" class="diamelle.common.user.UserDAOBean" >
<property name="entityManagerFactory" ref="entityManagerFactory"></property>
</bean>
-->
<!-- Org Beans -->

<!-- ========================= RESOURCE DEFINITIONS ========================= -->

<!-- Hibernate SessionFactory -->
<bean id="sessionFactory" class="org.springframework.orm.hibernate3.LocalSessionFac toryBean">
<property name="dataSource" ref="dataSource"/>
<property name="mappingResources">
<list>
<value>diamelle/common/service/Service.hbm.xml</value>
<value>diamelle/common/service/ServiceConfig.hbm.xml</value>
<value>diamelle/common/service/RequestForm.hbm.xml</value>
<value>diamelle/common/service/RequestApprover.hbm.xml</value>
</list>
</property>

<property name="hibernateProperties">
<props>
<prop key="hibernate.dialect">org.hibernate.dialect.Oracle9Dialect</prop>
<prop key="hibernate.show_sql">true</prop>
<prop key="hibernate.generate_statistics">true</prop>
</props>
</property>


</bean>


<!-- Local DataSource that works in any environment -->
<!-- Note that DriverManagerDataSource does not pool; it is not intended for production -->
<!-- See JPetStore for an example of using Commons DBCP BasicDataSource as alternative -->
<!-- See Image Database for an example of using C3P0 ComboPooledDataSource as alternative -->


<!-- JNDI DataSource for J2EE environments -->

<jee:jndi-lookup id="dataSource" jndi-name="java:diamellePool"/>




<bean id="transactionManager" class="org.springframework.orm.hibernate3.HibernateTransa ctionManager">
<property name="sessionFactory" ref="sessionFactory"/>
</bean>




<tx:advice id="txAdvice" transaction-manager="transactionManager">
<!-- the transactional semantics... -->
<tx:attributes>
<!-- all methods starting with 'get' are read-only -->
<tx:method name="get*" read-only="true" propagation="SUPPORTS" />
<!-- other methods use the default transaction settings (see below) -->
<tx:method name="*" propagation="REQUIRED"/>
</tx:attributes>
</tx:advice>



<!-- ensure that the above transactional advice runs for any execution
of an operation defined by the FooService interface -->
<aop:config>
<aop:pointcut id="serviceOperation" expression="execution(* diamelle.common.service.ServiceMgr.*(..))"/>
<aop:advisor advice-ref="txAdvice" pointcut-ref="serviceOperation"/>
</aop:config>


</beans>

[yvk]

The below line in context.xml file generates the output like that....


<prop key="hibernate.show_sql">true</prop>

Remove that line from the context.xml, if you want the information don't want to appear in the output.

[sshah]

Thanks, but I was actually to see how I can stop my table from being fully qualified.

ie. its now generating mysql.service_config and I only want service_config as the table name.

Any help would be greatly appreciated.

REgards
Suneet

[Andrei Stefan]

What database are you using ? You get an exception when performing that query ?

If you're using Oracle, I think the default behavior (if you look at the Hibernate source code) for this database is to use schema name inside queries: http://www.hibernate.org/hib_docs/v3/api/org/hibernate/dialect/Dialect.html#qualifyIndexName(). My first idea would be to create a custom Dialect by sub-classing Oracle9Dialect.

[sshah]

Hi Andrei,

The problem is that I created the maps on mysql using the hibernate tools. However, I need to run these against Oracle 9i. The schema in mysql was the default mysql and when the beans are run, hibernate is always adding on the mysql to the table name. it works on mysql, but it fails on Oracle with table not found error. which is true, there is no mysql.service_config. In oracle its idmuser.service_config. However, I have a public synonym for the name so an unqualified name would work just fine.

thanks