Express服务端使用mysql的一种方式
在使用Express做服务端的时候更喜欢使用mysql数据库而非更mongodb，原因是mysql可以配套使用powerdesign这种强力的设计工具进行概念模型（CDM）设计，更改设计更方便，以及自己吸收的一些springjdbc的数据库调用方法，在这边展示下供大家参考。 简单总结一下就是使用模板注入的方式传入参数，使用mysql的函数实现一些复杂的查询。
const pool = mysql.createPool(Object.assign(
config.mysql, {
typeCast: function(field, next) {
if (field.type === 'FLOAT') {
return (field.string());
}
return next();
},
queryFormat: function(query, values) {
if (!values) return query;
return query.replace(/\:(\w+)/g, function(txt, key) {
if (values.hasOwnProperty(key)) {
if (key == 'special' || key === 'special') {
return escape(values[key]);
} else {
return this.escape(values[key]);
}
}
return txt;
}.bind(this));
},
}));
这边的queryFormat 函数就是对查询语句的格式化，对形如:xxx的转化为具体的参数值查询。 然后对query进行了promise封装
exports.query = function(sql, params, callback) {
return new Promise(function (resolve, reject) {
pool.getConnection(function (err, con) {
if (err) {
if (con) {
con.release();
}
return reject(err);
}
con.query(sql, params, function (err,rows) {
if (err) {
if (con) {
con.release();
}
return reject(err);
}
resolve(rows);
con.release();
});
});
});
}
当然你也可以实现自己更复杂的查询模式，比如多语句查询，加入事务操作等。 数据库调用工具封装好以后就是在model层使用它。 我们可以先定义一个基类里面放各种查询方法
const mysql = require('../db/mysqlDriver');
const SQL_TOTAL = 'select FOUND_ROWS() total FROM DUAL ';
const message = require('../message');
class Base {
static getMessage() {
return message;
}
deleteOne(sql, params) {
return mysql.query(sql, params).then(_data => {
let rep = {};
rep.status = true;
return Promise.resolve(rep);
}).catch(err => {
return Promise.reject(err);
});
}
findOnlyOne(sql, params) {
return mysql.query(sql, params).then(_data => {
let rep = {};
rep.status = true;
rep.data = _data[0];
return Promise.resolve(rep);
}).catch(err => {
return Promise.reject(err);
});
}
findAll(sql, params) {
return mysql.query(sql, params).then(_data => {
let rep = {};
rep.status = true;
rep.data = _data;
return Promise.resolve(rep);
}).catch(err => {
return Promise.reject(err);
});
}
findWidthTotal(sql, params) {
return mysql.query(`${sql};${SQL_TOTAL}`, params).then(_data => {
let rep = {};
rep.status = true;
rep.data = _data[0];
rep.total = _data[1][0].total;
return Promise.resolve(rep);
}).catch(err => {
return Promise.reject(err);
});
}
insertOne(sql, params) {
return mysql.query(sql, params).then(_data => {
let rep = {};
rep.status = true;
rep.data = _data;
return Promise.resolve(rep);
}).catch(err => {
return Promise.reject(err);
});
}
}
大家可以看到我在顶部SQL_TOTAL这个常量，查询全部，这个是在mysql中查询全部数量的一个技巧。熟悉mysql的同学一定不会陌生，只要在写查询语句的时候加上 select SQL_CALC_FOUND_ROWS 以及SQL_TOTAL这个语句，那么我们得到的结果集里面就会包含本次查询的全部数量，这个技巧对分页功能来说还是很有效的。 接下来我们再定义具体的业务类，比如这里有一个交流的类。
var Base = require('./base');
var communion_sql = require('./communion_sql.json');
class Communions extends Base{
findFirstCommunions(params) {
return super.findWidthTotal(communion_sql.findfirstcommunions,params);
}
findSecondCommunions(params) {
return super.findWidthTotal(communion_sql.findsecondcommunions,params);
}
findCommunionDetail(params) {
return super.findOnlyOne(communion_sql.findcommunionbyid,params);
}
checkCommunionCollect(params) {
return super.findOnlyOne(communion_sql.checkcommunioncollect,params);
}
}
他继承了基类的方法，并引入了communion_sql.json 这种外部sql脚本,类似于mybatis那种配置，然后通过从脚本中引入具体的执行语句来查询具体业务。 communion_sql.json是这样的：
{
"findfirstcommunions": "select SQL_CALC_FOUND_ROWS a.*,count(distinct b.communionid) as collectnum, count(distinct c.communionid) as componentnum ,d.username from communion a left join communioncollect b on a.communionid=b.communionid left join communion c on c.com_communionid = a.communionid left join user d on a.userid=d.userid GROUP BY a.communionid HAVING a.com_communionid is null order by a.communionid desc limit :start ,:count",
"findcommunionbyid": "select t1.*,count(t2.communionid) usercollect from communion t1 left join communioncollect t2 on t1.communionid=t2.communionid and t2.userid=:userid where t1.communionid=:communionid ",
"findsecondcommunions": "select SQL_CALC_FOUND_ROWS a.*,count(b.communionid) as praisenum ,(select userid from communionpraise where userid=:userid and communionid=a.communionid) praiseuserid from communion a left join communionpraise b on a.communionid=b.communionid GROUP BY a.communionid HAVING a.com_communionid =:communionid order by a.communionid desc limit :start,:count",
"addcommunionimg": "insert into commimg (imgsrc) values(:imgsrc) ",
"addcommunion": "insert into communion (userid,createtime,content,title,images,communiontypeid) values(:userid,:createtime,:content,:title,:images,:communiontypeid)",
"findcommuniontypes": "select * from communiontypes",
"addcommunionpraise": "insert into communionpraise (communionid,userid,createtime) values(:communionid,:userid,:createtime)",
"cancelcommunionpraise": "delete from communionpraise where communionid=:communionid and userid=:userid",
"addcommunioncollect": "insert into communioncollect (communionid,userid,createtime) values(:communionid,:userid,:createtime)",
"cancelcommunioncollect": "delete from communioncollect where communionid=:communionid and userid=:userid",
"addcommunionchild": "insert into communion (userid,createtime,content,images,communiontypeid,com_communionid) values(:userid,:createtime,:content,:images,:communiontypeid,:com_communionid)",
"checkcommunioncollect": "select userid from communioncollect where userid=:userid and communionid=:communionid"
}
以键值对的形式存放具体的查询数据。在这里你可以自由写mysql脚本，实现复杂的逻辑。 当然这边选择实现逻辑的时候还需要具体考量，有的逻辑最好还是在js代码里面实现，我觉得这边的好处是可以缓解你在业务逻辑的代码量，毕竟js的回调是个让人头疼的问题。另外考量性能的时候我也跟自己搞java得同学交流过，通过使用缓存避免过多数据库操作也是一种很好的解决方向。总之仁者见仁智者见智。 然后你在controll里面调用model就会简洁许多。
const Communion = new(require('../models/communion'))();
const moment = require('moment');
const cache = require('../util/cache');
const pageCount = require('../config').pageCount;
const config = require('../config');
const upqiniu = require('../util/upqiniu');
/**
* 交流控制器
* @param {[type]} req [description]
* @param {[type]} res [description]
* @param {Function} next [description]
* @return {[type]} [description]
*/
exports.getFirstCommunions = function(req, res, next) {
const params = req.body;
let nowPage = params.page;
if (nowPage) {
nowPage = nowPage < 1 ? 1 : nowPage;
params.start = (nowPage - 1) * pageCount;
}
params.count = pageCount;
Communion.findFirstCommunions(params)
.then(data => res.json(data))
.catch(next);
}
/**
* 子集交流
* @param {[type]} req [description]
* @param {[type]} res [description]
* @param {Function} next [description]
* @return {[type]} [description]
*/
exports.getSecondCommunions = function(req, res, next) {
const params = req.body;
let nowPage = params.page;
if (nowPage) {
nowPage = nowPage < 1 ? 1 : nowPage;
params.start = (nowPage - 1) * pageCount;
}
params.count = pageCount;
Communion.findSecondCommunions(params)
.then(data => res.json(data))
.catch(next);
}
/**
* 获得交流详情
* @param {[type]} req [description]
* @param {[type]} res [description]
* @param {Function} next [description]
* @return {[type]} [description]
*/
exports.getCommunionDetail = function(req, res, next) {
const communionid = req.params.communionid;
Communion.findCommunionDetail({communionid:communionid})
.then(data => res.json(data))
.catch(next);
}
/**
* 交流图片上传
* @param {[type]} req [description]
* @param {[type]} res [description]
* @param {Function} next [description]
* @return {[type]} [description]
*/
exports.upCommunionImg = function(req, res, next) {
let key = Date.now() + '-' + req.files[0].originalname;
const localFile = req.files[0].path;
upqiniu.uploadFile(key, localFile).then(data=>{
let simg = {
imgsrc: config.qiniuurl + data.key,
}
return Communion.addCommunionImg(simg);
}).then(result=>{
res.json(result);
}).catch(next);
}
/**
* 增加交流
* @param {[type]} req [description]
* @param {[type]} res [description]
* @param {Function} next [description]
*/
exports.addCommunion = function(req, res, next) {
const params = req.body;
params.createtime = moment(new Date()).format('YYYY-MM-DD HH:mm:ss');
Communion.addCommunion(params)
.then(result=>{
res.json(result);
}).catch(next);
}
/**
* 交流类型查询
* @param {[type]} req [description]
* @param {[type]} res [description]
* @param {Function} next [description]
* @return {[type]} [description]
*/
exports.findCommunionTypes = function(req, res, next) {
Communion.findCommunionTypes({})
.then(result=>{
res.json(result);
}).catch(next);
}
exports.addCommunionPraise = function(req, res, next) {
const params = req.body;
params.createtime = moment(new Date()).format('YYYY-MM-DD HH:mm:ss');
Communion.addCommunionPraise(params)
.then(result=>{
res.json(result);
}).catch(next);
}
exports.cancelCommunionPraise = function(req, res, next) {
const params = req.body;
Communion.cancelCommunionPraise(params)
.then(result=>{
res.json(result);
}).catch(next);
}
看起来也舒服是吧。我偷了下懒，直接把代码粘了过来。 项目已经开源了包括服务端还有客户端，客户端是用RN做的，虽然并没有完成，但在写的过程用也用了一点心，对优化也做了一些小小的研究。android打包后用小米机测试都挺流畅。 服务端方面也想做的更精简一目了然。对路由做了可以动态化的实现，这边不细说。 iiiT服务端地址：https://github.com/wq123456/iiiTserver iiiT客户端地址: https://github.com/wq123456/iiiTclient
