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 && c_rows[0]['COUNT(*)']) {
pages = Math.ceil(c_rows[0]['COUNT(*)'] / pageSize)
}
if(pages) {
pageStart = (+pages - +currentPage) <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] && 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 && rows.length>0) {
for(let i = 0; i < 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 && _chat.from_user_id && _chat.to_user_id && _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<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 && 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 = chatrouter/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