Homestay_app_houtai/mhotel/src/com/happy/dao/impl/ArticleTweetImplDao.java

package com.happy.dao.impl;

import com.happy.Model.*;
import com.happy.Until.SqlUtil;
import com.happy.Until.UUIDUtil;
import com.happy.dao.ArticleTweetDao;
import com.happy.vo.*;
import org.apache.commons.lang.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSourceUtils;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository("ArticleTweetDao")
public class ArticleTweetImplDao implements ArticleTweetDao {

    @Autowired
    private NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    public NamedParameterJdbcTemplate getNamedParameterJdbcTemplate() {
        return namedParameterJdbcTemplate;
    }

    public void setNamedParameterJdbcTemplate(NamedParameterJdbcTemplate namedParameterJdbcTemplate) {
        this.namedParameterJdbcTemplate = namedParameterJdbcTemplate;
    }

    @Override
    public ArticleTweet queryArticleById(String id, Integer userId) {
        String caseSql = userId == null ? "" : "case when ac.id is null then 0 else 1 end is_collect,";
        String leftSql = userId == null ? "" : "left join article_collect ac on ac.is_lose = 0 and ac.article_id = at.id and ac.user_id = " + userId + " ";

        String sql = "SELECT " + caseSql + "(select IFNULL(count(*),0) from article_collect where article_id = :id and is_lose = 0) as collect_num,hd.name as location_name,at.* " +
                "FROM article_tweet at " +
                "left join hotel_dict hd on hd.code = 10 and hd.id = at.location_id " +
                leftSql +
                "WHERE at.id = :id ";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("id", id);
        List<ArticleTweet> list = null;
        try {
            list = namedParameterJdbcTemplate.query(sql, sps,
                    new BeanPropertyRowMapper<>(ArticleTweet.class));
        } catch (Exception e) {
            e.printStackTrace();
        }
        if (list != null && list.size() > 0) {
            return list.get(0);
        }
        return null;
    }


    @Override
    public int insert(ArticleTweet articleTweet) {
        KeyHolder key = new GeneratedKeyHolder();
        String sql = "insert into `article_tweet` (user_id,user_name,user_photo,title,content,location_id,hotel_id,create_id,create_date,status,approve) " +
                "values(:userId,:userName,:userPhoto,:title,:content,:locationId,:hotelId,:createId,:createDate,:status,:approve) ";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("userId", articleTweet.getUserId());
        sps.addValue("userName", articleTweet.getUserName());
        sps.addValue("userPhoto", articleTweet.getUserPhoto());
        sps.addValue("title", articleTweet.getTitle());
        sps.addValue("content", articleTweet.getContent());
        sps.addValue("locationId", articleTweet.getLocationId());
        sps.addValue("hotelId", articleTweet.getHotelId());
        sps.addValue("createId", articleTweet.getCreateId());
        sps.addValue("createDate", articleTweet.getCreateDate());
        sps.addValue("status", articleTweet.getStatus());
        sps.addValue("approve", articleTweet.getApprove());


        int num = 0;
        try {
            num = namedParameterJdbcTemplate.update(sql, sps, key);
            num = key.getKey().intValue();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return num;
    }


    @Override
    public int updateArticleApprove(ArticleTweet articleTweet) {
        String sql = "update article_tweet set approve = :approve where id = :id";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("approve", articleTweet.getApprove());
        sps.addValue("id", articleTweet.getId());
        int num = 0;
        try {
            num = namedParameterJdbcTemplate.update(sql, sps);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return num;
    }

    @Override
    public int insertArticleFileBatch(List<ArticleFileInfo> articleFileInfos) {
        SqlParameterSource[] params = SqlParameterSourceUtils.createBatch(articleFileInfos.toArray());
        String insertSql = "insert into article_file_info (link_id, url, name ,create_date ,type) " +
                "VALUES (:linkId, :url, :name ,:createDate ,:type) ";
        int[] m = namedParameterJdbcTemplate.batchUpdate(insertSql, params);
        return m.length;
    }


    @Override
    public List<HotelVo> queryHotelPage(String sqlx, int page, int rows) {
        SqlUtil.filterKeyword(sqlx);
        int start = (page - 1) * rows;// 每页的起始下标
        String sql = "select h.id,h.hname as name,h.cover_img,h.score_hotel as score,am.type " +
                "from admin_manager am\n" +
                "inner join hotel h on h.status = 1 and h.manager_id = am.id\n" +
                "where am.status = 1 and am." + sqlx + " ORDER BY name asc limit :start,:rows ";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("start", start);
        sps.addValue("rows", rows);
        List<HotelVo> list = namedParameterJdbcTemplate.query(sql, sps,
                new BeanPropertyRowMapper<>(HotelVo.class));
        if (list != null && list.size() > 0) {
            return list;
        }
        return null;
    }

    @Override
    public int queryHotelTotal(String sqlx) {
        SqlUtil.filterKeyword(sqlx);
        String sql = "select count(*) from admin_manager am\n" +
                "inner join hotel h on h.status = 1 and h.manager_id = am.id\n" +
                "where am.status = 1 and am." + sqlx;
        MapSqlParameterSource sps = new MapSqlParameterSource();
        return namedParameterJdbcTemplate.queryForInt(sql, sps);
    }

    @Override
    public List<HotelVo> queryHotels(String sqlx) {
        SqlUtil.filterKeyword(sqlx);
        String sql = "select h.manager_id,(select IFNULL(count(*),0) from booking_comment where status = 1 and hotel_id = h.id) as comment,h.id,h.hname as name,h.cover_img,h.score_hotel as score,am.type " +
                "from hotel h\n" +
                "left join admin_manager am on am.status = 1 and h.manager_id = am.id\n" +
                "where h.status = 1 and h." + sqlx + " ORDER BY name asc limit 0,2";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        List<HotelVo> list = namedParameterJdbcTemplate.query(sql, sps,
                new BeanPropertyRowMapper<>(HotelVo.class));
        if (list != null && list.size() > 0) {
            return list;
        }
        return null;
    }

    @Override
    public List<HotelVo> queryHotelPageByHotleId(String sqlx, int page, int rows) {
        SqlUtil.filterKeyword(sqlx);
        int start = (page - 1) * rows;// 每页的起始下标
        String sql =
                "select h.manager_id,(select IFNULL(count(*),0) from booking_comment where status = 1 and hotel_id = h.id) as comment,h.id,h.hname as name,h.cover_img,h.score_hotel as score,am.type " +
                        "from hotel h\n" +
                        "left join admin_manager am on am.status = 1 and h.manager_id = am.id\n" +
                        "where h.status = 1 and h." + sqlx + " ORDER BY name asc limit :start,:rows ";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("start", start);
        sps.addValue("rows", rows);
        List<HotelVo> list = namedParameterJdbcTemplate.query(sql, sps,
                new BeanPropertyRowMapper<>(HotelVo.class));
        if (list != null && list.size() > 0) {
            return list;
        }
        return null;
    }

    @Override
    public int queryHotelTotalByHotleId(String sqlx) {
        SqlUtil.filterKeyword(sqlx);
        String sql = "select count(*) from hotel h\n" +
                "where h.status = 1 and h." + sqlx;
        MapSqlParameterSource sps = new MapSqlParameterSource();
        return namedParameterJdbcTemplate.queryForInt(sql, sps);
    }

    @Override
    public List<FileInfo> queryList(String sqlx) {
        SqlUtil.filterKeyword(sqlx);
        String sql = "SELECT id,link_id,url,name " +
                "FROM `article_file_info` WHERE type=1 " + sqlx;
        List<FileInfo> list = null;
        try {
            list = namedParameterJdbcTemplate.query(sql, new BeanPropertyRowMapper<>(FileInfo.class));
        } catch (Exception e) {
            e.printStackTrace();
        }
        if (list != null && list.size() > 0) {
            return list;
        }
        return null;
    }


    @Override
    public UserCollect queryUserCollect(Integer parentId, Integer userId) {
        String sql = "SELECT * FROM user_collect " +
                "WHERE is_lose=0 and parent_userid = :parentId and user_id = :userId";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("parentId", parentId);
        sps.addValue("userId", userId);
        List<UserCollect> list = null;
        try {
            list = namedParameterJdbcTemplate.query(sql, sps,
                    new BeanPropertyRowMapper<>(UserCollect.class));
        } catch (Exception e) {
            e.printStackTrace();
        }
        if (list != null && list.size() > 0) {
            return list.get(0);
        }
        return null;
    }


    @Override
    public List<LikeListVo> queryLikesPage(Integer articleId, int page, int rows) {
        int start = (page - 1) * rows;// 每页的起始下标
        String sql = "select (select IFNULL(count(*),0) from article_tweet where user_id = al.like_id) as article_number,(select IFNULL(count(*),0) from user_collect where is_lose = 0 and parent_userid = al.like_id) as fans_number,al.like_id as id,al.like_image as image,al.like_name as name " +
                "from article_likes al\n" +
                "where al.is_lose = 0 and al.article_id = :articleId order by al.create_date desc limit :start,:rows";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("start", start);
        sps.addValue("rows", rows);
        sps.addValue("articleId", articleId);
        List<LikeListVo> list = null;
        try {
            list = namedParameterJdbcTemplate.query(sql, sps, new BeanPropertyRowMapper<>(LikeListVo.class));
        } catch (Exception e) {
            e.printStackTrace();
        }
        if (list != null && list.size() > 0) {
            return list;
        }
        return null;
    }


    @Override
    public int queryLikesTotal(Integer articleId) {
        String sql ="select count(*) from article_likes " +
                "where is_lose = 0 and article_id = :articleId order by create_date desc";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("articleId", articleId);

        return namedParameterJdbcTemplate.queryForInt(sql, sps);
    }


    @Override
    public List<LikeListVo> queryArticleLikes(Integer articleId) {
        String sql = "select (select IFNULL(count(*),0) from article_tweet where user_id = al.like_id) as article_number,(select IFNULL(count(*),0) from user_collect where is_lose = 0 and parent_userid = al.like_id) as fans_number,al.like_id as id,al.like_image as image,al.like_name as name " +
                "from article_likes al\n" +
                "where al.is_lose = 0 and al.article_id = :articleId order by al.create_date desc";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("articleId", articleId);
        List<LikeListVo> list = null;
        try {
            list = namedParameterJdbcTemplate.query(sql, sps, new BeanPropertyRowMapper<>(LikeListVo.class));
        } catch (Exception e) {
            e.printStackTrace();
        }
        if (list != null && list.size() > 0) {
            return list;
        }
        return null;
    }


    @Override
    public List<ArticleCommentVo> queryArticleComment(Integer articleId) {
        String sql = "select id,comment_parent_id as parent_id,comment_id as user_id,comment_name as user_name,comment_image as image,content,create_date as date " +
                "from article_comment " +
                "where article_id = :articleId and comment_parent_id = 0 " +
                "ORDER BY create_date desc limit 0,5";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("articleId", articleId);
        List<ArticleCommentVo> list = null;
        try {
            list = namedParameterJdbcTemplate.query(sql, sps, new BeanPropertyRowMapper<>(ArticleCommentVo.class));
        } catch (Exception e) {
            e.printStackTrace();
        }
        if (list != null && list.size() > 0) {
            return list;
        }
        return null;
    }


    @Override
    public int queryArticleCommentTotal(Integer articleId) {
        String sql = "select Count(*) from article_comment " +
                "where article_id = :articleId";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("articleId", articleId);

        return namedParameterJdbcTemplate.queryForInt(sql, sps);
    }


    @Override
    public List<ArticleCommentVo> queryCommentPageByArticle(Integer articleId, int page, int rows) {
        int start = (page - 1) * rows;// 每页的起始下标
        String sql = "select id,comment_parent_id as parent_id,comment_id as user_id,comment_name as user_name,comment_image as image,content,create_date as date " +
                "from article_comment " +
                "where article_id = :articleId and comment_parent_id = 0 " +
                "ORDER BY create_date desc limit :start,:rows";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("start", start);
        sps.addValue("rows", rows);
        sps.addValue("articleId", articleId);
        List<ArticleCommentVo> list = namedParameterJdbcTemplate.query(sql, sps,
                new BeanPropertyRowMapper<>(ArticleCommentVo.class));
        if (list != null && list.size() > 0) {
            return list;
        }
        return null;
    }

    @Override
    public int queryCommentTotalByArticle(Integer articleId) {
        String sql = "select count(*) from article_comment " +
                "where article_id = :articleId and comment_parent_id = 0";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("articleId", articleId);
        return namedParameterJdbcTemplate.queryForInt(sql, sps);
    }


    @Override
    public List<ArticleCommentVo> queryCommentsByArticle(Integer articleId) {
        String sql = "select id,comment_parent_id as parent_id,comment_id as user_id,comment_name as user_name,comment_image as image,content,create_date as date " +
                "from article_comment " +
                "where article_id = :articleId and comment_parent_id > 0 " +
                "order BY create_date desc";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("articleId", articleId);
        List<ArticleCommentVo> list = namedParameterJdbcTemplate.query(sql, sps,
                new BeanPropertyRowMapper<>(ArticleCommentVo.class));
        if (list != null && list.size() > 0) {
            return list;
        }
        return null;
    }

    @Override
    public articleUserVo queryUserInfo(Integer userId) {
        String sql = "select id,head_photo as image,user_name,descript,\n" +
                "(select count(*) from user_collect where is_lose = 0 and parent_userid = :userId) as fans_num,\n" +
                "(select count(*) from user_collect where is_lose = 0 and user_id = :userId) as follow_num,\n" +
                "(select count(*) from article_likes where is_lose = 0 and article_id in (select id from article_tweet where user_id = :userId)) as like_num\n" +
                "from users where id = :userId";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("userId", userId);
        List<articleUserVo> list = null;
        try {
            list = namedParameterJdbcTemplate.query(sql, sps,
                    new BeanPropertyRowMapper<>(articleUserVo.class));
        } catch (Exception e) {
            e.printStackTrace();
        }
        if (list != null && list.size() > 0) {
            return list.get(0);
        }
        return null;

    }

    @Override
    public List<OwnerArticleVo> queryOwnerArticlePage(Integer userId, String sqlWhere, int page, int rows) {
        int start = (page - 1) * rows;// 每页的起始下标
        String sql = "select case when al.id is null then 0 else 1 end is_like,at.approve,at.id,at.title,at.create_date as date,at.location_id as town_id,\n" +
                "hd.name as town_name,(select count(*) from article_likes where article_id = at.id and is_lose = 0) as like_num,\n" +
                "(select count(*) from article_comment where article_id = at.id) as comment_num\n" +
                "from article_tweet at\n" +
                "left join hotel_dict hd on hd.id = at.location_id\n" +
                "left join article_likes al on al.is_lose = 0 and al.article_id = at.id and al.like_id = :userId " +
                "where at.user_id = :userId " + sqlWhere +
                "ORDER BY at.create_date desc limit :start,:rows";

        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("start", start);
        sps.addValue("rows", rows);
        sps.addValue("userId", userId);
        List<OwnerArticleVo> list = namedParameterJdbcTemplate.query(sql, sps,
                new BeanPropertyRowMapper<>(OwnerArticleVo.class));
        if (list != null && list.size() > 0) {
            return list;
        }
        return null;
    }

    @Override
    public int queryOwnerArticleTotal(Integer userId, String sqlWhere) {
        String sql = "select count(*) from article_tweet at \n" +
                "where at.user_id = :userId " + sqlWhere;

        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("userId", userId);

        return namedParameterJdbcTemplate.queryForInt(sql, sps);
    }

    @Override
    public List<ArticleListVo> queryArticlesPage(String keyWord, String townId, Integer userId, Integer type, int page, int rows) {
        int start = (page - 1) * rows;// 每页的起始下标

        String innSql = "";
        String leftSql = "";
        String caseSql = "";
        if (type.intValue() == 0) {//发现
            caseSql = "case when ac.id is null then 0 else 1 end is_collect,";
            leftSql = "left join article_collect ac on ac.is_lose = 0 and ac.article_id = at.id and ac.user_id = :userId\n";
        } else if (type.intValue() == 1) {//关注
            caseSql = "case when ac.id is null then 0 else 1 end is_collect,";
            leftSql = "left join article_collect ac on ac.is_lose = 0 and ac.article_id = at.id and ac.user_id = :userId\n";
            innSql = "inner join user_collect uc on uc.is_lose = 0 and uc.parent_userid = at.user_id and uc.user_id = :userId\n";//关注
        } else if (type.intValue() == 2) {//收藏
            caseSql = "1 as is_collect,";
            innSql = "inner join article_collect acc on acc.is_lose = 0 and acc.article_id = at.id and acc.user_id = :userId\n";//收藏
        }

        String sqlWhere = townId == null ? "" : "and at.location_id = '" + townId + "' ";
        sqlWhere = sqlWhere + (keyWord == null ? "" : "and (at.user_name like '%" + keyWord + "%' or at.title like '%" + keyWord + "%' or at.content like '%" + keyWord + "%') ");
        String sql = "select at.id,at.location_id as town_id,hd.name as town_name,at.title,at.user_id,at.user_name,at.user_photo,\n" + caseSql +
                "(select count(*) from article_collect where is_lose = 0 and article_id = at.id) as collect_num\n" +
                "from article_tweet at\n" +
                "left join hotel_dict hd on hd.id = at.location_id\n" +
                leftSql +
                innSql +
                "where at.approve = 2 " + sqlWhere +
                "order by at.create_date desc ";

        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("start", start);
        sps.addValue("rows", rows);
        sps.addValue("userId", userId);
        List<ArticleListVo> list = namedParameterJdbcTemplate.query(sql, sps,
                new BeanPropertyRowMapper<>(ArticleListVo.class));
        if (list != null && list.size() > 0) {
            return list;
        }
        return null;
    }

    @Override
    public int queryArticlesTotal(String keyWord, String townId, Integer userId, Integer type) {

        String innSql = "";
        if (type.intValue() == 1) {//关注
            innSql = "inner join user_collect uc on uc.is_lose = 0 and uc.parent_userid = at.user_id and uc.user_id = :userId\n";//关注
        } else if (type.intValue() == 2) {//收藏
            innSql = "inner join article_collect acc on acc.is_lose = 0 and acc.article_id = at.id and acc.user_id = :userId\n";//收藏
        }

        String sqlWhere = townId == null ? "" : "and at.location_id = '" + townId + "' ";
        sqlWhere = sqlWhere + (keyWord == null ? "" : "and (at.user_name like '%" + keyWord + "%' or at.title like '%" + keyWord + "%' or at.content like '%" + keyWord + "%') ");
        String sql = "select count(*) from article_tweet at\n" +
                innSql +
                "where at.approve = 2 " + sqlWhere;

        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("userId", userId);
        sps.addValue("townId", townId);
        return namedParameterJdbcTemplate.queryForInt(sql, sps);
    }


    @Override
    public List<ArticleListVo> relatedTweetPage(Integer userId, String townId, Integer articleId, int page, int rows) {
        int start = (page - 1) * rows;// 每页的起始下标

        String sql = "select at.id,at.location_id as town_id,hd.name as town_name,at.title,at.user_id,at.user_name,at.user_photo,case when ac.id is null then 0 else 1 end is_collect,\n" +
                "(select count(*) from article_collect where is_lose = 0 and article_id = at.id) as collect_num\n" +
                "from article_tweet at\n" +
                "left join hotel_dict hd on hd.id = at.location_id\n" +
                "left join article_collect ac on ac.is_lose = 0 and ac.article_id = at.id and ac.user_id = :userId\n" +
                "where at.approve = 2 and at.location_id = :townId and at.id != :articleId\n" +
                "order by at.create_date desc ";

        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("start", start);
        sps.addValue("rows", rows);
        sps.addValue("userId", userId);
        sps.addValue("townId", townId);
        sps.addValue("articleId", articleId);
        List<ArticleListVo> list = namedParameterJdbcTemplate.query(sql, sps,
                new BeanPropertyRowMapper<>(ArticleListVo.class));
        if (list != null && list.size() > 0) {
            return list;
        }
        return null;
    }

    @Override
    public int relatedTweetTotal(String townId, Integer articleId) {

        String sql = "select count(*) from article_tweet at\n" +
                "where at.approve = 2 and at.location_id = :townId and at.id != :articleId";

        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("townId", townId);
        sps.addValue("articleId", articleId);
        return namedParameterJdbcTemplate.queryForInt(sql, sps);
    }


    @Override
    public ArticleCollect queryArticleCollect(Integer articleId, Integer userId) {
        String sql = "select * from article_collect\n" +
                "where article_id = :articleId and user_id = :userId ";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("articleId", articleId);
        sps.addValue("userId", userId);
        List<ArticleCollect> list = null;
        try {
            list = namedParameterJdbcTemplate.query(sql, sps,
                    new BeanPropertyRowMapper<>(ArticleCollect.class));
        } catch (Exception e) {
            e.printStackTrace();
        }
        if (list != null && list.size() > 0) {
            return list.get(0);
        }
        return null;

    }

    @Override
    public int updateArticleCollect(ArticleCollect articleCollect) {
        String sql = "";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        if (articleCollect.getId() == null) {
            sql = "insert into article_collect (article_id,user_id,is_lose,create_id,create_date,status) " +
                    "values(:articleId,:userId,:isLose,:createId,:createDate,:status) ";

            sps.addValue("articleId", articleCollect.getArticleId());
            sps.addValue("userId", articleCollect.getUserId());
            sps.addValue("isLose", articleCollect.getIsLose());
            sps.addValue("createId", articleCollect.getCreateId());
            sps.addValue("createDate", articleCollect.getCreateDate());
            sps.addValue("status", articleCollect.getStatus());
        } else {
            sql = "update article_collect set is_lose = :isLose where id = :id";
            sps.addValue("isLose", articleCollect.getIsLose());
            sps.addValue("id", articleCollect.getId());
        }

        int num = 0;
        try {
            num = namedParameterJdbcTemplate.update(sql, sps);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return num;
    }

    @Override
    public int insertArticleComment(ArticleComment articleComment) {
        MapSqlParameterSource sps = new MapSqlParameterSource();
        String sql = "insert into article_comment (article_id,comment_parent_id,comment_id,comment_name,comment_image,content,create_id,create_date,status) " +
                "values(:articleId,:commentParentId,:commentId,:commentName,:commentImage,:content,:createId,:createDate,:status) ";

        sps.addValue("articleId", articleComment.getArticleId());
        sps.addValue("commentParentId", articleComment.getCommentParentId());
        sps.addValue("commentId", articleComment.getCommentId());
        sps.addValue("commentName", articleComment.getCommentName());
        sps.addValue("commentImage", articleComment.getCommentImage());
        sps.addValue("content", articleComment.getContent());
        sps.addValue("createId", articleComment.getCreateId());
        sps.addValue("createDate", articleComment.getCreateDate());
        sps.addValue("status", articleComment.getStatus());

        int num = 0;
        try {
            num = namedParameterJdbcTemplate.update(sql, sps);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return num;
    }


    @Override
    public UserCollect queryUserCollectNoLose(Integer authorId, Integer userId) {
        String sql = "select * from user_collect\n" +
                "where parent_userid = :authorId and user_id = :userId ";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("authorId", authorId);
        sps.addValue("userId", userId);
        List<UserCollect> list = null;
        try {
            list = namedParameterJdbcTemplate.query(sql, sps,
                    new BeanPropertyRowMapper<>(UserCollect.class));
        } catch (Exception e) {
            e.printStackTrace();
        }
        if (list != null && list.size() > 0) {
            return list.get(0);
        }
        return null;

    }

    @Override
    public int updateUserCollect(UserCollect userCollect) {
        String sql = "";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        if (userCollect.getId() == null) {
            sql = "insert into user_collect (parent_userid,user_id,is_lose,create_id,create_date,status) " +
                    "values(:parentUserid,:userId,:isLose,:createId,:createDate,:status) ";

            sps.addValue("parentUserid", userCollect.getParentUserid());
            sps.addValue("userId", userCollect.getUserId());
            sps.addValue("isLose", userCollect.getIsLose());
            sps.addValue("createId", userCollect.getCreateId());
            sps.addValue("createDate", userCollect.getCreateDate());
            sps.addValue("status", userCollect.getStatus());
        } else {
            sql = "update user_collect set is_lose = :isLose where id = :id";
            sps.addValue("isLose", userCollect.getIsLose());
            sps.addValue("id", userCollect.getId());
        }

        int num = 0;
        try {
            num = namedParameterJdbcTemplate.update(sql, sps);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return num;
    }

    @Override
    public ArticleLikes queryArticleLike(Integer articleId, Integer userId) {
        String sql = "select * from article_likes\n" +
                "where article_id = :articleId and like_id = :userId ";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("articleId", articleId);
        sps.addValue("userId", userId);
        List<ArticleLikes> list = null;
        try {
            list = namedParameterJdbcTemplate.query(sql, sps,
                    new BeanPropertyRowMapper<>(ArticleLikes.class));
        } catch (Exception e) {
            e.printStackTrace();
        }
        if (list != null && list.size() > 0) {
            return list.get(0);
        }
        return null;

    }

    @Override
    public int updateArticleLike(ArticleLikes articleLikes) {
        String sql = "";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        if (articleLikes.getId() == null) {
            sql = "insert into article_likes (article_id,like_id,like_name,like_image,is_lose,create_id,create_date,status) " +
                    "values(:articleId,:likeId,:likeName,:likeImage,:isLose,:createId,:createDate,:status) ";

            sps.addValue("articleId", articleLikes.getArticleId());
            sps.addValue("likeId", articleLikes.getLikeId());
            sps.addValue("likeName", articleLikes.getLikeName());
            sps.addValue("likeImage", articleLikes.getLikeImage());
            sps.addValue("isLose", articleLikes.getIsLose());
            sps.addValue("createId", articleLikes.getCreateId());
            sps.addValue("createDate", articleLikes.getCreateDate());
            sps.addValue("status", articleLikes.getStatus());
        } else {
            sql = "update article_likes set is_lose = :isLose where id = :id";
            sps.addValue("isLose", articleLikes.getIsLose());
            sps.addValue("id", articleLikes.getId());
        }

        int num = 0;
        try {
            num = namedParameterJdbcTemplate.update(sql, sps);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return num;
    }


    @Override
    public int updateDescript(Integer authorId, String descript) {
        String sql = "update users set descript = :descript where id = :authorId";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("descript", descript);
        sps.addValue("authorId", authorId);

        int num = 0;
        try {
            num = namedParameterJdbcTemplate.update(sql, sps);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return num;
    }


    /**
     * 获取点赞数最多的最新推文数据
     */
    @Override
    public ArticleLikeMaxVo queryMaxArticleLike() {
        String sql = "select t.id,t.townId,t.townName,t.title,t.content from (select at.id,at.location_id as townId,hd.name as townName,at.title,at.content,(select Count(*) from article_likes where is_lose = 0 and article_id = at.id) as like_num,at.create_date\n" +
                     "from article_tweet at\n" +
                     "left join hotel_dict hd on hd.id = at.location_id\n" +
                     "where at.approve = 2) t\n" +
                     "order by t.like_num desc,t.create_date desc LIMIT 1";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        List<ArticleLikeMaxVo> list = null;
        try {
            list = namedParameterJdbcTemplate.query(sql, sps,
                    new BeanPropertyRowMapper<>(ArticleLikeMaxVo.class));
        } catch (Exception e) {
            e.printStackTrace();
        }
        if (list != null && list.size() > 0) {
            return list.get(0);
        }
        return null;

    }

    @Override
    public List<WalkthroughVo> walkthroughPage(String strSql, Integer page, Integer rows) {
        int start = (page - 1) * rows;// 每页的起始下标

        String sql=" SELECT AT.id AS id, AT.location_id AS locationId, AT\n" +
                "\t.user_name AS userName,\n" +
                "\tAT.title AS title,\n" +
                "\tAT.content AS content,\n" +
                "\tAT.create_date AS createDate,\n" +
                "\tafi2.urls AS urls \n" +
                "FROM\n" +
                "\t`article_tweet`\n" +
                "\tAT LEFT JOIN ( SELECT afi.link_id, GROUP_CONCAT( afi.url ) AS urls FROM article_file_info afi GROUP BY afi.link_id ) afi2 ON afi2.link_id = AT.id \n" +
                "WHERE\n" +
                "\tAT.approve = 2 " +strSql+"ORDER BY AT.create_date limit :page,:rows ";

        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("page", start);
        sps.addValue("rows", rows);
        List<WalkthroughVo> list = namedParameterJdbcTemplate.query(sql, sps,
                new BeanPropertyRowMapper<>(WalkthroughVo.class));
        if (list != null && list.size() > 0) return list;
        return null;
    }

    @Override
    public int walkthroughTotal(String strSql) {
        String sql="SELECT \n" +
                "\tCOUNT(*)\n" +
                "FROM\n" +
                "\t`article_tweet`\n" +
                "\tAT LEFT JOIN ( SELECT afi.link_id, GROUP_CONCAT( afi.url ) AS urls FROM article_file_info afi GROUP BY afi.link_id ) afi2 ON afi2.link_id = AT.id \n" +
                "WHERE\n" +
                "\tAT.approve = 2 " +strSql;

        MapSqlParameterSource sps = new MapSqlParameterSource();

        return namedParameterJdbcTemplate.queryForInt(sql, sps);
    }


}