JPA failed to automatically generate a table from an entity

Question

I am using Eclipselink with Derby database to automatically generated a database from Entities.

The generation worked just fine at first, but when i added a User entity to my model and tried to generate tables from entities with JPA tools all the tables are generated except the User table & i get the following error during the generation

[EL Warning]: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.6.1.v20150605-31e8258): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: java.sql.SQLSyntaxErrorException: Syntax error: Encountered "USER" at line 1, column 14.
Error Code: 30000

This is the user entity

@NamedQuery( name = "User.findByUsername", query = "SELECT u FROM User u WHERE u.username = :userneme AND u.password = :password" )
@Entity
public class User implements Serializable {

    @Transient
    private static final long serialVersionUID = 1L;

    public User() {
        super();
    }

    @Id
    @GeneratedValue( strategy = GenerationType.AUTO )
    private Integer id;

    private String  username;
    private String  password;

    public String getUsername() {
        return username;
    }

    public void setUsername( String username ) {
        this.username = username;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword( String password ) {
        this.password = password;
    }

Can anyone tell me why i am getting the exception ?

Thanks in advance

score 2 · Accepted Answer · answered Sep 24 '16 at 15:29

2

Your table name is "USER" which is a reserved keyword in SQL (and in Apache Derby). Some JPA providers (e.g DataNucleus JPA) automatically quote such keywords for you meaning it would just work. Others (e.g EclipseLink) don't and so you have to explicitly set the table name with surrounding quote marks (') or set the table name to something that is not a SQL keyword

@Table(name="'User'")
public class User {...}

answered Sep 24 '16 at 15:29

Neil Stockton

11,383
3
34
29

It doesn't work by adding the quote marks .. it did by changing the name completely .. Thanks for the reply – safdoescode Sep 24 '16 at 15:44
AFAIK, you have to use either double quotes - `@Table(name="\"User\"")` - or backticks - `@Table(name="\`User\`")`, but not single quotes. – Hans Nov 21 '22 at 13:54

Ramachandran.A.G · Answer 2 · 2016-09-24T15:47:00.347

1

This most likely is because USER is a function in the Derby Database (Refer this : http://db.apache.org/derby/docs/10.10/ref/rrefsqlj42476.html). You possibly can have the JPA bean as User , but specify a different table name using @Table annotation

edited Sep 24 '16 at 15:47

answered Sep 24 '16 at 15:28

Ramachandran.A.G

4,788
1
12
24

I am using Derby not H2 database but it worked with Derby to .. Thank you – safdoescode Sep 24 '16 at 15:45

JPA failed to automatically generate a table from an entity

2 Answers2