SqlServer数据库 jdbcTemplate实现分页

前言

今天记录下查询Sqlserver数据库并用jdbcTemplate实现分页查询接口的功能。(项目使用的MyBatisPlus，由于环境原因在xml文件中写SQL语句无法与mapper层映射，所以采用原生jdbc的方式实现。)
几个重点问题：

1. jdbcTemplate的queryForObject方法使用
1. jdbcTemplate的query方法，new BeanPropertyRowMapper（*.class）可用来映射Java对象的属性和MySQL表的字段名称。注意：需要使表中的字段名和实体类的成员变量名称一致。
1. SqlServer的sql语句实现分页不像MySQL支持limit方法，所以主要使用row_number ()和TOP() *。
1. sql语句使用CONVERT() 函数把日期转换为新数据类型。
1. 关于防止SQL注入问题，我查看了jdbcTemplate底层源码，里面已经包含预编译。如果不自带的话，需要手动使用PreparedStatement方法（这个我就不代码示例了）。

配置SqlServer依赖

我这边用到了多数据源，主数据库是Mysql，从库用的SqlServer。多数据源如何配置我就不详细说了，pom文件里需要配置下SqlServer的依赖。

        <dependency>
            <groupId>com.microsoft.sqlserver</groupId>
            <artifactId>mssql-jdbc</artifactId>
            <scope>runtime</scope>
        </dependency>

创建实体类EmpInfo

注意检查表中的字段名和实体类的成员变量名称保持一致。

@Data
public class EmpInfoDTO {

    private String id;
    private Integer EmpID;
    private String Name;
    private Date ExecTime;
   
}

Controller层

传参：index、size、name、date、time

@GetMapping("/page")
    @ApiOperation(value = "分页查询", httpMethod = "GET")
    @ApiImplicitParams({
            @ApiImplicitParam(name = "index", value = "第几页,默认1", dataType = "Integer", paramType = "query"),
            @ApiImplicitParam(name = "size", value = "每页几条数据,默认10", dataType = "Integer", paramType = "query"),
            @ApiImplicitParam(name = "name", value = "姓名", dataType = "String", paramType = "query"),
            @ApiImplicitParam(name = "date", value = "打卡日期 YYYY-MM-DD", dataType = "String", paramType = "query"),
            @ApiImplicitParam(name = "time", value = "打卡日期 hh:mm:ss", dataType = "String", paramType = "query")
    })
    public BaseResult<Page<TestVo>> queryPage(@RequestParam(name = "index", defaultValue = "1", required = false) Integer index,
                                                   @RequestParam(name = "size", defaultValue = "10", required = false) Integer size,
                                                   @RequestParam(name = "name", defaultValue = "", required = false) String name,
                                                   @RequestParam(name = "date", defaultValue = "", required = false) String date,
                                                   @RequestParam(name = "time", defaultValue = "", required = false) String time) {
        log.info("queryPage:{}:{}:{}", name, date, time);
        return testService.queryPage(index, size, name, date, time);
     
    }

Service层

BaseResult queryPage(Integer index, Integer size, String name, String date, String time);

ServiceImpl

接口方法实现

@Override
    public BaseResult queryPage(Integer index, Integer size, String name, String date, String time){
        try {
            int rowNumber = (index - 1) * size;
           //queryTotal方法统计总数
            int total = queryTotal(name,date,time);
           //queryRecordList方法查询并转换实体类List
            List<EmpInfoDTO> recordList = queryRecordList(name,date,time,size,rowNumber);

            log.info(">>>>>>>>>recordList" + recordList);

            List<TestVo> voList = new ArrayList<>();
            if (CollectionUtils.isNotEmpty(recordList)) {
                recordList.forEach(empInfoDTO -> {
                    TestVo testVo = new TestVo();
                     //具体转化省略
                    voList.add(testVo);
                });
            }
            Page page = new Page(index, size);
            page.setTotal(total);
            page.setRecords(voList);
            return new BaseResult(page);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

queryTotal()方法

注意点：

传参的处理
StringBuffer拼接sql
queryForObject方法的使用

private Integer queryTotal(String name, String date, String time){
        List<Object> params = new ArrayList<Object>();
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT count(r.EmpID) from dbo.EmpInfo e ,dbo.RecordInfo r where r.EmpID = e.EmpID");

        if(StringUtils.isNotEmpty(name)){
            sql.append("and e.Name=? ");
            params.add(name);
        }

        if(StringUtils.isNotEmpty(date)){
            sql.append("and CONVERT(varchar(100), r.ExecTime, 23) =? ");
            params.add(date);
        }

        if(StringUtils.isNotEmpty(time)){
            sql.append("and CONVERT(varchar(100), r.ExecTime, 24) =? ");
            params.add(time);
        }
        Object[] para = params.toArray(new Object[params.size()]);

        return this.jdbcTemplate.queryForObject(sql.toString(), para, Integer.class);
    }

queryRecordList方法

注意点：

TOP() * 和row_number ()的使用
CONVERT()函数转换日期类型
使用query方法，new BeanPropertyRowMapper(EmpInfoDTO.class)可以用来映射Java对象的属性和MySQL表的字段名称。这个方法比较方便省事，就是注意需要使表中的字段名和实体类的成员变量名称一致

private List<EmpInfoDTO> queryRecordList(String name, String date, String time, Integer size, Integer rowNumber) {
        List<Object> params = new ArrayList<Object>();
        StringBuffer sql = new StringBuffer();

        if(size != null){
            sql.append("SELECT TOP(?) * ");
            params.add(size);
        }
        sql.append("FROM (SELECT row_number () OVER (ORDER BY r.EmpID DESC) AS rownumber ,e.*,r.ExecTime as ExecTime FROM dbo.EmpInfo e ,dbo.RecordInfo r where r.EmpID = e.EmpID) temp_row WHERE 1=1");
        if(rowNumber != null){
            sql.append("and rownumber >? ");
            params.add(rowNumber);
        }
        if(StringUtils.isNotEmpty(name)){
            sql.append("and temp_row.Name = ? ");
            params.add(name);
        }

        if(StringUtils.isNotEmpty(date)){
            sql.append("and CONVERT(varchar(100), temp_row.ExecTime, 23) =? ");
            params.add(date);
        }

        if(StringUtils.isNotEmpty(time)){
            sql.append("and CONVERT(varchar(100), temp_row.ExecTime, 23) =? ");
            params.add(time);
        }

        Object[] para = params.toArray(new Object[params.size()]);

        log.info(">>>>>>>>>sql.toString()" + sql.toString());

        return this.jdbcTemplate.query(sql.toString(), para, new BeanPropertyRowMapper(EmpInfoDTO.class));
    }