Connecting 3 tables with hibernate / spring-boot

I have problem understanding how to connect 3 tables with spring-boot / hibernate. Tables are: Users, Technologies, Categories

Every user has all of the 10 categories but inside this categories they can save one or more technologies. Each technology can be listed in several different categories.

I have a code that works partially as for now instead referencing the table category, I am just creating new category so I have duplicates in my BDD. Ideally I would love to have for each user data structure that like something like this (in pseudo-code):

{
    {
    "category1" : {id, name}
    "technologies" [{id, name}, {id, name}, {id, name} ]
    },

    {
    "category2" : {id, name}
    "technologies

    }
    .
    .
    .   
}

my tables are:

USER TABLE

public class MyUser {

// other properties

@OneToMany(mappedBy="id")
    private Collection<Category> categories;
}

TECHNOLOGY

public class Technology {

// other properties

    @Id
    @SequenceGenerator(name = "id_seq", sequenceName = "id_seq", allocationSize = 1, initialValue = 1)
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "id_seq")
    private int id;

    @Column(name = "name")
    private String name;
}

TECHNOLOGY CATEGORY:

public class TechnologyCategory {

// other properties

    @Id
    @SequenceGenerator(name = "id_seq", sequenceName = "id_seq", allocationSize = 1, initialValue = 1)
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "id_seq")
    private int id;

    @Column(name="name")
    private String name;
}

and the table where I am trying to connect users with categories (from which each one has list of technologies)

USER_CATEGORIES

public class UserCategory {

    @Id
    @SequenceGenerator(name = "id_seq", sequenceName = "id_seq", allocationSize = 1, initialValue = 1)
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "id_seq")
    private int id;

    @Column(name = "name")
    private String technologyCategory; // here I would love to reference technology category table

    @ManyToMany()
    Collection <Technology> technologies;
}

so for I have tried/read this: Joining three tables using MySQL

ManyToManyToMany – Joining three tables with Hibernate annotations

Hibernate: How to Join three 3 tables in one join table in Annotation?

Hibernate: mapping 3 tables

but with no success as every try to implement solutions above resulted in exceptions (all connected with hibernate unable to create tables) that I couldn’t resolve. Thank you

Answer

If I understoond correctly, something like this would work for you, assumed your example code works:

User class, which joins the categories:

public class User {

    @Id
    @SequenceGenerator(name = "id_seq", sequenceName = "id_seq", allocationSize = 1, initialValue = 1)
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "id_seq")
    private int id;

    // other properties

    @ManyToMany(fetch = FetchType.EAGER)
    @JoinTable(name = "user_categories", joinColumns = @JoinColumn(name = "user_id", referencedColumnName = "id"),
            inverseJoinColumns = @JoinColumn(name = "category_id", referencedColumnName = "id"))
    private Set<Category> categories;
    
}

Category entity, which contains the technology’s category, and the technologies:

public class Category {

    @Id
    @SequenceGenerator(name = "id_seq", sequenceName = "id_seq", allocationSize = 1, initialValue = 1)
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "id_seq")
    private int id;

    // other properties

    @ManyToOne
    @JoinColumn(name = "technology_category_id")
    private TechnologyCategory category;
    
    @ManyToMany(fetch = FetchType.EAGER)
    @JoinTable(name = "category_technologies", joinColumns = @JoinColumn(name = "category_id", referencedColumnName = "id"),
            inverseJoinColumns = @JoinColumn(name = "technology_id", referencedColumnName = "id"))
    private Set<Technology> technologies;
    
}

TechnologyCategory entity:

public class TechnologyCategory {

    // other properties

    @Id
    @SequenceGenerator(name = "id_seq", sequenceName = "id_seq", allocationSize = 1, initialValue = 1)
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "id_seq")
    private int id;

    @Column(name="name")
    private String name;
}

Technology entity:

public class Technology {

    // other properties

    @Id
    @SequenceGenerator(name = "id_seq", sequenceName = "id_seq", allocationSize = 1, initialValue = 1)
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "id_seq")
    private int id;

    @Column(name="name")
    private String name;
}

If you don’t want the ids to show up in your JSON, just put @JsonIgnore annotation above the id property.