JPA - Setting entity class property from calculated column?

Question

I'm just getting to grips with JPA in a simple Java web app running on Glassfish 3 (Persistence provider is EclipseLink). So far, I'm really liking it (bugs in netbeans/glassfish interaction aside) but there's a thing that I want to be able to do that I'm not sure how to do.

I've got an entity class (Article) that's mapped to a database table (article). I'm trying to do a query on the database that returns a calculated column, but I can't figure out how to set up a property of the Article class so that the property gets filled by the column value when I call the query.

If I do a regular "select id,title,body from article" query, I get a list of Article objects fine, with the id, title and body properties filled. This works fine.

However, if I do the below:

Query q = em.createNativeQuery("select id,title,shorttitle,datestamp,body,true as published, ts_headline(body,q,'ShortWord=0') as headline, type from articles,to_tsquery('english',?) as q where idxfti @@ q order by ts_rank(idxfti,q) desc",Article.class);

(this is a fulltext search using tsearch2 on Postgres - it's a db-specific function, so I'm using a NativeQuery)

You can see I'm fetching a calculated column, called headline. How do I add a headline property to my Article class so that it gets populated by this query?

So far, I've tried setting it to be @Transient, but that just ends up with it being null all the time.

Answer 1

There are probably no good ways to do it, only manually:

Object[] r = (Object[]) em.createNativeQuery(
    "select id,title,shorttitle,datestamp,body,true as published, ts_headline(body,q,'ShortWord=0') as headline, type from articles,to_tsquery('english',?) as q where idxfti @@ q order by ts_rank(idxfti,q) desc","ArticleWithHeadline")
    .setParameter(...).getSingleResult();

Article a = (Article) r[0];
a.setHeadline((String) r[1]);

-

@Entity
@SqlResultSetMapping(
    name = "ArticleWithHeadline",
    entities = @EntityResult(entityClass = Article.class),
    columns = @ColumnResult(name = "HEADLINE"))
public class Article {
    @Transient
    private String headline;
    ...
}

Answer 2

AFAIK, JPA doesn't offer standardized support for calculated attributes. With Hibernate, one would use a Formula but EclipseLink doesn't have a direct equivalent. James Sutherland made some suggestions in Re: Virtual columns (@Formula of Hibernate) though:

There is no direct equivalent (please log an enhancement), but depending on what you want to do, there are ways to accomplish the same thing.

EclipseLink defines a TransformationMapping which can map a computed value from multiple field values, or access the database.

You can override the SQL for any CRUD operation for a class using its descriptor's DescriptorQueryManager.

You could define a VIEW on your database that performs the function and map your Entity to the view instead of the table.

You can also perform minor translations using Converters or property get/set methods.

Also have a look at the enhancement request that has a solution using a DescriptorEventListener in the comments.

All this is non standard JPA of course.