Spring Data Join 规范

Spring Data Join with Specifications(Spring Data Join 规范)

问题描述

我正在尝试转换这个原始 sql 查询:

I'm trying to convert this raw sql query:

select product.* from following_relationship
join product on following_relationship.following=product.owner_id
where following_relationship.owner=input 

在 Spring Data 规范中，我认为到目前为止我的问题在于加入这些表.

Into Spring Data specifications, i think that my issue so far is on joining those tables.

这是我目前在规范中的转换:

Here is my current conversion in Specification:

protected Specification<Product> test(final User user){
   return new Specification<Product>() {
       @Override
       public Predicate toPredicate(Root<Product> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
           Join<FollowingRelationship,Product> pfJoin = query.from(FollowingRelationship.class).join("following");
           pfJoin.on(cb.equal(pfJoin.get("following"),"owner"));
           return  query.where(cb.equal(pfJoin.get("following"),user)).getGroupRestriction();

       }
   };
}

我得到了这个例外:

Request processing failed; nested exception is org.springframework.dao.InvalidDataAccessA
piUsageException: org.hibernate.hql.internal.ast.InvalidWithClauseException: with clause can only reference columns in the driving table 

我想补充一点，我是 Spring 框架的新手，例如这是我在 Spring 上的第一个应用程序，所以对于新手问题我深表歉意；)

I will like to add that I'm new at Spring framework for instance this is my first application on spring, so my apologies for the newbie question ;)

添加实体 Product、FollowingRelationShip

added entities Product, FollowingRelationShip

Entity
@JsonIdentityInfo(generator = ObjectIdGenerators.IntSequenceGenerator.class, property = "json_id_prop")
public class FollowingRelationship extends BaseEntity {

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "OWNER", referencedColumnName = "uuid")
    private User owner;
    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "FOLLOWING", referencedColumnName = "uuid")
    private User following;

    public User getOwner() {
        return owner;
    }

    public void setOwner(User owner) {
        this.owner = owner;
    }

    public User getFollowing() {
        return following;
    }

    public void setFollowing(User following) {
        this.following = following;
    }

}

@Entity
@Table(name = "product")
@JsonIdentityInfo(generator = ObjectIdGenerators.IntSequenceGenerator.class, property = "json_id_prop")
public class Product extends BaseEntity {

    @ManyToOne(fetch = FetchType.EAGER)
    @JoinColumn(name = "OWNER_ID", referencedColumnName = "uuid")
    private User owner;
    @NotNull
    private String name;
    @NotNull
    private String description;
    @NotNull
    private String price;
    @NotNull
    private String brand;

    public String getName() {
        return name;
    }

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

    public String getDescription() {
        return description;
    }

    public void setDescription(String description) {
        this.description = description;
    }

    public String getPrice() {
        return price;
    }

    public void setPrice(String price) {
        this.price = price;
    }

    public String getBrand() {
        return brand;
    }

    public void setBrand(String brand) {
        this.brand = brand;
    }

    public User getOwner() {
        return owner;
    }

    public void setOwner(User owner) {
        this.owner = owner;
    }


}

Product 和 FollowRelationShip 实体没有任何明确的关系，因此加入我的实现 about.我想要实现的是从 Spring 数据规范中另一个用户遵循的所有用户获取所有产品.

Product and FollowingRelationShip entities do no have any explicit relationship, hence the join on my implementation about.What i want to achieve is to get all products from all users which another user follow in Spring data Specifications.

推荐答案

好的，我在这里做得很乱，但我希望这次我更接近正确的答案.

Ok, I did quite a mess here, but I hope this time I'm closer to the right answer.

考虑(ID 是自动生成的，例如 John 等的 1):

Consider (id's are auto-generated like 1 for John etc.):

INSERT INTO some_user (name) VALUES ('John');
INSERT INTO some_user (name) VALUES ('Ariel');
INSERT INTO some_user (name) VALUES ('Brian');
INSERT INTO some_user (name) VALUES ('Kelly');
INSERT INTO some_user (name) VALUES ('Tom');
INSERT INTO some_user (name) VALUES ('Sonya');

INSERT INTO product (owner_id,name) VALUES (1,'Nokia 3310');
INSERT INTO product (owner_id,name) VALUES (2,'Sony Xperia Aqua');
INSERT INTO product (owner_id,name) VALUES (3,'IPhone 4S');
INSERT INTO product (owner_id,name) VALUES (1,'Xiaomi MI5');
INSERT INTO product (owner_id,name) VALUES (3,'Samsung Galaxy S7');
INSERT INTO product (owner_id,name) VALUES (3,'Sony Xperia Z3');

INSERT INTO following_relationship (follower_id, owner_id) VALUES (4,1);
INSERT INTO following_relationship (follower_id, owner_id) VALUES (5,1);
INSERT INTO following_relationship (follower_id, owner_id) VALUES (4,2);
INSERT INTO following_relationship (follower_id, owner_id) VALUES (6,2);
INSERT INTO following_relationship (follower_id, owner_id) VALUES (6,3);
INSERT INTO following_relationship (follower_id, owner_id) VALUES (1,3);

基于您提供的实体的简化版本，以及 SomeUser 实体，例如:

Based on simplified version of entities that You provided, and SomeUser Entity like:

@Entity
public class FollowingRelationship {

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

@ManyToOne(cascade=CascadeType.ALL, fetch=FetchType.EAGER)
@JoinColumn(name = "owner_id")
SomeUser owner;
    
@ManyToOne(cascade=CascadeType.ALL, fetch=FetchType.EAGER)
@JoinColumn(name = "follower_id")
SomeUser follower;

...

@Entity
public class Product {

@Id
@GeneratedValue(strategy=GenerationType.IDENTITY)
private Long id;
    
@ManyToOne()
@JoinColumn(name = "owner_id")
private SomeUser owner;
    
@Column
private String name;

...

@Entity
public class SomeUser {

@Id
@GeneratedValue(strategy=GenerationType.IDENTITY)
private Long id;
    
@Column
private String name;

@OneToMany(mappedBy = "owner")
private Set<Product> products = new HashSet<Product>();

@OneToMany(mappedBy = "owner")
private Set<FollowingRelationship> ownedRelationships = new HashSet<FollowingRelationship>();

@OneToMany(mappedBy = "follower")
private Set<FollowingRelationship> followedRelationships = new HashSet<FollowingRelationship>();

我创建了如下规范:

public static Specification<Product> joinTest(SomeUser input) {
    return new Specification<Product>() {
        public Predicate toPredicate(Root<Product> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
            Join<Product,SomeUser> userProd = root.join("owner");
            Join<FollowingRelationship,Product> prodRelation = userProd.join("ownedRelationships");
            return cb.equal(prodRelation.get("follower"), input);
        }
    };
}

现在，我们可以执行查询:

And now, we can execute the query with:

SomeUser someUser = someUserRepository.findOne(Specification.where(ProductSpecifications.userHasName("Kelly")));
List<Product> thatProducts = productRepository.findAll(Specification.where(ProductSpecifications.joinTest(someUser)));
System.out.println(thatProducts.toString());

我们得到:

[Product [id=1, name=Nokia 3310], Product [id=4, name=Xiaomi MI5], Product [id=2, name=Sony Xperia Aqua]]

在我看来，这相当于:从其他用户关注的所有用户那里获取所有产品"；- 获取 Kelly 关注的所有用户的产品.

And this in My opinion is equivalent of: "get all products from all users which another user follow" - get products of all users that Kelly is following.

这篇关于Spring Data Join 规范的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！