JPA EclipseLink SEQUENCE table does not exist

[Lucas Smith]

Hi,

I want to save Customer entity in database (Derby):

@Entity
public class Customer implements Serializable {
	@Id
    @GeneratedValue(strategy=GenerationType.AUTO)  
	private Long id;
	
	private String name;
	private static final long serialVersionUID = 1L;

	public Customer() {
		super();
	}   
	public Long getId() {
		return this.id;
	}

	public void setId(Long id) {
		this.id = id;
	}   
	public String getName() {
		return this.name;
	}

	public void setName(String name) {
		this.name = name;
	}
   
}

I get reference to EntityManager and then I invoke:
manager.persist(new Customer());

However I get error:

Internal Exception: java.sql.SQLSyntaxErrorException: Table/view 'SEQUENCE' does not exist.
Call: UPDATE SEQUENCE SET SEQ_COUNT = SEQ_COUNT + ? WHERE SEQ_NAME = ?
bind => [2 parameters bound]


However SEQUENCE table is in my database. It was created automatically when I created tables from entities:
[EL Fine]: Connection(32280137)--CREATE TABLE CUSTOMER (ID BIGINT NOT NULL, NAME VARCHAR(255), PRIMARY KEY (ID))
[EL Fine]: Connection(32280137)--CREATE TABLE SEQUENCE (SEQ_NAME VARCHAR(50) NOT NULL, SEQ_COUNT DECIMAL(15), PRIMARY KEY (SEQ_NAME))
[EL Fine]: Connection(32280137)--DELETE FROM SEQUENCE WHERE SEQ_NAME = SEQ_GEN
[EL Fine]: Connection(32280137)--SELECT * FROM SEQUENCE WHERE SEQ_NAME = SEQ_GEN
[EL Fine]: Connection(32280137)--INSERT INTO SEQUENCE(SEQ_NAME, SEQ_COUNT) values (SEQ_GEN, 0)


I do not have any idea. I want to cry (((.

[Vijitha Kumara]

Have you ran the schema generation once before running the actual code (link)?

[Lucas Smith]

The problem actually was with that I searched wrong database. I did not specify: <jta-data-source>...</jta-data-source> element in persistence.xml and default database was chosen.

[James Boswell]

Lucas

When you say the default database, what do you mean?

[Lucas Smith]

I mean that 'sun-appserv-samples' was chosen. I use Eclipse with Glassfish and Derby. That database was created automatically.

Can someone give me a clue why:

@GeneratedValue(strategy=GenerationType.AUTO)

works flawlessly whereas:

@GeneratedValue(strategy=GenerationType.SEQUENCE)

does not work. It gives exception that ID cannot have NULL value.

[James Boswell]

Using GenerationType.SEQUENCE means you wish the ID to be generated from a sequence which is held as a database object. I'm assuming you ate getting null because you haven't specified which sequence to use.

[Lucas Smith]

OK, I have solved the problem.

After changing GenerationType in the entity it is necessary to regenerate tables. I use JPA Tools -> Generate tables from entities... from the context menu of JPA Project.

Can someone tell me what is the purpose of <property name="eclipselink.ddl-generation" value="none"/> element in persistence.xml?

By the way:
* adding <property name="eclipselink.logging.level" value="FINE"/> to persistence.xml is very useful while resolving strange issues like "JPA EclipseLink SEQUENCE table does not exist".

[James Boswell]

I think that eclipselink.ddl-generation is similar to Hibernate's hbm2ddl.auto property. It basically allows you to configure how eclipselink manages your database schema.

For example, if you wanted eclipselink to create the database schema, you would use:

<property name="eclipselink.ddl-generation" value="drop-and-create-tables"/>

[Lucas Smith]

OK, but when such creation occurs? During deployment?