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

ownership

owner_business

owner_individual

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?