Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » unidirectional ManyToMany with an order?
unidirectional ManyToMany with an order? [message #540351] Tue, 15 June 2010 14:59 Go to next message
Lars Fischer is currently offline Lars Fischer
Messages: 105
Registered: July 2009
Senior Member
Hello,

I would like to use a unidirectional ManyToMany with an order and don't
know how to do this. My classes look like this:

- A simple class Item, which has no knowledge of groups.
- A class Group, which has an ordered collection of items.
- The order depends not on a field of the items! Every group has a
special order of the items.
- One Item could be contained in many different groups.

I would be very happy, if someone could give me a hint, how to map this.


Kind regards,
Lars
Re: unidirectional ManyToMany with an order? [message #540364 is a reply to message #540351] Tue, 15 June 2010 15:55 Go to previous messageGo to next message
Lars Fischer is currently offline Lars Fischer
Messages: 105
Registered: July 2009
Senior Member
On 15.06.2010 20:59, Lars Fischer wrote:
>
> I would like to use a unidirectional ManyToMany with an order and don't
> know how to do this. My classes look like this:
>
> - A simple class Item, which has no knowledge of groups.
> - A class Group, which has an ordered collection of items.
> - The order depends not on a field of the items! Every group has a
> special order of the items.
> - One Item could be contained in many different groups.
>
> I would be very happy, if someone could give me a hint, how to map this.

I tried to use this in the Group class:

@ManyToMany
@JoinTable(name = "GROUP_ITEM", joinColumns = @JoinColumn(name =
"GROUP_ID"), inverseJoinColumns = @JoinColumn(name = "ITEM_ID"))
@MapKeyColumn(name = "ORDER")
private Map<Integer, Item> items;

But eclipselink throws an error when creating the table GROUP_ITEM:


[EL Fine]: Connection(24548764)--CREATE TABLE GROUP_ITEM (GROUP_ID
BIGINT NOT NULL, ITEM_ID BIGINT NOT NULL, ORDER VARCHAR, PRIMARY KEY
(GROUP_ID, ITEM_ID))
[EL Fine]: SELECT 1
[EL Warning]: Exception [EclipseLink-4002] (Eclipse Persistence Services
- 2.0.1.v20100213-r6600):
org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: org.h2.jdbc.JdbcSQLException: Syntax Fehler in SQL
Befehl "CREATE TABLE GROUP_ITEM (GROUP_ID BIGINT NOT NULL, ITEM_ID
BIGINT NOT NULL, ORDER[*] VARCHAR, PRIMARY KEY (GROUP_ID, ITEM_ID))";
erwartet "identifier"
Syntax error in SQL statement "CREATE TABLE GROUP_ITEM (GROUP_ID BIGINT
NOT NULL, ITEM_ID BIGINT NOT NULL, ORDER[*] VARCHAR, PRIMARY KEY
(GROUP_ID, ITEM_ID))"; expected "identifier"; SQL statement:
CREATE TABLE GROUP_ITEM (GROUP_ID BIGINT NOT NULL, ITEM_ID BIGINT NOT
NULL, ORDER VARCHAR, PRIMARY KEY (GROUP_ID, ITEM_ID)) [42001-133]
at org.h2.message.DbException.getJdbcSQLException(DbException.j ava:316)
at org.h2.message.DbException.get(DbException.java:167)
at org.h2.message.DbException.getSyntaxError(DbException.java:1 92)
at org.h2.command.Parser.readColumnIdentifier(Parser.java:2538)
at org.h2.command.Parser.parseCreateTable(Parser.java:4718)
at org.h2.command.Parser.parseCreate(Parser.java:3516)
at org.h2.command.Parser.parsePrepared(Parser.java:314)
at org.h2.command.Parser.parse(Parser.java:278)
at org.h2.command.Parser.parse(Parser.java:250)
at org.h2.command.Parser.prepareCommand(Parser.java:222)
at org.h2.engine.Session.prepareLocal(Session.java:420)
at org.h2.server.TcpServerThread.process(TcpServerThread.java:2 23)
at org.h2.server.TcpServerThread.run(TcpServerThread.java:134)
at java.lang.Thread.run(Thread.java:619)

Error Code: 42001
Call: CREATE TABLE GROUP_ITEM (GROUP_ID BIGINT NOT NULL, ITEM_ID BIGINT
NOT NULL, ORDER VARCHAR, PRIMARY KEY (GROUP_ID, ITEM_ID))
Query: DataModifyQuery(sql="CREATE TABLE GROUP_ITEM (GROUP_ID BIGINT NOT
NULL, ITEM_ID BIGINT NOT NULL, ORDER VARCHAR, PRIMARY KEY (GROUP_ID,
ITEM_ID))")

What is wrong with my mapping? Can I only use String as MapKey?

Regards,
Lars
Re: unidirectional ManyToMany with an order? [message #540370 is a reply to message #540364] Tue, 15 June 2010 16:23 Go to previous messageGo to next message
Lars Fischer is currently offline Lars Fischer
Messages: 105
Registered: July 2009
Senior Member
On 15.06.2010 21:55, Lars Fischer wrote:
>
> @ManyToMany
> @JoinTable(name = "GROUP_ITEM", joinColumns = @JoinColumn(name =
> "GROUP_ID"), inverseJoinColumns = @JoinColumn(name = "ITEM_ID"))
> @MapKeyColumn(name = "ORDER")
> private Map<Integer, Item> items;

Without overriding the MapKeyColumn, this is working fine.

Is this a bug on @MapKeyColumn?

Regards,
Lars
Re: unidirectional ManyToMany with an order? [message #540515 is a reply to message #540370] Wed, 16 June 2010 08:46 Go to previous messageGo to next message
Chris Delahunt is currently offline Chris Delahunt
Messages: 1016
Registered: July 2009
Senior Member
Hello,

I don't know what database you are using, but the problem seems to be at the database level when using a column "ORDER". Try a different column name as it is likely a reserved word in your database.

Best Regards,
Chris
Re: unidirectional ManyToMany with an order? [message #540993 is a reply to message #540515] Thu, 17 June 2010 17:44 Go to previous messageGo to next message
Lars Fischer is currently offline Lars Fischer
Messages: 105
Registered: July 2009
Senior Member
Hello Cris,

On 16.06.2010 14:46, Chris Delahunt wrote:
>
> I don't know what database you are using, but the problem seems to be at
> the database level when using a column "ORDER". Try a different column
> name as it is likely a reserved word in your database.

ORDER is a reserved word. I changed it and now the table could be created.

But now I run into the next problem:
If the map with the items will be loaded, I get this exception:

[EL Warning]: Exception [EclipseLink-6069] (Eclipse Persistence Services
- 2.0.2.v20100323-r6872): org.eclipse.persistence.exceptions.QueryException
Exception Description: The field [GROUP.SORT] in this expression has an
invalid table in this context.
Query: ReadAllQuery(referenceClass=Group sql="SELECT GROUP_ID,
GROUP_IDENT, GROUP_ENTRYDATE, GROUP_ACTSTATUS, GROUP_COMMENT,
GROUP_EDITDATE, GROUP_PROJECTID, GROUP_SUPERVISOR_USERID,
GROUP_EDITOR_USERID FROM GROUP")
[UIThread [1g3hqsofn5qa9q4ozsd2xiha2]] DEBUG
org.springframework.orm.jpa.JpaTransactionManager - Participating
transaction failed - marking existing transaction as rollback-only
[UIThread [1g3hqsofn5qa9q4ozsd2xiha2]] DEBUG
org.springframework.orm.jpa.JpaTransactionManager - Setting JPA
transaction on EntityManager
[org.eclipse.persistence.internal.jpa.EntityManagerImpl@1dfacc4]
rollback-only
[UIThread [1g3hqsofn5qa9q4ozsd2xiha2]] DEBUG
org.springframework.orm.jpa.JpaTransactionManager - Initiating
transaction rollback
[UIThread [1g3hqsofn5qa9q4ozsd2xiha2]] DEBUG
org.springframework.orm.jpa.JpaTransactionManager - Rolling back JPA
transaction on EntityManager
[org.eclipse.persistence.internal.jpa.EntityManagerImpl@1dfacc4]
[UIThread [1g3hqsofn5qa9q4ozsd2xiha2]] DEBUG
org.springframework.orm.jpa.JpaTransactionManager - Closing JPA
EntityManager
[org.eclipse.persistence.internal.jpa.EntityManagerImpl@1dfacc4] after
transaction
[UIThread [1g3hqsofn5qa9q4ozsd2xiha2]] DEBUG
org.springframework.orm.jpa.EntityManagerFactoryUtils - Closing JPA
EntityManager
Local Exception Stack:
Exception [EclipseLink-6069] (Eclipse Persistence Services -
2.0.2.v20100323-r6872): org.eclipse.persistence.exceptions.QueryException
Exception Description: The field [GROUP.SORT] in this expression has an
invalid table in this context.
Query: ReadAllQuery(referenceClass=Group sql="SELECT GROUP_ID,
GROUP_IDENT, GROUP_ENTRYDATE, GROUP_ACTSTATUS, GROUP_COMMENT,
GROUP_EDITDATE, GROUP_PROJECTID, GROUP_SUPERVISOR_USERID,
GROUP_EDITOR_USERID FROM GROUP")
at
org.eclipse.persistence.exceptions.QueryException.invalidTab leForFieldInExpression(QueryException.java:690)



The mapping now looks like this:

@ManyToMany(fetch = FetchType.EAGER)
@JoinTable(name = "GROUP_ITEM", joinColumns = @JoinColumn(name =
"GROUP_ID"), inverseJoinColumns = @JoinColumn(name = "ITEM_ID"))
@MapKeyColumn(name = "SORT")
private Map<Integer, Item> items;



Regards,
Lars
Re: unidirectional ManyToMany with an order? [message #541152 is a reply to message #540993] Fri, 18 June 2010 09:24 Go to previous messageGo to next message
Chris Delahunt is currently offline Chris Delahunt
Messages: 1016
Registered: July 2009
Senior Member
Hello,

Looks like it maybe a bug. Can you try setting the table in the MapKeyColumn annotation? Please also send the full stack trace, as it helps determine why the error is being thrown.

Best Regards,
Chris

[Updated on: Fri, 18 June 2010 09:25]

Report message to a moderator

Re: unidirectional ManyToMany with an order? [message #541268 is a reply to message #541152] Sat, 19 June 2010 04:32 Go to previous messageGo to next message
Lars Fischer is currently offline Lars Fischer
Messages: 105
Registered: July 2009
Senior Member
Hello Chris,

On 18.06.2010 15:24, Chris Delahunt wrote:
>
> Looks like it maybe a bug. Can you try setting the table in the
> MapKeyColumn annotation?
>

This solves the error. Thank you very much for this hint!

My mapping now looks like this:

@ManyToMany(fetch = FetchType.EAGER)
@JoinTable(name = "GROUP_ITEM", joinColumns = @JoinColumn(name =
"GROUP_ID"), inverseJoinColumns = @JoinColumn(name = "ITEM_ID"))
@MapKeyColumn(name = "SORT", table = "GROUP_ITEM")
private Map<Integer, Item> items;


But is the setting of the table needed? I found a description for my
mapping problem in a book. The shown example in the book does not use
the table setting in the MapKeyColumn annotation.
Could someone confirm that this would be a bug? Then I will file a new
entry in bugzilla.

Kind regards,
Lars
Re: unidirectional ManyToMany with an order? [message #555363 is a reply to message #541268] Thu, 26 August 2010 08:25 Go to previous messageGo to next message
WJ  is currently offline WJ
Messages: 2
Registered: August 2010
Junior Member
I have another problem with such mapping.
If you check sql ddl you can find that primary key for join table is composite comprised owner_id and mapvalue_id.
This leads to situation you can insert duplicate map keys into table.
In my opinion primary key for such table should be owner_id and map_key column.
Re: unidirectional ManyToMany with an order? [message #555424 is a reply to message #540351] Thu, 26 August 2010 10:34 Go to previous message
James Sutherland is currently offline James Sutherland
Messages: 1939
Registered: July 2009
Location: Ottawa, Canada
Senior Member

Please log a bug for this DDL generation issue.


James : Wiki : Book : Blog : Twitter
Previous Topic:Oracle Outer Join syntax
Next Topic:Two questions
Goto Forum:
  


Current Time: Tue Jul 29 04:55:39 EDT 2014

Powered by FUDForum. Page generated in 0.20148 seconds