Newer
Older
package it.inaf.ia2.gms.persistence;
import it.inaf.ia2.gms.model.GroupBreadcrumb;
import it.inaf.ia2.gms.persistence.model.GroupEntity;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
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 {
private final JdbcTemplate jdbcTemplate;
@Autowired
public GroupsDAO(DataSource dataSource) {
jdbcTemplate = new JdbcTemplate(dataSource);
}
public GroupEntity createGroup(GroupEntity group) {
String sql = "INSERT INTO gms_group (id, name, path, is_leaf) 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);
return ps;
});
return group;
}
public GroupEntity updateGroup(GroupEntity 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 deleteGroupById(String groupId) {
String sql = "DELETE FROM gms_group WHERE id = ?";
jdbcTemplate.update(sql, groupId);
}
public Optional<GroupEntity> findGroupById(String groupId) {
String sql = "SELECT id, name, path, is_leaf 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"));
return Optional.of(group);
}
return Optional.empty();
});
}
public Optional<GroupEntity> findGroupByPath(String path) {
String sql = "SELECT id, name, is_leaf 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()) {
GroupEntity group = new GroupEntity();
group.setId(resultSet.getString("id"));
group.setName(resultSet.getString("name"));
group.setLeaf(resultSet.getBoolean("is_leaf"));
group.setPath(path);
return Optional.of(group);
}
return Optional.empty();
});
public List<GroupEntity> findGroupsByIds(Set<String> identifiers) {
if (identifiers.isEmpty()) {
return new ArrayList<>();
}
return jdbcTemplate.query(conn -> {
String sql = "SELECT id, name, path, is_leaf 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 -> {
List<GroupEntity> groups = new ArrayList<>();
while (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"));
groups.add(group);
}
return groups;
});
}
public Optional<GroupEntity> findGroupByParentAndName(String parentPath, String childName) {
String sql = "SELECT id, path, is_leaf 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()) {
GroupEntity group = new GroupEntity();
group.setId(resultSet.getString("id"));
group.setName(childName);
group.setPath(resultSet.getString("path"));
group.setLeaf(resultSet.getBoolean("is_leaf"));
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) {
sql = "SELECT id, name, path, is_leaf FROM gms_group WHERE path ~ ? AND name ILIKE ? ORDER BY name";
sql = "SELECT id, name, path, is_leaf 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) {
String sql = "SELECT id, name, path, is_leaf 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) {
String sql = "SELECT id, name, path, is_leaf 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()) {
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"));
groups.add(group);
}
return groups;
}
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;
});
}
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
/**
* 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) {
String sql = "SELECT id, name, path, is_leaf 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);
});
}