2

I have a requirement to use a reporting-friendly query from HQL. That is, each column is represented by a standard Java type (Long, Integer, String, List) rather than a mapped class. The values will be used by a third party library, so I have very limited control over post-processing.

My example object tree looks like this:

a.x
a.y (a collection of z)
a.y.z[0].v
a.y.z[1].v
a.y.z[2].v

I would like query to retrieve two columns. The first column is the plain "a.x" field, and the second is a String of a comma separated list of all of the a.y.z.v values. If this is not possible, then having the second column return as a Java list of the a.y.z.v values would be satisfactory.

In short, I would like to flatten the a.y.z.v collection to a csv String or to a List object from inside the query.

I have already attempted the following:

  • Using the "new" keyword in a list subselect. ie, "select a.x, (select new list(a.y.z.v)) from a". If necessary I could have transformed the contents of the list into a csv, but this caused a syntax error.

  • Using the "new" keyword with a custom object in a subselect. ie, "select a.x, (select new custom.package.ListToCsvObject(a.y.z)) from a". This caused the same error as the first attempt

  • Using the "elements()" keyword in the select. Unfortunately, this keyword only seems to work inside "in", "exists" clauses (etc), not as the actual returned value.

The only solution we've been able to find was to create a stored procedure in the database and use that, but such a solution is painfully slow through HQL (it turns a sub-second query into a 30 second query) and therefore is not something we want to continue to do.

I am able to make some limited changes to the Hibernate mapping (so I can add @formula, etc) but I would prefer not to have to make major changes to the database schema to support it. (So no, I don't want to create a denormalized "csv_value" column in the database!)

Could anyone suggest some code or, failing that, an alternative approach to solving this problem?

Erica
  • 2,251
  • 16
  • 21

1 Answers1

2

Try something like this should work. Flattening of list to comma separated string is done in the constructor of your VO class. You can also take a look at resultTransformer, you can create a custom resultTransformer and attach it to the query.

class ResultVO {

String x;
String y;
  public ResultVO(String x,List<Z> y) {
     this.x = x;
     this.y = createCSV(y);
  }
}

then in HQL
select new ResultVO(a.x,a.y) from a

A warning - this is not a good way to use JPA. If most of your use cases are like this you should seriously reconsider using some other persistence approach (ibastis, spring jdbc template + sql etc).

gkamal
  • 20,777
  • 4
  • 60
  • 57
  • Wouldn't that return the whole 'y' object array from the db, not just the array of y.z.v? In this case, z is a very large object which I do not want to retrieve for speed reasons, I just want the 'v' values. (I agree that this is a poor use of Hibernate. If I had a choice, I would be using direct JDBC in this scenario, but unfortunately it is one of the constraints I have been given.) – Erica Dec 09 '11 at 22:49
  • Maybe you can try "select new ResultVO(a.x,a.y.z.v) from a". Do you have a back reference from z to y and y to a - if that is there you might be able to do a join to get a.x and z.v. – gkamal Dec 10 '11 at 04:18
  • During my initial research I +1'ed this answer as it was confirming what I was hoping to do was possible, but as also mentioned in (JPQL: Receiving a Collection in a Constructor Expression)[https://stackoverflow.com/questions/5998409/], this does not seem possible out of the box. When you use a.y kind of syntax in JPQL, you get an error about needing a Collection in c'tor, and if you fix that error, now the sql syntax gets messed up (with just a "." as the column expression). Please see my comment in the other question: [link](https://vladmihalcea.com/one-to-many-dto-projection-hibernate/) – OzgurH Aug 09 '21 at 00:09