I am trying to implement basic group by on a table using JPA specification and criteria. I used query.multiselect and provided one column. However, during execution, SQL query is being built to fetch all columns. Theoretically, everything looks good. Not sure where I am going wrong.
Below is the service method
public List<POJO> fetchInwardnventoryGroupByUsingSpec() throws ParseException
{
Specification<InwardInventory> spec = findAllGroupBy();
List<POJO> iiData = inwardInventoryRepo.findAll(spec);
return iiData;
}
public static Specification<InwardInventory> findAllGroupBy()
{
return new Specification<InwardInventory>()
{
@Override
public Predicate toPredicate(Root<InwardInventory> root, CriteriaQuery<?> query, CriteriaBuilder cb)
{
query.multiselect(root.get(InwardInventory_.VEHICLE_NO),cb.count(root));
query.groupBy(root.get(InwardInventory_.VEHICLE_NO));
return query.getRestriction();
}
};
}
Below is the SQL generated by hibernate
/* select
generatedAlias0
from
InwardInventory as generatedAlias0
where
generatedAlias0.warehouse.warehouseName like :param0
group by
generatedAlias0.vehicleNo */ select
inwardinve0_.inwardid as inwardid1_13_,
inwardinve0_.created_at as created_2_13_,
inwardinve0_.is_deleted as is_delet3_13_,
inwardinve0_.updated_at as updated_4_13_,
inwardinve0_.additional_info as addition5_13_,
inwardinve0_.date as date6_13_,
inwardinve0_.invoice_received as invoice_7_13_,
inwardinve0_.our_slip_no as our_slip8_13_,
inwardinve0_.contact_id as contact11_13_,
inwardinve0_.supplier_slip_no as supplier9_13_,
inwardinve0_.vehicle_no as vehicle10_13_,
inwardinve0_.warehouse_id as warehou12_13_
from
inward_inventory inwardinve0_ cross
join
warehouse warehouse1_
where
(
inwardinve0_.is_deleted = 'false'
)
and inwardinve0_.warehouse_id=warehouse1_.warehouse_id
and (
warehouse1_.warehouse_name like ?
)
group by
inwardinve0_.vehicle_no
Below is the entity class
@Entity
@Table(name = "inward_inventory")
@Audited
@Where(clause = ReusableFields.SOFT_DELETED_CLAUSE)
public class InwardInventory extends ReusableFields implements Cloneable
{
@Id
@GeneratedValue(strategy = GenerationType.AUTO)
@Column(name="inwardid")
Long inwardid;
@JsonFormat(shape = JsonFormat.Shape.STRING, pattern="yyyy-MM-dd")
@Column(nullable = false)
@NonNull
Date date;
@NonNull
String vehicleNo;
String supplierSlipNo;
String ourSlipNo;
@ManyToMany(fetch=FetchType.EAGER,cascade = CascadeType.ALL)
@JoinTable(name = "inwardinventory_entry", joinColumns = {
@JoinColumn(name = "inwardid", referencedColumnName = "inwardid") }, inverseJoinColumns = {
@JoinColumn(name = "entryId", referencedColumnName = "entryId") })
Set<InwardOutwardList> inwardOutwardList = new HashSet<>();;
@ManyToOne(fetch=FetchType.LAZY,cascade = CascadeType.ALL)
@JoinColumn(name="warehouse_id",nullable=false)
@JsonIgnoreProperties({"hibernateLazyInitializer", "handler"})
Warehouse warehouse;
@ManyToOne(fetch=FetchType.LAZY,cascade = CascadeType.ALL)
@JoinColumn(name="contactId",nullable=false)
@JsonIgnoreProperties({"hibernateLazyInitializer", "handler"})
Supplier supplier;
String additionalInfo;
@NonNull
@Column(nullable = false)
Boolean invoiceReceived;
//getter/setter
}