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.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) 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 = ? WHERE id = ?";
jdbcTemplate.update(conn -> {
PreparedStatement ps = conn.prepareStatement(sql);
ps.setString(1, group.getName());
ps.setObject(2, group.getPath(), Types.OTHER);
ps.setString(3, group.getId());
return ps;
});
return group;
}
public void deleteGroupById(String groupId) {
String sql = "DELETE FROM gms_group WHERE id = ?";
jdbcTemplate.update(sql, groupId);
}
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
public Optional<GroupEntity> findGroupById(String groupId) {
String sql = "SELECT id, name, path 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"));
return Optional.of(group);
}
return Optional.empty();
});
}
public Optional<GroupEntity> findGroupByPath(String path) {
String sql = "SELECT id, name 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.setPath(path);
return Optional.of(group);
}
return Optional.empty();
});
}
public List<GroupEntity> 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<GroupEntity> groups = new ArrayList<>();
while (resultSet.next()) {
GroupEntity group = new GroupEntity();
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
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;
});
}
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
/**
* 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;
});
}