Express+mysql单表分页条件查询

声明（自己还没测试过，只提供大概逻辑，什么多表连接查询可以在原基础上添加）

class

/*** @param connection Express的mysql数据库链接对象* current 当前页* pageSize 一页显示行数* where [{key:id,operator:=,value=15}], key查询字段,operator条件运算符(不填写默认=,其它> ,< ,!=),value 查询条件值* field [id,name,age] 查询字段* orderBy {order:[age,id],by:desc}, 排序,order要排序的字段， by默认不传desc* */
class  pageTable{constructor(connection,{current=0,pageSize=20,tableName,field=[],where=[],orderBy={order:[],by:'DESC'}}) {this.connection=connection;//数据库链接对象this.tableName=tableName;//表名this.field=field;//字段名称this.where=where;//查询条件this.orderBy=orderBy;//排序this.pageData={total:0,current:current<0?0:current,pageSize:pageSize,data:[]};}getField=()=>{if(this.field instanceof Array&&this.field.length>0){return this.field?.toString();}return '*'}getOrderBy=()=>{if(this.orderBy?.order instanceof Array&&this.orderBy.order?.length>0){return `ORDER BY ${this.orderBy?.order?.toString()} ${this.orderBy?.by||'DESC'}`;}return ''}//分页查询处理selectTable=(callback)=>{const baseThis=this;let totalSql=`select count(id) AS total form ${this.tableName}`;let selectSql=`select ${baseThis.getField()} form ${baseThis.tableName}`;//处理条件参数const sqlParams=[];if(baseThis.where instanceof Array&&baseThis.where.length>0){let whereJoin=` where `baseThis.where.forEach((v,index,array)=>{whereJoin=whereJoin.concat(`${v?.key} ${v?.operator||'='} ? ${(array?.length>1&&index>=0&&index<array?.length-1)?' and ':''}`);sqlParams.push(v?.value)});totalSql=totalSql.concat(whereJoin);selectSql=selectSql.concat(whereJoin);}//查询出总数baseThis.connection?.query(totalSql,sqlParams,function(err,resultTotal){if(err) throw err;const size= resultTotal?.[0]?.total||0;baseThis.pageData.total= size;if(size>0){let current = baseThis.pageData.current;//当前页码let pageSize = baseThis.pageData.pageSize;//一页展示多少条selectSql = selectSql.concat(baseThis.getOrderBy());selectSql = selectSql.concat(" limit ?,?");sqlParams.push(current*pageSize,(current+1)*pageSize);//执行分页查询baseThis.connection?.query(selectSql,sqlParams,function(err,resultPageData){if(err) throw err;baseThis.pageData.data= resultPageData||[];callback(baseThis.pageData);})}callback(baseThis.pageData);})}/*** 开始执行查询数据* */getPageData=(callback)=>{if(!this.tableName){throw new Error('tableName is null or undefined!');}if(!this.connection){throw new Error('connection is null or undefined!');}if(!this.connection?.query){throw new Error('connection is not sql connection!');}if(!(callback instanceof Function)){throw new Error('callback is not an Function!');}this.selectTable(callback);}
}