Spring JPA hibernate query builder

Question

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

marc_s · Accepted Answer · 2019-02-03 21:01:13Z

1

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.

edited Feb 3, 2019 at 21:01

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Mar 21, 2018 at 4:43

Richard Salam raj

366 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Adisesha · Accepted Answer · 2018-03-21 04:37:42Z

0

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.

answered Mar 21, 2018 at 4:37

Adisesha

5,2681 gold badge35 silver badges44 bronze badges

Collectives™ on Stack Overflow

Spring JPA hibernate query builder

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related