gpt4 book ai didi

Java Criteria API 查询具有复合主键的桥表

转载 作者:行者123 更新时间:2023-12-01 11:32:09 26 4
gpt4 key购买 nike

相当于以下 SQL 的 Java criteria api 查询是什么?我对此很陌生,尝试了一些方法但没有成功。

SELECT p.* FROM Product p
RIGHT JOIN store_has_product shp ON p.id_product=shp.id_product
WHERE shp.id_store=1;

表A:存储

桥接表:store_has_product(表有 id_store、id_product 作为复合 pk)

表B:产品

实体(没有构造函数、getter/setter)

    @Entity
@Table(name = "product")
@XmlRootElement
@NamedQueries({
@NamedQuery(name = "Product.findAll", query = "SELECT p FROM Product p"),
@NamedQuery(name = "Product.findByIdProduct", query = "SELECT p FROM Product p WHERE p.idProduct = :idProduct"),
@NamedQuery(name = "Product.findByName", query = "SELECT p FROM Product p WHERE p.name = :name"),
@NamedQuery(name = "Product.findByCost", query = "SELECT p FROM Product p WHERE p.cost = :cost")})
public class Product implements Serializable {
private static final long serialVersionUID = 1L;
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Basic(optional = false)
@Column(name = "idProduct")
private Integer idProduct;
@Size(max = 45)
@Column(name = "name")
private String name;
// @Max(value=?) @Min(value=?)//if you know range of your decimal fields consider using these annotations to enforce field validation
@Column(name = "cost")
private Double cost;
@OneToMany(cascade = CascadeType.ALL, mappedBy = "product")
private Collection<StoreHasProduct> storeHasProductCollection;

@Entity
@Table(name = "store")
@XmlRootElement
@NamedQueries({
@NamedQuery(name = "Store.findAll", query = "SELECT s FROM Store s"),
@NamedQuery(name = "Store.findByIdStore", query = "SELECT s FROM Store s WHERE s.idStore = :idStore"),
@NamedQuery(name = "Store.findByName", query = "SELECT s FROM Store s WHERE s.name = :name")})
public class Store implements Serializable {
private static final long serialVersionUID = 1L;
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Basic(optional = false)
@Column(name = "idStore")
private Integer idStore;
@Size(max = 45)
@Column(name = "name")
private String name;
@OneToMany(cascade = CascadeType.ALL, mappedBy = "store")
private Collection<StoreHasProduct> storeHasProductCollection;

@Entity
@Table(name = "store_has_product")
@XmlRootElement
@NamedQueries({
@NamedQuery(name = "StoreHasProduct.findAll", query = "SELECT s FROM StoreHasProduct s"),
@NamedQuery(name = "StoreHasProduct.findByStoreidStore", query = "SELECT s FROM StoreHasProduct s WHERE s.storeHasProductPK.storeidStore = :storeidStore"),
@NamedQuery(name = "StoreHasProduct.findByProductidProduct", query = "SELECT s FROM StoreHasProduct s WHERE s.storeHasProductPK.productidProduct = :productidProduct"),
@NamedQuery(name = "StoreHasProduct.findByActive", query = "SELECT s FROM StoreHasProduct s WHERE s.active = :active")})
public class StoreHasProduct implements Serializable {
private static final long serialVersionUID = 1L;
@EmbeddedId
protected StoreHasProductPK storeHasProductPK;
@Column(name = "active")
private Short active;
@JoinColumn(name = "Product_idProduct", referencedColumnName = "idProduct", insertable = false, updatable = false)
@ManyToOne(optional = false)
private Product product;
@JoinColumn(name = "Store_idStore", referencedColumnName = "idStore", insertable = false, updatable = false)
@ManyToOne(optional = false)
private Store store;

@Embeddable
public class StoreHasProductPK implements Serializable {
@Basic(optional = false)
@NotNull
@Column(name = "Store_idStore")
private int storeidStore;
@Basic(optional = false)
@NotNull
@Column(name = "Product_idProduct")
private int productidProduct;

元模型

@Generated(value = "org.hibernate.jpamodelgen.JPAMetaModelEntityProcessor")
@StaticMetamodel(Product.class)
public abstract class Product_ {

public static volatile SingularAttribute<Product, Double> cost;
public static volatile SingularAttribute<Product, String> name;
public static volatile SingularAttribute<Product, Integer> idProduct;
public static volatile CollectionAttribute<Product, StoreHasProduct> storeHasProductCollection;

}

@Generated(value = "org.hibernate.jpamodelgen.JPAMetaModelEntityProcessor")
@StaticMetamodel(Store.class)
public abstract class Store_ {

public static volatile SingularAttribute<Store, String> name;
public static volatile SingularAttribute<Store, Integer> idStore;
public static volatile CollectionAttribute<Store, StoreHasProduct> storeHasProductCollection;

}

@Generated(value = "org.hibernate.jpamodelgen.JPAMetaModelEntityProcessor")
@StaticMetamodel(StoreHasProduct.class)
public abstract class StoreHasProduct_ {

public static volatile SingularAttribute<StoreHasProduct, Product> product;
public static volatile SingularAttribute<StoreHasProduct, Short> active;
public static volatile SingularAttribute<StoreHasProduct, Store> store;
public static volatile SingularAttribute<StoreHasProduct, StoreHasProductPK> storeHasProductPK;

}

@Generated(value = "org.hibernate.jpamodelgen.JPAMetaModelEntityProcessor")
@StaticMetamodel(StoreHasProductPK.class)
public abstract class StoreHasProductPK_ {

public static volatile SingularAttribute<StoreHasProductPK, Integer> productidProduct;
public static volatile SingularAttribute<StoreHasProductPK, Integer> storeidStore;

}

编辑*问题:是否可以将其用作规范?我什至不知道我在谓词方面是否做了正确的事情,但这是我正在尝试的:

Integer resultsPerPage = 20;
Sort.Direction sortOrder = Sort.Direction.DESC;
String sortBy = "name";
PageRequest request
= new PageRequest(1 - 1, resultsPerPage, sortOrder, sortBy);
Page<Product> prods = productRepository.findAll(where(productBelongsToStore("Tesco")), request);

for (Product prod : prods.getContent()) {
System.out.println(""+ prod.getName());
}

规范:

public static Specification<Product> productBelongsToStore(final String searchTerm) {
return new Specification<Product>() {
@Override
public Predicate toPredicate(Root<Product> root, CriteriaQuery<?> cq, CriteriaBuilder cb) {
String likePattern = SpecificationUtilityMethods.getLikePattern(searchTerm);
Root<Product> product = cq.from(Product.class);
Join<Product, StoreHasProduct> storeHasProduct = product.join(Product_.storeHasProductCollection);
Path<Store> storeName = storeHasProduct.get(StoreHasProduct_.store);
return cb.like(cb.lower(storeName.<String>get(Store_.name)), likePattern);

}
};
}

输出:牛奶牛奶果汁果汁 Cereal Cereal 黄油黄油面包面包

哪个是错误的

*对于任何尝试相同操作的人,我通过删除“Root Product = cq.from(Product.class);”来修复此问题并且仅使用 root 参数。

最佳答案

尝试以下操作:

CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<Product> query = cb.createQuery(Product.class);

// "FROM Product p"
Root<Product> product = query.from(Product.class);
// "RIGHT JOIN store_has_product shp", "ON p.id_product=shp.id_product" is handled inside Product.class
Join<Product,StoreHasProduct> storeHasProduct = product.join(Product_.storeHasProductCollection);
// Create an alias to the shp.id_store column to use in the where condition
Path<Integer> id = storeHasProduct.get(StoreHasProduct_.storeHasProductPK).get(StoreHasProductPK_.storeidStore);
// "WHERE shp.id_store = 1"
query.where(cb.equal(id, 1));
// "SELECT p.*"
query.select(product);

TypedQuery<Product> tq = entityManager.createQuery(query);
for (Product p : tq.getResultList()) {
// do stuff
}

这是我的想法,代码未经测试,所以我希望我没有犯任何重大错误。

这也可以在没有元模型类的情况下解决,但是在将列名称作为字符串输入时可能会犯错误。编译器无法捕获的东西。使用元模型,编译器可以捕获更多否则可能会错过的错误。

关于Java Criteria API 查询具有复合主键的桥表,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/30307726/

26 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com