spring data JDBC generates invalid queries

Question

Here is my table structure. I am using spring-boot-starter-data-jdbc 3.0.3. I mapped these tables like below.

@Table("ticket")
public class Ticket {

    @Id
    private int id;
    private String subject;
    private TicketType ticketType;

// getters and setters
}

@Table("ticket_type")
public class TicketType {

    @Id
    private int id;
    private String name;

// getters and setters

}

when I try to retrieve tickets, spring throws a BadSqlGrammarException. The generated query is

SELECT `ticket`.`id` AS `id`, `ticket`.`subject` AS `subject`, `ticketType`.`id` AS `tickettype_id`, `ticketType`.`name` AS `tickettype_name` FROM `ticket` LEFT OUTER JOIN `ticket_type` `ticketType` ON `ticketType`.`ticket` = `ticket`.`id`

why spring data JDBC generates the on cause as

`ticketType`.`ticket` = `ticket`.`id` 

instead of

`ticketType`.`id` = `ticket`.`ticket_type`

I tried @MappedCollection(idColumn = "ticket_id") to the ticketType field in Ticket class. then the on cause of generated query was ticketType.ticket_id = ticket.id. what am I doing wrong ?

Answer 1

Spring Data JDBC expects for one-to-one relationships that the foreign key goes in the same direction as it would for a one-to-many relationship.

While this might seem weird at first, I do think it makes a lot of sense. It is symmetric to the one-to-many case. The aggregate root (Ticket) should not depend on the referenced entity (TicketType) and therefore it also shouldn't have the reference in the table, which then would have to be nullable. With the the foreign key pointing from the referenced entity to the aggregate root it can be not null.

See also Allow for FK in normal direction [DATAJDBC-128]

That said, from the entity names it seems you don't want to create a one-to-one but a one-to-many relationship. Those get modelled as a simple id value, optionally wrapped in an AggregateReference in Spring Data JDBC. See Spring Data JDBC, References, and Aggregates for details.