DataStore通过geopkg写入SQLite数据库

geotools工具类中 DataStore（或者说SimpleFeature）如何写入SQLite数据库。

引入依赖

只需要geopkg这个依赖，里面包括了sqlite-jdbc。

        <dependency>
            <groupId>org.geotools</groupId>
            <artifactId>gt-geopkg</artifactId>
            <version>${gt.version}</version>
        </dependency>

写入

先获取DataStore，因为SQLite是基于文件实现，一个文件一个数据库，单写多读。

    private DataStore getSqliteDataStore(String reqId) throws IOException {
        if (!Files.exists(Paths.get(cachePath))) {
            Files.createDirectory(Paths.get(cachePath));
        }
        Map<String, String> params = new HashMap<>();
        params.put("dbtype", "geopkg");
        params.put("database", getCacheDb(reqId));
        return DataStoreFinder.getDataStore(params);
    }

    private String getCacheDb(String reqId) {
        return cachePath + File.separator + reqId + ".db";
    }

写入实现, 我们传入参数是一个SimpleFeature的迭代器，采用分批写入方式。

    public void write(String reqId, SimpleFeatureIterator iterator) {
        if (!iterator.hasNext()) {
            return;
        }
        try {
            DataStore datastore = getSqliteDataStore(reqId);
            SimpleFeature f = iterator.next();
            SimpleFeatureType featureType = f.getFeatureType();
            final int batchSize = 512;
            List<SimpleFeature> batch = new ArrayList<>(batchSize);
            batch.add(f);
            // 建表
            datastore.createSchema(featureType);

            SimpleFeatureSource source = datastore.getFeatureSource(featureType.getTypeName());
            if (source instanceof SimpleFeatureStore) {
                SimpleFeatureStore store = (SimpleFeatureStore) source;
                while (iterator.hasNext()) {
                    f = iterator.next();
                    batch.add(f);
                    if (batch.size() == batchSize) {
                        writeFeatures(featureType, batch, store);
                    }
                }
                if (!batch.isEmpty()) {
                    writeFeatures(featureType, batch, store);
                }
            }
        } catch (IOException e) {
            log.error("Write Error", e);
        }
    }

分批写入：

    private void writeFeatures(SimpleFeatureType featureType, List<SimpleFeature> batch, 
    	SimpleFeatureStore store) throws IOException {
        Transaction transaction = new DefaultTransaction("create");
        try {
            store.addFeatures(new ListFeatureCollection(featureType, batch));
            transaction.commit();
            batch.clear();
        } catch (Exception e) {
            log.warn("Write SimpleFeature Error", e);
            transaction.rollback();
        } finally {
            transaction.close();
        }
    }

读取

读取采用直接用JDBC读SQLite数据库，返回分页数据。ResultSetFeatureIterator在这里自己实现，就是ResultSet转SimpleFeatureIterator，并不重要。

    public PageResultData read(String reqId, int current, int pageSize) {
        try (Connection connection = DriverManager.getConnection("jdbc:sqlite:" + getCacheDb(reqId))) {
            Statement statement = connection.createStatement();
            statement.setQueryTimeout(30);  // set timeout to 30 sec.

            ResultSet rs = statement.executeQuery(String.format("select count(1) from %s", reqId));
            rs.next();
            int total = rs.getInt(1);
            rs = statement.executeQuery(String.format("select * from %s limit %s offset %s", reqId, pageSize, current - 1));
            ResultSetFeatureIterator it = new ResultSetFeatureIterator(reqId, rs);
            List<SimpleFeature> features = new ArrayList<>(pageSize);
            while (it.hasNext()) {
                features.add(it.next());
            }
            return new PageResultData(features, current, pageSize, total);
        } catch (SQLException e) {
            log.error("Get Page Sqlite Error", e);
        }
        return new PageResultData();
    }

测试

    @Test
    public void testSqliteAccessFeature() {
        String reqId = "req" + System.currentTimeMillis();
        // 写,构造数据
        SimpleFeatureTypeBuilder bt = new SimpleFeatureTypeBuilder();
        bt.add("name", String.class);
        bt.setName(reqId);
        SimpleFeatureType sft = bt.buildFeatureType();
        ListFeatureCollection simpleFeatures = new ListFeatureCollection(sft, new SimpleFeature[]{
                SimpleFeatureBuilder.build(sft, new Object[]{"jimo"}, null),
                SimpleFeatureBuilder.build(sft, new Object[]{"hehe"}, null),
                SimpleFeatureBuilder.build(sft, new Object[]{"lily"}, null)
        });

        // 调用
        write(reqId, new DelegateSimpleFeatureIterator(simpleFeatures.iterator()));

        // 分页读
        PageResultData data = read(reqId, 1, 10);
        assertEquals(3, data.getTotal());
        assertEquals(3, data.getFeatures().size());
    }