This is an automated email from the ASF dual-hosted git repository.
ilgrosso pushed a commit to branch 4_0_X
in repository https://gitbox.apache.org/repos/asf/syncope.git
The following commit(s) were added to refs/heads/4_0_X by this push:
new d0e16b0c15 [SYNCOPE-1982] Improve query perfomance on
OraclePlainSchemaRepoExtImpl (#1450)
d0e16b0c15 is described below
commit d0e16b0c15a1d3308e601b7ea8d77f672777f3fc
Author: Valerio Crescia <[email protected]>
AuthorDate: Sat Jul 11 08:44:35 2026 +0200
[SYNCOPE-1982] Improve query perfomance on OraclePlainSchemaRepoExtImpl
(#1450)
---
.../persistence/jpa/OraclePersistenceContext.java | 3 +-
.../jpa/dao/repo/OraclePlainSchemaRepoExtImpl.java | 60 ++++++++++++----------
2 files changed, 35 insertions(+), 28 deletions(-)
diff --git
a/core/persistence-jpa/src/main/java/org/apache/syncope/core/persistence/jpa/OraclePersistenceContext.java
b/core/persistence-jpa/src/main/java/org/apache/syncope/core/persistence/jpa/OraclePersistenceContext.java
index c677d07869..a1345b37cf 100644
---
a/core/persistence-jpa/src/main/java/org/apache/syncope/core/persistence/jpa/OraclePersistenceContext.java
+++
b/core/persistence-jpa/src/main/java/org/apache/syncope/core/persistence/jpa/OraclePersistenceContext.java
@@ -102,9 +102,8 @@ public class OraclePersistenceContext {
public PlainSchemaRepoExt plainSchemaRepoExt(
final AnyUtilsFactory anyUtilsFactory,
final @Lazy ExternalResourceDAO resourceDAO,
- final @Lazy PlainSchemaDAO plainSchemaDAO,
final EntityManager entityManager) {
- return new OraclePlainSchemaRepoExtImpl(anyUtilsFactory, resourceDAO,
plainSchemaDAO, entityManager);
+ return new OraclePlainSchemaRepoExtImpl(anyUtilsFactory, resourceDAO,
entityManager);
}
}
diff --git
a/core/persistence-jpa/src/main/java/org/apache/syncope/core/persistence/jpa/dao/repo/OraclePlainSchemaRepoExtImpl.java
b/core/persistence-jpa/src/main/java/org/apache/syncope/core/persistence/jpa/dao/repo/OraclePlainSchemaRepoExtImpl.java
index 20c6c53bed..dbc3b1991f 100644
---
a/core/persistence-jpa/src/main/java/org/apache/syncope/core/persistence/jpa/dao/repo/OraclePlainSchemaRepoExtImpl.java
+++
b/core/persistence-jpa/src/main/java/org/apache/syncope/core/persistence/jpa/dao/repo/OraclePlainSchemaRepoExtImpl.java
@@ -21,9 +21,8 @@ package org.apache.syncope.core.persistence.jpa.dao.repo;
import jakarta.persistence.EntityManager;
import jakarta.persistence.Query;
import java.util.stream.Collectors;
+import org.apache.commons.lang3.StringUtils;
import org.apache.syncope.core.persistence.api.dao.ExternalResourceDAO;
-import org.apache.syncope.core.persistence.api.dao.NotFoundException;
-import org.apache.syncope.core.persistence.api.dao.PlainSchemaDAO;
import org.apache.syncope.core.persistence.api.entity.AnyUtils;
import org.apache.syncope.core.persistence.api.entity.AnyUtilsFactory;
import org.apache.syncope.core.persistence.api.entity.PlainAttrValue;
@@ -31,21 +30,18 @@ import
org.apache.syncope.core.persistence.api.entity.PlainSchema;
import org.apache.syncope.core.persistence.jpa.dao.OracleJPAAnySearchDAO;
import org.apache.syncope.core.persistence.jpa.dao.SearchSupport;
import org.apache.syncope.core.persistence.jpa.entity.JPARealm;
+import org.apache.syncope.core.provisioning.api.serialization.POJOHelper;
public class OraclePlainSchemaRepoExtImpl extends AbstractPlainSchemaRepoExt {
protected static final String HAS_ATTRS_QUERY = "SELECT id FROM %TABLE%,
%JSON_TABLE% ";
- protected final PlainSchemaDAO plainSchemaDAO;
-
public OraclePlainSchemaRepoExtImpl(
final AnyUtilsFactory anyUtilsFactory,
final ExternalResourceDAO resourceDAO,
- final PlainSchemaDAO plainSchemaDAO,
final EntityManager entityManager) {
super(anyUtilsFactory, resourceDAO, entityManager);
- this.plainSchemaDAO = plainSchemaDAO;
}
@Override
@@ -60,22 +56,39 @@ public class OraclePlainSchemaRepoExtImpl extends
AbstractPlainSchemaRepoExt {
return ((Number) query.getSingleResult()).intValue() > 0;
}
+ protected boolean existsPlainAttrUniqueValue(
+ final String table,
+ final String key,
+ final PlainSchema schema,
+ final PlainAttrValue attrValue) {
+
+ String queryString = new StringBuilder("SELECT COUNT(id) FROM
").append(table).
+ append(" WHERE ").
+ append("JSON_EXISTS(plainAttrs, '$[*]?(@.schema ==
\"").append(schema.getKey()).append("\" ").
+ append("&&
@.uniqueValue.").append(OracleJPAAnySearchDAO.key(schema.getType())).append("
== $value)' ").
+ append("PASSING ?1 AS \"value\") AND id <> ?2").
+ toString();
+
+ Query query = entityManager.createNativeQuery(queryString);
+
+ query.setParameter(1, StringUtils.stripEnd(StringUtils.stripStart(
+ POJOHelper.serialize(attrValue.getValue()), "\""), "\""));
+ query.setParameter(2, key);
+
+ return ((Number) query.getSingleResult()).longValue() > 0;
+ }
+
@Override
public boolean existsPlainAttrUniqueValue(
final String realmKey,
final PlainSchema schema,
final PlainAttrValue attrValue) {
- Query query = entityManager.createNativeQuery(
- "SELECT COUNT(id) FROM "
- + JPARealm.TABLE + ","
- +
OracleJPAAnySearchDAO.from(plainSchemaDAO.findById(schema.getKey()).
- orElseThrow(() -> new NotFoundException("PlainSchema "
+ schema.getKey())))
- + " WHERE " + schema.getKey() + ".uniqueValue=?1 AND id <>
?2");
- query.setParameter(1, attrValue.getValue());
- query.setParameter(2, realmKey);
-
- return ((Number) query.getSingleResult()).intValue() > 0;
+ return existsPlainAttrUniqueValue(
+ JPARealm.TABLE,
+ realmKey,
+ schema,
+ attrValue);
}
@Override
@@ -85,15 +98,10 @@ public class OraclePlainSchemaRepoExtImpl extends
AbstractPlainSchemaRepoExt {
final PlainSchema schema,
final PlainAttrValue attrValue) {
- Query query = entityManager.createNativeQuery(
- "SELECT COUNT(id) FROM "
- + new SearchSupport(anyUtils.anyTypeKind()).table().name() +
","
- +
OracleJPAAnySearchDAO.from(plainSchemaDAO.findById(schema.getKey()).
- orElseThrow(() -> new NotFoundException("PlainSchema "
+ schema.getKey())))
- + " WHERE " + schema.getKey() + ".uniqueValue=?1 AND id <>
?2");
- query.setParameter(1, attrValue.getValue());
- query.setParameter(2, anyKey);
-
- return ((Number) query.getSingleResult()).intValue() > 0;
+ return existsPlainAttrUniqueValue(
+ new SearchSupport(anyUtils.anyTypeKind()).table().name(),
+ anyKey,
+ schema,
+ attrValue);
}
}
