1

I'm getting a large stack trace which at its root is caused by: java.sql.SQLSyntaxErrorException: Table/View 'U' does not exist. Followed by an exception for everything that requires the entity: java.sql.SQLTransactionRollbackException: Constraint 'PSSVSERSRDTABASEID' is invalid: referenced table U does not exist.

I looked at this question and answer about a similar issue but it doesn't seem to address my particular problem. The reason being is when I open Database Development mode in Eclipse, I can see every other entity being mapped to a table, yet, not the entity User('U').

The User entity:

@Entity
@Inheritance(strategy = InheritanceType.JOINED)
@DiscriminatorColumn(name = "disc", discriminatorType = DiscriminatorType.STRING)
@Table(name = "U")
@NamedQuery(name = "FIND_WITH_USER_ID", query="SELECT DISTINCT u FROM User u WHERE u.userId = :userId")
public class User implements Serializable{
    private static final long serialVersionUID = 2468889149889625824L;
    @Id @GeneratedValue
    protected long databaseId;

    //getters/setters/etc

}

User does have a subclass (which shows up in the table schema):

@Entity
public class Customer extends User{
    private static final long serialVersionUID = -5239293307816318553L;

    public Customer(){
        super();
    } 

    //getters/setters/etc

}

In my persistence.xml file, I do specify for the tables to be created if they don't already exist:

<properties>
        <property name="javax.persistence.jdbc.driver" value="C:\Program Files\GlassFish\glassfish-4.1\glassfish4\javadb\lib\derby.jar"></property>
        <property name="javax.persistence.jdbc.url" value="jdbc:derby://localhost:1527/AppDB;create=true"></property>
        <property name="javax.persistence.schema-generation.database.action" value="drop-and-create"/>
</properties>

As stated, other tables are shown in the database schema (and pings to the database succeed). The problem seems to form around the User entity. I thought it might have had something to do with reserved keyword name clashes, so I changed the name of the table a few times (there seems to be a problem with Eclipse/Glassfish once I encounter this exception, even if I make changes, such as the name, and relaunch the app it doesn't update; out of scope of question I believe).

Why isn't a table being created for entity User? What am I missing? Why are the other tables being created still?

Community
  • 1
  • 1
chRyNaN
  • 3,592
  • 5
  • 46
  • 74
  • The word "user" is a reserved word in ANSI Standard SQL and so to use it with Derby you have to enclose it in double quotes (a.k.a. delimited identifiers). Perhaps your object/relational tool is not completely quoting the word User when issuing SQL to Derby. Can you use a different name than "user"? – Bryan Pendleton Apr 03 '15 at 00:49
  • @Tiny obviously the table is not present! My question was: **Why isn't a table being created for entity User?**. The entity name is User, the corresponding table is named U. I have configured it to create the tables on start-up (persistence.xml file). All other tables are created (stated in question) except for the User (table name 'U'). – chRyNaN Apr 03 '15 at 04:11
  • @BryanPendleton yes I know the word "user" is a reserved word, hence the `@table(name = "U") annotation`. This changes the table name so the persistence provider will map the entity to table U. The class name User should have nothing to do with the mapping since I specified the `table` annotation. I believe my error lies somewhere else with the persistence provider. I'm just not sure where. – chRyNaN Apr 03 '15 at 04:22
  • What if you replace `` with `` ? – wypieprz Apr 03 '15 at 09:55
  • Run your Derby with '-Dderby.language.logStatementText=true', and then look in your derby.log file to see what SQL statements are being issued to Derby, and what errors are being returned. – Bryan Pendleton Apr 03 '15 at 13:55
  • @wypieprz I've since added the `` line to my persistence file, unfortunately, with no effect. – chRyNaN Apr 03 '15 at 17:20
  • @BryanPendleton I will give the 'logStatementText=true' a try. Do you know of anyway I can use the command with Eclipse? Or is it only possible via the command line? – chRyNaN Apr 03 '15 at 17:22

1 Answers1

-1

Problem is with you SQL script, you are passing the table alias name instead of column name. it should be u.

SELECT DISTINCT e.emp_id FROM Emp e

sandeep verma
  • 149
  • 6
  • Can you elaborate? Are you speaking about the NamedQuery I have specified on the Entity? If so, it's a valid JPQL statement. – chRyNaN Apr 03 '15 at 00:21
  • I have faced the same problem and the reason was alias of the table name. In you case you have defined it as ":U" try this SELECT DISTINCT u FROM User as u WHERE u.userId = :userId – sandeep verma Apr 03 '15 at 00:43
  • It has nothing to do with the query statement. I receive the error on application start-up. The @NamedQuery is there for later use if I ever want to get a user instance and is correct the way it is. – chRyNaN Apr 03 '15 at 04:08
  • Also, in JPQL, the as clause is optional, so, your statement is exactly the same as my NamedQuery statement (which, once again, is not the cause of the problem). – chRyNaN Apr 03 '15 at 04:16