Joining tables using criteria and sqlProjection

Question

I have the following function that builds a Hibernate Criteria to generate binned data:

private Criteria getCustomBinResultCriteriaSQL(double binSizeX, double binSizeY, String xVar, String yVar, String customBinQuery) {
    return createCriteria(Residue.class)
            .setProjection(Projections.projectionList()
                    .add(Projections.sqlGroupProjection(
                            "floor(" + xVar + " / " + binSizeX + ") * " + binSizeX + " as xBin, " +
                                    "floor(" + yVar + " / " + binSizeY + ") * " + binSizeY + " as yBin, " +
                                    "CAST (" + customBinQuery + " AS DOUBLE PRECISION) as customBinResult",
                            "xBin, yBin",
                            new String[] { "xBin", "yBin", "customBinResult" },
                            new Type[] { Hibernate.DOUBLE, Hibernate.DOUBLE, Hibernate.DOUBLE })))
            .setResultTransformer(Transformers.aliasToBean(CustomBinResult.class));
}

This all works pretty well for data within the same table (residue), but let's say my datastructure is like this:

pdbentry:
id
pdbvar

expmethod:
id
expvar

residue:
id
resvar

pdbentry has a one-to-one relation with expmethod, and a one-to-many relation with residue.

How would I go about joining the residue table with expmethod, based on the criteria-builder above. So in other words: what do I need to add to the criteria to be able to have "expvar" as xVar?

I tried adding something like:

.setFetchMode("pdbentry", FetchMode.JOIN);
.setFetchMode("expmethod", FetchMode.JOIN);

at the end, but then I still couldn't put "expvar" nor "expmethod.expvar" as xVar.

Any ideas?

Read here http://stackoverflow.com/questions/30215528/java-hibernate-projections-sqlgroupprojection-aliasing and here http://stackoverflow.com/questions/2345419/is-it-possible-to-get-the-sql-alias-of-a-join-table-for-a-hibernate-sqlrestricti — digz6666, Oct 31 '16 at 16:16

score 1 · Answer 1 · edited Jan 10 '12 at 11:15

I'm aliasing the joined-to tables via

.createCriteria("rootEntity.foreignEntity", "someAlias")

This makes rootEntity.foreignEntity.someProperty available to the Criteria query as someAlias.someProperty (usually I choose "someAlias" to be the same as "foreignEntity").

Since I usually want an eager fetch, I tend to have Criteria.INNER_JOIN or Criteria.LEFT_JOIN on the createCriteria call, too.

mcyalcin · Answer 2 · 2011-02-22T12:08:55.427

0

Have you tried "pdbentry.expmethod.expvar"?

It is not clear (to me) what you are trying to do from just this, but the way you define your entity relationships, the way to access the relevant expvar from a given residue would be "residue.pdbentry.expmethod.expvar".

edited Feb 22 '11 at 12:08

answered Feb 22 '11 at 12:01

mcyalcin

2,074
1
15
12

Joining tables using criteria and sqlProjection

2 Answers2