CODEWITHSUNDEEP
javasqlpostgresqlhibernatespring-data-jpa
Pranav MS
Pranav MS

Reputation: 2296

Spring JPA hibernate query builder

I just wanted to create query dynamically.Means I just wanted to specify in which column the sorting will occure and type sorting as ASC or DESC also with this query i have to limit the no of records. I am using the PostgreSQL here. So in the query i should specify the limit and offset .

So that i am sending the four variable in the sense attribute to the interface.

WorkflowDetailsInterface.java

@Configuration
public interface WorkflowDetailsInterface extends CrudRepository<WorkflowDetails, Integer> {
@Query(value ="SELECT workflow_id, workflow_name, workflow_description, workflow_definition, "
        + "camunda_workflow_json, camuda_deployment_id, camunda_status,diagnostic_flag, "
        + "active, del_flag  FROM workflow  ORDER BY :orderByColoumn  : orderByOrder LIMIT  :recordCountLimit OFFSET :startLimit",nativeQuery = true)
    List<WorkflowDetails> listWorkflowName(@Param("startLimit") int startLimit,
            @Param("recordCountLimit")int recordCountLimit,@Param("orderByColoumn")String orderByColoumn,
            @Param("orderByOrder")String orderByOrder);
}

Here , @Param("startLimit") int startLimit will be the starting point @Param("recordCountLimit")int recordCountLimit this is the offset @Param("orderByColoumn")String orderByColoumn order by column name @Param("orderByOrder")String orderByOrder order by type ASC or DESC

How can i dynamically bind these variable to my JPA query

Upvotes: 1

Views: 1507

Answers (2)

Richard Salam raj
Richard Salam raj

Reputation: 36

Why not use stored procedures inside PostgreSQL and pass the parameter mentioning which column to sort and pass Limit parameter to it. Refer to here for more info.

You can call the stored procedure like this:

String sql ="call <stored_procedure_name>(:parameter1)";
@SuppressWarnings("deprecation")
Query query = sessionFactory.getCurrentSession().createNativeQuery(sql).addEntity(<your persistant>.class).setParameter("parameter1", <your param>);

Hope it helps.

Upvotes: 1

Adisesha
Adisesha

Reputation: 5258

Instead of extending CrudRepository extend PagingAndSortingRepository. 'findAll' method serves your purpose. PageRequest is an implementation of Pageable. You can specify limit, offset and sort details. Check this article to know more about usage. You may not need native query with this approach.

Upvotes: 0

Related Questions