• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Ron McLeod
  • Paul Clapham
  • Jeanne Boyarsky
  • Bear Bibeault
Sheriffs:
  • Rob Spoor
  • Henry Wong
  • Liutauras Vilda
Saloon Keepers:
  • Tim Moores
  • Carey Brown
  • Stephan van Hulst
  • Tim Holloway
  • Piet Souris
Bartenders:
  • Frits Walraven
  • Himai Minh
  • Jj Roberts

What kind of Spring Data JPA relationship is this?

 
Ranch Hand
Posts: 442
3
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I'm creating an inventory management website application.   There is a warehouse, and inside the warehouse there are bins, and inside the bins there are items.   Inside the bins there can be multiple of the same item.  For example if you are storing toothpaste, you can have 2 toothpaste in one bin, and toothpaste in another bin.   I'm trying to create something where a user can look up an item and find out all of its locations.     This doesn't seem to fit a one to many, because if you put an item in one bin, it won't let you put it in another bin if you have many of the same item.   Is it a many to many?   If so, how do I make it so that if I put one container of toothpaste in bin 12, I can put another one in bin 12 without it causing an error due to the duplicate mapping?  
 
Marshal
Posts: 26458
81
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
It sounds like you have a table which says, for example, "Bin 49 contains 3 cases of Snickers". Wikipedia calls this an associative table, or alternatively a join table or a junction table or a cross-reference table. Sorry, I don't know what Spring calls this but hopefully it uses one of those four names.
 
Sheriff
Posts: 22116
114
Eclipse IDE Spring VI Editor Chrome Java Windows
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
It's a many-to-many, but with an additional column in the join table - the count. The table would have three columns: a foreign key to the bin, a foreign key to the item, and the count. The two foreign keys combined form the primary key.

I don't think this can be done in JPA using a @ManyToMany, because of the additional column. Normally I'd probably go for an @ElementCollection in the bin table, but because you want the relation to be bidirectional (so you can look up bins for an item), you'll have to make it an entity. You'll end up with three entities:

1. Bin. It has a @OneToMany relation to the join table entity.
2. Item. It has a @OneToMany relation to the join table entity.
3. The join table entity. It has @ManyToOne relations to both Bin and Item. You'll need to use @MapsId twice to share the columns for the primary key with the relation fields.

The MapsId documentation (see the link) shows how you can set up the columns in the join table. You'll just have to do it twice, one for each relation.
 
Saloon Keeper
Posts: 23409
159
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Actually, I think you can define a join table with extra fields in it.

What I fiound frustrating about doing many-many in JPA was that - and memory is fuzzy here - JPA was favoring working with a join table that it automatically named itself, as well as naming the join fields and the names didn't fit the standards of the legacy database. So I had to learn how to explicitly set all that up.

Incidentally, SQL may not be the best approach in cases where the relationships between entities are more important that the attributes of the entities. For cases like that, a graph database like Neo4J might be preferable. There is a Spring Data module that supports it. Although the query language can look pretty strange relative to SQL.
 
Rob Spoor
Sheriff
Posts: 22116
114
Eclipse IDE Spring VI Editor Chrome Java Windows
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Tim Holloway wrote:Actually, I think you can define a join table with extra fields in it.


Then would that be done with a @ManyToMany on a Map? The key would then be the entity, the value the single column or an embeddable.

What I fiound frustrating about doing many-many in JPA was that - and memory is fuzzy here - JPA was favoring working with a join table that it automatically named itself, as well as naming the join fields and the names didn't fit the standards of the legacy database. So I had to learn how to explicitly set all that up.


True, the default (if I recall correctly) is <OwnerTable>_<OtherTable> and <ownerId> / <otherId>. @JoinTable can be used to tweak that.

Incidentally, SQL may not be the best approach in cases where the relationships between entities are more important that the attributes of the entities. For cases like that, a graph database like Neo4J might be preferable.


Agreed. People are used to SQL, but there are situations where fitting models in SQL is just not the correct way.
 
Tim Holloway
Saloon Keeper
Posts: 23409
159
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I'm pretty sure I have an example of what I'm proposing in a project somewhere, but alas, I cannot easily locate it. Nor, unfortunately, my venerable aPress JPA2 reference, which might illustrate such a concept.

The primary problem as I noww recall isn't so much the name of the join table - which can be overridden on the annotation - but on its definition and maintenance. A basic join table requires no application logic because JSF understands everything that should be done with it and does it automatically.

But I think that you can define your join table as @Entity and thereby make it available for additional payloads and operations. Although I'm unsure as to whether doing so would be blessed in all JPA versions and implementations. It's possible that being both an @Entity and a @Jointable might offend some implementations.

Obviously you can define an ad hoc manual Join Table as an Entity by simply making its primary key be the concatenation of the two (or more) foreign keys that reference a row in the table and doing many-to-one references from the tables that join on it. You don't get the full automation of a native @JoinTable, but it does give the join attributes.
 
I'd appreciate it if you pronounced my name correctly. Pinhead, with a silent "H". Petite ad:
SKIP - a book about connecting industrious people with elderly land owners
https://coderanch.com/t/skip-book
reply
    Bookmark Topic Watch Topic
  • New Topic