[hibernate-issues] [Hibernate-JIRA] Commented: (HBX-1000) Problem with PostgreSQL using uppercase letters in tables

[Jeff Adama (JIRA)]

    [ http://opensource.atlassian.com/projects/hibernate/browse/HBX-1000?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=33842#action_33842 ] 

Jeff Adama commented on HBX-1000:
---------------------------------

I finally found a work-around for using upper case and mixed case column names in postgreSQL with hibernate and Jboss Seam. The post in which I discovered this is at the following link:
https://forum.hibernate.org/viewtopic.php?f=1&t=935715&p=2416841#p2416841

The backticks information in hibernate reference is here in section 5.4. SQL quoted identifiers:
http://docs.jboss.org/hibernate/stable/core/reference/en/html/mapping.html

The following is an example of using the work-around and my suggestion:

I have a very large PostgreSQL database that I will be using with JBoss Seam. The database uses mixed-case column names per naming convensions at Sun Microsystems and IBM. Until finding this post, I was very surprised by the potential need to have to change all column names in the PostgreSQL database to lowercase in order for hibernate and PostgreSQL to successfully interact. Per this post, the backtick marks (`) solved the problem.

However, I would strongly suggest that either modifying Hibernate to allow for interacting with uppercase (and mixed case) in PostgreSQL without the backtick marks) and/or making it very clear in appropriate sections of hibernate, seam and postgresql documentation that backticks are required for upper case and mixed case column names.

The workaround solution to this issue was as follows:
In the entity I added an @Column(name="`mixedCaseColumnName`").
For example:

@Entity
@Name("cat")
@Table(name="cats")

public class Cat implements Serializable
{
	private Integer id;
	private String name;
	private String distinguishingFeatures;
   
   public Cat() {}
   
   public Cat(Integer id, String name, String distinguishingFeatures)
   {
      this.id = id;
      this.name = name;
      this.distinguishingFeatures = distinguishingFeatures;
   }

(The definition statements for the id and name variables)

   @Column(name="`distinguishingFeatures`")
   public String getDistinguishingFeatures()
   {
      return distinguishingFeatures;
   }

   public void setDistinguishingFeatures(String distinguishingFeatures)
   {
      this.distinguishingFeatures = distinguishingFeatures;
   }
   
   @Override
   public String toString()
   {
      return "Cat(" + id + "," + name + "," + distinguishingFeatures + ")";
   }




> Problem with PostgreSQL using uppercase letters in tables
> ---------------------------------------------------------
>
>                 Key: HBX-1000
>                 URL: http://opensource.atlassian.com/projects/hibernate/browse/HBX-1000
>             Project: Hibernate Tools
>          Issue Type: Bug
>          Components: reverse-engineer
>    Affects Versions: 3.2.beta11
>         Environment: Hibernate 3.2.5.ga, Hibernate Tools 3.2.0 beta9a, PostgreSql 8.2, postgresql-8.2-506.jdbc3.jar, Eclipse 3.3.0, all running on Windows XP at the moment (including PostgreSql)
>            Reporter: Ruediger Engelberger
>
> I have problems using Hibernate tools when using upper case letters in a PostgreSQL database.
> I can configure Hibernate Tools for my local PostgreSql installation without any problems. I can access the table structure in the view Hibernate Configurations and do reverse engineering by creating XML mappings and beans. No problem.
> But when I'm using upper case characters in table names Hibernate Tools can't access the table structures any more. Hibernate Configuration shows the tables, but when I want to see the columns, it doesn't show anything. Reverse engineering also doesn't work any more.
> So, the following works:
> TABLE cms_clusterMessage
> pk_clustermessageid
> fk_clusternodeid
> messagetype
> messagedetail
> The following doesn't work:
> TABLE cms_clusterMessage
> PK_ClusterMessageId
> FK_ClusterNodeId
> MessageType
> MessageDetail
> I tried to use different JDBC drivers because I thought it could be a bug of the driver. But it wasn't. 

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://opensource.atlassian.com/projects/hibernate/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira