java如何创建Criteria Builder查询来连接三个具有一对一和多对一实体关系的表?
我想创建一个用于连接三个表的criteria builder查询。我研究过互联网,但所有三个表连接的例子都有不同于我想要的关系。因此,我有三个表具有以下结构:
以及以下实体:
@Entity
@Table(name = "DEPARTMENT")
@Data
public class Department {
@Id
@GeneratedValue(generator = "uuid")
@Column(unique = true, length = 36)
private String id;
@NotNull
@Column(name = "NAME")
private String name;
@ManyToOne(fetch = FetchType.EAGER)
@JoinColumn(name = "HEAD_ID")
private Head head;
}
@Entity
@Table(name = "EMPLOYEES")
@Data
public class Employees {
@Id
@GeneratedValue(generator = "uuid")
@Column(unique = true, length = 36)
private String id;
@NotNull
@Column(name = "EMP_NAME")
private String empName;
@Column(name = "RESUME")//clob
private String resume;
@ManyToOne(fetch = FetchType.EAGER)
@JoinColumn(name = "DEPT_ID")
private Department department;
}
@Entity
@Table(name = "CONTACT")
@Data
public class Contact {
@Id
@GeneratedValue(generator = "uuid")
@Column(unique = true, length = 36)
private String id;
@NotNull
@Column(name = "NUMBER")
private Long number;
@NotNull
@Column(name = "CITY")
private String city;
@Column(name = "ADDRESS")//clob
private String address;
@OneToOne(fetch = FetchType.EAGER)
@JoinColumn(name = "DEPT_ID")
private Department department;
}
现在我想
Get the contact of department in which an employee named Mike is currently posted under the head has id=1234.
我尝试了以下问题
CriteriaBuilder builder = getCriteriaBuilder();
CriteriaQuery<Contact> criteria = builder.createQuery(Contact.class);
Root<Contact> contactRoot = criteria.from(Contact.class);
Root<Employees> employeesRoot = criteria.from(Employees.class);
//Join<Employees, Department> employeesDepartmentJoin = contactRoot.join("department", JoinType.INNER); With this statement#: Throws java.lang.IllegalArgumentException: Unable to locate Attribute with the the given name [empName] on this ManagedType [org.sample.something.Contact] exception.
Join<Employees, Department> employeesDepartmentJoin = employeesRoot.join("department", JoinType.INNER);
Join<Contact, Department> contactDepartmentJoin = contactRoot.join("department", JoinType.INNER);
List predicates = new ArrayList<Predicate>();
predicates.add(builder.equal(employeesDepartmentJoin.get("head").get("id"), headID));
predicates.add(builder.equal(employeesDepartmentJoin.getParent().get("empName"), empName));
criteria.where(builder.and((Predicate[]) predicates.toArray(new Predicate[0])));
criteria.select(contactRoot).distinct(true);
return getEm().createQuery(criteria).getResultList(); //Without statement#: Throws org.hibernate.exception.SQLGrammarException: could not extract ResultSet exception.
语法异常期间生成的hibernate查询是:
select distinct contact0_.id as id1_47_, contact0_.number as number_id9_47_, contact0_.city as city10_47_, contact0_.address as address10_47_, contact0_.department_id as department11_47_
from sample.contact contact0_
inner join sample.department department2_ on contact0_.department_id=department2_.id
cross join sample.employees employees1_
inner join sample.department department3_ on employees1_.department_id=department3_.id
where department3_.head_id=? and employees1_.employee_name=?
任何能照亮它的人都会很有帮助。谢谢
编辑:使用新字段和SQLGrammarException的StackTrace更新了代码(第一个例外是“无法定位属性…”在求值时抛出,否则会抛出相同的SQLGrammarException)
Caused by: javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not extract ResultSet
at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:154)
at org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1514)
at org.hibernate.query.Query.getResultList(Query.java:132)
at org.hibernate.query.criteria.internal.compile.CriteriaQueryTypeQueryAdapter.getResultList(CriteriaQueryTypeQueryAdapter.java:74)
at org.sample.something.dao.ContactDAO.getContactByHeadAndEmployeeName(ContactDAO.java:102)
... 61 common frames omitted
Caused by: org.hibernate.exception.SQLGrammarException: could not extract ResultSet
at org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:63)
at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:42)
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:113)
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:99)
at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:69)
at org.hibernate.loader.Loader.getResultSet(Loader.java:2167)
at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1930)
at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1892)
at org.hibernate.loader.Loader.doQuery(Loader.java:937)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:340)
at org.hibernate.loader.Loader.doList(Loader.java:2689)
at org.hibernate.loader.Loader.doList(Loader.java:2672)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2506)
at org.hibernate.loader.Loader.list(Loader.java:2501)
at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:504)
at org.hibernate.hql.internal.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:395)
at org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:220)
at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1508)
at org.hibernate.query.internal.AbstractProducedQuery.doList(AbstractProducedQuery.java:1537)
at org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1505)
... 64 common frames omitted
Caused by: java.sql.SQLSyntaxErrorException: ORA-00932: inconsistent datatypes: expected - got CLOB
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:450)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:399)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1017)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:655)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:249)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:566)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:215)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:58)
at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:776)
at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:897)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1034)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3820)
at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3867)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1502)
at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52)
at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeQuery(HikariProxyPreparedStatement.java)
共 (0) 个答案