GroupsDAO.java

package it.inaf.ia2.gms.persistence;

import it.inaf.ia2.gms.persistence.model.NewGroup;
import java.sql.PreparedStatement;
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 NewGroup createGroup(NewGroup group) {

        String sql = "INSERT INTO gms_group (id, name, path) 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 void deleteGroupById(String groupId) {
        String sql = "DELETE FROM gms_group WHERE id = ?";
        jdbcTemplate.update(sql, groupId);
    }

    public List<NewGroup> listSubGroups(String path) {

        String sql = "SELECT id, name, path from gms_group WHERE path ~ ? ORDER BY name";

        return jdbcTemplate.query(conn -> {
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setObject(1, getSubGroupsPath(path), Types.OTHER);
            return ps;
        }, resultSet -> {
            List<NewGroup> groups = new ArrayList<>();
            while (resultSet.next()) {
                NewGroup group = new NewGroup();
                group.setId(resultSet.getString("id"));
                group.setName(resultSet.getString("name"));
                group.setPath(resultSet.getString("path"));
                groups.add(group);
            }
            return groups;
        });
    }

    private String getSubGroupsPath(String path) {
        if (!path.isEmpty()) {
            path += ".";
        }
        path += "*{1}";
        return path;
    }

    public Map<String, Boolean> getHasChildrenMap(Set<String> groupIds) {

        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;
        });
    }
}