Newer
Older
package it.inaf.ia2.gms.persistence;
import it.inaf.ia2.gms.model.GroupBreadcrumb;
import it.inaf.ia2.gms.persistence.model.GroupEntity;
import it.inaf.ia2.gms.service.hook.GroupsHook;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Collections;
Sonia Zorba
committed
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.stream.Collectors;
import javax.sql.DataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;
@Component
public class GroupsDAO {
Sonia Zorba
committed
private static final String GROUP_COLUMNS = "id, name, path, is_leaf, locked, creation_time, created_by";
@Autowired(required = false)
protected GroupsHook groupsHook;
private final JdbcTemplate jdbcTemplate;
@Autowired
public GroupsDAO(DataSource dataSource) {
jdbcTemplate = new JdbcTemplate(dataSource);
}
public GroupEntity createGroup(GroupEntity group) {
if (groupsHook != null) {
groupsHook.beforeCreate(group);
}
Sonia Zorba
committed
String sql = "INSERT INTO gms_group (id, name, path, is_leaf, created_by) VALUES (?, ?, ?, ?, ?)";
jdbcTemplate.update(conn -> {
PreparedStatement ps = conn.prepareStatement(sql);
ps.setString(1, group.getId());
ps.setString(2, group.getName());
ps.setObject(3, group.getPath(), Types.OTHER);
Sonia Zorba
committed
ps.setString(5, group.getCreatedBy());
return ps;
});
return group;
}
public GroupEntity updateGroup(GroupEntity group) {
if (groupsHook != null) {
groupsHook.beforeUpdate(group);
}
String sql = "UPDATE gms_group SET name = ?, path = ?, is_leaf = ? WHERE id = ?";
jdbcTemplate.update(conn -> {
PreparedStatement ps = conn.prepareStatement(sql);
ps.setString(1, group.getName());
ps.setObject(2, group.getPath(), Types.OTHER);
ps.setBoolean(3, group.isLeaf());
ps.setString(4, group.getId());
return ps;
});
return group;
}
public void deleteGroup(GroupEntity group) {
if (groupsHook != null) {
groupsHook.beforeDelete(group);
}
String sql = "DELETE FROM gms_group WHERE id = ?";
jdbcTemplate.update(sql, group.getId());
public Optional<GroupEntity> findGroupById(String groupId) {
Sonia Zorba
committed
String sql = "SELECT " + GROUP_COLUMNS + " from gms_group WHERE id = ?";
return jdbcTemplate.query(conn -> {
PreparedStatement ps = conn.prepareStatement(sql);
ps.setString(1, groupId);
return ps;
}, resultSet -> {
if (resultSet.next()) {
GroupEntity group = new GroupEntity();
group.setId(resultSet.getString("id"));
group.setName(resultSet.getString("name"));
group.setPath(resultSet.getString("path"));
group.setLeaf(resultSet.getBoolean("is_leaf"));
group.setLocked(resultSet.getBoolean("locked"));
return Optional.of(group);
}
return Optional.empty();
});
}
public Optional<GroupEntity> findGroupByPath(String path) {
Sonia Zorba
committed
String sql = "SELECT " + GROUP_COLUMNS + " from gms_group WHERE path = ?";
return jdbcTemplate.query(conn -> {
PreparedStatement ps = conn.prepareStatement(sql);
ps.setObject(1, path, Types.OTHER);
return ps;
}, resultSet -> {
if (resultSet.next()) {
Sonia Zorba
committed
GroupEntity group = getGroupFromResultSet(resultSet);
return Optional.of(group);
}
return Optional.empty();
});
public List<GroupEntity> findGroupsByIds(Set<String> identifiers) {
if (identifiers.isEmpty()) {
return new ArrayList<>();
}
return jdbcTemplate.query(conn -> {
Sonia Zorba
committed
String sql = "SELECT " + GROUP_COLUMNS + " from gms_group WHERE id IN (";
sql += String.join(",", identifiers.stream().map(p -> "?").collect(Collectors.toList()));
sql += ")";
PreparedStatement ps = conn.prepareStatement(sql);
int i = 0;
for (String id : identifiers) {
ps.setString(++i, id);
}
return ps;
}, resultSet -> {
Sonia Zorba
committed
return getGroupsFromResultSet(resultSet);
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
/**
* @param groupIds
* @return map having group id as keys and group complete name as values
*/
public Map<String, String> getGroupCompleteNamesFromId(Set<String> groupIds) {
Map<String, String> result = new HashMap<>();
if (groupIds.isEmpty()) {
return result;
}
String sql = "SELECT id, complete_name FROM group_complete_name WHERE id IN ("
+ String.join(",", Collections.nCopies(groupIds.size(), "?")) + ")";
jdbcTemplate.query(conn -> {
PreparedStatement ps = conn.prepareStatement(sql);
int i = 0;
for (String groupId : groupIds) {
ps.setString(++i, groupId);
}
return ps;
}, (rs, index) -> {
result.put(rs.getString("id"), rs.getString("complete_name"));
return null;
});
return result;
}
public Optional<GroupEntity> findGroupByParentAndName(String parentPath, String childName) {
Sonia Zorba
committed
String sql = "SELECT " + GROUP_COLUMNS + " from gms_group WHERE name = ? AND path ~ ?";
return jdbcTemplate.query(conn -> {
PreparedStatement ps = conn.prepareStatement(sql);
ps.setString(1, childName);
ps.setObject(2, getSubGroupsPath(parentPath), Types.OTHER);
return ps;
}, resultSet -> {
if (resultSet.next()) {
Sonia Zorba
committed
GroupEntity group = getGroupFromResultSet(resultSet);
return Optional.of(group);
}
return Optional.empty();
});
public List<GroupEntity> getDirectSubGroups(String path) {
return getDirectSubGroups(path, null);
}
/**
* Returns the direct children (one level).
*/
public List<GroupEntity> getDirectSubGroups(String path, String searchFilter) {
boolean hasSearchFilter = searchFilter != null && !searchFilter.trim().isEmpty();
String sql;
if (hasSearchFilter) {
Sonia Zorba
committed
sql = "SELECT " + GROUP_COLUMNS + " FROM gms_group WHERE path ~ ? AND name ILIKE ? ORDER BY name";
Sonia Zorba
committed
sql = "SELECT " + GROUP_COLUMNS + " FROM gms_group WHERE path ~ ? ORDER BY name";
return jdbcTemplate.query(conn -> {
PreparedStatement ps = conn.prepareStatement(sql);
ps.setObject(1, getSubGroupsPath(path), Types.OTHER);
if (hasSearchFilter) {
ps.setObject(2, "%" + searchFilter + "%");
}
return ps;
}, resultSet -> {
return getGroupsFromResultSet(resultSet);
});
}
private String getSubGroupsPath(String path) {
if (!path.isEmpty()) {
path += ".";
}
path += "*{1}";
return path;
}
public List<GroupEntity> getAllChildren(String path) {
Sonia Zorba
committed
String sql = "SELECT " + GROUP_COLUMNS + " FROM gms_group WHERE path <@ ? AND path <> ? ORDER BY nlevel(path) DESC";
return jdbcTemplate.query(conn -> {
PreparedStatement ps = conn.prepareStatement(sql);
ps.setObject(1, path, Types.OTHER);
ps.setObject(2, path, Types.OTHER);
return ps;
}, resultSet -> {
return getGroupsFromResultSet(resultSet);
});
}
public List<GroupEntity> findGroupsByNames(List<String> names) {
Sonia Zorba
committed
String sql = "SELECT " + GROUP_COLUMNS + " from gms_group WHERE name IN ("
+ String.join(",", names.stream().map(g -> "?").collect(Collectors.toList()))
+ ")";
return jdbcTemplate.query(conn -> {
PreparedStatement ps = conn.prepareStatement(sql);
int i = 0;
for (String name : names) {
ps.setString(++i, name);
}
return ps;
}, resultSet -> {
return getGroupsFromResultSet(resultSet);
});
}
private List<GroupEntity> getGroupsFromResultSet(ResultSet resultSet) throws SQLException {
List<GroupEntity> groups = new ArrayList<>();
while (resultSet.next()) {
Sonia Zorba
committed
groups.add(getGroupFromResultSet(resultSet));
}
return groups;
}
Sonia Zorba
committed
private GroupEntity getGroupFromResultSet(ResultSet resultSet) throws SQLException {
GroupEntity group = new GroupEntity();
group.setId(resultSet.getString("id"));
group.setName(resultSet.getString("name"));
group.setPath(resultSet.getString("path"));
group.setLeaf(resultSet.getBoolean("is_leaf"));
group.setLocked(resultSet.getBoolean("locked"));
group.setCreationTime(new Date(resultSet.getDate("creation_time").getTime()));
group.setCreatedBy(resultSet.getString("created_by"));
return group;
}
public Map<String, Boolean> getHasChildrenMap(Set<String> groupIds) {
if (groupIds.isEmpty()) {
return new HashMap<>();
}
String sql = "SELECT g.id, COUNT(s.*) > 0 AS has_children \n"
+ "FROM gms_group g\n"
+ "LEFT JOIN gms_group s ON s.path <@ g.path AND s.path <> g.path\n"
+ "WHERE g.id IN("
+ String.join(",", groupIds.stream().map(g -> "?").collect(Collectors.toList()))
+ ") GROUP BY g.id";
return jdbcTemplate.query(conn -> {
PreparedStatement ps = conn.prepareStatement(sql);
int i = 0;
for (String groupId : groupIds) {
ps.setString(++i, groupId);
}
return ps;
}, resultSet -> {
Map<String, Boolean> map = new HashMap<>();
while (resultSet.next()) {
map.put(resultSet.getString("id"), resultSet.getBoolean("has_children"));
}
return map;
});
}
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
/**
* Returns the number of groups.
*/
public long count() {
String sql = "SELECT COUNT(*) FROM gms_group";
return jdbcTemplate.query(sql, resultSet -> {
resultSet.next();
return resultSet.getLong(1);
});
}
public List<GroupBreadcrumb> getBreadcrumbs(String path) {
String sql = "SELECT id, name FROM gms_group WHERE path @> ? ORDER BY length(path::varchar) ASC";
return jdbcTemplate.query(conn -> {
PreparedStatement ps = conn.prepareStatement(sql);
ps.setObject(1, path, Types.OTHER);
return ps;
}, resultSet -> {
List<GroupBreadcrumb> breadcrumbs = new ArrayList<>();
while (resultSet.next()) {
GroupBreadcrumb bc = new GroupBreadcrumb();
bc.setGroupId(resultSet.getString("id"));
bc.setGroupName(resultSet.getString("name"));
breadcrumbs.add(bc);
}
return breadcrumbs;
});
}
public List<GroupEntity> searchGroups(String searchText) {
Sonia Zorba
committed
String sql = "SELECT " + GROUP_COLUMNS + " from gms_group WHERE name ILIKE ?";
return jdbcTemplate.query(conn -> {
PreparedStatement ps = conn.prepareStatement(sql);
ps.setString(1, "%" + searchText + "%");
return ps;
}, resultSet -> {
return getGroupsFromResultSet(resultSet);
});
}