Hello, I'm the program ape pony, the hupiao family!
Writing an article is a summary of ordinary times and everyone's common learning and progress, so as to code out their dreams as soon as possible 😊

Coordinates: subway station next to pony work

1. Cause of problem

I believe that the development partners can't get around a problem. When returning list data, they must page the data to improve query efficiency and have a good display on the web. In this article, pony will use Mysql, Oracle and java mybatis plus to page.

2,Mysql

The paging in sql uses limit, so let's use limit first
The first parameter specifies the offset of the first return record line, and the second parameter specifies the maximum number of return record lines.

understand:

• Syntax: limit n;
  Get the first n data of query results
• Syntax: limit n,m;
  n is the starting subscript. How many pieces of data do m take
  n = number of pages
  m = (page number - 1) * number per page
  In paging, subscripts start at 0

Code demonstration:

• Controller

@RestController
public class MysqlController {

    @Autowired
    MysqlService mysqlService;

    @PostMapping(value = "/test/list11")
    public String ListRole(@RequestBody RoleDto roleDto) {
        return mysqlService.list(roleDto);
    }
}

• Service

@Service
public class MysqlServiceImpl implements MysqlService {

    @Autowired
    RoleMapper1 roleMapper;

    @Override
    public String list(RoleDto roleDto) {

		// (page number - 1) * the first value of quantity limit per page
        roleDto.setPageIndex((roleDto.getIndex()-1)*roleDto.getSize());
        List<Role1> roles = roleMapper.selectRoleList(roleDto);
        return JSON.toJSONString(roles);

    }
}

• Dao

public interface RoleMapper1 extends BaseMapper<Role1> {

    List<Role1> selectRoleList(RoleDto roleDto);
}

    <select id="selectRoleList" resultType="com.gafc.coloan.amc.model.Role1">
        select * from sys_role limit #{pageIndex},#{size}
    </select>

In general, Mysql provides limit paging, which greatly reduces the difficulty of writing sql!

3,Oracle

Oracle does not provide the convenient use of limit like MySQL. It can only use rownum, which is relatively complex.

Code demonstration:

• Controller

@RestController
public class OracleController {


    @Autowired
    OracleService oracleService;

    @PostMapping(value = "/test/list111")
    public String ListRole(@RequestBody TaskDto taskDto) {
        return oracleService.list(taskDto);
    }

}

• Service

@DS("coloan")
@Service
public class OracleServiceImpl implements OracleService {

    @Autowired
    TaskMapper taskMapper;

    @Override
    public String list(TaskDto taskDto) {
        // sql mode
        List<Task> tasks = taskMapper.selectTaskList(taskDto);
        return JSON.toJSONString(tasks);
    }

}

Here @ DS, you can see my last article

• Dao

public interface TaskMapper extends BaseMapper<Task> {

    List<Task> selectTaskList(TaskDto taskDto);
    
}

    <select id="selectTaskList" resultType="com.gafc.coloan.amc.model.Task">
        SELECT *
        FROM
        (
        SELECT t.*, rownum rn
        FROM
        (
        SELECT * FROM task
        ) t
        ) c
        WHERE
        c.rn between (#{index}-1)*#{size}+1 and #{index}*#{size}
    </select>

4. Java mybatis plus

4.1 Java side mybatis plus - Mysql

This refers to the data of the second Mysql and only modifies the code of the service layer.

@Service
public class MysqlServiceImpl implements MysqlService {

    @Autowired
    RoleMapper1 roleMapper;

    @Override
    public String list(RoleDto roleDto) {

        /**
         * sql The way
         */
         // (page number - 1) * the first value of quantity limit per page
         //roleDto.setPageIndex((roleDto.getIndex()-1)*roleDto.getSize());
         // List<Role1> roles = roleMapper.selectRoleList(roleDto);

        /**
         * Mybatis-Plus The way
         */
		QueryWrapper queryWrapper = new QueryWrapper();
        queryWrapper.orderByDesc("create_Dt");//Descending according to creation time

        Page<Role1> page = new Page(roleDto.getIndex(),roleDto.getSize());
        IPage<Role1> role1IPage = roleMapper.selectPage(page, queryWrapper);

        return JSON.toJSONString(list);

    }
}

be careful:

• Here, the index in the new Page(index,size) is the page number corresponding to the front end, which is different from the limit usage in MySQL
• Compared with sql, mybatis plus is more concise, but everyone has their own way. Both will be the best 🎈

4.2 Java mybatis plus Oracle

This refers to Oracle data and only modifies the code of the service layer.

@DS("coloan")
@Service
public class OracleServiceImpl implements OracleService {

    @Autowired
    TaskMapper taskMapper;

    @Override
    public String list(TaskDto taskDto) {
        // sql mode
        // List<Task> tasks = taskMapper.selectTaskList(taskDto);
        // return JSON.toJSONString(tasks);

        // Mybatis plus mode
        QueryWrapper queryWrapper = new QueryWrapper();
        queryWrapper.isNotNull("TASK_ID");

        Page<Task> page = new Page(taskDto.getIndex(),taskDto.getSize());
        IPage<Task> page1 = taskMapper.selectPage(page, queryWrapper);
        List<Task> list = page1.getRecords();
        return JSON.toJSONString(list);
    }
}

Pony thinks that if Oracle is used in the database, its query sql is not so complex that mybatis plus cannot be realized, and using MP is relatively simple.

Daily summary

• Familiar with various ways of paging query, we must be proficient in using
• Capable can knock it manually!

The above is what pony will send today. Welcome to learn from each other and make progress together. You can also interact in the comment area! 👇