前提・実現したいこと
Spring Bootで、サブクエリや結合を使ったクエリを書いて実行してみたところ、SQLでは正しい異なる値が得られますが、Query DSLでは、同じ値のレコード（件数はOK）しか得られません。Hibernate loaderが変な気がします。どうすれば良いでしょうか？
発生している問題・ログメッセージ
2022-01-05 09:45:15.244 DEBUG 23996 --- [nio-8081-exec-1] org.hibernate.SQL : 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 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]
該当のソースコード
sql
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) );
bash
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=#
Java
@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(); } }
Java
@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
Java
@Entity @Table(name="stat") public class StatCell implements Serializable { @EmbeddedId private StatCellId id; @Column(name = "data") private Double data; // setters and getters
Java
@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
Java
@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
Java
@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; }
試したこと
JPQLでも何度も試してみましたが、エラーが出て結果が得られなかったため、最終的にQuery DSLを使うことにしました。
Java
@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); }
補足情報（FW/ツールのバージョンなど）
Spring Tool Suite 4 Version: 4.13.0.RELEASE
<querydsl.version>5.0.0</querydsl.version>
PostgreSQL 12.8
まだ回答がついていません
会員登録して回答してみよう
