com.sg.capstone.dao.BlogDaoDbImpl.java Source code

Java tutorial

Introduction

Here is the source code for com.sg.capstone.dao.BlogDaoDbImpl.java

Source

/*
 * 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.sg.capstone.dao;

import com.sg.capstone.model.BlogPost;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.TreeMap;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

/**
 *
 * @author apprentice
 */
public class BlogDaoDbImpl implements BlogDao {

    private JdbcTemplate jdbcTemplate;

    public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    private static final String SQL_INSERT_BLOGPOST = "insert into blogPost (date, title, imagePath, blogText, isApproved) values (?, ?, ?, ?, 0)";
    private static final String SQL_DELETE_BLOGPOST = "delete from blogPost where blogPostId = ?";
    private static final String SQL_UPDATE_BLOGPOST = "update blogPost set date = ?, title = ?, imagePath = ?, blogText = ?, isApproved = ? where blogPostId= ?";
    private static final String SQL_SELECT_BLOGPOST = "select * from blogPost where blogPostId=?";
    private static final String SQL_SELECT_ALL_APPROVED_BLOGPOSTS = "select * from blogPost where isApproved= 1 order by `date` desc";
    private static final String SQL_APPROVE_BLOGPOST = "UPDATE blogPost SET isApproved=1 WHERE blogPostId = ?;";
    private static final String SQL_UNAPPROVE_BLOGPOST = "UPDATE blogPost SET isApproved=0 WHERE blogPostId = ?;";
    private static final String SQL_SELECT_ALL_BLOGPOST = "select * from blogPost order by `date` desc";
    private static final String SQL_SELECT_ALL_BLOGPOST_SUMMARY = "select `date`, `title`, `blogPostId`, `imagePath`, `isApproved`, substring(`blogText` , 1, 400) as `blogText` FROM `blogPost` WHERE isApproved = 1 order by `date` desc";
    private static final String SQL_SELECT_ALL_BLOGPOST_LIMITED_INFO = "select bp.blogPostId, bp.date, bp.title, bp.isApproved from blogPost bp";
    private static final String SQL_SELECT_ALL_BLOGPOST_BY_DATE = "select `date`, `title`, `blogPostId`, `imagePath`, `isApproved`, substring(`blogText` , 1, 400) as `blogText` FROM blogPost WHERE MONTH(`date`) = ? AND YEAR(`date`) = ? AND isApproved = 1 order by `date` desc";
    private static final String SQL_SELECT_ALL_BLOGPOST_BY_CATEGORY = "select bp.blogPostId, bp.date, bp.title, bp.imagePath, substring(`blogText` , 1, 400) as `blogText`, bp.isApproved from blogPost bp "
            + "join blogPost_categories cg on bp.blogPostId = cg.blogPostId where cg.categoryId = ? AND isApproved = 1 order by `date` desc";
    private static final String SQL_SELECT_ALL_BLOGPOST_BY_TAG = "select bp.blogPostId, bp.date, bp.title, bp.imagePath, substring(`blogText` , 1, 400) as `blogText`, bp.isApproved from blogPost bp "
            + "join blogPost_tags bpt on bp.blogPostId = bpt.blogPostId where bpt.tagId=? AND isApproved = 1 order by `date` desc";
    private static final String SQL_SEARCH_BLOGPOST_BY_KEYWORD = "select `date`, `title`, `blogPostId`, `imagePath`, `isApproved`, substring(`blogText` , 1, 400) as `blogText` from blogPost where (title like ? or blogText like ?) AND isApproved = 1 order by `date` desc";
    private static final String SQL_SELECT_ALL_UNIQUE_YEARS = "select year(date) as `year` from blogPost WHERE isApproved = 1 group by year(date) order by `year` desc";
    private static final String SQL_SELECT_ALL_UNIQUE_YEAR_MONTHS = "SELECT DISTINCT CONCAT (YEAR(`date`), ':', MONTHNAME(`date`)) AS `MonthYear` "
            + "FROM blogPost WHERE isApproved = 1 ORDER BY `date` asc";

    private static final String SQL_INSERT_BLOGPOST_TAGS = "insert into blogPost_tags (blogPostId, tagId) values(?, ?)";
    private static final String SQL_DELETE_BLOGPOST_TAGS = "delete from blogPost_tags where blogPostId = ?";
    private static final String SQL_SELECT_BLOGPOST_TAG_IDS_BY_BLOGPOST_ID = "select tagId from blogPost_tags where blogPostId = ?";

    private static final String SQL_INSERT_BLOGPOST_CATEGORIES = "insert into blogPost_categories (blogPostId, categoryId) values(?, ?)";
    private static final String SQL_DELETE_BLOGPOST_CATEGORIES = "delete from blogPost_categories where blogPostId = ?";
    private static final String SQL_SELECT_BLOGPOST_CATEGORY_IDS_BY_BLOGPOST_ID = "select categoryId from blogPost_categories where blogPostId = ?";

    @Override
    public void addBlogPost(BlogPost post) {
        jdbcTemplate.update(SQL_INSERT_BLOGPOST, post.getDate(), post.getTitle(), post.getImagePath(),
                post.getBlogText());
        post.setBlogPostId(jdbcTemplate.queryForObject("select LAST_INSERT_ID()", Integer.class));
        insertBlogPostTags(post);
        insertBlogPostCategories(post);
    }

    private void insertBlogPostTags(BlogPost blogPost) {
        final int blogPostId = blogPost.getBlogPostId();
        final ArrayList<Integer> tagIds = blogPost.getTagIds();

        jdbcTemplate.batchUpdate(SQL_INSERT_BLOGPOST_TAGS, new BatchPreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps, int i) throws SQLException {
                ps.setInt(1, blogPostId);
                ps.setInt(2, tagIds.get(i));
            }

            @Override
            public int getBatchSize() {
                return tagIds.size();
            }
        });
    }

    private void insertBlogPostCategories(BlogPost blogPost) {
        final int blogPostId = blogPost.getBlogPostId();
        final ArrayList<Integer> categoryIds = blogPost.getCategoryIds();

        jdbcTemplate.batchUpdate(SQL_INSERT_BLOGPOST_CATEGORIES, new BatchPreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps, int i) throws SQLException {
                ps.setInt(1, blogPostId);
                ps.setInt(2, categoryIds.get(i));
            }

            @Override
            public int getBatchSize() {
                return categoryIds.size();
            }
        });
    }

    private void deleteBlogPostTags(BlogPost blogPost) {
        jdbcTemplate.update(SQL_DELETE_BLOGPOST_TAGS, blogPost.getBlogPostId());
    }

    private void deleteBlogPostCategories(BlogPost blogPost) {
        jdbcTemplate.update(SQL_DELETE_BLOGPOST_CATEGORIES, blogPost.getBlogPostId());
    }

    @Override
    public BlogPost getBlogPostById(int blogPostId) {
        try {
            return jdbcTemplate.queryForObject(SQL_SELECT_BLOGPOST, new blogPostMapper(), blogPostId);
        } catch (EmptyResultDataAccessException ex) {
            return null;
        }
    }

    @Override
    public void updateBlogPost(BlogPost post) {
        jdbcTemplate.update(SQL_UPDATE_BLOGPOST, post.getDate(), post.getTitle(), post.getImagePath(),
                post.getBlogText(), post.getIsApproved(), post.getBlogPostId());
        deleteBlogPostTags(post);
        deleteBlogPostCategories(post);
        insertBlogPostTags(post);
        insertBlogPostCategories(post);
    }

    @Override
    public void deleteBlogPost(int blogPostId) {
        jdbcTemplate.update(SQL_DELETE_BLOGPOST_CATEGORIES, blogPostId);
        jdbcTemplate.update(SQL_DELETE_BLOGPOST_TAGS, blogPostId);
        jdbcTemplate.update(SQL_DELETE_BLOGPOST, blogPostId);
    }

    @Override
    public List<BlogPost> getAllBlogPosts() {
        return jdbcTemplate.query(SQL_SELECT_ALL_BLOGPOST, new blogPostMapper());
    }

    @Override
    public List<BlogPost> getAllBlogPosts_WithSummary() {
        return jdbcTemplate.query(SQL_SELECT_ALL_BLOGPOST_SUMMARY, new blogPostMapper());
    }

    @Override
    public List<BlogPost> getAllApprovedBlogPosts() {
        return jdbcTemplate.query(SQL_SELECT_ALL_APPROVED_BLOGPOSTS, new blogPostMapper());
    }

    @Override
    public List<BlogPost> getAllBlogPostsByDate(int monthInt, int year) {
        return jdbcTemplate.query(SQL_SELECT_ALL_BLOGPOST_BY_DATE, new blogPostMapper(), monthInt, year);
    }

    @Override
    public List<BlogPost> getAllBlogPostsByCategoryId(int categoryId) {
        return jdbcTemplate.query(SQL_SELECT_ALL_BLOGPOST_BY_CATEGORY, new blogPostMapper(), categoryId);
    }

    @Override
    public List<BlogPost> getAllBlogPostsByTagId(int tagId) {
        return jdbcTemplate.query(SQL_SELECT_ALL_BLOGPOST_BY_TAG, new blogPostMapper(), tagId);
    }

    @Override
    public List<BlogPost> searchBlogPostsByKeyword(String searchCriteria) {
        searchCriteria = "%" + searchCriteria + "%";
        return jdbcTemplate.query(SQL_SEARCH_BLOGPOST_BY_KEYWORD, new blogPostMapper(), searchCriteria,
                searchCriteria);
    }

    @Override
    public List<Integer> getAllTagIdsByBlogPostId(int blogPostId) {
        return jdbcTemplate.query(SQL_SELECT_BLOGPOST_TAG_IDS_BY_BLOGPOST_ID, new tagIdMapper(), blogPostId);
    }

    @Override
    public List<Integer> getAllCategoryIdsByBlogPostId(int blogPostId) {
        return jdbcTemplate.query(SQL_SELECT_BLOGPOST_CATEGORY_IDS_BY_BLOGPOST_ID, new categoryIdMapper(),
                blogPostId);
    }

    @Override
    public List<Integer> getAllUniqueYears() {
        // get the list of unique blogPost years
        List<Integer> uniqueYears = jdbcTemplate.queryForList(SQL_SELECT_ALL_UNIQUE_YEARS, Integer.class);
        return uniqueYears;
    }

    @Override
    public Map<Integer, List<String>> getAllUniqueYearMonths() {

        // create map that will have year as key and months as values
        TreeMap<Integer, List<String>> yearMonthsMap = new TreeMap<>();

        // get the list of unique blogPost years to be used as reference for sorting months       
        List<Integer> yearList = getAllUniqueYears();

        // get SQL Year-Month results in form of year:month (ie. "2016:November")
        List<String> uniqueYearMonths_SqlResult = jdbcTemplate.queryForList(SQL_SELECT_ALL_UNIQUE_YEAR_MONTHS,
                String.class);

        // for loop to separate year:month string and then store in hashMap based on year        
        for (int year : yearList) {

            List<String> monthList = new ArrayList<>();

            for (String entry : uniqueYearMonths_SqlResult) {
                // split each year:month String to (ie.) ["2016", "November"]
                String[] currentYearMonthArray = entry.split(":");
                Integer currentYear = Integer.parseInt(currentYearMonthArray[0]);
                String currentMonth = currentYearMonthArray[1];

                if (currentYear == year) {
                    monthList.add(currentMonth);
                }
            }
            yearMonthsMap.put(year, monthList);
        }

        return yearMonthsMap.descendingMap();
    }

    @Override
    public List<BlogPost> getAllBlogPosts_LimitedInformation() {
        return jdbcTemplate.query(SQL_SELECT_ALL_BLOGPOST_LIMITED_INFO, new blogPostMapperLimited());
    }

    @Override
    public void approveBlogPost(int blogPostId) {
        jdbcTemplate.update(SQL_APPROVE_BLOGPOST, blogPostId);
    }

    @Override
    public void unapproveBlogPost(int blogPostId) {
        jdbcTemplate.update(SQL_UNAPPROVE_BLOGPOST, blogPostId);
    }

    private static final class blogPostMapper implements RowMapper<BlogPost> {

        public BlogPost mapRow(ResultSet rs, int i) throws SQLException {
            BlogPost bp = new BlogPost();
            bp.setTitle(rs.getString("title"));
            bp.setDate(rs.getString("date"));
            bp.setImagePath(rs.getString("imagePath"));
            bp.setBlogText(rs.getString("blogText"));
            bp.setBlogPostId(rs.getInt("blogpostId"));
            bp.setIsApproved(rs.getInt("isApproved"));
            return bp;
        }

    }

    private static final class blogPostMapperLimited implements RowMapper<BlogPost> {

        public BlogPost mapRow(ResultSet rs, int i) throws SQLException {
            BlogPost bp = new BlogPost();
            bp.setTitle(rs.getString("title"));
            bp.setDate(rs.getString("date"));
            bp.setBlogPostId(rs.getInt("blogpostId"));
            bp.setIsApproved(rs.getInt("isApproved"));
            return bp;
        }

    }

    private static final class categoryIdMapper implements RowMapper<Integer> {

        public Integer mapRow(ResultSet rs, int i) throws SQLException {
            return Integer.parseInt(rs.getString("categoryId"));
        }

    }

    private static final class tagIdMapper implements RowMapper<Integer> {

        public Integer mapRow(ResultSet rs, int i) throws SQLException {
            return Integer.parseInt(rs.getString("tagId"));
        }

    }
}