建立连接
经过测试，这套连接逻辑除了支持纯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 demoWHERE xmbh = '3308812023104' AND zzdybh = '3308812023104003' AND zzlx = '10'
用代码查：
SimpleFeatureCollection simpleFeatureCollection = pgTemplate.queryFeatures("demo", 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("demo", 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);
}}, "demo" , geometry);
SimpleFeatureType simpleFeatureType = dataStore.getSchema("demo");
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);
}}, "demo" );
SimpleFeatureType simpleFeatureType = dataStore.getSchema("demo");
pgTemplate.appendFeatureByFeatureWriter(simpleFeatureType, Arrays.asList(build));
追加要素的原理都是借助于空间函数，我们如果调试源码就会发现最终生成了一个SQL类似于下面这个SQL
INSERT INTO "sde"."demo" ( "xmbh","zzdybh","zxmmc","zzlx","zxmlx","zxmbh","zgbm","jsgm","jhtz","xzgdmj","xzgddb","lxrq","wcrq","bz","txmj","bsm","czzj","shzj","gdb_geomattr_data","shape","objectid" ) VALUES ( 'ces','11111','update',null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,ST_GeomFromText('POLYGON ((119.1357115200458 29.966757303092994, 119.14239751148503 29.622428743972602, 119.49341206204465 29.84975245290645, 119.23265839591465 30.067047174681456, 119.1357115200458 29.966757303092994))', 4490),2)
源码如下：
{@link JDBCDataStore#insertNonPS}
try {
for (SimpleFeature feature : features) {
String sql = insertSQL(featureType, feature, keysFetcher, cx);
((BasicSQLDialect) dialect).onInsert(st, cx, featureType);
LOGGER.log(Level.FINE, "Inserting new feature: {0}", sql);
if (keysFetcher.hasAutoGeneratedKeys()) {
st.executeUpdate(sql, Statement.RETURN_GENERATED_KEYS);
} else {
st.executeUpdate(sql);
}
keysFetcher.postInsert(featureType, feature, cx, st);
}
} finally {
closeSafe(st);
}
更新
- 更新属性
/**
* 更新属性
* @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("demo");
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 是覆盖更新的逻辑,所以使用的时候要谨慎一点
下面有这么多实现类，具体怎么组合使用就看你的想象力了：
文章来源：https://www.toymoban.com/news/detail-812401.html
删除要素
/**
* 删除数据
*
* @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 代码难免写的比较草率，不要喷我奥，哈哈哈哈哈文章来源地址https://www.toymoban.com/news/detail-812401.html
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;
}
到了这里，关于Geotools-PG空间库（Crud，属性查询，空间查询）的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！
