java - ORA-00904 JPA EclipseLink 2.1 标识符无效

Question

我正在尝试在 JPA 中创建一个基本的 CRUD 数据库，但我陷入了“读取”部分。我创建了一个客户端表，其中包含几个存储客户端信息的字段，但由于某种原因，基本的 JPA TypedQuery 似乎无法在不遇到“无效标识符”的情况下查询它。我这里有创建代码，看起来没有什么不合适的。

create table Client (
cid NUMBER PRIMARY KEY,
fname VARCHAR2(255) NOT NULL,
lname VARCHAR2(255) NOT NULL,
email VARCHAR2(255),
phone VARCHAR2(255),
address VARCHAR(255) NOT NULL,
age number NOT NULL
);

这里我用来查询类的代码，它只是一个基本的 TypedQuery。

TypedQuery<Client> query = em.createQuery("SELECT a FROM Client a", Client.class);
    List<Client> results = query.getResultList();

以及输出(放入片段中进行格式化):

[EL Info]: 2017-11-29 14:56:23.809--ServerSession(1905114489)--EclipseLink, version: Eclipse Persistence Services - 2.5.2.v20140319-9ad6abd
[EL Info]: connection: 2017-11-29 14:56:24.091--ServerSession(1905114489)--file:////COMPHOME/homedir$/X00136103/Documents/CA2BANKACCOUNT/build/classes/_CA2BANKACCOUNTPU login successful
[EL Warning]: 2017-11-29 14:56:24.356--UnitOfWork(48522228)--Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.5.2.v20140319-9ad6abd): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: java.sql.SQLSyntaxErrorException: ORA-00904: "PHONE": invalid identifier

Error Code: 904
Exception in thread "main" javax.persistence.PersistenceException: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.5.2.v20140319-9ad6abd): org.eclipse.persistence.exceptions.DatabaseException
Call: SELECT CID, address, age, email, firstname, lastname, phone FROM Client
Query: ReadAllQuery(referenceClass=Client sql="SELECT CID, address, age, email, firstname, lastname, phone FROM Client")
Internal Exception: java.sql.SQLSyntaxErrorException: ORA-00904: "PHONE": invalid identifier

Error Code: 904
Call: SELECT CID, address, age, email, firstname, lastname, phone FROM Client
Query: ReadAllQuery(referenceClass=Client sql="SELECT CID, address, age, email, firstname, lastname, phone FROM Client")
	at org.eclipse.persistence.internal.jpa.QueryImpl.getDetailedException(QueryImpl.java:378)
	at org.eclipse.persistence.internal.jpa.QueryImpl.executeReadQuery(QueryImpl.java:260)
	at org.eclipse.persistence.internal.jpa.QueryImpl.getResultList(QueryImpl.java:469)
	at model.dbOperations.viewClients(dbOperations.java:21)
	at test.Test.main(Test.java:18)
Caused by: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.5.2.v20140319-9ad6abd): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: java.sql.SQLSyntaxErrorException: ORA-00904: "PHONE": invalid identifier

Error Code: 904
Call: SELECT CID, address, age, email, firstname, lastname, phone FROM Client
Query: ReadAllQuery(referenceClass=Client sql="SELECT CID, address, age, email, firstname, lastname, phone FROM Client")
	at org.eclipse.persistence.exceptions.DatabaseException.sqlException(DatabaseException.java:340)
	at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:682)
	at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeCall(DatabaseAccessor.java:558)
	at org.eclipse.persistence.internal.sessions.AbstractSession.basicExecuteCall(AbstractSession.java:2002)
	at org.eclipse.persistence.sessions.server.ServerSession.executeCall(ServerSession.java:570)
	at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:242)
	at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:228)
	at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeSelectCall(DatasourceCallQueryMechanism.java:299)
	at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.selectAllRows(DatasourceCallQueryMechanism.java:694)
	at org.eclipse.persistence.internal.queries.ExpressionQueryMechanism.selectAllRowsFromTable(ExpressionQueryMechanism.java:2738)
	at org.eclipse.persistence.internal.queries.ExpressionQueryMechanism.selectAllRows(ExpressionQueryMechanism.java:2691)
	at org.eclipse.persistence.queries.ReadAllQuery.executeObjectLevelReadQuery(ReadAllQuery.java:495)
	at org.eclipse.persistence.queries.ObjectLevelReadQuery.executeDatabaseQuery(ObjectLevelReadQuery.java:1168)
	at org.eclipse.persistence.queries.DatabaseQuery.execute(DatabaseQuery.java:899)
	at org.eclipse.persistence.queries.ObjectLevelReadQuery.execute(ObjectLevelReadQuery.java:1127)
	at org.eclipse.persistence.queries.ReadAllQuery.execute(ReadAllQuery.java:403)
	at org.eclipse.persistence.queries.ObjectLevelReadQuery.executeInUnitOfWork(ObjectLevelReadQuery.java:1215)
	at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.internalExecuteQuery(UnitOfWorkImpl.java:2896)
	at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1804)
	at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1786)
	at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1751)
	at org.eclipse.persistence.internal.jpa.QueryImpl.executeReadQuery(QueryImpl.java:258)
	... 3 more
Caused by: java.sql.SQLSyntaxErrorException: ORA-00904: "PHONE": invalid identifier

	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:450)
	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:399)
	at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1017)
	at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:655)
	at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:249)
	at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:566)
	at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:215)
	at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:58)
	at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:776)
	at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:897)
	at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1034)
	at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3820)
	at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3867)
	at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1502)
	at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeSelect(DatabaseAccessor.java:1007)
	at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:642)
	... 23 more

无论我尝试做什么， table 似乎都找不到“电话”。它总是无效，我不知道为什么。

Answer 1

你写道:

I've got the creation code here, and nothing seems out of place.

但是当我比较你的创作脚本时:

create table Client (
    cid NUMBER PRIMARY KEY,
    fname VARCHAR2(255) NOT NULL,
    lname VARCHAR2(255) NOT NULL,
    email VARCHAR2(255),
    phone VARCHAR2(255),
    address VARCHAR(255) NOT NULL,
   age number NOT NULL
);

以及堆栈跟踪中的 SELECT 语句:

Error Code: 904
Call: SELECT CID, address, age, email, firstname, lastname, phone FROM Client

它们并不相同。在堆栈跟踪中，这就是 EclipseLink 生成的内容，您有 firstname 和 lastname 列，因为这些列名称不在您的 CREATE TABLE 语句中。我认为您必须编辑您的实体或更正您的 DDL。

更新:

我不知道为什么它不适合你。我刚刚在 Oracle 11 和 EclipseLink 上完成了以下操作，一切正常:

• 实体:

  @Entity
  public class Client {
  
      @Id
      @GeneratedValue
      private int cid;
  
      @Column(name = "firstname", nullable = false)
      private String fname;
  
      @Column(name = "lastname", nullable = false)
      private String lname;
  
      private String email;
      private String phone;
  
      @Column(name = "address", nullable = false)
      private String address;
      int age;
  
      public int getCid() {
          return cid;
      }
  
      public void setCid(int cid) {
          this.cid = cid;
      }
  
      // rest of getters / setters
  }
  

• EclipseLink 生成的 SQL:

  CREATE TABLE CLIENT (
      CID NUMBER(10) NOT NULL, 
      address VARCHAR2(255) NOT NULL, 
      AGE NUMBER(10) NULL, 
      EMAIL VARCHAR2(255) NULL, 
      firstname VARCHAR2(255) NOT NULL, 
      lastname VARCHAR2(255) NOT NULL, 
      PHONE VARCHAR2(255) NULL, PRIMARY KEY (CID))
  

• 数据库截图