criteria.list() and sql-query return different result sets

Question

I've the following criteria-query:

@Transactional(
            propagation = Propagation.REQUIRED,
            isolation = Isolation.READ_COMMITTED,
            readOnly = true
    )
    public List<GameSession> getGameSessions(Integer maxId,
            boolean active, Integer gameId, GameType gameType,
            Integer playerCategoryId, String playerEmail, String playerLogin, Date from, Date to, Integer playerId, CasinoCurrency currency,
            CasinoGameSessionSortField sortField, SortOrder sortOrder,
            int offset, int limit) {

        Criteria criteria = getSession().createCriteria(GameSession.class);

        if (maxId != null) {
            criteria.add(Restrictions.le("id", maxId));
        }

        if (active) {
            criteria.add(Restrictions.eq("active", active));
        }

        Criteria gameCriteria = criteria.createCriteria("game", "g");

        if (gameId != null && gameId > 0) {
            gameCriteria.add(Restrictions.eq("g.id", gameId));
        }
        if (gameType != null) {
            gameCriteria.add(Restrictions.eq("g.type", gameType));
        }       

        if (from != null) {
            criteria.add(Restrictions.ge("startDate", from));
        }

        if (to != null) {
            criteria.add(Restrictions.le("startDate", to));
        }

        Criteria playerCriteria = criteria.createCriteria("player", "p");

        if (playerId != null) {
            playerCriteria.add(Restrictions.eq("p.id", playerId));
        }
        if (playerCategoryId != null) {
            playerCriteria.add(Restrictions.eq("p.category.id", playerCategoryId));
        }
        if (StringUtils.isNotBlank(playerEmail)) {
            playerCriteria.add(Restrictions.like("p.email", "%" + playerEmail.toLowerCase() + "%"));
        }
        if (StringUtils.isNotBlank(playerLogin)) {
            playerCriteria.add(Restrictions.like("p.login", "%" + playerLogin.toLowerCase() + "%"));
        }

        if (currency != null) {
            criteria.add(Restrictions.eq("currency", currency));
        }

        if (sortField != null && sortOrder != null) {
            Order order = (sortOrder == SortOrder.ASC) ? Order.asc(sortField.getValue()) : Order.desc(sortField.getValue());
            criteria.addOrder(order);
        }

        criteria.setFirstResult(offset);
        criteria.setMaxResults(limit);

        criteria.setResultTransformer(Criteria.ROOT_ENTITY);

        List<GameSession> list = (List<GameSession>) criteria.list(); 
        //Native sql, returned by Log4j and that criteria.list() returns different result set 
        //Why?                                                  
        return list;
    }

and Mapping:

@Entity
@Table(name = "game_session")
public class GameSession {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id")
    private int id;

    @Column(name = "last_access_date", nullable = false)
    private Date lastAccessedDate;

    @Column(
            name = "start_date",
            nullable = false,
            insertable = true,
            updatable = false
    )
    private Date startDate;

    @Column(name = "end_date", nullable = true)
    private Date endDate;

    @ManyToOne(targetEntity = Player.class, fetch = FetchType.LAZY)
    @JoinColumn(name = "player_id")
    private Player player;

    @ManyToOne(targetEntity = Game.class, fetch = FetchType.LAZY)
    @JoinColumn(name = "casino_game_id")
    private Game game;

    @Column(
            name = "payout",
            nullable = false,
            insertable = true,
            updatable = true
    )
    private BigDecimal payout;

    @Type(
        type = "com.strg.utils.db.hibernate.GenericEnumUserType",
        parameters = {
                @Parameter(
                    name  = "enumClass",
                    value = "com.strg.commons.db.entity.CasinoCurrency"),
                @Parameter(
                    name  = "identifierMethod",
                    value = "getValue"),
                @Parameter(
                    name  = "valueOfMethod",
                    value = "getByValue")
                }
    )
    @Column(name = "currency_id", nullable = true, updatable = true)
    private Currency currency;

    @Column(name = "balance", nullable = true)
    private BigDecimal balance;

    @Column(name = "active", nullable = false)
    private boolean active;

    @Column(
            name = "rounds_count",
            nullable = false,
            insertable = true,
            updatable = true
    )
    private int roundsCount;

The issue is I can't understand why that criteria.list() and sql-query logged by log4j2 returns different result set. I really have no idea what could be a problem with? Could you suggest me anything?

Answer 1

The results are different because you are likely getting duplicates in your criteria.list() results. You should replace this line:

criteria.setResultTransformer(Criteria.ROOT_ENTITY);

With this one:

criteria.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);

Doing so should at least ensure that your results won't return duplicates.