java SQLSyntaxErrorException: 表/视图 'BUYER' 不存在。缺什么？

SQLSyntaxErrorException: Table/View 'BUYER' does not exist. What is missing?

提问by sfrj

A few weeks ago I went on holidays and I paused one of my projects. When I came back I was just checking the registration page and was surprised when I got an SQLException saying that a table does not exist. I don't understand it because that table exists, I created it from an entity. I pasted the code here so you can see that everything seems to be ok. I think it probably has something to do with the database (I use glassfish 3 app server).

几周前，我去度假并暂停了我的一个项目。当我回来时，我只是在检查注册页面，当我收到 SQLException 说表不存在时，我感到很惊讶。我不明白，因为该表存在，我从一个实体创建了它。我在这里粘贴了代码，所以你可以看到一切似乎都没有问题。我认为这可能与数据库有关（我使用 glassfish 3 应用服务器）。

Here is an image from the user interface that says that the problem has to do with some validation methods (Checking if a user exists already and checking if an email already exists):

这是用户界面中的一张图片，说明问题与某些验证方法有关（检查用户是否已存在并检查电子邮件是否已存在）：

Just in case I will also print the stackTrace:

以防万一我也会打印stackTrace：

WARNING: Local Exception Stack: 
Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.0.1.v20100213-r6600): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: java.sql.SQLSyntaxErrorException: Table/View 'BUYER' does not exist.
Error Code: -1
Call: SELECT COUNT(NICKNAME) FROM BUYER WHERE (NICKNAME = ?)
    bind => [test]
Query: ReportQuery(referenceClass=Buyer sql="SELECT COUNT(NICKNAME) FROM BUYER WHERE (NICKNAME = ?)")
    at org.eclipse.persistence.exceptions.DatabaseException.sqlException(DatabaseException.java:333)
    at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:687)
    at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeCall(DatabaseAccessor.java:530)
    at org.eclipse.persistence.sessions.server.ServerSession.executeCall(ServerSession.java:529)
    at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:205)
    at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:191)
    at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeSelectCall(DatasourceCallQueryMechanism.java:262)
    at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.selectAllRows(DatasourceCallQueryMechanism.java:618)
    at org.eclipse.persistence.internal.queries.ExpressionQueryMechanism.selectAllRowsFromTable(ExpressionQueryMechanism.java:2537)
    at org.eclipse.persistence.internal.queries.ExpressionQueryMechanism.selectAllReportQueryRows(ExpressionQueryMechanism.java:2480)
    at org.eclipse.persistence.queries.ReportQuery.executeDatabaseQuery(ReportQuery.java:838)
    at org.eclipse.persistence.queries.DatabaseQuery.execute(DatabaseQuery.java:675)
    at org.eclipse.persistence.queries.ObjectLevelReadQuery.execute(ObjectLevelReadQuery.java:958)
    at org.eclipse.persistence.queries.ReadAllQuery.execute(ReadAllQuery.java:432)
    at org.eclipse.persistence.queries.ObjectLevelReadQuery.executeInUnitOfWork(ObjectLevelReadQuery.java:1021)
    at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.internalExecuteQuery(UnitOfWorkImpl.java:2857)
    at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1225)
    at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1207)
    at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1181)
    at org.eclipse.persistence.internal.jpa.EJBQueryImpl.executeReadQuery(EJBQueryImpl.java:453)
    at org.eclipse.persistence.internal.jpa.EJBQueryImpl.getSingleResult(EJBQueryImpl.java:714)
    at ejbs.BuyersRegistratorEJB.nickNameAlreadyExists(BuyersRegistratorEJB.java:82)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
    at java.lang.reflect.Method.invoke(Method.java:597)
    at org.glassfish.ejb.security.application.EJBSecurityManager.runMethod(EJBSecurityManager.java:1056)
    at org.glassfish.ejb.security.application.EJBSecurityManager.invoke(EJBSecurityManager.java:1128)
    at com.sun.ejb.containers.BaseContainer.invokeBeanMethod(BaseContainer.java:5292)
    at com.sun.ejb.EjbInvocation.invokeBeanMethod(EjbInvocation.java:615)
    at com.sun.ejb.containers.interceptors.AroundInvokeChainImpl.invokeNext(InterceptorManager.java:797)
    at com.sun.ejb.EjbInvocation.proceed(EjbInvocation.java:567)
    at org.jboss.weld.ejb.SessionBeanInterceptor.aroundInvoke(SessionBeanInterceptor.java:47)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
    at java.lang.reflect.Method.invoke(Method.java:597)
    at com.sun.ejb.containers.interceptors.AroundInvokeInterceptor.intercept(InterceptorManager.java:858)
    at com.sun.ejb.containers.interceptors.AroundInvokeChainImpl.invokeNext(InterceptorManager.java:797)
    at com.sun.ejb.EjbInvocation.proceed(EjbInvocation.java:567)
    at com.sun.ejb.containers.interceptors.SystemInterceptorProxy.doAround(SystemInterceptorProxy.java:157)
    at com.sun.ejb.containers.interceptors.SystemInterceptorProxy.aroundInvoke(SystemInterceptorProxy.java:139)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
    at java.lang.reflect.Method.invoke(Method.java:597)
    at com.sun.ejb.containers.interceptors.AroundInvokeInterceptor.intercept(InterceptorManager.java:858)
    at com.sun.ejb.containers.interceptors.AroundInvokeChainImpl.invokeNext(InterceptorManager.java:797)
    at com.sun.ejb.containers.interceptors.InterceptorManager.intercept(InterceptorManager.java:367)
    at com.sun.ejb.containers.BaseContainer.__intercept(BaseContainer.java:5264)
    at com.sun.ejb.containers.BaseContainer.intercept(BaseContainer.java:5252)
    at com.sun.ejb.containers.EJBLocalObjectInvocationHandler.invoke(EJBLocalObjectInvocationHandler.java:190)
    at com.sun.ejb.containers.EJBLocalObjectInvocationHandlerDelegate.invoke(EJBLocalObjectInvocationHandlerDelegate.java:84)
    at $Proxy174.nickNameAlreadyExists(Unknown Source)
    at managedbeans.RegistrationController.validateNickName(RegistrationController.java:170)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
    at java.lang.reflect.Method.invoke(Method.java:597)
    at com.sun.el.parser.AstValue.invoke(AstValue.java:234)
    at com.sun.el.MethodExpressionImpl.invoke(MethodExpressionImpl.java:297)
    at org.jboss.weld.util.el.ForwardingMethodExpression.invoke(ForwardingMethodExpression.java:43)
    at org.jboss.weld.el.WeldMethodExpression.invoke(WeldMethodExpression.java:72)
    at com.sun.faces.facelets.el.TagMethodExpression.invoke(TagMethodExpression.java:98)
    at javax.faces.validator.MethodExpressionValidator.validate(MethodExpressionValidator.java:95)
    at javax.faces.component.UIInput.validateValue(UIInput.java:1127)
    at javax.faces.component.UIInput.validate(UIInput.java:941)
    at javax.faces.component.UIInput.executeValidate(UIInput.java:1189)
    at javax.faces.component.UIInput.processValidators(UIInput.java:691)
    at javax.faces.component.UIForm.processValidators(UIForm.java:243)
    at javax.faces.component.UIComponentBase.processValidators(UIComponentBase.java:1080)
    at javax.faces.component.UIComponentBase.processValidators(UIComponentBase.java:1080)
    at javax.faces.component.UIViewRoot.processValidators(UIViewRoot.java:1180)
    at com.sun.faces.lifecycle.ProcessValidationsPhase.execute(ProcessValidationsPhase.java:76)
    at com.sun.faces.lifecycle.Phase.doPhase(Phase.java:101)
    at com.sun.faces.lifecycle.LifecycleImpl.execute(LifecycleImpl.java:118)
    at javax.faces.webapp.FacesServlet.service(FacesServlet.java:312)
    at org.apache.catalina.core.StandardWrapper.service(StandardWrapper.java:1523)
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:279)
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:188)
    at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:641)
    at com.sun.enterprise.web.WebPipeline.invoke(WebPipeline.java:97)
    at com.sun.enterprise.web.PESessionLockingStandardPipeline.invoke(PESessionLockingStandardPipeline.java:85)
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:185)
    at org.apache.catalina.connector.CoyoteAdapter.doService(CoyoteAdapter.java:325)
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:226)
    at com.sun.enterprise.v3.services.impl.ContainerMapper.service(ContainerMapper.java:165)
    at com.sun.grizzly.http.ProcessorTask.invokeAdapter(ProcessorTask.java:791)
    at com.sun.grizzly.http.ProcessorTask.doProcess(ProcessorTask.java:693)
    at com.sun.grizzly.http.ProcessorTask.process(ProcessorTask.java:954)
    at com.sun.grizzly.http.DefaultProtocolFilter.execute(DefaultProtocolFilter.java:170)
    at com.sun.grizzly.DefaultProtocolChain.executeProtocolFilter(DefaultProtocolChain.java:135)
    at com.sun.grizzly.DefaultProtocolChain.execute(DefaultProtocolChain.java:102)
    at com.sun.grizzly.DefaultProtocolChain.execute(DefaultProtocolChain.java:88)
    at com.sun.grizzly.http.HttpProtocolChain.execute(HttpProtocolChain.java:76)
    at com.sun.grizzly.ProtocolChainContextTask.doCall(ProtocolChainContextTask.java:53)
    at com.sun.grizzly.SelectionKeyContextTask.call(SelectionKeyContextTask.java:57)
    at com.sun.grizzly.ContextTask.run(ContextTask.java:69)
    at com.sun.grizzly.util.AbstractThreadPool$Worker.doWork(AbstractThreadPool.java:330)
    at com.sun.grizzly.util.AbstractThreadPool$Worker.run(AbstractThreadPool.java:309)
    at java.lang.Thread.run(Thread.java:662)
Caused by: java.sql.SQLSyntaxErrorException: Table/View 'BUYER' does not exist.
    at org.apache.derby.client.am.SQLExceptionFactory40.getSQLException(Unknown Source)
    at org.apache.derby.client.am.SqlException.getSQLException(Unknown Source)
    at org.apache.derby.client.am.Connection.prepareStatement(Unknown Source)
    at com.sun.gjc.spi.base.ConnectionHolder.prepareStatement(ConnectionHolder.java:535)
    at com.sun.gjc.spi.jdbc40.ConnectionWrapper40.prepareCachedStatement(ConnectionWrapper40.java:251)
    at com.sun.gjc.spi.jdbc40.ConnectionWrapper40.prepareCachedStatement(ConnectionWrapper40.java:48)
    at com.sun.gjc.spi.ManagedConnection.prepareCachedStatement(ManagedConnection.java:880)
    at com.sun.gjc.spi.jdbc40.ConnectionWrapper40.prepareStatement(ConnectionWrapper40.java:169)
    at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.prepareStatement(DatabaseAccessor.java:1404)
    at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.prepareStatement(DatabaseAccessor.java:1353)
    at org.eclipse.persistence.internal.databaseaccess.DatabaseCall.prepareStatement(DatabaseCall.java:645)
    at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:580)
    ... 99 more
Caused by: org.apache.derby.client.am.SqlException: Table/View 'BUYER' does not exist.
    at org.apache.derby.client.am.Statement.completeSqlca(Unknown Source)
    at org.apache.derby.client.net.NetStatementReply.parsePrepareError(Unknown Source)
    at org.apache.derby.client.net.NetStatementReply.parsePRPSQLSTTreply(Unknown Source)
    at org.apache.derby.client.net.NetStatementReply.readPrepareDescribeOutput(Unknown Source)
    at org.apache.derby.client.net.StatementReply.readPrepareDescribeOutput(Unknown Source)
    at org.apache.derby.client.net.NetStatement.readPrepareDescribeOutput_(Unknown Source)
    at org.apache.derby.client.am.Statement.readPrepareDescribeOutput(Unknown Source)
    at org.apache.derby.client.am.PreparedStatement.readPrepareDescribeInputOutput(Unknown Source)
    at org.apache.derby.client.am.PreparedStatement.flowPrepareDescribeInputOutput(Unknown Source)
    at org.apache.derby.client.am.PreparedStatement.prepare(Unknown Source)
    at org.apache.derby.client.am.Connection.prepareStatementX(Unknown Source)
    ... 109 more

Here is the EJB that the error says is making the problem:

这是错误所说的导致问题的 EJB：

@Stateless(name = "ejbs/BuyersRegistratorEJB")
public class BuyersRegistratorEJB implements IBuyersRegistratorEJB {
  @PersistenceContext
  private EntityManager em;

  @Override
  public Buyer createBuyer(Buyer buyer) {
    Date date = new Date();
    DateFormat dateFormat = new SimpleDateFormat("dd/MM/yyyy HH:mm:ss");
    buyer.setRegistrationDate(dateFormat.format(date));

    boolean textPatterCorrect = false;
    // TODO: Prepare regular expresion also for serbian latin characters
    String simpleTextPatternText = "^[a-zA-Z0-9]+$";
    Pattern textPattern = null;
    Matcher nameMatcher = null;
    Matcher secondNameMatcher = null;
    Matcher nickNameMatcher = null;
    Matcher passwordMatcher = null;

    textPattern = Pattern.compile(simpleTextPatternText);
    nameMatcher = textPattern.matcher(buyer.getName());
    secondNameMatcher = textPattern.matcher(buyer.getSecondName());
    nickNameMatcher = textPattern.matcher(buyer.getNickName());
    passwordMatcher = textPattern.matcher(buyer.getPassword());

    if (nameMatcher.matches() && secondNameMatcher.matches()
        && nickNameMatcher.matches() && passwordMatcher.matches()) {
      textPatterCorrect = true;
    } else {
      System.out
          .println("SOME OF THE INPUT DO NOT MATCH THE REGULAR EXPRESION FOR TEXT!");
    }

    if (textPatterCorrect) {
      em.persist(buyer);// EJB validation passed. Handle the input to the
                // next layer.
      return buyer;
    } else {
      throw new RuntimeException(
          "[BuyersRegistrationEJB] Text format validation FAILED!");
    }
  }

  // This will check if the email already exists!
  @Override
  public boolean emailAlreadyExists(String value) {
    Query checkEmailExists = em
        .createQuery("SELECT COUNT(b.email) FROM Buyer b WHERE b.email=:emailparam");
    checkEmailExists.setParameter("emailparam", value);
    long matchCounter = 0;
    matchCounter = (Long) checkEmailExists.getSingleResult();
    if (matchCounter > 0) {
      return true;
    }
    return false;
  }

  // This will check if the nickName already exists!
  @Override
  public boolean nickNameAlreadyExists(String value) {
    Query nickNameExists = em.createQuery("SELECT COUNT(n.nickName) FROM Buyer n WHERE n.nickName=:nicknameparam");
    nickNameExists.setParameter("nicknameparam", value);
    long matchCounter = 0;
    matchCounter = (Long) nickNameExists.getSingleResult();
    if (matchCounter > 0) {
      return true;
    }
    return false;
  }
}

I will post my configuration files also:

我也会发布我的配置文件：

sun-resources.xml

太阳资源.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE resources PUBLIC "-//Sun Microsystems, Inc.//DTD Application Server 9.0   Resource Definitions //EN" "http://www.sun.com/software/appserver/dtds/sun-resources_1_3.dtd">
<resources>
  <jdbc-resource enabled="true" jndi-name="jdbc/myDatasource" object-type="user" pool-name="Derby_groupbuydb_userPool"/>

  <jdbc-connection-pool allow-non-component-callers="false" associate-with-thread="false" connection-creation-retry-attempts="0" connection-creation-retry-interval-in-seconds="10" connection-leak-reclaim="false" connection-leak-timeout-in-seconds="0" connection-validation-method="auto-commit" datasource-classname="org.apache.derby.jdbc.ClientDataSource" fail-all-connections="false" idle-timeout-in-seconds="300" is-connection-validation-required="false" is-isolation-level-guaranteed="true" lazy-connection-association="false" lazy-connection-enlistment="false" match-connections="false" max-connection-usage-count="0" max-pool-size="32" max-wait-time-in-millis="60000" name="Derby_groupbuydb_userPool" non-transactional-connections="false" pool-resize-quantity="2" res-type="javax.sql.DataSource" statement-timeout-in-seconds="-1" steady-pool-size="8" validate-atmost-once-period-in-seconds="0" wrap-jdbc-objects="false">
    <property name="serverName" value="localhost"/>
    <property name="PortNumber" value="1527"/>
    <property name="DatabaseName" value="groupbuydb"/>
    <property name="User" value="user"/>
    <property name="Password" value="pwd"/>
    <property name="URL" value="jdbc:derby://localhost:1527/groupbuydb;create=true"/>
    <property name="driverClass" value="org.apache.derby.jdbc.ClientDriver"/>
  </jdbc-connection-pool>
</resources>

persistence.xml

持久化文件

<?xml version="1.0" encoding="UTF-8"?>
<persistence version="2.0" xmlns="http://java.sun.com/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd">
  <persistence-unit name="GroupBuySystem">
    <jta-data-source>jdbc/myDatasource</jta-data-source>
    <class>entities.Administ</class>
    <class>entities.Buyer</class>
    <class>entities.Comment</class>
    <class>entities.Log</class>
    <class>entities.Offer</class>
    <class>entities.Seller</class>
  </persistence-unit>
</persistence>

This all worked correctly 3 weeks ago, but now it doesn't. I dont know what is wrong.

这一切在 3 周前都正常工作，但现在却没有了。我不知道出了什么问题。

回答by Kieren Johnstone

There are several possible reasons:

有几个可能的原因：

1. Your table has been deleted
2. Your credentials can no longer see or access it
3. Your credentials have changed
4. Your configuration has become corrupted, or changed
5. You are looking at the wrong database, or something similar.

1. 您的表已被删除
2. 您的凭据无法再查看或访问
3. 您的凭据已更改
4. 您的配置已损坏或更改
5. 您正在查看错误的数据库或类似内容。

You need to rule each one of these out specifically in order, and you will be left with your answer. It's very likely one of these :)

您需要按顺序专门排除其中的每一个，然后您就会得到答案。这很可能是其中之一:)

回答by Hendra Rismana

I've just finished with this problem. I used Ecommerce Affablebean in NetBeans. And I found out that this code:

我刚刚解决了这个问题。我在 NetBeans 中使用了 Ecommerce Affablebean。我发现这段代码：

<resource-ref>
<description>Connects to database for AffableBean application</description>
<res-ref-name>jdbc/affablebean</res-ref-name>
<res-type>javax.sql.DataSource</res-type>
<res-auth>Container</res-auth>
<res-sharing-scope>Shareable</res-sharing-scope>

Should be like this

应该是这样的

<resource-ref>
<description>Connects to database for AffableBean application</description>
<res-ref-name>jdbc/affablebean</res-ref-name>
<res-type>javax.sql.ConnectionPoolDataSource</res-type>
<res-auth>Container</res-auth>
<res-sharing-scope>Shareable</res-sharing-scope>