I have a scenario in which I want to use the hibernate annotation @Filter (activated only if the condition is satisfied) but the condition needs to use alias (person as in the code snippet, because there is an ambiguous column in personId) from the original one which is written within Criteria. Note that the filter condition is written in sql. Besides the root.alias("person") don't work in this situation.

The model class:

@Entity
@Table(name = "PERSON")
@NoArgsConstructor
@Getter
@Setter
@Inheritance(strategy = InheritanceType.JOINED)
@FilterDef(name = "authorizePerson", parameters = {@ParamDef(name="userCode", type = "string")})
@Filter(name = "authorizePerson", condition = 
        " NOT EXISTS ("
          "                SELECT"
          "                    multilevel1_.id_dossier,"
          "                    multilevel1_.type_dossier"
          "                FROM"
          "                    dossier_multiniv multilevel1_"
          "                WHERE"
          "                    multilevel1_.id_dossier = TO_CHAR(**person**.id_per)"
          "                    AND multilevel1_.type_dossier = 'PERSONNE'"
          "            )"
          "OR EXISTS ("
          "                SELECT"
          "                    multilevel2_.id_dossier,"
          "                    multilevel2_.cd_util"
          "                FROM"
          "                    v_droits_multiniv_allusers multilevel2_"
          "                WHERE"
          "                    multilevel2_.cd_util = :userCode"
          "                    AND multilevel2_.id_dossier = TO_CHAR(**person**.id_per)"
          "                    AND multilevel2_.type_dossier = 'PERSONNE'"
          "            )", deduceAliasInjectionPoints = true)
public class PersonPO implements Serializable {
    private static final long serialVersionUID = 1L;

    @Id
    @Column(name = "ID_PER")
    private Long personId;

    @Column(name = "AFFICH_PER")
    private String archive;

    @Column(name = "AUTH_ERROR")
    private Long numberOfConnectionErrors;

    // bi-directional many-to-one association to Categper
    @OneToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "ID_PER")
    private PersonCategoryPO personCategory;
  ...
 }

Then the service:

@Transactional
    @Override
    public Pageable<IPersonBO> findAllPersons(String stringFilter, Page page, Sort sort, String userCode) {
        try {
            Predicate filterPredicate = null;
            CriteriaQuery<PersonPO> cq;
            Root<PersonPO> root;
            CriteriaBuilder cb = em.getCriteriaBuilder();
            Predicate allPredicates = cb.equal(cb.literal(1), 1);
            if (!stringFilter.isBlank()) {
                SearchCondition<PersonPO> filter = new FiqlParser<PersonPO>(PersonPO.class).parse(stringFilter);
                JPACriteriaQueryVisitor<PersonPO, PersonPO> jpa = new JPACriteriaQueryVisitor<PersonPO, PersonPO>(em,
                        PersonPO.class, PersonPO.class);
                filter.accept(jpa);
                cq = jpa.getQuery();
                root = (Root<PersonPO>) cq.getRoots().iterator().next();
                filterPredicate = jpa.getQuery().getRestriction();
                allPredicates = filterPredicate;
            } else {
                cq = cb.createQuery(PersonPO.class);
                root =cq.from(PersonPO.class);
            }
            cq.select(root.alias("person"));
            Fetch<PersonPO, PersonCategoryPO> pc = root.fetch("personCategory");
            Fetch<PersonCategoryPO, CategoryPO> category = pc.fetch("category");
            CriteriaQuery<Long> cqCount = cb.createQuery(Long.class);
            cqCount.select(cb.count(cqCount.from(PersonPO.class)));
            if (multiLevelIsActif()) {
                if (userCode != null) {
                    Filter filter = session.enableFilter("authorizePerson");
                    filter.setParameter("userCode", userCode);
                }
            }
            cq.where(allPredicates);
            cqCount.where(allPredicates);

            // sort
            JpaUtils.handleSort(sort, cq, cb, root);

            // get results
            TypedQuery<PersonPO> query = em.createQuery(cq);
            if (page != null) {
                query.setFirstResult(page.getFirstResult()).setMaxResults(page.getPageSize());
            }
            List<IPersonBO> items = query.getResultStream().map(c -> getPerson((PersonPO) c))
                    .collect(Collectors.toList());

            // count results
            Long totalSize = 0l;
            totalSize = em.createQuery(cqCount).getSingleResult();

            return new Pageable<IPersonBO>(items, totalSize, page);
        } catch (Exception e) {
            log.error(e.getMessage());
            return null;
        }

Why the sql-show still generates query and never consider the alias (person) like below ?

        select
            personpo0_.ID_PER as id_per1_102_,
        ...
        from
            RKHATERCHI2.PERSONNE personpo0_ 
        ...
        where
            1=1 
        order by
            personpo0_.ID_PER desc

Is there a way how to enforce the alias in JPA/Criteria so that I can use it in the filter condition? Your thoughts are appreciated.

CodePudding user response：

Take a look into the documentation about how to specify table aliases in filter conditions: https://docs.jboss.org/hibernate/orm/5.6/userguide/html_single/Hibernate_User_Guide.html#pc-filter-sql-fragment-alias