Remove 32K limit from queries with collection parameters

SQL queries taking collection parameters currently create a seperate
query parameter for each collection element. There is a limit of
around 2^15 (32,768) query parameters.
Postgres DB natively supports array types, in which the whole array
is transmitted in binary as a single parameter. Changing queries to
use arrays removes the 32K limit on queries.

- Add support for Postgres arrays to queries
- Change repository methods to use arrays, and provide overloaded
  versions taking collection parameters
- Update tests to reflect 32K limit being lifted

Issue-ID: CPS-1573
Signed-off-by: danielhanrahan <daniel.hanrahan@est.tech>
Change-Id: I64f2aeaedbe54bfe12e3079cba0f2216759142c3

10 files changed, 130 insertions, 179 deletions

diff --git a/cps-ri/src/main/java/org/onap/cps/spi/impl/CpsModulePersistenceServiceImpl.java b/cps-ri/src/main/java/org/onap/cps/spi/impl/CpsModulePersistenceServiceImpl.java
index b4366de75b..cd1457e359 100755
--- a/cps-ri/src/main/java/org/onap/cps/spi/impl/CpsModulePersistenceServiceImpl.java
+++ b/cps-ri/src/main/java/org/onap/cps/spi/impl/CpsModulePersistenceServiceImpl.java
@@ -159,7 +159,7 @@ public class CpsModulePersistenceServiceImpl implements CpsModulePersistenceServ
     @Override
     public Collection<SchemaSet> getSchemaSetsByDataspaceName(final String dataspaceName) {
         final DataspaceEntity dataspaceEntity = dataspaceRepository.getByName(dataspaceName);
-        final List<SchemaSetEntity> schemaSetEntities = schemaSetRepository.getByDataspace(dataspaceEntity);
+        final List<SchemaSetEntity> schemaSetEntities = schemaSetRepository.findByDataspace(dataspaceEntity);
         return schemaSetEntities.stream()
                 .map(CpsModulePersistenceServiceImpl::toSchemaSet).collect(Collectors.toList());
     }
diff --git a/cps-ri/src/main/java/org/onap/cps/spi/repository/AnchorRepository.java b/cps-ri/src/main/java/org/onap/cps/spi/repository/AnchorRepository.java
index f7b586d7b3..fe9ff9e2f0 100755
--- a/cps-ri/src/main/java/org/onap/cps/spi/repository/AnchorRepository.java
+++ b/cps-ri/src/main/java/org/onap/cps/spi/repository/AnchorRepository.java
@@ -27,6 +27,7 @@ import org.onap.cps.spi.entities.DataspaceEntity;
 import org.onap.cps.spi.entities.SchemaSetEntity;
 import org.onap.cps.spi.exceptions.AnchorNotFoundException;
 import org.springframework.data.jpa.repository.JpaRepository;
+import org.springframework.data.jpa.repository.Modifying;
 import org.springframework.data.jpa.repository.Query;
 import org.springframework.data.repository.query.Param;
 import org.springframework.stereotype.Repository;
@@ -45,11 +46,27 @@ public interface AnchorRepository extends JpaRepository<AnchorEntity, Integer> {
 
     Collection<AnchorEntity> findAllBySchemaSet(SchemaSetEntity schemaSetEntity);
 
-    Collection<AnchorEntity> findAllByDataspaceAndNameIn(DataspaceEntity dataspaceEntity,
-                                                         Collection<String> anchorNames);
+    @Query(value = "SELECT * FROM anchor WHERE dataspace_id = :dataspaceId AND name = ANY (:anchorNames)",
+        nativeQuery = true)
+    Collection<AnchorEntity> findAllByDataspaceIdAndNameIn(@Param("dataspaceId") int dataspaceId,
+                                                           @Param("anchorNames") String[] anchorNames);
 
-    Collection<AnchorEntity> findAllByDataspaceAndSchemaSetNameIn(DataspaceEntity dataspaceEntity,
-                                                                  Collection<String> schemaSetNames);
+    default Collection<AnchorEntity> findAllByDataspaceAndNameIn(final DataspaceEntity dataspaceEntity,
+                                                                 final Collection<String> anchorNames) {
+        return findAllByDataspaceIdAndNameIn(dataspaceEntity.getId(), anchorNames.toArray(new String[0]));
+    }
+
+    @Query(value = "SELECT a.* FROM anchor a"
+        + " LEFT OUTER JOIN schema_set s ON a.schema_set_id = s.id"
+        + " WHERE a.dataspace_id = :dataspaceId AND s.name = ANY (:schemaSetNames)",
+        nativeQuery = true)
+    Collection<AnchorEntity> findAllByDataspaceIdAndSchemaSetNameIn(@Param("dataspaceId") int dataspaceId,
+                                                                    @Param("schemaSetNames") String[] schemaSetNames);
+
+    default Collection<AnchorEntity> findAllByDataspaceAndSchemaSetNameIn(final DataspaceEntity dataspaceEntity,
+                                                                          final Collection<String> schemaSetNames) {
+        return findAllByDataspaceIdAndSchemaSetNameIn(dataspaceEntity.getId(), schemaSetNames.toArray(new String[0]));
+    }
 
     Integer countByDataspace(DataspaceEntity dataspaceEntity);
 
@@ -57,12 +74,29 @@ public interface AnchorRepository extends JpaRepository<AnchorEntity, Integer> {
         + "JOIN schema_set_yang_resources ON schema_set_yang_resources.yang_resource_id = yang_resource.id\n"
         + "JOIN schema_set ON schema_set.id = schema_set_yang_resources.schema_set_id\n"
         + "JOIN anchor ON anchor.schema_set_id = schema_set.id\n"
-        + "WHERE schema_set.dataspace_id = :dataspaceId AND module_name IN (:moduleNames)\n"
+        + "WHERE schema_set.dataspace_id = :dataspaceId AND module_name = ANY (:moduleNames)\n"
         + "GROUP BY anchor.id, anchor.name, anchor.dataspace_id, anchor.schema_set_id\n"
         + "HAVING COUNT(DISTINCT module_name) = :sizeOfModuleNames", nativeQuery = true)
     Collection<AnchorEntity> getAnchorsByDataspaceIdAndModuleNames(@Param("dataspaceId") int dataspaceId,
-        @Param("moduleNames") Collection<String> moduleNames, @Param("sizeOfModuleNames") int sizeOfModuleNames);
+                                                                   @Param("moduleNames") String[] moduleNames,
+                                                                   @Param("sizeOfModuleNames") int sizeOfModuleNames);
+
+    default Collection<AnchorEntity> getAnchorsByDataspaceIdAndModuleNames(final int dataspaceId,
+                                                                           final Collection<String> moduleNames,
+                                                                           final int sizeOfModuleNames) {
+        final String[] moduleNamesArray = moduleNames.toArray(new String[0]);
+        return getAnchorsByDataspaceIdAndModuleNames(dataspaceId, moduleNamesArray, sizeOfModuleNames);
+    }
+
+    @Modifying
+    @Query(value = "DELETE FROM anchor WHERE dataspace_id = :dataspaceId AND name = ANY (:anchorNames)",
+        nativeQuery = true)
+    void deleteAllByDataspaceIdAndNameIn(@Param("dataspaceId") int dataspaceId,
+                                         @Param("anchorNames") String[] anchorNames);
+
+    default void deleteAllByDataspaceAndNameIn(final DataspaceEntity dataspaceEntity,
+                                               final Collection<String> anchorNames) {
+        deleteAllByDataspaceIdAndNameIn(dataspaceEntity.getId(), anchorNames.toArray(new String[0]));
+    }
 
-    void deleteAllByDataspaceAndNameIn(DataspaceEntity dataspaceEntity,
-                                       Collection<String> anchorNames);
 }
diff --git a/cps-ri/src/main/java/org/onap/cps/spi/repository/FragmentNativeRepository.java b/cps-ri/src/main/java/org/onap/cps/spi/repository/FragmentNativeRepository.java
deleted file mode 100644
index bad68f7e58..0000000000
--- a/cps-ri/src/main/java/org/onap/cps/spi/repository/FragmentNativeRepository.java
+++ /dev/null
@@ -1,49 +0,0 @@
-/*
- *  ============LICENSE_START=======================================================
- *  Copyright (C) 2023 Nordix Foundation
- *  ================================================================================
- *  Licensed under the Apache License, Version 2.0 (the "License");
- *  you may not use this file except in compliance with the License.
- *  You may obtain a copy of the License at
- *
- *        http://www.apache.org/licenses/LICENSE-2.0
- *
- *  Unless required by applicable law or agreed to in writing, software
- *  distributed under the License is distributed on an "AS IS" BASIS,
- *  WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
- *  See the License for the specific language governing permissions and
- *  limitations under the License.
- *
- *  SPDX-License-Identifier: Apache-2.0
- *  ============LICENSE_END=========================================================
- */
-
-package org.onap.cps.spi.repository;
-
-import java.util.Collection;
-
-/**
- * This interface is used in delete fragment entity by id with child using native sql queries.
- */
-public interface FragmentNativeRepository {
-
-    /**
-     * Delete fragment entities for each supplied xpath.
-     * This method will delete list elements or other data nodes, but not whole lists.
-     * Non-existing xpaths will not result in an exception.
-     * @param anchorId the id of the anchor
-     * @param xpaths   xpaths of data nodes to remove
-     */
-    void deleteByAnchorIdAndXpaths(int anchorId, Collection<String> xpaths);
-
-    /**
-     * Delete fragment entities that are list elements of each supplied list xpath.
-     * For example, if xpath '/parent/list' is provided, then list all elements in '/parent/list' will be deleted,
-     * e.g. /parent/list[@key='A'], /parent/list[@key='B'].
-     * This method will only delete whole lists by xpath; xpaths to list elements or other data nodes will be ignored.
-     * Non-existing xpaths will not result in an exception.
-     * @param anchorId   the id of the anchor
-     * @param listXpaths xpaths of whole lists to remove
-     */
-    void deleteListsByAnchorIdAndXpaths(int anchorId, Collection<String> listXpaths);
-}
diff --git a/cps-ri/src/main/java/org/onap/cps/spi/repository/FragmentNativeRepositoryImpl.java b/cps-ri/src/main/java/org/onap/cps/spi/repository/FragmentNativeRepositoryImpl.java
deleted file mode 100644
index 04b7080def..0000000000
--- a/cps-ri/src/main/java/org/onap/cps/spi/repository/FragmentNativeRepositoryImpl.java
+++ /dev/null
@@ -1,72 +0,0 @@
-/*
- *  ============LICENSE_START=======================================================
- *  Copyright (C) 2023 Nordix Foundation
- *  ================================================================================
- *  Licensed under the Apache License, Version 2.0 (the "License");
- *  you may not use this file except in compliance with the License.
- *  You may obtain a copy of the License at
- *
- *        http://www.apache.org/licenses/LICENSE-2.0
- *
- *  Unless required by applicable law or agreed to in writing, software
- *  distributed under the License is distributed on an "AS IS" BASIS,
- *  WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
- *  See the License for the specific language governing permissions and
- *  limitations under the License.
- *
- *  SPDX-License-Identifier: Apache-2.0
- *  ============LICENSE_END=========================================================
- */
-
-package org.onap.cps.spi.repository;
-
-import java.util.Collection;
-import java.util.Collections;
-import java.util.stream.Collectors;
-import javax.persistence.EntityManager;
-import javax.persistence.PersistenceContext;
-import javax.persistence.Query;
-import lombok.RequiredArgsConstructor;
-
-@RequiredArgsConstructor
-public class FragmentNativeRepositoryImpl implements FragmentNativeRepository {
-
-    @PersistenceContext
-    private final EntityManager entityManager;
-
-    @Override
-    public void deleteByAnchorIdAndXpaths(final int anchorId, final Collection<String> xpaths) {
-        final String queryString =
-            "DELETE FROM fragment f WHERE f.anchor_id = ? AND (f.xpath IN (:parameterPlaceholders))";
-        executeUpdateWithAnchorIdAndCollection(queryString, anchorId, xpaths);
-    }
-
-    @Override
-    public void deleteListsByAnchorIdAndXpaths(final int anchorId, final Collection<String> listXpaths) {
-        final Collection<String> listXpathPatterns =
-            listXpaths.stream().map(listXpath -> listXpath + "[%").collect(Collectors.toSet());
-        final String queryString =
-            "DELETE FROM fragment f WHERE f.anchor_id = ? AND (f.xpath LIKE ANY (array[:parameterPlaceholders]))";
-        executeUpdateWithAnchorIdAndCollection(queryString, anchorId, listXpathPatterns);
-    }
-
-    // Accept security hotspot as placeholders in SQL query are created internally, not from user input.
-    @SuppressWarnings("squid:S2077")
-    private void executeUpdateWithAnchorIdAndCollection(final String sqlTemplate, final int anchorId,
-                                                        final Collection<String> collection) {
-        if (!collection.isEmpty()) {
-            final String parameterPlaceholders = String.join(",", Collections.nCopies(collection.size(), "?"));
-            final String queryStringWithParameterPlaceholders =
-                sqlTemplate.replaceFirst(":parameterPlaceholders\\b", parameterPlaceholders);
-
-            final Query query = entityManager.createNativeQuery(queryStringWithParameterPlaceholders);
-            query.setParameter(1, anchorId);
-            int parameterIndex = 2;
-            for (final String parameterValue : collection) {
-                query.setParameter(parameterIndex++, parameterValue);
-            }
-            query.executeUpdate();
-        }
-    }
-
-}
diff --git a/cps-ri/src/main/java/org/onap/cps/spi/repository/FragmentRepository.java b/cps-ri/src/main/java/org/onap/cps/spi/repository/FragmentRepository.java
index d486a39c7e..d25832ba54 100755
--- a/cps-ri/src/main/java/org/onap/cps/spi/repository/FragmentRepository.java
+++ b/cps-ri/src/main/java/org/onap/cps/spi/repository/FragmentRepository.java
@@ -37,8 +37,7 @@ import org.springframework.data.repository.query.Param;
 import org.springframework.stereotype.Repository;

 

 @Repository

-public interface FragmentRepository extends JpaRepository<FragmentEntity, Long>, FragmentRepositoryCpsPathQuery,

-        FragmentNativeRepository {

+public interface FragmentRepository extends JpaRepository<FragmentEntity, Long>, FragmentRepositoryCpsPathQuery {

 

     Optional<FragmentEntity> findByAnchorAndXpath(AnchorEntity anchorEntity, String xpath);

 

@@ -52,13 +51,39 @@ public interface FragmentRepository extends JpaRepository<FragmentEntity, Long>,
     @Query("SELECT f FROM FragmentEntity f WHERE anchor = :anchor")

     List<FragmentExtract> findAllExtractsByAnchor(@Param("anchor") AnchorEntity anchorEntity);

 

-    List<FragmentEntity> findAllByAnchorAndXpathIn(AnchorEntity anchorEntity, Collection<String> xpath);

+    @Query(value = "SELECT * FROM fragment WHERE xpath = ANY (:xpaths)", nativeQuery = true)

+    List<FragmentEntity> findAllByXpathIn(@Param("xpaths") String[] xpath);

 

-    List<FragmentEntity> findAllByXpathIn(Collection<String> xpath);

+    default List<FragmentEntity> findAllByXpathIn(final Collection<String> xpaths) {

+        return findAllByXpathIn(xpaths.toArray(new String[0]));

+    }

+

+    @Modifying

+    @Query(value = "DELETE FROM fragment WHERE anchor_id = ANY (:anchorIds)", nativeQuery = true)

+    void deleteByAnchorIdIn(@Param("anchorIds") int[] anchorIds);

+

+    default void deleteByAnchorIn(final Collection<AnchorEntity> anchorEntities) {

+        deleteByAnchorIdIn(anchorEntities.stream().map(AnchorEntity::getId).mapToInt(id -> id).toArray());

+    }

 

     @Modifying

-    @Query("DELETE FROM FragmentEntity WHERE anchor IN (:anchors)")

-    void deleteByAnchorIn(@Param("anchors") Collection<AnchorEntity> anchorEntities);

+    @Query(value = "DELETE FROM fragment WHERE anchor_id = :anchorId AND xpath = ANY (:xpaths)", nativeQuery = true)

+    void deleteByAnchorIdAndXpaths(@Param("anchorId") int anchorId, @Param("xpaths") String[] xpaths);

+

+    default void deleteByAnchorIdAndXpaths(final int anchorId, final Collection<String> xpaths) {

+        deleteByAnchorIdAndXpaths(anchorId, xpaths.toArray(new String[0]));

+    }

+

+    @Modifying

+    @Query(value = "DELETE FROM fragment f WHERE anchor_id = :anchorId AND xpath LIKE ANY (:xpathPatterns)",

+        nativeQuery = true)

+    void deleteByAnchorIdAndXpathLikeAny(@Param("anchorId") int anchorId,

+                                         @Param("xpathPatterns") String[] xpathPatterns);

+

+    default void deleteListsByAnchorIdAndXpaths(int anchorId, Collection<String> xpaths) {

+        final String[] listXpathPatterns = xpaths.stream().map(xpath -> xpath + "[%").toArray(String[]::new);

+        deleteByAnchorIdAndXpathLikeAny(anchorId, listXpathPatterns);

+    }

 

     @Query("SELECT f FROM FragmentEntity f WHERE anchor = :anchor"

         + " AND (xpath = :parentXpath OR xpath LIKE CONCAT(:parentXpath,'/%'))")

@@ -80,9 +105,15 @@ public interface FragmentRepository extends JpaRepository<FragmentEntity, Long>,
     List<FragmentExtract> quickFindWithDescendants(@Param("anchorId") int anchorId,

                                                    @Param("xpathRegex") String xpathRegex);

 

-    @Query("SELECT xpath FROM FragmentEntity WHERE anchor = :anchor AND xpath IN :xpaths")

-    List<String> findAllXpathByAnchorAndXpathIn(@Param("anchor") AnchorEntity anchorEntity,

-                                                @Param("xpaths") Collection<String> xpaths);

+    @Query(value = "SELECT xpath FROM fragment WHERE anchor_id = :anchorId AND xpath = ANY (:xpaths)",

+        nativeQuery = true)

+    List<String> findAllXpathByAnchorIdAndXpathIn(@Param("anchorId") int anchorId,

+                                                  @Param("xpaths") String[] xpaths);

+

+    default List<String> findAllXpathByAnchorAndXpathIn(final AnchorEntity anchorEntity,

+                                                        final Collection<String> xpaths) {

+        return findAllXpathByAnchorIdAndXpathIn(anchorEntity.getId(), xpaths.toArray(new String[0]));

+    }

 

     boolean existsByAnchorAndXpathStartsWith(AnchorEntity anchorEntity, String xpath);

 

@@ -93,7 +124,7 @@ public interface FragmentRepository extends JpaRepository<FragmentEntity, Long>,
         = "WITH RECURSIVE parent_search AS ("

         + "  SELECT id, 0 AS depth "

         + "    FROM fragment "

-        + "   WHERE anchor_id = :anchorId AND xpath IN :xpaths "

+        + "   WHERE anchor_id = :anchorId AND xpath = ANY (:xpaths) "

         + "   UNION "

         + "  SELECT c.id, depth + 1 "

         + "    FROM fragment c INNER JOIN parent_search p ON c.parent_id = p.id"

@@ -104,9 +135,14 @@ public interface FragmentRepository extends JpaRepository<FragmentEntity, Long>,
         nativeQuery = true

     )

     List<FragmentExtract> findExtractsWithDescendants(@Param("anchorId") int anchorId,

-                                                      @Param("xpaths") Collection<String> xpaths,

+                                                      @Param("xpaths") String[] xpaths,

                                                       @Param("maxDepth") int maxDepth);

 

+    default List<FragmentExtract> findExtractsWithDescendants(final int anchorId, final Collection<String> xpaths,

+                                                              final int maxDepth) {

+        return findExtractsWithDescendants(anchorId, xpaths.toArray(new String[0]), maxDepth);

+    }

+

     @Query(value = "SELECT id, anchor_id AS anchorId, xpath, parent_id AS parentId,"

             + " CAST(attributes AS TEXT) AS attributes"

             + " FROM FRAGMENT WHERE xpath ~ :xpathRegex",

diff --git a/cps-ri/src/main/java/org/onap/cps/spi/repository/SchemaSetRepository.java b/cps-ri/src/main/java/org/onap/cps/spi/repository/SchemaSetRepository.java
index 3263f34473..3c5f973cb0 100644
--- a/cps-ri/src/main/java/org/onap/cps/spi/repository/SchemaSetRepository.java
+++ b/cps-ri/src/main/java/org/onap/cps/spi/repository/SchemaSetRepository.java
@@ -24,7 +24,6 @@ package org.onap.cps.spi.repository;
 import java.util.Collection;
 import java.util.List;
 import java.util.Optional;
-import java.util.stream.Collectors;
 import org.onap.cps.spi.entities.DataspaceEntity;
 import org.onap.cps.spi.entities.SchemaSetEntity;
 import org.onap.cps.spi.exceptions.SchemaSetNotFoundException;
@@ -44,7 +43,7 @@ public interface SchemaSetRepository extends JpaRepository<SchemaSetEntity, Inte
      * @param dataspaceEntity dataspace entity
      * @return list of schema set entity
      */
-    Collection<SchemaSetEntity> findByDataspace(DataspaceEntity dataspaceEntity);
+    List<SchemaSetEntity> findByDataspace(DataspaceEntity dataspaceEntity);
 
     Integer countByDataspace(DataspaceEntity dataspaceEntity);
 
@@ -61,24 +60,20 @@ public interface SchemaSetRepository extends JpaRepository<SchemaSetEntity, Inte
             .orElseThrow(() -> new SchemaSetNotFoundException(dataspaceEntity.getName(), schemaSetName));
     }
 
-    /**
-     * Gets all schema sets for a given dataspace.
-     *
-     * @param dataspaceEntity dataspace entity
-     * @return list of schema set entity
-     * @throws SchemaSetNotFoundException if SchemaSet not found
-     */
-    default List<SchemaSetEntity> getByDataspace(final DataspaceEntity dataspaceEntity) {
-        return findByDataspace(dataspaceEntity).stream().collect(Collectors.toList());
-    }
+    @Modifying
+    @Query(value = "DELETE FROM schema_set WHERE dataspace_id = :dataspaceId AND name = ANY (:schemaSetNames)",
+        nativeQuery = true)
+    void deleteByDataspaceIdAndNameIn(@Param("dataspaceId") final int dataspaceId,
+                                      @Param("schemaSetNames") final String[] schemaSetNames);
 
     /**
      * Delete multiple schema sets in a given dataspace.
      * @param dataspaceEntity dataspace entity
      * @param schemaSetNames  schema set names
      */
-    @Modifying
-    @Query("DELETE FROM SchemaSetEntity s WHERE s.dataspace = :dataspaceEntity AND s.name IN (:schemaSetNames)")
-    void deleteByDataspaceAndNameIn(@Param("dataspaceEntity") DataspaceEntity dataspaceEntity,
-                                    @Param("schemaSetNames") Collection<String> schemaSetNames);
+    default void deleteByDataspaceAndNameIn(final DataspaceEntity dataspaceEntity,
+                                            final Collection<String> schemaSetNames) {
+        deleteByDataspaceIdAndNameIn(dataspaceEntity.getId(), schemaSetNames.toArray(new String[0]));
+    }
+
 }
diff --git a/cps-ri/src/main/java/org/onap/cps/spi/repository/YangResourceRepository.java b/cps-ri/src/main/java/org/onap/cps/spi/repository/YangResourceRepository.java
index fff0a6a037..7584ff65c0 100644
--- a/cps-ri/src/main/java/org/onap/cps/spi/repository/YangResourceRepository.java
+++ b/cps-ri/src/main/java/org/onap/cps/spi/repository/YangResourceRepository.java
@@ -35,7 +35,11 @@ import org.springframework.stereotype.Repository;
 public interface YangResourceRepository extends JpaRepository<YangResourceEntity, Long>,
     YangResourceNativeRepository, SchemaSetYangResourceRepository {
 
-    List<YangResourceEntity> findAllByChecksumIn(Set<String> checksum);
+    List<YangResourceEntity> findAllByChecksumIn(String[] checksums);
+
+    default List<YangResourceEntity> findAllByChecksumIn(final Collection<String> checksums) {
+        return findAllByChecksumIn(checksums.toArray(new String[0]));
+    }
 
     @Query(value = "SELECT DISTINCT\n"
         + "yang_resource.module_name AS module_name,\n"
@@ -86,9 +90,14 @@ public interface YangResourceRepository extends JpaRepository<YangResourceEntity
         + "schema_set.id\n"
         + "JOIN yang_resource ON yang_resource.id = schema_set_yang_resources.yang_resource_id\n"
         + "WHERE\n"
-        + "dataspace.name = :dataspaceName and yang_resource.module_Name IN (:moduleNames)", nativeQuery = true)
+        + "dataspace.name = :dataspaceName and yang_resource.module_Name = ANY (:moduleNames)", nativeQuery = true)
     Set<YangResourceModuleReference> findAllModuleReferencesByDataspaceAndModuleNames(
-            @Param("dataspaceName") String dataspaceName, @Param("moduleNames") Collection<String> moduleNames);
+            @Param("dataspaceName") String dataspaceName, @Param("moduleNames") String[] moduleNames);
+
+    default Set<YangResourceModuleReference> findAllModuleReferencesByDataspaceAndModuleNames(
+        final String dataspaceName, final Collection<String> moduleNames) {
+        return findAllModuleReferencesByDataspaceAndModuleNames(dataspaceName, moduleNames.toArray(new String[0]));
+    }
 
     @Modifying
     @Query(value = "DELETE FROM yang_resource yr WHERE NOT EXISTS "
diff --git a/cps-ri/src/test/groovy/org/onap/cps/spi/impl/CpsModulePersistenceServiceSpec.groovy b/cps-ri/src/test/groovy/org/onap/cps/spi/impl/CpsModulePersistenceServiceSpec.groovy
index 9ef9732681..811c3290b9 100644
--- a/cps-ri/src/test/groovy/org/onap/cps/spi/impl/CpsModulePersistenceServiceSpec.groovy
+++ b/cps-ri/src/test/groovy/org/onap/cps/spi/impl/CpsModulePersistenceServiceSpec.groovy
@@ -84,7 +84,7 @@ class CpsModulePersistenceServiceSpec extends Specification {
 
     def 'Store schema set error scenario: #scenario.'() {
         given: 'no yang resource are currently saved'
-            yangResourceRepositoryMock.findAllByChecksumIn(_) >> Collections.emptyList()
+            yangResourceRepositoryMock.findAllByChecksumIn(_ as Collection<String>) >> Collections.emptyList()
         and: 'persisting yang resource raises db constraint exception (in case of concurrent requests for example)'
             yangResourceRepositoryMock.saveAll(_) >> { throw dbException }
         when: 'attempt to store schema set '
diff --git a/integration-test/src/test/groovy/org/onap/cps/integration/performance/cps/CpsAdminServiceLimits.groovy b/integration-test/src/test/groovy/org/onap/cps/integration/performance/cps/CpsAdminServiceLimits.groovy
index 7875caec35..0034af453b 100644
--- a/integration-test/src/test/groovy/org/onap/cps/integration/performance/cps/CpsAdminServiceLimits.groovy
+++ b/integration-test/src/test/groovy/org/onap/cps/integration/performance/cps/CpsAdminServiceLimits.groovy
@@ -22,7 +22,6 @@ package org.onap.cps.integration.performance.cps
 
 import org.onap.cps.api.CpsAdminService
 import org.onap.cps.integration.performance.base.CpsPerfTestBase
-import org.springframework.dao.DataAccessResourceFailureException
 
 class CpsAdminServiceLimits extends CpsPerfTestBase {
 
@@ -32,20 +31,20 @@ class CpsAdminServiceLimits extends CpsPerfTestBase {
 
     def 'Get anchors from multiple schema set names limit exceeded: 32,766 (~ 2^15) schema set names.'() {
         given: 'more than 32,766 schema set names'
-            def schemaSetNames = (0..32_766).collect { "size-of-this-name-does-not-matter-for-limit-" + it }
+            def schemaSetNames = (0..40_000).collect { "size-of-this-name-does-not-matter-for-limit-" + it }
         when: 'single get is executed to get all the anchors'
             objectUnderTest.getAnchors(CPS_PERFORMANCE_TEST_DATASPACE, schemaSetNames)
-        then: 'a database exception is thrown'
-            thrown(DataAccessResourceFailureException.class)
+        then: 'a database exception is not thrown'
+            noExceptionThrown()
     }
 
     def 'Querying anchor names limit exceeded: 32,766 (~ 2^15) modules.'() {
         given: 'more than 32,766 module names'
-            def moduleNames = (0..32_766).collect { "size-of-this-name-does-not-matter-for-limit-" + it }
+            def moduleNames = (0..40_000).collect { "size-of-this-name-does-not-matter-for-limit-" + it }
         when: 'single query is executed to get all the anchors'
             objectUnderTest.queryAnchorNames(CPS_PERFORMANCE_TEST_DATASPACE, moduleNames)
-        then: 'a database exception is thrown'
-            thrown(DataAccessResourceFailureException.class)
+        then: 'a database exception is not thrown'
+            noExceptionThrown()
     }
 
 }
diff --git a/integration-test/src/test/groovy/org/onap/cps/integration/performance/cps/CpsDataServiceLimits.groovy b/integration-test/src/test/groovy/org/onap/cps/integration/performance/cps/CpsDataServiceLimits.groovy
index 2df910194d..1579470eab 100644
--- a/integration-test/src/test/groovy/org/onap/cps/integration/performance/cps/CpsDataServiceLimits.groovy
+++ b/integration-test/src/test/groovy/org/onap/cps/integration/performance/cps/CpsDataServiceLimits.groovy
@@ -23,8 +23,7 @@ package org.onap.cps.integration.performance.cps
 import java.time.OffsetDateTime
 import org.onap.cps.api.CpsDataService
 import org.onap.cps.integration.performance.base.CpsPerfTestBase
-import org.springframework.dao.DataAccessResourceFailureException
-import org.springframework.transaction.TransactionSystemException
+import org.onap.cps.spi.exceptions.DataNodeNotFoundException
 
 import static org.onap.cps.spi.FetchDescendantsOption.INCLUDE_ALL_DESCENDANTS
 
@@ -36,29 +35,29 @@ class CpsDataServiceLimits extends CpsPerfTestBase {
 
     def 'Multiple get limit exceeded: 32,764 (~ 2^15) xpaths.'() {
         given: 'more than 32,764 xpaths'
-            def xpaths = (0..32_764).collect { "/size/of/this/path/does/not/matter/for/limit[@id='" + it + "']" }
+            def xpaths = (0..40_000).collect { "/size/of/this/path/does/not/matter/for/limit[@id='" + it + "']" }
         when: 'single operation is executed to get all datanodes with given xpaths'
             objectUnderTest.getDataNodesForMultipleXpaths(CPS_PERFORMANCE_TEST_DATASPACE, 'bookstore1', xpaths, INCLUDE_ALL_DESCENDANTS)
-        then: 'a database exception is thrown'
-            thrown(DataAccessResourceFailureException.class)
+        then: 'a database exception is not thrown'
+            noExceptionThrown()
     }
 
     def 'Delete multiple datanodes limit exceeded: 32,767 (~ 2^15) xpaths.'() {
         given: 'more than 32,767 xpaths'
-            def xpaths = (0..32_767).collect { "/size/of/this/path/does/not/matter/for/limit[@id='" + it + "']" }
+            def xpaths = (0..40_000).collect { "/size/of/this/path/does/not/matter/for/limit[@id='" + it + "']" }
         when: 'single operation is executed to delete all datanodes with given xpaths'
             objectUnderTest.deleteDataNodes(CPS_PERFORMANCE_TEST_DATASPACE, 'bookstore1', xpaths, OffsetDateTime.now())
-        then: 'a database exception is thrown'
-            thrown(TransactionSystemException.class)
+        then: 'a database exception is not thrown (but a CPS DataNodeNotFoundException is thrown)'
+            thrown(DataNodeNotFoundException.class)
     }
 
     def 'Delete datanodes from multiple anchors limit exceeded: 32,766 (~ 2^15) anchors.'() {
         given: 'more than 32,766 anchor names'
-            def anchorNames = (0..32_766).collect { "size-of-this-name-does-not-matter-for-limit-" + it }
+            def anchorNames = (0..40_000).collect { "size-of-this-name-does-not-matter-for-limit-" + it }
         when: 'single operation is executed to delete all datanodes in given anchors'
             objectUnderTest.deleteDataNodes(CPS_PERFORMANCE_TEST_DATASPACE, anchorNames, OffsetDateTime.now())
-        then: 'a database exception is thrown'
-            thrown(DataAccessResourceFailureException.class)
+        then: 'a database exception is not thrown'
+            noExceptionThrown()
     }
 
 }