private void insertOrUpdate(Connection conn, ResultSet oldRow, ResultSet newRow) throws SQLException {
        Long apiVersionId = newRow.getLong("api_version_id");
        String planId = newRow.getString("plan_id");
        String planVersion = newRow.getString("version");
        DiscoverabilityLevel level = Optional.ofNullable(newRow.getString("discoverability"))
                .map(DiscoverabilityLevel::valueOf)
                .orElse(DiscoverabilityLevel.ORG_MEMBERS);

        Jdbi jdbi = Jdbi.create(conn);

        FlatApiVersionBeanView av = jdbi.withHandle(h -> h
                        .createQuery("select avb.* FROM api_versions avb WHERE avb.id = :id")
                        .bind("id", apiVersionId))
                .mapToBean(FlatApiVersionBeanView.class)
                .first();

        String pdeKey = String.join(":", av.getApiOrgId(), av.getApiId(), av.getVersion(), planId, planVersion);

        if (oldRow != null) {
            DiscoverabilityLevel oldLevel = Optional.ofNullable(oldRow.getString("discoverability"))
                    .map(DiscoverabilityLevel::valueOf)
                    .orElse(DiscoverabilityLevel.ORG_MEMBERS);
            // If no change, just exit.
            if (oldLevel == level) {
                return;
            }
        }

        jdbi.withHandle(h -> h.createUpdate(
                        "MERGE INTO discoverability(id, org_id, api_id, api_version, plan_id, plan_version, discoverability) KEY(id) "
                                + "VALUES (:id, :orgId, :apiId, :apiVersion, :planId, :planVersion, :discoverability)")
                .bind("id", pdeKey)
                .bind("orgId", av.getApiOrgId())
                .bind("apiId", av.getApiId())
                .bind("apiVersion", av.getVersion())
                .bind("planId", planId)
                .bind("planVersion", planVersion)
                .bind("discoverability", level)
                .execute()
        );
    }
Synchronize DiscoverabilityEntity materialized view when any ApiPlan with a DiscoverabilityLevel is inserted, updated, or deleted.

@author Marc Savy {@literal <marc@blackparrotlabs.io>}
manager/api/jpa/src/main/java/io/apiman/manager/api/jpa/h2/ApiPlanDiscoverabilityTrigger.java