JPA - calculated columns?

[growse]

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.

 

[growse]

I certainly appreciate what you're saying - if it were possible to do the calculation in code, that would make life easy. I'd just have a separate method and a getter which called that.

The difficulty is twofold.

1) I'm making use of a Postgres-specific syntax and functionality to do fulltext searching. As far as I know, the ORM (I've switched to Hibernate now, more stable) cannot abstract this sort of database-specific functionality. If you look at the query, I'm performing several db functions within the query as well as using pg-style syntax "@@". I don't believe there's a way to do this from a CriteriaBuilder or any other JPA mechanism.

2) The value of the headline is dependent on what the parameters passed to the query are. What ts_headline() does in postgres is take the requested search term and for a given result, generate a snippet of the fulltext which surrounds where the search term was found. A bit like the page snippets you see below google results with the search terms highlighted. I could try and replicate this functionality in the application layer, but this would be a fair bit of work to do, given that fulltext searching is quite tuned to the natural language of the text. In effect, I'd have to replicate the entire fulltext algorithms in Java, just to figure out where the database found a hit for a given term in a given text.

Thinking about it, I could abstract the query into a dedicated stored proc and then just call that (can you do that from JQL in a namedquery?). Although I've still then got the problem of having a piece of data being returned that doesn't fit into the entity class.