Hibernate criteria addOrder messes results

Question

My User table looks like this:

USER (
    "USER_ID" NUMBER(15,0) NOT NULL, 
    "TYPE" NUMBER(2,0), 
    "DOMAIN_ID" NUMBER(15,0), 
    "FIRST_NAME" VARCHAR2(64), 
    "LAST_NAME" VARCHAR2(64) NOT NULL, 
    "EMAIL" VARCHAR2(256), 
    "PHONE_NUMBER" VARCHAR2(11), 
    "IS_ENABLED" NUMBER(1,0),
)

ORM looks like this:

@Entity
@Table(name = "\"USER\"")
public class User implements Serializable
{
    @Id
    @GeneratedValue(generator = "USER_SEQ")
    @GenericGenerator(name = "USER_SEQ", strategy = "sequence", parameters = @Parameter(name = "sequence", value = "SEQ_USER_ID"))
    @Column(name = "USER_ID", nullable = false)
    private Long userId;

    @Column(name = "FIRST_NAME")
    private String firstName;

    @Column(name = "LAST_NAME")
    private String lastName;

    @Column(name = "EMAIL")
    private String email;

    @Column(name = "PHONE_NUMBER")
    private String phoneNumber;

    @ManyToOne(fetch = FetchType.LAZY)
    @Cascade(CascadeType.ALL)
    @JoinColumn(name = "DOMAIN_ID")
    @ForeignKey(name = "DOMAIN_ID")
    private Domain domain;

    @Column(name = "IS_ENABLED")
    private Boolean isEnabled;

    @Column(name = "TYPE")
    private Integer type;
}

And I'm doing Criteria for the user list:

@SuppressWarnings("unchecked")
public List<User> getUsersList( String firstName, String lastName, Integer domainId, Boolean activeFlag, String email,
                                String phoneNr, int pageNr, int pageSize, String sortColumn, Boolean sortAscending )
{
    Session session = sessionFactory.getCurrentSession();
    Criteria criteria = createUserCriteria( firstName, lastName, domainId, activeFlag, email, phoneNr, session );

    criteria.createAlias( "domain", "domain" );
    criteria.setFirstResult( pageSize * pageNr );
    criteria.setMaxResults( pageSize );
    criteria.setFetchSize( pageSize );

    Order order;
    if ( sortColumn != null && sortAscending != null )
    {
        order = (sortAscending) ? Order.asc( sortColumn ) : Order.desc( sortColumn );
    } else
    {
        order = Order.asc( "lastName" );
    }

    criteria.addOrder( order );

    return criteria.list();
}

private Criteria createBusinessUserCriteria( String firstName, String lastName, Integer domainId, Boolean activeFlag, String email, String phoneNr, Session session )
{
    Criteria criteria = session.createCriteria( User.class );
    // only businness users
    criteria.add( Restrictions.eq( "type", UserTypeEnum.BUSINESS_USER.getUserTypeId() ) );

    if ( firstName != null )
    {
        criteria.add( Restrictions.like( "firstName", firstName + "%" ).ignoreCase() );
    }
    if ( lastName != null )
    {
        criteria.add( Restrictions.like( "lastName", lastName + "%" ).ignoreCase() );
    }
    if ( domainId != null )
    {
        criteria.add( Restrictions.eq( "domain.domainId", domainId ) );
    }
    if ( activeFlag != null )
    {
        criteria.add( Restrictions.eq( "isEnabled", activeFlag ) );
    } else
    {
        criteria.add( Restrictions.eq( "isEnabled", true ) );
    }
    if ( email != null )
    {
        criteria.add( Restrictions.like( "email", email + "%" ).ignoreCase() );
    }
    if ( phoneNr != null )
    {
        criteria.add( Restrictions.like( "phoneNumber", phoneNr + "%" ).ignoreCase() );
    }
    return criteria;
}

Quite simple, when theres no Order param passed to method, I'm using default sort by user Lastname. PageSize is 25, pageNr is taken from httpRequest param (DisplayTag), it starts from 0 and goes up.

Spring MVC controller for this:

@RequestMapping(value = "/GetNotificationUsers/", method = RequestMethod.GET)
public String getNotificationUsers( @ModelAttribute("eventNotifUserSearchForm") UsersSearchForm searchForm, Model model,
                                     HttpServletRequest request ) throws IOException
{
    searchForm.setDefaultsToNull();

    Integer pageNr = 0;
    String pageNoParamAsString = request.getParameter( new ParamEncoder( "eventNotifUsers" ).encodeParameterName(
            TableTagParameters.PARAMETER_PAGE ) );
    if ( pageNoParamAsString != null && !pageNoParamAsString.isEmpty() )
    {
        pageNr = Integer.parseInt( pageNoParamAsString ) - 1;
    }

    List<User> resultUsersList = administrativeManager.getUsersList( searchForm.getFirstName(),
            searchForm.getLastName(), userManager.getLoggedInUser().getDomain().getDomainId(), searchForm.getEnabledFlag(),
            searchForm.getEmail(), searchForm.getPhoneNumber(), pageNr, PAGE_SIZE, null, null );

    Long size = administrativeManager.getUsersListCount( searchForm.getFirstName(), searchForm.getLastName(),
            searchForm.getDomainId(), searchForm.getEnabledFlag(), searchForm.getEmail(), searchForm.getPhoneNumber() );
    //for ( User user : resultUsersList )
    //{
    //   System.out.println( user );
    //}
    model.addAttribute( "eventUserListSize", size.intValue() );
    model.addAttribute( "eventNotifUsers", resultUsersList );
    model.addAttribute( "pageSize", PAGE_SIZE );

    return "notifications/EventNotificationUserList";
}

DisplayTag jsp:

<display:table name="${eventNotifUsers}" id="eventNotifUsers" pagesize="${pageSize}"
               cellspacing="0" cellpadding="10" requestURI="" partialList="true" size="eventUserListSize" sort="external" defaultsort="2">
    <display:column title="FirstName" property="firstName"/>
    <display:column property="lastName" title="LastName"/>
    <display:column property="email" title="Email"/>
    <display:column property="phoneNumber" title="Phone"/>
</display:table>

But when I display actuall list, first page looks fine, but then till the end the first one is repeated. When I remove Order it works fine. I have no idea why... what might be wrong?

EDIT

After some research I discovered that the problem is only when sorting by lastName. Is it possible because my test that has all same lastName? When sorting by other column it works fine.

JB Nizet · Accepted Answer · 2012-10-27T15:41:56.720

If you have several users with the same last name, sorting by last name will return users sorted by last name, but you don't have any guarantee over the order of the users who share the same last name. So you should add an additional sort criterion to guarantee a deterministic order. For example:

order by lastName asc, userId asc

In this case, users sharing the same last name will always be sorted by ID, and the order will the be deterministic.

Hibernate criteria addOrder messes results

1 Answers1