java.sql.SQLSyntaxErrorException:ORA-00923:在预期位置找不到FROM关键字

Question

我正试图在一个名为Account的对象中强制转换本机查询的结果。但当我调用API时，它抛出了这个问题。有趣的事实是，如果在sql开发人员中复制粘贴查询，它可以完美地工作，而不会抛出"在…处找不到FROM关键字"错误

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

这是我的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

连接字符串时，不同的命令之间没有空格

您的版本

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

因此，您的SQL是"；胶合"；并且不能形成正确的SQL(例如SELECTQ1.ACCOUNT_NAME ... AS groupNameFROM USERS_DEV Q1(

应该如何

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

您需要在每一行SQL的末尾(当然也可以在开头(添加空格