Cannot insert Point into mysql database using Hibernate Spatial

Question

I am using mysql server from docker image mysql:8.0.16. It has MySQL InnoDB version 8.0.16.

I am trying to insert entity that has Point variable into the database using hibernate framework. I am always receiving the same error:

Cannot get geometry object from data you send to the GEOMETRY field

I am getting this error when I am trying to insert Booking type into database (definition of Booking is below). Bookings has 2 fields of type Point annotated by @Column with ColumnDefinition that is equal to geometry(POINT, 0).

I am using database-first approach. Hibernate is not creating any tables by itself.

I am using com.vividsolutions.jts.geom.Point to store my 2 Points.

I investigated that hibernate is trying to execute this sql:

insert into Bookings (CarId, DropPosition, EndDateTime, PickupPosition, StartDateTime, UserId) values (13, x'ACED000573720021636F6D2E7669766964736F6C7574696F6E732E6A74732E67656F6D2E506F696E7444077BAD161CBB2A0200014C000B636F6F7264696E617465737400304C636F6D2F7669766964736F6C7574696F6E732F6A74732F67656F6D2F436F6F7264696E61746553657175656E63653B78720024636F6D2E7669766964736F6C7574696F6E732E6A74732E67656F6D2E47656F6D65747279799EA46522854A3E020004490004535249444C0008656E76656C6F70657400264C636F6D2F7669766964736F6C7574696F6E732F6A74732F67656F6D2F456E76656C6F70653B4C0007666163746F727974002D4C636F6D2F7669766964736F6C7574696F6E732F6A74732F67656F6D2F47656F6D65747279466163746F72793B4C000875736572446174617400124C6A6176612F6C616E672F4F626A6563743B787000000000707372002B636F6D2E7669766964736F6C7574696F6E732E6A74732E67656F6D2E47656F6D65747279466163746F7279A158A0DFAE114CED020003490004535249444C0019636F6F7264696E61746553657175656E6365466163746F72797400374C636F6D2F7669766964736F6C7574696F6E732F6A74732F67656F6D2F436F6F7264696E61746553657175656E6365466163746F72793B4C000E707265636973696F6E4D6F64656C74002C4C636F6D2F7669766964736F6C7574696F6E732F6A74732F67656F6D2F507265636973696F6E4D6F64656C3B7870000000007372003F636F6D2E7669766964736F6C7574696F6E732E6A74732E67656F6D2E696D706C2E436F6F7264696E617465417272617953657175656E6365466163746F7279C71B60593090D15702000078707372002A636F6D2E7669766964736F6C7574696F6E732E6A74732E67656F6D2E507265636973696F6E4D6F64656C6BEE6404E9A25C3B0200024400057363616C654C00096D6F64656C547970657400314C636F6D2F7669766964736F6C7574696F6E732F6A74732F67656F6D2F507265636973696F6E4D6F64656C24547970653B787000000000000000007372002F636F6D2E7669766964736F6C7574696F6E732E6A74732E67656F6D2E507265636973696F6E4D6F64656C2454797065B3467532BF594D420200014C00046E616D657400124C6A6176612F6C616E672F537472696E673B7870740008464C4F4154494E477073720038636F6D2E7669766964736F6C7574696F6E732E6A74732E67656F6D2E696D706C2E436F6F7264696E617465417272617953657175656E6365F34BB588724E71F602000249000964696D656E73696F6E5B000B636F6F7264696E617465737400295B4C636F6D2F7669766964736F6C7574696F6E732F6A74732F67656F6D2F436F6F7264696E6174653B787000000003757200295B4C636F6D2E7669766964736F6C7574696F6E732E6A74732E67656F6D2E436F6F7264696E6174653BEDC4D448AAFA43B702000078700000000173720026636F6D2E7669766964736F6C7574696F6E732E6A74732E67656F6D2E436F6F7264696E6174655CBF2C235C7E583E02000344000178440001794400017A7870000000000000000000000000000000007FF8000000000000', '2019-07-08 00:46:57.809', x'ACED000573720021636F6D2E7669766964736F6C7574696F6E732E6A74732E67656F6D2E506F696E7444077BAD161CBB2A0200014C000B636F6F7264696E617465737400304C636F6D2F7669766964736F6C7574696F6E732F6A74732F67656F6D2F436F6F7264696E61746553657175656E63653B78720024636F6D2E7669766964736F6C7574696F6E732E6A74732E67656F6D2E47656F6D65747279799EA46522854A3E020004490004535249444C0008656E76656C6F70657400264C636F6D2F7669766964736F6C7574696F6E732F6A74732F67656F6D2F456E76656C6F70653B4C0007666163746F727974002D4C636F6D2F7669766964736F6C7574696F6E732F6A74732F67656F6D2F47656F6D65747279466163746F72793B4C000875736572446174617400124C6A6176612F6C616E672F4F626A6563743B787000000000707372002B636F6D2E7669766964736F6C7574696F6E732E6A74732E67656F6D2E47656F6D65747279466163746F7279A158A0DFAE114CED020003490004535249444C0019636F6F7264696E61746553657175656E6365466163746F72797400374C636F6D2F7669766964736F6C7574696F6E732F6A74732F67656F6D2F436F6F7264696E61746553657175656E6365466163746F72793B4C000E707265636973696F6E4D6F64656C74002C4C636F6D2F7669766964736F6C7574696F6E732F6A74732F67656F6D2F507265636973696F6E4D6F64656C3B7870000000007372003F636F6D2E7669766964736F6C7574696F6E732E6A74732E67656F6D2E696D706C2E436F6F7264696E617465417272617953657175656E6365466163746F7279C71B60593090D15702000078707372002A636F6D2E7669766964736F6C7574696F6E732E6A74732E67656F6D2E507265636973696F6E4D6F64656C6BEE6404E9A25C3B0200024400057363616C654C00096D6F64656C547970657400314C636F6D2F7669766964736F6C7574696F6E732F6A74732F67656F6D2F507265636973696F6E4D6F64656C24547970653B787000000000000000007372002F636F6D2E7669766964736F6C7574696F6E732E6A74732E67656F6D2E507265636973696F6E4D6F64656C2454797065B3467532BF594D420200014C00046E616D657400124C6A6176612F6C616E672F537472696E673B7870740008464C4F4154494E477073720038636F6D2E7669766964736F6C7574696F6E732E6A74732E67656F6D2E696D706C2E436F6F7264696E617465417272617953657175656E6365F34BB588724E71F602000249000964696D656E73696F6E5B000B636F6F7264696E617465737400295B4C636F6D2F7669766964736F6C7574696F6E732F6A74732F67656F6D2F436F6F7264696E6174653B787000000003757200295B4C636F6D2E7669766964736F6C7574696F6E732E6A74732E67656F6D2E436F6F7264696E6174653BEDC4D448AAFA43B702000078700000000173720026636F6D2E7669766964736F6C7574696F6E732E6A74732E67656F6D2E436F6F7264696E6174655CBF2C235C7E583E02000344000178440001794400017A7870000000000000000000000000000000007FF8000000000000', '2019-07-08 00:46:57.809', 1)]

I have a feeling that data being inserted into point is too large. I am not entirely sure if right things is being inserted into database.

My pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>someName</groupId>
    <artifactId>someName</artifactId>
    <version>0.1</version>

    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <!-- Define Spring version as a constant -->
        <spring.version>5.1.8.RELEASE</spring.version>
        <hibernate.version>5.4.3.Final</hibernate.version>
    </properties>

    <dependencies>
        <!-- https://mvnrepository.com/artifact/org.hibernate/hibernate-core -->
        <dependency>
            <groupId>org.hibernate</groupId>
            <artifactId>hibernate-core</artifactId>
            <version>${hibernate.version}</version>
        </dependency>

        <dependency>
            <groupId>org.hibernate</groupId>
            <artifactId>hibernate-spatial</artifactId>
            <version>${hibernate.version}</version>
        </dependency>

        <dependency>
            <groupId>org.hibernate</groupId>
            <artifactId>hibernate-entitymanager</artifactId>
            <version>${hibernate.version}</version>
        </dependency>

        <dependency>
            <groupId>org.slf4j</groupId>
            <artifactId>slf4j-log4j12</artifactId>
            <version>1.7.26</version>
        </dependency>

        <!-- https://mvnrepository.com/artifact/org.junit.jupiter/junit-jupiter-engine -->
        <dependency>
            <groupId>org.junit.jupiter</groupId>
            <artifactId>junit-jupiter-engine</artifactId>
            <version>5.5.0</version>
            <scope>test</scope>
        </dependency>


        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.16</version>
        </dependency>
        <!-- https://hibernate.org/validator/documentation/getting-started/ -->
        <dependency>
            <groupId>org.hibernate.validator</groupId>
            <artifactId>hibernate-validator</artifactId>
            <version>6.0.16.Final</version>
        </dependency>
        <dependency>
            <groupId>javax.el</groupId>
            <artifactId>javax.el-api</artifactId>
            <version>3.0.0</version>
        </dependency>
        <dependency>
            <groupId>org.glassfish</groupId>
            <artifactId>javax.el</artifactId>
            <version>3.0.0</version>
        </dependency>

        <!-- https://mvnrepository.com/artifact/org.mockito/mockito-all -->
        <dependency>
            <groupId>org.mockito</groupId>
            <artifactId>mockito-all</artifactId>
            <version>1.10.19</version>
            <scope>test</scope>
        </dependency>

        <dependency>
            <groupId>com.vividsolutions</groupId>
            <artifactId>jts</artifactId>
            <version>1.13</version>
        </dependency>

    </dependencies>    
</project>

my hibernate config file:

<?xml version='1.0' encoding='utf-8'?>
<!--
  ~ Hibernate, Relational Persistence for Idiomatic Java
  ~
  ~ License: GNU Lesser General Public License (LGPL), version 2.1 or later.
  ~ See the lgpl.txt file in the root directory or <http://www.gnu.org/licenses/lgpl-2.1.html>.
  -->
<!DOCTYPE hibernate-configuration PUBLIC
        "-//Hibernate/Hibernate Configuration DTD 3.0//EN"
        "http://www.hibernate.org/dtd/hibernate-configuration-3.0.dtd">

<hibernate-configuration>

    <session-factory>

        <!-- Database connection settings -->
        <property name="connection.driver_class">com.mysql.jdbc.Driver</property>
        <property name="connection.url">jdbc:mysql://127.0.0.1:11000/db</property>
        <property name="connection.username">user</property>
        <property name="connection.password">!Qazxsw2</property>


        <!-- Disable the second-level cache  -->
        <property name="cache.provider_class">org.hibernate.cache.internal.NoCacheProvider</property>

        <property name="hibernate.dialect">org.hibernate.spatial.dialect.mysql.MySQLSpatialDialect</property>

        <!-- Echo all executed SQL to stdout -->
        <property name="show_sql">true</property>

        <!-- Drop and re-create the database schema on startup -->
        <property name="hbm2ddl.auto">validate</property>

        <!-- Names the annotated entity class -->


    </session-factory>

</hibernate-configuration>

create script for Bookings:

CREATE TABLE Bookings (
-- removed other fields for clarity
    PickupPosition POINT NOT NULL,
    DropPosition POINT,

)

Booking.java

@Entity
@Table( name = "Bookings" )
public class Booking {
    @Column(name = "PickupPosition", nullable = false, columnDefinition = "geometry(POINT, 0)")
    @NotNull
    private Point pickupPosition;


    @Column(name = "DropPosition", columnDefinition = "geometry(POINT, 0)")
    private Point dropPosition;

// removed other fields/methods/getters/setters for clarity
}

What I tried:

• Setting different MySQL spatial dialects
  • did not help
• Removing columnDefinition from Column annotation
  • I receive following error Schema-validation: wrong column type encountered in column [DropPosition] in table [Bookings]; found [geometry (Types#BINARY)], but expecting [tinyblob (Types#VARBINARY)]
• Changing type from Point into Geometry in MySQL script
  • did not help - same error
• Changing type from Point into Geometry in Java code
  • did not help - same error
• Tried to change ColumnDefinition into Point
  • I received this error during construction of sessionFactory Schema-validation: wrong column type encountered in column [DropPosition] in table [Bookings]; found [geometry (Types#BINARY)], but expecting [point (Types#VARBINARY)]

--

I read massive number of stack overflow questions about that but I did not find any that would have exact same problem as I have.

I would just like to insert Point into database. That's all.

Answer 1

It turns out that solution to my problem was to use org.locationtech.jts.geom.Point; instead of com.vividsolutions.jts.geom.Point.