0

Its quite easy to use spring data jdbc dealing with CUD actions for entities with many-to-one refs, but the R(eading) is not so straightforward. Although i could use AggregateReference for querying data with ids only, but frontend asks for full data filled. I.e. i have smth like that:

CREATE TABLE TYPE (
   ID   SERIAL NOT NULL,
   NAME VARCHAR NOT NULL,
   PRIMARY KEY (ID)
);

CREATE TABLE RECORD (   
   ID   SERIAL NOT NULL,
   NAME VARCHAR NOT NULL,
   TYPE_ID INTEGER REFERENCES TYPE(ID),
   PRIMARY KEY (ID)
);

And corresponding objects:

@Table
class Type {
    @Id
    Long id;
    String name;
}

@Table
class Record {
    @Id
    Long id;
    String name;
    @Column("type_id")
    AggregateReference<Type, Long> type;
}

I need to provide pageable service for frontend returning records with filled in type id and type name. Smth like this:

[{"id":1,"name":"record1","type":{"id":1,"name":"type1"}},...]

Preferably with paging info provided by Page object (total count, page number and so on). With spring data jdbc repositories all i can get out of box is

[{"id":1,"name":"record1","type":{"id":1}},...]

I could try to write query by hand with @Query in corresponding method within Repository, but i lose paging services. The same goes if i use JDBCTemplate and rowmapper.

I can also try to use mapstruct to map Record to some RecordDTO with type name included, but this requires querying for Type object for every row which seems to be quite ineffective. What is the best way to solve this?

user1032836
  • 55
  • 1
  • 7
  • You are asking about Spring Data JDBC but then you show JPA entity classes. Are you really using JDBC or are you using Spring Data JPA instead? You could use `@ManyToOne` to configure a many-to-one relation with JPA, and then there wouldn't be a need to manually define a query with `@Query`. – Jesper Oct 04 '22 at 09:28
  • @Jesper, its not JPA entity classes. `@Table` and `@Column` are from spring-data-relational, i use them because in real app the table and column names do not match spring data naming conventions. `@Id` is from commons. Those annotations are commonly used in every second spring data jdbc example ;) – user1032836 Oct 04 '22 at 14:39

0 Answers0