I've a issue when i try to store an hibernate spatial point to o PostgreSQL db with PostGis extensions installed.
Essentially, when I try to insert the record into the table the org.springframework.dao.InvalidDataAccessResourceUsageException is raised explaining that the org.postgis.PGgeometry could not be mapped into a SQL type.
The operating system is Windows 10 64 bit. The PostgreSQL server Database version is 9.6 64 bit on localhost The PostGis version is 2.3.2 64 bit The Tomcat version is 8.5.8 The JVM version is 1.8.0_112-b15 The Spring version is 4.1.4.RELEASE The Aspectj version is 1.8.4 The Hibernate version is 4.3.5.Final The PostGis JDBC driver version is 1.5.2 The hibernate dialect has been set to org.hibernate.spatial.dialect.postgis.PostgisDialect The datasource on Tomcat has been configured in this way:
<Resource name="jdbc/safetyAroundSchool" auth="Container" type="javax.sql.DataSource" maxActive="2" maxIdle="2" maxWait="10000" username="biotope-xxxx" password="xxxxxxxxxx" driverClassName="org.postgis.DriverWrapper" url="jdbc:postgresql_postGIS://localhost:5432/biotope-ssp"/>
But I've also used:
<Resource name="jdbc/safetyAroundSchool" auth="Container" type="javax.sql.DataSource" maxActive="2" maxIdle="2" maxWait="10000" username="biotope-xxxx" password="xxxxxxxxxxx" DriverClassName="org.postgresql.Driver" url="jdbc:postgresql://localhost:5432/biotope-ssp"/>
This is the the field declaration:
@Column(columnDefinition = "Geometry", nullable = true)
@Type(type="org.hibernate.spatial.GeometryType")
private Point location;
The table is created with the correct geometry type for the column this is the create script:
CREATE TABLE public.issue
(
id bigint NOT NULL,
date timestamp without time zone,
location geometry,
longdesc character varying(255) COLLATE pg_catalog."default",
shortdesc character varying(255) COLLATE pg_catalog."default",
type bigint,
userid bigint,
CONSTRAINT issue_pkey PRIMARY KEY (id)
);
And this is the error that i receive when I try to insert a new record.
18:14:11,878 WARN SqlExceptionHelper:144 - SQL Error: 0, SQLState: 07006 18:14:11,883 ERROR SqlExceptionHelper:146 - Non è possibile identificare il tipo SQL da usare per l'istanza di tipo «org.postgis.PGgeometry». Usare «setObject()» specificando esplicitamente il tipo da usare per questo valore. 18:14:11,898 ERROR WebApiExceptionProcessor:28 - Internal server error intercepted org.springframework.dao.InvalidDataAccessResourceUsageException: could not insert: [it.holonix.safetyAroundSchoold.entity.Issue]; SQL [insert into Issue (date, location, longDesc, shortDesc, type, userId, id) values (?, ?, ?, ?, ?, ?, ?)]; nested exception is org.hibernate.exception.SQLGrammarException: could not insert: [it.holonix.safetyAroundSchoold.entity.Issue] at org.springframework.orm.jpa.vendor.HibernateJpaDialect.convertHibernateAccessException(HibernateJpaDialect.java:231) at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:214) at org.springframework.orm.jpa.JpaTransactionManager.doCommit(JpaTransactionManager.java:521) at org.springframework.transaction.support.AbstractPlatformTransactionManager.processCommit(AbstractPlatformTransactionManager.java:757) at org.springframework.transaction.support.AbstractPlatformTransactionManager.commit(AbstractPlatformTransactionManager.java:726) at org.springframework.transaction.interceptor.TransactionAspectSupport.commitTransactionAfterReturning(TransactionAspectSupport.java:515) at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:291) at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:653) at it.holonix.safetyAroundSchoold.controller.IssueCtrl$$EnhancerBySpringCGLIB$$f6840c31.insertIssue() 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:498) at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:221) at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:137) at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:110) at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandleMethod(RequestMappingHandlerAdapter.java:777) at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:706) at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:85) at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:943) at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:877) at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:966) at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:868) at javax.servlet.http.HttpServlet.service(HttpServlet.java:648) at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:842) at javax.servlet.http.HttpServlet.service(HttpServlet.java:729) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:230) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:165) at it.holonix.webapi.auth.AuthRequestWrapperFilter.doFilter(AuthRequestWrapperFilter.java:23) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:192) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:165) at it.holonix.webapi.util.CorsFilter.doFilterInternal(CorsFilter.java:85) at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:192) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:165) at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:192) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:165) at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:198) at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:108) at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:472) at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:140) at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:79) at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:620) at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:87) at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:349) at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:783) at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:66) at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:789) at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1455) at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61) at java.lang.Thread.run(Thread.java:745) Caused by: org.hibernate.exception.SQLGrammarException: could not insert: [it.holonix.safetyAroundSchoold.entity.Issue] 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.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:3144) at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:3581) at org.hibernate.action.internal.EntityInsertAction.execute(EntityInsertAction.java:104) at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:463) at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:349) at org.hibernate.event.internal.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:350) at org.hibernate.event.internal.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:56) at org.hibernate.internal.SessionImpl.flush(SessionImpl.java:1222) at org.hibernate.internal.SessionImpl.managedFlush(SessionImpl.java:425) at org.hibernate.engine.transaction.internal.jdbc.JdbcTransaction.beforeTransactionCommit(JdbcTransaction.java:101) at org.hibernate.engine.transaction.spi.AbstractTransactionImpl.commit(AbstractTransactionImpl.java:177) at org.hibernate.jpa.internal.TransactionImpl.commit(TransactionImpl.java:77) at org.springframework.orm.jpa.JpaTransactionManager.doCommit(JpaTransactionManager.java:517) ... 54 more Caused by: org.postgresql.util.PSQLException: Non è possibile identificare il tipo SQL da usare per l'istanza di tipo «org.postgis.PGgeometry». Usare «setObject()» specificando esplicitamente il tipo da usare per questo valore. at org.postgresql.jdbc.PgPreparedStatement.setObject(PgPreparedStatement.java:967) at org.apache.tomcat.dbcp.dbcp2.DelegatingPreparedStatement.setObject(DelegatingPreparedStatement.java:188) at org.apache.tomcat.dbcp.dbcp2.DelegatingPreparedStatement.setObject(DelegatingPreparedStatement.java:188) at org.hibernate.spatial.dialect.AbstractJTSGeometryValueBinder.bind(AbstractJTSGeometryValueBinder.java:48) at org.hibernate.spatial.dialect.AbstractJTSGeometryValueBinder.bind(AbstractJTSGeometryValueBinder.java:39) at org.hibernate.type.AbstractStandardBasicType.nullSafeSet(AbstractStandardBasicType.java:286) at org.hibernate.type.AbstractStandardBasicType.nullSafeSet(AbstractStandardBasicType.java:281) at org.hibernate.type.AbstractSingleColumnStandardBasicType.nullSafeSet(AbstractSingleColumnStandardBasicType.java:56) at org.hibernate.persister.entity.AbstractEntityPersister.dehydrate(AbstractEntityPersister.java:2843) at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:3121) ... 66 more
The quote in italian could be translated in this way: It's not possible to identify the SQL type to be used for the instance of type «org.postgis.PGgeometry». Use «setObject()» specifying the type to be used for this value.
Could you help me to solve this issue? Do you need some other information?
Roberto
