CODEWITHSUNDEEP
sqlpostgresqlspring-boothibernatejpa
sai
sai

Reputation: 27

Map a Normalized Postgres View to Entity | SringBoot

I have multiple tables with one to many relationships. for example:

Document
- id
- filename
- status

Field ( relation b/w document & field is 1 to many )
- id
- type
- fk_document_id -> Document::id

FieldValue ( relation b/w field & field_value is 1 to many )
- id
- value
- fk_field_id -> Field::id

Coordinates ( relation b/w field_value & coordinate is 1 to many )
- id
- start_x
- start_y
- end_x
- end_y
- fk_field_value_id -> FieldValue::id

as i can't access these tables directly from my project so i create a view

CREATE VIEW AS
 SELECT * FROM document doc
   LEFT JOIN field ON doc.id=field.document_id
   LEFT JOIN field_value ON field.id=field_value.field_id
   LEFT JOIN coordinate ON field_value.id=coordinate.id;

The result set of view for one document would look something like:

document_id | filename  | field_id  | type   | field_value_id   | value  | coordinate_id | start_x   | start_y ...
id_1        | test.pdf  | f_id_1    | single | f_v_id_1         | test1  | c_id_1        | 0         | 1
id_1        | test.pdf  | f_id_1    | single | f_v_id_1         | test1  | c_id_2        | 1         | 3
id_1        | test.pdf  | f_id_1    | single | f_v_id_2         | test2  | c_id_3        | 2         | 4
id_1        | test.pdf  | f_id_1    | single | f_v_id_2         | test2  | c_id_4        | 0         | 5

i have entities defined in Java which looks like following:

class Document {
    String id;
    String filename;
    List<Field> fields;
}

class Field {
    String id;
    Sring type;
    List<FieldValue> values;
}

class FieldValue {
    String id;
    String value;
    List<Cordinate> coordinates;
}

class Coordinate {
    String id;
    int start_x;
    int start_y;
    int end_x;
    int end_y;
}

So how do i map the result from view to the document entity?

Note: i can't create multiple views as in actual scenario i have lots of tables.

Upvotes: 1

Views: 199

Answers (1)

Christian Beikov
Christian Beikov

Reputation: 16410

That's impossible, you can't map one-to-many associations which were flattened in a view as entity. Hibernate has no way to manage the rows properly this way. You don't need the view though. Hibernate is perfectly capable of creating a query like the one you are using for the view on demand.

Upvotes: 1

Related Questions