How to map mysql point type in java using mybatis


Problem :

How to map mysql point type in java using mybatis? It is java.lang.Object now. This is my table:

CREATE TABLE `test` (
  `id` int(11) NOT NULL,
  `location` point DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

And this is the xml that the generator gives:

  <resultMap id="BaseResultMap" type="package.model.Test">
    <id column="id" jdbcType="INTEGER" property="id" />
    <result column="location" jdbcType="OTHER" property="location" />
  </resultMap>
  <insert id="insert" parameterType="package.model.Test">
    insert into test (id, location)
    values (#{id,jdbcType=INTEGER}, #{location,jdbcType=OTHER})
  </insert>

I have tried:

public void testPointType() {
    GeometryFactory geometryFactory = new GeometryFactory();
    com.vividsolutions.jts.geom.Point point = geometryFactory.createPoint(new Coordinate(1, 1));
    package.model.Test record = new package.model.Test();
    record.setLocation(point.toText());
    testMapper.insertSelective(record);
}

But get: com.mysql.jdbc.MysqlDataTruncation: Data truncation: Cannot get geometry object from data you send to the GEOMETRY field



Solution :

Not sure what fields is in model Test and not sure if it works for you, but just try it;)

<insert id="insert" parameterType="package.model.Test">
    insert into test (id, location)
    values (#{id,jdbcType=INTEGER}, Point( #{location.x}, #{location.x} ))
</insert>

And I think you'd better define you model Test like:

public class Test {
    private String id;
    private String locationX;
    private String locationY;

    ... ...
    getter and setter
    ... ...

}

And then you can do it like this:

<insert id="insert" parameterType="package.model.Test">
    insert into test (id, location)
    values (#{id,jdbcType=INTEGER}, Point( #{locationX}, #{locationY} ))
</insert>

For this kind model Test, you can do select like:

<select id="insert" resultType="package.model.Test">
    select id, x(location) as locationX, y(location) as locationY from test
</select>

Mysql Tutorials

Mysql Howto..

how to subtract two column in mysql where clause?

How to automate data gathering and not freeze in 10%

how to bulk add in mysql using php

mysql c++ connector, how to keep the connection alive and reconnect if connection is broked?

How to store a list of data (like an array) in mysql cell? (but have it still be searchable)

MYSQL: two joins on same table become double grouped count. How to fix it?

How should I arrange multiple tables in MySQL

How to use delete cascade on MySQL MyISAM storage engine?

How to match string with escaped quote character in MySQL?

how to 'split' words entered in a text field on a form and query each word against the database

How to pull more than 1mb data from mysql column using php

How to properly insert time values in mysql using php framework laravel 5.1?

How do I get multiple COUNT with multiple JOINS and multiple conditions?

Idea how to implement server-push content without php looping

how to import (.dmp) database format in mysql or phpmyadmin?

How to optimize a MySQL table? [duplicate]

How to find Mysql thread

NodeJS MySQL - How to know connection is release or not

how to display multiple rows from mysql using php oop?

MySQL: How to perform case-insensitive query

How to sort MySQL ResultSet by Custom Field?

how to protect access to my company website to only employees [closed]

How to get ID on the fly with Ajax

How do I tell the MySQL Optimizer to use the index on a derived table?

how to store parameters in asp.net for use with sql?

How to go about implementing prepared statements with parameters, is it even possible with this query?

How can i generate a 6 digit number in php?

How do protect yourself against SQL injection when using prepared statements/store procedures in PHP?

How to deal with huge database in mysql?

How storing database info outside webroot of php applications will improve security