Geotools-PG空间库（Crud，属性查询，空间查询）

建立连接

经过测试，这套连接逻辑除了支持纯PG以外，也支持人大金仓，凡是套壳PG的都可以尝试一下。我这里的测试环境是Geosence创建的pg SDE，数据库选用的是人大金仓。

/**
  * 获取数据库连接资源
  *
  * @param connectConfig
  * @return
  * {@link PostgisNGDataStoreFactory} PostgisNGDataStoreFactory还有跟多的定制化参数可以进去看看
  * @throws Exception
  */
 public static DataStore ConnectDatabase(GISConnectConfig connectConfig) throws Exception {
     if (pgDatastore != null) {
         return pgDatastore;
     }
     //数据库连接参数配置
     Map<String, Object> params = new HashMap<String, Object>();
     // 数据库类型
     params.put(PostgisNGDataStoreFactory.DBTYPE.key, connectConfig.getType());
     params.put(PostgisNGDataStoreFactory.HOST.key, connectConfig.getHost());
     params.put(PostgisNGDataStoreFactory.PORT.key, connectConfig.getPort());
     // 数据库名
     params.put(PostgisNGDataStoreFactory.DATABASE.key, connectConfig.getDataBase());
     //用户名和密码
     params.put(PostgisNGDataStoreFactory.USER.key, connectConfig.getUser());
     params.put(PostgisNGDataStoreFactory.PASSWD.key, connectConfig.getPassword());
     // 模式名称
     params.put(PostgisNGDataStoreFactory.SCHEMA.key, "sde");
     // 最大连接
     params.put( PostgisNGDataStoreFactory.MAXCONN.key, 25);
     // 最小连接
     params.put(PostgisNGDataStoreFactory.MINCONN.key, 10);
     // 超时时间
     params.put( PostgisNGDataStoreFactory.MAXWAIT.key, 10);
     try {
         pgDatastore = DataStoreFinder.getDataStore(params);
         return pgDatastore;
     } catch (IOException e) {
         LOG.error("获取数据源信息出错");
     }
     return null;
 }

查询

• 查询所有的表格

/**
  * 查询所有的表格
  * @return
  * @throws IOException
  */
 public List<String> getAllTables() throws IOException {
     String[] typeNames = this.dataStore.getTypeNames();
     List<String> tables = Arrays.stream(typeNames).collect(Collectors.toList());
     return tables;
 }

属性查询&&空间查询通用

 /**
    * 查询要素
    * @param layerName
    * @param filter
    * @return
    * @throws IOException
    */
   public  SimpleFeatureCollection queryFeatures(String layerName, Filter filter) throws IOException {
       SimpleFeatureCollection features = null;
       try {
           SimpleFeatureSource featureSource = dataStore.getFeatureSource(layerName);
           features = featureSource.getFeatures(filter);
           return features;
       } catch (Exception e) {
           e.printStackTrace();
       }
       return features;
   }

• 属性筛选查询
  用数据库查：
  

SELECT *FROM mzxm_lx WHERE xmbh = '3308812023104'  AND zzdybh = '3308812023104003' AND zzlx = '10'

用代码查：

 SimpleFeatureCollection simpleFeatureCollection = pgTemplate.queryFeatures("mzxm_lx", CQL.toFilter("xmbh = '3308812023104'  AND zzdybh = '3308812023104003' AND zzlx = '10'"));

• 空间筛选

Geometry geometry = new WKTReader().read("Polygon ((119.13571152004580256 29.96675730309299368, 119.14239751148502933 29.62242874397260195, 119.49341206204465493 29.84975245290645063, 119.23265839591465465 30.0670471746814556, 119.13571152004580256 29.96675730309299368))");
// 直接写SQL
Filter filter = CQL.toFilter("INTERSECTS(shape," + geometry .toString() + ")");
// 或者使用FilterFactory 
Within within = ff.within(ff.property("shape"), ff.literal(geometry));
SimpleFeatureCollection simpleFeatureCollection = pgTemplate.queryFeatures("mzxm_lx", within);

如果不知道使用的什么关键字就比如相交INTERSECTS，可以点进对应的这个空间关系里去看这个Name，和这个保持一致。

总结：这里就在于怎么去写这个Filter，可以直接使用SQL语法。也可以自己去构造,需要借助这两个类

private static FilterFactory2 spatialFilterFc = CommonFactoryFinder.getFilterFactory2(GeoTools.getDefaultHints());
private static FilterFactory propertyFilterFc = CommonFactoryFinder.getFilterFactory(null);

添加要素

/**
 * 
 * @param type 
 * @param features 需要追加的要素
 * @throws IOException
 */
public  void appendFeatures(SimpleFeatureType type, List<SimpleFeature> features) throws IOException {
    ListFeatureCollection featureCollection = new ListFeatureCollection(type, features);
    String typeName = type.getTypeName();
    FeatureStore featureStore = (FeatureStore) dataStore.getFeatureSource(typeName);
    try {
        featureStore.addFeatures(featureCollection);
    } catch (IOException e) {
        e.printStackTrace();
    }
    Transaction transaction = new DefaultTransaction("appendData");
    featureStore.setTransaction(transaction);
    transaction.commit();
}

测试代码：

Geometry geometry = new WKTReader().read("Polygon ((118.41044123299997182 29.89092741100000694, 118.42024576499994737 29.83296547499998042, 118.30907619399994246 29.75101510400003235, 118.19200671200002262 29.74673207400002184, 118.41044123299997182 29.89092741100000694))");
    SimpleFeature build = CustomFeatureBuilder.build(new HashMap<String, Object>() {{
        put("xmbh", "ceshiceshi");
        put("zxmmc", "测试一把");
        put("shape", geometry);
    }}, "mzxm_lx" , geometry);
    SimpleFeatureType simpleFeatureType = dataStore.getSchema("mzxm_lx");
    pgTemplate.appendFeatures(simpleFeatureType, Arrays.asList(build));

构建要素的代码如下：

/**
   *  构建一个Feature
   * @param fieldsMap
   * @param typeName
   * @return
   */
  public static SimpleFeature build(Map<String, Object> fieldsMap, String typeName) {
      SimpleFeatureTypeBuilder simpleFeatureTypeBuilder = new SimpleFeatureTypeBuilder();
      List<Object> values = new ArrayList<>();
      fieldsMap.forEach((key, val) -> {
          simpleFeatureTypeBuilder.add(key, val.getClass());
          values.add(val);
      });
      simpleFeatureTypeBuilder.setName(typeName);
      SimpleFeatureType simpleFeatureType = simpleFeatureTypeBuilder.buildFeatureType();
      SimpleFeatureBuilder builder = new SimpleFeatureBuilder(simpleFeatureType);
      builder.addAll(values);
      SimpleFeature feature = builder.buildFeature(null);
      return feature;
  }

图形也能正常展示：

/**
  * 通过FeatureWriter 追加要素
  * @param type
  * @param features
  * @throws IOException
  */
 public  void appendFeatureByFeatureWriter(SimpleFeatureType type, List<SimpleFeature> features) throws IOException {
     String typeName = type.getTypeName();
     FeatureWriter<SimpleFeatureType, SimpleFeature> featureWriter = dataStore.getFeatureWriterAppend(typeName, new DefaultTransaction("appendData"));
     for (SimpleFeature feature : features) {
         SimpleFeature remoteNext = featureWriter.next();
         remoteNext.setAttributes(feature.getAttributes());
         remoteNext.setDefaultGeometry(feature.getDefaultGeometry());
         featureWriter.write();
     }
    featureWriter.close();
 }

使用FeatureWriter这个时候要注意啦，你插入的时候必须每个字段都设置值，追进源码里面发现它的SQL是写了所有字段的
源码路径：JDBCDataStore#insertNonPS

所以下面这种方式是不会成功的，要成功的话必须设置所有的字段对应上，我懒得弄了原理就是上面源码那样的：

// 错误示范
Geometry geometry = new WKTReader().read("Polygon ((118.41044123299997182 29.89092741100000694, 118.42024576499994737 29.83296547499998042, 118.30907619399994246 29.75101510400003235, 118.19200671200002262 29.74673207400002184, 118.41044123299997182 29.89092741100000694))");
SimpleFeature build = CustomFeatureBuilder.build(new HashMap<String, Object>() {{
    put("xmbh", "writer");
    put("zxmmc", "demo");
    put("zzdybh", "fdsa");
    put("shape", geometry);
}}, "mzxm_lx" );
SimpleFeatureType simpleFeatureType = dataStore.getSchema("mzxm_lx");
pgTemplate.appendFeatureByFeatureWriter(simpleFeatureType, Arrays.asList(build));

更新

• 更新属性

/**
   * 更新属性
   * @param type
   * @param fieldsMap
   * @param filter
   * @throws IOException
   */
  public  void updateFeatures(SimpleFeatureType type, Map<String, Object> fieldsMap, Filter filter) throws IOException {
      String typeName = type.getTypeName();
      List<Name> names =new ArrayList<>();
      FeatureStore featureStore = (FeatureStore) dataStore.getFeatureSource(typeName);
      Set<String> keys = fieldsMap.keySet();
      for (String field : keys) {
          Name name = new NameImpl(field);
          names.add(name);
      }
      featureStore.modifyFeatures(names.toArray(new NameImpl[names.size()]), fieldsMap.values().toArray(), filter);
  }

测试代码：

HashMap<String, Object> fieldsMap = new HashMap<String, Object>() {{
    put("xmbh", "testupdate");
    put("zxmmc", "update");
    put("zzdybh", "3308812023104003");
}};
SimpleFeatureType simpleFeatureType = dataStore.getSchema("mzxm_lx");
pgTemplate.updateFeatures(simpleFeatureType, fieldsMap, CQL.toFilter(" xmbh = 'ceshiceshi'"));

如果你需要更新几何,只需要设置几何字段即可：

HashMap<String, Object> fieldsMap = new HashMap<String, Object>() {{
     put("xmbh", "ces");
     put("zxmmc", "update");
     put("zzdybh", "3308812023104003");
     put("shape", geometry);
 }};

我们还可以这样写

/**
 * 覆盖更新
 * @param type
 * @param fieldsMap
 * @param filter
 * @throws IOException
 */
public  void updateFeatureFeatureReader(SimpleFeatureType type, Map<String, Object> fieldsMap, Filter filter) throws IOException {
    String typeName = type.getTypeName();
    FeatureStore featureStore = (FeatureStore) dataStore.getFeatureSource(typeName);
    SimpleFeature simpleFeature = CustomFeatureBuilder.build(fieldsMap, typeName);
    // 设置一个 FeatureReader
    FeatureReader<SimpleFeatureType, SimpleFeature> featureReader = new CollectionFeatureReader(simpleFeature);
    featureStore.setFeatures(featureReader);
    featureReader.close();
}

这里还需要注意一点，featureReaders 是覆盖更新的逻辑,所以使用的时候要谨慎一点

下面有这么多实现类，具体怎么组合使用就看你的想象力了：

删除要素

/**
 * 删除数据
 *
 * @param layerName 图层名称
 * @param filter 过滤器
 */
public  boolean deleteData(String layerName, Filter filter) {
    try {
        SimpleFeatureSource featureSource = dataStore.getFeatureSource(layerName);
        FeatureStore featureStore = (FeatureStore) featureSource;
        featureStore.removeFeatures(filter);
        Transaction transaction = new DefaultTransaction("delete");
        featureStore.setTransaction(transaction);
        transaction.commit();
    } catch (Exception e) {
        e.printStackTrace();
        return false;
    }
    return true;
}

完整DEMO

Demo 代码难免写的比较草率，不要喷我奥，哈哈哈哈哈

public class PgTemplate {
private final DataStore dataStore;

public PgTemplate(DataStore dataStore) {
    this.dataStore = dataStore;
}

/**
 * @param type
 * @param features 需要追加的要素
 * @throws IOException
 */
public void appendFeatures(SimpleFeatureType type, List<SimpleFeature> features) throws IOException {
    ListFeatureCollection featureCollection = new ListFeatureCollection(type, features);
    String typeName = type.getTypeName();
    FeatureStore featureStore = (FeatureStore) dataStore.getFeatureSource(typeName);
    try {
        featureStore.addFeatures(featureCollection);
    } catch (IOException e) {
        e.printStackTrace();
    }
    Transaction transaction = new DefaultTransaction("appendData");
    featureStore.setTransaction(transaction);
    transaction.commit();
}

/**
 * 更新属性
 *
 * @param type
 * @param fieldsMap
 * @param filter
 * @throws IOException
 */
public void updateFeatures(SimpleFeatureType type, Map<String, Object> fieldsMap, Filter filter) throws IOException {
    String typeName = type.getTypeName();
    List<Name> names = new ArrayList<>();
    FeatureStore featureStore = (FeatureStore) dataStore.getFeatureSource(typeName);
    Set<String> keys = fieldsMap.keySet();
    for (String field : keys) {
        Name name = new NameImpl(field);
        names.add(name);
    }
    featureStore.modifyFeatures(names.toArray(new NameImpl[names.size()]), fieldsMap.values().toArray(), filter);
}

/**
 * 覆盖更新
 *
 * @param type
 * @param fieldsMap
 * @param filter
 * @throws IOException
 */
public void updateFeatureFeatureReader(SimpleFeatureType type, Map<String, Object> fieldsMap, Filter filter) throws IOException {
    String typeName = type.getTypeName();
    FeatureStore featureStore = (FeatureStore) dataStore.getFeatureSource(typeName);
    SimpleFeature simpleFeature = CustomFeatureBuilder.build(fieldsMap, typeName);
    FeatureReader<SimpleFeatureType, SimpleFeature> featureReader = new CollectionFeatureReader(simpleFeature);
    featureStore.setFeatures(featureReader);
    featureReader.close();
}

/**
 * 通过FeatureWriter 追加要素
 *
 * @param type
 * @param features
 * @throws IOException
 */
public void appendFeatureByFeatureWriter(SimpleFeatureType type, List<SimpleFeature> features) throws IOException {
    String typeName = type.getTypeName();
    FeatureWriter<SimpleFeatureType, SimpleFeature> featureWriter = dataStore.getFeatureWriterAppend(typeName, new DefaultTransaction("appendData"));
    for (SimpleFeature feature : features) {
        SimpleFeature remoteNext = featureWriter.next();
        remoteNext.setAttributes(feature.getAttributes());
        remoteNext.setDefaultGeometry(feature.getDefaultGeometry());
        featureWriter.write();
    }
    featureWriter.close();
}

/**
 * 删除数据
 *
 * @param
 * @param
 * @param
 */
public boolean deleteData(String layerName, Filter filter) {
    try {
        SimpleFeatureSource featureSource = dataStore.getFeatureSource(layerName);
        FeatureStore featureStore = (FeatureStore) featureSource;
        featureStore.removeFeatures(filter);
        Transaction transaction = new DefaultTransaction("delete");
        featureStore.setTransaction(transaction);
        transaction.commit();
    } catch (Exception e) {
        e.printStackTrace();
        return false;
    }
    return true;
}

/**
 * 查询要素
 *
 * @param layerName
 * @param filter
 * @return
 * @throws IOException
 */
public SimpleFeatureCollection queryFeatures(String layerName, Filter filter) throws IOException {
    SimpleFeatureCollection features = null;
    try {
        SimpleFeatureSource featureSource = dataStore.getFeatureSource(layerName);
        features = featureSource.getFeatures(filter);
        return features;
    } catch (Exception e) {
        e.printStackTrace();
    }
    return features;
}

/**
 * 查询要素
 *
 * @param layerName
 * @param filter
 * @return
 * @throws IOException
 */
public SimpleFeatureCollection queryFeaturesByFeatureReader(String layerName, Filter filter) throws IOException {
    FeatureReader<SimpleFeatureType, SimpleFeature> featureReader = dataStore.getFeatureReader(new Query(layerName, filter), new DefaultTransaction("query"));
    SimpleFeatureType featureType = featureReader.getFeatureType();
    List<SimpleFeature> features = new ArrayList<>();
    while (featureReader.hasNext()) {
        SimpleFeature next = featureReader.next();
        features.add(next);
    }
    return new ListFeatureCollection(featureType, features);
}

/**
 * 查询所有的表格
 *
 * @return
 * @throws IOException
 */
public List<String> getAllTables() throws IOException {
    String[] typeNames = this.dataStore.getTypeNames();
    List<String> tables = Arrays.stream(typeNames).collect(Collectors.toList());
    return tables;
}