一、準備階段

1、依賴引入

• pom.xml

<properties>...<postgresql.verison>42.5.6</postgresql.verison><mybatis.version>3.0.1</mybatis.version>
</properties>

<dependencies>...<!-- postgresql 驅動 --><dependency><groupId>org.postgresql</groupId><artifactId>postgresql</artifactId><version>${postgresql.verison}</version></dependency><!-- mybatis 和 springboot 整合的起步依賴 --><dependency><groupId>org.mybatis.spring.boot</groupId><artifactId>mybatis-spring-boot-starter</artifactId><version>${mybatis.version}</version></dependency>
</dependencies>

2、配置文件

1. application.yml

mybatis:mapper-locations: classpath:/mapper/*.xml # 映射文件路徑config-location: classpath:/mybatis-config.xml # 核心配置文件路徑

2. mybatis-config.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration PUBLIC"-//mybatis.org//DTD Config 3.0//EN""http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration><settings><!-- 設置駝峰標識 --><setting name="mapUnderscoreToCamelCase" value="true"/><!-- 打印 SQL 語句 --><setting name="logImpl" value="STDOUT_LOGGING"/></settings>
</configuration>

3、準備數據

1. 創建數據表

CREATE TABLE staff (id SERIAL PRIMARY KEY,name VARCHAR(255) NOT NULL,role VARCHAR(255),salary DECIMAL(10, 2)
);

2. 插入數據

INSERT INTO staff (name, role, salary) VALUES
('張三', '項目經理', 25000.00),
('李四', '高級開發工程師', 18000.00),
('王五', '開發工程師', 15000.00),
('趙六', '測試工程師', 12000.00),
('錢七', 'UI設計師', 13000.00),
('孫八', '產品經理', 20000.00),
('周九', '運維工程師', 14000.00),
('吳十', '初級開發工程師', 10000.00),
('鄭十一', '數據庫管理員', 16000.00),
('王十二', '技術總監', 30000.00);

---

二、手寫 SQL 分頁實現

1、XML 方式

（1）Mapper

1. StaffMapper.java

@Mapper
public interface StaffMapper {List<Staff> queryStaffs(int offset, int pageSize);Integer countStaffs();
}

2. StaffMapper.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.my.mapper.StaffMapper"><resultMap id="staffResultMap" type="com.my.model.db.Staff"><id column="id" property="id"/><result column="name" property="name"/><result column="role" property="role"/><result column="salary" property="salary"/></resultMap><select id="queryStaffs" resultMap="staffResultMap">SELECT *FROM staff LIMIT #{pageSize}OFFSET #{offset}</select><select id="countStaffs" resultType="int">SELECT COUNT(*)FROM staff</select>
</mapper>

（2）Test

// 分頁參數計算
int pageNum = 2; // 當前頁碼
int pageSize = 2; // 每頁條數
int offset = (pageNum - 1) * pageSize;List<Staff> staffs = staffMapper.queryStaffs(offset, pageSize);
int total = staffMapper.countStaffs();
int pageTotal = (int) Math.ceil((double) total / pageSize);for (Staff staff : staffs) {System.out.println(staff);
}System.out.println("pageNum: " + pageNum);
System.out.println("pageSize: " + pageSize);
System.out.println("pageTotal: " + pageTotal);
System.out.println("total: " + total);

# 輸出結果Staff(id=5, name=王五, role=開發工程師, salary=15000.0)
Staff(id=6, name=趙六, role=測試工程師, salary=12000.0)
pageNum: 2
pageSize: 2
pageTotal: 5
total: 10

2、注解方式

（1）Mapper

• StaffMapper.java

@Select("SELECT * FROM staff ORDER BY id LIMIT #{pageSize} OFFSET #{offset}")
List<Staff> queryStaffs(int offset, int pageSize);@Select("SELECT COUNT(*) FROM staff")
Integer countStaffs();

（2）Test

// 分頁參數計算
int pageNum = 2; // 當前頁碼
int pageSize = 2; // 每頁條數
int offset = (pageNum - 1) * pageSize;List<Staff> staffs = staffMapper.queryStaffs(offset, pageSize);
int total = staffMapper.countStaffs();
int pageTotal = (int) Math.ceil((double) total / pageSize);for (Staff staff : staffs) {System.out.println(staff);
}System.out.println("pageNum: " + pageNum);
System.out.println("pageSize: " + pageSize);
System.out.println("pageTotal: " + pageTotal);
System.out.println("total: " + total);

# 輸出結果Staff(id=5, name=王五, role=開發工程師, salary=15000.0)
Staff(id=6, name=趙六, role=測試工程師, salary=12000.0)
pageNum: 2
pageSize: 2
pageTotal: 5
total: 10

---

三、手寫 SQL 分頁實現封裝

1、Entity

• PageResult.java

@Data
@AllArgsConstructor
@NoArgsConstructor
public class PageResult<T> {private List<T> data;private Integer pageNum;private Integer pageSize;private Integer pageTotal;private Integer total;
}

2、Mapper

• StaffMapper.java

@Select("SELECT * FROM staff ORDER BY id LIMIT #{pageSize} OFFSET #{offset}")
List<Staff> queryStaffs(int offset, int pageSize);@Select("SELECT COUNT(*) FROM staff")
Integer countStaffs();

3、Service

1. StaffService.java

public interface StaffService {PageResult<Staff> queryStaffs(int pageNum, int pageSize);
}

2. StaffServiceImpl.java

@Service
public class StaffServiceImpl implements StaffService {@Autowiredprivate StaffMapper staffMapper;@Overridepublic PageResult<Staff> queryStaffs(int pageNum, int pageSize) {int offset = (pageNum - 1) * pageSize;List<Staff> staffs = staffMapper.queryStaffs(offset, pageSize);int total = staffMapper.countStaffs();int pageTotal = (int) Math.ceil((double) total / pageSize);return new PageResult<>(staffs, pageNum, pageSize, pageTotal, total);}
}

4、Test

PageResult<Staff> staffPageResult = staffService.queryStaffs(1, 5);List<Staff> staffs = staffPageResult.getData();
int pageNum = staffPageResult.getPageNum();
int pageSize = staffPageResult.getPageSize();
int pageTotal = staffPageResult.getPageTotal();
int total = staffPageResult.getTotal();for (Staff staff : staffs) {System.out.println(staff);
}System.out.println("pageNum: " + pageNum);
System.out.println("pageSize: " + pageSize);
System.out.println("pageTotal: " + pageTotal);
System.out.println("total: " + total);

# 輸出結果Staff(id=3, name=張三, role=項目經理, salary=25000.0)
Staff(id=4, name=李四, role=高級開發工程師, salary=18000.0)
Staff(id=5, name=王五, role=開發工程師, salary=15000.0)
Staff(id=6, name=趙六, role=測試工程師, salary=12000.0)
Staff(id=7, name=錢七, role=UI設計師, salary=13000.0)
pageNum: 1
pageSize: 5
pageTotal: 2
total: 10