Using EntityQuery: best practices

[kwutzke]

Hello,

I'm new to Seam and it's my first webapp using JPA/Hibernate. I want to display a box score of sports stats, basically just the same as here.

I've created an EntityQuery subclass:

@Name("boxScore")
public class BoxScore extends EntityQuery<Object[]> 
{
    private static final String EJBQL =
        "SELECT pl.lastName, pl.firstName, ps.jerseyNbr, ps.isStarter, ps.pf, SUM(st.pts), (SUM(st.pts)-SUM(st.ftm)-SUM(st.tpm)*3)/2, SUM(st.tpm), SUM(st.ftm), SUM(st.fta) " +
        "FROM Stat st " +
        "  LEFT JOIN st.playerStat ps " +
        "  LEFT JOIN ps.score sc " +
        "  LEFT JOIN sc.game ga " +
        "  LEFT JOIN sc.roster ro " +
        "  LEFT JOIN ps.teamMember tm " +
        "  LEFT JOIN tm.player pl ";
    
    private static final String[] RESTRICTIONS = {
            "ga.id=#{boxScore.gameId}",
            "sc.id.isHome=#{boxScore.isHome}",
        };

    ...
}

As you can see, the query mixes properties of different classes/tables. Some of them are derived properties. This is why the generic type of the class is an Object array.

My question now is:

Would it be better to declare the generic type as PlayerStat, adjust the JPQL, return the List of PlayerStat's and then gather the player names and derived values from the list entities or would it make more sense to introduce an artificial class representing each stat line?

If the former is true, wouldn't this mean additional joins per entity when calling a PlayerStat's ps.getTeamMember().getPlayer().getLastName() etc.? If the latter is true, subclassing from EntityQuery does no longer make sense, as a StatLine class isn't an entity.

I think this is such a general question, I wonder where to find such best practices in one spot.

So, what's your recommendation?

Karsten

[lvdberg]

Hi,

especially for these cases Hibernate has the new SomethingClass available. You create a view class with the default constructor and a constructor which matches the vars you want to use.

The query must be changed to

SELECT new playerStat(pl.lastName, pl.firstName, ps.jerseyNbr, ps.isStarter, ps.pf, SUM(st.pts), (SUM(st.pts)-SUM(st.ftm)-SUM(st.tpm)*3)/2, SUM(st.tpm), SUM(st.ftm), SUM(st.fta)) from ...
 

Instead of an object array you will geta a list with the newly created PlayerStat objects. Your code is much cleaner and you save yourself some additional transfer work.

Leo

[kwutzke]

Thanks for your answer. I very much appreciate your extremely valuable tips.

BTW the syntax isn't specific to Hibernate, it's JPA-compatible.

I just found this in my JPA book:

Constructor expressions are powerful tools for constructing coarse-grained data transfer objects or view objects for use in other application tiers.

This is interesting! I didn't know fine-grained entity objects are considered model domain, whereas coarse-grained non-entity objects are considered rather view domain. That really changes my view on coarse-grained objects.

Karsten