Crudrepository / Join single field from other table to entity as readonly

Question

Bear with me for any mistakes I make, as this is my first question here.

I have a database with two tables, one table called: PERSON

with the following entity:

@Entity
class Person {
    @Id
    private String guid;
    private String firstName;
    private String organisationGuid;
    ...
    ...
}

And one table called: ORGANISATION

with the following entity:

@Entity
class Organisation {
    @Id
    private String guid;
    private String name;
    ...
    ...
}

As you can see, every Person belongs to an Organisation.

And now I need to list all my persons, with the name of the organisation. I do not want the full Organisation-entity on the Person-entity, rather just the name. Like so:

[{
    "guid": "xxx",
    "firstName": "Name",
    "organisationGuid": "yyy",
    "organisationName": "Name of yyy"
}]

How can I accomplish this in the easiest way possible?

Things I have already tried:

1) Adding property to Person and modyfing select-statement

@Entity
class Person {
    @Id
    private String guid;
    private String firstName;
    private String organisationGuid;
    private String organisationName;
    ...
    ...
}

--

@Repository
public interface PersonRepository extends CrudRepository<Person, String> {
    @Query(nativeQuery = true, value = "select p.*, o.name as organisation_name from person p left join organisation o on p.organisation_guid = o.guid")
    List<Person> findAll();
}

Result: This works fine when using findAll but as soon as I try to save a Person I get an error stating that column ORGANISATION_NAME does not exist.

2) OK, makes sense, so I tried to put @Transient on the field in the entity

@Entity
class Person {
    ...
    @Transient
    private String organisationName;
    ...
    ...
}

Result: Now it works to save the entity, but I never get the organisationName (as it is marked as Transient).

3) Well damn, then I try to use the annotation @ReadOnlyProperty

@Entity
class Person {
    ...
    @ReadOnlyProperty
    private String organisationName;
    ...
    ...
}

Result: Same error as in (1). I can findAll but as soon as I try to save a person entity hibernate reports that the column does not exist (because in order to save an item, hibernate first needs to select it, and this particular select does NOT use my own custom select I created in the repository).

4) So then I created a class called PersonOrganisation (with @Table(name="organisation")) with a @ManyToOne-relation from Person to PersonOrganisation, where PersonOrganisation is an entity with just two fields, guid and name.

Result: Error. I can findAll but as soon as I try to save a person entity hibernate reports that the organisationGuid does not match a PersonOrganisation in the database (as it seems that PersonOrganisation is not an Organisation the way Hibernate sees it).

Answer 1

Many things that can be improved here:

1) Add a relationship in the Person relating to the Organization:

@Entity
class Person {
  @Id
  private String guid;
  private String firstName;

  @ManyToOne
  @JoinColumn(name = "organisationGuid")
  private Organisation organisation;

2) create a Result Class which would be holding the projection results:

package com.mypkg;

@Entity
class PersonOrganization {
    private String guid;
    private String firstName;
    private String organisationGuid;
    private String organisationName;

    public PersonOrganization(String guid, String firstName
               , String organisationGuid, String organisationName){
        // set the fields
    } 
}

3) Change the query (dont use native.. its not necessary):

@Repository
public interface PersonRepository extends CrudRepository<Person, String> {
    @Query("select NEW com.mypkg.PersonOrganization(p.guid as guid ...
                , o.name as organisationName) 
            from person p left join p.organisation o")
    List<PersonOrganization> findPersonWithOrganization();
}

Remeber to add aliases to each result column to match the consturctor of the PersonOrganization class.

Answer 2

I started using the solution from Maciej above, but ran into problems when I didn't want to create an extra "projection" entity with the same fields as the original entity (there were 20 additional fields the on Person entity).

So I actually found another solution which I am very happy with.

1) I started out with adding a @ManyToOne in Person (like Maciej suggested)

@Entity
public class Person {
    @Id
    private String guid;
    private String firstName;
    @ManyToOne
    private Organisation organisation;
    ...
    ...
}

2) I also added a custom serializer to the get-method for the Organisation on the Person entity:

@JsonSerialize(using = OrganisationLightSerializer.class)
public Organisation getOrganisation() {
    return organisation;
}

The custom serializer is super simple:

public class OrganisationLightSerializer extends JsonSerializer<Organisation> {
@Override
    public void serialize(Organisation organisation, JsonGenerator jsonGenerator, SerializerProvider serializerProvider)
        throws IOException, JsonProcessingException {
        jsonGenerator.writeStartObject();
        jsonGenerator.writeStringField("name", organisation.getName());
        jsonGenerator.writeEndObject();
    }
}

3) Then I changed all find-queries in my repository and added join fetch, and with this I stopped hibernate from using (at least) two SQL-queries when fetching a list of Persons:

@Repository
public interface PersonRepository extends CrudRepository<Person, String> {
    @Override
    @Query("select p from Person p join fetch p.organisation o")
    List<Person> findAll();
}

Result: I did not get the JSON-structure exactly the way I wanted it, but I managed to get just the pertinent information from each Organisation on each Person. The resulting JSON when fetching persons looks like so (as you can see I skipped the guid of the Organisation in the end, but it can easily be added again by just changing the custom serializer):

[{
    "guid": "xxx",
    "firstName": "Name",
    "organisation": { name: "Name of yyy"}
}]

Disclaimer: Now I realize that my response here is not an exact answer to my own query, as I stated I wanted the JSON as a flat structure, but having an Organisation object in the JSON, which only contains the name of the Organisation is almost as good a solution. Should I edit/change/comment the question so that it reflects the actual answer here, or is this minor change an "acceptable" deviation of the requirements.