-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

All times are UTC - 5 hours [ DST ]



Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 5 posts ] 
Author Message
 Post subject: Quote table and column names
PostPosted: Wed Apr 01, 2009 4:08 am 
Newbie

Joined: Wed Nov 26, 2008 5:51 am
Posts: 3
Location: Mannheim
Hi folks,

Yesterday I bumped into the following problem: I've defined an entity called "Constraint". When trying to run my app-server with the hibernate.tablecreation setting on "create", Hibernate complains that there has been a problem with running the create table statement. That is, however, no surprise, as the word constraint is a reserved word in SQL and thus the statement fails. It works fine, when I try to run the statement like "create table `constraint`...". I read somewhere that defining the entity like


Code:
@Entity(name="Constraint")
@Table(name="`Constraint`")
public class Constraint
{
....


will do the trick. However, I am interested in a way to tell Hibernate to always use quotes for table and column names. Is that possible?

Hibernate version:3.2.5.ga


Regards,

Vassil


Top
 Profile  
 
 Post subject:
PostPosted: Wed Apr 01, 2009 7:47 am 
Expert
Expert

Joined: Wed Mar 03, 2004 6:35 am
Posts: 1240
Location: Lund, Sweden
I am not 100% sure, but I think that maybe a custom NamingStrategy can do the trick. See http://www.hibernate.org/hib_docs/v3/re ... ategy.html
and http://www.hibernate.org/hib_docs/v3/ap ... ategy.html


Top
 Profile  
 
 Post subject:
PostPosted: Wed Apr 01, 2009 8:57 am 
Newbie

Joined: Wed Nov 26, 2008 5:51 am
Posts: 3
Location: Mannheim
You were right! It did the trick! Thanks a lot.

For anyone who might be interested, here's what I did:

1. create a custom naming strategy class:

Code:
package com.mdpframework.dataaccess.hibernate;

import org.hibernate.cfg.DefaultNamingStrategy;
import org.hibernate.dialect.Dialect;

public class DefaultQuotedNamingStrategy extends DefaultNamingStrategy
{
   private static Dialect _dialect = new MySQLInnoDBUTF8Dialect();

   @Override
   public String classToTableName(String className)
   {

      System.out.println("Adding quotes to table from class "+className);
      return addQuotes(super.classToTableName(className));
   }
   
   @Override
   public String tableName(String tableName)
   {
      System.out.println("Adding quotes to table "+tableName);
      return addQuotes(super.tableName(tableName));
   }

   /**
    * Adds opening and closing quotes as provided by the current dialect.
    *
    * @param input
    *          the input to quote
    * @return the qouted input
    */
   private static String addQuotes(String input)
   {
      return new StringBuffer().append(_dialect.openQuote())
                         .append(input)
                         .append(_dialect.closeQuote()).toString();
   }
}


Unfortunately, what I didn't get to work is, load the dialect from the settings. When using
Code:
private static Dialect _dialect = Dialect.getDialect();
I got an exception, telling me there is no dialect set. It's some kind of config issue, as the config says

Code:
   <bean id="hibernateProperties" class="org.springframework.beans.factory.config.PropertiesFactoryBean">
      <property name="properties">
         <props>
            ...
            <prop key="hibernate.dialect">com.mdpframework.dataaccess.hibernate.MySQLInnoDBUTF8Dialect</prop>
            ...
         </props>
      </property>
   </bean>


Usually that works. Aaaanyway

2. Set the naming strategy
Code:
   <bean id="sessionFactory" class="org.springframework.orm.hibernate3.annotation.AnnotationSessionFactoryBean" autowire-candidate="true"  depends-on="hibernateDataSource">
      <property name="dataSource" ref="hibernateDataSource" />
      <property name="hibernateProperties" ref="hibernateProperties" />
      <property name="namingStrategy" ref="namingStrategy"/>
      <property name="configurationClass"><value>org.hibernate.cfg.AnnotationConfiguration</value></property>
      <property name="configLocation"><value>classpath:hibernate.cfg.xml</value></property>
   </bean>

   <bean id="namingStrategy" class="com.movilitas.mdpframework.dataaccess.hibernate.DefaultQuotedNamingStrategy"/>


3. Stop worrying about table names that are SQL reserved words :)

One could extend the above class to also quote column names to use special words for column names as well.

Thanks again!

All the best,
Vassil


Top
 Profile  
 
 Post subject:
PostPosted: Wed Apr 01, 2009 9:37 am 
Expert
Expert

Joined: Wed Mar 03, 2004 6:35 am
Posts: 1240
Location: Lund, Sweden
Quote:
Unfortunately, what I didn't get to work is, load the dialect from the settings


Hmmm... I am wondering if the dialect needed...

If you have backticks in the mappings file, Hibernate will convert it to the proper quote character internally, but I don't know if this happens before or after invoking the methods in the NamingStrategy. If the conversion happens after it should be enough for the NamingStrategy to always use backticks.


Top
 Profile  
 
 Post subject:
PostPosted: Wed Apr 01, 2009 10:08 am 
Newbie

Joined: Wed Nov 26, 2008 5:51 am
Posts: 3
Location: Mannheim
Hm, interesting point.

I guess it's even the right way to do this, as it's simpler and cleaner.

So the class would be:

Code:
package com.mdpframework.dataaccess.hibernate;

import org.hibernate.cfg.DefaultNamingStrategy;

public class DefaultQuotedNamingStrategy extends DefaultNamingStrategy
{

   @Override
   public String classToTableName(String className)
   {
      return addQuotes(super.classToTableName(className));
   }
   
   @Override
   public String tableName(String tableName)
   {
      return addQuotes(super.tableName(tableName));
   }

   /**
    * Adds backticks before and after the name.
    *
    * @param input
    *          the input to quote
    * @return the quoted input
    */
   private static String addQuotes(String input)
   {
      return new StringBuffer().append('`')
                         .append(input)
                         .append('`').toString();
   }
}


Regards,
Vassil


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 5 posts ] 

All times are UTC - 5 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.