我想创建一个用于连接三个表的条件构建器查询。我已经研究了互联网,但是三个表连接的所有示例都具有与我想要的关系不同的关系。所以我有三个具有以下结构的表:
实体如下:
@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;
}
现在我要
获取名为Mike的员工所在部门的联系方式 当前发布在标题下的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.
语法异常期间生成的休眠查询为:
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)