JPA 标准查询连接三个不可直接导航的表

我需要将此SQL查询转换为JPA标准：

SELECT tbl1.id_t1, tbl2.name, tbl3.name, tbl4.symbol, tbl1.limit, tbl1.value, tbl1.uncertainty 
FROM table_1 AS tbl1
JOIN table_2 AS tbl2 ON tbl2.id_t2=tbl1.id_t2
JOIN table_3 AS tbl3 ON tbl3.id_t3=tbl1.id_t3
JOIN table_4 AS tbl4 ON tbl4.id_t4=tbl1.id_t4
WHERE (tbl2.id_l=1 AND tbl3.id_l=1) AND tbl1.id_s=1;

我在 POJO 和数据库表之间的映射如下：

Table_1

@Entity
@Table("table_1")
public class Table1 {
 @Id
 @Column(name="id_t1")
 private Long idRowT1
 @ManyToOne
 @JoinColumn(name="id_t2")
 private Table2 tbl2;
 @ManyToOne
 @JoinColumn(name="id_t3")
 private Table3 tbl3;
 @ManyToOne
 @JoinColumn(name="id_t4")
 private Table4 tbl4;
 @Column(name="limit")
 private String limit;
 @Column(name="value")
 private String value;
 @Column(name="uncertainty")
 private String uncertainty;
 // getter and setter
}

Table_2

@Entity
@Table("table_2")
public class Table2 {
 @Id
 @Column(name="id_t2")
 private Long idT2;
 // getter and setter
}

Table_2_lang

@Entity
@Table("table_2_lang")
@IdClass(Table2LangPK.class)
public class Table2Lang {
 @Id
 @Column(name="id_t2")
 private Long idT2;
 @Id
 @Column(name="id_l")
 private Lang l;
 @Column(name="name")
 private String name;
 // getter and setter
}

Table_3

@Entity
@Table("table_3")
public class Table3 {
 @Id
 @Column(name="id_t3")
 private Long idT3;
 // getter and setter
}

Table_3_lang

@Entity
@Table("table_3_lang")
@IdClass(Table3LangPK.class)
public class Table3Lang {
 @Id
 @Column(name="id_t3")
 private Long idT3;
 @Id
 @Column(name="id_l")
 private Lang l;
 @Column(name="name")
 private String name;
 // getter and setter
}

Table_4

@Entity
@Table("table_4")
public class Table4 {
 @Id
 @Column(name="id_t4")
 private Long idT4;
 @Column(name="name")
 private String name;
 // getter and setter
}

为了将数据从业务层发送到前端，我使用了定义如下的值对象：

简单实体

public class SimpleEntityVO {
 private Long entityId;
 private String name;
 // getter and setter
}

复杂实体

public class SimpleEntityVO {
 private Long entityId;
 private SimpleEntityVO tbl2VO;
 private SimpleEntityVO tbl3VO;
 private SimpleEntityVO tbl4VO;
 // ... other field of table_1
 // getter and setter
}

在我的 EJB 中，我需要实现一个从 Table_1 开始返回 ComplexEntityVO 列表的方法

...
private CriteriaBuilder cB = eM.getCriteriaBuilder();
public List<ComplexEntityVO> findAll(Long id_s, Long id_l) {
 CriteriaQuery<ComplexEntityVO> cQ = cB.createQuery(ComplexEntityVO.class)
 Root<Table1> tbl1Root = cQ.from(Table1.class);
 // UPDATE BEGIN
 Root<Table2Lang> tbl2Root = cQ.from(Table2Lang.class);
 ...
 Selection<SimpleEntityVO> sESTbl2 = cB.construct(SimpleEntityVO.class, tbl2Root.get(Table2Lang_.id_t2), tbl2Root.get(Table2Lang_.name));
  // The selection for table_3_lang and table_4 are the same
 // UPDATE END
 TypedQuery<ComplexEntityVO> tQ = eM.createQuery(cQ);
}
...

为了达到我尝试在 Table1 和 Table2Lang 之间连接的结果，尝试使用如下所示

的选择

`Selection<SimpleEntityVO> sES = cB.construct(SimpleEntityVO.class, ...);`

使用

根作为 lang 表，尝试使用此处公开的解决方案

https://community.oracle.com/message/10795956#10795956

但是当我尝试执行此语句时

`cQ.select(cB.construct(ComplexEntityVO.class, id_t1, SimpleEntityVO)`

或者这个

`cQ.multiselect(...)`

我得到：IllegalArgumentException

Caused by: org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: , near line 1, column 64
[select new com.example.vo.ComplexEntityVO(generatedAlias0.id_t1,
 new com.example.labims.vo.SimpleEntityVO(generatedAlias1.table2.id_t2, generatedAlias1.name),
 new com.example.vo.SimpleEntityVO(generatedAlias2.table_3.id_t3, generatedAlias2.name),
 new com.example.vo.SimpleEntityVO(generatedAlias3.id_t4, generatedAlias3.name),
 generatedAlias0.limit, generatedAlias0.value, generatedAlias0.uncertainty)
 from com.example.Table1 as generatedAlias0, 
 com.example.model.Table2Lang as generatedAlias1, 
 com.example.model.Table3Lang as generatedAlias2,
 com.example.model.Table4 as generatedAlias3
 where ( generatedAlias0.id_s=:param0 ) and ( ( generatedAlias1.lang.id_l=:param1 ) and ( generatedAlias2.lang.id_l=:param1 ) )]

从执行的原因了解到我无法在select或multiselect语句中实例化新对象，但我找不到使用标准 API 实现原始 SQL 查询的方法。

更新我添加了一段摘录，说明我试图在//UPDATE BEGIN和//UPDATE END之间实现的结果

我认为让休眠显示sql ==true并通过控制台进行查询，测试显示查询您的数据库并发现错误 hbernate 未生成正确的查询

有两种方法可以解决这个问题。

将构造函数方法添加到 ComplexEntityVO，如下所示：

public ComplexEntityVO(Long id, Long simpleId2, String simpleName2 /*     etc ... */) {
    this.simpleEntityVo = new SimpleEntityVO(simpleId2, simpleName2);
    // etc for other associations
}

将 ProjectionList 添加到查询中，返回List<Object[]>而不是List<ComplexEntityVO>，然后像这样迭代结果

for(Object[] o: results) {
    ComplexEntityVO cvo = new ComplexEntityVO((Long)o[0]);
    new SimpleEntityVO vo2 = new SimpleEntityVO((Long) o[1], (String) o[2]);
    cvo.setTbl2VO(vo2);
    // ... etc for other associations
}

虽然第二个更丑陋，但我更喜欢它，因为它更灵活，并且允许更多调试、日志记录等机会。

请参阅 AliasToBeanResultTransformer(MyDTO.class( 无法实例化 MyDTO

相关内容

最新更新

热门标签：