声明（自己还没测试过，只提供大概逻辑，什么多表连接查询可以在原基础上添加）
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);
}
}
大致使用方式
处理后sql结果
