ビュー
views/users/index.ejs
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<link rel="stylesheet" href="/stylesheets/style.css">
<title>Document</title>
</head>
<body>
<div id="header">
<h1>User Index</h1>
</div>
<div id="content">
<table>
<tr>
<th>ID</th>
<th>Name</th>
<th>Age</th>
</tr>
<% for(let i in users){ %>
<% let obj = users[i]; %>
<tr>
<td><%= obj.id %></td>
<td><%= obj.name %></td>
<td><%= obj.age %></td>
</tr>
<% } %>
</table>
<ul id="pagination" class="clearfix">
<% if(page > 1){ %>
<li><a class="paging_item" href="/users/1" title="最初のページへ"><<</a></li>
<% } else { %>
<li><span class="paging_item"><<</span></li>
<% } %>
<% if(page > 1){ %>
<li><a class="paging_item" href="/users/<%= prev %>" title="前のページへ"><</a></li>
<% } else { %>
<li><span class="paging_item"><</span></li>
<% } %>
<% if(page > 1){ %>
<li><a class="paging_item" href="/users/1">1</a></li>
<% } else { %>
<li><span class="paging_item current">1</span></li>
<% } %>
<% if(start > pageRange){ %>
<li><span>...</span></li>
<% } %>
<% for(let i in nums) { %>
<% if(nums[i] == page){ %>
<li><span class="paging_item current"><%= nums[i] %></span></li>
<% } else { %>
<li><a class="paging_item" href="/users/<%= nums[i] %>" class="num"><%= nums[i] %></a></li>
<% } %>
<% } %>
<% if ((totalPage - 1) > end){ %>
<li><span>...</span></li>
<% } %>
<% if(page < totalPage){ %>
<li><a class="paging_item" href="/users/<%= totalPage %>"><%= totalPage %></a></li>
<% } else if(totalPage == 1 || totalPage == 0) { %>
<% } else { %>
<li><span class="paging_item current"><%= totalPage %></span></li>
<% } %>
<% if(page < totalPage){ %>
<li><a class="paging_item" href="/users/<%= next %>">></a></li>
<% } else { %>
<li><span class="paging_item">></span></li>
<% } %>
<% if(page < totalPage){ %>
<li><a class="paging_item" href="/users/<%= totalPage %>">>></a></li>
<% } else { %>
<li><span class="paging_item">>></span></li>
<% } %>
</ul>
</div>
</body>
</html>public/stylesheets/style.css
body {
padding: 50px;
font: "Lucida Grande", Helvetica, Arial, sans-serif;
}
a {
color: #00B7FF;
}
.clearfix:after {
content: "";
clear: both;
display: block;
}
#pagination li {
list-style: none;
float: left;
margin-right: 20px;
line-height: 24px;
}
#pagination li a {
text-decoration: none;
}自作ライブラリ
lib/pagination.js
module.exports = class Pagination {
constructor(total){
this.view = 10; // 1ページに表示するレコード数
this.page_range = 2; // リンクを前後何ページ分表示するかの設定
this.count = total; // レコード数
this.total_page = Math.ceil(this.count / this.view); // ページ総数
this.current_page; // 現在のページ
this.sql_start = (this.current_page - 1) * this.view;
this.nums = []; // ページ番号格納用
};
notParam(){
this.current_page = 1;
this.sql_start = (this.current_page - 1) * this.view;
let prev = Math.max(this.current_page - 1, 1);
let next = Math.min(this.current_page + 1, this.total_page);
let start = Math.max(this.current_page - this.page_range, 2);
let end = Math.min(this.current_page + this.page_range, this.total_page - 1); // ページ番号終点
for (let i = start; i <= end; i++) { this.nums.push(i) }
let data = {
view: this.view,
sql_start: this.sql_start,
total_count: this.count,
prev: prev,
next: next,
start: start,
end: end,
nums: this.nums,
page: this.current_page,
pageRange: this.page_range,
totalPage: this.total_page
}
return data;
}
param(param){
this.current_page = param;
this.sql_start = (this.current_page - 1) * this.view;
let prev = Math.max(this.current_page - 1, 1);
let next = Math.min(Number(this.current_page) + 1, this.total_page);
let start = Math.max(this.current_page - this.page_range, 2);
let end = Math.min(Number(this.current_page) + Number(this.page_range), this.total_page - 1); // ページ番号終点
for (let i = start; i <= end; i++) { this.nums.push(i) }
let data = {
view: this.view,
sql_start: this.sql_start,
total_count: this.count,
prev: prev,
next: next,
start: start,
end: end,
nums: this.nums,
page: this.current_page,
pageRange: this.page_range,
totalPage: this.total_page
}
return data;
}
}ルーティング
app.js
var usersRouter = require('./routes/users');
app.use('/users', usersRouter);routes/users.js
var express = require('express');
var router = express.Router();
const Users = require('../models/User');
const Pagination = require('../lib/pagination');
router.get('/', function(req, res, next) {
new Promise((resolve)=>{
Users.countUser().then((total) => {
let pagination = new Pagination(total);
let data = pagination.notParam();
Users.limitUser(data["sql_start"], data["view"]).then((results) => {
data['users'] = results;
data['msg'] = "";
res.render('users/index', data);
})
})
})
});
router.get('/:page', function(req, res, next) {
new Promise((resolve)=>{
Users.countUser().then((total) => {
let pagination = new Pagination(total);
let data = pagination.param(req.params.page);
Users.limitUser(data["sql_start"], data["view"]).then((results) => {
data['users'] = results;
data['msg'] = "";
res.render('users/index', data);
})
})
})
});
module.exports = router;モデル
models/User.js
const mysql = require('mysql2/promise');
const pool = require('../db');
module.exports = {
countUser: () => {
return new Promise ((resolve, reject) => {
(async () => {
try {
const [results, fields] = await pool.query('SELECT COUNT(*) FROM users');
resolve(results[0]["COUNT(*)"]);
} catch (err) {
console.log(err);
}
})();
});
},
limitUser: (sql_start, view) => {
return new Promise ((resolve, reject) => {
(async () => {
try {
const [results, fields] = await pool.query('SELECT * FROM users LIMIT ?, ?', [sql_start, view]);
resolve(results);
} catch (err) {
console.log(err);
}
})();
});
}
}データベース設定
db.js
const mysql = require('mysql2/promise');
const pool = mysql.createPool({
host: 'localhost',
user: 'root',
database: 'users',
password: 'password',
});
module.exports = pool;サンプルデータ投入用プログラム
const mysql = require('mysql2/promise');
const pool = mysql.createPool({
host: 'localhost',
user: 'root',
database: 'users',
password: 'password',
});
(async () => {
try {
for(let i = 0; i < 200; i++){
await pool.query('INSERT INTO users (name, age) VALUES (?, ?);', ["taro" + i, i] );
}
} catch (err) {
console.log(err);
}
pool.end();
})();