使用@ usestringtemplate 3 statementlocator时，JDBI给出错误“ORA-00900:无效的SQL语句”

Question

我正在尝试创建一个与DAO.sql链接的DAO接口。stg字符串模板文件。查询非常简单，只需按id选择。

MyDao.groovy:

@UseStringTemplate3StatementLocator
@RegisterMapperFactory(BeanMapperFactory.class)
interface MyDao {
  @SqlQuery
  Foo getFooById(@Bind("fooId") long fooId)
}

MyDao.sql.stg：

group MyDao;

getFooById() ::= <<
    SELECT bar, baz
    FROM foos
    WHERE fooid = (:fooId)
>>

我的后端数据库是甲骨文。我在甲骨文SQLDeveloper中尝试过这个查询，它工作得很好，但是当我使用应用程序和sql.stg文件运行查询时，它会给我：

java.sql.SQLSyntaxErrorException: ORA-00900: invalid SQL statement

我怀疑它不知怎么找不到sql。stg文件，即使我在POM中包含了资源目录。

如何让JDBI找到正确的sql.stg字符串模板文件？我在这个应用程序中遵循了其他Dao的例子，它们使用完全相同的设置。

编辑：请注意，如果我将查询字符串硬编码到 DAO 接口中的@SqlQuery注释中，而不是使用 stringtemplate 文件，那么它就可以正常工作。所以问题肯定是 dao 无法正确找到或处理字符串模板文件。以下是完整的堆栈跟踪：

ERROR [2015-04-15 11:22:29,588] com.yammer.dropwizard.jdbi.jersey.LoggingDBIExceptionMapper: Error handling a request: a5dfcceea8110fd1
! java.sql.SQLSyntaxErrorException: ORA-00900: invalid SQL statement

! at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:445) ~[ojdbc6-11.2.0.3.jar:11.2.0.3.0]
! at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396) ~[ojdbc6-11.2.0.3.jar:11.2.0.3.0]
! at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:879) ~[ojdbc6-11.2.0.3.jar:11.2.0.3.0]
! at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:450) ~[ojdbc6-11.2.0.3.jar:11.2.0.3.0]
! at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:192) ~[ojdbc6-11.2.0.3.jar:11.2.0.3.0]
! at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:531) ~[ojdbc6-11.2.0.3.jar:11.2.0.3.0]
! at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:207) ~[ojdbc6-11.2.0.3.jar:11.2.0.3.0]
! at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:1044) ~[ojdbc6-11.2.0.3.jar:11.2.0.3.0]
! at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1329) ~[ojdbc6-11.2.0.3.jar:11.2.0.3.0]
! at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3584) ~[ojdbc6-11.2.0.3.jar:11.2.0.3.0]
! at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3685) ~[ojdbc6-11.2.0.3.jar:11.2.0.3.0]
! at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePreparedStatementWrapper.java:1376) ~[ojdbc6-11.2.0.3.jar:11.2.0.3.0]
! at org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:172) ~[tomcat-dbcp-7.0.37.jar:7.0.37]
! at org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:172) ~[tomcat-dbcp-7.0.37.jar:7.0.37]
! at org.skife.jdbi.v2.SQLStatement.internalExecute(SQLStatement.java:1300) ~[jdbi-2.41.jar:na]
! at org.skife.jdbi.v2.Query.fold(Query.java:172) ~[jdbi-2.41.jar:na]
! at org.skife.jdbi.v2.Query.first(Query.java:267) ~[jdbi-2.41.jar:na]
! at org.skife.jdbi.v2.Query.first(Query.java:259) ~[jdbi-2.41.jar:na]
! at org.skife.jdbi.v2.sqlobject.ResultReturnThing$SingleValueResultReturnThing.result(ResultReturnThing.java:91) ~[jdbi-2.41.jar:na]
! at org.skife.jdbi.v2.sqlobject.ResultReturnThing.map(ResultReturnThing.java:32) ~[jdbi-2.41.jar:na]
! at org.skife.jdbi.v2.sqlobject.QueryHandler.invoke(QueryHandler.java:45) ~[jdbi-2.41.jar:na]
! at org.skife.jdbi.v2.sqlobject.SqlObject.invoke(SqlObject.java:147) ~[jdbi-2.41.jar:na]
! at org.skife.jdbi.v2.sqlobject.SqlObject$1.intercept(SqlObject.java:60) ~[jdbi-2.41.jar:na]
! at org.skife.jdbi.v2.sqlobject.CloseInternalDoNotUseThisClass$$EnhancerByCGLIB$$19df5856.getFooById(<generated>) ~[jdbi-2.41.jar:na]
! at com.mydomain.mypackage.dao.MyDAO$getFooById.call(Unknown Source) ~[na:na]
! at org.codehaus.groovy.runtime.callsite.CallSiteArray.defaultCall(CallSiteArray.java:45) ~[groovy-all-2.2.1.jar:2.2.1]
! at org.codehaus.groovy.runtime.callsite.AbstractCallSite.call(AbstractCallSite.java:108) ~[groovy-all-2.2.1.jar:2.2.1]
! at org.codehaus.groovy.runtime.callsite.AbstractCallSite.call(AbstractCallSite.java:116) ~[groovy-all-2.2.1.jar:2.2.1]
! at com.mydomain.mypackage.resources.FooResource.testDaoGetter(FooResource.groovy:374) ~[classes/:na]
! at com.mydomain.mypackage.resources.FooResource.this$3$testDaoGetter(FooResource.groovy) ~[classes/:na]
! at com.mydomain.mypackage.resources.FooResource$this$3$testDaoGetter$3.callCurrent(Unknown Source) ~[na:na]
! at org.codehaus.groovy.runtime.callsite.CallSiteArray.defaultCallCurrent(CallSiteArray.java:49) ~[groovy-all-2.2.1.jar:2.2.1]
! at org.codehaus.groovy.runtime.callsite.AbstractCallSite.callCurrent(AbstractCallSite.java:133) ~[groovy-all-2.2.1.jar:2.2.1]
! at com.mydomain.mypackage.resources.FooResource.test(FooResource.groovy:352) ~[classes/:na]
! at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.7.0_75]
! at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) ~[na:1.7.0_75]
! at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.7.0_75]
! at java.lang.reflect.Method.invoke(Method.java:606) ~[na:1.7.0_75]
! at com.sun.jersey.spi.container.JavaMethodInvokerFactory$1.invoke(JavaMethodInvokerFactory.java:60) ~[jersey-server-1.17.1.jar:1.17.1]
! at com.sun.jersey.server.impl.model.method.dispatch.AbstractResourceMethodDispatchProvider$ResponseOutInvoker._dispatch(AbstractResourceMethodDispatchProvider.java:205) ~[jersey-server-1.17.1.jar:1.17.1]
! at com.sun.jersey.server.impl.model.method.dispatch.ResourceJavaMethodDispatcher.dispatch(ResourceJavaMethodDispatcher.java:75) ~[jersey-server-1.17.1.jar:1.17.1]
! at com.yammer.metrics.jersey.InstrumentedResourceMethodDispatchProvider$TimedRequestDispatcher.dispatch(InstrumentedResourceMethodDispatchProvider.java:32) ~[metrics-jersey-2.2.0.jar:na]
! at com.yammer.dropwizard.jersey.OptionalResourceMethodDispatchAdapter$OptionalRequestDispatcher.dispatch(OptionalResourceMethodDispatchAdapter.java:37) ~[dropwizard-core-0.6.2.jar:na]
! at com.sun.jersey.server.impl.uri.rules.HttpMethodRule.accept(HttpMethodRule.java:302) ~[jersey-server-1.17.1.jar:1.17.1]
! at com.sun.jersey.server.impl.uri.rules.RightHandPathRule.accept(RightHandPathRule.java:147) ~[jersey-server-1.17.1.jar:1.17.1]
! at com.sun.jersey.server.impl.uri.rules.ResourceObjectRule.accept(ResourceObjectRule.java:100) ~[jersey-server-1.17.1.jar:1.17.1]
! at com.sun.jersey.server.impl.uri.rules.RightHandPathRule.accept(RightHandPathRule.java:147) ~[jersey-server-1.17.1.jar:1.17.1]
! at com.sun.jersey.server.impl.uri.rules.RootResourceClassesRule.accept(RootResourceClassesRule.java:84) ~[jersey-server-1.17.1.jar:1.17.1]
! at com.sun.jersey.server.impl.application.WebApplicationImpl._handleRequest(WebApplicationImpl.java:1511) [jersey-server-1.17.1.jar:1.17.1]
! at com.sun.jersey.server.impl.application.WebApplicationImpl._handleRequest(WebApplicationImpl.java:1442) [jersey-server-1.17.1.jar:1.17.1]
! at com.sun.jersey.server.impl.application.WebApplicationImpl.handleRequest(WebApplicationImpl.java:1391) [jersey-server-1.17.1.jar:1.17.1]
! at com.sun.jersey.server.impl.application.WebApplicationImpl.handleRequest(WebApplicationImpl.java:1381) [jersey-server-1.17.1.jar:1.17.1]
! at com.sun.jersey.spi.container.servlet.WebComponent.service(WebComponent.java:416) [jersey-servlet-1.17.1.jar:1.17.1]
! at com.sun.jersey.spi.container.servlet.ServletContainer.service(ServletContainer.java:538) [jersey-servlet-1.17.1.jar:1.17.1]
! at com.sun.jersey.spi.container.servlet.ServletContainer.service(ServletContainer.java:716) [jersey-servlet-1.17.1.jar:1.17.1]
! at javax.servlet.http.HttpServlet.service(HttpServlet.java:848) [javax.servlet-3.0.0.v201112011016.jar:na]
! at org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:669) [jetty-servlet-8.1.10.v20130312.jar:8.1.10.v20130312]
! at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1448) [jetty-servlet-8.1.10.v20130312.jar:8.1.10.v20130312]
! at com.com.mydomain.mypackage.MyAuthenticationFilter.doFilter(MyAuthenticationFilter.java:99) [auth-filter-1.0.0.1.jar:na]
! at com.mydomain.mypackage.MyHttpFilter.doFilter(HttpFilter.java:32) [auth-filter-1.0.0.1.jar:na]
! at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1419) [jetty-servlet-8.1.10.v20130312.jar:8.1.10.v20130312]
! at com.yammer.dropwizard.servlets.ThreadNameFilter.doFilter(ThreadNameFilter.java:29) [dropwizard-core-0.6.2.jar:na]
! at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1419) [jetty-servlet-8.1.10.v20130312.jar:8.1.10.v20130312]
! at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:455) [jetty-servlet-8.1.10.v20130312.jar:8.1.10.v20130312]
! at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1075) [jetty-server-8.1.10.v20130312.jar:8.1.10.v20130312]
! at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:384) [jetty-servlet-8.1.10.v20130312.jar:8.1.10.v20130312]
! at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:1009) [jetty-server-8.1.10.v20130312.jar:8.1.10.v20130312]
! at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:135) [jetty-server-8.1.10.v20130312.jar:8.1.10.v20130312]
! at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:116) [jetty-server-8.1.10.v20130312.jar:8.1.10.v20130312]
! at com.yammer.metrics.jetty.InstrumentedHandler.handle(InstrumentedHandler.java:200) [metrics-jetty-2.2.0.jar:na]
! at org.eclipse.jetty.server.handler.HandlerCollection.handle(HandlerCollection.java:154) [jetty-server-8.1.10.v20130312.jar:8.1.10.v20130312]
! at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:116) [jetty-server-8.1.10.v20130312.jar:8.1.10.v20130312]
! at org.eclipse.jetty.server.Server.handle(Server.java:368) [jetty-server-8.1.10.v20130312.jar:8.1.10.v20130312]
! at org.eclipse.jetty.server.AbstractHttpConnection.handleRequest(AbstractHttpConnection.java:489) [jetty-server-8.1.10.v20130312.jar:8.1.10.v20130312]
! at org.eclipse.jetty.server.BlockingHttpConnection.handleRequest(BlockingHttpConnection.java:53) [jetty-server-8.1.10.v20130312.jar:8.1.10.v20130312]
! at org.eclipse.jetty.server.AbstractHttpConnection.content(AbstractHttpConnection.java:953) [jetty-server-8.1.10.v20130312.jar:8.1.10.v20130312]
! at org.eclipse.jetty.server.AbstractHttpConnection$RequestHandler.content(AbstractHttpConnection.java:1014) [jetty-server-8.1.10.v20130312.jar:8.1.10.v20130312]
! at org.eclipse.jetty.http.HttpParser.parseNext(HttpParser.java:861) [jetty-http-8.1.10.v20130312.jar:8.1.10.v20130312]
! at org.eclipse.jetty.http.HttpParser.parseAvailable(HttpParser.java:235) [jetty-http-8.1.10.v20130312.jar:8.1.10.v20130312]
! at org.eclipse.jetty.server.BlockingHttpConnection.handle(BlockingHttpConnection.java:72) [jetty-server-8.1.10.v20130312.jar:8.1.10.v20130312]
! at org.eclipse.jetty.server.bio.SocketConnector$ConnectorEndPoint.run(SocketConnector.java:264) [jetty-server-8.1.10.v20130312.jar:8.1.10.v20130312]
! at org.eclipse.jetty.server.ssl.SslSocketConnector$SslConnectorEndPoint.run(SslSocketConnector.java:670) [jetty-server-8.1.10.v20130312.jar:8.1.10.v20130312]
! at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:608) [jetty-util-8.1.10.v20130312.jar:8.1.10.v20130312]
! at org.eclipse.jetty.util.thread.QueuedThreadPool$3.run(QueuedThreadPool.java:543) [jetty-util-8.1.10.v20130312.jar:8.1.10.v20130312]
! at java.lang.Thread.run(Thread.java:745) [na:1.7.0_75]

Answer 1

哇哦。好的，事实证明它找不到字符串模板文件，因为它不在与DAO的那个包对应的目录中。我以为是。我的DAO在包里：

com.mydomain.mypackage

意思是目录

src/main/java/com/mydomain/mypackage

但是，字符串模板文件位于目录中

src/main/resources/com.mydomain.mypackage

当它应该在目录中时

src/main/resources/com/mydomain/mypackage

它们在Intellij中以相同的方式显示。