JPA Criteria Specification - multiselect with group by returns all columns

Viewed 3613

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
}
1 Answers

Finally after spending days of effort came to know that this is an known issue

https://jira.spring.io/browse/DATAJPA-1532

Hence, I handled it by Auto-wiring entity manager, then creating all required predicated and queries. then calling getResults() instead of findall()

@Autowired
    EntityManager entityManager;
    public List<Model> getResults() throws ParseException 
    {
        CriteriaQuery<Model> query = modelSpecification.getSpecQuery();
        List<Model> allData  = entityManager.createQuery(query).getResultList();
        return allData;
    }
Related