优雅的使用JdbcTemplate

一、前言

springboot项目中我个人喜欢用JPA，做表的保存或更新非常方便，对于简单的单表查询更是做到了面向对象的思想。但对于前端的列表展示查询，大多数据都是多表关联且还是动态条件，如果使用JPA到处理的话会有点吃力，且出问题不好排查，所以我用了JdbcTemplate替代了这个工作。
至于我为什么不用mybaits、tk mybatis、mybatisplus等，是因为我不喜欢和xml打交道，且大多数据前端列表查询是不可复用的，用原生SQL直接上更有效率上的优势。

二、用法

查询列表数据

@ApiOperation(value = "查询")
@GetMapping("query")
public Result<List<AdminConfigQueryVO>> cacheQuery(){
	String sql = "select a.*,ifnull(b.value,a.defaultValue) value from s_config a left join (select * from s_config_value where sid=?) b on a.id=b.configId ";
	List<AdminConfigQueryVO> configList = jdbcUtil.queryForList(sql,AdminConfigQueryVO.class, getSid());
	return Result.ofSuccess(configList);
}

分页查询 + 动态条件

@ApiOperation(value = "查询")
@GetMapping("query")
public Result<PageOut<AdminAdminQueryVO>> query(@Validated AdminAdminQueryDTO dto, @Validated PageIn pageIn){
	SqlJoin sqlJoin = SqlJoin.instance("select a.*,b.telephone,b.photo from admin a left join user b on a.userId=b.id where a.sid=?", getSid())
			.join("and a.enable=?", dto.getEnable())
			.join("and b.telephone like '%' ? '%'", dto.getTelephone())
			.join("and a.name like '%' ? '%'", dto.getName())
			.join("order by a.id desc");
	PageOut<AdminAdminQueryVO> pageOut = jdbcUtil.pageQuery(sqlJoin,AdminAdminQueryVO.class,pageIn);
	return Result.ofSuccess(pageOut);
}

三、工具类

类	作用
JdbcUtil	用JdbcTemplate封装的工具类，支持分页、列表、单个对象
SqlJoin	SQL拼接辅助类，主要用于动态SQL
PageIn	分页入参DTO
PageOut	分页返回VO

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.util.CollectionUtils;

import java.util.List;

/**
 * @desc:
 * @author: 猪猪侠
 * @date: 2019/11/20 20:06
 */
public class JdbcUtil{

    @Autowired
    JdbcTemplate jdbcTemplate;

    public JdbcTemplate jt() {
        return jdbcTemplate;
    }

    public <T> PageOut<T> pageQuery(SqlJoin sqlJoin, Class c, PageIn pageIn){
        return pageQuery(sqlJoin.getSql(),c, pageIn,sqlJoin.getArgs());
    }

    public <T> PageOut<T> pageQuery(String sql, Class c, PageIn pageIn, Object... args){
        String countSql ="select count(1) from  ( "+ sql +" ) temp";
        Long count = jdbcTemplate.queryForObject(countSql,args, Long.class);
        String contentSql = sql + " limit "+(pageIn.getPageNumber()-1)*pageIn.getPageSize()+","+pageIn.getPageSize();
        RowMapper<T> rowMapper = new BeanPropertyRowMapper<T>(c);
        List<T> data = jdbcTemplate.query(contentSql,rowMapper,args);
        PageOut pageOut = new PageOut();
        pageOut.setPageNumber(pageIn.getPageNumber());
        pageOut.setPageSize(pageIn.getPageSize());
        pageOut.setPageData(data);
        pageOut.setCurrentSize(data.size());
        pageOut.setTotalSize(count);
        Long totalPage = count%pageIn.getPageSize()==0L?count/pageIn.getPageSize():count/pageIn.getPageSize()+1;
        pageOut.setTotalPage(totalPage.intValue());
        return pageOut;
    }

    public <T> List<T> queryForList(String sql, Class<T> c, Object... args){
        RowMapper<T> rowMapper = new BeanPropertyRowMapper<>(c);
        List<T> data = jdbcTemplate.query(sql,rowMapper,args);
        return data;
    }

    public <T> List<T> queryForList(SqlJoin sqlJoin, Class<T> c){
        return queryForList(sqlJoin.getSql(), c, sqlJoin.getArgs());
    }

    public <T> T queryForOne(String sql, Class<T> c, Object... args) {
        List<T> list = queryForList(sql, c, args);
        return CollectionUtils.isEmpty(list) ? null:list.get(0);
    }
}

import org.springframework.util.StringUtils;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

/**
 * @Auther: 朱增亮
 * @Date: 2020/4/27 09:18
 * @Description:
 */
public class SqlJoin {

    private StringBuilder sqlSB;
    private List<Object> params;

    private SqlJoin(String sql, Object... args){
        this.sqlSB = new StringBuilder(sql);
        this.params = new ArrayList<>();
        this.params.addAll(Arrays.asList(args));
    }

    public static SqlJoin instance(String sql,Object... args){
        if(sql.indexOf("where")>-1){
            return new SqlJoin(sql ,args);
        }
        return new SqlJoin(sql + " where 1=1",args);
    }

    public SqlJoin join(String partSql, Object... args){
        if(args.length>0){
            for(int i=0;i<args.length;i++){
                if(StringUtils.isEmpty(args[i]))return this;
            }
        }
        this.sqlSB.append(" ").append(partSql);
        this.params.addAll(Arrays.asList(args));
        return this;
    }

    public SqlJoin flagJoin(String partSql, boolean flag, Object... args){
        if(flag){
            if(args.length>0){
                for(int i=0;i<args.length;i++){
                    if(StringUtils.isEmpty(args[i]))return this;
                }
            }
            this.sqlSB.append(" ").append(partSql);
            this.params.addAll(Arrays.asList(args));
        }
        return this;
    }

    public SqlJoin joinStrIn(String partSql, String arg){
        if(StringUtils.isEmpty(arg))return this;
        Object[] args = arg.split(",");
        StringBuilder SB = new StringBuilder();
        for(int i=0;i<args.length;i++){
            SB.append("?").append(",");
        }
        String sbStr = SB.substring(0,SB.length()-1);
        this.sqlSB.append(" ").append(partSql.replace("?",sbStr));
        this.params.addAll(Arrays.asList(args));
        return this;
    }

    public SqlJoin joinNumberIn(String partSql, String arg){
        if(StringUtils.isEmpty(arg))return this;
        String[] args = arg.split(",");
        Object[] args2 = new Object[args.length];
        StringBuilder SB = new StringBuilder();
        for(int i=0;i<args.length;i++){
            SB.append("?").append(",");
            args2[i] = Long.valueOf(args[i]);
        }
        String sbStr = SB.substring(0,SB.length()-1);
        this.sqlSB.append(" ").append(partSql.replace("?",sbStr));
        this.params.addAll(Arrays.asList(args2));
        return this;
    }

    public SqlJoin append(String partSql, Object... args){
        if(args.length>0){
            for(int i=0;i<args.length;i++){
                if(StringUtils.isEmpty(args[i]))return this;
                partSql = partSql.replaceFirst("\?",args[i]+"");
            }
        }
        this.sqlSB.append(" ").append(partSql);
        return this;
    }

    public SqlJoin appendNumberIn(String partSql, String arg){
        if(StringUtils.isEmpty(arg))return this;
        this.sqlSB.append(" ").append(partSql.replace("?",arg));
        return this;
    }

    public SqlJoin appendStrIn(String partSql, String arg){
        if(StringUtils.isEmpty(arg))return this;
        arg = "'"+arg.replace(",","','")+"'";
        this.sqlSB.append(" ").append(partSql.replace("?",arg));
        return this;
    }

    public String getSql(){
        return this.sqlSB.toString();
    }

    public Object[] getArgs(){
        return this.params.toArray();
    }
}

import io.swagger.annotations.ApiModelProperty;
import lombok.Getter;
import lombok.Setter;
import org.hibernate.validator.constraints.Range;

import javax.validation.constraints.NotNull;

/**
 * @Auther: pigmen
 * @Date: 2020/4/10 15:17
 * @Description:
 */
@Getter
@Setter
public class PageIn {

    @ApiModelProperty(value = "页码，默认1",example = "1")
    @NotNull
    @Range(min = 1)
    private Integer pageNumber = 1;

    @ApiModelProperty(value = "页条数，最大100，默认10",example = "10")
    @NotNull
    @Range(max = 100,min = 1)
    private Integer pageSize = 10;

    public PageIn(){

    }

    public PageIn(Integer pageNumber, Integer pageSize) {
        this.pageNumber = pageNumber;
        this.pageSize = pageSize;
    }

}

import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Getter;
import lombok.Setter;

import java.util.List;

/**
 * @desc:
 * @author: 猪猪侠
 * @date: 2019/11/17 19:41
 */
@Setter
@Getter
@ApiModel
public class PageOut<T> {

    @ApiModelProperty("页数")
    private Integer pageNumber;//页数
    @ApiModelProperty("每页条数")
    private Integer pageSize;//每页条数
    @ApiModelProperty("总页数")
    private Integer totalPage;//总页数
    @ApiModelProperty("总条数")
    private Long totalSize;//总条数
    @ApiModelProperty("当前页返回条数")
    private Integer currentSize;//当前页返回条数
    @ApiModelProperty("当前面详细数据")
    private List<T> pageData;//当前面详细数据

    public PageOut(){

    }

    public PageOut(Integer pageNumber, Integer pageSize) {
        this.pageNumber = pageNumber;
        this.pageSize = pageSize;
    }
}

工具类全为本人封装，如有不妥之处欢迎指出！