Custom @Query in JPA

Question

I am trying to do a @Query in JPA that does not return a Entity from my Model. In my Model I have BudgetPlan, BudgetGeschaftsfeld, BudgetMarke, BudgetKampagne, BudgetBucket, Kosten Entities which have a @OneToMany relationship.

So Budgetplan has many BudgetGeschaftsfeld which has many BudgetMarke and so on.

I want to return a CustomKosten with all couple of the standard Kosten attributes, and the ID to each "Parent ID". A Pojo of the Class would look like this.

@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@ToString
public class CustomKosten {

private long id;
private String name;
private double betrag;
private String status;
private Date local_date;
private long idBucket;
private String bucket;
private long idKampagne;
private String kampagne;
private long idMarke;
private String marke;
private long idGeschaeftsfeld;
private String geschaeftsfeld;
private long idPlan;
private String plan;        
}

I have accomplished the SQL query in MySQL and it gets the result I want. Which looks like this.

public interface CustomKostenRepository extends JpaRepository<CustomKosten, Long> {

@Query(nativeQuery = true, value="select id, name, betrag, status, local_date, idbucket, bucket, idkampagne, kampagne, idmarke, marke, idgeschaeftsfeld, geschaeftsfeld, idplan, plan from \r\n"
        + "(select * from (select * from (select * from (select * from (select pid as pidgeschaeftsfeld, id as idgeschaeftsfeld, name as geschaeftsfeld from budman_db.budget where dtype=\"Budgetgeschaftsfeld\") as tgeschaeftsfeld \r\n"
        + "left join (select id as idplan, name as plan from budman_db.budget where dtype=\"Budgetplan\" ) as tplan on tgeschaeftsfeld.pidgeschaeftsfeld= tplan.idplan) as t1\r\n"
        + "left join (select pid as pidmarke, id as idmarke, name as marke from budman_db.budget where dtype=\"Budgetmarke\") as tmarke on t1.idgeschaeftsfeld= tmarke.pidmarke) as t2\r\n"
        + "Left join (select pid as pidkampagne, id as idkampagne, name as kampagne from budman_db.budget where dtype=\"Budgetkampagne\") as tkampagne on t2.idmarke= tkampagne.pidkampagne ) as t3\r\n"
        + "left join (select pid as pidbucket, id as idbucket, name as bucket from budman_db.budget where dtype=\"Budgetbucket\") as tbucket on t3.idkampagne= tbucket.pidbucket) as t4\r\n"
        + "left join (select * from budman_db.kosten ) as tkosten on t4.idbucket= tkosten.pid")
public List<CustomKosten> getCustomKostenKomplex();
}

is the result I'm trying to get even possible? I am getting this Error when running my code. Any advice is much appreciated

Caused by: java.lang.IllegalArgumentException: Not a managed type: class com.bm.ent.kosten.CustomKosten

Edit:

After following the Example Eugene adviced im getting another error.

Caused by: java.lang.IllegalArgumentException: Could not locate appropriate 
constructor on class : com.bm.ent.kosten.CustomKosten

I'm guessing it has something to do with the Type of each field. And I think I don't have to put a type on those which have a primitive class. note: I purposely left out the date to test. Any advice?

@Entity(name= "Kosten")
@Table(name= "kosten")
@NamedNativeQuery(name = "CustomKostenAll",
        query= "select id, name, betrag, status, idbucket, bucket, idkampagne, kampagne, idmarke, marke, idgeschaeftsfeld, geschaeftsfeld, idplan, plan from \r\n"
                + "(select * from (select * from (select * from (select * from (select pid as pidgeschaeftsfeld, id as idgeschaeftsfeld, name as geschaeftsfeld from budman_db.budget where dtype=\"Budgetgeschaftsfeld\") as tgeschaeftsfeld \r\n"
                + "left join (select id as idplan, name as plan from budman_db.budget where dtype=\"Budgetplan\" ) as tplan on tgeschaeftsfeld.pidgeschaeftsfeld= tplan.idplan) as t1\r\n"
                + "left join (select pid as pidmarke, id as idmarke, name as marke from budman_db.budget where dtype=\"Budgetmarke\") as tmarke on t1.idgeschaeftsfeld= tmarke.pidmarke) as t2\r\n"
                + "left join (select pid as pidkampagne, id as idkampagne, name as kampagne from budman_db.budget where dtype=\"Budgetkampagne\") as tkampagne on t2.idmarke= tkampagne.pidkampagne ) as t3\r\n"
                + "left join (select pid as pidbucket, id as idbucket, name as bucket from budman_db.budget where dtype=\"Budgetbucket\") as tbucket on t3.idkampagne= tbucket.pidbucket) as t4\r\n"
                + "left join (select * from budman_db.kosten ) as tkosten on t4.idbucket= tkosten.pid",
        resultSetMapping= "CustomKostenMapping")
@SqlResultSetMapping(name = "CustomKostenMapping",
        classes = {
                @ConstructorResult(targetClass = CustomKosten.class,
                        columns = {
                                @ColumnResult(name = "id"), 
                                @ColumnResult(name = "name", type = String.class),
                                @ColumnResult(name = "betrag"),
                                @ColumnResult(name = "status", type = Status.class),    
//                              @ColumnResult(name = "local_date"),
                                @ColumnResult(name = "idbucket"),
                                @ColumnResult(name = "bucket", type = String.class),
                                @ColumnResult(name = "idkampagne"),
                                @ColumnResult(name = "kampagne", type = String.class),
                                @ColumnResult(name = "idmarke"),
                                @ColumnResult(name = "marke", type = String.class),
                                @ColumnResult(name = "idgeschaeftsfeld"),
                                @ColumnResult(name = "geschaeftsfeld", type = String.class),
                                @ColumnResult(name = "idplan"),
                                @ColumnResult(name = "plan", type = String.class),
                                }
                        )
        })
@Getter
@Setter
@NoArgsConstructor
public class Kosten {...}

JPA does not support automatic query result mapping into POJO, not entity classes. You need to specify `@SqlResultSetMapping` for your native query. Example: https://stackoverflow.com/questions/72308092/how-to-take-data-from-crosstabs-from-database-related-manytomany-relationship-us/72325728#72325728 — Eugene, May 22 '22 at 23:19
If you make CustomKosten an interface Spring Data JPA Interface projection should work — Simon Martinelli, May 23 '22 at 06:17
@Eugene thankyou! I think this is exactl what i needed. Im getting another error. I have edited my post. — Nortap, May 23 '22 at 08:39
What type of Date field do you have? it should be `java.util.Date` — Eugene, May 23 '22 at 08:42
@Eugene Date is not the problem, as I have commented it oout of evrything (CustomKosten, Query). But yes it would be java.util.Date — Nortap, May 23 '22 at 08:44
You have lombok `@AllArgs` constructor, count of arguments in `@ConstructorResult` must fit your constructor — Eugene, May 23 '22 at 08:46
And another thing is that types of arguments must be equal, otherwise, constructor will not be matched. I think, better use reference types `Long`, `Double` — Eugene, May 23 '22 at 08:51

times29 · Answer 1 · 2022-05-24T13:14:18.477

First, if you want to use the CustomKosten class as return type of your repository, you need to place annotate it with @Entity so that it becomes a managed type for JPA / Hibernate.
This however would only work if you have a table with a structure corresponding to your entity. In your case, you query several columns from different tables. Therefore, you need to specify how Hibernate should map all the returned columns to your Java class. I recommend this tutorial on SQL result set mapping by one of the Hibernate maintainers Thorben Janssen. If you already use Hibernate 6, you could also use a TupleTransformer as shown here.

Custom @Query in JPA

1 Answers1