扩展mybatis和通用mapper，支持mysql的geometry类型字段

因项目中需要用到地理位置信息的存储、查询、计算等，经过研究决定使用mysql（5.7版本）数据库的geometry类型字段来保存地理位置坐标，使用虚拟列（virtual generated column）来保存geohash值，便于查询。
需要了解geometry如何使用及优势可参看：

mysql geometry扩展在地理位置计算中的效率优势

本文主要讲解扩展mybatis和通用mapper，使其支持geometry类型字段的新增、修改、查询

首先创建一张表，作为本文的案例

create table `t_user` (
  `id` varchar(45) not null,
  `name` varchar(10) not null comment '姓名',
  `gis` geometry not null comment '空间位置信息',
  `geohash` varchar(20)  generated always as (st_geohash(`gis`,8)) virtual not null comment 'geo哈希',
  primary key (`id`),
  unique key `id` (`id`),
  spatial key `idx_gis` (`gis`),
  key `idx_geohash` (`geohash`)
) engine=innodb default charset=utf8mb4 comment='用户';

创建对应的实体类

@table(name = "t_user")
public class user {
    private string id;
    private string name;
    @column
    private geopoint gis;
    @virtualgenerated
    private string geohash;
}

其中geopoint类型是我们自定义的类型，用来对应mysql的geometry类型

public class geopoint {
    public geopoint(bigdecimal lng, bigdecimal lat) {
        this.lng = lng;
        this.lat = lat;
    }
    /* 经度 */
    private bigdecimal lng;
    /* 纬度 */
    private bigdecimal lat;
}

@virtualgenerated注解是我们自定义的注解，用来标识虚拟列字段，使insert、update时能够忽略该字段

使tk通用mapper的insert支持geometry类型

tk通用mapper默认生成的insert语句xml是这样

<insert>
    insert into t_user 
    <trim prefix="(" suffix=")" suffixoverrides=",">
        <if test="id != null">id,</if>
        <if test="name != null">name,</if>
        <if test="gis != null">gis,</if>
    </trim>
    <trim prefix="values(" suffix=")" suffixoverrides=",">
        <if test="id != null">#{id},</if>
        <if test="name != null">#{name},</if>
        <if test="gis != null">#{gis},</if>
    </trim>
</insert>

而我们希望生成的insert语句xml是这样

<insert>
    insert into t_user 
    <trim prefix="(" suffix=")" suffixoverrides=",">
        <if test="id != null">id,</if>
        <if test="name != null">name,</if>
        <if test="gis != null">gis,</if>
    </trim>
    <trim prefix="values(" suffix=")" suffixoverrides=",">
        <if test="id != null">#{id},</if>
        <if test="name != null">#{name},</if>
        <if test="gis != null">geomfromtext('point(${gis.lng} ${gis.lat})'),</if>
    </trim>
</insert>

于是...开始我们的修改,查看通用mapper的源码得知，通用insert主要是通过baseinsertmapper和baseinsertprovider这两个类实现的，所以我们仿造着创建geobaseinsertmapper.java 和 geobaseinsertprovider.java，其中geobaseinsertprovider.java直接复制baseinsertprovider来修改即可
geobaseinsertmapper.java如下：

@registermapper
public interface geobaseinsertmapper<t> {
    @insertprovider(type = geobaseinsertprovider.class, method = "dynamicsql")
    int insert(t record);

    @insertprovider(type = geobaseinsertprovider.class, method = "dynamicsql")
    int insertselective(t record);
}

最主要的是geobaseinsertprovider.java
将

public class geobaseinsertprovider extends mappertemplate {

    public geobaseinsertprovider(class<?> mapperclass, mapperhelper mapperhelper) {
        super(mapperclass, mapperhelper);
    }

    public string insert(mappedstatement ms) {
        class<?> entityclass = getentityclass(ms);
        stringbuilder sql = new stringbuilder();
        //获取全部列
        set<entitycolumn> columnlist = entityhelper.getcolumns(entityclass);
        entitycolumn logicdeletecolumn = sqlhelper.getlogicdeletecolumn(entityclass);
        processkey(sql, entityclass, ms, columnlist);
        sql.append(sqlhelper.insertintotable(entityclass, tablename(entityclass)));
        sql.append("<trim prefix=\"(\" suffix=\")\" suffixoverrides=\",\">");
        //当某个列有主键策略时，不需要考虑他的属性是否为空，因为如果为空，一定会根据主键策略给他生成一个值
        for (entitycolumn column : columnlist) {
            if (!column.isinsertable()) {
                continue;
            }
            //忽略虚拟列
            if (column.getentityfield().isannotationpresent(virtualgenerated.class)) {
                continue;
            }
            sql.append(column.getcolumn() + ",");
        }
        sql.append("</trim>");
        sql.append("<trim prefix=\"values(\" suffix=\")\" suffixoverrides=\",\">");
        for (entitycolumn column : columnlist) {
            if (!column.isinsertable()) {
                continue;
            }
            //忽略虚拟列
            if (column.getentityfield().isannotationpresent(virtualgenerated.class)) {
                continue;
            }
            if (logicdeletecolumn != null && logicdeletecolumn == column) {
                sql.append(sqlhelper.getlogicdeletedvalue(column, false)).append(",");
                continue;
            }

            //优先使用传入的属性值,当原属性property!=null时，用原属性
            //自增的情况下,如果默认有值,就会备份到property_cache中,所以这里需要先判断备份的值是否存在
            if (column.isidentity()) {
                sql.append(sqlhelper.getifcachenotnull(column, column.getcolumnholder(null, "_cache", ",")));
            } else {
                //判断字段是geopoint类型时，调用getgeocolumnholder方法来生成
                if (column.getjavatype() == geopoint.class) {
                    //<if test="property != null">geomfromtext('point(108.9498710632 34.2588125935)'),</if>
                    sql.append(sqlhelper.getifnotnull(column, getgeocolumnholder(column), isnotempty()));
                } else {
                    //其他情况值仍然存在原property中
                    sql.append(sqlhelper.getifnotnull(column, column.getcolumnholder(null, null, ","), isnotempty()));
                }

            }
            //当属性为null时，如果存在主键策略，会自动获取值，如果不存在，则使用null
            if (column.isidentity()) {
                sql.append(sqlhelper.getifcacheisnull(column, column.getcolumnholder() + ","));
            } else {
                //判断字段是geopoint类型时，调用getgeocolumnholder方法来生成
                if (column.getjavatype() == geopoint.class) {
                    //<if test="property == null">geomfromtext('point(108.9498710632 34.2588125935)'),</if>
                    sql.append(sqlhelper.getifisnull(column, getgeocolumnholder(column), isnotempty()));
                } else {
                    //当null的时候，如果不指定jdbctype，oracle可能会报异常，指定varchar不影响其他
                    sql.append(sqlhelper.getifisnull(column, column.getcolumnholder(null, null, ","), isnotempty()));
                }
            }
        }
        sql.append("</trim>");
        return sql.tostring();
    }

    /*
     * insert geo字段占位符
     */
    private string getgeocolumnholder(entitycolumn column){
        return string.format("geomfromtext('point(${%s.lng} ${%s.lat})'),",column.getproperty(),column.getproperty());
    }

    //忽略以下部分代码

}

让你的mapper接口继承geobaseinsertmapper就能使insert方法支持geometry类型了，同时能够忽略虚拟列。

@repository
public interface usermapper extends geobaseinsertmapper<user>{
}

如果你理解了通用insert的修改，update的修改也同样如此，相信难不倒你，这里就不再贴代码了。

使mybatis查询支持将geometry类型字段映射到geopoint类型

mybatis通过定义typehandler将数据类型映射为java类型，mybatis内置了多种常见的typehandler，但没有支持geometry，好在mybatis提供了足够的扩展性，我们可以自定义typehandler，这里还需要在pom.xml引入jts库来解析

<dependency>
    <groupid>com.vividsolutions</groupid>
    <artifactid>jts</artifactid>
    <version>${jts.version}</version>
</dependency>

接下来是自定义的mysqlgeopointtypehandler

/*
 * mybatis查询结果集中 mysql的geometry类型映射到geopoint对象
 */
@mappedtypes(value = {geopoint.class})
public class mysqlgeopointtypehandler extends basetypehandler<geopoint> {

    private wkbreader _wkbreader;

    public mysqlgeopointtypehandler(int srid) {
        geometryfactory _geometryfactory = new geometryfactory(new precisionmodel(), srid);
        _wkbreader = new wkbreader(_geometryfactory);
    }

    @override
    public void setnonnullparameter(preparedstatement ps, int i, geopoint parameter, jdbctype jdbctype) {
        //因为geopoint对象里包含经度和纬度两个值，无法直接适配到一个参数，所以也不会使用到这个方法
    }

    @override
    public geopoint getnullableresult(resultset rs, string columnname) throws sqlexception {
        return frommysqlwkb(rs.getbytes(columnname));
    }

    @override
    public geopoint getnullableresult(resultset rs, int columnindex) throws sqlexception {
        return frommysqlwkb(rs.getbytes(columnindex));
    }

    @override
    public geopoint getnullableresult(callablestatement cs, int columnindex) throws sqlexception {
        return frommysqlwkb(cs.getbytes(columnindex));
    }

    /*
     * bytes转geopoint对象
     */
    private geopoint frommysqlwkb(byte[] bytes) {
        if (bytes == null) {
            return null;
        }
        try {
            byte[] geombytes = bytebuffer.allocate(bytes.length - 4).order(byteorder.little_endian)
                    .put(bytes, 4, bytes.length - 4).array();
            geometry geometry = _wkbreader.read(geombytes);
            point point = (point) geometry;
            return new geopoint(new bigdecimal(string.valueof(point.getx())), new bigdecimal(string.valueof(point.gety())));
        } catch (exception e) {
        }
        return null;
    }
}

然后我们需要将mysqlgeopointtypehandler添加到mybatis配置中，这样mybatis在遇到geopoint时就知道怎么映射了。
这里演示用java代码来配置mybatis，也可以在mybatis.xml文件中配置

@configuration
@mapperscan(basepackages = {"com.carson.**.mapper"}, sqlsessiontemplateref = "sqlsessiontemplate")
public class mybatisconfig {

    @bean
    public sqlsessionfactory sqlsessionfactory(datasource datasource) {
        sqlsessionfactorybean bean = new sqlsessionfactorybean();
        bean.setdatasource(datasource);
        bean.setvfs(springbootvfs.class);
        //添加xml目录
        resourcepatternresolver resolver = new pathmatchingresourcepatternresolver();
        try {
            bean.setmapperlocations(resolver.getresources("classpath:mybatis/**/*mapper.xml"));
            bean.settypealiasespackage("com.carson.pojo");
            //添加mysqlgeopointtypehandler
            bean.settypehandlers(new typehandler[]{new mysqlgeopointtypehandler()});
            bean.getobject().getconfiguration().setmapunderscoretocamelcase(true);
            return bean.getobject();
        } catch (exception e) {
            e.printstacktrace();
            throw new runtimeexception(e);
        }
    }
}

完成这些以后查询的结果集里包含geometry类型的字段，就能映射到geopoint了，从而可以获取经纬度

源码在哪里？ talk is cheap,show me the code！

如果你懒得看以上长篇大论，只想要开箱即用的代码，就在这里了，有帮助的话记得给个star哦！