Java tutorial
/* * To change this license header, choose License Headers in Project Properties. * To change this template file, choose Tools | Templates * and open the template in the editor. */ package com.swcguild.blacksmithblogcapstone.dao; import com.swcguild.blacksmithblogcapstone.dto.BlogEntry; import com.swcguild.blacksmithblogcapstone.dto.BlogSummary; import com.swcguild.blacksmithblogcapstone.dto.Category; import com.swcguild.blacksmithblogcapstone.dto.Comment; import java.sql.ResultSet; import java.sql.SQLException; import java.util.List; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.jdbc.core.RowMapper; import org.springframework.transaction.annotation.Propagation; import org.springframework.transaction.annotation.Transactional; /** * * @author apprentice */ public class BlackSmithDaoImpl implements BlackSmithDao { private JdbcTemplate jdbcTemplate; public void setJdbcTemplate(JdbcTemplate jdbcTemplate) { this.jdbcTemplate = jdbcTemplate; } private static final String SQL_ADD_BLOG_ENTRY = "INSERT INTO BlogEntries (author, blogTimestamp, title, blogBody, category, approvalStatus) VALUES (?,?,?,?,?,?)"; // private static final String SQL_CHECK_AUTHOR = "SELECT id FROM BlogAuthors WHERE authorName = '?'"; private static final String SQL_ADD_CATEGORY_FROM_BLOG_ENTRY = "INSERT INTO Categories(category, blogEntryId) VALUES(?,?)"; @Override @Transactional(propagation = Propagation.REQUIRED, readOnly = false) public BlogEntry addBlogEntry(BlogEntry blogEntry) { blogEntry.setApprovalStatus("NOT_APPROVED"); jdbcTemplate.update(SQL_ADD_BLOG_ENTRY, blogEntry.getAuthorName(), new java.util.Date(), blogEntry.getTitle(), blogEntry.getBody(), blogEntry.getCategory(), blogEntry.getApprovalStatus() // "NOT_APPROVED" ); int id = jdbcTemplate.queryForObject("SELECT LAST_INSERT_ID()", Integer.class); blogEntry.setId(id); if (blogEntry.getCategory() != null && !blogEntry.getCategory().equals("")) { addCategoryFromBlogEntry(blogEntry.getCategory(), blogEntry.getId()); } return blogEntry; } private void addCategoryFromBlogEntry(String category, int blogId) { jdbcTemplate.update(SQL_ADD_CATEGORY_FROM_BLOG_ENTRY, category, blogId); } private static final String SQL_ADD_COMMENT = "INSERT INTO Comments (body, cName, commentTimestamp, blogEntryId) VALUES(?,?,?,?)"; @Override @Transactional(propagation = Propagation.REQUIRED, readOnly = false) public Comment addComment(Comment comment) { jdbcTemplate.update(SQL_ADD_COMMENT, comment.getBody(), comment.getName(), new java.util.Date(), comment.getBlogEntryId()); int id = jdbcTemplate.queryForObject("SELECT LAST_INSERT_ID()", Integer.class); comment.setId(id); return comment; } private static final String SQL_GET_BLOG_ENTRIES = "SELECT * FROM BlogEntries;"; @Override public List<BlogEntry> getAllBlogEntries() { List<BlogEntry> allBlogEntries = jdbcTemplate.query(SQL_GET_BLOG_ENTRIES, new BlogMapper()); return allBlogEntries; } private static final String SQL_GET_BLOG_ENTRY_BY_ID = "SELECT * FROM BlogEntries WHERE id = ?"; @Override public BlogEntry getBlogEntryById(int id) { BlogEntry blogEntryToGet = jdbcTemplate.queryForObject(SQL_GET_BLOG_ENTRY_BY_ID, new BlogMapper(), id); return blogEntryToGet; } private static final String SQL_GET_BLOG_ENTRY_BY_TITLE = "SELECT * FROM BlogEntries WHERE title = ?"; @Override public BlogEntry getBlogEntryByTitle(String title) { BlogEntry blogEntryToGet = jdbcTemplate.queryForObject(SQL_GET_BLOG_ENTRY_BY_TITLE, new BlogMapper(), title); return blogEntryToGet; } private static final String SQL_GET_COMMENTS_BY_BLOG_ID = "SELECT * FROM Comments WHERE blogEntryId = ?;"; @Override public List<Comment> getCommentsByBlogId(int blogId) { List<Comment> comments = jdbcTemplate.query(SQL_GET_COMMENTS_BY_BLOG_ID, new CommentMapper(), blogId); return comments; } private static final String SQL_UPDATE_BLOG_ENTRY = "UPDATE BlogEntries SET author = ?, blogTimestamp = ?, title = ?, blogBody = ?, category = ? WHERE id = ?"; @Override public void editBlogEntry(BlogEntry editedBlogEntry) { jdbcTemplate.update(SQL_UPDATE_BLOG_ENTRY, editedBlogEntry.getAuthorName(), new java.util.Date(), editedBlogEntry.getTitle(), editedBlogEntry.getBody(), editedBlogEntry.getCategory(), editedBlogEntry.getId()); } private static final String SQL_DELETE_BLOG_ENTRY = "DELETE FROM BlogEntries WHERE id = ?"; @Override @Transactional(propagation = Propagation.REQUIRED, readOnly = false) public void removeBlogEntry(int blogId) { removeAllComments(blogId); jdbcTemplate.update(SQL_DELETE_BLOG_ENTRY, blogId); } private static final String SQL_DELETE_COMMENT = "DELETE FROM Comments WHERE id= ?"; @Override public void removeComment(int commentId) { jdbcTemplate.update(SQL_DELETE_COMMENT, commentId); } private static final String SQL_DELETE_ALL_COMMENTS_FOR_A_BLOG_ENTRY = "DELETE FROM Comments WHERE blogEntryId = ?"; private void removeAllComments(int blogEntryId) { jdbcTemplate.update(SQL_DELETE_ALL_COMMENTS_FOR_A_BLOG_ENTRY, blogEntryId); } private static final String SQL_SEARCH_BODY = "SELECT * FROM BlogEntries WHERE blogBody LIKE '?'"; private static final String SQL_SEARCH_TITLE = "SELECT * FROM BlogEntries WHERE title LIKE '?'"; private static final String SQL_GET_BLOG_SUMMARIES = "SELECT BlogEntries.id as blogID, author, blogTimestamp, title, COUNT(Comments.blogEntryId) as commentCount, approvalStatus\n" + "FROM BlogEntries\n" + "LEFT JOIN Comments ON Comments.blogEntryId = BlogEntries.id\n" + "GROUP BY BlogEntries.id\n" + "ORDER BY blogTimestamp DESC;"; @Override public List<BlogSummary> getBlogSummaries() { return jdbcTemplate.query(SQL_GET_BLOG_SUMMARIES, new BlogSummaryMapper()); } private static final String SQL_GET_ALL_CATEGORIES = "SELECT * FROM Categories"; @Override public List<Category> getAllCategories() { return jdbcTemplate.query(SQL_GET_ALL_CATEGORIES, new CategoryMapper()); } private static final String SQL_SEARCH_BY_CATEGORY = "SELECT * FROM Categories WHERE category LIKE ?"; @Override public List<Category> searchCategory(String searchCategory) { String categorySearch = ""; if (searchCategory == null || searchCategory.equals("")) { categorySearch = "%"; } else { categorySearch = "%" + searchCategory + "%"; } return jdbcTemplate.query(SQL_SEARCH_BY_CATEGORY, new CategoryMapper(), categorySearch); } private static final String SQL_SELECT_FROM_MULTIPLE_CRITERIA = "SELECT * FROM BlogEntries WHERE title LIKE ? OR blogBody LIKE ? OR author LIKE ? OR category LIKE ?"; public List<BlogEntry> searchBlogEntries(String searchTerm) { String titleCriteria = ""; String blogBodyCriteria = ""; String authorCriteria = ""; String categoryCriteria = ""; if (searchTerm == null || searchTerm.equals("")) { titleCriteria = "%"; blogBodyCriteria = "%"; authorCriteria = "%"; categoryCriteria = "%"; } else { titleCriteria = "%" + searchTerm + "%"; blogBodyCriteria = "%" + searchTerm + "%"; authorCriteria = "%" + searchTerm + "%"; categoryCriteria = "%" + searchTerm + "%"; } return jdbcTemplate.query(SQL_SELECT_FROM_MULTIPLE_CRITERIA, new BlogMapper(), titleCriteria, blogBodyCriteria, authorCriteria, categoryCriteria); } private static final String SQL_APPROVE_BLOG_ENTRY = "UPDATE BlogEntries SET approvalStatus = 'APPROVED' WHERE id = ?"; @Override @Transactional(propagation = Propagation.REQUIRED, readOnly = false) public void approveBlogEntry(int blogEntryId) { getBlogEntryById(blogEntryId).setApprovalStatus("APPROVED"); jdbcTemplate.update(SQL_APPROVE_BLOG_ENTRY, blogEntryId); } private static final class BlogMapper implements RowMapper<BlogEntry> { @Override public BlogEntry mapRow(ResultSet rs, int i) throws SQLException { BlogEntry newBlogEntry = new BlogEntry(); newBlogEntry.setId(rs.getInt("id")); newBlogEntry.setTitle(rs.getString("title")); newBlogEntry.setTimestamp(rs.getTimestamp("blogTimestamp")); newBlogEntry.setBody(rs.getString("blogBody")); newBlogEntry.setAuthorName(rs.getString("author")); newBlogEntry.setCategory(rs.getString("category")); return newBlogEntry; } } private static final class CommentMapper implements RowMapper<Comment> { @Override public Comment mapRow(ResultSet rs, int i) throws SQLException { Comment newComment = new Comment(); newComment.setId(rs.getInt("id")); newComment.setBlogEntryId(rs.getInt("blogEntryId")); newComment.setBody(rs.getString("body")); newComment.setName(rs.getString("cName")); newComment.setTimestamp(rs.getTimestamp("commentTimestamp")); return newComment; } } private static final class BlogSummaryMapper implements RowMapper<BlogSummary> { @Override public BlogSummary mapRow(ResultSet rs, int i) throws SQLException { BlogSummary newBlogSummary = new BlogSummary(); newBlogSummary.setBlogId(rs.getInt("blogID")); newBlogSummary.setTitle(rs.getString("title")); newBlogSummary.setAuthor(rs.getString("author")); newBlogSummary.setNumComments(rs.getInt("commentCount")); newBlogSummary.setDate(rs.getDate("blogTimestamp")); newBlogSummary.setApprovalStatus(rs.getString("approvalStatus")); //newBlogSummary.setCategory(rs.getString("category")); return newBlogSummary; } } private static final class CategoryMapper implements RowMapper<Category> { @Override public Category mapRow(ResultSet rs, int i) throws SQLException { Category newCategory = new Category(); newCategory.setId(rs.getInt("id")); newCategory.setCategory(rs.getString("category")); return newCategory; } } }