I implemented the function as "select * from stat where id = ?" with Spring Boot; JPA method findAllById(), but it returns all equivalent records. Please show me how I should fix the code.
create table stat(
id integer
, row integer
, col integer
, data double precision not null
, constraint stat_pk1 primary key (id, row, col)
)
otnj=# select * from stat;
id | row | col | data
---- ------ ----- ----------
1 | 1964 | 1 | 352832
1 | 1965 | 1 | 366649
1 | 1966 | 1 | 432937
...
1 | 2020 | 1 | 4115828
1 | 2021 | 1 | 173263
(58 rows)
package com.sct.entity;
import java.io.Serializable;
import java.util.Objects;
import javax.persistence.Column;
import javax.persistence.Embeddable;
@Embeddable
public class StatId implements Serializable {
private static final long serialVersionUID = 1289075301160465252L;
@Column(name = "id")
private Integer id;
public StatId() {
}
@Override
public int hashCode() {
return Objects.hash(id);
}
@Override
public boolean equals(Object obj) {
if (this == obj)
return true;
if (obj == null)
return false;
if (getClass() != obj.getClass())
return false;
StatId other = (StatId) obj;
return Objects.equals(id, other.id);
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
}
package com.sct.entity;
import java.io.Serializable;
import java.util.Objects;
import javax.persistence.Column;
import javax.persistence.EmbeddedId;
import javax.persistence.Entity;
import javax.persistence.Table;
@Entity
@Table(name="stat")
public class StatCell implements Serializable {
private static final long serialVersionUID = 6501935237628672264L;
@EmbeddedId
private StatId id;
@Column(name = "row")
private Integer row;
@Column(name = "col")
private Integer col;
@Column(name = "data")
private Double data;
public StatCell() {
}
@Override
public int hashCode() {
return Objects.hash(col, data, id, row);
}
@Override
public boolean equals(Object obj) {
if (this == obj)
return true;
if (obj == null)
return false;
if (getClass() != obj.getClass())
return false;
StatCell other = (StatCell) obj;
return Objects.equals(col, other.col) && Objects.equals(data, other.data) && Objects.equals(id, other.id)
&& Objects.equals(row, other.row);
}
// getters and setters
}
package com.sct.repository;
import java.util.List;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;
import com.sct.entity.StatCell;
import com.sct.entity.StatId;
@Repository
public interface StatCellRepository extends JpaRepository<StatCell, StatId> {
List<StatCell> findAll();
@Query(value="SELECT s FROM StatCell s WHERE id = ?1")
List<StatCell> findAllById(@Param("id") Iterable<StatId> ids);
}
package com.sct.service;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import com.sct.entity.StatCell;
import com.sct.entity.StatCellId;
import com.sct.entity.StatId;
import com.sct.repository.StatCellRepository;
@Service
public class StatService {
protected static final Logger log = LoggerFactory.getLogger(StatService.class);
@Autowired(required=true)
private StatCellRepository repo;
public List<StatCell> findAllById(Integer id) {
StatId statId = new StatId();
statId.setId(id);
List<StatId> statIds = new ArrayList<>();
statIds.add(statId);
List<StatCell> cells = repo.findAllById(statIds);
log.info("cells={}", cells.size());
return cells;
}
public Map<StatCellId, Double> findByIdAsMap(Integer id) {
Map<StatCellId, Double> map = new HashMap<>();
for (StatCell cell : findAllById(id)) {
StatCellId cellId = new StatCellId();
cellId.setId(id);
cellId.setCol(cell.getCol());
cellId.setRow(cell.getRow());
Double data = cell.getData();
log.info("col={}, row={}, data={}, hashCode={}",
cell.getCol(), cell.getRow(), data, cellId.hashCode());
map.put(cellId, data);
}
log.info("map.size={}", map.size());
return map;
}
}
2021-12-22 02:32:08.663 DEBUG 37572 --- [nio-8081-exec-1] org.hibernate.SQL :
select
statcell0_.id as id1_0_,
statcell0_.col as col2_0_,
statcell0_.data as data3_0_,
statcell0_.row as row4_0_
from
stat statcell0_
where
statcell0_.id=?
Hibernate:
select
statcell0_.id as id1_0_,
statcell0_.col as col2_0_,
statcell0_.data as data3_0_,
statcell0_.row as row4_0_
from
stat statcell0_
where
statcell0_.id=?
2021-12-22 02:32:08.663 TRACE 37572 --- [nio-8081-exec-1] o.h.type.descriptor.sql.BasicBinder : binding parameter [1] as [INTEGER] - [1]
2021-12-22 02:32:08.665 DEBUG 37572 --- [nio-8081-exec-1] org.hibernate.loader.Loader : Result set row: 0
2021-12-22 02:32:08.666 DEBUG 37572 --- [nio-8081-exec-1] org.hibernate.loader.Loader : Result row: EntityKey[com.sct.entity.StatCell#component[id]{id=1}]
2021-12-22 02:32:08.671 DEBUG 37572 --- [nio-8081-exec-1] org.hibernate.loader.Loader : Result set row: 1
2021-12-22 02:32:08.671 DEBUG 37572 --- [nio-8081-exec-1] org.hibernate.loader.Loader : Result row: EntityKey[com.sct.entity.StatCell#component[id]{id=1}]
...
2021-12-22 02:32:08.675 DEBUG 37572 --- [nio-8081-exec-1] org.hibernate.loader.Loader : Result set row: 56
2021-12-22 02:32:08.675 DEBUG 37572 --- [nio-8081-exec-1] org.hibernate.loader.Loader : Result row: EntityKey[com.sct.entity.StatCell#component[id]{id=1}]
2021-12-22 02:32:08.675 DEBUG 37572 --- [nio-8081-exec-1] org.hibernate.loader.Loader : Result set row: 57
2021-12-22 02:32:08.675 DEBUG 37572 --- [nio-8081-exec-1] org.hibernate.loader.Loader : Result row: EntityKey[com.sct.entity.StatCell#component[id]{id=1}]
2021-12-22 02:32:08.676 DEBUG 37572 --- [nio-8081-exec-1] o.h.engine.internal.TwoPhaseLoad : Resolving attributes for [com.sct.entity.StatCell#component[id]{id=1}]
2021-12-22 02:32:08.676 DEBUG 37572 --- [nio-8081-exec-1] o.h.engine.internal.TwoPhaseLoad : Processing attribute `col` : value = 1
2021-12-22 02:32:08.676 DEBUG 37572 --- [nio-8081-exec-1] o.h.engine.internal.TwoPhaseLoad : Attribute (`col`) - enhanced for lazy-loading? - false
2021-12-22 02:32:08.676 DEBUG 37572 --- [nio-8081-exec-1] o.h.engine.internal.TwoPhaseLoad : Processing attribute `data` : value = 352832.0
2021-12-22 02:32:08.676 DEBUG 37572 --- [nio-8081-exec-1] o.h.engine.internal.TwoPhaseLoad : Attribute (`data`) - enhanced for lazy-loading? - false
2021-12-22 02:32:08.676 DEBUG 37572 --- [nio-8081-exec-1] o.h.engine.internal.TwoPhaseLoad : Processing attribute `row` : value = 1964
2021-12-22 02:32:08.676 DEBUG 37572 --- [nio-8081-exec-1] o.h.engine.internal.TwoPhaseLoad : Attribute (`row`) - enhanced for lazy-loading? - false
2021-12-22 02:32:08.677 DEBUG 37572 --- [nio-8081-exec-1] o.h.engine.internal.TwoPhaseLoad : Done materializing entity [com.sct.entity.StatCell#component[id]{id=1}]
2021-12-22 02:32:08.677 DEBUG 37572 --- [nio-8081-exec-1] o.h.e.t.internal.TransactionImpl : committing
2021-12-22 02:32:08.678 INFO 37572 --- [nio-8081-exec-1] com.sct.service.StatService : cells=58
2021-12-22 02:32:08.679 INFO 37572 --- [nio-8081-exec-1] com.sct.service.StatService : col=1, row=1964, data=352832.0, hashCode=32747
2021-12-22 02:32:08.679 INFO 37572 --- [nio-8081-exec-1] com.sct.service.StatService : col=1, row=1964, data=352832.0, hashCode=32747
2021-12-22 02:32:08.679 INFO 37572 --- [nio-8081-exec-1] com.sct.service.StatService : col=1, row=1964, data=352832.0, hashCode=32747
In interface StatCellRepository, whether I omit @Query or not, Iterable<> or not, the result: every record (col, row, data) is always same. What happens on earth?
CodePudding user response：
If you want to get different values, you need:
- move the attributes "row" and "col" from StatCell to StatId and change the equals and hashcode methods in these entities;
- in the repository use method like this:
List findAllByIdId(Integer id);
I think you get the same values because entity StatId only contains "id". This means that only one row should have a unique value "id". If you have used a constraint key for the table(id, row, col), then you must use all the key attributes in your EmbaddedId.