java - Hibernate 标准 addOrder 弄乱了结果

Question

我的用户表如下所示:

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 看起来像这样:

@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;
}

我正在为用户列表制定标准:

@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;
}

非常简单，当没有 Order 参数传递给方法时，我使用默认的按用户Lastname排序。PageSize为25，pageNr取自httpRequest参数(DisplayTag)，从0开始向上。

Spring MVC Controller :

@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";
}

显示标签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>

但是当我显示实际列表时，第一页看起来不错，但直到最后，第一页都会重复。当我删除 Order 时，它工作正常。我不知道为什么......可能出了什么问题？

编辑

经过一些研究，我发现问题仅出现在按姓氏排序时。这可能是因为我的测试具有相同的 lastName 吗？当按其他列排序时，效果很好。

Answer 1

如果您有多个具有相同姓氏的用户，则按姓氏排序将返回按姓氏排序的用户，但您无法保证具有相同姓氏的用户的顺序。因此，您应该添加额外的排序标准来保证确定性顺序。例如:

order by lastName asc, userId asc

在这种情况下，具有相同姓氏的用户将始终按 ID 排序，并且顺序是确定的。