0

I have defined the named native query in orm.xml which maps to the method declared in repository interface

Student Repository

@Query(nativeQuery = true)
public List<DeptStuInfo> deptEmplInfosPageable(Pageable pageable);

orm.xml

<named-native-query
        name="Student.deptEmplInfosPageable"
        result-set-mapping="DeptEmplMapping">
        <query><![CDATA[select dept.name as deptName,stu.name as stuName,stu.doj as stuDoj 
                        from department dept,student stu
                        where dept.id=stu.studeptId
                        group by dept.name,stu.name,stu.doj 
                ]]>
        </query>
    </named-native-query>


    <named-native-query
        name="Student.deptEmplInfosPageable.count">
        <query><![CDATA[select count(1) from student stu]]></query>
    </named-native-query>
    
    <sql-result-set-mapping name="DeptEmplMapping">
        <constructor-result target-class="com.example.querydslex.pojo.DeptStuInfo">
            <column name="deptName"/>
            <column name="stuName"/>
            <column name="stuDoj" class="java.time.LocalDate"/>
        </constructor-result>
    </sql-result-set-mapping>

The result is mapped to class DeptStuInfo

public class DeptStuInfo {

    private String deptName;

    private String studentName;

    @JsonFormat(pattern = "dd-MM-yyyy")
    private LocalDate studentDoj;

    public String getDeptName() {
        return deptName;
    }

    public void setDeptName(String deptName) {
        this.deptName = deptName;
    }

    public String getStudentName() {
        return studentName;
    }

    public void setStudentName(String studentName) {
        this.studentName = studentName;
    }

    public LocalDate getStudentDoj() {
        return studentDoj;
    }

    public void setStudentDoj(LocalDate studentDoj) {
        this.studentDoj = studentDoj;
    }

    public DeptStuInfo(String deptName, String studentName, LocalDate studentDoj) {
        super();
        this.deptName = deptName;
        this.studentName = studentName;
        this.studentDoj = studentDoj;
    }
}

I am using a class to set the Pageable object

public class PageablePojo {
    
    private Integer page;
    
    private Integer size;
    
    private String sort;
    
    private String direction;

    public Integer getPage() {
        return page;
    }

    public void setPage(Integer page) {
        this.page = page;
    }

    public Integer getSize() {
        return size;
    }

    public void setSize(Integer size) {
        this.size = size;
    }

    public String getSort() {
        return sort;
    }

    public void setSort(String sort) {
        this.sort = sort;
    }

    public String getDirection() {
        return direction;
    }

    public void setDirection(String direction) {
        this.direction = direction;
    }

}

Using the above class to create the pageable object as below

PageRequest.of(obj.getPage(), obj.getSize(), Sort.by(obj.getSort()));

The issue I am facing is that order by clause is not generated in the query. Only limit clause is getting generated

0 Answers0