==UPDATE==
Since I can't answer my own question here is what my problem really was:
Manage @NamedNativeQuery and schema
==ORIGINAL POST:==
I created a project with dropwizard, and all my querys works ok, except for a NamedNativeQuery that throws a 'PSQLException: ERROR: relation "mytable" does not exist'. I have re reviewed my code and search on the forums but didn't find a solution. Here is my code:
yml config:
Database settings.
database:
driverClass: org.postgresql.Driver
user: myuser
password: myuserpassword
url: jdbc:postgresql://127.0.0.1/mydb
properties:
charSet: UTF-8
hibernate.dialect: org.hibernate.dialect.PostgreSQLDialect
hibernate.show_sql: false
hibernate.default_schema: market
maxWaitForConnection: 1s
validationQuery: "SELECT 1"
minSize: 8
maxSize: 32
checkConnectionWhileIdle: false
class:
@Entity
@Table(name="ok_purchases", uniqueConstraints = {@UniqueConstraint(columnNames={"idreg"})})
@NamedQueries({
@NamedQuery(
name = Purchases.findAll,
query = "FROM Purchases "
),
@NamedQuery(
name = Purchases.findByOriginalWorkflow,
query = "FROM Purchases WHERE idoriginalworkflow = :ORIGINAL_WORKFLOW_ID"
),
})
@NamedNativeQueries({
@NamedNativeQuery(
name = Purchases.rateById,
query = "SELECT count(p.idreg) , sum(p.rating) " +
"FROM ok_purchases p " +
"WHERE p.idoriginalworkflow = :ORIGINAL_WORKFLOW_ID "
)
})
The NamedQuerys works fine, the one with the problem is the NamedNativeQuery. Looking another example code that works and is very simmilar, when I call Purchases.rateById It should return an Object[] or List< Object[] > depending on the serviceDao method findUniqueWithNamedQuery or findWithNamedQuery. Here is an example of how I use it:
@UnitOfWork
@Path("rating/{idoriginalworkflow}")
@Produces(MediaType.APPLICATION_JSON)
public AvgRatingInfo getRatingByIdWorkflow(
@ApiParam(value = "idoriginalworkflow", required = true) @PathParam("idoriginalworkflow")String idoriginalworkflow){
Object[] rating = null;
try {
rating = serviceDAO.findUniqueWithNamedQuery(Purchases.rateById, QueryParameters.with("ORIGINAL_WORKFLOW_ID", idoriginalworkflow).parameters());
}catch (Exception e){
e.printStackTrace();
}
if(rating==null)
return new AvgRatingInfo();
AvgRatingInfo response = new AvgRatingInfo();
response.setTotalVotes( Integer.parseInt( rating[0].toString() ));
response.setTotalStars( Integer.parseInt( rating[1].toString() ) );
if(response.getTotalVotes()>0){
double avg = response.getTotalStars() / response.getTotalVotes();
response.setAvg(avg);
}
return response;
}
but it keep throwing the exception PSQLException: ERROR: relation "ok_purchases" does not exist, when it does exist, since the NamedQueries works ok. BTW I already test the query on the sql client and works ok, It returns the correct results.
Here is the full stack trace:
org.hibernate.exception.SQLGrammarException: could not extract ResultSet
at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:123)
at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:49)
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:126)
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:112)
at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:89)
at org.hibernate.loader.Loader.getResultSet(Loader.java:2065)
at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1862)
at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1838)
at org.hibernate.loader.Loader.doQuery(Loader.java:909)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:354)
at org.hibernate.loader.Loader.doList(Loader.java:2553)
at org.hibernate.loader.Loader.doList(Loader.java:2539)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2369)
at org.hibernate.loader.Loader.list(Loader.java:2364)
at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:353)
at org.hibernate.internal.SessionImpl.listCustomQuery(SessionImpl.java:1873)
at org.hibernate.internal.AbstractSessionImpl.list(AbstractSessionImpl.java:311)
at org.hibernate.internal.SQLQueryImpl.list(SQLQueryImpl.java:141)
at net.oklex.market.db.HibernateServiceDAO.findWithNamedQuery(HibernateServiceDAO.java:84)
at net.oklex.market.resources.PublicResource.getRatingByIdWorkflow(PublicResource.java:73)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:497)
at org.glassfish.jersey.server.model.internal.ResourceMethodInvocationHandlerFactory$1.invoke(ResourceMethodInvocationHandlerFactory.java:81)
at org.glassfish.jersey.server.model.internal.AbstractJavaResourceMethodDispatcher$1.run(AbstractJavaResourceMethodDispatcher.java:144)
at org.glassfish.jersey.server.model.internal.AbstractJavaResourceMethodDispatcher.invoke(AbstractJavaResourceMethodDispatcher.java:161)
at org.glassfish.jersey.server.model.internal.JavaResourceMethodDispatcherProvider$TypeOutInvoker.doDispatch(JavaResourceMethodDispatcherProvider.java:205)
at org.glassfish.jersey.server.model.internal.AbstractJavaResourceMethodDispatcher.dispatch(AbstractJavaResourceMethodDispatcher.java:99)
at org.glassfish.jersey.server.model.ResourceMethodInvoker.invoke(ResourceMethodInvoker.java:389)
at org.glassfish.jersey.server.model.ResourceMethodInvoker.apply(ResourceMethodInvoker.java:347)
at org.glassfish.jersey.server.model.ResourceMethodInvoker.apply(ResourceMethodInvoker.java:102)
at org.glassfish.jersey.server.ServerRuntime$2.run(ServerRuntime.java:308)
at org.glassfish.jersey.internal.Errors$1.call(Errors.java:271)
at org.glassfish.jersey.internal.Errors$1.call(Errors.java:267)
at org.glassfish.jersey.internal.Errors.process(Errors.java:315)
at org.glassfish.jersey.internal.Errors.process(Errors.java:297)
at org.glassfish.jersey.internal.Errors.process(Errors.java:267)
at org.glassfish.jersey.process.internal.RequestScope.runInScope(RequestScope.java:317)
at org.glassfish.jersey.server.ServerRuntime.process(ServerRuntime.java:291)
at org.glassfish.jersey.server.ApplicationHandler.handle(ApplicationHandler.java:1140)
at org.glassfish.jersey.servlet.WebComponent.service(WebComponent.java:403)
at org.glassfish.jersey.servlet.ServletContainer.service(ServletContainer.java:386)
at org.glassfish.jersey.servlet.ServletContainer.service(ServletContainer.java:334)
at org.glassfish.jersey.servlet.ServletContainer.service(ServletContainer.java:221)
at io.dropwizard.jetty.NonblockingServletHolder.handle(NonblockingServletHolder.java:49)
at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1669)
at org.eclipse.jetty.servlets.UserAgentFilter.doFilter(UserAgentFilter.java:83)
at org.eclipse.jetty.servlets.GzipFilter.doFilter(GzipFilter.java:364)
at io.dropwizard.jetty.BiDiGzipFilter.doFilter(BiDiGzipFilter.java:134)
at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1652)
at io.dropwizard.servlets.ThreadNameFilter.doFilter(ThreadNameFilter.java:29)
at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1652)
at io.dropwizard.jersey.filter.AllowedMethodsFilter.handle(AllowedMethodsFilter.java:44)
at io.dropwizard.jersey.filter.AllowedMethodsFilter.doFilter(AllowedMethodsFilter.java:39)
at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1652)
at org.eclipse.jetty.servlets.CrossOriginFilter.handle(CrossOriginFilter.java:259)
at org.eclipse.jetty.servlets.CrossOriginFilter.doFilter(CrossOriginFilter.java:222)
at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1652)
at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:585)
at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1127)
at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:515)
at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:1061)
at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:141)
at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:97)
at com.codahale.metrics.jetty9.InstrumentedHandler.handle(InstrumentedHandler.java:240)
at io.dropwizard.jetty.ContextRoutingHandler.handle(ContextRoutingHandler.java:38)
at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:97)
at org.eclipse.jetty.server.handler.RequestLogHandler.handle(RequestLogHandler.java:95)
at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:97)
at org.eclipse.jetty.server.handler.StatisticsHandler.handle(StatisticsHandler.java:159)
at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:97)
at org.eclipse.jetty.server.Server.handle(Server.java:497)
at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:310)
at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:257)
at org.eclipse.jetty.io.AbstractConnection$2.run(AbstractConnection.java:540)
at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:635)
at org.eclipse.jetty.util.thread.QueuedThreadPool$3.run(QueuedThreadPool.java:555)
at java.lang.Thread.run(Thread.java:745)
Caused by: org.postgresql.util.PSQLException: ERROR: relation "ok_purchases" does not exist
Position: 93
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2270)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1998)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:570)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:420)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:305)
at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:80)
... 74 more
