Tiina Tiina - 5 months ago 75
Java Question

How to map mysql point type in java using mybatis

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

Answer

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>
Comments