SqlServer数据库 jdbcTemplate实现分页
前言
今天记录下查询Sqlserver数据库并用jdbcTemplate实现分页查询接口的功能。(项目使用的MyBatisPlus，由于环境原因在xml文件中写SQL语句无法与mapper层映射，所以采用原生jdbc的方式实现。)
几个重点问题：
-
- jdbcTemplate的queryForObject方法使用
-
- jdbcTemplate的query方法，new BeanPropertyRowMapper（*.class）可用来映射Java对象的属性和MySQL表的字段名称。注意：需要使表中的字段名和实体类的成员变量名称一致。
-
- SqlServer的sql语句实现分页不像MySQL支持limit方法，所以主要使用row_number ()和TOP() *。
-
- sql语句使用CONVERT() 函数把日期转换为新数据类型。
-
- 关于防止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));
}
相关文章