This Bugzilla instance is a read-only archive of historic NetBeans bug reports. To report a bug in NetBeans please follow the project's instructions for reporting issues.

Bug 192314 - Generated entity class does not work: table name capitalization is wrong
Summary: Generated entity class does not work: table name capitalization is wrong
Status: RESOLVED WONTFIX
Alias: None
Product: javaee
Classification: Unclassified
Component: Persistence (show other bugs)
Version: 7.0
Hardware: All All
: P3 normal (vote)
Assignee: Sergey Petrov
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2010-11-23 03:31 UTC by slreynolds
Modified: 2015-09-17 13:09 UTC (History)
1 user (show)

See Also:
Issue Type: DEFECT
Exception Reporter:


Attachments
messages log file for completeness (45.11 KB, text/x-log)
2010-11-23 03:31 UTC, slreynolds
Details
generated entity class Ideas.java (2.75 KB, text/x-java)
2010-11-23 03:34 UTC, slreynolds
Details
sql commands to create the IDEAS table (292 bytes, text/x-sql)
2010-11-23 03:36 UTC, slreynolds
Details
zip file containing NetBeans project with source code (16.95 KB, application/zip)
2010-11-23 03:40 UTC, slreynolds
Details
Ideas.java file modified so that it works correctly -- rename to Ideas.java (2.76 KB, text/plain)
2010-11-24 03:35 UTC, slreynolds
Details

Note You need to log in before you can comment on or make changes to this bug.
Description slreynolds 2010-11-23 03:31:30 UTC
Created attachment 103221 [details]
messages log file for completeness

I generated an entity class from a postgres table named IDEAS (table name is all caps). The generated entity class has annotation @Table(name = "IDEAS").

When I try to use the entity class, it fails with this error

run:
hello
[EL Info]: 2010-11-22 21:23:03.649--ServerSession(29524641)--EclipseLink, version: Eclipse Persistence Services - 2.2.0.v20101020-r8375
[EL Info]: 2010-11-22 21:23:04.314--ServerSession(29524641)--file:/home/steven/NetBeansProjects/JPATestApp/src/_JPATestAppPU login successful
Exception in thread "main" Local Exception Stack: 
Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.2.0.v20101020-r8375): org.eclipse.persistence.exceptions.DatabaseException
[EL Warning]: 2010-11-22 21:23:04.462--UnitOfWork(19235919)--Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.2.0.v20101020-r8375): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: org.postgresql.util.PSQLException: ERROR: relation "ideas" does not exist
Error Code: 0
Call: SELECT IDX, CREATION, DESCRIPTION FROM IDEAS
Query: ReadAllQuery(name="Ideas.findAll" referenceClass=Ideas sql="SELECT IDX, CREATION, DESCRIPTION FROM IDEAS")
Internal Exception: org.postgresql.util.PSQLException: ERROR: relation "ideas" does not exist
Error Code: 0
Call: SELECT IDX, CREATION, DESCRIPTION FROM IDEAS
Query: ReadAllQuery(name="Ideas.findAll" referenceClass=Ideas sql="SELECT IDX, CREATION, DESCRIPTION FROM IDEAS")
        at org.eclipse.persistence.exceptions.DatabaseException.sqlException(DatabaseException.java:333)
        at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:683)
        at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeCall(DatabaseAccessor.java:526)
        at org.eclipse.persistence.sessions.server.ServerSession.executeCall(ServerSession.java:530)
        at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:206)
        at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:192)
        at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeSelectCall(DatasourceCallQueryMechanism.java:263)
        at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.selectAllRows(DatasourceCallQueryMechanism.java:644)
        at org.eclipse.persistence.internal.queries.ExpressionQueryMechanism.selectAllRowsFromTable(ExpressionQueryMechanism.java:2558)
        at org.eclipse.persistence.internal.queries.ExpressionQueryMechanism.selectAllRows(ExpressionQueryMechanism.java:2517)
        at org.eclipse.persistence.queries.ReadAllQuery.executeObjectLevelReadQuery(ReadAllQuery.java:410)
        at org.eclipse.persistence.queries.ObjectLevelReadQuery.executeDatabaseQuery(ObjectLevelReadQuery.java:1080)
        at org.eclipse.persistence.queries.DatabaseQuery.execute(DatabaseQuery.java:774)
        at org.eclipse.persistence.queries.ObjectLevelReadQuery.execute(ObjectLevelReadQuery.java:1040)
        at org.eclipse.persistence.queries.ReadAllQuery.execute(ReadAllQuery.java:383)
        at org.eclipse.persistence.queries.ObjectLevelReadQuery.executeInUnitOfWork(ObjectLevelReadQuery.java:1126)
        at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.internalExecuteQuery(UnitOfWorkImpl.java:2943)
        at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1303)
        at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1285)
        at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1259)
        at org.eclipse.persistence.internal.jpa.EJBQueryImpl.executeReadQuery(EJBQueryImpl.java:479)
        at org.eclipse.persistence.internal.jpa.EJBQueryImpl.getResultList(EJBQueryImpl.java:714)
        at mydb.Main.main(Main.java:33)
Caused by: org.postgresql.util.PSQLException: ERROR: relation "ideas" does not exist
        at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1592)
        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1327)
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:192)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:451)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:350)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:254)
        at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeSelect(DatabaseAccessor.java:888)
        at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:598)
        ... 21 more
Java Result: 1
BUILD SUCCESSFUL (total time: 3 seconds)
Comment 1 slreynolds 2010-11-23 03:32:59 UTC
Postgres defaults to table names in lower case which is what the entity is using. The database table name is in all caps, and so this causes an error.
Comment 2 slreynolds 2010-11-23 03:34:17 UTC
Created attachment 103222 [details]
generated entity class Ideas.java
Comment 3 slreynolds 2010-11-23 03:36:22 UTC
Created attachment 103223 [details]
sql commands to create the IDEAS table
Comment 4 slreynolds 2010-11-23 03:38:08 UTC
My postgres version is 

Version string	PostgreSQL 8.4.5 on i486-pc-linux-gnu, compiled by GCC gcc-4.4.real (Ubuntu 4.4.1-4ubuntu9) 4.4.1, 32-bit
Version number	8.4

And my NetBeans is version

Product Version: NetBeans IDE 7.0 Beta (Build 201011152355)
Java: 1.6.0_20; Java HotSpot(TM) Client VM 16.3-b01
System: Linux version 2.6.31-22-generic running on i386; UTF-8; en_US (nb)
Userdir: /home/steven/.netbeans/7.0beta
Comment 5 slreynolds 2010-11-23 03:40:28 UTC
Created attachment 103224 [details]
zip file containing NetBeans project with source code
Comment 6 Sergey Petrov 2010-11-23 09:37:19 UTC
It's unclear what is wrong with generated code, can you fix it manually?
You said your table is upper case and annotation use the same, also is postgres case sensitive?
May it be eclipselink issue?
Comment 7 slreynolds 2010-11-24 03:32:41 UTC
Good questions. I can easily modify the generated Ideas.java to work properly. Here are the diffs needed to make it work:


Satellite> diff -u Ideas.java.asGenerated Ideas.java.modifiedToWork
--- Ideas.java.asGenerated	2010-11-23 21:18:52.426536108 -0600
+++ Ideas.java.modifiedToWork	2010-11-23 21:24:45.478842776 -0600
@@ -23,7 +23,7 @@
  * @author steven
  */
 @Entity
-@Table(name = "IDEAS")
+@Table(name = "\"IDEAS\"")
 @XmlRootElement
 @NamedQueries({
     @NamedQuery(name = "Ideas.findAll", query = "SELECT i FROM Ideas i"),
@@ -34,13 +34,13 @@
     private static final long serialVersionUID = 1L;
     @Id
     @Basic(optional = false)
-    @Column(name = "IDX")
+    @Column(name = "\"IDX\"")
     private Integer idx;
     @Basic(optional = false)
-    @Column(name = "DESCRIPTION")
+    @Column(name = "\"DESCRIPTION\"")
     private String description;
     @Basic(optional = false)
-    @Column(name = "CREATION")
+    @Column(name = "\"CREATION\"")
     @Temporal(TemporalType.TIMESTAMP)
     private Date creation;
 

Running the Main.java in the project then generates this output:

run:
hello
[EL Info]: 2010-11-23 21:24:17.21--ServerSession(29524641)--EclipseLink, version: Eclipse Persistence Services - 2.2.0.v20101020-r8375
[EL Info]: 2010-11-23 21:24:17.848--ServerSession(29524641)--file:/home/steven/NetBeansProjects/JPATestApp/src/_JPATestAppPU login successful
mydb.Ideas[ idx=2 ]
mydb.Ideas[ idx=1 ]
Size: 2
BUILD SUCCESSFUL (total time: 2 seconds)

Also, eclipselink is not the issue. If I connect to the postgres database 
using the psql command line tool, you can see that I must enclose the
table name in quotes in order to sucessfully select from it. Here is the
output from a session with psql showing that:

Satellite> sudo -u postgres psql mydb
psql (8.4.5)
Type "help" for help.

mydb=# select * from IDEAS;
ERROR:  relation "ideas" does not exist
LINE 1: select * from IDEAS;
                      ^
mydb=# select * from "IDEAS";
 IDX |              DESCRIPTION              |           CREATION            
-----+---------------------------------------+-------------------------------
   2 | some other task that we might do      | 2010-10-23 16:02:49.76621-05
   1 | some great task that must not be done | 2010-10-23 16:02:31.229363-05
(2 rows)

It is evident that postgres assumes that table names (and column names) are
lowercase unless they are wrapped with quotes.
Comment 8 slreynolds 2010-11-24 03:35:40 UTC
Created attachment 103265 [details]
Ideas.java file modified so that it works correctly -- rename to Ideas.java
Comment 9 Sergey Petrov 2010-11-24 06:48:49 UTC
it looks  like persistence code will need some knowledge about db specific issues in generation to resolve the issue and unfortunately it may require a lot of code redesign, anyway more evaluation is required, thanks for investigation and the issue.
Comment 10 Sergey Petrov 2010-12-02 11:52:04 UTC
I hope to make some improvement later to remove unnecessary annotations and unnecessary parameters and use default mapping where appropriate.

as from persistence specification
@Column(name = "IDX")
private Integer idx;
should be same as
@Column
private Integer idx;
and even
private Integer idx;

will it work in your case? If it will not work I wonder if it's eclipselink issue, but if will work this improvement may be dome as part of this issue fix.
Comment 11 slreynolds 2010-12-03 03:59:43 UTC
I tried 

    @Column
    private Integer idx; 

and this caused an error

run:
hello
[EL Info]: 2010-12-02 21:44:49.715--ServerSession(17707667)--EclipseLink, version: Eclipse Persistence Services - 2.2.0.v20101020-r8375
[EL Info]: 2010-12-02 21:44:50.433--ServerSession(17707667)--file:/home/steven/NetBeansProjects/TestApp/src/_TestAppPU login successful
[EL Warning]: 2010-12-02 21:44:50.678--UnitOfWork(16001744)--Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.2.0.v20101020-r8375): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: org.postgresql.util.PSQLException: ERROR: column "idx" does not exist
Error Code: 0
Exception in thread "main" Local Exception Stack: 
Call: SELECT IDX, "CREATION", "DESCRIPTION" FROM "IDEAS"
Query: ReadAllQuery(name="Ideas.findAll" referenceClass=Ideas sql="SELECT IDX, "CREATION", "DESCRIPTION" FROM "IDEAS"")
Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.2.0.v20101020-r8375): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: org.postgresql.util.PSQLException: ERROR: column "idx" does not exist
Error Code: 0
Call: SELECT IDX, "CREATION", "DESCRIPTION" FROM "IDEAS"
Query: ReadAllQuery(name="Ideas.findAll" referenceClass=Ideas sql="SELECT IDX, "CREATION", "DESCRIPTION" FROM "IDEAS"")
        at org.eclipse.persistence.exceptions.DatabaseException.sqlException(DatabaseException.java:333)
        at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:683)
        at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeCall(DatabaseAccessor.java:526)
        at org.eclipse.persistence.sessions.server.ServerSession.executeCall(ServerSession.java:530)

The sql statement goes through to postgress without quotes around IDX, and so it fails like before.
Comment 12 Sergey Petrov 2010-12-03 06:06:17 UTC
ok, thanks, looks like a real problem in case of case sensitive db
Comment 13 Sergey Petrov 2012-02-22 20:41:30 UTC
*** Bug 208750 has been marked as a duplicate of this bug. ***
Comment 14 Sergey Petrov 2012-02-22 21:03:13 UTC
looks like eclipselink by default isn't case sensitive
eclipselink.jpa.uppercase-column-names can be set to "false" to make it case sensitive if I got it right.
Comment 16 Sergey Petrov 2012-02-22 21:21:20 UTC
I may need to check disacussions like in https://bugs.eclipse.org/bugs/show_bug.cgi?id=299926 to address this issue later
Comment 17 Vladimir Riha 2012-02-22 21:37:19 UTC
I'm not sure that bug #208750 is a duplicate of this. The bug was about ability to run given sample project.

Comment to the #208750:
Wouldn't be useful to edit the sql dump in PetCatalog so it will contain the exact same lower/upper cases as annotation in java source files? As a result if one will run on case insensitive DB then it won't matter and if one will run it on case sensitive it will fits and also possible to run.
Comment 18 Sergey Petrov 2012-02-22 21:58:56 UTC
ok, after rereading this one I'm not sure too, it need some more evaluation
Comment 19 cvielma 2012-07-23 16:41:27 UTC
(In reply to comment #18)
> ok, after rereading this one I'm not sure too, it need some more evaluation

I just wanted to say that I'm also facing this problem.
Comment 20 Martin Balin 2015-09-17 13:09:51 UTC
Report from old NetBeans version. Due to code changes since it was reported likely not reproducible now. Feel free to reopen if happens in 8.0.2 or 8.1.