Spring Boot Entity how to check value if exist in another table by custom field-CodePudding

The user can search for products if any product shown in the result exists in the user_favorites table so the show flag tells the front-end this product was added for this user by user_id and product_id. with spring boot and spring data.

My Entity :

@Id
@Column(name = "catId")
private Integer catId;
@Column(name = "cat_no")
private String catNo;
@Column(name = "cat_sn")
private String catSn;
@Column(name = "doc_ref")
private String docRef;
@Column(name = "user_id")
private Integer userId;
@Column(name = "updated_at")
private String updatedAt;
@Column(name = "created_at")
private String createdAt;

I tried that using @Formula but nothing happing always returns null. and if it's done by @Formula how can i add parameters to @Formula

@Formula(value = "SELECT count(*) as checker  FROM fb_user_favorites WHERE cat_id = 34699 AND user_id = '52') ")
@Transient
private String checker;

CodePudding user response：

@Transient is part of JPA spec. In Hibernate fields marked with this annotation just simply ignored/excluded from any JPA engine/runtime logic.

@Formula is part of Hibernate. Fields, marked with it, don't persisted by Hibernate (first argument do not use @Transient as redundant), values are calculated by provided SQL when executing query for entity. So for Hibernate to see this fields, they should not be excluded by @Transient

TL;DR remove @Transient annotation

CodePudding user response：

Complicated but fast working way.

Adding isFavorite field to the entity:

@Transient
private boolean isFavorite;

Create an entity linking Product and User:

public class ProductFavorite {

    @Id
    @GeneratedValue(strategy = IDENTITY)
    private Long id;

    @ManyToOne(optional = false, fetch = LAZY)
    private Product product;

    @ManyToOne(optional = false, fetch = LAZY)
    private User user;

}

Then create a repository with a method to find the user's favorite products:

@Repository
public interface ProductLikeRepository extends JpaRepository<ProductFavorite, Long> {

    @Query("select f.product.id from ProductFavorite f where f.product in ?1 and f.user = ?2")
    Set<Integer> findProductIdsByIdsAndUser(List<Product> products, User user);

}

And at the end, write a method that will fill in the isFavorite field:

public void fillFavorite(List<Product> products, User user) {
    if (products.isEmpty()) {
        return;
    }
    var likedIds = favoriteRepository.findProductIdsByIdsAndUser(products, user);
    for (Product product : products) {
        product.setFavorite(likedIds.contains(product.getId()));
    }
}

You need to call it manually:

List<Product> products = productRepository.findAll();
fillFavorite(products, currentUser());