如何从Hibernate的自引用实体中获取数据

Question

我有两个表:

1. medicaments_group输入图片描述
2. medicaments_group_join输入图片描述

这是我的实体类:

@Entity @Table(name = "medicaments_group") @Getter @Setter public class MedicamentGroup extends GenericDictionary {
@Id
private Long id;
private boolean groupMain;
@ManyToMany
@JoinTable(
name = "medicaments_group_join",
joinColumns = @JoinColumn(name = "medicament_group_id"),
inverseJoinColumns = @JoinColumn(name = "medicament_join_id")
)
private List<MedicamentGroup> childrens = new ArrayList<>();
@ManyToMany
@JoinTable(
name = "medicaments_group_join",
joinColumns = @JoinColumn(name = "medicament_join_id"),
inverseJoinColumns = @JoinColumn(name = "medicament_group_id")
)
private List<MedicamentGroup> childrenOf; }

我想返回MedicamentGroup的列表及其子列表，所以我写了这样的查询:

@Override
public List<MedicamentGroup> getGroupsAndItsChildren() {
List<Tuple> query = Objects.requireNonNull(getQuerydsl())
.createQuery()
.from(medicamentGroup)
.select(id, shortName, childrens)
.where(groupMain.isFalse())
.fetch();

return query.stream().map(tuple -> {
MedicamentGroup group = new MedicamentGroup();
group.setId(tuple.get(id));
group.setShortName(tuple.get(shortName));
group.setChildrens(tuple.get(childrens));
return group;
}).toList();
}

现在，当我尝试从我的存储库层调用这个方法时，我得到了一个错误:

Hibernate: select medicament0_。Id为col_0_0_， medicament0_.short_name[qh]就像药物治疗一样。我是id1_20_，medicament2_。Active为active2_20_， medicament2_.name为name3_20_，medicament2_。Short_name为short_na4_20_， medicament2_。group_main作为Group_ma5_20_从medicaments_group medicament0_内部连接Medicaments_group_join childrens1_ onmedicament0_.id = childrens1_。Medicament_group_id内连接Medicaments_group medicament_onchildrens1_.medicament_join_id = medicament2_。身份证的地方medicament0_.group_main = ?2023-04-03 20:12:23.563警告46674——[nio-8089- exec3] .h.engine.jdbc.spi. sqlexceptionhelperError: 0, SQLState: 42601 2023-04-03 20:12:23.563 Error 46674——[nio-8089- exec3] .h.engine.jdbc.spi. sqlexceptionhelper:错误:" "处或附近的语法错误位置:74 2023-04-03 20:12:23.579ERROR 46674—[nio-8089- exec3] p.k.c.w.f.ErrorResponseFactoryImpl
:错误响应

org.springframework.dao。InvalidDataAccessResourceUsageException:不提取ResultSet;SQL (n/a);嵌套异常是org.hibernate.exception. sqlgrammareexception:无法提取结果集在org.springframework.orm.jpa.vendor.HibernateJpaDialect.convertHibernateAccessException (HibernateJpaDialect.java: 259)~ (spring-orm-5.3.22.jar: 5.3.22]org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible (HibernateJpaDialect.java: 233)~ (spring-orm-5.3.22.jar: 5.3.22]org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible (AbstractEntityManagerFactoryBean.java: 551)~ (spring-orm-5.3.22.jar: 5.3.22]org.springframework.dao.support.ChainedPersistenceExceptionTranslator.translateExceptionIfPossible (ChainedPersistenceExceptionTranslator.java: 61)~ (spring-tx-5.3.22.jar: 5.3.22]org.springframework.dao.support.DataAccessUtils.translateIfNecessary (DataAccessUtils.java: 242)~ (spring-tx-5.3.22.jar: 5.3.22]org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke (PersistenceExceptionTranslationInterceptor.java: 152)~ (spring-tx-5.3.22.jar: 5.3.22]org.springframework.aop.framework.ReflectiveMethodInvocation.proceed (ReflectiveMethodInvocation.java: 186)~ (spring-aop-5.3.22.jar: 5.3.22]org.springframework.aop.framework.CglibAopProxy CglibMethodInvocation.proceed美元(CglibAopProxy.java: 763)~ (spring-aop-5.3.22.jar 5.3.22):

所以你能帮我，告诉我我的代码有什么问题吗?

Answer 1

在您的实体类中这样做映射:

@Entity @Table(name = "medicaments_group") @Getter @Setter public class MedicamentGroup extends GenericDictionary {
@Id
private Long id;
private boolean groupMain;
@ManyToMany(cascade = CascadeType.ALL)
private List<MedicamentGroup> childrens = new ArrayList<>();
@ManyToMany(cascade = CascadeType.ALL)
private List<MedicamentGroup> childrenOf; }

另外，尝试删除数据库，然后创建一个新数据库。