Retrieve JPQL Query as DTO in Spring boot application

Question

What I am basically trying to do is to run a query and put the result within the object of the class Account. What is very important to emphasize is that the query performs a JOIN between 2 tables THAT ARE NOT MAPPED as @Entity. So how can I do that?

This is my FooRepository.java class

@Repository
public interface FooRepository extends JpaRepository<Foo, Long> {

  Foo findById(Long id);

@Query(value = "SELECT Q1.ACCOUNT_NAME," +
          "Q2.GROUP_NAME  " +
          "FROM USERS_DEV Q1\n" +
          "JOIN USERS_GROUPS Q2 ON Q1.ACCOUNT_NAME = Q2.ACCOUNT_NAME\n" +
          "WHERE LOWER(Q1.ACCOUNT_NAME) = 'john.pit'", nativeQuery = true)
  List<Account> getAllAccounts();

So how can I change my query to get the desired result?

And here is my Account.java class

public class Account {

    String samAccountName;
    String groupName;

    public Account(String accountName, String groupName) {
        this.accountName = accountName;
        this.groupName = groupName;
    }

    public String getAccountName() {
        return accountName;
    }

    public void setAccountName(String accountName) {
        this.accountName = accountName;
    }

    public String getGroupName() {
        return groupName;
    }

    public void setGroupName(String groupName) {
        this.groupName = groupName;
    }
}

Answer 1

As an alternative you can use @NamedNativeQuery with @SqlResultSetMapping in the following way:

@Entity
@NamedNativeQuery(
  name = "findAllAccounts",
  query = 
     "SELECT " +
     "  Q1.ACCOUNT_NAME AS accountName, " +
     "  Q2.GROUP_NAME AS groupName " +
     "FROM USERS_DEV Q1 " +
     "JOIN USERS_GROUPS Q2 ON Q1.ACCOUNT_NAME = Q2.ACCOUNT_NAME " + 
     "WHERE LOWER(Q1.ACCOUNT_NAME) = 'john.pit'",
  resultSetMapping = "findAllAccountsMapping"
)
@SqlResultSetMapping(
   name = "findAllAccountsMapping",
   classes = @ConstructorResult(
      targetClass = Account.class,
      columns = {
         @ColumnResult(name="accountName"),
         @ColumnResult(name="groupName"),
      }
   )
)
public class Foo {
   // ...
}

and FooRepository:

@Repository
public interface FooRepository extends JpaRepository<Foo, Long> {

  @Query(name = "findAllAccounts", nativeQuery = true)
  List<Account> getAllAccounts();
}

See hibernate documentation for additional details.