分页
如果你的数据库中有数百万的记录，你肯定不希望将其一骨碌地给到同一个页面中。那样的话，客户端和服务端都无法承受。所以，你需要分页。
添加分页逻辑
调整方法参数，domain/gateway/book_manager.ts：
@@ -5,5 +5,5 @@ export interface BookManager {
updateBook(id: number, b: Book): Promise<void>;
deleteBook(id: number): Promise<void>;
getBook(id: number): Promise<Book | null>;
- getBooks(): Promise<Book[]>;
+ getBooks(offset: number): Promise<Book[]>;
}
为 getBooks 方法添加名为 offset 的参数。
在 infrastructure/config/config.ts 中添加 page_size 配置项：
@@ -9,6 +9,7 @@ interface DBConfig {
interface ApplicationConfig {
port: number;
+ page_size: number;
}
export interface CacheConfig {
设置 page_size 配置值，config.json：
@@ -1,6 +1,7 @@
{
"app": {
- "port": 3000
+ "port": 3000,
+ "page_size": 5
},
"db": {
"file_name": "test.db",
合入 page_size，application/wire_helper.ts：
@@ -10,7 +10,7 @@ export class WireHelper {
private kv_store: RedisCache;
constructor(c: Config) {
- this.sql_persistence = new MySQLPersistence(c.db.dsn);
+ this.sql_persistence = new MySQLPersistence(c.db.dsn, c.app.page_size);
this.no_sql_persistence = new MongoPersistence(
c.db.mongo_uri,
c.db.mongo_db_name
更新查询逻辑，将 offset 和 page_size 考虑进去，infrastructure/database/mysql.ts：
@@ -5,8 +5,10 @@ import { BookManager } from "@/domain/gateway/book_manager";
export class MySQLPersistence implements BookManager {
private db: mysql.Connection;
+ private page_size: number;
- constructor(dsn: string) {
+ constructor(dsn: string, page_size: number) {
+ this.page_size = page_size;
this.db = mysql.createConnection(dsn);
this.db.addListener("error", (err) => {
console.error("Error connecting to MySQL:", err.message);
@@ -67,8 +69,10 @@ export class MySQLPersistence implements BookManager {
return rows.length ? (rows[0] as Book) : null;
}
- async getBooks(): Promise<Book[]> {
- const [rows] = await this.db.promise().query("SELECT * FROM books");
+ async getBooks(offset: number): Promise<Book[]> {
+ const [rows] = await this.db
+ .promise()
+ .query("SELECT * FROM books LIMIT ?, ?", [offset, this.page_size]);
return rows as Book[];
}
调整缓存键，application/executor/book_operator.ts：
@@ -23,13 +23,14 @@ export class BookOperator {
return await this.bookManager.getBook(id);
}
- async getBooks(): Promise<Book[]> {
- const cache_value = await this.cacheHelper.load(booksKey);
+ async getBooks(offset: number): Promise<Book[]> {
+ const k = `${booksKey}-${offset}`;
+ const cache_value = await this.cacheHelper.load(k);
if (cache_value) {
return JSON.parse(cache_value);
}
- const books = await this.bookManager.getBooks();
- await this.cacheHelper.save(booksKey, JSON.stringify(books));
+ const books = await this.bookManager.getBooks(offset);
+ await this.cacheHelper.save(k, JSON.stringify(books));
return books;
}
最后一步，在 adaptor/router.ts 中传递 query 参数：
@@ -16,8 +16,12 @@ class RestHandler {
// Get all books
public async getBooks(req: Request, res: Response): Promise<void> {
+ let offset = parseInt(req.query.o as string);
+ if (isNaN(offset)) {
+ offset = 0;
+ }
try {
- const books = await this.bookOperator.getBooks();
+ const books = await this.bookOperator.getBooks(offset);
res.status(200).json(books);
} catch (err) {
console.error(`Failed to get books: ${err}`);
好啦！改完啦！让我们试下。
使用 curl 测试
放入待测试数据：
curl -X POST -H "Content-Type: application/json" -d '{"title": "The Great Gatsby", "author": "F. Scott Fitzgerald", "published_at": "1925-04-10", "description": "A novel depicting the opulent lives of wealthy Long Island residents during the Jazz Age.", "isbn": "9780743273565", "total_pages": 218}' http://localhost:3000/books
curl -X POST -H "Content-Type: application/json" -d '{"title": "To Kill a Mockingbird", "author": "Harper Lee", "published_at": "1960-07-11", "description": "A novel set in the American South during the 1930s, dealing with themes of racial injustice and moral growth.", "isbn": "9780061120084", "total_pages": 281}' http://localhost:3000/books
curl -X POST -H "Content-Type: application/json" -d '{"title": "1984", "author": "George Orwell", "published_at": "1949-06-08", "description": "A dystopian novel depicting a totalitarian regime, surveillance, and propaganda.", "isbn": "9780451524935", "total_pages": 328}' http://localhost:3000/books
curl -X POST -H "Content-Type: application/json" -d '{"title": "Pride and Prejudice", "author": "Jane Austen", "published_at": "1813-01-28", "description": "A classic novel exploring the themes of love, reputation, and social class in Georgian England.", "isbn": "9780486284736", "total_pages": 279}' http://localhost:3000/books
curl -X POST -H "Content-Type: application/json" -d '{"title": "The Catcher in the Rye", "author": "J.D. Salinger", "published_at": "1951-07-16", "description": "A novel narrated by a disaffected teenager, exploring themes of alienation and identity.", "isbn": "9780316769488", "total_pages": 277}' http://localhost:3000/books
curl -X POST -H "Content-Type: application/json" -d '{"title": "The Lord of the Rings", "author": "J.R.R. Tolkien", "published_at": "1954-07-29", "description": "A high fantasy epic following the quest to destroy the One Ring and defeat the Dark Lord Sauron.", "isbn": "9780544003415", "total_pages": 1178}' http://localhost:3000/books
curl -X POST -H "Content-Type: application/json" -d '{"title": "Moby-Dick", "author": "Herman Melville", "published_at": "1851-10-18", "description": "A novel exploring themes of obsession, revenge, and the nature of good and evil.", "isbn": "9780142000083", "total_pages": 624}' http://localhost:3000/books
curl -X POST -H "Content-Type: application/json" -d '{"title": "The Hobbit", "author": "J.R.R. Tolkien", "published_at": "1937-09-21", "description": "A fantasy novel set in Middle-earth, following the adventure of Bilbo Baggins and the quest for treasure.", "isbn": "9780345339683", "total_pages": 310}' http://localhost:3000/books
curl -X POST -H "Content-Type: application/json" -d '{"title": "The Adventures of Huckleberry Finn", "author": "Mark Twain", "published_at": "1884-12-10", "description": "A novel depicting the journey of a young boy and an escaped slave along the Mississippi River.", "isbn": "9780486280615", "total_pages": 366}' http://localhost:3000/books
curl -X POST -H "Content-Type: application/json" -d '{"title": "War and Peace", "author": "Leo Tolstoy", "published_at": "1869-01-01", "description": "A novel depicting the Napoleonic era in Russia, exploring themes of love, war, and historical determinism.", "isbn": "9781400079988", "total_pages": 1392}' http://localhost:3000/books
curl -X POST -H "Content-Type: application/json" -d '{"title": "Alice’s Adventures in Wonderland", "author": "Lewis Carroll", "published_at": "1865-11-26", "description": "A children’s novel featuring a young girl named Alice who falls into a fantastical world populated by peculiar creatures.", "isbn": "9780141439761", "total_pages": 192}' http://localhost:3000/books
curl -X POST -H "Content-Type: application/json" -d '{"title": "The Odyssey", "author": "Homer", "published_at": "8th Century BC", "description": "An ancient Greek epic poem attributed to Homer, detailing the journey of Odysseus after the Trojan War.", "isbn": "9780140268867", "total_pages": 541}' http://localhost:3000/books
列出第一页的图书：
curl -X GET http://localhost:3000/books
结果：
[
{
"id": 2,
"title": "Sample Book 222",
"author": "John Doe",
"published_at": "2023-01-01",
"description": "A sample book description",
"isbn": "1234567890",
"total_pages": 200,
"created_at": "2024-03-01T04:11:57.000Z",
"updated_at": "2024-03-01T04:11:57.000Z"
},
{
"id": 3,
"title": "Sample Book",
"author": "John Doe",
"published_at": "2023-01-01",
"description": "A sample book description",
"isbn": "1234567890",
"total_pages": 200,
"created_at": "2024-03-01T04:40:16.000Z",
"updated_at": "2024-03-01T04:40:16.000Z"
},
{
"id": 4,
"title": "The Great Gatsby",
"author": "F. Scott Fitzgerald",
"published_at": "1925-04-10",
"description": "A novel depicting the opulent lives of wealthy Long Island residents during the Jazz Age.",
"isbn": "9780743273565",
"total_pages": 218,
"created_at": "2024-03-01T21:48:25.000Z",
"updated_at": "2024-03-01T21:48:25.000Z"
},
{
"id": 5,
"title": "To Kill a Mockingbird",
"author": "Harper Lee",
"published_at": "1960-07-11",
"description": "A novel set in the American South during the 1930s, dealing with themes of racial injustice and moral growth.",
"isbn": "9780061120084",
"total_pages": 281,
"created_at": "2024-03-01T21:48:25.000Z",
"updated_at": "2024-03-01T21:48:25.000Z"
},
{
"id": 6,
"title": "1984",
"author": "George Orwell",
"published_at": "1949-06-08",
"description": "A dystopian novel depicting a totalitarian regime, surveillance, and propaganda.",
"isbn": "9780451524935",
"total_pages": 328,
"created_at": "2024-03-01T21:48:25.000Z",
"updated_at": "2024-03-01T21:48:25.000Z"
}
]
列出偏移之后的图书：
curl -X GET "http://localhost:3000/books?o=5"
结果：
[
{
"id": 7,
"title": "Pride and Prejudice",
"author": "Jane Austen",
"published_at": "1813-01-28",
"description": "A classic novel exploring the themes of love, reputation, and social class in Georgian England.",
"isbn": "9780486284736",
"total_pages": 279,
"created_at": "2024-03-01T21:48:25.000Z",
"updated_at": "2024-03-01T21:48:25.000Z"
},
{
"id": 8,
"title": "The Catcher in the Rye",
"author": "J.D. Salinger",
"published_at": "1951-07-16",
"description": "A novel narrated by a disaffected teenager, exploring themes of alienation and identity.",
"isbn": "9780316769488",
"total_pages": 277,
"created_at": "2024-03-01T21:48:25.000Z",
"updated_at": "2024-03-01T21:48:25.000Z"
},
{
"id": 9,
"title": "The Lord of the Rings",
"author": "J.R.R. Tolkien",
"published_at": "1954-07-29",
"description": "A high fantasy epic following the quest to destroy the One Ring and defeat the Dark Lord Sauron.",
"isbn": "9780544003415",
"total_pages": 1178,
"created_at": "2024-03-01T21:48:25.000Z",
"updated_at": "2024-03-01T21:48:25.000Z"
},
{
"id": 10,
"title": "Moby-Dick",
"author": "Herman Melville",
"published_at": "1851-10-18",
"description": "A novel exploring themes of obsession, revenge, and the nature of good and evil.",
"isbn": "9780142000083",
"total_pages": 624,
"created_at": "2024-03-01T21:48:25.000Z",
"updated_at": "2024-03-01T21:48:25.000Z"
},
{
"id": 11,
"title": "The Hobbit",
"author": "J.R.R. Tolkien",
"published_at": "1937-09-21",
"description": "A fantasy novel set in Middle-earth, following the adventure of Bilbo Baggins and the quest for treasure.",
"isbn": "9780345339683",
"total_pages": 310,
"created_at": "2024-03-01T21:48:25.000Z",
"updated_at": "2024-03-01T21:48:25.000Z"
}
]
列出另一个偏移后的图书：
curl -X GET "http://localhost:3000/books?o=10"
结果：
[
{
"id": 12,
"title": "The Adventures of Huckleberry Finn",
"author": "Mark Twain",
"published_at": "1884-12-10",
"description": "A novel depicting the journey of a young boy and an escaped slave along the Mississippi River.",
"isbn": "9780486280615",
"total_pages": 366,
"created_at": "2024-03-01T21:48:25.000Z",
"updated_at": "2024-03-01T21:48:25.000Z"
},
{
"id": 13,
"title": "War and Peace",
"author": "Leo Tolstoy",
"published_at": "1869-01-01",
"description": "A novel depicting the Napoleonic era in Russia, exploring themes of love, war, and historical determinism.",
"isbn": "9781400079988",
"total_pages": 1392,
"created_at": "2024-03-01T21:48:25.000Z",
"updated_at": "2024-03-01T21:48:25.000Z"
},
{
"id": 14,
"title": "Alice’s Adventures in Wonderland",
"author": "Lewis Carroll",
"published_at": "1865-11-26",
"description": "A children’s novel featuring a young girl named Alice who falls into a fantastical world populated by peculiar creatures.",
"isbn": "9780141439761",
"total_pages": 192,
"created_at": "2024-03-01T21:48:25.000Z",
"updated_at": "2024-03-01T21:48:25.000Z"
},
{
"id": 15,
"title": "The Odyssey",
"author": "Homer",
"published_at": "8th Century BC",
"description": "An ancient Greek epic poem attributed to Homer, detailing the journey of Odysseus after the Trojan War.",
"isbn": "9780140268867",
"total_pages": 541,
"created_at": "2024-03-01T21:48:25.000Z",
"updated_at": "2024-03-01T21:48:25.000Z"
}
]
看来分页功能一切正常！进展不错！