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 ?

score 0 · Answer 1 · answered Mar 06 '23 at 09:28

0

You may try to use the ManyToOne and joinColumn annotation to map the entity while still keeping the FK column field.

    @Table("ticket")
public class Ticket {

    @Id
    private int id;
    private String subject;
    @Column(name = "ticket_type")
    private int ticketType;
    
    @ManyToOne
    @JoinColumn(name = "ticket_type", referencedColumnName = "id", insertable = false, updatable = false)
    private TicketType ticketType;

// getters and setters
}

answered Mar 06 '23 at 09:28

Tommy

1,960
1
19
32

1

ManyToOne & JoinColumn Annotations are for JPA right ? , This is Spring data JDBC not Spring Data JPA. – Thisara Dilshan Mar 06 '23 at 09:43
Yes, you are right, i think you can use @MappedCollection inside entity TicketType, that means Set tickets – Tommy Mar 06 '23 at 10:02

score 0 · Accepted Answer · answered Mar 07 '23 at 08:45

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.

spring data JDBC generates invalid queries

2 Answers2