扩展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的修改，update的修改也同样如此，相信难不倒你，这里就不再贴代码了。 mybatis通过定义typehandler将数据类型映射为java类型，mybatis内置了多种常见的typehandler，但没有支持geometry，好在mybatis提供了足够的扩展性，我们可以自定义typehandler，这里还需要在pom.xml引入jts库来解析 接下来是自定义的mysqlgeopointtypehandler 然后我们需要将mysqlgeopointtypehandler添加到mybatis配置中，这样mybatis在遇到geopoint时就知道怎么映射了。 完成这些以后查询的结果集里包含geometry类型的字段，就能映射到geopoint了，从而可以获取经纬度 如果你懒得看以上长篇大论，只想要开箱即用的代码，就在这里了，有帮助的话记得给个star哦！@repository
public interface usermapper extends geobaseinsertmapper<user>{
}
使mybatis查询支持将geometry类型字段映射到geopoint类型
<dependency>
<groupid>com.vividsolutions</groupid>
<artifactid>jts</artifactid>
<version>${jts.version}</version>
</dependency>
/*
* 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;
}
}
这里演示用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);
}
}
}
源码在哪里？ talk is cheap,show me the code！