はじめに
以下の書籍をやっていたところDBのクエリ周りでつまづいたのでまとめます。
最終的には代替えで動くようにしていますが、根本的な解決はできていませんのでわかるかたはコメントいただきたいです。
問題
ToDoリストを新規作成する処理があります。ここでIDはレコードの最後のIDを取得するようにしています
JdbcTaskRepository.kt
override fun create(content: String): Task {
jdbcTemplate.update("INSERT INTO task(content) VALUES(?)", content)
// val id = jdbcTemplate.queryForObject("SELECT last_insert_id()", Long::class.java)
val id: Long = jdbcTemplate.queryForObject("SELECT last_insert_id()")
return Task(id, content, false)
}
しかしこのコードを実行して、ToDo作成をするとid取得部分でエラーが発生します
kotlin | 2022-12-14T03:27:45.447Z DEBUG 310 --- [nio-8080-exec-3] o.s.web.servlet.DispatcherServlet : Failed to complete request: org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [SELECT last_insert_id() from tasks]
kotlin | 2022-12-14T03:27:45.448Z ERROR 310 --- [nio-8080-exec-3] o.a.c.c.C.[.[.[/].[dispatcherServlet] : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed: org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [SELECT last_insert_id() from tasks]] with root cause
kotlin |
kotlin | org.h2.jdbc.JdbcSQLSyntaxErrorException: Function "LAST_INSERT_ID" not found; SQL statement:
kotlin | SELECT last_insert_id() from tasks [90022-214]
kotlin | at org.h2.message.DbException.getJdbcSQLException(DbException.java:632) ~[h2-2.1.214.jar:2.1.214]
kotlin | at org.h2.message.DbException.getJdbcSQLException(DbException.java:477) ~[h2-2.1.214.jar:2.1.214]
kotlin | at org.h2.message.DbException.get(DbException.java:223) ~[h2-2.1.214.jar:2.1.214]
kotlin | at org.h2.message.DbException.get(DbException.java:199) ~[h2-2.1.214.jar:2.1.214]
kotlin | at org.h2.command.Parser.readFunction(Parser.java:3847) ~[h2-2.1.214.jar:2.1.214]
kotlin | at org.h2.command.Parser.readTermWithIdentifier(Parser.java:5211) ~[h2-2.1.214.jar:2.1.214]
kotlin | at org.h2.command.Parser.readTerm(Parser.java:4901) ~[h2-2.1.214.jar:2.1.214]
kotlin | at org.h2.command.Parser.readFactor(Parser.java:3398) ~[h2-2.1.214.jar:2.1.214]
kotlin | at org.h2.command.Parser.readSum(Parser.java:3385) ~[h2-2.1.214.jar:2.1.214]
kotlin | at org.h2.command.Parser.readConcat(Parser.java:3350) ~[h2-2.1.214.jar:2.1.214]
kotlin | at org.h2.command.Parser.readCondition(Parser.java:3132) ~[h2-2.1.214.jar:2.1.214]
kotlin | at org.h2.command.Parser.readExpression(Parser.java:3053) ~[h2-2.1.214.jar:2.1.214]
kotlin | at org.h2.command.Parser.parseSelectExpressions(Parser.java:2853) ~[h2-2.1.214.jar:2.1.214]
kotlin | at org.h2.command.Parser.parseSelect(Parser.java:2871) ~[h2-2.1.214.jar:2.1.214]
kotlin | at org.h2.command.Parser.parseQueryPrimary(Parser.java:2762) ~[h2-2.1.214.jar:2.1.214]
kotlin | at org.h2.command.Parser.parseQueryTerm(Parser.java:2633) ~[h2-2.1.214.jar:2.1.214]
kotlin | at org.h2.command.Parser.parseQueryExpressionBody(Parser.java:2612) ~[h2-2.1.214.jar:2.1.214]
kotlin | at org.h2.command.Parser.parseQueryExpressionBodyAndEndOfQuery(Parser.java:2605) ~[h2-2.1.214.jar:2.1.214]
kotlin | at org.h2.command.Parser.parseQueryExpression(Parser.java:2598) ~[h2-2.1.214.jar:2.1.214]
kotlin | at org.h2.command.Parser.parseQuery(Parser.java:2567) ~[h2-2.1.214.jar:2.1.214]
kotlin | at org.h2.command.Parser.parsePrepared(Parser.java:724) ~[h2-2.1.214.jar:2.1.214]
kotlin | at org.h2.command.Parser.parse(Parser.java:689) ~[h2-2.1.214.jar:2.1.214]
kotlin | at org.h2.command.Parser.parse(Parser.java:661) ~[h2-2.1.214.jar:2.1.214]
kotlin | at org.h2.command.Parser.prepareCommand(Parser.java:569) ~[h2-2.1.214.jar:2.1.214]
kotlin | at org.h2.engine.SessionLocal.prepareLocal(SessionLocal.java:631) ~[h2-2.1.214.jar:2.1.214]
kotlin | at org.h2.engine.SessionLocal.prepareCommand(SessionLocal.java:554) ~[h2-2.1.214.jar:2.1.214]
kotlin | at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1116) ~[h2-2.1.214.jar:2.1.214]
kotlin | at org.h2.jdbc.JdbcStatement.executeQuery(JdbcStatement.java:92) ~[h2-2.1.214.jar:2.1.214]
kotlin | at com.zaxxer.hikari.pool.ProxyStatement.executeQuery(ProxyStatement.java:110) ~[HikariCP-5.0.1.jar:na]
kotlin | at com.zaxxer.hikari.pool.HikariProxyStatement.executeQuery(HikariProxyStatement.java) ~[HikariCP-5.0.1.jar:na]
kotlin | at org.springframework.jdbc.core.JdbcTemplate$1QueryStatementCallback.doInStatement(JdbcTemplate.java:452) ~[spring-jdbc-6.0.2.jar:6.0.2]
kotlin | at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:381) ~[spring-jdbc-6.0.2.jar:6.0.2]
kotlin | at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:465) ~[spring-jdbc-6.0.2.jar:6.0.2]
kotlin | at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:475) ~[spring-jdbc-6.0.2.jar:6.0.2]
kotlin | at org.springframework.jdbc.core.JdbcTemplate.queryForObject(JdbcTemplate.java:508) ~[spring-jdbc-6.0.2.jar:6.0.2]
kotlin | at org.springframework.jdbc.core.JdbcTemplate.queryForObject(JdbcTemplate.java:515) ~[spring-jdbc-6.0.2.jar:6.0.2]
kotlin | at com.example.todolist.JdbcTaskRepository.create(JdbcTaskRepository.kt:34) ~[main/:na]
kotlin | at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:na]
kotlin | at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:78) ~[na:na]
kotlin | at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:na]
kotlin | at java.base/java.lang.reflect.Method.invoke(Method.java:568) ~[na:na]
kotlin | at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:343) ~[spring-aop-6.0.2.jar:6.0.2]
kotlin | at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:196) ~[spring-aop-6.0.2.jar:6.0.2]
kotlin | at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163) ~[spring-aop-6.0.2.jar:6.0.2]
kotlin | at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:752) ~[spring-aop-6.0.2.jar:6.0.2]
kotlin | at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:137) ~[spring-tx-6.0.2.jar:6.0.2]
kotlin | at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184) ~[spring-aop-6.0.2.jar:6.0.2]
kotlin | at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:752) ~[spring-aop-6.0.2.jar:6.0.2]
kotlin | at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:703) ~[spring-aop-6.0.2.jar:6.0.2]
kotlin | at com.example.todolist.JdbcTaskRepository$$SpringCGLIB$$0.create(<generated>) ~[main/:na]
kotlin | at com.example.todolist.TaskController.create(TaskController.kt:36) ~[main/:na]
kotlin | at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:na]
kotlin | at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:78) ~[na:na]
kotlin | at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:na]
kotlin | at java.base/java.lang.reflect.Method.invoke(Method.java:568) ~[na:na]
kotlin | at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:207) ~[spring-web-6.0.2.jar:6.0.2]
kotlin | at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:152) ~[spring-web-6.0.2.jar:6.0.2]
kotlin | at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:117) ~[spring-webmvc-6.0.2.jar:6.0.2]
kotlin | at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:884) ~[spring-webmvc-6.0.2.jar:6.0.2]
kotlin | at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:797) ~[spring-webmvc-6.0.2.jar:6.0.2]
kotlin | at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:87) ~[spring-webmvc-6.0.2.jar:6.0.2]
kotlin | at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:1080) ~[spring-webmvc-6.0.2.jar:6.0.2]
kotlin | at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:973) ~[spring-webmvc-6.0.2.jar:6.0.2]
kotlin | at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:1003) ~[spring-webmvc-6.0.2.jar:6.0.2]
kotlin | at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:906) ~[spring-webmvc-6.0.2.jar:6.0.2]
kotlin | at jakarta.servlet.http.HttpServlet.service(HttpServlet.java:731) ~[tomcat-embed-core-10.1.1.jar:6.0]
kotlin | at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:880) ~[spring-webmvc-6.0.2.jar:6.0.2]
kotlin | at jakarta.servlet.http.HttpServlet.service(HttpServlet.java:814) ~[tomcat-embed-core-10.1.1.jar:6.0]
kotlin | at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:223) ~[tomcat-embed-core-10.1.1.jar:10.1.1]
kotlin | at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:158) ~[tomcat-embed-core-10.1.1.jar:10.1.1]
kotlin | at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53) ~[tomcat-embed-websocket-10.1.1.jar:10.1.1]
kotlin | at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:185) ~[tomcat-embed-core-10.1.1.jar:10.1.1]
kotlin | at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:158) ~[tomcat-embed-core-10.1.1.jar:10.1.1]
kotlin | at org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:100) ~[spring-web-6.0.2.jar:6.0.2]
kotlin | at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:116) ~[spring-web-6.0.2.jar:6.0.2]
kotlin | at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:185) ~[tomcat-embed-core-10.1.1.jar:10.1.1]
kotlin | at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:158) ~[tomcat-embed-core-10.1.1.jar:10.1.1]
kotlin | at org.springframework.web.filter.FormContentFilter.doFilterInternal(FormContentFilter.java:93) ~[spring-web-6.0.2.jar:6.0.2]
kotlin | at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:116) ~[spring-web-6.0.2.jar:6.0.2]
kotlin | at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:185) ~[tomcat-embed-core-10.1.1.jar:10.1.1]
kotlin | at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:158) ~[tomcat-embed-core-10.1.1.jar:10.1.1]
kotlin | at org.springframework.web.filter.HiddenHttpMethodFilter.doFilterInternal(HiddenHttpMethodFilter.java:91) ~[spring-web-6.0.2.jar:6.0.2]
kotlin | at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:116) ~[spring-web-6.0.2.jar:6.0.2]
kotlin | at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:185) ~[tomcat-embed-core-10.1.1.jar:10.1.1]
kotlin | at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:158) ~[tomcat-embed-core-10.1.1.jar:10.1.1]
kotlin | at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:201) ~[spring-web-6.0.2.jar:6.0.2]
kotlin | at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:116) ~[spring-web-6.0.2.jar:6.0.2]
kotlin | at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:185) ~[tomcat-embed-core-10.1.1.jar:10.1.1]
kotlin | at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:158) ~[tomcat-embed-core-10.1.1.jar:10.1.1]
kotlin | at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:197) ~[tomcat-embed-core-10.1.1.jar:10.1.1]
kotlin | at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:97) ~[tomcat-embed-core-10.1.1.jar:10.1.1]
kotlin | at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:542) ~[tomcat-embed-core-10.1.1.jar:10.1.1]
kotlin | at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:119) ~[tomcat-embed-core-10.1.1.jar:10.1.1]
kotlin | at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92) ~[tomcat-embed-core-10.1.1.jar:10.1.1]
kotlin | at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:78) ~[tomcat-embed-core-10.1.1.jar:10.1.1]
kotlin | at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:357) ~[tomcat-embed-core-10.1.1.jar:10.1.1]
kotlin | at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:400) ~[tomcat-embed-core-10.1.1.jar:10.1.1]
kotlin | at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:65) ~[tomcat-embed-core-10.1.1.jar:10.1.1]
kotlin | at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:861) ~[tomcat-embed-core-10.1.1.jar:10.1.1]
kotlin | at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1739) ~[tomcat-embed-core-10.1.1.jar:10.1.1]
kotlin | at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:52) ~[tomcat-embed-core-10.1.1.jar:10.1.1]
kotlin | at org.apache.tomcat.util.threads.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1191) ~[tomcat-embed-core-10.1.1.jar:10.1.1]
kotlin | at org.apache.tomcat.util.threads.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:659) ~[tomcat-embed-core-10.1.1.jar:10.1.1]
kotlin | at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61) ~[tomcat-embed-core-10.1.1.jar:10.1.1]
kotlin | at java.base/java.lang.Thread.run(Thread.java:831) ~[na:na]
kotlin |
kotlin | 2022-12-14T03:27:45.461Z DEBUG 310 --- [nio-8080-exec-3] o.s.web.servlet.DispatcherServlet : "ERROR" dispatch for POST "/error", parameters={masked}
kotlin | 2022-12-14T03:27:45.465Z DEBUG 310 --- [nio-8080-exec-3] s.w.s.m.m.a.RequestMappingHandlerMapping : Mapped to org.springframework.boot.autoconfigure.web.servlet.error.BasicErrorController#errorHtml(HttpServletRequest, HttpServletResponse)
kotlin | 2022-12-14T03:27:45.474Z DEBUG 310 --- [nio-8080-exec-3] o.s.w.s.v.ContentNegotiatingViewResolver : Selected 'text/html' given [text/html, text/html;q=0.8]
kotlin | 2022-12-14T03:27:45.479Z DEBUG 310 --- [nio-8080-exec-3] o.s.web.servlet.DispatcherServlet : Exiting from "ERROR" dispatch, status 500
解決方法
なぜlast_insert_id()が使えないのかはよくわかりませんでした。
ですので、同じ動きをするクエリを書いて回避しました
JdbcTaskRepository.kt
override fun create(content: String): Task {
jdbcTemplate.update("INSERT INTO task(content) VALUES(?)", content)
// val id = jdbcTemplate.queryForObject("SELECT last_insert_id()", Long::class.java)
// val id: Long = jdbcTemplate.queryForObject("SELECT last_insert_id()")
val id: Long = jdbcTemplate.queryForObject("SELECT id FROM task ORDER BY id DESC LIMIT 1")
return Task(id, content, false)
}
SELECT id FROM task ORDER BY id DESC LIMIT 1とするとこで最後のtaskテーブルのIDを取得することができます
おわりに
このハンズオンならidを適当にして返しても作成はできるので問題ない気もしましたが、代替えを考えてみました
H2ならinsert_last_id()が使えるようなのですが、調べてもあまり情報がなかったので今回は飛ばしました
参考