Anyline+PostgreSQL使用理解之二

第一篇：springboot+postgresql集成anyline试水

总结几个目前遇到的比较简单的使用场景，以后可能会继续在此更新。下文anylineService皆为org.anyline.service.AnylineService。
仅供参考，如果自己遇到很怪的问题还是推荐进技术支持群询问作者。

查询

基本列表查询

String[] conditions;
try{
    conditions = dto.toConditions();
}catch(Exception e){
    e.printStackTrace();
    conditions = new String[0];
}
DataSet ds = anylineService.querys(TABLE_NAME + "(id,row,col,start_time,ST_asgeojson(geom) as geom,detail)", conditions);

查询条件构建

以常见的整型、日期、字符串查询为例。用了lombok省去手写get和set。

import com.fasterxml.jackson.annotation.JsonFormat;
import lombok.Data;
import org.springframework.format.annotation.DateTimeFormat;

import java.util.Date;

@Data
public class Dto {
    private Integer row;

    private Integer col;

    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")
    @DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")
    private Date startTime; // 精确查询

    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")
    @DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")
    private Date start; // start_time >= start

    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")
    @DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")
    private Date end; // start_time < end

    private String detail;

    private String geom; // json

    public String[] toConditions() throws Exception{
        String[] conditions = new String[7];
        if(this.row != null){
            conditions[0] = "row:" + this.row; // 精确匹配 row=？
        }
        if(this.col != null){
            conditions[1] = "col:" + this.col;
        }
        if(this.startTime != null){
            conditions[2] = "start_time:" + DateUtils.parseDateToStr("yyyy-MM-dd HH:mm:ss",this.startTime ); // 精确匹配 start_time=？
        }
        if(this.start != null){
            String startStr = DateUtils.parseDateToStr("yyyy-MM-dd HH:mm:ss",this.start ); // 转换成日期字符串
            conditions[3] = "start_time:>" + startStr + "|start_time:" + startStr; // 日期大于等于查询条件 start_time >= ?
        }
        if(this.end != null){
            conditions[4] = "start_time:<" + DateUtils.parseDateToStr("yyyy-MM-dd HH:mm:ss",this.end ); // 日期小于查询条件 start_time < ?
        }
        if(StringUtils.isNotEmpty(this.detail)){
            conditions[5] = "detail:%" + this.detail + "%"; // 模糊查询 detail like '%?%'
        }
        // geom
        // ST_Within ( geom, ST_GeomFromGeoJSON ( #{geom,javaType=String,jdbcType=VARCHAR}::text) ) // 空间查询，相当于mapper里这么写
        if(StringUtils.isNotEmpty(this.geom)){
            conditions[6] = String.format("ST_Within( geom, ST_GeomFromGeoJSON( '%s' ))", this.geom);
        }

        return conditions;
    };
}

联表查询

DataSet ds = anylineService.querys(TableBuilder.init(TABLE_NAME_1 + "(I.id,I.row,I.col,I.start_time,ST_asgeojson(I.geom) as geom,I.detail," +
                        "S.thumb,S.download_num) AS I")
                        .left(TABLE_NAME_2 + " AS S","S.id = I.id")
                        .build(),
                conditions);

带别名的查询条件构建

public class Dto {

    ...

    public String[] toConditions(String alias) throws Exception{
        String prefix = "";
        if(StringUtils.isNotEmpty(alias)){
            prefix = alias + ".";
        }
        String[] conditions = new String[7];
        if(this.row != null){
            conditions[0] = prefix + "row:" + this.row;
        }
        if(this.col != null){
            conditions[1] = prefix + "col:" + this.col;
        }
        if(this.startTime != null){
            conditions[2] = prefix + "start_time:" + DateUtils.parseDateToStr("yyyy-MM-dd HH:mm:ss", this.startTime );
        }
        if(this.start != null){
            String startStr = DateUtils.parseDateToStr("yyyy-MM-dd HH:mm:ss", this.start );
            conditions[3] = prefix + "start_time:>" + startStr + "|" + prefix + "start_time:" + startStr;
        }
        if(this.end != null){
            conditions[4] = prefix + "start_time:<" + DateUtils.parseDateToStr("yyyy-MM-dd HH:mm:ss",this.end );
        }
        if(StringUtils.isNotEmpty(this.detail)){
            conditions[5] = prefix + "detail:%" + this.detail + "%";
        }
        // geom
        if(StringUtils.isNotEmpty(this.geom)){
            conditions[6] = String.format("ST_Within( %sgeom, ST_GeomFromGeoJSON( '%s' ))", prefix, this.geom);
        }

        return conditions;
    };
}

查询结果拎出来做处理

比如某个字段是文件路径需要处理成返回文件。

        List<DataRow> data = ds.getRows();
        for(int i=0; i<data.size(); i++){
            DataRow row = data.get(i);
            Object value = row.get("xxx");
            if(value != null){
                row.set("xxx", (...处理后的结果));
            }
        }

PostgreSQL空间查询特殊处理

空间数据查询结果以geojson格式返回

字段加ST_asgeojson函数。

DataSet ds = anylineService.querys(TABLE_NAME + "(ST_asgeojson(geom) as geom)", conditions);

空间查询condition条件构建

如查询范围在输入的polygon之内的数据

// ST_Within ( geom, ST_GeomFromGeoJSON ( #{geom,javaType=String,jdbcType=VARCHAR}::text) ) // 空间查询，相当于mapper里这么写
conditions[0] = String.format("ST_Within( geom, ST_GeomFromGeoJSON( '%s' ))", this.geom);

坐标系预转换

进行空间查询时往往遇到数据库存的数据与传参坐标系不一致，需要在查询前做预处理，这里以转换为4326为例：

private void preUpdateGeom(){
    // "update table_name SET geom=st_setsrid(geom, 4326)"; // mapper里这样写
    anylineService.execute(String.format("update %s SET %s=st_setsrid(%s, 4326)", TABLE_NAME, "geom", "geom"));
}

放权给前端的通用查询

列表查询

这里响应封装我用的是若依的后端框架。

    @ApiOperation("表数据查询")
    @GetMapping(value="/list", name = "表数据查询")
    public AjaxResult list(String src, String ... conditions)
    {
        return success(anylineService.querys(src, conditions));
    }

新增或修改

    @ApiOperation("表数据新增或修改")
    @PostMapping(value = "/save", name = "表数据新增或修改")
    public AjaxResult save(String tableName, DataRow data) {
        return toAjax(anylineService.save(tableName, data));
    }

数据量统计

    @ApiOperation("表数据量统计")
    @GetMapping(value = "/count", name = "表数据量统计")
    public AjaxResult totalCount(String tableName) {
        return success(anylineService.count(tableName));
    }