NodeJs实战-待办列表(5)-使用MySQL存储待办事项

之前的章节是基于内存存储待办事项的，那么会产生一个问题，服务重启之后，待办事项数据会丢失，本节将数据存储到数据库中。

项目结构

需要了解的基础知识

1. MySQL 数据库，SQL 语句
2. NodeJS web 服务

创建数据库，数据表

1. 创建数据库

create database test charset=utf8mb4;
1

2. 创建数据表

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;
1
2
3
4
5
6
7
8

NodeJS 如何连接 MySQL，执行SQL

1. 安装 mysql 模块 npm install mysql

F:\Github\Nodejs\todolist>npm install mysql
1

2. 编写 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 } ]
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45

获取当前时间

> const silly = require('silly-datetime')
undefined
> silly.format(new Date(), 'YYYY-MM-DD HH:mm:ss')
'2022-11-19 20:32:38'
1
2
3
4

完整的 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();
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43

3. 运行 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
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

4. 编写 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();
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

调用这个方法会输出啥？将此方法保存到 js 文件 test_async.js

发现数据没有存入到 list 中，说明是异步执行了。如果在输出的地方加个延时，等查询执行完毕。

 // 输出延时3秒钟，等异步查询数据完毕
 setTimeout(() => {console.log(dataList)}, 3000);
1
2

修改服务端代码 server.js

经过前面的demo 程序，学会了如何使用 nodejs mysql 模块实现增删改查。需要把原来对内存的增删改查修改成对数据库的增删改查就可以了

1. 增加查询方法

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));
	});
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

2. 增加添加待办事项方法

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);
		});
	});
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

3. 增加完成待办事项方法

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);
		});
	});
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

4. 修改主方法

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;
	}
});
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

5. 完整的 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('