@Override
public List<Map<String, String>> getSubFoldersByFolderId(int folderId) {
List<Map<String, String>> subFolderList = new ArrayList<Map<String, String>>();
try {
Connection conn = ConnectionFactory.getInstance().getConnection();
String sql =
"SELECT f.id AS folder_id, f.name AS folder_name, i.item_type AS type, i.position "
+ "FROM folders f "
+ "JOIN folder_items i ON f.id = i.item_id "
+ "WHERE i.folder_id = ? ORDER BY position DESC";
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setLong(1, folderId);
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
if (rs.getString("type").equals("Folder")) {
// the sub folder
Map<String, String> folder = new HashMap<String, String>();
folder.put("id", String.valueOf(rs.getInt("folder_id")));
folder.put("name", rs.getString("folder_name"));
subFolderList.add(folder);
}
}
rs.close();
pstmt.close();
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return subFolderList;
}
@Override
public List<Map<String, String>> getFoldersByIds(List<Integer> folderIdList) {
List<Map<String, String>> folders = new ArrayList<Map<String, String>>();
StringBuilder sb = new StringBuilder();
for (int i = 0; i < folderIdList.size(); i++) {
sb.append("?,");
}
String s = sb.substring(0, sb.length() - 1);
try {
Connection conn = ConnectionFactory.getInstance().getConnection();
String sql = "SELECT id, name FROM folders WHERE id IN (" + s + ") ORDER BY name";
PreparedStatement pstmt = conn.prepareStatement(sql);
for (int i = 0; i < folderIdList.size(); i++) {
pstmt.setLong(i + 1, folderIdList.get(i));
}
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
Map<String, String> folder = new HashMap<String, String>();
folder.put("id", Long.toString(rs.getLong("id")));
folder.put("name", rs.getString("name"));
folders.add(folder);
}
rs.close();
pstmt.close();
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return folders;
}
@Override
public ProblemSet find(long id) {
ProblemSet ps = new ProblemSet();
try {
Connection conn = ConnectionFactory.getInstance().getConnection();
String query = "SELECT * FROM sequences WHERE id = ?";
PreparedStatement pstm = conn.prepareStatement(query);
pstm.setLong(1, id);
ResultSet rs = pstm.executeQuery();
if (rs.next()) {
ps.setDecodedID(rs.getInt("id"));
ps.setName(rs.getString("name"));
ps.setHeadSectionId(rs.getLong("head_section_id"));
ps.setParameters(rs.getString("parameters"));
} else {
throw new RuntimeException("Cannot find Problem Set with this " + id);
}
if (rs != null) rs.close();
if (pstm != null) pstm.close();
if (conn != null) conn.close();
} catch (SQLException e) {
throw new RuntimeException(e);
}
return ps;
}
@Override
public JsonArray getProblemSetsByFolder(long folderId) {
JsonArray problemSets = new JsonArray();
try {
Connection conn = ConnectionFactory.getInstance().getConnection();
String sql =
"SELECT f.id AS folder_id, f.name AS folder_name, i.item_type AS type, i.position, s.name AS item_name, s.id AS problem_set_id "
+ "FROM folders f "
+ "RIGHT JOIN folder_items i ON f.id = i.item_id "
+ "LEFT JOIN curriculum_items c ON c.id = i.item_id "
+ "LEFT JOIN sequences s ON s.id = c.sequence_id "
+ "WHERE i.folder_id = ? ORDER BY position DESC";
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setLong(1, folderId);
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
if (rs.getString("type").equals("Folder")) {
// the sub folder
JsonObject folder = new JsonObject();
folder.addProperty("type", "Folder");
folder.addProperty("name", rs.getString("folder_name"));
folder.add("problem_sets", getProblemSetsByFolder(rs.getLong("folder_id")));
problemSets.add(folder);
} else if (rs.getString("type").equals("CurriculumItem")) {
// the problem set
long problemSetId = rs.getLong("problem_set_id");
// get the share link ref by problemSetId in direct
JsonObject problemSet = new JsonObject();
problemSet.addProperty("id", Long.toString(problemSetId));
problemSet.addProperty("type", "CurriculumItem");
problemSet.addProperty("name", rs.getString("item_name"));
problemSets.add(problemSet);
}
}
rs.close();
pstmt.close();
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return problemSets;
}
@Override
public List<FolderItem> getFolderItemsByFolder(long folderId) {
List<FolderItem> items = new ArrayList<>();
try {
Connection conn = ConnectionFactory.getInstance().getConnection();
String sql =
"SELECT f.id AS folder_id, f.name AS folder_name, i.item_type AS type, i.position, s.name AS item_name, s.id AS problem_set_id "
+ "FROM folders f "
+ "RIGHT JOIN folder_items i ON f.id = i.item_id "
+ "LEFT JOIN curriculum_items c ON c.id = i.item_id "
+ "LEFT JOIN sequences s ON s.id = c.sequence_id "
+ "WHERE i.folder_id = ? ORDER BY position DESC";
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setLong(1, folderId);
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
String name = rs.getString("folder_name");
if (rs.getString("type").equals("Folder")) {
FolderItem item = new FolderItem(ItemType.FOLDER, name);
List<FolderItem> children = getFolderItemsByFolder(rs.getLong("folder_id"));
item.setChildren(children);
items.add(item);
} else if (rs.getString("type").equals("CurriculumItem")) {
// the problem set
long problemSetId = rs.getLong("problem_set_id");
// get the share link ref by problemSetId in direct
ProblemSet ps = find((int) problemSetId);
FolderItem item = new FolderItem(ItemType.CURRICULUM_ITEM, name);
item.setPs(ps);
items.add(item);
}
}
rs.close();
pstmt.close();
conn.close();
} catch (SQLException e) {
new RuntimeException(e);
}
return items;
}
public ProblemSetControllerDAOImpl() {
ds = ConnectionFactory.getDataSource();
jdbcTemplate = new JdbcTemplate(ds);
}