@Override
public ArrayList<Page> getAllPages(Project project) {
ArrayList<Page> result = new ArrayList<Page>();
if (project == null || project.getID() == null) {
return result;
}
try {
cmdString = "select * from pages where project = ?";
PreparedStatement s = db.prepareStatement(cmdString);
s.setString(1, project.getID());
ResultSet row = s.executeQuery();
while (row.next()) {
String id = row.getString("id");
String body = row.getString("body");
String title = row.getString("title");
int viewCount = Integer.parseInt(row.getString("views"));
result.add(new Page(id, title, body, project, viewCount));
}
row.close();
} catch (Exception e) {
processSQLError(e);
}
return result;
}
@Override
public String getPageSequential(Project project, List<Page> pageResult) {
if (project != null) {
try {
cmdString = "select * from pages where project = ?";
PreparedStatement s = db.prepareStatement(cmdString);
s.setString(1, project.getID());
// On delete cascade will delete categories and pages involved.
ResultSet row = s.executeQuery();
while (row.next()) {
String id = row.getString("id");
String body = row.getString("body");
String title = row.getString("title");
int viewCount = row.getInt("views");
pageResult.add(new Page(id, title, body, project, viewCount));
}
row.close();
} catch (Exception e) {
processSQLError(e);
}
}
return null;
}
@Override
public void insertProject(Project project) {
try {
cmdString = "insert into projects (id, title, home, views) values (?, ?, ?, ?)";
PreparedStatement s = db.prepareStatement(cmdString);
s.setString(1, project.getID());
s.setString(2, project.getTitle());
s.setString(3, project.getHomeID());
s.setInt(4, project.getViewCount());
s.executeUpdate();
} catch (Exception e) {
processSQLError(e);
}
}
@Override
public String updateProject(Project project) {
try {
cmdString = "update projects set title = ?, home = ?, views = ? where id = ?";
PreparedStatement s = db.prepareStatement(cmdString);
s.setString(1, project.getTitle());
s.setString(2, project.getHomeID());
s.setInt(3, project.getViewCount());
s.setString(4, project.getID());
s.executeUpdate();
} catch (Exception e) {
processSQLError(e);
}
return null;
}
@Override
public String deleteProject(Project project) {
try {
cmdString = "delete from projects where id = ?";
PreparedStatement s = db.prepareStatement(cmdString);
s.setString(1, project.getID());
// On delete cascade will delete categories and pages involved.
s.executeUpdate();
} catch (Exception e) {
processSQLError(e);
}
return null;
}
@Override
public int getPageSequentialSize(Project project) {
int count = 0;
try {
cmdString = "select count(*) as PageCount from pages where project = ?";
PreparedStatement s = db.prepareStatement(cmdString);
s.setString(1, project.getID());
ResultSet row = s.executeQuery();
row.next();
count = Integer.parseInt(row.getString("PageCount"));
row.close();
} catch (Exception e) {
processSQLError(e);
}
return count;
}