Java Spring Hibernate JPA PostgreSQL Avoid saving duplicate rows / records-CodePudding

I'm very green when it comes to databases. I feel like this is probably a pretty common database problem, I can't seem to find the correct search terms to find my answer.

My issue is "duplicate" rows in a table. I'm trying to save restaurant food menus in a database, and for the most part its working alright. I have a object called RestaurantWeek, which contains a list of RestaurantDay objects and each day contains a list of RestaurantCourse objects. They get saved as such in the database: image. "weeks_days" and "days_courses" tables are the link between the "weeks", "days" and "courses" tables.

Now the problem comes when many days can have the same "course". Almost every single day has "Salad" as a course, so what ends up happening is I have 12 identical rows in my "courses" table, the only exception being the id column: image. So now the question is, how can I tell JPA or Hibernate to use the existing "Salad" row in my "courses" table instead of inserting a new one every time? Is it possible to do this by adding some specific annotation to my objects or their properties?

I have tried setting the "name" property on "RestaurantCourse" to unique with @Column(unique=true) but then I get errors about hibernate trying to save multiple courses with the same name (since name must be unique). I have tried grabbing the "courses" table when saving data and using the same id multiple times, but then I get errors about hibernate trying to save multiple courses with the same id (since id must be unique).

Is it even possible to fix this "easily", such as with few specific annotation I'm in the unknown about? Do I need to change something else about how my data is saved to the database, such as the classes, the annotations, or the way I'm trying to save?

Here are my classes.

@AllArgsConstructor
@NoArgsConstructor
@Data
@Entity
@Table(name="weeks")
public class RestaurantWeek {
    @Id
    private long id;
    private Date saveDate;
    private String weekName;
    @OneToMany(cascade = CascadeType.ALL)
    private List<RestaurantDay> days;
}

@AllArgsConstructor
@NoArgsConstructor
@Data
@Entity
@Table(name="days")
public class RestaurantDay {
    @Id
    @GeneratedValue (strategy = GenerationType.IDENTITY)
    private Long id;
    private String day;
    @OneToMany(cascade = CascadeType.ALL)
    private List<RestaurantCourse> courses;
}

@AllArgsConstructor
@NoArgsConstructor
@Data
@TypeDef(name = "list-array", typeClass = ListArrayType.class)
@Entity
@Table(name = "courses")
public class RestaurantCourse {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private long id;
    @Column(unique = true)
    private String name;
    private String price;
    private String type;
    @Type(type = "list-array")
    @Column(name = "tags", columnDefinition = "text[]")
    private List<String> tags;
}

And what I'm using to save:

@Repository
public interface RestaurantMenuRepository extends JpaRepository<RestaurantWeek, Long> {
}

public class RestaurantMenuServiceImpl implements RestaurantMenuService {
    @Autowired
    private RestaurantMenuRepository restaurantMenuRepository;
    @Override
    public RestaurantWeek addNewWeek(RestaurantWeek restaurantWeek) {
        return this.restaurantMenuRepository.save(restaurantWeek);
    }
}

Thanks in advance.

CodePudding user response：

Yes is posible, you must use existing entity. But use in this method public RestaurantWeek addNewWeek(RestaurantWeek restaurantWeek) parameter RestaurantWeek is not correct try put this method some dto class with need field to create entity class, additionally pass the parameter to available identify courses entity find which you can doing relationship and add to days entity. No pass all parameter every time!

CodePudding user response：

Alright, finally found the correct search terms and found the answer. Resolution was a combination of serob's answer and a bunch of googling.

In RestaurantDay I changed @OneToMany to @ManyToMany.

I created repository interfaces for RestaurantDay and RestaurantCourse.

When saving the course, I save the courses first, then the days, and finally the week, while grabbing all the new ids.

public RestaurantWeek addNewWeek(RestaurantWeek restaurantWeek) {
        for (RestaurantDay day : restaurantWeek.getDays()) {
            for (RestaurantCourse course : day.getCourses()) {
                RestaurantCourse dbCourse = this.restaurantCourseRepository.findCourseByName(course.getName());
                if (dbCourse == null) {
                    course.setId(this.restaurantCourseRepository.save(course).getId());
                }
                else {
                    course.setId(dbCourse.getId());
                }
            }
            this.restaurantDayRepository.save(day);
        }

        return this.restaurantMenuRepository.saveAndFlush(restaurantWeek);
    }

CodePudding user response：

Try @NaturalId, this would make your name an Id for the Course entity: https://vladmihalcea.com/the-best-way-to-map-a-naturalid-business-key-with-jpa-and-hibernate/