SSM Framework Manually Implements Paging Logic (Non-PageHelper)

The first method is to query all the data and then paginate it.

Analysis:

  1. When paging, we need to get two parameters from the front desk, pageNo (which page data) and pageSize (the number of entries per page).
  2. According to these two parameters, the data needed by the front end is to find the start index and the end index in the data list.
  3. The subList method of List is used to segment all the queried data and return it.

Implementation process

1. Obtaining parameters

The json data interface is returned here, and the implementation method is in the service layer.

@ResponseBody
@GetMapping("/allPage")
public String findAllPage(
   @RequestParam(required = true,defaultValue = "1") Integer pageNo,
   @RequestParam(required = false,defaultValue = "5") Integer pageSize) {
    return customerService.findAllPage(pageNo,pageSize);
}

2. Intercept data

CustomerServiceImpl.java

  • The pageUtil class, iterCustomer() method and JsonUtil class are introduced below. The purpose of extracting these classes or methods is to improve code reusability and reduce code redundancy.
@Override
public String findAllPage(Integer pageNo, Integer pageSize) {
    List<Customer> customers = customerDao.findAll();
    List<Map<String, String>> resultList = new ArrayList<>();
    PageUtil<Customer> pageUtil = new PageUtil<>();
    for (Customer customer : pageUtil.pageList(customers, pageNo, pageSize){
        resultList.add(iterCustomer(customer));
    }
    return JsonUtil.toJSON(resultList);
}

==PageUtil.java==

  • This class is a tool class I extracted to intercept List. When creating an object, I need to pass a generic type, which is the type in the processing list.
  • This kind of object calls the pageList method to return a List array with a good intercepted length.
  • The pageList method has three parameters.
    • The first parameter is the List array that holds all the data.
    • The second parameter is the page number.
    • The third parameter is the number of data bars displayed on each page.
public class PageUtil<T> {
    private int beginIndex;//Initial index
    private int endIndex;//Stop index

    public List<T> pageList(List<T> list, int pageNo, int pageSize) {
        int size = list.size();

        beginIndex = (pageNo - 1) * pageSize;
        endIndex = pageNo * pageSize > size ? size : pageNo * pageSize;
        List<T> resultList = list.subList(beginIndex, endIndex);
        return resultList;
    }
}

iterCustomer() method

  • The function is to get a map to store the value of a Customer instance, which is represented by key-value pairs.
  • The parameter is the class object to be wrapped.
  • The return value is a Map object, which stores some information of the parameter class object.
public Map<String, String> iterCustomer(Customer customer) {
        Map<String, String> resultMap = new HashMap<>();
        resultMap.put("id", customer.getId().toString());
        resultMap.put("name", customer.getName());
        resultMap.put("phone", customer.getPhone());
        resultMap.put("email", customer.getEmail());
        return resultMap;
}

JsonUtil.java

  • The function is to encapsulate Jackson and return a json string.
public class JsonUtil {
    //The first step in using jackson to transform json data
    private static ObjectMapper MAPPER = new ObjectMapper();
    static String jsonString=null;

    public static String toJSON(Object object){
        try {
            //jackson turns any object object object into a json string 
            jsonString = MAPPER.writeValueAsString(object);
        } catch (JsonProcessingException e) {
            e.printStackTrace();
        }
        return jsonString;
    }
}

The second method is to use SQL paging to query only the data needed by the current page.

Analysis:

  • The first method can be used when the amount of data is relatively small, but when the amount of data is very large, if we only need a few pieces of data on a page to find all the data, it seems unnecessary, or too expensive, so we now use the second paging method.

  • The second method is similar to the first one, which obtains the number of pages passed from the front end and the number of bars displayed on each page, and queries the database through custom SQL statements to get the required data.

  • sql Paging Reference:

    Mysql Complex Query or

    Sort Aggregated Paging Query for [MySQL] Conditional Query

Implementation process

1. Obtaining parameters

Analysis

  • In the parameter, pageNo is the page number from the front end, that is, the number of pages; pageSize is the number of items to be displayed on each page, the default is 5;
  • When using SQL paging, the parameters needed in the query statement are the index of the displayed data in the database and the number of data items displayed on each page.
    • Formula for calculating the index: the starting index = (current page number - 1)* the number of bars displayed per page;
  • It is also necessary to determine whether the number of pages coming from the front end exceeds the maximum number of pages in the total data, and if it exceeds, the maximum number of pages will be displayed.
    • Maximum page count: int maxPage =(int) Math.ceil(count/pageSize.doubleValue());

CustomerController.java

@Controller
public class CustomerController {
    @Autowired
    CustomerService customerService´╝Ť
    
    @ResponseBody
    @GetMapping("/allSql")
    public String findAllPageSql(
        @RequestParam(required = true,defaultValue = "1") Integer pageNo,
        @RequestParam(required = false,defaultValue = "5") Integer pageSize) {
         /**
         * pageSize Number of items displayed per page
         * pageNo The first parameter passed by sql paging is the initial index.
         * Formula: Starting Index = (Current Page Number - 1)* Number of Bars Displayed per Page
         */
        //Total data
        int count = customerService.count();
        //Calculate the maximum page number
        int maxPage =(int) Math.ceil(count/pageSize.doubleValue());
        //The current page number exceeds the maximum page number and returns the maximum page number value:
        pageNo = pageNo>maxPage?maxPage:pageNo;
        //Index value at the beginning of paging
        int index = (pageNo - 1) * pageSize > count ? count : (pageNo - 1) * pageSize;
        return customerService.findAllPageSql(index, pageSize);
    }
}

2. Hierarchical invocation method

CustomerServiceImpl.java

@Service
public class CustomerServiceImpl implements CustomerService {
    @Autowired
    CustomerDao customerDao;
    @Override
    public String findAllPageSql(Integer index, Integer pageSize) {
        return JsonUtil.toJSON(customerDao.findAllPageSql(index, pageSize));
    }
}

CustomerDao.java

public interface CustomerDao {   
    List<Customer> findAllPageSql(Integer index,Integer pageSize);
}

3. custom sql

CustomerMapper.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.vue.dao.CustomerDao">
    <select id="findAllPageSql" parameterType="java.lang.Integer" resultType="com.vue.entity.Customer">
        SELECT * FROM t_customer LIMIT #{param1},#{param2}
    </select>
</mapper>

Mapper.xml mapping file for multiple parameter references:

Mapper.xml mapping file sql query in Mybatis receives multiple parameters or

Mapper.xml mapping file sql query in Mybatis receives multiple parameters

Above

  • This is the whole process of paging in SSM framework without PageHelper plug-in. The second kind of customized SQL query is recommended, because this query method is more in line with the realization of paging logic. It queries only the data needed at a time, not all the data at a time, so it is more efficient.
  • If you have questions, you can communicate with each other.

Tags: Java SQL xml Mybatis

Posted on Tue, 08 Oct 2019 03:40:58 -0700 by cigardude