问题描述
我正在尝试转换这个原始 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.