CODEWITHSUNDEEP
spring-bootkotlinjpaormrelational-database
ConfusedUser
ConfusedUser

Reputation: 11

How to merge data from different tables into one entity with Kotlin and JPA?

I'm new to the Kotlin world and I've been trying to figure out what would be the best way to represent the data fetched from 3 different tables in the database (2 out of 3 at a time), ideally as an entity with flat hierarchy, using JPA (or anything else that makes sense, really).

Table structure:

asset

asset_id INT other...

ownership

ownership_id INT owner_id INT owned_id INT owner_type VARCHAR owned_type VARCHAR other...

owner_business

owner_business_id INT other...

owner_individual

owner_individual_id INT other...

Logic:

An asset is linked to the ownership table based on asset.asset_id <-> ownership.owned_id reference, then the ownership.owner_id can reference either owner_business.owner_business_id or owner_individual.owner_individual_id and needs to be determined based on ownership.owner_type (either 'BUSINESS' or 'INDIVIDUAL').

One asset can belong to many owner_business and/or owner_individual entities, which would be represented by multiple records in the ownership table with a common owned_id.

Finally, this query logic should only show results where ownership.owned_type equals 'ASSET'.

Goal:

JSON structure representing the owner data as a single object to be retrieved/updated via an API:

{
   "assetId":111,
   "otherFromAssetTable":"...",
   "owners":[
      {
         "ownershipId":222,
         "ownerId":333,
         "ownedId":111,
         "ownerType":"BUSINESS",
         "otherFromOwnerBusinessTable":"..."
      },
      {
         "ownershipId":223,
         "ownerId":334,
         "ownedId":111,
         "ownerType":"INDIVIDUAL",
         "otherFromOwnerIndividualTable":"..."
      }
   ]
}

I did some research on this and tried experimenting with @Entity class inheritance, different @Inheritance strategies, secondary tables, mapped superclasses, etc. with everything ending up either not (fully) working or not giving me the results I need. I'm aware now that ultimately I should probably transform the JPA entity into a DTO and only then serve it as a nicely formatted API response, but otherwise I feel clueless and I'm reaching peak frustration over this.

Can someone think of a reasonable way to solve this?

Upvotes: 1

Views: 40

Answers (0)

Related Questions