利用EXPRESSJS编写RESET-API

expressJS是个很轻量且简单的JavaScript后端框架，基于nodeJS开发。可以利用express简单的就可以输出reset api，当然它可以连接任意数据库。

准备工作

1、安装nodeJS（请自行安装）

2、安装express-generator（采用全局安装）

npm install express-generator -g

3、创建项目（我采用的是ejs模板引擎）

express –view=ejs myapp

4、连接mysql，请自行安装mysql数据库，我们需要安装mysql依赖

npm i mysql

创建数据库名为chat并创建数据库表：

`CREATE TABLE IF NOT EXISTS message(
            id char(100) PRIMARY KEY,
            from_user_id int(20) NOT NULL,
            to_user_id int(20) NOT NULL,
            read_status int(1) NOT NULL DEFAULT 0,
            content text NOT NULL,
            create_time timestamp NOT NULL DEFAULT NOW()
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8;`

数据库连接编写

dbConfig.js

const db = require('mysql')
const connection = db.createPool({
    host: 'localhost',
    user: 'root',
    password: '123456',
    database: 'chat'
})

const testMysqlConnection = () => {
    return new Promise((resolve, reject) => {
        // 测试mysql是否已经连接成功
        connection.query('SELECT 1 + 1 AS solution', (err, rows, fields) => {
            if(err) {
                reject({status: 0, err})
            }
            resolve({status: rows[0].solution})
        })
    })
}

module.exports = {connection, testMysqlConnection}

reset api编写（增删改查）

msgModel.js

const db = require('../config/dbConfig').connection

const chat = {
    // 分页查询所有聊天记录
    getAllMsg: (params) => {
        return new Promise((resolve, reject) => {
            // 传入页码、数量
            let currentPage = params.currentPage ? +params.currentPage : 1
            let pageSize = params.pageSize ? +params.pageSize : 10
            let count_sql = 'select COUNT(*) from users'
            let sql='select SQL_CALC_FOUND_ROWS * from message order by create_time desc limit ' + (currentPage - 1) + ',' + pageSize
            db.query(count_sql, (c_err, c_rows) => {
                if(err){
                    reject({
                        status: 0,
                        data: [],
                        message: c_err
                    })
                }
                db.query(sql, (err, rows) => {
                    if(err) {
                        reject({
                            status: 0,
                            data: [],
                            message: err
                        })
                    }
                    resolve({
                        status: 2,
                        data: rows,
                        count: c_rows[0]['COUNT(*)'],
                        message: 'Get all message'
                    })
                })
               
            })
        })
    },
    getMsgById: (_msg) => {
        // 由于消息数量可能会过多，因此需要进行分页，分页条数为50条
        return new Promise((resolve, reject) => {
            let currentPage = _msg.currentPage ? _msg.currentPage : 1
            let pageSize = 50
            let id = _msg['id']
            let count_sql = 'select COUNT(*) from message where id like "'+ id +'%"'
           
            db.query(count_sql, (c_err, c_rows) => {
                if(c_err) {
                    reject({
                        status: 0,
                        data: [],
                        message: c_err
                    })
                }
                // 计算页数总数
                let pages
                let pageStart
                if(c_rows &amp;&amp; c_rows[0]['COUNT(*)']) {
                    pages = Math.ceil(c_rows[0]['COUNT(*)'] / pageSize)
                }
                if(pages) {
                    pageStart = (+pages - +currentPage) &lt;0 ? 1 : (+pages - +currentPage)
                }else {
                    pageStart = 0
                }
                let sql='select * from message where id like "'+ id +'%" order by create_time asc limit ' + (pageStart * pageSize) + ',' + pageSize
                console.log(pages, sql)
                db.query(sql, [id], (err, rows) => {
                    if(err) {
                        reject({
                            status: 0,
                            data: [],
                            message: err
                        })
                    }
                    resolve({
                        status: 2,
                        data: rows,
                        count: c_rows[0] &amp;&amp; c_rows[0]['COUNT(*)'] ? c_rows[0]['COUNT(*)'] : [],
                        message: 'Get all message'
                    })
                })

            })
        })
    },
    // 获取未读状态的信息id
    getMsgByStatus: (id) => {
        return new Promise((resolve, reject) => {
            let sql = 'select id,from_user_id,to_user_id from message where read_status = 0 and id like "%' + id + '%"' // (id like "%'+ '_'+id +'%" or id like "%'+ id+'_' +'%")
            // console.log('sql', sql)
            db.query(sql, (err, rows) => {
                if(err) {
                    reject({
                        status: 0,
                        data: [],
                        message: err
                    })
                }
                let newData = []
                let data = []
                if(rows &amp;&amp; rows.length>0) {
                    for(let i = 0; i &lt; rows.length; i++){
                        rows[i]['unreadMessage'] = 1
                        newData.push(rows[i])
                    }
                }
                let addUnreadMsgHaveSameId = (array) => {
                    const hash = {};
                    array.map(item => {
                        const id = item['to_user_id'];
                        if (hash[id]) {
                            hash[id]['unreadMessage'] += item['unreadMessage'];
                        } else {
                            hash[id] = item;
                        }
                    });
                    return Object.values(hash);
                  }
                  console.log('rows', rows)
                  data = addUnreadMsgHaveSameId(newData)
                resolve({
                    status: 2,
                    data: data,
                    msg: 'get all unread message'
                })
            })
        })
    },
    addMsg: (_chat) => {
        return new Promise((resolve, reject) => {
            console.log(_chat)
            if(_chat.id &amp;&amp; _chat.from_user_id &amp;&amp; _chat.to_user_id &amp;&amp; _chat.content){
                let sql = 'insert into message(id, from_user_id, to_user_id, content) values(?,?,?,?)'
                let chatParams = [_chat.id, _chat.from_user_id, _chat.to_user_id, _chat.content]
                db.query(sql,  chatParams, (err, rows) => {
                    if(err) {
                        reject({
                            status: 0,
                            data: [],
                            message: err
                        })
                    }
                    resolve({status: 2, data: rows, message: 'add message success'})
                })
            }else {
                resolve({
                    status: 0,
                    data: [],
                    message: 'Incomplete message information'
                })
            }
        })
    },
    updateMsgReadStatus: (_chat) => {
        return new Promise((resolve, reject) => {
            console.log('params',_chat)
            let s_sql = 'select * from message where id like "%'+ _chat.id +'%" and from_user_id=' + _chat.to_user_id
            db.query(s_sql, (s_err, s_rows) => {
                if(s_err){
                    reject({
                        status: 0,
                        data: [],
                        message: s_err
                    })
                }
                for(let i=0;i&lt;s_rows.length;i++) {
                    let sql = 'update message set read_status = 1 where id=? and from_user_id=' + _chat.to_user_id
                    db.query(sql,[s_rows[i]['id']], (err, rows) => {
                        if(err) {
                            reject({
                                status: 0,
                                data: [],
                                message: err
                            })
                        }
                        resolve({status: 2, data: rows, message: 'update message success'})
                    })
                }
            })
        })
    },
    updateMsg: (_chat) => {
        // 更新仅能更新阅读状态以及消息内容
        return new Promise((resolve, reject) => {
            let _chatParams = [
                _chat.read_status ? _chat.read_status : 0,
                _chat.content ? _chat.content : null
            ]
            _chatParams = _chatParams.filter(item => item)
            let hasReadStatus = _chat.read_status || _chat.read_status == 0 ? 'read_status=?' : ''
            let hasContent = _chat.content ? 'content=?' : ''
            let haveTwoParams = hasReadStatus &amp;&amp; hasContent ? ',' : ''
            let sql = 'update message set ' + hasReadStatus + haveTwoParams + hasContent + ' where id like "'+ _chat.id +'%"'
            if(_chat.id) {
                db.query(sql, _chatParams, (err, rows) => {
                    if(err) {
                        reject({
                            status: 0,
                            data: [],
                            message: err
                        })
                    }
                    resolve({status: 2, data: rows, message: 'update message success'})
                })
            }else {
                resolve({
                    status: 0,
                    data: [],
                    message: 'id is null, please check'
                })
            }
        })
    },
    deleteMsg: (_chat) => {
        // 批量删除聊天记录
        return new Promise((resolve, reject) => {
            let _ids = _chat.ids
            _ids = '"' + JSON.parse(_ids).join('","') + '"'
            let sql = 'delete from message where id in ('+ _ids +')'
            db.query(sql, (err, rows) => {
                if(err) {
                    reject({
                        status: 0,
                        data: [],
                        message: err
                    })
                }
                resolve({status:2, data: rows, message: 'delete messages success'})
            })
        })
    }
}

module.exports = chat

router/msg.js

const express = require('express');
const router = express.Router();
const chat = require('../models/msgModel');

router.get('/:id/:currentPage', (req, res) => {
    let body = req.params
    // console.log(req.body, req.params, req.query)
    chat.getMsgById(body).then(result => {
        res.json(result)
    }, err => {
        res.json(500, 'Select dababase is error')
    })
})

// add message
router.post('/', (req, res) => {
    let body = req.body
    chat.addMsg(body).then(result => {
        res.json(result)
    }, err => {
        res.json(500, 'add message is error')
    })
})

// update msg
router.put('/', (req, res) => {
    let body = req.body
    console.log(req.body, req.params, req.query)
    chat.updateMsgReadStatus(body).then(result => {
        res.json(result)
    }, err => {
        res.json(err)
    })
})

// delete msg 批量删除
router.delete('/', (req, res) => {
    // 传入一个字符串，但是格式必须为'[1,2,3,4]'，以便后端解析
    console.log('req', req.body, req.params, req.query)
    let body = req.body
    chat.deleteMsg(body).then(result => {
        res.json(result)
    }, err => {
        res.json(err)
    })
})

module.exports = router

1	`CREATE TABLE IF NOT EXISTS message(
2	id char(100) PRIMARY KEY,
3	from_user_id int(20) NOT NULL,
4	to_user_id int(20) NOT NULL,
5	read_status int(1) NOT NULL DEFAULT 0,
6	content text NOT NULL,
7	create_time timestamp NOT NULL DEFAULT NOW()
8	) ENGINE=InnoDB DEFAULT CHARSET=utf8;`

1	const db = require('mysql')
2	const connection = db.createPool({
3	host: 'localhost',
4	user: 'root',
5	password: '123456',
6	database: 'chat'
7	})
8
9	const testMysqlConnection = () => {
10	return new Promise((resolve, reject) => {
11	// 测试mysql是否已经连接成功
12	connection.query('SELECT 1 + 1 AS solution', (err, rows, fields) => {
13	if(err) {
14	reject({status: 0, err})
15	}
16	resolve({status: rows[0].solution})
17	})
18	})
19	}
20
21	module.exports = {connection, testMysqlConnection}

1	const db = require('../config/dbConfig').connection
2
3	const chat = {
4	// 分页查询所有聊天记录
5	getAllMsg: (params) => {
6	return new Promise((resolve, reject) => {
7	// 传入页码、数量
8	let currentPage = params.currentPage ? +params.currentPage : 1
9	let pageSize = params.pageSize ? +params.pageSize : 10
10	let count_sql = 'select COUNT(*) from users'
11	let sql='select SQL_CALC_FOUND_ROWS * from message order by create_time desc limit ' + (currentPage - 1) + ',' + pageSize
12	db.query(count_sql, (c_err, c_rows) => {
13	if(err){
14	reject({
15	status: 0,
16	data: [],
17	message: c_err
18	})
19	}
20	db.query(sql, (err, rows) => {
21	if(err) {
22	reject({
23	status: 0,
24	data: [],
25	message: err
26	})
27	}
28	resolve({
29	status: 2,
30	data: rows,
31	count: c_rows[0]['COUNT(*)'],
32	message: 'Get all message'
33	})
34	})
35
36	})
37	})
38	},
39	getMsgById: (_msg) => {
40	// 由于消息数量可能会过多，因此需要进行分页，分页条数为50条
41	return new Promise((resolve, reject) => {
42	let currentPage = _msg.currentPage ? _msg.currentPage : 1
43	let pageSize = 50
44	let id = _msg['id']
45	let count_sql = 'select COUNT(*) from message where id like "'+ id +'%"'
46
47	db.query(count_sql, (c_err, c_rows) => {
48	if(c_err) {
49	reject({
50	status: 0,
51	data: [],
52	message: c_err
53	})
54	}
55	// 计算页数总数
56	let pages
57	let pageStart
58	if(c_rows && c_rows[0]['COUNT(*)']) {
59	pages = Math.ceil(c_rows[0]['COUNT(*)'] / pageSize)
60	}
61	if(pages) {
62	pageStart = (+pages - +currentPage) <0 ? 1 : (+pages - +currentPage)
63	}else {
64	pageStart = 0
65	}
66	let sql='select * from message where id like "'+ id +'%" order by create_time asc limit ' + (pageStart * pageSize) + ',' + pageSize
67	console.log(pages, sql)
68	db.query(sql, [id], (err, rows) => {
69	if(err) {
70	reject({
71	status: 0,
72	data: [],
73	message: err
74	})
75	}
76	resolve({
77	status: 2,
78	data: rows,
79	count: c_rows[0] && c_rows[0]['COUNT()'] ? c_rows[0]['COUNT()'] : [],
80	message: 'Get all message'
81	})
82	})
83
84	})
85	})
86	},
87	// 获取未读状态的信息id
88	getMsgByStatus: (id) => {
89	return new Promise((resolve, reject) => {
90	let sql = 'select id,from_user_id,to_user_id from message where read_status = 0 and id like "%' + id + '%"' // (id like "%'+ '_'+id +'%" or id like "%'+ id+'_' +'%")
91	// console.log('sql', sql)
92	db.query(sql, (err, rows) => {
93	if(err) {
94	reject({
95	status: 0,
96	data: [],
97	message: err
98	})
99	}
100	let newData = []
101	let data = []
102	if(rows && rows.length>0) {
103	for(let i = 0; i < rows.length; i++){
104	rows[i]['unreadMessage'] = 1
105	newData.push(rows[i])
106	}
107	}
108	let addUnreadMsgHaveSameId = (array) => {
109	const hash = {};
110	array.map(item => {
111	const id = item['to_user_id'];
112	if (hash[id]) {
113	hash[id]['unreadMessage'] += item['unreadMessage'];
114	} else {
115	hash[id] = item;
116	}
117	});
118	return Object.values(hash);
119	}
120	console.log('rows', rows)
121	data = addUnreadMsgHaveSameId(newData)
122	resolve({
123	status: 2,
124	data: data,
125	msg: 'get all unread message'
126	})
127	})
128	})
129	},
130	addMsg: (_chat) => {
131	return new Promise((resolve, reject) => {
132	console.log(_chat)
133	if(_chat.id && _chat.from_user_id && _chat.to_user_id && _chat.content){
134	let sql = 'insert into message(id, from_user_id, to_user_id, content) values(?,?,?,?)'
135	let chatParams = [_chat.id, _chat.from_user_id, _chat.to_user_id, _chat.content]
136	db.query(sql, chatParams, (err, rows) => {
137	if(err) {
138	reject({
139	status: 0,
140	data: [],
141	message: err
142	})
143	}
144	resolve({status: 2, data: rows, message: 'add message success'})
145	})
146	}else {
147	resolve({
148	status: 0,
149	data: [],
150	message: 'Incomplete message information'
151	})
152	}
153	})
154	},
155	updateMsgReadStatus: (_chat) => {
156	return new Promise((resolve, reject) => {
157	console.log('params',_chat)
158	let s_sql = 'select * from message where id like "%'+ _chat.id +'%" and from_user_id=' + _chat.to_user_id
159	db.query(s_sql, (s_err, s_rows) => {
160	if(s_err){
161	reject({
162	status: 0,
163	data: [],
164	message: s_err
165	})
166	}
167	for(let i=0;i<s_rows.length;i++) {
168	let sql = 'update message set read_status = 1 where id=? and from_user_id=' + _chat.to_user_id
169	db.query(sql,[s_rows[i]['id']], (err, rows) => {
170	if(err) {
171	reject({
172	status: 0,
173	data: [],
174	message: err
175	})
176	}
177	resolve({status: 2, data: rows, message: 'update message success'})
178	})
179	}
180	})
181	})
182	},
183	updateMsg: (_chat) => {
184	// 更新仅能更新阅读状态以及消息内容
185	return new Promise((resolve, reject) => {
186	let _chatParams = [
187	_chat.read_status ? _chat.read_status : 0,
188	_chat.content ? _chat.content : null
189	]
190	_chatParams = _chatParams.filter(item => item)
191	let hasReadStatus = _chat.read_status \|\| _chat.read_status == 0 ? 'read_status=?' : ''
192	let hasContent = _chat.content ? 'content=?' : ''
193	let haveTwoParams = hasReadStatus && hasContent ? ',' : ''
194	let sql = 'update message set ' + hasReadStatus + haveTwoParams + hasContent + ' where id like "'+ _chat.id +'%"'
195	if(_chat.id) {
196	db.query(sql, _chatParams, (err, rows) => {
197	if(err) {
198	reject({
199	status: 0,
200	data: [],
201	message: err
202	})
203	}
204	resolve({status: 2, data: rows, message: 'update message success'})
205	})
206	}else {
207	resolve({
208	status: 0,
209	data: [],
210	message: 'id is null, please check'
211	})
212	}
213	})
214	},
215	deleteMsg: (_chat) => {
216	// 批量删除聊天记录
217	return new Promise((resolve, reject) => {
218	let _ids = _chat.ids
219	_ids = '"' + JSON.parse(_ids).join('","') + '"'
220	let sql = 'delete from message where id in ('+ _ids +')'
221	db.query(sql, (err, rows) => {
222	if(err) {
223	reject({
224	status: 0,
225	data: [],
226	message: err
227	})
228	}
229	resolve({status:2, data: rows, message: 'delete messages success'})
230	})
231	})
232	}
233	}
234
235	module.exports = chat

1	const express = require('express');
2	const router = express.Router();
3	const chat = require('../models/msgModel');
4
5	router.get('/:id/:currentPage', (req, res) => {
6	let body = req.params
7	// console.log(req.body, req.params, req.query)
8	chat.getMsgById(body).then(result => {
9	res.json(result)
10	}, err => {
11	res.json(500, 'Select dababase is error')
12	})
13	})
14
15	// add message
16	router.post('/', (req, res) => {
17	let body = req.body
18	chat.addMsg(body).then(result => {
19	res.json(result)
20	}, err => {
21	res.json(500, 'add message is error')
22	})
23	})
24
25	// update msg
26	router.put('/', (req, res) => {
27	let body = req.body
28	console.log(req.body, req.params, req.query)
29	chat.updateMsgReadStatus(body).then(result => {
30	res.json(result)
31	}, err => {
32	res.json(err)
33	})
34	})
35
36	// delete msg 批量删除
37	router.delete('/', (req, res) => {
38	// 传入一个字符串，但是格式必须为'[1,2,3,4]'，以便后端解析
39	console.log('req', req.body, req.params, req.query)
40	let body = req.body
41	chat.deleteMsg(body).then(result => {
42	res.json(result)
43	}, err => {
44	res.json(err)
45	})
46	})
47
48	module.exports = router