NodeJs实战-待办列表5-使用MySQL存储待办事项
之前的章节是基于内存存储待办事项的,那么会产生一个问题,服务重启之后,待办事项数据会丢失,本节将数据存储到数据库中。
项目结构
需要了解的基础知识
- MySQL 数据库,SQL 语句
- NodeJS web 服务
创建数据库,数据表
- 创建数据库
create database test charset=utf8mb4;
- 创建数据表
CREATE TABLE `t_todo_list` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`item_name` varchar(50) DEFAULT NULL,
`create_time` datetime DEFAULT NULL,
`complete_time` datetime DEFAULT NULL,
`todo_status` int(11) DEFAULT NULL COMMENT '0-初始状态 1-已完成',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
NodeJS 如何连接 MySQL,执行SQL
- 安装 mysql 模块 npm install mysql
F:\Github\Nodejs\todolist>npm install mysql
- 编写 demo 程序连接
创建数据库连接池 db
F:\Github\Nodejs\todolist>node
Welcome to Node.js v18.12.1.
Type ".help" for more information.
> const mysql = require('mysql')
undefined
> const db = mysql.createPool({
... host: "127.0.0.1",
... port: 3306,
... user: 'root',
... password: 'shootercheng',
... database: "test"
... })
undefined
> db.query('select 1', (err, result) => {console.log(result)});
<ref *1> Query {
_events: [Object: null prototype] {},
_eventsCount: 0,
_maxListeners: undefined,
_callback: [Function: runBound],
_callSite: Error
at Pool.query (F:\Github\Nodejs\todolist\node_modules\mysql\lib\Pool.js:199:23)
at REPL15:1:4
at Script.runInThisContext (node:vm:129:12)
at REPLServer.defaultEval (node:repl:572:29)
at bound (node:domain:433:15)
at REPLServer.runBound [as eval] (node:domain:444:12)
at REPLServer.onLine (node:repl:902:10)
at REPLServer.emit (node:events:525:35)
at REPLServer.emit (node:domain:489:12)
at [_onLine] [as _onLine] (node:internal/readline/interface:425:12),
_ended: false,
_timeout: undefined,
_timer: Timer { _object: [Circular *1], _timeout: null },
sql: 'select 1',
values: undefined,
typeCast: true,
nestTables: false,
_resultSet: null,
_results: [],
_fields: [],
_index: 0,
_loadError: null,
[Symbol(kCapture)]: false
}
> [ RowDataPacket { '1': 1 } ]
获取当前时间
> const silly = require('silly-datetime')
undefined
> silly.format(new Date(), 'YYYY-MM-DD HH:mm:ss')
'2022-11-19 20:32:38'
完整的 test.js 代码如下
const mysql = require('mysql');
const db = mysql.createPool({
host: "127.0.0.1",
port: 3306,
user: 'root',
password: 'shootercheng',
database: "test"
});
const silly = require('silly-datetime');
function currentTime() {
return silly.format(new Date(), 'YYYY-MM-DD HH:mm:ss');
}
function insertTest() {
db.query('insert into t_todo_list(item_name, create_time) values(?, ?)', ['中文', currentTime()], (err, result) => {
console.log(result);
});
}
function selectTest() {
db.query('select * from t_todo_list where id = ?', ['2'],(err, result) => {
if (err) {
console.log(err);
return;
}
for (var index in result) {
console.log(result[index]);
}
});
}
function updateTest() {
db.query('update t_todo_list set complete_time = ? where id = ?', [currentTime(), 2],
(err, result) => {
console.log(result);
})
}
insertTest();
selectTest();
updateTest();
- 运行 node test.js 可以看到如下信息
F:\Github\Nodejs\todolist>node test.js
RowDataPacket {
id: 2,
item_name: '中文',
create_time: 2022-11-19T09:20:13.000Z,
complete_time: 2022-11-19T12:35:12.000Z
}
OkPacket {
fieldCount: 0,
affectedRows: 1,
insertId: 4,
serverStatus: 2,
warningCount: 0,
message: '',
protocol41: true,
changedRows: 0
}
OkPacket {
fieldCount: 0,
affectedRows: 1,
insertId: 0,
serverStatus: 2,
warningCount: 0,
message: '(Rows matched: 1 Changed: 1 Warnings: 0',
protocol41: true,
changedRows: 1
}
- 编写 demo 程序中遇到的问题
刚开始使用 nodejs 的 mysql 模块,不知道 query 方法是异步执行的,写了如下方法
const mysql = require('mysql');
const db = mysql.createPool({
host: "127.0.0.1",
port: 3306,
user: 'root',
password: 'shootercheng',
database: "test"
});
function selectTest() {
var dataList = [];
db.query('select * from t_todo_list where id = ?', ['2'],(err, result) => {
if (err) {
console.log(err);
return;
}
for (var index in result) {
dataList.push(result[index]);
}
});
console.log(dataList);
return dataList;
}
selectTest();
调用这个方法会输出啥?将此方法保存到 js 文件 test_async.js
发现数据没有存入到 list 中,说明是异步执行了。如果在输出的地方加个延时,等查询执行完毕。
// 输出延时3秒钟,等异步查询数据完毕
setTimeout(() => {console.log(dataList)}, 3000);
修改服务端代码 server.js
经过前面的demo 程序,学会了如何使用 nodejs mysql 模块实现增删改查。需要把原来对内存的增删改查修改成对数据库的增删改查就可以了
- 增加查询方法
function doQuery(response) {
db.query('select item_name from t_todo_list where todo_status = ?', ['0'], (err, result) => {
if (err) {
console.log(err);
var data = buildData(CODE_ERROR, [], '查询数据库失败');
sendMsg(response, JSON.stringify(data));
return;
}
var itemList = [];
for (var i in result) {
itemList.push(result[i].item_name);
}
var data = buildData(CODE_SUCCESS, itemList, '查询成功');
sendMsg(response, JSON.stringify(data));
});
}
- 增加添加待办事项方法
function doAdd(response, itemData) {
db.query('select count(*) as num from t_todo_list where item_name = ? and todo_status = ?', [itemData,'0'], (err, result) => {
if (err) {
console.log(err);
var data = buildData(CODE_ERROR, [], '查询数据库失败');
sendMsg(response, JSON.stringify(data));
return;
}
if (result[0].num > 0) {
var data = buildData(CODE_ERROR, [], itemData + '-待办事项已存在');
sendMsg(response, JSON.stringify(data));
return;
}
db.query('insert into t_todo_list(item_name, create_time, todo_status) values(?, ?, ?)', [itemData, currentTime(), '0'], (err, result) => {
if (err) {
console.log(err);
var data = buildData(CODE_ERROR, [], itemData + '-待办事项添加到数据库失败');
sendMsg(response, JSON.stringify(data));
return;
}
doQuery(response);
});
});
}
- 增加完成待办事项方法
function doCompelete(response, itemData) {
db.query('select count(*) as num from t_todo_list where item_name = ? and todo_status = ?', [itemData, '0'], (err, result) => {
if (err) {
console.log(err);
var data = buildData(CODE_ERROR, [], '查询数据库失败');
sendMsg(response, JSON.stringify(data));
return;
}
if (result[0].num == 0) {
var data = buildData(CODE_ERROR, [], itemData + '-待办事项不存在');
sendMsg(response, JSON.stringify(data));
return;
}
db.query('update t_todo_list set complete_time = ?, todo_status = ? where item_name = ?', [currentTime(), '1', itemData], (err, result) => {
if (err) {
console.log(err);
var data = buildData(CODE_ERROR, [], itemData + '-更新待办事项失败');
sendMsg(response, JSON.stringify(data));
return;
}
doQuery(response);
});
});
}
- 修改主方法
const server = http.createServer((request, response) => {
var urlParse = parse(request.url);
var urlPath = urlParse.pathname;
var itemData;
if (urlPath == '/add' || urlPath == '/complete') {
itemData = findItemData(urlParse);
if (itemData.length == 0) {
var data = buildData(CODE_ERROR, [], '输入数据有误');
return sendMsg(response, JSON.stringify(data));
}
}
switch (urlPath) {
case '/':
var filePath = 'public/index.html';
var absPath = './' + filePath;
readFile(response, absPath);
break;
case '/query':
doQuery(response);
break;
case '/add':
doAdd(response, itemData);
break;
case '/complete':
doCompelete(response, itemData);
break;
}
});
- 完整的 server.js
const http = require('http');
const fs = require('fs');
const parse = require('url').parse;
const mysql = require('mysql');
const silly = require('silly-datetime');
const db = mysql.createPool({
host: "127.0.0.1",
port: 3306,
user: 'root',
password: 'shootercheng',
database: "test"
});
const hostname = '127.0.0.1';
const port = 3000;
const CODE_ERROR = 0;
const CODE_SUCCESS = 1;
function send404(response) {
response.writeHead(404, {'Content-Type': 'text/plain'});
response.write('Error 404: resource not found.');
response.end();
}
function sendMsg(response, msg) {
response.writeHead(200, {'Content-Type': 'application/json;charset=UTF-8'});
response.write(msg);
response.end();
}
function readFile(response, filePath) {
fs.readFile(filePath, (err, data) => {
if (err) {
return send404(response);
}
var html = data.toString();
// html = html.replace('%', Array.from(todoSet).join('</li><li>'));
response.writeHead(200, {'Content-Type': 'text/html'});
response.end(html);
});
}
function findItemData(urlParse) {
if (urlParse.query.length > 0) {
var queryArray = urlParse.query.split('=');
if (queryArray.length >= 2) {
return decodeURI(queryArray[1]);
}
}
return '';
}
function buildData(code, data, msg) {
// 返回数据
let retData = {
'code':'',
'data': [],
'msg':''
}
retData.code = code;
retData.data = data;
retData.msg = msg;
return retData;
}
/**
* 获取当前时间
* @returns
*/
function currentTime() {
return silly.format(new Date(), 'YYYY-MM-DD HH:mm:ss');
}
/**
* 查询待办数据,并且输出到浏览器
* @param {*} response
*/
function doQuery(response) {
db.query('select item_name from t_todo_list where todo_status = ?', ['0'], (err, result) => {
if (err) {
console.log(err);
var data = buildData(CODE_ERROR, [], '查询数据库失败');
sendMsg(response, JSON.stringify(data));
return;
}
var itemList = [];
for (var i in result) {
itemList.push(result[i].item_name);
}
var data = buildData(CODE_SUCCESS, itemList, '查询成功');
sendMsg(response, JSON.stringify(data));
});
}
/**
* 添加待办事项
* @param {*} response
* @param {*} itemData
*/
function doAdd(response, itemData) {
db.query('select count(*) as num from t_todo_list where item_name = ? and todo_status = ?', [itemData,'0'], (err, result) => {
if (err) {
console.log(err);
var data = buildData(CODE_ERROR, [], '查询数据库失败');
sendMsg(response, JSON.stringify(data));
return;
}
if (result[0].num > 0) {
var data = buildData(CODE_ERROR, [], itemData + '-待办事项已存在');
sendMsg(response, JSON.stringify(data));
return;
}
db.query('insert into t_todo_list(item_name, create_time, todo_status) values(?, ?, ?)', [itemData, currentTime(), '0'], (err, result) => {
if (err) {
console.log(err);
var data = buildData(CODE_ERROR, [], itemData + '-待办事项添加到数据库失败');
sendMsg(response, JSON.stringify(data));
return;
}
doQuery(response);
});
});
}
/**
* 完成待办事项
* @param {*} response
* @param {*} itemData
*/
function doCompelete(response, itemData) {
db.query('select count(*) as num from t_todo_list where item_name = ? and todo_status = ?', [itemData, '0'], (err, result) => {
if (err) {
console.log(err);
var data = buildData(CODE_ERROR, [], '查询数据库失败');
sendMsg(response, JSON.stringify(data));
return;
}
if (result[0].num == 0) {
var data = buildData(CODE_ERROR, [], itemData + '-待办事项不存在');
sendMsg(response, JSON.stringify(data));
return;
}
db.query('update t_todo_list set complete_time = ?, todo_status = ? where item_name = ?', [currentTime(), '1', itemData], (err, result) => {
if (err) {
console.log(err);
var data = buildData(CODE_ERROR, [], itemData + '-更新待办事项失败');
sendMsg(response, JSON.stringify(data));
return;
}
doQuery(response);
});
});
}
const server = http.createServer((request, response) => {
var urlParse = parse(request.url);
var urlPath = urlParse.pathname;
var itemData;
if (urlPath == '/add' || urlPath == '/complete') {
itemData = findItemData(urlParse);
if (itemData.length == 0) {
var data = buildData(CODE_ERROR, [], '输入数据有误');
return sendMsg(response, JSON.stringify(data));
}
}
switch (urlPath) {
case '/':
var filePath = 'public/index.html';
var absPath = './' + filePath;
readFile(response, absPath);
break;
case '/query':
doQuery(response);
break;
case '/add':
doAdd(response, itemData);
break;
case '/complete':
doCompelete(response, itemData);
break;
}
});
server.listen(port, hostname, () => {
console.log(`Server running at http://${hostname}:${port}/`)
})
验证
执行命令 node server.js,启动项目
添加待办
- 1111
- en1111
- 中文1111
完成待办
- 1111
- en1111
- 中文1111
查看数据库的数据是否符合预期
项目代码地址
Gitee代码仓库: https://gitee.com/3281328128/todo_list/tree/mysql/