避免@ManyToMany中的MultipleBagFetchException

我搜索了整个Internet,但到目前为止没有找到适合我的答案。我想对我的项目应用过滤器:用户输入颜色列表和尺寸列表,然后程序根据输入返回所有选项。

为此,我使用查询:

    @Query("select distinct clothes FROM Clothes as clothes left join fetch clothes.colors as ccolor left join fetch clothes.sizes as csize where " +
            "ccolor.name in (:colors) and csize.name in (:sizes)")
    List<Clothes> findAllByColorsInAndSizesIn(List<String> colors, List<String> sizes);

I saw a lot of participants' answers that it is better to use Set<...> instead of List<...>, but I believe that this is not true (there is an article about it from Vlad Mihalcea). Using Set<...> is like using @Transactional to pass tests in your Spring project.

        List<Clothes> clothes = entityManager
                .createQuery(
                        "select distinct clothes FROM Clothes as clothes left join fetch clothes.colors as ccolor where " +
                                " ccolor.name in (:colors)", Clothes.class)
                .setParameter("colors", colors)
                .setHint(QueryHints.PASS_DISTINCT_THROUGH, false)
                .getResultList();
        clothes = entityManager
                .createQuery(
                        "select distinct clothes FROM Clothes as clothes left join fetch clothes.sizes as csize where " +
                        "csize.name in (:sizes)", Clothes.class)
                .setParameter("sizes", sizes)
                .setHint(QueryHints.PASS_DISTINCT_THROUGH, false)
                .getResultList();

However, I don't think it's correct as the first query is redundant. Here's the proof: enter image description here

因此,我想到了“手动”比较两个数组的想法,并在衣服的尺寸和颜色满足原始过滤条件的情况下将它们组合在一起。

我的UML:

UML

码:

Clothes.java:

@Entity
@Table(name = "clothes")
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Clothes {
    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "clothes_generator")
    @SequenceGenerator(name="clothes_generator", sequenceName = "clothes_seq", allocationSize = 1, initialValue = 1)
    private Long id;

    @Column
    private String name;

    @ManyToMany(fetch = FetchType.LAZY,
            cascade = {
                    CascadeType.PERSIST,
                    CascadeType.MERGE
            })
    @JoinTable(name = "clother_color",
            joinColumns = { @JoinColumn(name = "clother_id") },
            inverseJoinColumns = { @JoinColumn(name = "color_id") })
    private List<Color> colors = new ArrayList<>();

    public void addColor(Color color) {
        colors.add(color);
        color.getClothes().add(this);
    }

    public void removeColor(Color color) {
        colors.remove(color);
        color.getClothes().remove(this);
    }

    @ManyToMany(fetch = FetchType.LAZY,
            cascade = {
                    CascadeType.PERSIST,
                    CascadeType.MERGE
            })
    @JoinTable(name = "clother_size",
            joinColumns = { @JoinColumn(name = "clother_id") },
            inverseJoinColumns = { @JoinColumn(name = "size_id") })
    private List<Size> sizes = new ArrayList<>();

    public void addSize(Size size) {
        sizes.add(size);
        size.getClothes().add(this);
    }

    public void removeSize(Size size) {
        sizes.remove(size);
        size.getClothes().remove(this);
    }
}

Sizes.java:

@Entity
@Table(name = "color")
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Color {

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

    @Column
    private String name;

    @ManyToMany(mappedBy="colors")
    private List<Clothes> clothes = new ArrayList<>();

    public void addClothes(Clothes clothes) {
        this.clothes.add(clothes);
        clothes.getColors().add(this);
    }

    public void removeClothes(Clothes clothes) {
        this.clothes.remove(clothes);
        clothes.getColors().remove(this);
    }

    public Color(String name){
        this.name = name;
    }
}

Color.java

@Entity
@Table(name = "color")
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Color {

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

    @Column
    private String name;

    @ManyToMany(mappedBy="colors")
    private List<Clothes> clothes = new ArrayList<>();

    public void addClothes(Clothes clothes) {
        this.clothes.add(clothes);
        clothes.getColors().add(this);
    }

    public void removeClothes(Clothes clothes) {
        this.clothes.remove(clothes);
        clothes.getColors().remove(this);
    }

    public Color(String name){
        this.name = name;
    }
}

测试我要通过的测试(我知道我需要使用assert):

    @Test
    void findClothesByColorsAndSizes(){

        Color colorBlue = new Color("Blue");
        Color colorRed = new Color("Red");
        Color colorPink = new Color("Pink");
        colorRepository.saveAll(Arrays.asList(colorBlue, colorPink, colorRed));
        colorRepository.flush();

        Size sizeL = new Size("L");
        Size sizeXL = new Size("XL");
        Size sizeS = new Size("S");
        sizeRepository.saveAll(Arrays.asList(sizeL, sizeS, sizeXL));
        sizeRepository.flush();

        Clothes clothes1 = new Clothes("Clothes1");
        clothes1.addColor(colorRepository.findByName(colorBlue.getName()));
        clothes1.addSize(sizeRepository.findByName(sizeS.getName()));

        Clothes clothes2 = new Clothes("Clothes2");
        clothes2.addColor(colorRepository.findByName(colorBlue.getName()));
        clothes2.addSize(sizeRepository.findByName(sizeL.getName()));

        Clothes clothes3 = new Clothes("Clothes3");
        clothes3.addColor(colorRepository.findByName(colorRed.getName()));
        clothes3.addSize(sizeRepository.findByName(sizeXL.getName()));

        Clothes clothes4 = new Clothes("Clothes4");
        clothes4.addColor(colorRepository.findByName(colorPink.getName()));
        clothes4.addSize(sizeRepository.findByName(sizeS.getName()));

        Clothes clothes5 = new Clothes("Clothes5");
        clothes5.addColor(colorRepository.findByName(colorPink.getName()));
        clothes5.addSize(sizeRepository.findByName(sizeL.getName()));


        clothesRepository.saveAll(Arrays.asList(clothes1, clothes2, clothes3, clothes4, clothes5));
        clothesRepository.flush();

        for (Clothes clothes: clothesRepository.findAll()){
            System.out.println("name: " + clothes.getName());
            System.out.println("color: " + clothes.getColors().get(0).getName());
            System.out.println("size: " + clothes.getSizes().get(0).getName());
            System.out.println();
        }

        System.out.println();
        System.out.println();
        System.out.println();

        List<String> colors = new ArrayList<>();
        colors.add(colorBlue.getName());
        colors.add(colorRed.getName());

        List<String> sizes = new ArrayList<>();
        sizes.add(sizeL.getName());
        sizes.add(sizeXL.getName());

//        List<Clothes> clothes = clothesRepository.findAllBySizesIn(sizes);
//        List<Clothes> common = new ArrayList<>();
//        for(Clothes clothesItem : clothesRepository.findAllByColorsIn(colors)){
//            if(clothes.contains(clothesItem)){
//                common.add(clothesItem);
//            }
//        }

//        System.out.println(common.size());
//
//        System.out.println();
//        for (Clothes clothesItem : common) {
//            System.out.println("name: " + clothesItem.getName());
//            System.out.println("color: " + clothesItem.getColors().size());
//            System.out.println("size: " + clothesItem.getSizes().size());
//            System.out.println();
//        }

        List<Clothes> clothes = entityManager
                .createQuery(
                        "select distinct clothes FROM Clothes as clothes left join fetch clothes.colors as ccolor where " +
                                " ccolor.name in (:colors)", Clothes.class)
                .setParameter("colors", colors)
                .setHint(QueryHints.PASS_DISTINCT_THROUGH, false)
                .getResultList();
        clothes = entityManager
                .createQuery(
                        "select distinct clothes FROM Clothes as clothes left join fetch clothes.sizes as csize where " +
                        "csize.name in (:sizes)", Clothes.class)
                .setParameter("sizes", sizes)
                .setHint(QueryHints.PASS_DISTINCT_THROUGH, false)
                .getResultList();


        System.out.println(clothes.size());

        System.out.println();
        for (Clothes clothesItem : clothes) {
            System.out.println("name: " + clothesItem.getName());
            System.out.println("color: " + clothesItem.getColors().size());
            System.out.println("size: " + clothesItem.getSizes().size());
            System.out.println();
        }
    }