我正在等待一个产品类型实体对象,其数量和总 cahmps 包含 SUM (lc.quantity( 作为定量,SUM (lc.montant( 作为总数的值,但我得到一个 java.lang.object 类型的对象
这是我的查询
@NamedQuery(name = "Produit.findAllBuyPorductGroupByProduct",
query = "SELECT p, SUM(lc.quantite) quantite, SUM(lc.montant) total
FROM LigneCommande lc JOIN lc.produit p
GROUP BY p")
这是我的产品实体
/*
* To change this license header, choose License Headers in Project Properties.
* To change this template file, choose Tools | Templates
* and open the template in the editor.
*/
package com.instantech.stogeg.data.base.entity;
import java.io.Serializable;
import java.util.Collection;
import java.util.Date;
import javax.persistence.Basic;
import javax.persistence.CascadeType;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.NamedQueries;
import javax.persistence.NamedQuery;
import javax.persistence.OneToMany;
import javax.persistence.Table;
import javax.persistence.Temporal;
import javax.persistence.TemporalType;
import javax.persistence.Transient;
import javax.xml.bind.annotation.XmlRootElement;
import javax.xml.bind.annotation.XmlTransient;
/**
*
* @author instantech
*/
@Entity
@Table(name = "produit")
@XmlRootElement
@NamedQueries({
@NamedQuery(name = "Produit.findAll", query = "SELECT p FROM Produit p")
, @NamedQuery(name = "Produit.findById", query = "SELECT p FROM Produit p WHERE p.id = :id")
, @NamedQuery(name = "Produit.findByReference", query = "SELECT p FROM Produit p WHERE p.reference = :reference")
, @NamedQuery(name = "Produit.findByDesignation", query = "SELECT p FROM Produit p WHERE p.designation = :designation")
, @NamedQuery(name = "Produit.findByStock", query = "SELECT p FROM Produit p WHERE p.stock = :stock")
, @NamedQuery(name = "Produit.findAllBuyPorductGroupByProduct", query = "SELECT p, SUM(lc.quantite) quantite, SUM(lc.montant) total FROM LigneCommande lc JOIN lc.produit p GROUP BY p")
, @NamedQuery(name = "Produit.findBy_", query = "SELECT p FROM Produit p WHERE CAST(p.id as CHAR) LIKE :numeroProduit OR CAST(p.pu as CHAR) LIKE :pu OR CAST(p.stock as CHAR) LIKE :stock OR p.reference LIKE :reference OR p.designation LIKE :designation ORDER BY p.designation")
, @NamedQuery(name = "Produit.findByPu", query = "SELECT p FROM Produit p WHERE p.pu = :pu")
, @NamedQuery(name = "Produit.findByDate", query = "SELECT p FROM Produit p WHERE p.date = :date")
, @NamedQuery(name = "Produit.findByHeure", query = "SELECT p FROM Produit p WHERE p.heure = :heure")})
public class Produit implements Serializable {
private static final long serialVersionUID = 1L;
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Basic(optional = false)
@Column(name = "id")
private Integer id;
@Basic(optional = false)
@Column(name = "reference")
private String reference;
@Basic(optional = false)
@Column(name = "designation")
private String designation;
@Basic(optional = false)
@Column(name = "stock")
private int stock;
@Basic(optional = false)
@Column(name = "pu")
private float pu;
@Basic(optional = false)
@Column(name = "date")
@Temporal(TemporalType.DATE)
private Date date;
@Basic(optional = false)
@Column(name = "heure")
@Temporal(TemporalType.TIME)
private Date heure;
@OneToMany(cascade = CascadeType.ALL, mappedBy = "produit")
private Collection<LigneCommande> ligneCommandeCollection;
@OneToMany(cascade = CascadeType.ALL, mappedBy = "produit")
private Collection<HistoryStock> historyStockCollection;
@Transient
private int quantite;
@Transient
private double total;
public Produit() {
}
public Produit(Integer id) {
this.id = id;
}
public Produit(Integer id, String reference, String designation, int stock, float pu, Date date, Date heure) {
this.id = id;
this.reference = reference;
this.designation = designation;
this.stock = stock;
this.pu = pu;
this.date = date;
this.heure = heure;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getReference() {
return reference;
}
public void setReference(String reference) {
this.reference = reference;
}
public String getDesignation() {
return designation;
}
public void setDesignation(String designation) {
this.designation = designation;
}
public int getStock() {
return stock;
}
public void setStock(int stock) {
this.stock = stock;
}
public float getPu() {
return pu;
}
public void setPu(float pu) {
this.pu = pu;
}
public Date getDate() {
return date;
}
public void setDate(Date date) {
this.date = date;
}
public Date getHeure() {
return heure;
}
public void setHeure(Date heure) {
this.heure = heure;
}
@XmlTransient
public Collection<LigneCommande> getLigneCommandeCollection() {
return ligneCommandeCollection;
}
public void setLigneCommandeCollection(Collection<LigneCommande> ligneCommandeCollection) {
this.ligneCommandeCollection = ligneCommandeCollection;
}
@XmlTransient
public Collection<HistoryStock> getHistoryStockCollection() {
return historyStockCollection;
}
public void setHistoryStockCollection(Collection<HistoryStock> historyStockCollection) {
this.historyStockCollection = historyStockCollection;
}
@Override
public int hashCode() {
int hash = 0;
hash += (id != null ? id.hashCode() : 0);
return hash;
}
@Override
public boolean equals(Object object) {
// TODO: Warning - this method won't work in the case the id fields are not set
if (!(object instanceof Produit)) {
return false;
}
Produit other = (Produit) object;
return !((this.id == null && other.id != null) || (this.id != null && !this.id.equals(other.id)));
}
@Override
public String toString() {
return this.getDesignation() + " ( "+this.getStock()+ " ) ";
}
public Object[] getProduit(){
return new Object [] {
this.getId(), this.getReference(), this.getDesignation(), this.getStock(), this.getPu()
};
}
public int updateStock(int quantite) throws Exception{
if(quantite > this.getStock())
throw new Exception("Le stock du produit est inférieur à la quantité commandé");
else
return (this.getStock() - quantite);
}
public int adQuantity(int quantite) throws Exception{
if(quantite > 0){
return (this.getStock() + quantite);
}else{
throw new Exception("Le stock du produit est ne peut pas être null ou négatif");
}
}
public int getQuantite() {
return quantite;
}
public void setQuantite(int quantite) {
this.quantite = quantite;
}
public double getTotal() {
return total;
}
public void setTotal(double total) {
this.total = total;
}
}
这是我的线路命令实体
/*
* To change this license header, choose License Headers in Project Properties.
* To change this template file, choose Tools | Templates
* and open the template in the editor.
*/
package com.instantech.stogeg.data.base.entity;
import java.io.Serializable;
import java.util.Collection;
import java.util.Date;
import javax.persistence.Basic;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.ManyToMany;
import javax.persistence.ManyToOne;
import javax.persistence.NamedQueries;
import javax.persistence.NamedQuery;
import javax.persistence.Table;
import javax.persistence.Temporal;
import javax.persistence.TemporalType;
import javax.xml.bind.annotation.XmlRootElement;
import javax.xml.bind.annotation.XmlTransient;
/**
*
* @author instantech
*/
@Entity
@Table(name = "ligne_commande")
@XmlRootElement
@NamedQueries({
@NamedQuery(name = "LigneCommande.findAll", query = "SELECT l FROM LigneCommande l")
, @NamedQuery(name = "LigneCommande.findById", query = "SELECT l FROM LigneCommande l WHERE l.id = :id")
, @NamedQuery(name = "LigneCommande.findByQuantite", query = "SELECT l FROM LigneCommande l WHERE l.quantite = :quantite")
, @NamedQuery(name = "LigneCommande.findByMontant", query = "SELECT l FROM LigneCommande l WHERE l.montant = :montant")})
public class LigneCommande implements Serializable {
@Basic(optional = false)
@Column(name = "date")
@Temporal(TemporalType.DATE)
private Date date;
private static final long serialVersionUID = 1L;
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Basic(optional = false)
@Column(name = "id")
private Integer id;
@Basic(optional = false)
@Column(name = "quantite")
private int quantite;
@Basic(optional = false)
@Column(name = "montant")
private double montant;
@ManyToMany(mappedBy = "ligneCommandeCollection")
private Collection<Commande> commandeCollection;
@JoinColumn(name = "produit", referencedColumnName = "id")
@ManyToOne(optional = false)
private Produit produit;
public LigneCommande() {
}
public LigneCommande(Integer id) {
this.id = id;
}
public LigneCommande(Integer id, int quantite, double montant, Date date) {
this.id = id;
this.quantite = quantite;
this.montant = montant;
this.date = date;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public int getQuantite() {
return quantite;
}
public void setQuantite(int quantite) {
this.quantite = quantite;
}
public double getMontant() {
return montant;
}
public void setMontant(double montant) {
this.montant = montant;
}
@XmlTransient
public Collection<Commande> getCommandeCollection() {
return commandeCollection;
}
public void setCommandeCollection(Collection<Commande> commandeCollection) {
this.commandeCollection = commandeCollection;
}
public Produit getProduit() {
return produit;
}
public void setProduit(Produit produit) {
this.produit = produit;
}
@Override
public int hashCode() {
int hash = 0;
hash += (id != null ? id.hashCode() : 0);
return hash;
}
@Override
public boolean equals(Object object) {
if (!(object instanceof LigneCommande)) {
return false;
}
LigneCommande other = (LigneCommande) object;
return !((this.id == null && other.id != null) || (this.id != null && !this.id.equals(other.id)));
}
@Override
public String toString() {
return "com.instantech.entity.LigneCommande[ id=" + id + " ]";
}
public void addQuantite(int quantite){
this.quantite += quantite;
}
public double getTotal() {
return this.getQuantite() * this.getProduit().getPu();
}
public Object[] getLigneCommande(){
return new Object[] {
this.getProduit().getReference(),
this.getProduit().getDesignation(),
this.getQuantite(),
String.format("%, .0f",this.getProduit().getPu()),
String.format("%, .0f",this.getTotal())
};
}
public Date getDate() {
return date;
}
public void setDate(Date date) {
this.date = date;
}
}
检索查询结果的函数
/**
* Return list of product that grouped by product
*
* @return
*/
public List<Produit> findProductGroupBy() {
TypedQuery<Produit> query = this.getEntitymanager().createNamedQuery("Produit.findAllBuyPorductGroupByProduct", Produit.class);
return query.getResultList();
}
测试我的查询
public class test {
private static ProduitManager pm;
/**
* @param args the command line arguments
*/
public static void main(String[] args) {
pm = new ProduitManager();
pm.findProductGroupBy().forEach((p)->{
System.out.println(p);
});
pm.close();
}
}
这是执行后的结果
Exception in thread "main" java.lang.ClassCastException: [Ljava.lang.Object; cannot be cast to com.instantech.stogeg.data.base.entity.Produit
at java.util.Vector.forEach(Vector.java:1275)
at test.main(test.java:22)
注意:在数据库中的产品表中,我的产品实体中的数量和总计字段不存在。
1(创建某种TO:
public class ResultDTO{
private Product product;
private Integer quantity;
private Double total;
public ResultDTO(Product product, Integer quantity, Double total){
// set fields
}
2( 更改查询以使用该类:
@NamedQuery(name = "Produit.findAllBuyPorductGroupByProduct",
query = "SELECT new com.mypackage.ResultDTO(
p as product, SUM(lc.quantite) as quantity, SUM(lc.montant) as total)
FROM LigneCommande lc JOIN lc.produit p
GROUP BY p")
3( 将泛型类型更改为结果DTO:
public List<ResultDTO> findProductGroupBy() {
TypedQuery<ResultDTO> query = this.getEntitymanager().createNamedQuery(
"Produit.findAllBuyPorductGroupByProduct", ResultDTO.class);
return query.getResultList();
}
您正在寻找的内容称为投影。如何使用它们在这里得到了非常彻底的解释。它们比自定义对象(通过 new 关键字(工作得更好,因为它们支持本机查询并且通常更易于理解。
步骤 1:声明一个简单的 Bean 数据传输对象 (DTO(
@Data
@NoArgsConstructor
@AllArgsConstructor
public class ProductSummaryDto {
private Product product;
private Integer quantity;
private Double total;
}
步骤 2:在存储库中创建自定义 Spring 数据查询
@Query("SELECT p product, SUM(lc.quantite) quantity, SUM(lc.montant) total FROM LigneCommande lc JOIN lc.produit p GROUP BY p")
List<ProductSummaryDto> getProductSummaries();