title: how to implement interface based projection into dto in spring data jpa with inner join query table
body: <p>I have two table- one is <code>USERS_TAXPAYER</code>(contains column- <code>tin_no</code>, <code>zone_no</code> and another one is <code>PAYMENT_DATA</code> (contains column- <code>tin_no</code> , <code>amount</code> ), my goal is to calculate total amount produced by same <code>zone_no</code>(there are multiple amount under same zone), so I am using a native query in my <code>repository layer</code> in the spring boot project like this-</p>
  <pre><code>@Query(value = &quot;select USERS_TAXPAYER.zone_no, sum(PAYMENT_DATA.amount) as amount_sum from PAYMENT_DATA inner join USERS_TAXPAYER on \n&quot; +
          &quot;PAYMENT_DATA.tin_no = USERS_TAXPAYER.tin_no group by USERS_TAXPAYER.zone_no&quot;,nativeQuery = true)
  List&lt;ZoneWiseTotalAmountDto&gt; countZoneWiseReturnAmount();
  </code></pre>
  <p><a href="https://i.stack.imgur.com/sYd4E.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/sYd4E.png" alt="enter image description here" /></a></p>
  <p>but now I want to retrieve this response as dto, I tried to follow the interface based projection , so I modified the code as follows-</p>
  <pre><code>public interface IZoneAmountDTO{
          Integer getZone();
          Double getAmount();
      }
      @Query(value = &quot;select new com.example.nbr.entity.ZoneWiseTotalAmountDto(z.zone,z.amount) from (select USERS_TAXPAYER.zone_no, sum(PAYMENT_DATA.amount) as amount_sum from PAYMENT_DATA inner join USERS_TAXPAYER on \n&quot; +
              &quot;PAYMENT_DATA.tin_no = USERS_TAXPAYER.tin_no group by USERS_TAXPAYER.zone_no) z&quot;,nativeQuery = true)
      List&lt;ZoneWiseTotalAmountDto&gt; countZoneWiseReturnAmount();
  </code></pre>
  <p>below is my <code>ZoneWiseTotalAmountDto</code> class</p>
  <pre><code>package com.example.nbr.entity;
  
  import lombok.AllArgsConstructor;
  import lombok.Data;
  import lombok.NoArgsConstructor;
  
  @Data
  @AllArgsConstructor
  @NoArgsConstructor
  public class ZoneWiseTotalAmountDto {
      private Integer zone;
      private Double amount;
  
  }
  
  </code></pre>
  <p>If I run it then Intellij says-</p>
  <pre><code>ERROR org.hibernate.engine.jdbc.spi.SqlExceptionHelper.logExceptions - Incorrect syntax near '.'
  </code></pre>
  <p>What am I doing wrong here? Where to use that Interface that I created? I was following this blog- <a href="https://roytuts.com/how-to-map-custom-query-results-into-dto-in-spring-data-jpa/" rel="nofollow noreferrer">link</a></p>