We encountered a problem when using Criteria in Hibernate 6 and an Oracle database. Due to the fact that the query contains fields of the same name in different tables, the query without explicit aliases for the fields returns an error on the Oracle side ``` ORA-00918: column ambiguously defined ``` It was not possible to explicitly add aliases to the Hibernate-generated SQL query in this way ``` query.multiselect( from.get(“id”), group.get(“name”).alias(“gk”), group.get(“id”), branch.get(“name”).alias(“branch”), gsz.get(“name”).alias(“gsz”) ); ``` From the logs it is clear that Hibernate generates the following query ``` Hibernate: select ro1_0.“ORGID”, g1_0.“NAME”, o1_0.“X_GK_ID”, rb1_0.“NAME”, lt1_0.”NAME” ``` but the Oracle database requires explicit aliases for the query to be executed successfully, that is, we need to get a generated query like this ``` Hibernate: select ro1_0.“ORGID”, g1_0.“NAME” AS gk, o1_0.“X_GK_ID”, rb1_0.“NAME” AS branch, lt1_0.”NAME” AS gsz ``` as far as I could find out, in Hibernate 6, aliases were removed from the generated query for optimization purposes, it was not possible to find a solution on how to explicitly tell Hibernate to generate a SQL query with aliases for fields in the selection, maybe someone can suggest if there are any available solutions to solve this problem?
What you’re posting here doesn’t make any sense. We run tests against Oracle and don’t have this problem.
If you seek help, please post the versions of the software you’re using and the JPA Criteria query, as well as the full stack trace of the error, along with the generated SQL query.
class in which the request for Criteria is formed
public abstract class AbstractOrganizationCustomRepository<T> {
public static final String PROBLEM_FIELD = "problem";
public static final String FULL_NAME_FIELD = "fullName";
public static final String SHORT_NAME_FIELD = "shortName";
public static final String VALUE_FIELD = "value";
public static final String OKOPF_FIELD = "okopf";
protected Root<T> root;
protected CriteriaBuilder cb;
protected CriteriaQuery<OrganizationInfo> query;
protected Join<Object, Object> organization;
protected Join<Object, Object> lovOkopf;
protected Join<Object, Object> lovType;
protected Join<Object, Object> lovStatus;
protected Join<Object, Object> lovSegment;
protected Join<Object, Object> accountPositions;
protected Join<Object, Object> position;
protected Join<Object, Object> contact;
protected Join<Object, Object> extension;
protected Join<Object, Object> group;
protected Join<Object, Object> industry;
protected Join<Object, Object> businessUnit;
protected Join<Object, Object> branch;
protected ListJoin<Object, Object> gskMembers;
protected ListJoin<Object, Object> gszMembers;
protected ListJoin<Object, Object> gksMembers;
protected Join<Object, Object> gsk;
protected Join<Object, Object> gsz;
protected Join<Object, Object> gks;
protected abstract <X, Y> Expression<Object> getAreaResponsibility(CriteriaBuilder cb, From<X, Y> from);
protected abstract <X, Y> Expression<Object> getMfh(CriteriaBuilder cb, From<X, Y> from);
protected abstract Expression<Object> getGks();
protected abstract <X, Y> List<Predicate> getStaticPredicates(From<X, Y> from);
protected abstract <X, Y> List<Predicate> getSpecificDynamicPredicates(OrganizationsWrapperRequestDto request, CriteriaBuilder cb, From<X, Y> from);
protected abstract TypedQuery<OrganizationInfo> createQueryWithPaging(PageRequestDto pageDto);
protected List<OrganizationInfo> findAll(OrganizationsWrapperRequestDto request) {
joinTables(root);
buildSelect(root);
applyFiltering(request, root);
applySorting(request, root);
return createQueryWithPaging(request.page())
.getResultList();
}
protected <X, Y> void joinTables(From<X, Y> from) {
lovOkopf = from.join("lovOkopf", LEFT);
lovOkopf.alias("lovOkopf");
lovType = from.join("lovType", LEFT);
lovType.alias("lovType");
lovStatus = from.join("lovStatus", LEFT);
lovStatus.alias("lovStatus");
lovSegment = from.join("lovSegment", LEFT);
lovSegment.alias("lovSegment");
accountPositions = from.joinList("accountPositions", INNER);
position = accountPositions.join("position", LEFT);
contact = position.join("contact", LEFT);
extension = from.join("extension", LEFT);
group = from.join("group", LEFT);
industry = from.join("industry", LEFT);
businessUnit = from.join("businessUnit", LEFT);
branch = businessUnit.join("regionalBranch", LEFT);
gszMembers = from.joinList("gszMembers", LEFT);
gsz = gszMembers.join("gsz", LEFT);
}
protected <X, Y> void buildSelect(From<X, Y> from) {
query.multiselect(
from.get("id").alias("id"),
from.get("inn").alias("inn"),
from.get("opf").alias("opf"),
from.get("kpp").alias("kpp"),
from.get(OKOPF_FIELD).alias(OKOPF_FIELD),
from.get(PROBLEM_FIELD).alias(PROBLEM_FIELD),
from.get(SHORT_NAME_FIELD).alias(SHORT_NAME_FIELD),
getAreaResponsibility(cb, from).alias("responsibilityArea"),
extension.get("fp").alias("fp"),
extension.get("fpz").alias("fpz"),
extension.get("attribute56").alias("interbranch"),
group.get("name").alias("gk"),
group.get("id").alias("groupId"),
branch.get("name").alias("branch"),
lovType.get(VALUE_FIELD).alias("type"),
lovStatus.get(VALUE_FIELD).alias("status"),
lovSegment.get(VALUE_FIELD).alias("segment"),
contact.get(FULL_NAME_FIELD).alias("clientManager"),
getMfh(cb, from).alias("mfh"),
gsz.get("name").alias("gsz"),
getGks().alias("gks")
);
}
protected <X, Y> void applyFiltering(OrganizationsWrapperRequestDto request, From<X, Y> from) {
var staticPredicates = getStaticPredicates(from);
var dynamicPredicates = getDynamicPredicates(request, from);
List<Predicate> allPredicates = new ArrayList<>();
allPredicates.addAll(staticPredicates);
allPredicates.addAll(dynamicPredicates);
if (!allPredicates.isEmpty()) {
query.where(cb.and(allPredicates.toArray(new Predicate[0])));
}
}
private <X, Y> ArrayList<Predicate> getDynamicPredicates(OrganizationsWrapperRequestDto request, From<X, Y> from) {
var filter = request.filter();
var dynamicPredicates = new ArrayList<Predicate>();
dynamicPredicates.addAll(getOrganizationDynamicPredicates(from, filter));
dynamicPredicates.addAll(getOrganizationExtensionDynamicPredicates(filter));
dynamicPredicates.addAll(getGroupDynamicPredicates(filter));
dynamicPredicates.addAll(getLovValuesDynamicPredicates(filter));
dynamicPredicates.addAll(getSpecificDynamicPredicates(request, cb, from));
if (nonNull(filter.branch())) {
dynamicPredicates.add(ignoreCaseLike(branch.get("name"), filter.branch()));
}
if (nonNull(filter.clientManager())) {
dynamicPredicates.add(ignoreCaseLike(contact.get(FULL_NAME_FIELD), filter.clientManager()));
}
return dynamicPredicates;
}
private <X, Y> List<Predicate> getOrganizationDynamicPredicates(From<X, Y> from, OrganizationsFilterDto filter) {
var dynamicPredicates = new ArrayList<Predicate>();
if (nonNull(filter.organisationId())) {
dynamicPredicates.add(cb.equal(from.get("id"), filter.organisationId()));
}
if (nonNull(filter.inn())) {
dynamicPredicates.add(ignoreCaseLike(from.get("inn"), filter.inn()));
}
if (nonNull(filter.opf())) {
dynamicPredicates.add(ignoreCaseLike(from.get("opf"), filter.opf()));
}
if (nonNull(filter.kpp())) {
dynamicPredicates.add(ignoreCaseLike(from.get("kpp"), filter.kpp()));
}
if (nonNull(filter.okopf())) {
dynamicPredicates.add(ignoreCaseLike(lovOkopf.get(VALUE_FIELD), filter.okopf()));
}
if (nonNull(filter.problem())) {
dynamicPredicates.add(cb.equal(from.get(PROBLEM_FIELD), filter.problem()));
}
if (nonNull(filter.shortName())) {
dynamicPredicates.add(ignoreCaseLike(from.get(SHORT_NAME_FIELD), filter.shortName()));
}
if (nonNull(filter.responsibilityArea())) {
dynamicPredicates.add(ignoreCaseLike(from.get("areaResponsibility"), filter.responsibilityArea()));
}
return dynamicPredicates;
}
private List<Predicate> getOrganizationExtensionDynamicPredicates(OrganizationsFilterDto filter) {
var dynamicPredicates = new ArrayList<Predicate>();
if (nonNull(filter.fp())) {
dynamicPredicates.add(cb.equal(extension.get("fp"), filter.fp()));
}
if (nonNull(filter.fpz())) {
dynamicPredicates.add(cb.equal(extension.get("fpz"), filter.fpz()));
}
if (nonNull(filter.interbranch())) {
dynamicPredicates.add(cb.equal(extension.get("attribute56"), filter.interbranch()));
}
return dynamicPredicates;
}
private List<Predicate> getGroupDynamicPredicates(OrganizationsFilterDto filter) {
var dynamicPredicates = new ArrayList<Predicate>();
if (nonNull(filter.groupId())) {
dynamicPredicates.add(cb.equal(group.get("id"), filter.groupId()));
}
if (nonNull(filter.gk())) {
dynamicPredicates.add(cb.equal(group.get("name"), filter.gk()));
}
return dynamicPredicates;
}
private List<Predicate> getLovValuesDynamicPredicates(OrganizationsFilterDto filter) {
var dynamicPredicates = new ArrayList<Predicate>();
if (nonNull(filter.type())) {
dynamicPredicates.add(ignoreCaseLike(lovType.get(VALUE_FIELD), filter.type()));
}
if (nonNull(filter.status())) {
dynamicPredicates.add(ignoreCaseLike(lovStatus.get(VALUE_FIELD), filter.status()));
}
if (nonNull(filter.segment())) {
dynamicPredicates.add(ignoreCaseLike(lovSegment.get(VALUE_FIELD), filter.segment()));
}
return dynamicPredicates;
}
private Predicate ignoreCaseLike(Expression<String> path, String value) {
return cb.like(cb.lower(path), "%" + value.toLowerCase() + "%", '\\');
}
private <X, Y> void applySorting(OrganizationsWrapperRequestDto request, From<X, Y> from) {
var sort = request.page().sort();
if (isBlank(sort)) {
return;
}
boolean desc = sort.startsWith("-");
var sortField = desc ? sort.substring(1) : sort;
BiFunction<From<?, ?>, String, Order> getOrder =
(path, field) -> desc ? cb.desc(path.get(field)) : cb.asc(path.get(field));
var order = switch (sortField) {
case "id", "inn", "opf", "kpp", "okopf", "areaResponsibility", PROBLEM_FIELD, SHORT_NAME_FIELD ->
getOrder.apply(from, sortField);
case "fp", "fpz", "interbranch" -> getOrder.apply(extension, sortField);
case "groupId", "gk" -> getOrder.apply(group, sortField);
case "branch" -> getOrder.apply(branch, "name");
case "type" -> getOrder.apply(lovType, VALUE_FIELD);
case "status" -> getOrder.apply(lovStatus, VALUE_FIELD);
case "segment" -> getOrder.apply(lovSegment, VALUE_FIELD);
case "clientManager" -> getOrder.apply(contact, FULL_NAME_FIELD);
default -> null;
};
if (nonNull(order)) {
query.orderBy(order);
}
}
}
exception with select
org.springframework.dao.InvalidDataAccessResourceUsageException",
"message": "JDBC exception executing SQL [select o1_0.row_id,o1_0.x_inn,o1_0.x_opf,o1_0.x_kpp,o1_0.x_okopf,o1_0.x_problem_flg,o1_0.x_short_name_rus,case when o1_0.x_area_resp=? then cast(? as varchar2(4000 char)) else o1_0.x_area_resp end,e1_0.x_fp_flg,e1_0.x_fpz_flg,e1_0.attrib_56,g2_0.name,o1_0.x_gk_id,rb1_0.name,lt1_0.val,ls1_0.val,ls2_0.val,(c1_0.LAST_NAME || ' ' || c1_0.FST_NAME || ' ' || c1_0.MID_NAME),case when o1_0.x_income_comp<=? and (i1_0.x_mfh_flg='Y' or lo1_0.x_attrib_1='Y') then cast(? as number(1,0)) else cast(? as number(1,0)) end,g3_0.name,g1_0.name from SIEBEL.s_org_ext o1_0 left join SIEBEL.cx_gsz_member gm1_0 on o1_0.row_id=gm1_0.member_id and (gm1_0.GROUP_TYPE = 'GKS' AND gm1_0.STATUS = 'Approved') left join SIEBEL.cx_gsz g1_0 on g1_0.row_id=gm1_0.gsz_id left join SIEBEL.s_lst_of_val lo1_0 on lo1_0.active_flg='Y' and lo1_0.lang_id='RUS' and lo1_0.name=o1_0.x_okopf and lo1_0.type='ATC_ORG_OKOPF' left join SIEBEL.s_lst_of_val lt1_0 on lt1_0.name=o1_0.x_type and lt1_0.type='ATC_ORG_TYPE' left join SIEBEL.s_lst_of_val ls1_0 on ls1_0.name=o1_0.status_cd and ls1_0.type='ORG_STATUS' left join SIEBEL.s_lst_of_val ls2_0 on ls2_0.name=o1_0.x_segment and ls2_0.type='ATC_SEGMENT_TYPE' join SIEBEL.s_accnt_postn ap1_0 on o1_0.row_id=ap1_0.ou_ext_id and (ap1_0.CVRG_ROLE_CD = 'Client Manager') left join SIEBEL.s_postn p1_0 on p1_0.row_id=ap1_0.position_id left join SIEBEL.s_contact c1_0 on c1_0.row_id=p1_0.pr_emp_id left join SIEBEL.s_org_ext_x e1_0 on o1_0.row_id=e1_0.par_row_id left join SIEBEL.cx_gk g2_0 on g2_0.row_id=o1_0.x_gk_id left join SIEBEL.s_indust i1_0 on i1_0.row_id=o1_0.pr_indust_id left join SIEBEL.s_org_bu bu1_0 on bu1_0.bu_id=o1_0.bu_id and bu1_0.org_id=o1_0.row_id left join SIEBEL.s_org_ext rb1_0 on rb1_0.row_id=bu1_0.bu_id left join SIEBEL.cx_gsz_member gm2_0 on o1_0.row_id=gm2_0.member_id and (gm2_0.GROUP_TYPE = 'GSZ') left join SIEBEL.cx_gsz g3_0 on g3_0.row_id=gm2_0.gsz_id where (o1_0.int_org_flg='N' or o1_0.prtnr_flg='Y') and o1_0.accnt_flg='Y' and o1_0.x_removed_flg='N' offset ? rows fetch first ? rows only] [ORA-00918: column ambiguously defined\n] [n/a]; SQL [n/a]
using Hibernate 6.5.2
Hibernate ORM 6.5 is not supported anymore. Please upgrade to the latest Hibernate ORM version. Also, what version of Oracle are you using? It seems like this error could be due to the use of the fetch first clause and how it is implemented on older versions of Oracle.
Might be necessary to update to the latest version there as well.
we will update the Hibernate version to the latest, thank you
Our Oracle version is 12.1, I read that this version had errors with fetch first clause, we will try to update the versions, thanks
Oracle 12 is not supported anymore and as of Hibernate ORM 6.4, Hibernate also dropped support for it. So unless you update to Oracle 19 and have trouble, there is nothing we can help you with.