How to print a response from a GET that link two entities/tables?

Question

Hello =] I was tasked with something due to an activity, and don't know how to make it work, hope someone could help :P

I have three entities: 'person', 'paper' and 'property'.

Person id is 'persCod', papers id is 'serialNumber', and properties has both 'persCod' and 'serialNumber' as ids (composite primary keys).

The idea is that a single person (persCod) can have multiple properties, each property being a given paper.

When sending a GET request to find a certain property, I want the response body to contain all that property's info + the name of the 'paper' which serialNumber equals the one from the found property. I need to search the property through its 'persCod' tho.

Person

@Data
@Entity
@Table(name = "PERSON")
@NoArgsConstructor
@AllArgsConstructor
public class Person{
  @Id
  private Long persCod;
  private String name;
}

Paper

@Data
@Entity
@Table(name = "PAPERS")
@NoArgsConstructor
@AllArgsConstructor
public class Paper{
  @Id
  private Integer serialNumber;
  private String paperName;
}

Property

@Data
@Entity
@Table(name = "PROPERTY")
@IdClass(IdProperty.class)
@NoArgsConstructor
@AllArgsConstructor
public class Property {
  @Id
  private Long persCod;
  @Id
  private Integer serialNumber;
  private String paperName;
  private String description;

}

What I need is, when sending a GET request to localhost:8080/property/{persCodA}, to get a response like this:

{
serialNumber: 101010,
paperName: "[the name of the paper which serialNumber also is 101010]",
description: the given description
},
{
serialNumber: 030303,
paperName: "[the name of the paper which serialNumber also is 030303]",
description: the given description
}

Don't know if I managed to explain it well enough, but would be glad to get some guidance =]

Answer 1

• First, all properties that belong to that person are queried via their primary key, persCod.
• Second, then based on this set of properties, the loop queries the paperName of the associated paper via the field serialNumber of each property.
• Third, combine the results of your query and return them.

---

• Option one:

-- Use join query
SELECT pro.serialNumber, pap.paperName, pro.description
FROM propertie pro
    LEFT JOIN pape pap
    ON pro.serialNumber = pap.serialNumber
WHERE pro.persCod = #{persCod};

• Option two:

public List<PropertyDTO> listByPersCode(String persCode) {
    // 1. query property table
    List<PropertyDO> propertyDOs = propertyDAO.selectByPersCode(persCode);

    // 2. query paper table
    List<String> serialNumbers = propertyDOs.stream()
            .map(PropertyDO::getSerialNumber)
            .collect(Collectors.toList());
    List<PaperDO> PaperDOs = paperDAO.selectBatchBySerialNumber(serialNumbers);

    // 3. combine the results
    Map<String, String> map = PaperDOs.stream()
            .collect(Collectors.toMap(PaperDO::getSerialNumber, PaperDO::getPaperName));

    return propertyDOs.stream()
            .map(propertyConverter::toDTO)  // copy object's fields
            .peek(e -> e.setPaperName(map.get(e.getSerialNumber())))
            .collect(Collectors.toList());
}