jdbcTemplate.query的写法

 public PageVO<ArticleVO> queryArticlePage(String searchKeyword, Integer current, Integer size){
        StringBuilder sql = new StringBuilder(
                "  from cms_article ca \n" +
                "  left join app_thumb at on ca.article_id = at.type_id and at.type = 2 and at.status = 1 \n" +
                " where ca.del_flag = 0 \n");

        List<Object> param = Lists.newArrayList();

        if(StringUtils.isNotEmpty(searchKeyword)){
            sql.append(" and ca.title like ? ");
            param.add("%" + searchKeyword + "%");
        }

        sql.append(" group by ca.article_id");

        StringBuilder sql1 = new StringBuilder("select count(1) from (").append("select ca.article_id ").append(sql).append(") tt");
        //获取总记录
        Integer count = jdbcTemplate.queryForObject(sql1.toString(), param.toArray(), Integer.class);
        logger.info("queryArticlePageCount sql1: {}", sql1);

        sql.append(" order by ca.create_date desc ");

        StringBuilder sql2 = new StringBuilder("select ca.*, count(at.thumb_id) as thumb_count ")
                .append(sql).append(" limit ").append(current * size).append(",").append(size);
        logger.info("queryArticlePage sql2: {}", sql2);
        List<ArticleVO> list = jdbcTemplate.query(sql2.toString(), param.toArray(), ARTICLE_MANAGE_ROW_MAPPER);
        return new PageVO(count, list);
    }

 

    private static final RowMapper<ArticleVO> ARTICLE_MANAGE_ROW_MAPPER = (rs, rowNum) -> {
        ArticleVO vo = new ArticleVO();
        vo.setArticleId(rs.getLong("article_id"));
        vo.setAppId(rs.getLong("app_id"));
        vo.setContentTxt(ObjectUtils.toStringByBlob(rs.getBlob("content_txt")));
        Integer readCount = rs.getInt("read_count");
        vo.setReadCount(readCount == null ? 0 : readCount);
        vo.setSort(rs.getInt("sort"));
        vo.setSpecialId(rs.getLong("special_id"));
        vo.setStatus(rs.getInt("status"));
        vo.setTitle(rs.getString("title"));
        vo.setType(rs.getInt("type"));
        String updateDateStr = rs.getString("update_date");
        Date updateDate = StringUtils.isNotEmpty(updateDateStr) ? DateUtil.parseDateTime(updateDateStr) : null;
        vo.setUpdateDate(updateDate);
        vo.setThumbCount(rs.getInt("thumb_count"));
        return vo;
    };

 

package com.comtop.map.store.mobile.bean.response;

import com.comtop.map.store.mobile.entity.ArticleEntity;
import com.comtop.map.store.mobile.utils.BusinessUtils;
import com.comtop.map.store.mobile.utils.HttpContextUtils;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
import java.util.Date;

@ApiModel(description = "动态")
@Data
public class ArticleVO {

    /** 主键 */
    @ApiModelProperty("主键")
    private Long articleId;

    /** 专题id 不是专题时id为空-1 */
    @ApiModelProperty("专题id 不是专题时id为空-1")
    private Long specialId;

    @ApiModelProperty("专题图片")
    private String specialLogo;

    /** 专题名称 */
    @ApiModelProperty("专题名称")
    private String specialName;

    /** 应用id */
    @ApiModelProperty("应用id")
    private Long appId;

    /** 应用名称 */
    @ApiModelProperty("应用名称")
    private String appName;

    /** 阅读数 */
    @ApiModelProperty("阅读数")
    private Integer readCount;

    /** 内容-h5 */
    @ApiModelProperty("内容-h5")
    private String contentHtml;

    /** 标题 */
    @ApiModelProperty("标题")
    private String title;

    /** 排序 */
    @ApiModelProperty("排序")
    private Integer sort;

    /** 发布时间 */
    @ApiModelProperty("发布时间")
    private Date publishTime;

    /** 置顶 1置顶 0不置顶 */
    @ApiModelProperty("置顶 1置顶 0不置顶")
    private Integer top;

    /** 状态 */
    @ApiModelProperty("状态")
    private Integer status;

    /** 文章类型 1官方公告 2.专题系统 3.活动宣传 4.应用征集 */
    @ApiModelProperty("文章类型 1官方公告 2.专题系统 3.活动宣传 4.应用征集")
    private Integer type;

    @ApiModelProperty("类型名称")
    private String typeName;

    /** 封面logo */
    @ApiModelProperty("封面logo")
    private String articleLogo;

    @ApiModelProperty("logo文件编号")
    private String logoFileId;

    /** 动态内容，无格式 */
    @ApiModelProperty("动态内容，无格式")
    private String contentTxt;

    /** 评论数 */
    @ApiModelProperty("评论数")
    private Integer commentCount;

    /** 点赞数 */
    @ApiModelProperty("点赞数")
    private int thumbCount;

    /** 修改时间 */
    @ApiModelProperty("修改时间")
    private Date updateDate;

}