Eclipse Community Forums: EclipseLink » DDL Generation & @GenerationType.IDENTITY

Home » Eclipse Projects » EclipseLink » DDL Generation & @GenerationType.IDENTITY(Automatically generated tables doesn't add identity to columns)

Show: Today's Messages :: Show Polls :: Message Navigator

DDL Generation & @GenerationType.IDENTITY [message #1709053]

Wed, 23 September 2015 15:00

Andreas Fagschlunger

Messages: 17
Registered: June 2011

Junior Member

Hy!

I have a Entity which has a combined Primary key like the following

@Entity
@IdClass(MessagePK.class)
public class MessageEntity {

	@Id
	@Column(Name = "CUSTOMER_ID")
	private Integer customerId

	@Id
	@GeneratedValue(strategy = GenerationType.IDENTITY)
	@Column(name = "ID", unique = true, nullable = false, columnDefinition = "INTEGER")
	private Integer id;

So the ID column has an Auto-Increment value in the production database. In Unit-Test we are using a in-Memory derby database and DDL Generation. But the table created doesn't respect the @GeneratedValue Annotation, only a plain Integer column without any Identity/Auto-Increment is defined.

So in Unit-Tests a EntityManager.persist(...) doesn't create a ID value, which causes NullPointerException's.

Is there any way to tell the DDL Generator to add Identity to a column?

Best regards,
Andreas Fagschlunger

Report message to a moderator

Re: DDL Generation & @GenerationType.IDENTITY [message #1709075 is a reply to message #1709053]

Wed, 23 September 2015 18:54

Chris Delahunt

Messages: 1389
Registered: July 2009

Senior Member

What does the DDL statement for the table creation look like on Derby, and what does the insert statement look like? You can get the statements executed by turning logging to FINE or higher as described here: https://wiki.eclipse.org/EclipseLink/Examples/JPA/Logging

You might also want to check that the code is using the DerbyPlatform, and set the "eclipselink.target-database"="Derby" persistence property in testing if not.

[Updated on: Wed, 23 September 2015 18:55]

Report message to a moderator

Re: DDL Generation & @GenerationType.IDENTITY [message #1709103 is a reply to message #1709075]

Thu, 24 September 2015 06:06

Andreas Fagschlunger

Messages: 17
Registered: June 2011

Junior Member

Hello Chris!

Here's the DDL statement for table creation:

CREATE TABLE MESSAGEENTITY (COMPANYID INTEGER, MESSAGEID INTEGER, SUBJECT VARCHAR(56), TEXT VARCHAR(1024), PRIMARY KEY (COMPANYID, MESSAGEID))

And this is what the INSERT Statement Looks like:

[EL Fine]: sql: 2015-09-24 07:50:06.127--ClientSession(1724457619)--Connection(2123960023)--Thread(Thread[main,5,main])--INSERT INTO MESSAGEENTITY (COMPANYID, SUBJECT, TEXT) VALUES (?, ?, ?)
	bind => [1, Lorem ipsum ..., Lorem ipsum dolor sit amet ...]

The outcome is a SQLException:

Caused by: ERROR 23502: Column 'MESSAGEID'  cannot accept a NULL value.

Properties are set as follow:

		properties.put("javax.persistence.provider", "org.eclipse.persistence.jpa.PersistenceProvider");
		properties.put(PersistenceUnitProperties.JDBC_URL, "jdbc:derby:memory:testDB2;create=true");
		properties.put(PersistenceUnitProperties.TRANSACTION_TYPE, "RESOURCE_LOCAL");
		properties.put(PersistenceUnitProperties.DDL_GENERATION, PersistenceUnitProperties.DROP_AND_CREATE);
		properties.put(PersistenceUnitProperties.DDL_GENERATION_MODE, PersistenceUnitProperties.DDL_BOTH_GENERATION);
		properties.put(PersistenceUnitProperties.LOGGING_PARAMETERS, Boolean.TRUE.toString());
		properties.put(PersistenceUnitProperties.TARGET_DATABASE, TargetDatabase.Derby);
		properties.put(PersistenceUnitProperties.LOGGING_LEVEL, SessionLog.FINER_LABEL);

Attached to this post you will find some Java source files to reproduce the Problem.

Best regards,
Andreas Fagschlunger

Attachment: Thread1070531.zip
(Size: 3.61KB, Downloaded 119 times)

Report message to a moderator

Re: DDL Generation & @GenerationType.IDENTITY [message #1709209 is a reply to message #1709103]

Fri, 25 September 2015 00:25

Chris Delahunt

Messages: 1389
Registered: July 2009

Senior Member

I didn't notice the @Id on your customerId mapping as well. Sequencing is used to uniquely identify rows, so you shouldn't use other fields as a composite primary key - it isn't supported. Try removing the @IdClass annotation and @Id from the customerId and see if it works.

Best Regards,
Chris

[Updated on: Fri, 25 September 2015 00:26]

Report message to a moderator

Re: DDL Generation & @GenerationType.IDENTITY [message #1709931 is a reply to message #1709209]

Fri, 02 October 2015 06:21

Andreas Fagschlunger

Messages: 17
Registered: June 2011

Junior Member

Hy Chris!

Sorry I was busy so I could not test until now, it didn't work:

[EL Fine]: sql: 2015-10-02 08:16:35.739--ServerSession(1638172114)--Connection(335708295)--Thread(Thread[main,5,main])--CREATE TABLE MESSAGEENTITY (MESSAGEID INTEGER, COMPANYID INTEGER, SUBJECT VARCHAR(56), TEXT VARCHAR(1024), PRIMARY KEY (MESSAGEID))

Message-ID is now a single Primary key column, but no "AUTO_INCREMENT" etc. is added.

Report message to a moderator

Re: DDL Generation & @GenerationType.IDENTITY [message #1709996 is a reply to message #1709931]

Fri, 02 October 2015 14:20

Chris Delahunt

Messages: 1389
Registered: July 2009

Senior Member

What happens if you remove the columnDefinition = "INTEGER" part from the @Column annotation? I suspect this is overriding any options EclipseLink would have added if it controlled the field definition.

If this is the cause, I may have been wrong about sequencing working on a compound PK. It shouldn't be needed, but might still work.

Best Regards,
Chris

Report message to a moderator

Re: DDL Generation & @GenerationType.IDENTITY [message #1710190 is a reply to message #1709996]

Mon, 05 October 2015 09:01

Andreas Fagschlunger

Messages: 17
Registered: June 2011

Junior Member

Hello Chris!

Yes, removing the columDefinition did the trick:

[EL Fine]: sql: 2015-10-05 10:54:48.159--ServerSession(1638172114)--Connection(335708295)--Thread(Thread[main,5,main])--CREATE TABLE MESSAGEENTITY (MESSAGEID INTEGER GENERATED BY DEFAULT AS IDENTITY NOT NULL, COMPANYID INTEGER, SUBJECT VARCHAR(56), TEXT VARCHAR(1024), PRIMARY KEY (MESSAGEID))

This also answers my original question, adding "GENERATED BY DEFAULT AS IDENTITY NOT NULL" to the columnDefinition creates the column with identity.

	@Id
	@GeneratedValue(strategy = GenerationType.IDENTITY)
	@Column(nullable = false, columnDefinition = "INTEGER GENERATED BY DEFAULT AS IDENTITY NOT NULL")
	private Integer messageId;

Thank you.

Report message to a moderator

Re: DDL Generation & @GenerationType.IDENTITY [message #1717943 is a reply to message #1710190]

Fri, 18 December 2015 00:01

Shakila Rajaiah

Messages: 1
Registered: December 2015

Junior Member

I tried the above solution and it did not work for me. Please help. I am unable to create GenerationType.IDENTITY key for my derby database.

Report message to a moderator

Re: DDL Generation & @GenerationType.IDENTITY [message #1718900 is a reply to message #1717943]

Fri, 01 January 2016 19:04

Chris Delahunt

Messages: 1389
Registered: July 2009

Senior Member

It might be better to open a new question and detail what your issue is. What do you have, what have you tried, and what is the DDL that is generated?

Report message to a moderator

Previous Topic:	Problems building EclipseLink
Next Topic:	ReplicationDriver

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 19 12:06:42 GMT 2024

.:: Contact :: Home ::.

Breadcrumbs

Sign up to our Newsletter