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);
    }
}

大致使用方式

处理后sql结果