Hibernate returns equivalent rows-CodePudding

I executed my query including a subquery and a join; I retrieved the expective rows by SQL, but the rows of equivalent values by Query DSL (the count of rows is correct). The Hibernate loader sounds wrong. What should I do?

Log Message

2022-01-05 09:45:15.244 DEBUG 23996 --- [nio-8081-exec-1] org.hibernate.SQL                        :
Hibernate:
    select
        distinct axis0_.id as id1_0_,
        axis0_.label as label2_0_,
        axis0_.row as row3_0_
    from
        axis_item axis0_
    right outer join
        stat statcell1_
            on (
                statcell1_.col=axis0_.row
            )
    where
        axis0_.id=(
            select
                graph2_.col_axis_id
            from
                graph graph2_
            where
                graph2_.id=?
        )
    order by
        axis0_.row asc
09:45:15.244 TRACE  -  o.h.type.descriptor.sql.BasicBinder      : binding parameter [1] as [INTEGER] - [2]
09:45:15.248 DEBUG  -  org.hibernate.loader.Loader              : Result set row: 0
09:45:15.248 DEBUG  -  org.hibernate.loader.Loader              : Result row: EntityKey[com.sct.entity.Axis#2]
09:45:15.248 DEBUG  -  org.hibernate.loader.Loader              : Result set row: 1
09:45:15.248 DEBUG  -  org.hibernate.loader.Loader              : Result row: EntityKey[com.sct.entity.Axis#2]
09:45:15.248 DEBUG  -  org.hibernate.loader.Loader              : Result set row: 2
09:45:15.248 DEBUG  -  org.hibernate.loader.Loader              : Result row: EntityKey[com.sct.entity.Axis#2]
09:45:15.248 DEBUG  -  org.hibernate.loader.Loader              : Result set row: 3
09:45:15.248 DEBUG  -  org.hibernate.loader.Loader              : Result row: EntityKey[com.sct.entity.Axis#2]
09:45:15.248 DEBUG  -  org.hibernate.loader.Loader              : Result set row: 4
09:45:15.248 DEBUG  -  org.hibernate.loader.Loader              : Result row: EntityKey[com.sct.entity.Axis#2]
09:45:15.248 DEBUG  -  org.hibernate.loader.Loader              : Result set row: 5
09:45:15.248 DEBUG  -  org.hibernate.loader.Loader              : Result row: EntityKey[com.sct.entity.Axis#2]
09:45:15.248 DEBUG  -  org.hibernate.loader.Loader              : Result set row: 6
09:45:15.249 DEBUG  -  org.hibernate.loader.Loader              : Result row: EntityKey[com.sct.entity.Axis#2]
09:45:15.249 DEBUG  -  org.hibernate.loader.Loader              : Result set row: 7
09:45:15.249 DEBUG  -  org.hibernate.loader.Loader              : Result row: EntityKey[com.sct.entity.Axis#2]
09:45:15.249 DEBUG  -  org.hibernate.loader.Loader              : Result set row: 8
09:45:15.249 DEBUG  -  org.hibernate.loader.Loader              : Result row: EntityKey[com.sct.entity.Axis#2]
09:45:15.249 DEBUG  -  org.hibernate.loader.Loader              : Result set row: 9
09:45:15.249 DEBUG  -  org.hibernate.loader.Loader              : Result row: EntityKey[com.sct.entity.Axis#2]
09:45:15.249 DEBUG  -  o.h.engine.internal.TwoPhaseLoad         : Resolving attributes for [com.sct.entity.Axis#2]
09:45:15.249 DEBUG  -  o.h.engine.internal.TwoPhaseLoad         : Processing attribute `label` : value = Hokkaido
09:45:15.249 DEBUG  -  o.h.engine.internal.TwoPhaseLoad         : Attribute (`label`)  - enhanced for lazy-loading? -
false
09:45:15.249 DEBUG  -  o.h.engine.internal.TwoPhaseLoad         : Processing attribute `row` : value = 1
09:45:15.249 DEBUG  -  o.h.engine.internal.TwoPhaseLoad         : Attribute (`row`)  - enhanced for lazy-loading? - fa
lse
09:45:15.249 DEBUG  -  o.h.engine.internal.TwoPhaseLoad         : Done materializing entity [com.sct.entity.Axis#2]
09:45:15.250  INFO  -  com.sct.service.StatService              : axis_items=10
09:45:15.250  INFO  -  com.sct.service.StatService              : row=1, label=Hokkaido, hashCode=-1774477528
09:45:15.250  INFO  -  com.sct.service.StatService              : row=1, label=Hokkaido, hashCode=-1774477528
09:45:15.250  INFO  -  com.sct.service.StatService              : row=1, label=Hokkaido, hashCode=-1774477528
09:45:15.250  INFO  -  com.sct.service.StatService              : row=1, label=Hokkaido, hashCode=-1774477528
09:45:15.250  INFO  -  com.sct.service.StatService              : row=1, label=Hokkaido, hashCode=-1774477528
09:45:15.250  INFO  -  com.sct.service.StatService              : row=1, label=Hokkaido, hashCode=-1774477528
09:45:15.250  INFO  -  com.sct.service.StatService              : row=1, label=Hokkaido, hashCode=-1774477528
09:45:15.250  INFO  -  com.sct.service.StatService              : row=1, label=Hokkaido, hashCode=-1774477528
09:45:15.250  INFO  -  com.sct.service.StatService              : row=1, label=Hokkaido, hashCode=-1774477528
09:45:15.250  INFO  -  com.sct.service.StatService              : row=1, label=Hokkaido, hashCode=-1774477528
09:45:15.261 DEBUG  -  o.s.w.s.v.ContentNegotiatingViewResolver : Selected 'text/html' given [text/html, application/x
html xml, image/avif, image/webp, application/xml;q=0.9, */*;q=0.8]

Source Code

create table stat(
    id integer
    , row integer
    , col integer
    , data double precision not null
    , constraint stat_pk1 primary key (id, row, col)
);

create table axis_item(
    id integer
    , row integer
    , label varchar(30)
    , constraint axis_item_pk1 primary key (id, row)
);

create table graph(
    id integer
    , stat_id integer not null
    , col_axis_id integer
    , row_axis_id integer
    , o_label varchar(30)
    , caption varchar(60) not null
    , shape integer default 0 not null
    , constraint graph_pk1 primary key (id)
);

otnj=# SELECT DISTINCT a.id, a.row, a.label
    FROM axis_item a
    RIGHT JOIN stat s ON s.col = a.row
    WHERE a.id = (SELECT g.col_axis_id FROM graph g WHERE g.id = 2)
    ORDER BY a.row;
 id | row |       label
---- ----- -------------------
  2 |   1 | Hokkaido
  2 |   2 | Tohoku
  2 |   3 | Kanto
  2 |   4 | Hokuriku Shinetsu
  2 |   5 | Kinki
  2 |   6 | Chubu
  2 |   7 | Chugoku
  2 |   8 | Shikoku
  2 |   9 | Kyushu
  2 |  10 | Okinawa
(10 rows)

otnj=#

@Repository
public class QAxisRepository {
    @PersistenceContext
    private EntityManager em;
    
    public List<Axis> findColAndLabelById(Integer graphId) {
        
        JPQLQuery<Axis> query = new JPAQuery<Axis>(em, EclipseLinkTemplates.DEFAULT);
        QAxis axis = QAxis.axis;
        QStatCell statCell = QStatCell.statCell;
        QGraph graph = QGraph.graph;
        
        return query.
                from(axis).
                rightJoin(statCell).on(statCell.id().col.eq(axis.row)).
                where(axis.id.eq(
                        JPAExpressions.select(graph.colAxisId).from(graph).where(graph.id.eq(graphId))
                )).
                orderBy(axis.row.asc()).
                distinct().
                fetch();
    }
}

@Entity
@Table(name="axis_item")
public class Axis implements Serializable {
    @Id
    @Column(name="id")
    private Integer id;
    
    @Column(name="row")
    private Integer row;
    
    @Column(name="label")
    private String label;

// setters and getters

@Entity
@Table(name="stat")
public class StatCell implements Serializable {
    @EmbeddedId
    private StatCellId id;

    @Column(name = "data")
    private Double data;

// setters and getters

@Embeddable
public class StatCellId implements Serializable {
    @Column(name = "id")
    private Integer id;
    
    @Column(name = "row")
    private Integer row;
    
    @Column(name = "col")
    private Integer col;

// setters and getters

@Entity
@Table(name="graph")
public class Graph implements Serializable {
    @Id
    @Column(name="id")
    private Integer id;

    @Column(name="caption")
    private String caption;
    
    @Column(name="stat_id")
    private Integer statId;
    
    @Transient
    private List<List<Double>> matrix;
    
    @Column(name="o_label")
    private String oLabel;
    
    @Column(name="col_axis_id")
    private Integer colAxisId;
    
    @Transient
    private List<Axis> colAxis;

// setters and getters

    @Autowired(required=true)
    private QAxisRepository axisRepo;

    List<Axis> findColAndLabelById(Integer graphId) {
        List<Axis> axis = axisRepo.findColAndLabelById(graphId);
        log.info("axis_items={}", axis.size());
        for (Axis item : axis) {
            log.info("row={}, label={}, hashCode={}", item.getRow(), item.getLabel(), item.hashCode());
        }
        return axis;
    }

I tried JPQL many times, it occurred errors and I could not retrieve the result, that was why I use Query DSL.

@Repository
public interface AxisRepository extends JpaRepository<Axis, Integer> {
/*  @Query(value="SELECT DISTINCT a.id, a.row, a.label"
              "    FROM axis_item a"
              "    RIGHT JOIN stat s ON s.col = a.row"
              "    WHERE a.id = (SELECT g.col_axis_id FROM graph g WHERE g.id = ?1)"
              "    ORDER BY a.row"
            , nativeQuery=true)*/
    @Query(value="SELECT DISTINCT a.id, a.row, a.label"
              "    FROM Axis a"
              "    RIGHT JOIN StatCell s ON s.id.col = a.row"
              "    WHERE a.id = (SELECT g.colAxisId FROM Graph g WHERE g.id = ?1)"
              "    ORDER BY a.row")
    List<Axis> findColAndLabelById(Integer graphId);
}

Environment

Spring Tool Suite 4 Version: 4.13.0.RELEASE <querydsl.version>5.0.0</querydsl.version> PostgreSQL 12.8

CodePudding user response：

I use MyBatis because of this problem and the other inconveniences of Hibernate.