前提・実現したいこと
Spring Bootでselect distinct row from stat where id = ? order by row
同等機能を実装し取得したrowがなぜか同じ値になりました。
どう対処すればよいのか、ご教授お願い致します。
発生している問題・ログメッセージ
2021-12-19 08:50:39.455 DEBUG 32664 --- [nio-8081-exec-1] org.hibernate.SQL : select distinct statrow0_.id as id2_0_, statrow0_.row as row3_0_ from stat statrow0_ where statrow0_.id=? order by statrow0_.row asc Hibernate: select distinct statrow0_.id as id2_0_, statrow0_.row as row3_0_ from stat statrow0_ where statrow0_.id=? order by statrow0_.row asc 2021-12-19 08:50:39.459 TRACE 32664 --- [nio-8081-exec-1] o.h.type.descriptor.sql.BasicBinder : binding parameter [1] as [INTEGER] - [1] 2021-12-19 08:50:40.434 DEBUG 32664 --- [nio-8081-exec-1] org.hibernate.loader.Loader : Result set row: 0 2021-12-19 08:50:40.447 DEBUG 32664 --- [nio-8081-exec-1] org.hibernate.loader.Loader : Result row: EntityKey[com.sct.entity.StatRow#component[id]{id=1}] 2021-12-19 08:50:40.451 DEBUG 32664 --- [nio-8081-exec-1] org.hibernate.loader.Loader : Result set row: 1 2021-12-19 08:50:40.451 DEBUG 32664 --- [nio-8081-exec-1] org.hibernate.loader.Loader : Result row: EntityKey[com.sct.entity.StatRow#component[id]{id=1}] 2021-12-19 08:50:40.452 DEBUG 32664 --- [nio-8081-exec-1] org.hibernate.loader.Loader : Result set row: 2 中略 2021-12-19 08:50:40.456 DEBUG 32664 --- [nio-8081-exec-1] org.hibernate.loader.Loader : Result set row: 56 2021-12-19 08:50:40.456 DEBUG 32664 --- [nio-8081-exec-1] org.hibernate.loader.Loader : Result row: EntityKey[com.sct.entity.StatRow#component[id]{id=1}] 2021-12-19 08:50:40.456 DEBUG 32664 --- [nio-8081-exec-1] org.hibernate.loader.Loader : Result set row: 57 2021-12-19 08:50:40.456 DEBUG 32664 --- [nio-8081-exec-1] org.hibernate.loader.Loader : Result row: EntityKey[com.sct.entity.StatRow#component[id]{id=1}] 2021-12-19 08:50:40.460 DEBUG 32664 --- [nio-8081-exec-1] o.h.engine.internal.TwoPhaseLoad : Resolving attributes for [com.sct.entity.StatRow#component[id]{id=1}] 2021-12-19 08:50:40.460 DEBUG 32664 --- [nio-8081-exec-1] o.h.engine.internal.TwoPhaseLoad : Processing attribute `row` : value = 1964 2021-12-19 08:50:40.460 DEBUG 32664 --- [nio-8081-exec-1] o.h.engine.internal.TwoPhaseLoad : Attribute (`row`) - enhanced for lazy-loading? - false 2021-12-19 08:50:40.461 DEBUG 32664 --- [nio-8081-exec-1] o.h.engine.internal.TwoPhaseLoad : Done materializing entity [com.sct.entity.StatRow#component[id]{id=1}] 2021-12-19 08:50:40.464 INFO 32664 --- [nio-8081-exec-1] com.sct.service.StatRowService : row=1964 2021-12-19 08:50:40.464 INFO 32664 --- [nio-8081-exec-1] com.sct.service.StatRowService : row=1964 2021-12-19 08:50:40.464 INFO 32664 --- [nio-8081-exec-1] com.sct.service.StatRowService : row=1964 2021-12-19 08:50:40.464 INFO 32664 --- [nio-8081-exec-1] com.sct.service.StatRowService : row=1964 中略 2021-12-19 08:50:40.464 INFO 32664 --- [nio-8081-exec-1] com.sct.service.StatRowService : row=1964 2021-12-19 08:50:40.464 INFO 32664 --- [nio-8081-exec-1] com.sct.service.StatRowService : row=1964 2021-12-19 08:50:40.470 DEBUG 32664 --- [nio-8081-exec-1] o.s.w.s.v.ContentNegotiatingViewResolver : Selected 'text/html' given [text/html, application/xhtml+xml, image/avif, image/webp, application/xml;q=0.9, */*;q=0.8] 2021-12-19 08:50:41.470 DEBUG 32664 --- [nio-8081-exec-1] o.s.web.servlet.DispatcherServlet : Completed 200 OK
該当のソースコード
SQL
1create table stat( 2 id integer 3 , row integer 4 , col integer 5 , data double precision not null 6 , constraint stat_pk1 primary key (id, row, col) 7)
Java
1package com.sct.entity; 2 3import java.io.Serializable; 4 5import javax.persistence.Column; 6import javax.persistence.Embeddable; 7 8@Embeddable 9public class StatId implements Serializable { 10 11 private static final long serialVersionUID = 1289075301160465252L; 12 13 @Column(name = "id") 14 private Integer id; 15 16 public Integer getId() { 17 return id; 18 } 19 20 public void setId(Integer id) { 21 this.id = id; 22 } 23}
Java
1package com.sct.entity; 2 3import java.io.Serializable; 4 5import javax.persistence.Column; 6import javax.persistence.EmbeddedId; 7import javax.persistence.Entity; 8import javax.persistence.Table; 9 10@Entity 11@Table(name="stat") 12public class StatRow implements Serializable { 13 14 private static final long serialVersionUID = 1289075301160465252L; 15 16 @EmbeddedId 17 private StatId id; 18 19 @Column(name = "row") 20 private Integer row; 21 22 public StatId getId() { 23 return id; 24 } 25 26 public void setId(StatId id) { 27 this.id = id; 28 } 29 30 public Integer getRow() { 31 return row; 32 } 33 34 public void setRow(Integer row) { 35 this.row = row; 36 } 37}
Java
1package com.sct.repository; 2 3import java.util.List; 4 5import org.springframework.data.jpa.repository.JpaRepository; 6import org.springframework.stereotype.Repository; 7 8import com.sct.entity.StatId; 9import com.sct.entity.StatRow; 10 11@Repository 12public interface StatRowRepository extends JpaRepository<StatRow, StatId> { 13 List<StatRow> findDistinctRowByIdOrderByRow(StatId Id); 14}
Java
1package com.sct.service; 2 3import java.util.ArrayList; 4import java.util.List; 5 6import org.slf4j.Logger; 7import org.slf4j.LoggerFactory; 8import org.springframework.beans.factory.annotation.Autowired; 9import org.springframework.stereotype.Service; 10 11import com.sct.entity.StatId; 12import com.sct.entity.StatRow; 13import com.sct.repository.StatRowRepository; 14 15@Service 16public class StatRowService { 17 static protected final Logger log = LoggerFactory.getLogger(StatRowService.class); 18 19 @Autowired 20 private StatRowRepository rowRepo; 21 22 public List<Integer> findAllRows(Integer id) { 23 List<Integer> rows = new ArrayList<>(); 24 25 StatId statId = new StatId(); 26 statId.setId(id); 27 28 for (StatRow statRow : rowRepo.findDistinctRowByIdOrderByRow(statId)) { 29 Integer row = statRow.getRow(); 30 log.info("row={}", row); 31 rows.add(row); 32 } 33 34 return rows; 35 } 36}
Java
1package com.sct.controller; 2 3import java.io.File; 4import java.io.IOException; 5import java.util.List; 6 7import org.springframework.beans.factory.annotation.Autowired; 8import org.springframework.stereotype.Controller; 9import org.springframework.ui.Model; 10import org.springframework.web.bind.annotation.GetMapping; 11 12import com.sct.service.StatRowService; 13 14@Controller 15public class StatController { 16 @Autowired 17 private StatRowService rowService; 18 19 @GetMapping(value = "/stat") 20 public String displayList(Model model) { 21 List<Integer> rowList = rowService.findAllRows(1); 22 model.addAttribute("rowList", rowList); 23 return "stat"; 24 } 25}
試したこと
Hibernateで生成されたSQL(?を1に置き換えて)をpsqlで実行すると期待した値が取得できました。
bash
1$ psql -d 'postgresql://localhost:5432/otnj' 2psql (12.8) 3Type "help" for help. 4 5otnj=# select 6 distinct statrow0_.id as id2_0_, 7 statrow0_.row as row3_0_ 8 from 9 stat statrow0_ 10 where 11 statrow0_.id=1 12 order by 13 statrow0_.row; 14 15 id2_0_ | row3_0_ 16--------+--------- 17 1 | 1964 18 1 | 1965 19 1 | 1966 20 21中略 22 23 1 | 2020 24 1 | 2021 25(58 rows) 26 27otnj=#
補足情報(FW/ツールのバージョンなど)
sts-4.13.0
PostgreSQL 12.8
回答1件
あなたの回答
tips
プレビュー
バッドをするには、ログインかつ
こちらの条件を満たす必要があります。
2021/12/19 04:32
2021/12/19 09:56
2021/12/19 09:56
2021/12/19 10:04
2021/12/19 19:59
2021/12/19 21:01
2021/12/19 21:23
2021/12/19 21:48
2021/12/19 22:24
2021/12/19 22:28
2021/12/19 23:15
2021/12/20 11:46
2021/12/21 07:57