Map OneToMany recursive relationship in Spring Data

By | July 16, 2018
Questions:

I want to map a recursive relationship between a category and a subcategory using Spring Data. I have the following code:

@Entity
public class Category {

    @Id
    @GeneratedValue(strategy=GenerationType.AUTO)
    private Long id;

    @Column(nullable=false)
    private String name;

    @ManyToOne(cascade={CascadeType.ALL})
    @JoinColumn(name="parent_id")
    private Category parentId;

    @OneToMany(mappedBy="parentId", cascade=CascadeType.ALL)
    private Set<Category>subcategories = new HashSet<Category>();

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Category getParentId() {
        return parentId;
    }

    public void setParentId(Category parentId) {
        this.parentId = parentId;
    }

    public Set<Category> getSubcategories() {
        return subcategories;
    }

    public void setSubcategories(Set<Category> subcategories) {
        this.subcategories = subcategories;
    }

}

For test purposes, I have created a named query into a repository that extends JpaRepository:

    @Query(value="select category.id, category.name from category ", nativeQuery=true)
    List<Category> test();

I have a spring boot endpoint that calls a service in which I autowired my repository. When called, the following error occures:

org.springframework.dao.InvalidDataAccessResourceUsageException: could not execute query; SQL [select category.id, category.name from category ]; nested exception is org.hibernate.exception.SQLGrammarException: could not execute query

Caused by: org.h2.jdbc.JdbcSQLException: Column “parent_id” not found [42122-197]

The sql table looks like this:

CREATE TABLE CATEGORY(
    ID INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
    NAME VARCHAR(400) NOT NULL,
    PARENT_ID INT ,
    FOREIGN KEY(PARENT_ID) REFERENCES CATEGORY(ID)
);

How to map recursive relationship?

Answers:

Leave a Reply

Your email address will not be published. Required fields are marked *