eclipselink jpa criteria reference unmapped column

Question

when writing jpql queries I can reference not mapped columns with COLUMN() (I know it is eclipselink specific). Is there any way I can reference unmapped column when building criteria using javax.persistence.criteria.CriteriaBuilder, javax.persistence.criteria.Predicate, etc?

The problem I am facing: I have postgresql table with full text search column. I do not want it to be mapped to entity objects but I qould like to use is in queries (and I need also queries using CriteriaBuilder).

Edit: code example

I have a table: X (id integer, name varchar, fts tsvector)

"fts" column is full text search index data. I need entity class without "fts" attribute, like:

@Entity class X {
    @Id Integer id;
    @Column String name;
}

so that fts data is never fetched (for performance), but I need to be able to query that column. I can do this with jpql: SELECT t FROM X t WHERE COLUMN('fts', t) IS NOT NULL;

but how can I reference such column when building criteria like:

Specification<Fts> spec = new Specification<Fts>() {
        @Override
        public Predicate toPredicate(Root<Fts> root, CriteriaQuery<?> query, CriteriaBuilder builder) {
            return builder.notNull(root.<String>get("fts"));
        }
    };

other choice: How to add attribute in entity class that reference table column but is never fetched? I tried adding @Basic(fetchType = FetchType.LAZY) but it does not work and value is fetched upon query...

Code please. And what do you mean, "*unmapped column*" - those marked with `@Transient`? — Tiny, Apr 08 '15 at 09:16
@transient is the opposite case - attribute in entity which is not mapped to database column. — redguy, Apr 08 '15 at 09:18
Don't get me wrong but for me your problem looks more like bad design problem. put your fts in separate entity/table, then define your fetch type as lazy. That means class on other side of relation will be loaded only when requested. — user902383, Apr 08 '15 at 11:52
I think there is nothing wrong in having columns in table which I do not want to be fetched, but I still want to use them in queries. FTS data is one case, spatial data is another - you do not need that data fetched but you would like to find records matching some area... consider a query like: SELECT (all but 'fts' and 'spatial' columns) FROM table WHERE fts match 'xxx' AND spatial match 'area'; — redguy, Apr 10 '15 at 09:39

score 1 · Accepted Answer · answered Apr 08 '15 at 10:32

Ok. I managed to solve it this way:

Specification<Fts> spec = new Specification<Fts>() {
        @Override
        public Predicate toPredicate(Root<Fts> root, CriteriaQuery<?> query, CriteriaBuilder builder) {
            JpaCriteriaBuilder cb = (JpaCriteriaBuilder) builder;
            List<String> args = new ArrayList();
            args.add("FTS query");

            javax.persistence.criteria.Expression<Boolean> expr = cb.fromExpression(
              cb.toExpression(
                cb.function("", Boolean.class, cb.fromExpression(((RootImpl) root).getCurrentNode().getField("fts")))
              )
              .sql("? @@ to_tsquery(?)", args)
            );
            // getField() allows to reference column not mapped to Entity

            query.where(expr);

            return null; // or return cb.isTrue(expr); and then not need to set query.where()
        }
    };

You should be able to do `cb.toExpression(root)` instead of `((RootImpl) root).getCurrentNode()`, which results in cleaner code IMHO — gpeche, May 17 '18 at 08:26

eclipselink jpa criteria reference unmapped column

1 Answers1

Linked