Left join without using Hibernate join relation

Question

Suppose I have one hibernate jpa entity-A:

public class A{
   id ,//pk
   name,
   age
   //getters and setters
}

Another Enitity-B:

public class B{
   id, //pk
   a_id, //pk id of A
   degree,
   institute
   //getters and setters
}

Here to note that entity A and B don't have implicit relation among them, a_id in B table is handled manually and hence- the relation is like one to many(A-->B) but not the jpa-hibernate relation. And, My FullDto has all the A's properties and List of B:

public class FullDto{
      id ,//pk
      name,
      age,
     List<B> bList;

    public class FullDto(id, age, name, bList)
     {
        this.id.id;
        this.age=age;
        this.name=name;
        this.bList=bList;
     }
    //getters and setters

}

Now, I want to pull everything(both A and B tables in one go) from repository interface of A like this:

public interface ARepository extends JpaRepository<A, Long> {
@Query("select " +
            "new packageName.FullDto(a.id, a.name, a.age, b) " +
            "from" +
            "   A a " +
            " left join B  b "+
            " on a.id=b.a_id "+
            "where " +
            "   a.id=:id ")
      FullDto getFullDetails(@Param("id") Long id);
}

But this seems not the way to do this!

When I tried this way: I got this error

WARN ] 2021-08-12 13:20:13.091 [restartedMain] TemplateRenderer - HHH000174: Function template anticipated 4 arguments, but 1 arguments encountered [ERROR] 2021-08-12 13:20:13.123 [restartedMain] ErrorTracker - line 1:51: unexpected token: ) [ERROR] 2021-08-12 13:20:13.123 [restartedMain] ErrorTracker - line 1:51: unexpected token: ) antlr.NoViableAltException: unexpected token: ) at org.hibernate.hql.internal.antlr.HqlBaseParser.negatedExpression(HqlBaseParser.java:2534) ~[hibernate-core-5.3.7.Final.jar:5.3.7.Final] at org.hibernate.hql.internal.antlr.HqlBaseParser.logicalAndExpression(HqlBaseParser.java:2438) ~[hibernate-core-5.3.7.Final.jar:5.3.7.Final] at org.hibernate.hql.internal.antlr.HqlBaseParser.logicalOrExpression(HqlBaseParser.java:2403) ~[hibernate-core-5.3.7.Final.jar:5.3.7.Final] at org.hibernate.hql.internal.antlr.HqlBaseParser.expression(HqlBaseParser.java:2116) ~[hibernate-core-5.3.7.Final.jar:5.3.7.Final] at org.hibernate.hql.internal.antlr.HqlBaseParser.aliasedExpression(HqlBaseParser.java:2357) ~[hibernate-core-5.3.7.Final.jar:5.3.7.Final] at org.hibernate.hql.internal.antlr.HqlBaseParser.selectedPropertiesList(HqlBaseParser.java:1390) ~[hibernate-core-5.3.7.Final.jar:5.3.7.Final] at org.hibernate.hql.internal.antlr.HqlBaseParser.newExpression(HqlBaseParser.java:1434) ~[hibernate-core-5.3.7.Final.jar:5.3.7.Final] at org.hibernate.hql.internal.antlr.HqlBaseParser.selectClause(HqlBaseParser.java:1306) ~[hibernate-core-5.3.7.Final.jar:5.3.7.Final] at org.hibernate.hql.internal.antlr.HqlBaseParser.selectFrom(HqlBaseParser.java:1040) ~[hibernate-core-5.3.7.Final.jar:5.3.7.Final] at org.hibernate.hql.internal.antlr.HqlBaseParser.queryRule(HqlBaseParser.java:748) ~[hibernate-core-5.3.7.Final.jar:5.3.7.Final] at org.hibernate.hql.internal.antlr.HqlBaseParser.selectStatement(HqlBaseParser.java:319) ~[hibernate-core-5.3.7.Final.jar:5.3.7.Final] at org.hibernate.hql.internal.antlr.HqlBaseParser.statement(HqlBaseParser.java:198) ~[hibernate-core-5.3.7.Final.jar:5.3.7.Final] at org.hibernate.hql.internal.ast.QueryTranslatorImpl.parse(QueryTranslatorImpl.java:289) ~[hibernate-core-5.3.7.Final.jar:5.3.7.Final] at org.hibernate.hql.internal.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:188) ~[hibernate-core-5.3.7.Final.jar:5.3.7.Final] at org.hibernate.hql.internal.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:143) ~[hibernate-core-5.3.7.Final.jar:5.3.7.Final] at org.hibernate.engine.query.spi.HQLQueryPlan.(HQLQueryPlan.java:119) ~[hibernate-core-5.3.7.Final.jar:5.3.7.Final] at org.hibernate.engine.query.spi.HQLQueryPlan.(HQLQueryPlan.java:80) ~[hibernate-core-5.3.7.Final.jar:5.3.7.Final] at org.hibernate.engine.query.spi.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:153) ~[hibernate-core-5.3.7.Final.jar:5.3.7.Final] at org.hibernate.internal.AbstractSharedSessionContract.getQueryPlan(AbstractSharedSessionContract.java:595) ~[hibernate-core-5.3.7.Final.jar:5.3.7.Final] at org.hibernate.internal.AbstractSharedSessionContract.createQuery(AbstractSharedSessionContract.java:704) ~[hibernate-core-5.3.7.Final.jar:5.3.7.Final] at org.hibernate.internal.AbstractSessionImpl.createQuery(AbstractSessionImpl.java:23) ~[hibernate-core-5.3.7.Final.jar:5.3.7.Final] at sun.reflect.GeneratedMethodAccessor88.invoke(Unknown Source) ~[?:?] at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:1.8.0_121] at java.lang.reflect.Method.invoke(Method.java:498) ~[?:1.8.0_121] at org.springframework.orm.jpa.ExtendedEntityManagerCreator$ExtendedEntityManagerInvocationHandler.invoke(ExtendedEntityManagerCreator.java:350) ~[spring-orm-5.1.4.RELEASE.jar:5.1.4.RELEASE] at com.sun.proxy.$Proxy155.createQuery(Unknown Source) ~[?:?] at org.springframework.data.jpa.repository.query.SimpleJpaQuery.validateQuery(SimpleJpaQuery.java:87) ~[spring-data-jpa-2.1.4.RELEASE.jar:2.1.4.RELEASE] at org.springframework.data.jpa.repository.query.SimpleJpaQuery.(SimpleJpaQuery.java:63) ~[spring-data-jpa-2.1.4.RELEASE.jar:2.1.4.RELEASE] at org.springframework.data.jpa.repository.query.JpaQueryFactory.fromMethodWithQueryString(JpaQueryFactory.java:76) ~[spring-data-jpa-2.1.4.RELEASE.jar:2.1.4.RELEASE] at org.springframework.data.jpa.repository.query.JpaQueryFactory.fromQueryAnnotation(JpaQueryFactory.java:56) ~[spring-data-jpa-2.1.4.RELEASE.jar:2.1.4.RELEASE] at org.springframework.data.jpa.repository.query.JpaQueryLookupStrategy$DeclaredQueryLookupStrategy.resolveQuery(JpaQueryLookupStrategy.java:139) ~[spring-data-jpa-2.1.4.RELEASE.jar:2.1.4.RELEASE] at org.springframework.data.jpa.repository.query.JpaQueryLookupStrategy$CreateIfNotFoundQueryLookupStrategy.resolveQuery(JpaQueryLookupStrategy.java:206) ~[spring-data-jpa-2.1.4.RELEASE.jar:2.1.4.RELEASE] at org.springframework.data.jpa.repository.query.JpaQueryLookupStrategy$AbstractQueryLookupStrategy.resolveQuery(JpaQueryLookupStrategy.java:79) ~[spring-data-jpa-2.1.4.RELEASE.jar:2.1.4.RELEASE] at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.lookupQuery(RepositoryFactorySupport.java:566) ~[spring-data-commons-2.1.4.RELEASE.jar:2.1.4.RELEASE] at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.lambda$mapMethodsToQuery$1(RepositoryFactorySupport.java:559) ~[spring-data-commons-2.1.4.RELEASE.jar:2.1.4.RELEASE] at java.util.stream.ReferencePipeline$3$1.accept(ReferencePipeline.java:193) ~[?:1.8.0_121]...

How to get all the data without using hibernate oneToMany and ManyToOne relational join?

The problem is the `new FullDto() `. You need to provide the parameters. — Michal, Aug 12 '21 at 07:44

score 1 · Answer 1 · answered Aug 26 '21 at 09:31

This is not possible as the constructor syntax only allows to pass singular attributes i.e. there is no such thing as aggregation of records on this level, so you would have to create some kind of IntermediateDto and aggregate this into a FullDto through some custom code like explain by Vlad Mihalcea here: https://vladmihalcea.com/one-to-many-dto-projection-hibernate/

I think this is a perfect use case for Blaze-Persistence Entity Views though as it employs a more efficient approach that is easier to use and maintain.

I created the library to allow easy mapping between JPA models and custom interface or abstract class defined models, something like Spring Data Projections on steroids. The idea is that you define your target structure(domain model) the way you like and map attributes(getters) via JPQL expressions to the entity model.

A DTO model for your use case could look like the following with Blaze-Persistence Entity-Views:

@EntityView(A.class)
public interface FullDto {
    @IdMapping
    Long getId();
    String getName();
    Long getAge();
    @Mapping("B[aId = VIEW(id)]")
    Set<BDto> getBList();

    @EntityView(B.class)
    interface BDto {
        @IdMapping
        Long getId();
        String getName();
    }
}

Querying is a matter of applying the entity view to a query, the simplest being just a query by id.

FullDto a = entityViewManager.find(entityManager, FullDto.class, id);

The Spring Data integration allows you to use it almost like Spring Data Projections: https://persistence.blazebit.com/documentation/entity-view/manual/en_US/index.html#spring-data-features

Page<FullDto> findAll(Pageable pageable);

The best part is, it will only fetch the state that is actually necessary!

score 0 · Answer 2 · answered Aug 12 '21 at 07:47

0

FullDto must have a constructor that matches the projection in your query. And you must provide the fully qualified name of FullDto in the query:

 @Query("select " +
        "   new packagename.FullDto(a.id, a.name, a.age)" +
        " from A a " +
        " left join B b on a.id = b.a_id "+
        " where a.id=:id ")
  FullDto getFullDetails(@Param("id") Long id);

answered Aug 12 '21 at 07:47

Simon Martinelli

34,053
5
48
82

I have added my fully qualified dto with constructor like you showed but I need to get the result from b table too which is a list and passed in the constructor. I tried this way: ```new packagename.FullDto(a.id, a.name, a.age, b.education)``` but didn't work. – user404 Aug 12 '21 at 08:07
B table has multiple rows for same a.id, so getFullDetails requires to return a List. If I do this, it works fine. But I want to get a single FullDto where data from table a and data from table b which are actually List will be in the single FullDto from this method. – user404 Aug 12 '21 at 08:27
Is it possible with jdbc template? – user404 Aug 12 '21 at 10:51
That's not possible with JPA and also not with JdbcTemplate. The only way I know is multiset with jOOQ: https://blog.jooq.org/2021/07/06/jooq-3-15s-new-multiset-operator-will-change-how-you-think-about-sql/ – Simon Martinelli Aug 12 '21 at 12:35

score 0 · Answer 3 · answered Aug 12 '21 at 07:47

0

The DTO projection needs to use an constructor expression to fill-up the DTO.

Further the DTO class name shall be fully qualified.

answered Aug 12 '21 at 07:47

Michal

2,353
1
15
18

I used fully qualified dto with constructor. But don't get it how to use it for the list in the query though it is present in the constructor – user404 Aug 12 '21 at 08:17
You can't. See https://stackoverflow.com/questions/5998409/jpql-receiving-a-collection-in-a-constructor-expression – Michal Aug 12 '21 at 08:27
Is it possible with jdbc template? – user404 Aug 12 '21 at 10:50
Tthis is not possible, neither with JPA DTO Projection nor with JdbcTemplate. Either way you receive the flat data and have to normalize them into the desired parent-childern relationship manually. – Michal Aug 12 '21 at 12:52
JPA Query would do but even that would require an update to the model with the ManyToOne and OneToMany relationships. – Michal Aug 12 '21 at 12:53

Left join without using Hibernate join relation

3 Answers3