Hibernate (or JPA?) creating invalid query


Hello, earlier I made a post about retrieving only specific fields using hibernate (for a child).
The advice given to me was to use the JPA query, however I’m running into an issue.

First of all; I have articles. in the “list” of those articles I do not want recommended other articles.
I create a jpa query. It looks like this :

SELECT NEW models.hibernateModels.BaseDeckArticle(a.id, a.title,a.content,a.author,a.decks,a.difficulty,a.date,a.editDate) FROM BaseDeckArticle a WHERE published = 1

With the added info:
Everything works unti, I include a.decks.
a.decks is a list of Decks. (For a cardgame). The amount can fluctuate.
Each deck has a list of cards (+ more) however - I dont think thats the issue right now.
The statement logger creates the following (beautified this online) (I hid some names):

SELECT     basedeckar0_.id         AS col_0_0_, 
           basedeckar0_.title      AS col_1_0_, 
           basedeckar0_.content    AS col_2_0_, 
           basedeckar0_.author_id  AS col_3_0_, 
           .    					as col_4_0_, 
           basedeckar0_.difficulty AS col_5_0_, 
           basedeckar0_.date       AS col_6_0_, 
           basedeckar0_.editdate   AS col_7_0_ 
FROM       basedeckarticle basedeckar0_ 
ON         basedeckar0_.author_id=user1_.id 
                  SELECT id, 
                         NULL AS x, 
                         NULL AS y, 
                         1    AS clazz_ 
                  FROM   decklist2
                  SELECT id, 
                         NULL AS a, 
                         NULL AS b, 
                         2 AS clazz_ 
                  FROM   decklist1) decks2_ 
ON         basedeckar0_.id=decks2_.basedeck_id 
WHERE      published=1 limit 400

One can see col 4 is bugged, it’s ‘selecting’ a dot. also AS isn’t capitalized, though not sure why.
I do thinkthe “dot” which generates the error /should/ be replaced by decks2_.id, so each article gets duplicated for each deck-id it has, and later hibernate will load the the decks in.

Now my next question stems from this.
I wanted to be able to test this, so I could later on test to create a JPA query that limits fields from children, but since I cant test it right now I might aswell ask directly what the correct way to do something like this is (or if it would directly work, since it’s a List, or whether I should create a separate request for this.):

select NEW models.hibernateModels.BaseDeckArticle(a.id, a.title,a.content,a.author, a.decks.id, ,a.difficulty,a.date,a.editDate) FROM BaseDeckArticle a WHERE published = 1

where a.decks.id would mean “List of deck ids that belong to this article”
instead of the full deck Class.

Additional info:
The list of decks. DeckList is a parent of both DeckList1 and DeckList2 classes (which are name-changed for info reasons) this is why decks2_ is a union.

      // in BaseDeckArticle
       //also tried eager
	@OrderBy("id ASC")
	protected List<DeckList> decks;


  //in DeckList
    @JoinColumn(name="basedeck_id", nullable=true)
    public BaseDeckArticle<?> baseDeckArticle;

This code works fine when requesting a full basedeckarticle: IE SELECT a FROM BaseDeckArticle a works fine, but if I can do anything in the code to fix this query, I’d be happy to.


Why do you use the DTO projection constructor to build an entity (BaseDeckArticle)

When you could just fetch the entity itself.

So, probably your query should be like this:

FROM BaseDeckArticle a
JOIN FETCH a.author
WHERE a.published = 1

That’s it.



Because there are certain properties on the main model that I do not want, I’ve left them out as they made no change. (as I said at the start, don’t want the recommended articles and some other fields loaded)

But I assume I can use this method somehow to limit also the fields it pulls from decks or is this not possible?

What I really want is:
Get some fields from “a” including decks, but not all.
Get some from those decks but not all.
As per your example, keeping just the important fields:

SELECT a.id,a.author, a.decks //not all fields
FROM BaseDeckArticle a
JOIN FETCH a.author
LEFT JOIN FETCH a.decks //somehow only get a.decks.id,a.decks.cards leaving out other fields
WHERE a.published = 1


JOIN FETCH translates to SELECT *, so you cannot apply a projection like that.

In your case, you have two options:

  1. You use the JPQL query since fetching associations is exactly why you have an entity query besides SQL.
  2. If you really want to fetch DTO projections, you can issue a native query that selects just the amount of data you need, and using a ResultTransformer, you assemble the tabular result set into a tree of objects.