Spring data jdbc one-to-many wrong join

Question

I'm using spring-boot 2.3.3 and spring-data-jdbc 2.0.3 to model a relationship between to elements guitar and classType. A guitar has a classType.

My schema in H2 (also in MySQL) is this:

CREATE TABLE class_type (
    id bigint NOT NULL AUTO_INCREMENT,
    description varchar(50) NOT NULL
    PRIMARY KEY (id)
);

CREATE TABLE guitars (
    id bigint NOT NULL AUTO_INCREMENT,
    name varchar(255) NOT NULL,
    description varchar(1000) NOT NULL,
    classType bigint NOT NULL,
    PRIMARY KEY (id),
    UNIQUE (name),
    FOREIGN KEY (classType) references class_type(id)
);

And I have these classes:

@Data
@NoArgsConstructor
@AllArgsConstructor
@Builder
@Table(value = "guitars")
public class Guitar {
    @Id
    long id;
    String name;
    String description;
    @MappedCollection(idColumn = "id")
    @Column(value = "classType")
    ClassType classType;
}

@Data
@NoArgsConstructor
@AllArgsConstructor
@Builder
@Table(value = "class_type")
public class ClassType {
    @Id
    Long id;
    String description;    
}

My repository is like this:

@Repository
public interface GuitarRepository extends CrudRepository<Guitar, Long>, PagingAndSortingRepository<Guitar, Long> {}

When I invoke this test:

@SpringBootTest
public class GuitarOneManyTest {

    @Autowired
    GuitarRepository guitarRepository;

    @Test
    void findAllByName() {
        System.out.println(guitarRepository.findAll());
    }
}

This sentence appears with an incorrect LEFT OUTER JOIN

SELECT `guitars`.`id` AS `id`, `guitars`.`name` AS `name`, `guitars`.`description` AS `description`, `classType`.`id` AS `classType_id`, `classType`.`description` AS `classType_description` FROM `guitars` LEFT OUTER JOIN `class_type` AS `classType` ON `classType`.`id` = `guitars`.`id`

But I want the select to be like this:

SELECT (...) FROM `guitars` LEFT OUTER JOIN `class_type` AS `classType` ON `classType`.`id` = `guitars`.`classType`

Did I miss something?

score 1 · Accepted Answer · answered Dec 14 '20 at 09:12

This is not a one-to-many relationship. One Guitar would reference multiple ClassType instances.

Instead it seems to be intended as a many-to-one relationship: Many Guitar instances might reference the same ClassType. This makes ClassType a different aggregate from Guitar and therefore it must not be referenced by a direct java reference, but only by it's id.

See Spring Data JDBC, References, and Aggregates for a more detailed explanation how to model such a relationship with Spring Data JDBC.

Spring data jdbc one-to-many wrong join

1 Answers1