データベース情報
データベース mydb の作成
CREATE DATABASE mydb;users テーブルの作成
CREATE TABLE users (
id INT AUTO_INCREMENT,
name VARCHAR(255) NOT NULL,
age INT NULL,
PRIMARY KEY (id)
);以下のスクリプトをターミナルで実行し、サンプルデータの登録をします。
script.js
const mysql = require('mysql2/promise');
const pool = mysql.createPool({
host: 'localhost',
user: 'root',
database: 'mydb',
password: 'password',
});
(async () => {
try {
for(let i = 0; i < 200; i++){
await pool.query('INSERT INTO users (name, age) VALUES (?, ?);', ["name_" + i, i] );
}
} catch (err) {
console.log(err);
}
pool.end();
})();ビュー
app/views/myapp/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 of users){ %>
<tr>
<td><%= i.id %></td>
<td><%= i.name %></td>
<td><%= i.age %></td>
</tr>
<% } %>
</table>
<ul id="pagination" class="clearfix">
<li><a class="paging_item" href="/myapp/1">First</a></li>
<% if(page > 1){ %>
<li><a class="paging_item" href="/myapp/<%= prev %>" title="前のページへ"><</a></li>
<% } else { %>
<li><span class="paging_item"><</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="/myapp/<%= nums[i] %>" class="num"><%= nums[i] %></a></li>
<% } %>
<% } %>
<% if(page < totalPage){ %>
<li><a class="paging_item" href="/myapp/<%= next %>">></a></li>
<% } else { %>
<li><span class="paging_item">></span></li>
<% } %>
<% if(totalPage != 0) { %>
<li><a class="paging_item" href="/myapp/<%= totalPage %>">LAST</a></li>
<% } %>
</ul>
</div>
</body>
</html>app/public/stylesheets/style.css
body {
padding: 50px;
font: 14px "Lucida Grande", Helvetica, Arial, sans-serif;
}
a {
color: #00B7FF;
}
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;
}自作ライブラリ
app/lib/pagination.js
module.exports = class Pagination {
constructor(total, page_num, current_page, link_num){
this.view = Number(page_num); // 1ページに表示するレコード数
this.count = Number(total); // レコード数
this.total_page = Math.ceil(this.count / this.view); // ページ総数
this.current_page = Number(current_page); // 現在のページ
this.sql_start = (this.current_page - 1) * this.view;
this.nums = []; // ページ番号格納用
this.link = link_num; // 表示するリンクの数
};
paginate(){
let prev = Math.max(this.current_page - 1, 1);
let next = Math.min(this.current_page + 1, this.total_page);
let number = Math.floor(this.link / 2);
if(this.total_page < this.link) {
for (let i = 1; i <= this.total_page; i++) { this.nums.push(i) }
} else {
if(this.current_page <= this.link){
for (let i = 1; i <= this.link; i++) { this.nums.push(i) }
} else if(this.current_page >= this.total_page - number) {
for (let i = this.total_page - this.link + 1; i <= this.total_page ; i++) { this.nums.push(i) }
} else {
if (this.link % 2 == 1) {
for (let i = this.current_page - number; i <= this.current_page + number; i++) { this.nums.push(i) }
} else if (this.link % 2 == 0){
for (let i = this.current_page - number; i <= this.current_page - 1 + number; i++) { this.nums.push(i) }
}
}
}
let data = {
view: this.view,
sql_start: this.sql_start,
total_count: this.count,
prev: prev,
next: next,
nums: this.nums,
page: this.current_page,
totalPage: this.total_page
}
return data;
}
}ルーティング
app/app.js
var createError = require('http-errors');
var express = require('express');
var path = require('path');
var cookieParser = require('cookie-parser');
var logger = require('morgan');
var topsRouter = require('./routes/tops');
var myappRouter = require('./routes/myapp');
var app = express();
// view engine setup
app.set('views', path.join(__dirname, 'views'));
app.set('view engine', 'ejs');
app.use(logger('dev'));
app.use(express.json());
app.use(express.urlencoded({ extended: false }));
app.use(cookieParser());
app.use(express.static(path.join(__dirname, 'public')));
app.use('/tops', topsRouter);
app.use('/myapp', myappRouter);
// catch 404 and forward to error handler
app.use(function(req, res, next) {
next(createError(404));
});
// error handler
app.use(function(err, req, res, next) {
// set locals, only providing error in development
res.locals.message = err.message;
res.locals.error = req.app.get('env') === 'development' ? err : {};
// render the error page
res.status(err.status || 500);
res.render('error');
});
module.exports = app;app/routes/myapp.js
var express = require('express');
var router = express.Router();
const Users = require('../models/User');
const Pagination = require('../lib/pagination');
router.get('/:page', function(req, res, next) {
Users.countMax().then((record_max) => {
return new Promise((resolve, reject) => {
resolve(record_max)
})
}).then((record_max) => {
let pagination = new Pagination(record_max, 10, req.params.page);
let data = pagination.paginate();
Users.allUser(data["sql_start"], data["view"]).then((results) => {
data['users'] = results;
res.render('myapp/index', data);
})
})
});
module.exports = router;let pagination = new Pagination(record_max, 10, req.params.page);
record_max: データベースのレコード総数
10: 1ページに表示するレコードの数
req.params.page: 現在のページ数
モデル
app/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);
}
})();
});
}
}データベース設定
app/config/db.js
const mysql = require('mysql2/promise');
const pool = mysql.createPool({
host: 'localhost',
user: 'root',
database: 'users',
password: 'password',
});
module.exports = pool; myapp へのリンク
<a href="/myapp/1">myapp</a>