You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@openmeetings.apache.org by so...@apache.org on 2022/05/16 17:57:22 UTC
[openmeetings] branch master updated: [OPENMEETINGS-2733] query construction is improved
This is an automated email from the ASF dual-hosted git repository.
solomax pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/openmeetings.git
The following commit(s) were added to refs/heads/master by this push:
new 8f499d303 [OPENMEETINGS-2733] query construction is improved
8f499d303 is described below
commit 8f499d303fc95ae8eb9374832be9103d3c9719c4
Author: Maxim Solodovnik <so...@gmail.com>
AuthorDate: Tue May 17 00:57:13 2022 +0700
[OPENMEETINGS-2733] query construction is improved
---
.../openmeetings/db/dao/IDataProviderDao.java | 3 +-
.../db/dao/IGroupAdminDataProviderDao.java | 5 +-
.../db/dao/basic/ConfigurationDao.java | 14 +-
.../openmeetings/db/dao/basic/MailMessageDao.java | 29 +--
.../db/dao/calendar/AppointmentDao.java | 3 +-
.../db/dao/calendar/OmCalendarDao.java | 3 +-
.../openmeetings/db/dao/file/BaseFileItemDao.java | 3 +-
.../apache/openmeetings/db/dao/label/LabelDao.java | 2 +-
.../openmeetings/db/dao/room/ExtraMenuDao.java | 51 ++----
.../openmeetings/db/dao/room/InvitationDao.java | 76 ++++----
.../apache/openmeetings/db/dao/room/RoomDao.java | 44 +++--
.../openmeetings/db/dao/server/LdapConfigDao.java | 11 +-
.../openmeetings/db/dao/server/OAuth2Dao.java | 12 +-
.../apache/openmeetings/db/dao/user/GroupDao.java | 43 +++--
.../openmeetings/db/dao/user/GroupUserDao.java | 27 ++-
.../db/dao/user/PrivateMessageDao.java | 3 +-
.../db/dao/user/PrivateMessageFolderDao.java | 3 +-
.../apache/openmeetings/db/dao/user/UserDao.java | 195 ++++++++++----------
.../openmeetings/db/entity/room/Invitation.java | 8 -
.../org/apache/openmeetings/db/util/DaoHelper.java | 204 ++++++++++++++-------
.../web/admin/groups/GroupUsersPanel.java | 2 +-
.../web/data/SearchableDataProvider.java | 10 +-
.../web/data/SearchableGroupAdminDataProvider.java | 4 +-
.../web/user/profile/InvitationsPanel.java | 6 +-
.../web/user/profile/UserSearchPanel.java | 10 +-
.../openmeetings/webservice/GroupWebService.java | 3 +-
26 files changed, 419 insertions(+), 355 deletions(-)
diff --git a/openmeetings-db/src/main/java/org/apache/openmeetings/db/dao/IDataProviderDao.java b/openmeetings-db/src/main/java/org/apache/openmeetings/db/dao/IDataProviderDao.java
index 228c4b369..a18d987a4 100644
--- a/openmeetings-db/src/main/java/org/apache/openmeetings/db/dao/IDataProviderDao.java
+++ b/openmeetings-db/src/main/java/org/apache/openmeetings/db/dao/IDataProviderDao.java
@@ -21,6 +21,7 @@ package org.apache.openmeetings.db.dao;
import java.util.List;
import org.apache.openmeetings.db.entity.IDataProviderEntity;
+import org.apache.wicket.extensions.markup.html.repeater.util.SortParam;
import org.springframework.transaction.annotation.Transactional;
/**
@@ -62,7 +63,7 @@ public interface IDataProviderDao<T extends IDataProviderEntity> {
* @param order - column and sort order
* @return list of instances in the range specified
*/
- List<T> get(String search, long start, long count, String order);
+ List<T> get(String search, long start, long count, SortParam<String> order);
/**
* Count the number of instances of {@link T}
diff --git a/openmeetings-db/src/main/java/org/apache/openmeetings/db/dao/IGroupAdminDataProviderDao.java b/openmeetings-db/src/main/java/org/apache/openmeetings/db/dao/IGroupAdminDataProviderDao.java
index 2eed8571f..2bc825754 100644
--- a/openmeetings-db/src/main/java/org/apache/openmeetings/db/dao/IGroupAdminDataProviderDao.java
+++ b/openmeetings-db/src/main/java/org/apache/openmeetings/db/dao/IGroupAdminDataProviderDao.java
@@ -21,6 +21,7 @@ package org.apache.openmeetings.db.dao;
import java.util.List;
import org.apache.openmeetings.db.entity.IDataProviderEntity;
+import org.apache.wicket.extensions.markup.html.repeater.util.SortParam;
public interface IGroupAdminDataProviderDao<T extends IDataProviderEntity> extends IDataProviderDao<T> {
/**
@@ -33,7 +34,7 @@ public interface IGroupAdminDataProviderDao<T extends IDataProviderEntity> exten
* @param order - column and sort order
* @return list of instances in the range specified
*/
- List<T> adminGet(String search, Long adminId, long start, long count, String order);
+ List<T> adminGet(String search, Long adminId, long start, long count, SortParam<String> order);
/**
* Get a list of instances of {@link T}
@@ -44,7 +45,7 @@ public interface IGroupAdminDataProviderDao<T extends IDataProviderEntity> exten
* @param order - column and sort order
* @return list of instances in the range specified
*/
- default List<T> adminGet(String search, long start, long count, String order) {
+ default List<T> adminGet(String search, long start, long count, SortParam<String> order) {
return get(search, start, count, order);
}
diff --git a/openmeetings-db/src/main/java/org/apache/openmeetings/db/dao/basic/ConfigurationDao.java b/openmeetings-db/src/main/java/org/apache/openmeetings/db/dao/basic/ConfigurationDao.java
index e3fcf6c81..6220fbcd7 100644
--- a/openmeetings-db/src/main/java/org/apache/openmeetings/db/dao/basic/ConfigurationDao.java
+++ b/openmeetings-db/src/main/java/org/apache/openmeetings/db/dao/basic/ConfigurationDao.java
@@ -20,8 +20,8 @@ package org.apache.openmeetings.db.dao.basic;
import static org.apache.commons.lang3.math.NumberUtils.toInt;
import static org.apache.openmeetings.db.util.DaoHelper.setLimits;
-import static org.apache.openmeetings.util.OpenmeetingsVariables.*;
import static org.apache.openmeetings.util.OmVersion.getLine;
+import static org.apache.openmeetings.util.OpenmeetingsVariables.*;
import static org.apache.wicket.csp.CSPDirectiveSrcValue.SELF;
import static org.apache.wicket.csp.CSPDirectiveSrcValue.STRICT_DYNAMIC;
@@ -34,7 +34,6 @@ import java.util.TimeZone;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
-import javax.persistence.TypedQuery;
import org.apache.openjpa.conf.OpenJPAConfiguration;
import org.apache.openjpa.event.RemoteCommitProvider;
@@ -51,6 +50,7 @@ import org.apache.openmeetings.util.crypt.CryptProvider;
import org.apache.wicket.Application;
import org.apache.wicket.csp.CSPDirective;
import org.apache.wicket.csp.CSPHeaderConfiguration;
+import org.apache.wicket.extensions.markup.html.repeater.util.SortParam;
import org.apache.wicket.protocol.http.WebApplication;
import org.apache.wicket.util.string.Strings;
import org.slf4j.Logger;
@@ -79,7 +79,7 @@ import com.github.openjson.JSONObject;
@Transactional
public class ConfigurationDao implements IDataProviderDao<Configuration> {
private static final Logger log = LoggerFactory.getLogger(ConfigurationDao.class);
- private static final String[] searchFields = {"key", "value"};
+ private static final List<String> searchFields = List.of("key", "value");
@PersistenceContext
private EntityManager em;
@@ -202,9 +202,8 @@ public class ConfigurationDao implements IDataProviderDao<Configuration> {
}
@Override
- public List<Configuration> get(String search, long start, long count, String sort) {
- return setLimits(em.createQuery(DaoHelper.getSearchQuery("Configuration", "c", search, true, false, sort, searchFields), Configuration.class)
- , start, count).getResultList();
+ public List<Configuration> get(String search, long start, long count, SortParam<String> sort) {
+ return DaoHelper.get(em, Configuration.class, true, search, searchFields, false, null, sort, start, count);
}
@Override
@@ -214,8 +213,7 @@ public class ConfigurationDao implements IDataProviderDao<Configuration> {
@Override
public long count(String search) {
- TypedQuery<Long> q = em.createQuery(DaoHelper.getSearchQuery("Configuration", "c", search, true, true, null, searchFields), Long.class);
- return q.getSingleResult();
+ return DaoHelper.count(em, Configuration.class, search, searchFields, true, null);
}
@Override
diff --git a/openmeetings-db/src/main/java/org/apache/openmeetings/db/dao/basic/MailMessageDao.java b/openmeetings-db/src/main/java/org/apache/openmeetings/db/dao/basic/MailMessageDao.java
index cf75fe9cb..b954828ab 100644
--- a/openmeetings-db/src/main/java/org/apache/openmeetings/db/dao/basic/MailMessageDao.java
+++ b/openmeetings-db/src/main/java/org/apache/openmeetings/db/dao/basic/MailMessageDao.java
@@ -26,18 +26,19 @@ import java.util.List;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
-import javax.persistence.TypedQuery;
import org.apache.openmeetings.db.dao.IDataProviderDao;
import org.apache.openmeetings.db.entity.basic.MailMessage;
import org.apache.openmeetings.db.entity.basic.MailMessage.Status;
-import org.apache.wicket.util.string.Strings;
+import org.apache.openmeetings.db.util.DaoHelper;
+import org.apache.wicket.extensions.markup.html.repeater.util.SortParam;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;
@Repository
@Transactional
public class MailMessageDao implements IDataProviderDao<MailMessage> {
+ private static final List<String> searchFields = List.of("recipients", "subject", "body", "lastError");
@PersistenceContext
private EntityManager em;
@@ -57,27 +58,9 @@ public class MailMessageDao implements IDataProviderDao<MailMessage> {
, start, count).getResultList();
}
- private <T> TypedQuery<T> getQuery(boolean isCount, String search, String order, Class<T> clazz) {
- StringBuilder sb = new StringBuilder("SELECT ");
- sb.append(isCount ? "COUNT(m)" : "m")
- .append(" FROM MailMessage m");
- if (!Strings.isEmpty(search)) {
- sb.append(" WHERE m.recipients LIKE :search OR m.subject LIKE :search OR m.body LIKE :search OR m.lastError LIKE :search");
- }
- if (!Strings.isEmpty(order)) {
- sb.append(" ORDER BY m.").append(order);
- }
- TypedQuery<T> q = em.createQuery(sb.toString(), clazz);
- if (!Strings.isEmpty(search)) {
- q.setParameter("search", String.format("%%%s%%", search));
- }
- return q;
- }
-
@Override
- public List<MailMessage> get(String search, long start, long count, String order) {
- return setLimits(getQuery(false, search, order, MailMessage.class)
- , start, count).getResultList();
+ public List<MailMessage> get(String search, long start, long count, SortParam<String> sort) {
+ return DaoHelper.get(em, MailMessage.class, false, search, searchFields, false, null, sort, start, count);
}
@Override
@@ -87,7 +70,7 @@ public class MailMessageDao implements IDataProviderDao<MailMessage> {
@Override
public long count(String search) {
- return getQuery(true, search, null, Long.class).getSingleResult();
+ return DaoHelper.count(em, MailMessage.class, search, searchFields, false, null);
}
public void resetSendingStatus(Calendar date) {
diff --git a/openmeetings-db/src/main/java/org/apache/openmeetings/db/dao/calendar/AppointmentDao.java b/openmeetings-db/src/main/java/org/apache/openmeetings/db/dao/calendar/AppointmentDao.java
index c68f4af99..0ad2a752b 100644
--- a/openmeetings-db/src/main/java/org/apache/openmeetings/db/dao/calendar/AppointmentDao.java
+++ b/openmeetings-db/src/main/java/org/apache/openmeetings/db/dao/calendar/AppointmentDao.java
@@ -45,6 +45,7 @@ import org.apache.openmeetings.db.entity.calendar.MeetingMember;
import org.apache.openmeetings.db.entity.room.Invitation.MessageType;
import org.apache.openmeetings.db.entity.room.Room;
import org.apache.openmeetings.db.manager.IInvitationManager;
+import org.apache.wicket.extensions.markup.html.repeater.util.SortParam;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
@@ -285,7 +286,7 @@ public class AppointmentDao implements IDataProviderDao<Appointment>{
}
@Override
- public List<Appointment> get(String search, long start, long count, String order) {
+ public List<Appointment> get(String search, long start, long count, SortParam<String> order) {
throw UNSUPPORTED;
}
diff --git a/openmeetings-db/src/main/java/org/apache/openmeetings/db/dao/calendar/OmCalendarDao.java b/openmeetings-db/src/main/java/org/apache/openmeetings/db/dao/calendar/OmCalendarDao.java
index 29c2802d7..48fe917b5 100644
--- a/openmeetings-db/src/main/java/org/apache/openmeetings/db/dao/calendar/OmCalendarDao.java
+++ b/openmeetings-db/src/main/java/org/apache/openmeetings/db/dao/calendar/OmCalendarDao.java
@@ -28,6 +28,7 @@ import javax.persistence.PersistenceContext;
import org.apache.openmeetings.db.dao.IDataProviderDao;
import org.apache.openmeetings.db.entity.calendar.OmCalendar;
+import org.apache.wicket.extensions.markup.html.repeater.util.SortParam;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;
@@ -117,7 +118,7 @@ public class OmCalendarDao implements IDataProviderDao<OmCalendar> {
}
@Override
- public List<OmCalendar> get(String search, long start, long count, String order) {
+ public List<OmCalendar> get(String search, long start, long count, SortParam<String> order) {
throw UNSUPPORTED;
}
diff --git a/openmeetings-db/src/main/java/org/apache/openmeetings/db/dao/file/BaseFileItemDao.java b/openmeetings-db/src/main/java/org/apache/openmeetings/db/dao/file/BaseFileItemDao.java
index 46d0148d9..9ef4ce279 100644
--- a/openmeetings-db/src/main/java/org/apache/openmeetings/db/dao/file/BaseFileItemDao.java
+++ b/openmeetings-db/src/main/java/org/apache/openmeetings/db/dao/file/BaseFileItemDao.java
@@ -34,6 +34,7 @@ import org.apache.openmeetings.db.entity.file.BaseFileItem;
import org.apache.openmeetings.db.entity.room.Room;
import org.apache.openmeetings.db.entity.user.Group;
import org.apache.openmeetings.db.entity.user.User;
+import org.apache.wicket.extensions.markup.html.repeater.util.SortParam;
import org.apache.wicket.util.string.Strings;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
@@ -139,7 +140,7 @@ public class BaseFileItemDao implements IDataProviderDao<BaseFileItem> {
}
@Override
- public List<BaseFileItem> get(String search, long start, long count, String order) {
+ public List<BaseFileItem> get(String search, long start, long count, SortParam<String> order) {
throw UNSUPPORTED;
}
diff --git a/openmeetings-db/src/main/java/org/apache/openmeetings/db/dao/label/LabelDao.java b/openmeetings-db/src/main/java/org/apache/openmeetings/db/dao/label/LabelDao.java
index 71c9f85de..11da22897 100644
--- a/openmeetings-db/src/main/java/org/apache/openmeetings/db/dao/label/LabelDao.java
+++ b/openmeetings-db/src/main/java/org/apache/openmeetings/db/dao/label/LabelDao.java
@@ -212,7 +212,7 @@ public class LabelDao implements IDataProviderDao<StringLabel>{
}
@Override
- public List<StringLabel> get(String search, long start, long count, String order) {
+ public List<StringLabel> get(String search, long start, long count, SortParam<String> order) {
throw UNSUPPORTED;
}
diff --git a/openmeetings-db/src/main/java/org/apache/openmeetings/db/dao/room/ExtraMenuDao.java b/openmeetings-db/src/main/java/org/apache/openmeetings/db/dao/room/ExtraMenuDao.java
index 39519fc01..814c2d2c1 100644
--- a/openmeetings-db/src/main/java/org/apache/openmeetings/db/dao/room/ExtraMenuDao.java
+++ b/openmeetings-db/src/main/java/org/apache/openmeetings/db/dao/room/ExtraMenuDao.java
@@ -18,36 +18,34 @@
*/
package org.apache.openmeetings.db.dao.room;
-import static org.apache.openmeetings.db.util.DaoHelper.getSearchQuery;
+import static org.apache.openmeetings.db.util.DaoHelper.getRoot;
import static org.apache.openmeetings.db.util.DaoHelper.setLimits;
import static org.apache.openmeetings.db.util.DaoHelper.single;
import java.util.List;
-import java.util.stream.Collectors;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
+import javax.persistence.criteria.CriteriaBuilder;
+import javax.persistence.criteria.CriteriaQuery;
+import javax.persistence.criteria.Predicate;
+import javax.persistence.criteria.Root;
import org.apache.openmeetings.db.dao.IGroupAdminDataProviderDao;
-import org.apache.openmeetings.db.dao.user.UserDao;
import org.apache.openmeetings.db.entity.room.ExtraMenu;
-import org.apache.openmeetings.db.entity.user.Group;
-import org.apache.openmeetings.db.entity.user.GroupUser;
-import org.springframework.beans.factory.annotation.Autowired;
+import org.apache.openmeetings.db.util.DaoHelper;
+import org.apache.wicket.extensions.markup.html.repeater.util.SortParam;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;
@Repository
@Transactional
public class ExtraMenuDao implements IGroupAdminDataProviderDao<ExtraMenu> {
- private static final String[] searchFields = {"name", "link", "description"};
+ private static final List<String> searchFields = List.of("name", "link", "description");
@PersistenceContext
private EntityManager em;
- @Autowired
- private UserDao userDao;
-
@Override
public ExtraMenu get(Long id) {
return single(em.createNamedQuery("getExtraMenuById", ExtraMenu.class)
@@ -65,11 +63,8 @@ public class ExtraMenuDao implements IGroupAdminDataProviderDao<ExtraMenu> {
}
@Override
- public List<ExtraMenu> get(String search, long start, long count, String order) {
- return setLimits(
- em.createQuery(getSearchQuery("ExtraMenu", "m", search, false, false, order, searchFields)
- , ExtraMenu.class)
- , start, count).getResultList();
+ public List<ExtraMenu> get(String search, long start, long count, SortParam<String> sort) {
+ return DaoHelper.get(em, ExtraMenu.class, false, search, searchFields, false, null, sort, start, count);
}
public List<ExtraMenu> getByGroups(List<Long> groups) {
@@ -85,8 +80,7 @@ public class ExtraMenuDao implements IGroupAdminDataProviderDao<ExtraMenu> {
@Override
public long count(String search) {
- return em.createQuery(getSearchQuery("ExtraMenu", "m", search, false, true, null, searchFields), Long.class)
- .getSingleResult();
+ return DaoHelper.count(em, ExtraMenu.class, search, searchFields, false, null);
}
@Override
@@ -105,25 +99,20 @@ public class ExtraMenuDao implements IGroupAdminDataProviderDao<ExtraMenu> {
}
@Override
- public List<ExtraMenu> adminGet(String search, Long adminId, long start, long count, String order) {
- final String additionalWhere = getGroupFilter(adminId);
- return setLimits(em.createQuery(getSearchQuery("ExtraMenu", "m", null, search, false, false, additionalWhere, order, searchFields), ExtraMenu.class)
- , start, count).getResultList();
+ public List<ExtraMenu> adminGet(String search, Long adminId, long start, long count, SortParam<String> sort) {
+ return DaoHelper.get(em, ExtraMenu.class, true, search, searchFields, false
+ , (builder, query) -> getGroupFilter(adminId, builder, query)
+ , sort, start, count);
}
@Override
public long adminCount(String search, Long adminId) {
- final String additionalWhere = getGroupFilter(adminId);
- return em.createQuery(getSearchQuery("ExtraMenu", "m", null, search, false, true, additionalWhere, null, searchFields), Long.class)
- .getSingleResult();
+ return DaoHelper.count(em, ExtraMenu.class, search, searchFields, false
+ , (builder, query) -> getGroupFilter(adminId, builder, query));
}
- private String getGroupFilter(Long adminId) {
- return userDao.get(adminId).getGroupUsers().stream()
- .filter(GroupUser::isModerator)
- .map(GroupUser::getGroup)
- .map(Group::getId)
- .map(String::valueOf)
- .collect(Collectors.joining(", ", "m.groups IN (", ")"));
+ private Predicate getGroupFilter(Long adminId, CriteriaBuilder builder, CriteriaQuery<?> query) {
+ Root<ExtraMenu> root = getRoot(query, ExtraMenu.class);
+ return builder.in(root.get("groups")).value(DaoHelper.groupAdminQuery(adminId, builder, query));
}
}
diff --git a/openmeetings-db/src/main/java/org/apache/openmeetings/db/dao/room/InvitationDao.java b/openmeetings-db/src/main/java/org/apache/openmeetings/db/dao/room/InvitationDao.java
index e48fe34a2..7c7aa4972 100644
--- a/openmeetings-db/src/main/java/org/apache/openmeetings/db/dao/room/InvitationDao.java
+++ b/openmeetings-db/src/main/java/org/apache/openmeetings/db/dao/room/InvitationDao.java
@@ -18,16 +18,8 @@
*/
package org.apache.openmeetings.db.dao.room;
-import static org.apache.openmeetings.db.entity.room.Invitation.BY_ALL;
-import static org.apache.openmeetings.db.entity.room.Invitation.BY_GROUP;
-import static org.apache.openmeetings.db.entity.room.Invitation.BY_USER;
-import static org.apache.openmeetings.db.entity.room.Invitation.SELECT_COUNT;
-import static org.apache.openmeetings.db.entity.room.Invitation.SELECT_I;
-import static org.apache.openmeetings.db.util.DaoHelper.appendSort;
-import static org.apache.openmeetings.db.util.DaoHelper.appendWhereClause;
-import static org.apache.openmeetings.db.util.DaoHelper.setLimits;
+import static org.apache.openmeetings.db.util.DaoHelper.getRoot;
import static org.apache.openmeetings.util.CalendarHelper.getZoneId;
-import static org.apache.openmeetings.util.OpenmeetingsVariables.PARAM_USER_ID;
import java.time.LocalDateTime;
import java.time.ZonedDateTime;
@@ -36,14 +28,22 @@ import java.util.TimeZone;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
+import javax.persistence.criteria.CriteriaBuilder;
+import javax.persistence.criteria.CriteriaQuery;
+import javax.persistence.criteria.Predicate;
+import javax.persistence.criteria.Root;
+import javax.persistence.criteria.Subquery;
import org.apache.openmeetings.db.dao.IDataProviderDao;
import org.apache.openmeetings.db.entity.record.Recording;
import org.apache.openmeetings.db.entity.room.Invitation;
import org.apache.openmeetings.db.entity.room.Invitation.Valid;
import org.apache.openmeetings.db.entity.room.Room;
+import org.apache.openmeetings.db.entity.user.GroupUser;
import org.apache.openmeetings.db.entity.user.User;
+import org.apache.openmeetings.db.util.DaoHelper;
import org.apache.openmeetings.util.CalendarHelper;
+import org.apache.wicket.extensions.markup.html.repeater.util.SortParam;
import org.apache.wicket.util.string.Strings;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
@@ -53,6 +53,7 @@ import org.springframework.transaction.annotation.Transactional;
@Repository
@Transactional
public class InvitationDao implements IDataProviderDao<Invitation> {
+ private static final List<String> searchFields = List.of("invitee.firstname", "invitee.lastname", "invitee.login");
private static final Logger log = LoggerFactory.getLogger(InvitationDao.class);
@PersistenceContext
@@ -70,21 +71,9 @@ public class InvitationDao implements IDataProviderDao<Invitation> {
return get(null, start, count, null);
}
- private static String getQuery(String head, String tail, String search) {
- return getQuery(head, tail, search, null);
- }
-
- private static String getQuery(String head, String tail, String search, String sort) {
- StringBuilder sb = new StringBuilder(head);
- sb.append(tail);
- appendWhereClause(sb, search, "i", "invitee.firstname", "invitee.lastname", "invitee.login");
- return appendSort(sb, "i", sort).toString();
- }
-
@Override
- public List<Invitation> get(String search, long start, long count, String order) {
- return setLimits(em.createQuery(getQuery(SELECT_I, BY_ALL, search, order), Invitation.class)
- , start, count).getResultList();
+ public List<Invitation> get(String search, long start, long count, SortParam<String> sort) {
+ return DaoHelper.get(em, Invitation.class, false, search, searchFields, true, null, sort, start, count);
}
@Override
@@ -94,29 +83,44 @@ public class InvitationDao implements IDataProviderDao<Invitation> {
@Override
public long count(String search) {
- return em.createQuery(getQuery(SELECT_COUNT, BY_ALL, search), Long.class).getSingleResult();
+ return DaoHelper.count(em, Invitation.class, search, searchFields, true, null);
}
- public List<Invitation> getGroup(String search, long start, long count, Long userId, String order) {
- return setLimits(em.createQuery(getQuery(SELECT_I, BY_GROUP, search, order), Invitation.class)
- .setParameter(PARAM_USER_ID, userId)
- , start, count).getResultList();
+ private Predicate getGroupFilter(Long userId, CriteriaBuilder builder, CriteriaQuery<?> query) {
+ Subquery<Long> subquery = query.subquery(Long.class);
+ Root<GroupUser> root = subquery.from(GroupUser.class);
+ subquery.select(root.get("user").get("id"));
+ subquery.where(builder.in(root.get("group").get("id")).value(DaoHelper.groupAdminQuery(userId, builder, subquery)));
+
+ Root<Invitation> mainRoot = getRoot(query, Invitation.class);
+ return builder.in(mainRoot.get("invitedBy").get("id")).value(subquery);
+ }
+
+ public List<Invitation> getGroup(String search, long start, long count, Long userId, SortParam<String> sort) {
+ return DaoHelper.get(em, Invitation.class, false, search, searchFields, true
+ , (builder, query) -> getGroupFilter(userId, builder, query)
+ , sort, start, count);
}
public long countGroup(String search, Long userId) {
- return em.createQuery(getQuery(SELECT_COUNT, BY_GROUP, search), Long.class)
- .setParameter(PARAM_USER_ID, userId).getSingleResult();
+ return DaoHelper.count(em, Invitation.class, search, searchFields, true
+ , (builder, query) -> getGroupFilter(userId, builder, query));
+ }
+
+ private Predicate getUserFilter(Long userId, CriteriaBuilder builder, CriteriaQuery<?> query) {
+ Root<Invitation> root = getRoot(query, Invitation.class);
+ return builder.equal(root.get("invitedBy").get("id"), userId);
}
- public List<Invitation> getUser(String search, long start, long count, Long userId, String order) {
- return setLimits(em.createQuery(getQuery(SELECT_I, BY_USER, search, order), Invitation.class)
- .setParameter(PARAM_USER_ID, userId)
- , start, count).getResultList();
+ public List<Invitation> getUser(String search, long start, long count, Long userId, SortParam<String> sort) {
+ return DaoHelper.get(em, Invitation.class, false, search, searchFields, true
+ , (builder, query) -> getUserFilter(userId, builder, query)
+ , sort, start, count);
}
public long countUser(String search, Long userId) {
- return em.createQuery(getQuery(SELECT_COUNT, BY_USER, search), Long.class)
- .setParameter(PARAM_USER_ID, userId).getSingleResult();
+ return DaoHelper.count(em, Invitation.class, search, searchFields, true
+ , (builder, query) -> getUserFilter(userId, builder, query));
}
public Invitation update(Invitation invitation) {
diff --git a/openmeetings-db/src/main/java/org/apache/openmeetings/db/dao/room/RoomDao.java b/openmeetings-db/src/main/java/org/apache/openmeetings/db/dao/room/RoomDao.java
index 9231dac7b..9eed7c2fb 100644
--- a/openmeetings-db/src/main/java/org/apache/openmeetings/db/dao/room/RoomDao.java
+++ b/openmeetings-db/src/main/java/org/apache/openmeetings/db/dao/room/RoomDao.java
@@ -19,6 +19,7 @@
package org.apache.openmeetings.db.dao.room;
import static org.apache.openmeetings.db.util.DaoHelper.fillLazy;
+import static org.apache.openmeetings.db.util.DaoHelper.getRoot;
import static org.apache.openmeetings.db.util.DaoHelper.setLimits;
import static org.apache.openmeetings.db.util.DaoHelper.single;
import static org.apache.openmeetings.db.util.TimezoneUtil.getTimeZone;
@@ -37,6 +38,10 @@ import java.util.TimeZone;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.TypedQuery;
+import javax.persistence.criteria.CriteriaBuilder;
+import javax.persistence.criteria.CriteriaQuery;
+import javax.persistence.criteria.Predicate;
+import javax.persistence.criteria.Root;
import org.apache.openmeetings.db.dao.IGroupAdminDataProviderDao;
import org.apache.openmeetings.db.dao.basic.ConfigurationDao;
@@ -49,6 +54,7 @@ import org.apache.openmeetings.db.entity.room.RoomFile;
import org.apache.openmeetings.db.entity.room.RoomGroup;
import org.apache.openmeetings.db.manager.ISipManager;
import org.apache.openmeetings.db.util.DaoHelper;
+import org.apache.wicket.extensions.markup.html.repeater.util.SortParam;
import org.apache.wicket.util.string.Strings;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
@@ -60,7 +66,7 @@ import org.springframework.transaction.annotation.Transactional;
@Transactional
public class RoomDao implements IGroupAdminDataProviderDao<Room> {
private static final Logger log = LoggerFactory.getLogger(RoomDao.class);
- private static final String[] searchFields = {"name", "comment"};
+ private static final List<String> searchFields = List.of("name", "comment");
public static final String GRP_MODERATORS = "roomModerators";
public static final String GRP_GROUPS = "roomGroups";
public static final String GRP_FILES = "roomFiles";
@@ -117,37 +123,41 @@ public class RoomDao implements IGroupAdminDataProviderDao<Room> {
}
@Override
- public List<Room> get(String search, long start, long count, String sort) {
- return setLimits(em.createQuery(DaoHelper.getSearchQuery("Room", "r", search, true, false, sort, searchFields), Room.class)
- , start, count).getResultList();
+ public List<Room> get(String search, long start, long count, SortParam<String> sort) {
+ return DaoHelper.get(em, Room.class, false, search, searchFields, true, null, sort, start, count);
+ }
+
+ private Predicate getAdminFilter(Long adminId, CriteriaBuilder builder, CriteriaQuery<?> query) {
+ Root<RoomGroup> root = getRoot(query, RoomGroup.class);
+ return builder.in(root.get("group").get("id")).value(DaoHelper.groupAdminQuery(adminId, builder, query));
}
@Override
- public List<Room> adminGet(String search, Long adminId, long start, long count, String order) {
- return setLimits(em.createQuery(DaoHelper.getSearchQuery("RoomGroup rg, IN(rg.room)", "r", null, search, true, true, false
- , "rg.group.id IN (SELECT gu1.group.id FROM GroupUser gu1 WHERE gu1.moderator = true AND gu1.user.id = :adminId)", order, searchFields), Room.class)
- .setParameter("adminId", adminId)
- , start, count).getResultList();
+ public List<Room> adminGet(String search, Long adminId, long start, long count, SortParam<String> sort) {
+ return DaoHelper.get(em, RoomGroup.class, Room.class
+ , (builder, root) -> root.get("room")
+ , true, search, searchFields, false
+ , (b, q) -> getAdminFilter(adminId, b, q)
+ , sort, start, count);
}
@Override
public long count() {
- TypedQuery<Long> q = em.createNamedQuery("countRooms", Long.class);
- return q.getSingleResult();
+ return em.createNamedQuery("countRooms", Long.class)
+ .getSingleResult();
}
@Override
public long count(String search) {
- TypedQuery<Long> q = em.createQuery(DaoHelper.getSearchQuery("Room", "r", search, true, true, null, searchFields), Long.class);
- return q.getSingleResult();
+ return DaoHelper.count(em, Room.class, search, searchFields, true, null);
}
@Override
public long adminCount(String search, Long adminId) {
- TypedQuery<Long> q = em.createQuery(DaoHelper.getSearchQuery("RoomGroup rg, IN(rg.room)", "r", null, search, true, true, true
- , "rg.group.id IN (SELECT gu1.group.id FROM GroupUser gu1 WHERE gu1.moderator = true AND gu1.user.id = :adminId)", null, searchFields), Long.class);
- q.setParameter("adminId", adminId);
- return q.getSingleResult();
+ return DaoHelper.count(em, RoomGroup.class
+ , (builder, root) -> builder.countDistinct(root.get("room"))
+ , search, searchFields, false
+ , (b, q) -> getAdminFilter(adminId, b, q));
}
public List<Room> getPublicRooms() {
diff --git a/openmeetings-db/src/main/java/org/apache/openmeetings/db/dao/server/LdapConfigDao.java b/openmeetings-db/src/main/java/org/apache/openmeetings/db/dao/server/LdapConfigDao.java
index efc5a04c8..91cb12e1e 100644
--- a/openmeetings-db/src/main/java/org/apache/openmeetings/db/dao/server/LdapConfigDao.java
+++ b/openmeetings-db/src/main/java/org/apache/openmeetings/db/dao/server/LdapConfigDao.java
@@ -32,6 +32,7 @@ import org.apache.openmeetings.db.dao.IDataProviderDao;
import org.apache.openmeetings.db.dao.user.UserDao;
import org.apache.openmeetings.db.entity.server.LdapConfig;
import org.apache.openmeetings.db.util.DaoHelper;
+import org.apache.wicket.extensions.markup.html.repeater.util.SortParam;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
@@ -49,7 +50,7 @@ import org.springframework.transaction.annotation.Transactional;
@Transactional
public class LdapConfigDao implements IDataProviderDao<LdapConfig> {
private static final Logger log = LoggerFactory.getLogger(LdapConfigDao.class);
- private static final String[] searchFields = {"name", "configFileName", "domain", "comment"};
+ private static final List<String> searchFields = List.of("name", "configFileName", "domain", "comment");
@PersistenceContext
private EntityManager em;
@@ -94,9 +95,8 @@ public class LdapConfigDao implements IDataProviderDao<LdapConfig> {
}
@Override
- public List<LdapConfig> get(String search, long start, long count, String sort) {
- return setLimits(em.createQuery(DaoHelper.getSearchQuery("LdapConfig", "lc", search, true, false, sort, searchFields), LdapConfig.class)
- , start, count).getResultList();
+ public List<LdapConfig> get(String search, long start, long count, SortParam<String> sort) {
+ return DaoHelper.get(em, LdapConfig.class, false, search, searchFields, true, null, sort, start, count);
}
@Override
@@ -114,8 +114,7 @@ public class LdapConfigDao implements IDataProviderDao<LdapConfig> {
@Override
public long count(String search) {
- TypedQuery<Long> q = em.createQuery(DaoHelper.getSearchQuery("LdapConfig", "lc", search, true, true, null, searchFields), Long.class);
- return q.getSingleResult();
+ return DaoHelper.count(em, LdapConfig.class, search, searchFields, true, null);
}
@Override
diff --git a/openmeetings-db/src/main/java/org/apache/openmeetings/db/dao/server/OAuth2Dao.java b/openmeetings-db/src/main/java/org/apache/openmeetings/db/dao/server/OAuth2Dao.java
index 2899c82e5..c22823bb4 100644
--- a/openmeetings-db/src/main/java/org/apache/openmeetings/db/dao/server/OAuth2Dao.java
+++ b/openmeetings-db/src/main/java/org/apache/openmeetings/db/dao/server/OAuth2Dao.java
@@ -30,8 +30,10 @@ import javax.persistence.TypedQuery;
import org.apache.openmeetings.db.dao.IDataProviderDao;
import org.apache.openmeetings.db.dao.basic.ConfigurationDao;
+import org.apache.openmeetings.db.entity.server.LdapConfig;
import org.apache.openmeetings.db.entity.server.OAuthServer;
import org.apache.openmeetings.db.util.DaoHelper;
+import org.apache.wicket.extensions.markup.html.repeater.util.SortParam;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;
@@ -39,7 +41,7 @@ import org.springframework.transaction.annotation.Transactional;
@Repository
@Transactional
public class OAuth2Dao implements IDataProviderDao<OAuthServer> {
- private static final String[] searchFields = {"name"};
+ private static final List<String> searchFields = List.of("name");
@PersistenceContext
private EntityManager em;
@Autowired
@@ -67,9 +69,8 @@ public class OAuth2Dao implements IDataProviderDao<OAuthServer> {
}
@Override
- public List<OAuthServer> get(String search, long start, long count, String order) {
- return setLimits(em.createQuery(DaoHelper.getSearchQuery("OAuthServer", "s", search, true, false, null, searchFields), OAuthServer.class)
- , start, count).getResultList();
+ public List<OAuthServer> get(String search, long start, long count, SortParam<String> sort) {
+ return DaoHelper.get(em, OAuthServer.class, false, search, searchFields, true, null, sort, start, count);
}
@Override
@@ -80,8 +81,7 @@ public class OAuth2Dao implements IDataProviderDao<OAuthServer> {
@Override
public long count(String search) {
- TypedQuery<Long> q = em.createQuery(DaoHelper.getSearchQuery("OAuthServer", "s", search, true, true, null, searchFields), Long.class);
- return q.getSingleResult();
+ return DaoHelper.count(em, LdapConfig.class, search, searchFields, true, null);
}
@Override
diff --git a/openmeetings-db/src/main/java/org/apache/openmeetings/db/dao/user/GroupDao.java b/openmeetings-db/src/main/java/org/apache/openmeetings/db/dao/user/GroupDao.java
index 1fbfae693..6acb8fd8f 100644
--- a/openmeetings-db/src/main/java/org/apache/openmeetings/db/dao/user/GroupDao.java
+++ b/openmeetings-db/src/main/java/org/apache/openmeetings/db/dao/user/GroupDao.java
@@ -18,6 +18,7 @@
*/
package org.apache.openmeetings.db.dao.user;
+import static org.apache.openmeetings.db.util.DaoHelper.getRoot;
import static org.apache.openmeetings.db.util.DaoHelper.setLimits;
import java.util.Collection;
@@ -25,17 +26,23 @@ import java.util.List;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
+import javax.persistence.criteria.CriteriaBuilder;
+import javax.persistence.criteria.CriteriaQuery;
+import javax.persistence.criteria.Predicate;
+import javax.persistence.criteria.Root;
import org.apache.openmeetings.db.dao.IGroupAdminDataProviderDao;
import org.apache.openmeetings.db.entity.user.Group;
+import org.apache.openmeetings.db.entity.user.GroupUser;
import org.apache.openmeetings.db.util.DaoHelper;
+import org.apache.wicket.extensions.markup.html.repeater.util.SortParam;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;
@Repository
@Transactional
public class GroupDao implements IGroupAdminDataProviderDao<Group> {
- private static final String[] searchFields = {"name"};
+ private static final List<String> searchFields = List.of("name");
@PersistenceContext
private EntityManager em;
@@ -67,17 +74,24 @@ public class GroupDao implements IGroupAdminDataProviderDao<Group> {
}
@Override
- public List<Group> get(String search, long start, long count, String sort) {
- return setLimits(em.createQuery(DaoHelper.getSearchQuery("Group", "g", search, true, false, sort, searchFields), Group.class)
- , start, count).getResultList();
+ public List<Group> get(String search, long start, long count, SortParam<String> sort) {
+ return DaoHelper.get(em, Group.class, false, search, searchFields, true
+ , null
+ , sort, start, count);
+ }
+
+ private Predicate getAdminFilter(Long adminId, CriteriaBuilder builder, CriteriaQuery<?> query) {
+ Root<GroupUser> root = getRoot(query, GroupUser.class);
+ return builder.and(builder.equal(root.get("user").get("id"), adminId), builder.isTrue(root.get("moderator")));
}
@Override
- public List<Group> adminGet(String search, Long adminId, long start, long count, String order) {
- return setLimits(em.createQuery(DaoHelper.getSearchQuery("GroupUser gu, IN(gu.group)", "g", null, search, true, true, false
- , "gu.user.id = :adminId AND gu.moderator = true", order, searchFields), Group.class)
- .setParameter("adminId", adminId)
- , start, count).getResultList();
+ public List<Group> adminGet(String search, Long adminId, long start, long count, SortParam<String> sort) {
+ return DaoHelper.get(em, GroupUser.class, Group.class
+ , (builder, root) -> root.get("group")
+ , true, search, searchFields, true
+ , (b, q) -> getAdminFilter(adminId, b, q)
+ , sort, start, count);
}
@Override
@@ -87,16 +101,15 @@ public class GroupDao implements IGroupAdminDataProviderDao<Group> {
@Override
public long count(String search) {
- return em.createQuery(DaoHelper.getSearchQuery("Group", "o", search, true, true, null, searchFields), Long.class)
- .getSingleResult();
+ return DaoHelper.count(em, Group.class, search, searchFields, true, null);
}
@Override
public long adminCount(String search, Long adminId) {
- return em.createQuery(DaoHelper.getSearchQuery("GroupUser gu, IN(gu.group)", "g", null, search, true, true, true
- , "gu.user.id = :adminId AND gu.moderator = true", null, searchFields), Long.class)
- .setParameter("adminId", adminId)
- .getSingleResult();
+ return DaoHelper.count(em, GroupUser.class
+ , (builder, root) -> builder.countDistinct(root.get("group"))
+ , search, searchFields, false
+ , (b, q) -> getAdminFilter(adminId, b, q.distinct(true)));
}
public List<Group> get(Collection<Long> ids) {
diff --git a/openmeetings-db/src/main/java/org/apache/openmeetings/db/dao/user/GroupUserDao.java b/openmeetings-db/src/main/java/org/apache/openmeetings/db/dao/user/GroupUserDao.java
index 542b666fd..d72e4fc4e 100644
--- a/openmeetings-db/src/main/java/org/apache/openmeetings/db/dao/user/GroupUserDao.java
+++ b/openmeetings-db/src/main/java/org/apache/openmeetings/db/dao/user/GroupUserDao.java
@@ -19,6 +19,7 @@
package org.apache.openmeetings.db.dao.user;
import static org.apache.openmeetings.db.util.DaoHelper.UNSUPPORTED;
+import static org.apache.openmeetings.db.util.DaoHelper.getRoot;
import static org.apache.openmeetings.db.util.DaoHelper.setLimits;
import static org.apache.openmeetings.db.util.DaoHelper.single;
import static org.apache.openmeetings.util.OpenmeetingsVariables.PARAM_USER_ID;
@@ -28,18 +29,23 @@ import java.util.List;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
+import javax.persistence.criteria.CriteriaBuilder;
+import javax.persistence.criteria.CriteriaQuery;
+import javax.persistence.criteria.Predicate;
+import javax.persistence.criteria.Root;
import org.apache.openmeetings.db.dao.IDataProviderDao;
import org.apache.openmeetings.db.entity.user.GroupUser;
import org.apache.openmeetings.db.entity.user.User;
import org.apache.openmeetings.db.util.DaoHelper;
+import org.apache.wicket.extensions.markup.html.repeater.util.SortParam;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;
@Repository
@Transactional
public class GroupUserDao implements IDataProviderDao<GroupUser> {
- private static final String[] searchFields = {"user.lastname", "user.firstname", "user.login", "user.address.email"};
+ private static final List<String> searchFields = List.of("user.lastname", "user.firstname", "user.login", "user.address.email");
private static final String PARAM_GROUPID = "groupId";
@PersistenceContext
private EntityManager em;
@@ -57,15 +63,19 @@ public class GroupUserDao implements IDataProviderDao<GroupUser> {
}
@Override
- public List<GroupUser> get(String search, long start, long count, String sort) {
+ public List<GroupUser> get(String search, long start, long count, SortParam<String> sort) {
throw UNSUPPORTED;
}
- public List<GroupUser> get(long groupId, String search, long start, long count, String sort) {
- return setLimits(
- em.createQuery(DaoHelper.getSearchQuery(GroupUser.class.getSimpleName(), "ou", null, search, false, false, "ou.group.id = :groupId", sort, searchFields), GroupUser.class)
- .setParameter(PARAM_GROUPID, groupId)
- , start, count).getResultList();
+ private Predicate getGroupFilter(Long groupId, CriteriaBuilder builder, CriteriaQuery<?> query) {
+ Root<GroupUser> root = getRoot(query, GroupUser.class);
+ return builder.equal(root.get("group").get("id"), groupId);
+ }
+
+ public List<GroupUser> get(long groupId, String search, long start, long count, SortParam<String> sort) {
+ return DaoHelper.get(em, GroupUser.class, false, search, searchFields, false
+ , (builder, query) -> getGroupFilter(groupId, builder, query)
+ , sort, start, count);
}
public List<GroupUser> get(long groupId, long start, long count) {
@@ -95,8 +105,7 @@ public class GroupUserDao implements IDataProviderDao<GroupUser> {
@Override
public long count(String search) {
- return em.createQuery(DaoHelper.getSearchQuery(GroupUser.class.getSimpleName(), "ou", search, false, true, null, searchFields), Long.class)
- .getSingleResult();
+ return DaoHelper.count(em, GroupUser.class, search, searchFields, false, null);
}
public long count(long groupId) {
diff --git a/openmeetings-db/src/main/java/org/apache/openmeetings/db/dao/user/PrivateMessageDao.java b/openmeetings-db/src/main/java/org/apache/openmeetings/db/dao/user/PrivateMessageDao.java
index 12eae4c51..51a3e3d60 100644
--- a/openmeetings-db/src/main/java/org/apache/openmeetings/db/dao/user/PrivateMessageDao.java
+++ b/openmeetings-db/src/main/java/org/apache/openmeetings/db/dao/user/PrivateMessageDao.java
@@ -35,6 +35,7 @@ import javax.persistence.TypedQuery;
import org.apache.openmeetings.db.dao.IDataProviderDao;
import org.apache.openmeetings.db.entity.user.PrivateMessage;
import org.apache.openmeetings.db.entity.user.User;
+import org.apache.wicket.extensions.markup.html.repeater.util.SortParam;
import org.apache.wicket.util.string.Strings;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
@@ -168,7 +169,7 @@ public class PrivateMessageDao implements IDataProviderDao<PrivateMessage> {
}
@Override
- public List<PrivateMessage> get(String search, long start, long count, String order) {
+ public List<PrivateMessage> get(String search, long start, long count, SortParam<String> order) {
throw UNSUPPORTED;
}
diff --git a/openmeetings-db/src/main/java/org/apache/openmeetings/db/dao/user/PrivateMessageFolderDao.java b/openmeetings-db/src/main/java/org/apache/openmeetings/db/dao/user/PrivateMessageFolderDao.java
index 0a6702345..08fca325a 100644
--- a/openmeetings-db/src/main/java/org/apache/openmeetings/db/dao/user/PrivateMessageFolderDao.java
+++ b/openmeetings-db/src/main/java/org/apache/openmeetings/db/dao/user/PrivateMessageFolderDao.java
@@ -29,6 +29,7 @@ import javax.persistence.PersistenceContext;
import org.apache.openmeetings.db.dao.IDataProviderDao;
import org.apache.openmeetings.db.entity.user.PrivateMessageFolder;
+import org.apache.wicket.extensions.markup.html.repeater.util.SortParam;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Repository;
@@ -97,7 +98,7 @@ public class PrivateMessageFolderDao implements IDataProviderDao<PrivateMessageF
}
@Override
- public List<PrivateMessageFolder> get(String search, long start, long count, String order) {
+ public List<PrivateMessageFolder> get(String search, long start, long count, SortParam<String> order) {
throw UNSUPPORTED;
}
diff --git a/openmeetings-db/src/main/java/org/apache/openmeetings/db/dao/user/UserDao.java b/openmeetings-db/src/main/java/org/apache/openmeetings/db/dao/user/UserDao.java
index b8dc754ed..ed2e10abd 100644
--- a/openmeetings-db/src/main/java/org/apache/openmeetings/db/dao/user/UserDao.java
+++ b/openmeetings-db/src/main/java/org/apache/openmeetings/db/dao/user/UserDao.java
@@ -20,6 +20,7 @@ package org.apache.openmeetings.db.dao.user;
import static java.util.UUID.randomUUID;
import static org.apache.openmeetings.db.util.DaoHelper.fillLazy;
+import static org.apache.openmeetings.db.util.DaoHelper.getRoot;
import static org.apache.openmeetings.db.util.DaoHelper.getStringParam;
import static org.apache.openmeetings.db.util.DaoHelper.setLimits;
import static org.apache.openmeetings.db.util.DaoHelper.single;
@@ -35,22 +36,28 @@ import java.time.LocalDate;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Date;
-import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Locale;
-import java.util.Map;
import java.util.Set;
+import java.util.stream.Collectors;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.TypedQuery;
+import javax.persistence.criteria.CriteriaBuilder;
+import javax.persistence.criteria.CriteriaQuery;
+import javax.persistence.criteria.JoinType;
+import javax.persistence.criteria.Predicate;
+import javax.persistence.criteria.Root;
+import javax.persistence.criteria.Subquery;
import org.apache.commons.lang3.StringUtils;
import org.apache.openmeetings.db.dao.IGroupAdminDataProviderDao;
import org.apache.openmeetings.db.dao.label.LabelDao;
import org.apache.openmeetings.db.entity.user.Address;
import org.apache.openmeetings.db.entity.user.AsteriskSipUser;
+import org.apache.openmeetings.db.entity.user.GroupUser;
import org.apache.openmeetings.db.entity.user.User;
import org.apache.openmeetings.db.entity.user.User.Right;
import org.apache.openmeetings.db.entity.user.User.Salutation;
@@ -61,6 +68,7 @@ import org.apache.openmeetings.util.OmException;
import org.apache.openmeetings.util.OmFileHelper;
import org.apache.openmeetings.util.crypt.CryptProvider;
import org.apache.openmeetings.util.crypt.ICrypt;
+import org.apache.wicket.extensions.markup.html.repeater.util.SortParam;
import org.apache.wicket.util.string.Strings;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
@@ -78,7 +86,8 @@ import org.springframework.transaction.annotation.Transactional;
public class UserDao implements IGroupAdminDataProviderDao<User> {
private static final Logger log = LoggerFactory.getLogger(UserDao.class);
private static final String PARAM_EMAIL = "email";
- private static final String[] searchFields = {"lastname", "firstname", "login", "address.email", "address.town"};
+ private static final List<String> searchFields = List.of("lastname", "firstname", "login", "address.email", "address.town");
+ private static final List<String> guSearchFields = searchFields.stream().map(f -> "user." + f).collect(Collectors.toList());
public static final String FETCH_GROUP_GROUP = "groupUsers";
public static final String FETCH_GROUP_BACKUP = "backupexport";
@@ -123,81 +132,102 @@ public class UserDao implements IGroupAdminDataProviderDao<User> {
, first, count).getResultList();
}
- private static String getAdditionalJoin(boolean filterContacts) {
- return filterContacts ? "LEFT JOIN u.groupUsers ou" : null;
+ private Predicate getContactsFilter(CriteriaBuilder builder, CriteriaQuery<?> query) {
+ Root<User> root = getRoot(query, User.class);
+ return builder.notEqual(root.get("type"), Type.CONTACT);
}
- private static String getAdditionalWhere(boolean excludeContacts, Map<String, Object> params) {
- if (excludeContacts) {
- params.put("contact", Type.CONTACT);
- return "u.type <> :contact";
- }
- return null;
+ private Predicate getOwnerContactsFilter(Long ownerId, CriteriaBuilder builder, CriteriaQuery<?> query) {
+ Root<User> root = getRoot(query, User.class);
+ root.join("groupUsers", JoinType.LEFT);
+
+ Subquery<Long> subquery = query.subquery(Long.class);
+ Root<GroupUser> subRoot = subquery.from(GroupUser.class);
+ subquery.select(subRoot.get("group").get("id"));
+ subquery.where(builder.equal(subRoot.get("user").get("id"), ownerId));
+ return builder.or(
+ builder.and(builder.notEqual(root.get("type"), Type.CONTACT), root.get("groupUsers").get("group").get("id").in(subquery))
+ , builder.and(builder.equal(root.get("type"), Type.CONTACT), builder.equal(root.get("ownerId"), ownerId))
+ );
}
- private static String getAdditionalWhere(boolean filterContacts, Long ownerId, Map<String, Object> params) {
+ private List<User> get(String search, Long start, Long count, SortParam<String> sort, boolean filterContacts, Long currentUserId, boolean filterDeleted) {
if (filterContacts) {
- params.put("ownerId", ownerId);
- params.put("contact", Type.CONTACT);
- return "((u.type <> :contact AND ou.group.id IN (SELECT ou.group.id FROM GroupUser ou WHERE ou.user.id = :ownerId)) "
- + "OR (u.type = :contact AND u.ownerId = :ownerId))";
+ return DaoHelper.get(em, User.class
+ , true, search, searchFields, true
+ , (b, q) -> getOwnerContactsFilter(currentUserId, b, q)
+ , sort, start, count);
+ } else {
+ return DaoHelper.get(em, User.class, false, search, searchFields, filterDeleted
+ , null, sort, start, count);
}
- return null;
}
- private static void setAdditionalParams(TypedQuery<?> q, Map<String, Object> params) {
- for (Map.Entry<String, Object> me: params.entrySet()) {
- q.setParameter(me.getKey(), me.getValue());
- }
+ // This is AdminDao method
+ public List<User> get(String search, boolean excludeContacts, long start, long count) {
+ return DaoHelper.get(em, User.class, false, search, searchFields, true
+ , excludeContacts ? (b, q) -> getContactsFilter(b, q) : null
+ , null, start, count);
}
- private List<User> get(String search, Long start, Long count, String order, boolean filterContacts, Long currentUserId, boolean filterDeleted) {
- Map<String, Object> params = new HashMap<>();
- TypedQuery<User> q = em.createQuery(DaoHelper.getSearchQuery("User", "u", getAdditionalJoin(filterContacts), search, true, filterDeleted, false
- , getAdditionalWhere(filterContacts, currentUserId, params), order, searchFields), User.class);
- setAdditionalParams(setLimits(q, start, count), params);
- return q.getResultList();
+ public List<User> get(String search, long start, long count, SortParam<String> sort, boolean filterContacts, Long currentUserId) {
+ return get(search, start, count, sort, filterContacts, currentUserId, true);
}
- // This is AdminDao method
- public List<User> get(String search, boolean excludeContacts, long first, long count) {
- Map<String, Object> params = new HashMap<>();
- TypedQuery<User> q = em.createQuery(DaoHelper.getSearchQuery("User", "u", null, search, true, true, false
- , getAdditionalWhere(excludeContacts, params), null, searchFields), User.class);
- setAdditionalParams(setLimits(q, first, count), params);
- return q.getResultList();
+ @Override
+ public List<User> adminGet(String search, long start, long count, SortParam<String> sort) {
+ return get(search, start, count, sort, false, null, false);
}
- public List<User> get(String search, long start, long count, String sort, boolean filterContacts, Long currentUserId) {
- return get(search, start, count, sort, filterContacts, currentUserId, true);
+ private Predicate getAdminFilter(Long adminId, CriteriaBuilder builder, CriteriaQuery<?> query) {
+ Root<GroupUser> root = getRoot(query, GroupUser.class);
+ return builder.in(root.get("group").get("id")).value(DaoHelper.groupAdminQuery(adminId, builder, query));
}
@Override
- public List<User> adminGet(String search, long start, long count, String order) {
- return get(search, start, count, order, false, null, false);
+ public List<User> adminGet(String search, Long adminId, long start, long count, SortParam<String> sort) {
+ return DaoHelper.get(em, GroupUser.class, User.class
+ , (builder, root) -> root.get("user")
+ , true, search, guSearchFields, false
+ , (b, q) -> getAdminFilter(adminId, b, q)
+ , sort, start, count);
+ }
+
+ private Predicate getProfileFilter(Long userId, String userOffers, String userSearches, CriteriaBuilder builder, CriteriaQuery<?> query) {
+ Root<User> root = getRoot(query, User.class);
+ Predicate result = getOwnerContactsFilter(userId, builder, query);
+ if (!Strings.isEmpty(userOffers)) {
+ result = builder.and(result, DaoHelper.like("userOffers", getStringParam(userOffers), builder, root));
+ }
+ if (!Strings.isEmpty(userSearches)) {
+ result = builder.and(result, DaoHelper.like("userSearches", getStringParam(userSearches), builder, root));
+ }
+ return result;
}
- @Override
- public List<User> adminGet(String search, Long adminId, long start, long count, String order) {
- TypedQuery<User> q = em.createQuery(DaoHelper.getSearchQuery("GroupUser gu, IN(gu.user)", "u", null, search, true, false, false
- , "gu.group.id IN (SELECT gu1.group.id FROM GroupUser gu1 WHERE gu1.moderator = true AND gu1.user.id = :adminId)", order, searchFields), User.class);
- q.setParameter("adminId", adminId);
- return setLimits(q, start, count).getResultList();
+ public List<User> searchUserProfile(Long userId, String search, String userOffers, String userSearches, SortParam<String> sort, long start, long count) {
+ return DaoHelper.get(em, User.class
+ , true, search, searchFields, true
+ , (b, q) -> getProfileFilter(userId, userOffers, userSearches, b, q)
+ , sort, start, count);
}
private long count(String search, boolean filterContacts, Long currentUserId, boolean filterDeleted) {
- Map<String, Object> params = new HashMap<>();
- TypedQuery<Long> q = em.createQuery(DaoHelper.getSearchQuery("User", "u", getAdditionalJoin(filterContacts), search, true, filterDeleted, true
- , getAdditionalWhere(filterContacts, currentUserId, params), null, searchFields), Long.class);
- setAdditionalParams(q, params);
- return q.getSingleResult();
+ if (filterContacts) {
+ return DaoHelper.count(em, User.class
+ , (builder, root) -> builder.countDistinct(root)
+ , search, searchFields, filterDeleted
+ , (b, q) -> getOwnerContactsFilter(currentUserId, b, q));
+ } else {
+ return DaoHelper.count(em, User.class, search, searchFields, filterDeleted
+ , null);
+ }
}
@Override
public long count() {
- // get all users
- TypedQuery<Long> q = em.createNamedQuery("countNondeletedUsers", Long.class);
- return q.getSingleResult();
+ return em.createNamedQuery("countNondeletedUsers", Long.class)
+ .getSingleResult();
}
@Override
@@ -220,10 +250,17 @@ public class UserDao implements IGroupAdminDataProviderDao<User> {
@Override
public long adminCount(String search, Long adminId) {
- TypedQuery<Long> q = em.createQuery(DaoHelper.getSearchQuery("GroupUser gu, IN(gu.user)", "u", null, search, true, false, true
- , "gu.group.id IN (SELECT gu1.group.id FROM GroupUser gu1 WHERE gu1.moderator = true AND gu1.user.id = :adminId)", null, searchFields), Long.class);
- q.setParameter("adminId", adminId);
- return q.getSingleResult();
+ return DaoHelper.count(em, GroupUser.class
+ , (builder, root) -> builder.countDistinct(root.get("user"))
+ , search, guSearchFields, false
+ , (b, q) -> getAdminFilter(adminId, b, q));
+ }
+
+ public Long searchCountUserProfile(Long userId, String search, String userOffers, String userSearches) {
+ return DaoHelper.count(em, User.class
+ , (builder, root) -> builder.countDistinct(root)
+ , search, searchFields, true
+ , (b, q) -> getProfileFilter(userId, userOffers, userSearches, b, q));
}
@Override
@@ -524,48 +561,6 @@ public class UserDao implements IGroupAdminDataProviderDao<User> {
, FETCH_GROUP_GROUP));
}
- private <T> TypedQuery<T> getUserProfileQuery(Class<T> clazz, Long userId, String text, String offers, String search, String orderBy, boolean asc) {
- Map<String, Object> params = new HashMap<>();
- boolean filterContacts = true;
- boolean count = clazz.isAssignableFrom(Long.class);
-
- StringBuilder sb = new StringBuilder("SELECT ");
- sb.append(count ? "COUNT(" : "").append("DISTINCT u").append(count ? ") " : " ")
- .append("FROM User u ").append(getAdditionalJoin(filterContacts)).append(" WHERE u.deleted = false AND ")
- .append(getAdditionalWhere(filterContacts, userId, params));
- if (!Strings.isEmpty(offers)) {
- sb.append(" AND (LOWER(u.userOffers) LIKE :userOffers) ");
- params.put("userOffers", getStringParam(offers));
- }
- if (!Strings.isEmpty(search)) {
- sb.append(" AND (LOWER(u.userSearchs) LIKE :userSearchs) ");
- params.put("userSearchs", getStringParam(search));
- }
- if (!Strings.isEmpty(text)) {
- sb.append(" AND (LOWER(u.login) LIKE :search ")
- .append("OR LOWER(u.firstname) LIKE :search ")
- .append("OR LOWER(u.lastname) LIKE :search ")
- .append("OR LOWER(u.address.email) LIKE :search ")
- .append("OR LOWER(u.address.town) LIKE :search " + ") ");
- params.put("search", getStringParam(text));
- }
- if (!count && !Strings.isEmpty(orderBy)) {
- sb.append(" ORDER BY ").append(orderBy).append(asc ? " ASC" : " DESC");
- }
- TypedQuery<T> query = em.createQuery(sb.toString(), clazz);
- setAdditionalParams(query, params);
- return query;
- }
-
- public List<User> searchUserProfile(Long userId, String text, String offers, String search, String orderBy, long start, long max, boolean asc) {
- return setLimits(getUserProfileQuery(User.class, userId, text, offers, search, orderBy, asc)
- , start, max).getResultList();
- }
-
- public Long searchCountUserProfile(Long userId, String text, String offers, String search) {
- return getUserProfileQuery(Long.class, userId, text, offers, search, null, false).getSingleResult();
- }
-
public User getExternalUser(String extId, String extType) {
return single(fillLazy(em
, oem -> oem.createNamedQuery("getExternalUser", User.class)
@@ -576,8 +571,8 @@ public class UserDao implements IGroupAdminDataProviderDao<User> {
}
@Override
- public List<User> get(String search, long start, long count, String order) {
- return get(search, start, count, order, false, Long.valueOf(-1));
+ public List<User> get(String search, long start, long count, SortParam<String> sort) {
+ return get(search, start, count, sort, false, Long.valueOf(-1));
}
public Set<Right> getRights(Long id) {
diff --git a/openmeetings-db/src/main/java/org/apache/openmeetings/db/entity/room/Invitation.java b/openmeetings-db/src/main/java/org/apache/openmeetings/db/entity/room/Invitation.java
index 76a46c831..ba3fccaf6 100644
--- a/openmeetings-db/src/main/java/org/apache/openmeetings/db/entity/room/Invitation.java
+++ b/openmeetings-db/src/main/java/org/apache/openmeetings/db/entity/room/Invitation.java
@@ -51,14 +51,6 @@ import org.apache.openmeetings.db.entity.user.User;
})
public class Invitation extends HistoricalEntity {
private static final long serialVersionUID = 1L;
- public static final String SELECT_I = "SELECT i ";
- public static final String SELECT_COUNT = "SELECT COUNT(i) ";
- public static final String BY_ALL = " FROM Invitation i WHERE i.deleted = false";
- public static final String BY_GROUP = BY_ALL + " AND i.invitedBy.id IN "
- + "(SELECT gu1.user.id FROM GroupUser gu1 WHERE gu1.group.id IN "
- + " (SELECT gu.group.id FROM GroupUser gu WHERE gu.moderator = true AND gu.user.id = :userId)"
- + ") ";
- public static final String BY_USER = BY_ALL + " AND i.invitedBy.id = :userId";
public enum MessageType {
CREATE
diff --git a/openmeetings-db/src/main/java/org/apache/openmeetings/db/util/DaoHelper.java b/openmeetings-db/src/main/java/org/apache/openmeetings/db/util/DaoHelper.java
index 6bca78271..ce0a97220 100644
--- a/openmeetings-db/src/main/java/org/apache/openmeetings/db/util/DaoHelper.java
+++ b/openmeetings-db/src/main/java/org/apache/openmeetings/db/util/DaoHelper.java
@@ -20,15 +20,27 @@ package org.apache.openmeetings.db.util;
import java.util.List;
import java.util.Locale;
+import java.util.function.BiFunction;
import java.util.function.Function;
+import java.util.stream.Stream;
import javax.persistence.EntityManager;
import javax.persistence.TypedQuery;
+import javax.persistence.criteria.AbstractQuery;
+import javax.persistence.criteria.CriteriaBuilder;
+import javax.persistence.criteria.CriteriaQuery;
+import javax.persistence.criteria.Expression;
+import javax.persistence.criteria.Path;
+import javax.persistence.criteria.Predicate;
+import javax.persistence.criteria.Root;
+import javax.persistence.criteria.Subquery;
import org.apache.commons.lang3.StringUtils;
import org.apache.openjpa.persistence.OpenJPAEntityManager;
import org.apache.openjpa.persistence.OpenJPAPersistence;
import org.apache.openjpa.persistence.OpenJPAQuery;
+import org.apache.openmeetings.db.entity.user.GroupUser;
+import org.apache.wicket.extensions.markup.html.repeater.util.SortParam;
import org.apache.wicket.util.string.Strings;
public class DaoHelper {
@@ -36,89 +48,132 @@ public class DaoHelper {
private DaoHelper() {}
- public static String getSearchQuery(String table, String alias, String search, boolean filterDeleted, boolean count, String sort, String... fields) {
- return getSearchQuery(table, alias, search, false, filterDeleted, count, sort, fields);
+ public static String getStringParam(String param) {
+ return "%" + StringUtils.lowerCase(param, Locale.ROOT) + "%";
}
- public static String getSearchQuery(String table, String alias, String search, boolean distinct, boolean filterDeleted, boolean count, String sort, String... fields) {
- return getSearchQuery(table, alias, null, search, distinct, filterDeleted, count, null, sort, fields);
+ public static <T> long count(EntityManager em
+ , Class<T> clazz
+ , String search
+ , List<String> searchFields
+ , boolean noDeleted
+ , BiFunction<CriteriaBuilder, CriteriaQuery<?>, Predicate> filter)
+ {
+ return count(em, clazz, (builder, root) -> builder.count(root), search, searchFields, noDeleted, filter);
}
- public static String getSearchQuery(String table, String alias, String join, String search, boolean filterDeleted, boolean count, String additionalWhere, String sort, String... fields) {
- return getSearchQuery(table, alias, join, search, false, filterDeleted, count, additionalWhere, sort, fields);
+ public static <T> long count(EntityManager em
+ , Class<T> clazz
+ , BiFunction<CriteriaBuilder, Root<T>, Expression<Long>> queuePath
+ , String search
+ , List<String> searchFields
+ , boolean noDeleted
+ , BiFunction<CriteriaBuilder, CriteriaQuery<?>, Predicate> filter)
+ {
+ CriteriaQuery<Long> query = query(em, clazz, Long.class, queuePath, false, search, searchFields, noDeleted, filter, null);
+ return em.createQuery(query).getSingleResult();
}
- public static String getSearchQuery(String table, String alias, String join, String search, boolean distinct, boolean filterDeleted, boolean count, String additionalWhere, String sort, String... fields) {
- StringBuilder sb = new StringBuilder("SELECT ");
- if (count) {
- sb.append("COUNT(");
- }
+ public static <T> List<T> get(
+ EntityManager em
+ , Class<T> clazz
+ , boolean distinct
+ , String search
+ , List<String> searchFields
+ , boolean noDeleted
+ , BiFunction<CriteriaBuilder, CriteriaQuery<?>, Predicate> filter
+ , SortParam<String> sort
+ , long start
+ , long count)
+ {
+ return get(em, clazz, clazz, (builder, root) -> root
+ , distinct, search, searchFields, noDeleted, filter, sort, start, count);
+ }
+
+ public static <T, R> List<T> get(
+ EntityManager em
+ , Class<R> rootClazz
+ , Class<T> clazz
+ , BiFunction<CriteriaBuilder, Root<R>, Expression<T>> queuePath
+ , boolean distinct
+ , String search
+ , List<String> searchFields
+ , boolean noDeleted
+ , BiFunction<CriteriaBuilder, CriteriaQuery<?>, Predicate> filter
+ , SortParam<String> sort
+ , long start
+ , long count)
+ {
+ CriteriaQuery<T> query = query(em, rootClazz, clazz, queuePath, distinct, search, searchFields, noDeleted, filter, sort);
+ return setLimits(em.createQuery(query), start, count).getResultList();
+ }
+
+ public static <T, R> CriteriaQuery<T> query(
+ EntityManager em
+ , Class<R> rootClazz
+ , Class<T> clazz
+ , BiFunction<CriteriaBuilder, Root<R>, Expression<T>> queuePath
+ , boolean distinct
+ , String search
+ , List<String> searchFields
+ , boolean noDeleted
+ , BiFunction<CriteriaBuilder, CriteriaQuery<?>, Predicate> filter
+ , SortParam<String> sort)
+ {
+ CriteriaBuilder builder = em.getCriteriaBuilder();
+ CriteriaQuery<T> query = builder.createQuery(clazz);
+ Root<R> root = query.from(rootClazz);
+ query.select(queuePath.apply(builder, root));
if (distinct) {
- sb.append("DISTINCT ");
- }
- sb.append(alias);
- if (count) {
- sb.append(")");
+ query.distinct(distinct);
}
- sb.append(" FROM ").append(table).append(" ").append(alias);
- if (!Strings.isEmpty(join)) {
- sb.append(" ").append(join);
+
+ query.where(search(search, searchFields, noDeleted, filter, builder, root, query));
+ sort(sort, builder, root, query);
+ return query;
+ }
+
+ public static <T, Q> Predicate search(String search
+ , List<String> searchFields
+ , boolean noDeleted
+ , BiFunction<CriteriaBuilder, CriteriaQuery<?>, Predicate> filter
+ , CriteriaBuilder builder
+ , Root<T> root
+ , CriteriaQuery<Q> query)
+ {
+ Predicate result = builder.isNull(null);
+ if (noDeleted) {
+ result = builder.and(result, builder.equal(root.get("deleted"), false));
}
- sb.append(" WHERE 1 = 1 ");
- if (filterDeleted) {
- sb.append("AND ").append(alias).append(".deleted = false ");
+ if (filter != null) {
+ result = builder.and(result, filter.apply(builder, query));
}
- appendWhereClause(sb, search, alias, fields);
- if (!Strings.isEmpty(additionalWhere)) {
- sb.append("AND ").append(additionalWhere);
+ if (!Strings.isEmpty(search)) {
+ Predicate[] criterias = Stream.of(search.replace("\'", "").replace("\"", "").split(" "))
+ .filter(searchItem -> !searchItem.isEmpty())
+ .map(DaoHelper::getStringParam)
+ .flatMap(searchItem -> searchFields.stream().map(col -> like(col, searchItem, builder, root)))
+ .toArray(Predicate[]::new);
+ result = builder.and(result, builder.or(criterias));
}
- return appendSort(sb, alias, sort).toString();
+ return result;
}
- public static StringBuilder appendWhereClause(StringBuilder sb, String search, String alias, String... fields) {
- if (!Strings.isEmpty(search) && fields != null) {
- boolean notEmpty = false;
- StringBuilder inSb = new StringBuilder();
- String[] searchItems = search.replace("\'", "").replace("\"", "").split(" ");
- for (int i = 0; i < searchItems.length; ++i) {
- if (searchItems[i].isEmpty()) {
- continue;
- }
- if (i == 0) {
- notEmpty = true;
- inSb.append(" (");
- } else {
- inSb.append(" OR ");
- }
- StringBuilder placeholder = new StringBuilder();
- placeholder.append("%").append(StringUtils.lowerCase(searchItems[i], Locale.ROOT)).append("%");
-
- inSb.append("(");
- for (int j = 0; j < fields.length; ++j) {
- if (j != 0) {
- inSb.append(" OR ");
- }
- inSb.append("lower(").append(alias).append(".").append(fields[j]).append(") LIKE '").append(placeholder).append("' ");
- }
- inSb.append(")");
- }
- if (notEmpty) {
- inSb.append(") ");
- sb.append(" AND").append(inSb);
- }
+ public static <T> Predicate like(String col, String searchItem, CriteriaBuilder builder, Path<T> root) {
+ Path<String> colPath = null;
+ String[] cols = col.split("[.]");
+ for(String s : cols) {
+ colPath = colPath == null ? root.get(s) : colPath.get(s);
}
- return sb;
+ return builder.like(builder.lower(colPath), "%" + searchItem + "%");
}
- public static StringBuilder appendSort(StringBuilder sb, String alias, String sort) {
- if (!Strings.isEmpty(sort)) {
- sb.append(" ORDER BY ").append(alias).append(".").append(sort);
+ public static <T, Q> void sort(SortParam<String> sort, CriteriaBuilder builder, Root<T> root, CriteriaQuery<Q> query) {
+ if (sort != null && !Strings.isEmpty(sort.getProperty())) {
+ query.orderBy(sort.isAscending()
+ ? builder.asc(root.get(sort.getProperty()))
+ : builder.desc(root.get(sort.getProperty())));
}
- return sb;
- }
-
- public static String getStringParam(String param) {
- return param == null ? "%" : "%" + StringUtils.lowerCase(param, Locale.ROOT) + "%";
}
public static <T> TypedQuery<T> setLimits(TypedQuery<T> q, Long first, Long max) {
@@ -149,4 +204,21 @@ public class DaoHelper {
public static <T> T single(List<T> l) {
return l.isEmpty() ? null : l.get(0);
}
+
+ @SuppressWarnings("unchecked")
+ public static <T> Root<T> getRoot(CriteriaQuery<?> query, Class<T> clazz) {
+ return query.getRoots().stream()
+ .filter(r -> clazz.equals(r.getModel().getJavaType()))
+ .map(r -> (Root<T>)r)
+ .findAny()
+ .orElseThrow();
+ }
+
+ public static Subquery<Long> groupAdminQuery(Long userId, CriteriaBuilder builder, AbstractQuery<?> parentQ) {
+ Subquery<Long> query = parentQ.subquery(Long.class);
+ Root<GroupUser> root = query.from(GroupUser.class);
+ query.select(root.get("group").get("id"));
+ query.where(builder.and(builder.isTrue(root.get("moderator")), builder.equal(root.get("user").get("id"), userId)));
+ return query;
+ }
}
diff --git a/openmeetings-web/src/main/java/org/apache/openmeetings/web/admin/groups/GroupUsersPanel.java b/openmeetings-web/src/main/java/org/apache/openmeetings/web/admin/groups/GroupUsersPanel.java
index 469b5350a..572ad9b82 100644
--- a/openmeetings-web/src/main/java/org/apache/openmeetings/web/admin/groups/GroupUsersPanel.java
+++ b/openmeetings-web/src/main/java/org/apache/openmeetings/web/admin/groups/GroupUsersPanel.java
@@ -152,7 +152,7 @@ public class GroupUsersPanel extends Panel {
list.addAll(users2add);
list.addAll(search == null && getSort() == null
? getDao().get(groupId, first, count)
- : getDao().get(groupId, search, first, count, getSortStr()));
+ : getDao().get(groupId, search, first, count, getSort()));
return list.iterator();
}
diff --git a/openmeetings-web/src/main/java/org/apache/openmeetings/web/data/SearchableDataProvider.java b/openmeetings-web/src/main/java/org/apache/openmeetings/web/data/SearchableDataProvider.java
index 7edb21c12..2db68993a 100644
--- a/openmeetings-web/src/main/java/org/apache/openmeetings/web/data/SearchableDataProvider.java
+++ b/openmeetings-web/src/main/java/org/apache/openmeetings/web/data/SearchableDataProvider.java
@@ -53,19 +53,11 @@ public class SearchableDataProvider<T extends IDataProviderEntity> extends Sorta
return get().getBean(clazz);
}
- protected String getSortStr() {
- String result = null;
- if (getSort() != null) {
- result = getSort().getProperty() + " " + (getSort().isAscending() ? "ASC" : "DESC");
- }
- return result;
- }
-
@Override
public Iterator<? extends T> iterator(long first, long count) {
return (search == null && getSort() == null
? getDao().get(first, count)
- : getDao().get(search, first, count, getSortStr())).iterator();
+ : getDao().get(search, first, count, getSort())).iterator();
}
@Override
diff --git a/openmeetings-web/src/main/java/org/apache/openmeetings/web/data/SearchableGroupAdminDataProvider.java b/openmeetings-web/src/main/java/org/apache/openmeetings/web/data/SearchableGroupAdminDataProvider.java
index 54771e60b..66f911e45 100644
--- a/openmeetings-web/src/main/java/org/apache/openmeetings/web/data/SearchableGroupAdminDataProvider.java
+++ b/openmeetings-web/src/main/java/org/apache/openmeetings/web/data/SearchableGroupAdminDataProvider.java
@@ -42,8 +42,8 @@ public class SearchableGroupAdminDataProvider<T extends IDataProviderEntity> ext
@Override
public Iterator<? extends T> iterator(long first, long count) {
return (hasGroupAdminLevel(getRights())
- ? getDao().adminGet(search, getUserId(), first, count, getSortStr())
- : getDao().adminGet(search, first, count, getSortStr())).iterator();
+ ? getDao().adminGet(search, getUserId(), first, count, getSort())
+ : getDao().adminGet(search, first, count, getSort())).iterator();
}
@Override
diff --git a/openmeetings-web/src/main/java/org/apache/openmeetings/web/user/profile/InvitationsPanel.java b/openmeetings-web/src/main/java/org/apache/openmeetings/web/user/profile/InvitationsPanel.java
index 3be2347b8..76bcd1bd0 100644
--- a/openmeetings-web/src/main/java/org/apache/openmeetings/web/user/profile/InvitationsPanel.java
+++ b/openmeetings-web/src/main/java/org/apache/openmeetings/web/user/profile/InvitationsPanel.java
@@ -122,11 +122,11 @@ public class InvitationsPanel extends UserBasePanel {
@Override
public Iterator<? extends Invitation> iterator(long first, long count) {
if (rights.contains(Right.ADMIN)) {
- return getDao().get(search, first, count, getSortStr()).iterator();
+ return getDao().get(search, first, count, getSort()).iterator();
} else if (rights.contains(Right.GROUP_ADMIN)) {
- return getDao().getGroup(search, first, count, userId, getSortStr()).iterator();
+ return getDao().getGroup(search, first, count, userId, getSort()).iterator();
} else {
- return getDao().getUser(search, first, count, userId, getSortStr()).iterator();
+ return getDao().getUser(search, first, count, userId, getSort()).iterator();
}
}
diff --git a/openmeetings-web/src/main/java/org/apache/openmeetings/web/user/profile/UserSearchPanel.java b/openmeetings-web/src/main/java/org/apache/openmeetings/web/user/profile/UserSearchPanel.java
index 94afde39d..277e0e11d 100644
--- a/openmeetings-web/src/main/java/org/apache/openmeetings/web/user/profile/UserSearchPanel.java
+++ b/openmeetings-web/src/main/java/org/apache/openmeetings/web/user/profile/UserSearchPanel.java
@@ -22,7 +22,7 @@ import static org.apache.openmeetings.db.util.TimezoneUtil.getTimeZone;
import static org.apache.openmeetings.util.OpenmeetingsVariables.ATTR_CLASS;
import static org.apache.openmeetings.web.app.WebSession.getUserId;
-import java.util.ArrayList;
+import java.util.Collections;
import java.util.Iterator;
import java.util.List;
@@ -35,6 +35,7 @@ import org.apache.openmeetings.web.common.UserBasePanel;
import org.apache.wicket.AttributeModifier;
import org.apache.wicket.ajax.AjaxRequestTarget;
import org.apache.wicket.core.request.handler.IPartialPageRequestHandler;
+import org.apache.wicket.extensions.markup.html.repeater.util.SortParam;
import org.apache.wicket.markup.html.WebMarkupContainer;
import org.apache.wicket.markup.html.basic.Label;
import org.apache.wicket.markup.html.form.Form;
@@ -54,11 +55,10 @@ import de.agilecoders.wicket.core.markup.html.bootstrap.button.Buttons;
public class UserSearchPanel extends UserBasePanel {
private static final long serialVersionUID = 1L;
private static final List<Integer> itemsPerPage = List.of(10, 25, 50, 75, 100, 200, 500, 1000, 2500, 5000);
+ private static final SortParam<String> orderBy = new SortParam<>("firstname", true);
private final TextField<String> text = new TextField<>("text", Model.of(""));
private final TextField<String> search = new TextField<>("search", Model.of(""));
private final TextField<String> offer = new TextField<>("offer", Model.of(""));
- private String orderBy = "u.firstname";
- private boolean asc = true;
private boolean searched = false;
private final WebMarkupContainer container = new WebMarkupContainer("container");
@SpringBean
@@ -94,8 +94,8 @@ public class UserSearchPanel extends UserBasePanel {
@Override
public Iterator<? extends User> iterator(long first, long count) {
- return searched ? userDao.searchUserProfile(getUserId(), text.getModelObject(), offer.getModelObject(), search.getModelObject(), orderBy, first, count, asc).iterator()
- : new ArrayList<User>().iterator();
+ return searched ? userDao.searchUserProfile(getUserId(), text.getModelObject(), offer.getModelObject(), search.getModelObject(), orderBy, first, count).iterator()
+ : Collections.emptyIterator();
}
@Override
diff --git a/openmeetings-webservice/src/main/java/org/apache/openmeetings/webservice/GroupWebService.java b/openmeetings-webservice/src/main/java/org/apache/openmeetings/webservice/GroupWebService.java
index f9608cafa..4da907296 100644
--- a/openmeetings-webservice/src/main/java/org/apache/openmeetings/webservice/GroupWebService.java
+++ b/openmeetings-webservice/src/main/java/org/apache/openmeetings/webservice/GroupWebService.java
@@ -54,6 +54,7 @@ import org.apache.openmeetings.webservice.error.ServiceException;
import org.apache.openmeetings.webservice.schema.GroupDTOListWrapper;
import org.apache.openmeetings.webservice.schema.ServiceResultWrapper;
import org.apache.openmeetings.webservice.schema.UserSearchResultWrapper;
+import org.apache.wicket.extensions.markup.html.repeater.util.SortParam;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
@@ -319,7 +320,7 @@ public class GroupWebService extends BaseWebService {
result.setRecords(groupUserDao.count(id));
result.setResult(new ArrayList<>());
String order = isAlphanumeric(orderby) ? orderby : "id";
- for (GroupUser ou : groupUserDao.get(id, null, start, max, order + " " + (asc ? "ASC" : "DESC"))) {
+ for (GroupUser ou : groupUserDao.get(id, null, start, max, order == null ? null : new SortParam<>(order, asc))) {
result.getResult().add(ou.getUser());
}
return new UserSearchResult(result);