Cannot use sort feature for pagination as my variable are in snake case [Spring-boot]

Question

I'm trying to implement pagination in my project. pagination is working fine if I am paginating result using pageOffset and pageSize. but when I try to use Sort feature it is unable to process the data and is throwing

org.springframework.data.mapping.PropertyReferenceException: No property request found for type InstituteSmsAllocationHistory!

I tried to change the variable name in many ways and found that if I keep name as "request id" then the error changes to

org.springframework.data.mapping.PropertyReferenceException: No property request id found for type InstituteSmsAllocationHistory! Did you mean 'request_id'?

but I dont know how to make it understand that I want it to sort by request_id

I don't want to change the structure of my project to camel case from snake case.

Please find my code below: InstituteSmsAllocationHistory.java (Entity)

package com.proc.admin.rest.model;

import java.io.Serializable;

import javax.persistence.CascadeType;
import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.OneToOne;
import javax.persistence.Table;

import com.fasterxml.jackson.annotation.JsonIgnoreProperties;
import com.proc.common.entity.AuditableEntity;
import com.proc.common.entity.Institution;

import lombok.Getter;
import lombok.Setter;

@Entity
@Table(name = "tab_inst_sms_allocation_hist")
@Getter
@Setter
public class InstituteSmsAllocationHistory extends AuditableEntity<String> implements Serializable {

    /**
     * 
     */
    private static final long serialVersionUID = -4218076388772964964L;
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private int               request_id;
    @JsonIgnoreProperties({ "hibernateLazyInitializer", "handler" })
    @OneToOne(cascade = CascadeType.ALL, fetch = FetchType.LAZY)
    @JoinColumn(name = "institution_id")
    private Institution       institution_id;
    @JsonIgnoreProperties({ "hibernateLazyInitializer", "handler" })
    @OneToOne(cascade = CascadeType.ALL, fetch = FetchType.LAZY)
    @JoinColumn(name = "sms_plan_id")
    private SMSPlanMaster     sms_plan_id;
    private int               sms_quota_allocated;
    private String            quota_type;
    private String            reason;
}

InstituteSmsAllocationHistoryController.java (Controller)

 @GetMapping(value = HISTORY + ALL)
    public ResponseEntity<PageResponseEntity<InstituteSmsAllocationHistory>> getSmsAllocationHistory(@RequestParam(defaultValue = "1") int pageOffset,
            @RequestParam(defaultValue = "10") int pageSize,
            @RequestParam(defaultValue = "request_id") String sortBy) {
        return RestResponseConverterUtil.sucess(smsPlanMasterService.getAllSmsHistory(pageOffset - 1, pageSize, sortBy));
    }

InstituteSmsAllocationHistoryServiceImpl.java (Service)

 @Override
    public PageResponseEntity<InstituteSmsAllocationHistory> getAllSmsHistory(int pageOffset, int pageSize, String sortBy) {
        Pageable pageable = PageRequest.of(pageOffset, pageSize, Sort.by("request_id"));
        Page<InstituteSmsAllocationHistory> page = instituteSmsAllocationHistoryDao.findAll(pageable);
        PageResponseEntity<InstituteSmsAllocationHistory> pageList = new PageResponseEntity<InstituteSmsAllocationHistory>(page.getContent(), ++pageOffset, pageSize, sortBy, page.getTotalElements());
        return pageList;
    }

Please let me know if you need anything other than this.

Thank you!

did you try with request__id with two underscore – Shailesh Chandra Oct 24 '19 at 05:51 — Shailesh Chandra, Oct 24 '19 at 05:51
@ShaileshChandra yes I tried request__id – Abhishek Shrivastava Oct 24 '19 at 12:12 — Abhishek Shrivastava, Oct 24 '19 at 12:12

score 0 · Answer 1 · answered Oct 24 '19 at 07:28

0

Why would you call your properties like that?

If you want to make snake case the default for column names, just use the appropriate naming strategy (snake case is the default BTW). I'd strongly advise to use regular Java naming conventions, otherwise you might run into trouble later on.

As regards sorting, if all else fails, try using JpaSort.unsafe(sortBy). Keep in mind that Spring Data will not validate the existence of the property in such a case - a misspelled property will only be recognized by the underlying DB when a query is attempted.

answered Oct 24 '19 at 07:28

crizzis

9,978
2
28
47

I cannot change the structure as I am not allowed to do so. also the error didn't go by using `JpaSort.unsafe(sortBy)` the error remained the same. I also read about [this] https://stackoverflow.com/questions/43993952/how-to-sort-projection-by-alias-from-select-clause-in-spring-data-jpa-with-pagin but trying this didn't helped me. – Abhishek Shrivastava Oct 24 '19 at 12:12

Cannot use sort feature for pagination as my variable are in snake case [Spring-boot]

1 Answers1