Not able to fetch results based on OrderBy using CriteriaBuilder

Hibernate ORM
1 
@Entity
@Table(name = "Test_Cases")
@OptimisticLocking(type = OptimisticLockType.NONE)
@DynamicUpdate(value=true)
@SelectBeforeUpdate(value=true)
public class TestCases {
    
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "CASE_ID")
    private int case_id;
    
    
    @ManyToMany(cascade ={ CascadeType.PERSIST, CascadeType.MERGE  }, fetch = FetchType.LAZY)
    @JoinTable( name = "Cases_Actions", joinColumns = @JoinColumn(name = "CASE_ID" ), inverseJoinColumns = @JoinColumn(name = "ACTION_ID"))
    private Collection<Actions> actions  = new ArrayList<Actions>();
    
    
    @OneToMany(mappedBy = "testCase", cascade = CascadeType.ALL,fetch = FetchType.EAGER, orphanRemoval = true)
    @OrderBy("sequence ASC")
    private Collection<CaseActionSequence> caseActionSequences = new ArrayList<CaseActionSequence>();
    }


 @Entity
@Table(name = "Actions")
@OptimisticLocking(type = OptimisticLockType.NONE)
@DynamicUpdate(value=true)
@SelectBeforeUpdate(value=true)
public class Actions {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "ACTION_ID")
    private int action_id;
    
    @ManyToMany(mappedBy = "actions" , fetch = FetchType.LAZY)
    @JsonIgnore
    private Collection<TestCases> testCases = new ArrayList<TestCases>();
    
    @OneToOne(cascade = CascadeType.ALL,fetch = FetchType.LAZY,orphanRemoval=false)  
    @JoinColumn(name="ACTIONS_FK",referencedColumnName = "action_id")
    private Actions actions ;
    
    @OneToMany(mappedBy = "action", cascade = CascadeType.ALL, orphanRemoval = true)    //for Particular case - this particular action - at what sequence 
    @OrderBy("sequence ASC")
    private Collection<CaseActionSequence> caseActionSequences = new ArrayList<>();
}


@Entity
@Table(name = "Cases_Actions")
@OptimisticLocking(type = OptimisticLockType.NONE)
@DynamicUpdate(value=true)
@SelectBeforeUpdate(value=true)
public class CaseActionSequence {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "CASE_ACTION_SEQ_ID")
    private Long id;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "CASE_ID")
    private TestCases testCase;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "ACTION_ID")
    private Actions action;

    @Column(name = "SEQUENCE")
    private int sequence;
}
These are my entity classes .


public TestCases getTestCaseDetail( Session session, int id ) throws Exception
    {
                
            TestCases tempModule;
            CriteriaBuilder cb = session.getCriteriaBuilder();

            CriteriaQuery<TestCases> query = cb.createQuery(TestCases.class);
            Root<TestCases> rootEntry = query.from(TestCases.class);
            Join<TestCases, CaseActionSequence> caseActionJoin = rootEntry.join("caseActionSequences", JoinType.INNER);
            Join<CaseActionSequence, Actions> actionJoin = caseActionJoin.join("action", JoinType.INNER);

            query.select(rootEntry)
                .where(cb.equal(rootEntry.get("case_id"), id))
                .orderBy(cb.asc(caseActionJoin.get("sequence")));

            TypedQuery<TestCases> typedQuery = session.createQuery(query);
            tempModule = typedQuery.getSingleResult(); // This fetches a single TestCases entity
            return tempModule.testCaseWithActions();
}

Using this method am trying to fetch the actions in an sequence.But it not working.It fetches in the order of CASE_ACTION_SEQ_ID i need in the order of SEQUENCE.How can i get the action in the order that i maintained in DB

Note : Am new to Spring/hibernate/JPA kindly guide me

2

This should just work fine. Please try to create a reproducer with our test case template and if you are able to reproduce the issue, create a bug ticket in our issue tracker and attach that reproducer.

3

Thanks for the reply @beikov .Did my code is correct ?

4

Your code looks ok to me.

1 Like
5

Ok then i’ll reproduce and update @beikov

1 Like