Hibernate (or JPA?) creating invalid query

Hibernate ORM
1

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_ 
INNER JOIN USER user1_ 
ON         basedeckar0_.author_id=user1_.id 
INNER JOIN 
           ( 
                  SELECT id, 
                         NAME, 
                         basedeck_id, 
                         a, 
                         b, 
                         NULL AS x, 
                         NULL AS y, 
                         1    AS clazz_ 
                  FROM   decklist2
                  UNION 
                  SELECT id, 
                         NAME, 
                         basedeck_id, 
                         NULL AS a, 
                         NULL AS b, 
                         y, 
                         x, 
                         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
	@OneToMany(fetch=FetchType.LAZY,mappedBy="baseDeckArticle")
	@OrderBy("id ASC")
	protected List<DeckList> decks;

DeckList:

  //in DeckList
   @ManyToOne(cascade={CascadeType.MERGE,CascadeType.PERSIST})
    @JoinColumn(name="basedeck_id", nullable=true)
    @JsonIgnore
    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.

2

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:

SELECT a 
FROM BaseDeckArticle a
JOIN FETCH a.author
LEFT JOIN FETCH a.decks
WHERE a.published = 1

That’s it.

1 Like
3

Hey!

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
4

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.