java.sql.SQLSyntaxErrorException: ORA-00923: FROM keyword not found where expected

Question

I am trying to cast the result of a native query within an object called Account. But when I call the API it throws me this. The fun fact is that if a copy paste the query in the sql developer it works perfectly, without throwing the "FROM keyword not found where ...." error.

org.springframework.dao.InvalidDataAccessResourceUsageException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet
Caused by: java.sql.SQLSyntaxErrorException: ORA-00923: FROM keyword not found where expected

Here is my Foo.java

@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.lenon'",
        resultSetMapping = "findAllAccountsMapping"
)
@SqlResultSetMapping(
        name = "findAllAccountsMapping",
        classes = @ConstructorResult(
                targetClass = Account.class,
                columns = {
                        @ColumnResult(name = "accountName"),
                        @ColumnResult(name = "groupName"),
                }
        )
)
@Table(name = "abc")
public class Foo {

    @Id
    @Column(name = "ID")
    private Long id;

    @Column(name = "NAME")
    private String name;

    @Column(name = "SOME_NUMBER")
    private String someNumber;
}

Account.java

public class Account {

    String accountName;

    String groupName;

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

FooRepository.java

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

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

Answer 1

When concatenating your strings, there is no space between different commands

YOUR VERSION

"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.lenon'",

So your SQL is "glued" together and doesn't form a correct SQL (e.g. SELECTQ1.ACCOUNT_NAME ... AS groupNameFROM USERS_DEV Q1)

HOW IT SHOULD BE

"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.lenon'",

You need to add spaces in the end of each SQL line (or in the beginning, ofcourse).