SQLSyntaxErrorException: Table / View ‘BUYER’ não existe. O que está faltando?

Algumas semanas atrás eu saí de férias e fiz uma pausa em um dos meus projetos. Quando voltei, estava apenas checando a página de registro e fiquei surpreso quando recebi uma SQLException dizendo que uma tabela não existe. Eu não entendo porque essa tabela existe, eu criei de uma entidade. Eu colei o código aqui para que você possa ver que tudo parece estar bem. Eu acho que provavelmente tem algo a ver com o database (eu uso glassfish 3 app server).

Aqui está uma imagem da interface do usuário que diz que o problema tem a ver com alguns methods de validação (Verificando se um usuário já existe e verificar se um email já existe):

insira a descrição da imagem aqui

Apenas no caso de eu também imprimir o 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 

Aqui está o EJB que o erro diz que está causando o problema:

 @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; } } 

Vou postar meus arquivos de configuração também:

sun-resources.xml

               

persistence.xml

    jdbc/myDatasource entities.Administ entities.Buyer entities.Comment entities.Log entities.Offer entities.Seller   

Isso tudo funcionou corretamente há 3 semanas, mas agora não funciona. Eu não sei o que está errado.

Existem várias razões possíveis:

  1. Sua tabela foi deletada
  2. Suas credenciais não podem mais ver ou acessá-lo
  3. Suas credenciais foram alteradas
  4. Sua configuração foi corrompida ou alterada
  5. Você está olhando para o database errado, ou algo similar.

Você precisa decidir cada um deles especificamente em ordem, e você ficará com sua resposta. É muito provável que um desses 🙂

Acabei de terminar com este problema. Eu usei o E-commerce Affablebean no NetBeans. E eu descobri que esse código:

  Connects to database for AffableBean application jdbc/affablebean javax.sql.DataSource Container Shareable 

Deve ser assim

  Connects to database for AffableBean application jdbc/affablebean javax.sql.ConnectionPoolDataSource Container Shareable