0

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();

1
  • 2
    Your SQL query string literal is formatted incorrectly, and is missing spaces in critical places. Assign that to a string, then log it to see the problems. Commented Oct 7, 2020 at 5:59

1 Answer 1

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).

Sign up to request clarification or add additional context in comments.

6 Comments

This is a typo question, and, as such, you should probably avoid answering it.
Hm, ok, curious to find out more about this policy. Can you point me out to read more about it?
Click the "close" button underneath the question. Simple typographical errors, under "community reasons," is one of the reasons for voting to close a question. That aside, your answer doesn't even correct the problems, it just copies the same incorrect string from the OP.
It worked, still didn`t understand why @Tim says you should not have asnwered
LIvanov, thanks a lot. I could not even think about sql glueing the query together. Thanks
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.