MembershipsDAO.java

package it.inaf.ia2.gms.persistence;

import it.inaf.ia2.gms.persistence.model.GroupEntity;
import it.inaf.ia2.gms.persistence.model.MembershipEntity;
import java.sql.PreparedStatement;
import java.util.ArrayList;
import java.util.List;
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 MembershipsDAO {

    private final JdbcTemplate jdbcTemplate;

    @Autowired
    public MembershipsDAO(DataSource dataSource) {
        jdbcTemplate = new JdbcTemplate(dataSource);
    }

    public List<MembershipEntity> findByGroup(String groupId) {

        String sql = "SELECT user_id FROM gms_membership WHERE group_id = ?";

        return jdbcTemplate.query(conn -> {
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setString(1, groupId);
            return ps;
        }, resultSet -> {
            List<MembershipEntity> members = new ArrayList<>();
            while (resultSet.next()) {
                MembershipEntity membership = new MembershipEntity();
                membership.setGroupId(groupId);
                membership.setUserId(resultSet.getString("user_id"));
                members.add(membership);
            }
            return members;
        });
    }

    public List<GroupEntity> getUserMemberships(String userId) {

        String sql = "SELECT g.id, g.name, g.path, g.is_leaf FROM "
                + " gms_membership m "
                + " JOIN gms_group g ON g.id = m.group_id"
                + " WHERE m.user_id = ?";

        return jdbcTemplate.query(conn -> {
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setString(1, userId);
            return ps;
        }, resultSet -> {
            List<GroupEntity> memberships = 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"));
                memberships.add(group);
            }
            return memberships;
        });
    }

    public boolean isMemberOf(String userId, String groupId) {

        String sql = "SELECT COUNT(*) FROM gms_membership "
                + " WHERE user_id = ? AND group_id = ?";

        return jdbcTemplate.query(conn -> {
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setString(1, userId);
            ps.setString(2, groupId);
            return ps;
        }, resultSet -> {
            resultSet.next();
            int count = resultSet.getInt(1);
            return count == 1;
        });
    }

    public MembershipEntity addMember(MembershipEntity membership) {

        String sql = "INSERT INTO gms_membership (group_id, user_id) VALUES (?, ?)";

        jdbcTemplate.update(conn -> {
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setString(1, membership.getGroupId());
            ps.setString(2, membership.getUserId());
            return ps;
        });

        return membership;
    }

    public void removeMembership(String groupId, String userId) {

        String sql = "DELETE FROM gms_membership WHERE group_id = ? AND user_id = ?";

        jdbcTemplate.update(conn -> {
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setString(1, groupId);
            ps.setString(2, userId);
            return ps;
        });
    }

    public void moveMemberships(String fromUserId, String toUserId) {

        String sql = "UPDATE gms_membership SET user_id = ? WHERE user_id = ?";

        jdbcTemplate.update(conn -> {
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setString(1, toUserId);
            ps.setString(2, fromUserId);
            return ps;
        });
    }

    public void deleteAllGroupsMembership(List<String> groupIds) {

        String sql = "DELETE FROM gms_membership WHERE group_id IN ("
                + String.join(",", groupIds.stream().map(g -> "?").collect(Collectors.toList()))
                + ")";

        jdbcTemplate.update(conn -> {
            PreparedStatement ps = conn.prepareStatement(sql);
            int i = 0;
            for (String groupId : groupIds) {
                ps.setString(++i, groupId);
            }
            return ps;
        });
    }
}