SqlServer database jdbcTemplate to achieve paging

preface

Sqlserver database and jdbcTemplate to implement the function of paging query interface. (MyBatisPlus used in the project, due to environmental reasons, writing SQL statements in XML files cannot map to mapper layer, so it adopts the way of native JDBC.) A few key issues:

1. The queryForObject method of jdbcTemplate
1. JdbcTemplate’s query method, New BeanPropertyRowMapper (*.class), can be used to map Java object properties to MySQL table field names. Note: The field names in the table need to be the same as the member variable names of the entity class.
1. SQL Server SQL statements implement paging unlike MySQL supports limit methods, so use row_number () and TOP() * instead.
1. The SQL statement converts the date to the new data type using the CONVERT() function.
1. To prevent SQL injection problems, I looked at the underlying source code of jdbcTemplate, which already includes precompilation. If you don’t, you’ll need to manually use the PreparedStatement method (which I won’t code as an example).

Configure SqlServer dependencies

I have used multiple data sources. The primary database is Mysql and the SqlServer used by the secondary library. How to configure multiple data sources I will not elaborate, pom files need to configure the dependency of SqlServer.

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

Create the entity class EmpInfo

Notice that the field names in the checklist are the same as the name of the member variable of the entity class.

@Data
public class EmpInfoDTO {

    private String id;
    private Integer EmpID;
    private String Name;
    private Date ExecTime;
   
}
Copy the code

The Controller layer

Parameters: index, size, name, date, time

@getMapping ("/page") @apiOperation (value = "", HttpMethod = "GET") @apiIMPLICITParams ({@apiIMPLICITParam (name = "index", value = "number of pages, default 1", dataType = "Integer", ParamType = "query"), @apiIMPLICITParam (name = "size", value = "several data items per page, default 10", dataType = "Integer", ParamType = "query"), @apiIMPLICITParam (name = "name", value = "name", dataType = "String", paramType = "query"), @APIIMPLICITParam (name = "date", value = "date yyyY-MM-DD ", dataType = "String", paramType = "query"), @apiIMPLICITParam (name = "time", value = "time", 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); }Copy the code

The Service layer

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

ServiceImpl

Interface method implementation

@Override public BaseResult queryPage(Integer index, Integer size, String name, String date, String time){ try { int rowNumber = (index - 1) * size; Int total = queryTotal(name,date,time); / / queryRecordList method query and transform entity class 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; }Copy the code

QueryTotal () method

Note:

Processing of parameter transmission
StringBuffer splicing SQL
Use of the queryForObject method

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);
    }
Copy the code

QueryRecordList method