MYSQL SQL拼接工具

最近用到node+express+mysql开发后端业务，但是sql的拼写相当麻烦，特别是查询条件的拼接，故书写了如下工具，方便sql生成。

当查询条件值为null时，一般不需要该条件。即sql需要动态生成。

工具文件

sqlUtil.ts

/*查询字段 */
interface fieldProps { //distinct name as aname
    expression: any;    //字段表达式，对应数据库字段,例如distinct name
    valueField?: string;  //数据对应的字段
    alias?: string;      //别名
}

/*where条件 */
interface whereProps {   //where name=? and 
    expression: string; //条件表达式,例如name=?
    valueField?: string;    //数据对应字段
    relation?: string;   //关系，and、or
}

/*排序 */
interface orderByProps {
    field: string; //排序字段
    direction?: string;   //排序方向，desc/asc
}

/* limit */
interface limitProps {
    begin: number;   //起始位置
    end: number;     //结束位置
}


interface sqlOptionsProps {
    type: string;
    fields?: null | fieldProps[];
    tableName: string;
    where?: null | whereProps[];
    orderBy?: null | orderByProps[];
    limit?: null | limitProps;
}


/**
 * 
 * @param type //sql类型，select、insert、update、delete
 * @param fields //查询字段，默认为空，查询所有字段
 * @param tableName //表名
 * @param where //where条件，默认为空，即无条件
 * @param orderBy //排序条件，默认为空，即不排序
 * @param limit   //分页参数，存在时默认查询前10条数据
 */
const getSqlStr = (
    type: string,
    fields: null | fieldProps[] = [],
    tableName: string,
    where: null | whereProps[] = [],
    orderBy: null | orderByProps[] = [],
    limit: null | limitProps = { begin: 0, end: 9 }
) => {
    let sql = "";
    //表名
    if (!tableName) {
        console.log("表名不能为空！")
    }
    switch (type) {
        case "select":
        case "SELECT":
            //sql类型
            sql += "SELECT ";
            //查询字段
            if (fields && fields.length > 0) {
                for (let field of fields) {
                    if (field.expression) {
                        sql += field.expression;
                    }
                    if (field.alias) {
                        sql += " AS " + field.alias
                    }

                    sql += ",";
                }
                //去掉末尾的,
                sql = sql.substring(0, sql.length - 1);
            } else {
                sql += "*";
            }

            sql += " FROM " + tableName;

            //where条件
            if (where && where.length > 0) {
                let whereStr = joinWhere(where);
                sql += whereStr
            }

            //order by
            if (orderBy && orderBy.length) {
                sql += " ORDER BY ";
                for (let i = 0; i < orderBy.length; i++) {
                    let order = orderBy[i];
                    let direction = order.direction ? order.direction : "ASC";
                    sql += `${order.field} ${direction}`;
                    //添加逗号
                    if (i != orderBy.length - 1) {
                        sql += ","
                    }
                }
            }

            //limit
            if (limit) {
                sql += ` limit ${limit.begin},${limit.end}`;
            }

            break;

        case "insert":
        case "INSERT":
            sql += `INSERT INTO ${tableName}(`;
            if (fields && fields.length > 0) {
                //设置新增字段
                for (let i = 0; i < fields.length; i++) {
                    let field = fields[i];
                    sql += `${field.expression}`
                    if (i != fields.length - 1) {
                        sql += ","
                    } else {
                        sql += ")"
                    }
                }

                //设置新增值，新增和编辑的值用?代替，使用mysql的传参方式传入，
                //因为参数入库涉及到参数类型校验，例如日期格式，前端检验相当麻烦，所以直接交由mysql的Api处理
                sql += " VALUE(";
                for (let i = 0; i < fields.length; i++) {
                    let field = fields[i];
                    sql += "?"
                    if (i != fields.length - 1) {
                        sql += ","
                    } else {
                        sql += ")"
                    }
                }
            } else {
                console.log("新增表字段不能为空!")
            }

            break;

        case "update":
        case "UPDATE":
            sql += `UPDATE ${tableName} SET `;
            if (fields && fields.length > 0) {
                for (let i = 0; i < fields.length; i++) {
                    let field = fields[i];
                    sql += `${field.expression}=?`
                    if (i != fields.length - 1) {
                        sql += ","
                    }
                }
            } else {
                console.log("修改表字段不能为空!")
            }

            //where条件
            if (where && where.length > 0) {
                let whereStr = joinWhere(where);
                sql += whereStr
            }
            break;
        case "delete":
        case "DELETE":
            sql += `DELETE FROM ${tableName}`;
            if (where && where.length > 0) {
                let whereStr = joinWhere(where);
                sql += whereStr
            }
            break;
    }
    return sql;
}

/**
 * 生成可执行sql和sql所需要的参数
 * @param sqlOptions 
 * @param resquestParams 
 */
const sqlJoin = (sqlOptions: sqlOptionsProps, resquestParams: any) => {
    let type = sqlOptions.type || "select";
    let fields: any = sqlOptions.fields;
    let tableName = sqlOptions.tableName;
    let where: any = sqlOptions.where;
    let orderBy = sqlOptions.orderBy;
    let limit = sqlOptions.limit;
    let whereValue = getWhereValue(where, resquestParams);
    let filedsParams = getFiledsParams(type, fields, resquestParams);
    let sqlStr = getSqlStr(type, fields, tableName, whereValue.whereArr, orderBy, limit);
    let paramsArr = filedsParams.concat(whereValue.paramsArr);
    return {
        sqlStr,
        paramsArr
    }
}


/**
 * 拼接wehre条件,最后一个条件的关系将会被舍弃，默认如果未配置关系则视为AND
 * @param where 
 */
const joinWhere = (where: whereProps[]) => {
    let whereStr = "";
    //where条件
    whereStr += " WHERE ";
    for (let i = 0; i < where.length; i++) {
        let wh = where[i]
        whereStr += wh.expression;
        if (i != where.length - 1) {
            if (wh.relation && wh.relation != "") {
                whereStr += " " + wh.relation + " ";
            } else {
                whereStr += " AND ";
            }

        }
    }
    return whereStr;
}

/**
 * 获取where条件和其对应的参数，返回一个对象，whereArr为条件，paramsArr为条件值，一一对应
 * @param whereList //手动配置的可选条件
 * @param searchObj //条件值对象
 */
const getWhereValue = (whereList: whereProps[], searchObj: any) => {
    let whereArr = [];  //条件数组
    let paramsArr = []; //条件值数组，用于替换问号数据
    if (searchObj && whereList && whereList.length > 0) {
        for (let where of whereList) {
            if (where.valueField) {
                let value = searchObj[where.valueField];
                let isEmpty = isEmptyFilter(value, true);
                if (!isEmpty) {
                    whereArr.push(where);
                    paramsArr.push(value);
                }
            }
        }
    }
    return {
        whereArr,
        paramsArr
    };
}


const getFiledsParams = (type: string, fileds: fieldProps[], resquestParams: any) => {
    let filedsParams: any[] = [];
    let typeLower = type.toLowerCase();
    //查询和删除不需要字段参数，只有where参数
    if (typeLower === 'select' || typeLower === 'delete') {
        return filedsParams;
    }
    //字段拼接参数
    if (fileds && fileds.length > 0) {
        for (let filed of fileds) {
            if (filed.valueField) {
                //数据库不接收undefined
                let param = resquestParams[filed.valueField] || null;
                filedsParams.push(param);
            }
        }
    }

    return filedsParams;
}

/**
 * 校验数据是否为空
 * @param value             //数据
 * @param vaildEmptyStr     //是否对空字符串进行校验
 */
const isEmptyFilter = (value: any, vaildEmptyStr: boolean = false) => {
    let valueType = Object.prototype.toString.call(value);
    switch (valueType) {
        case "[object Null]":
        case "[object Undefined]":
            return true;
        case "[object String]":
            if (vaildEmptyStr && value === "") {
                return true;
            }
            return false;
        default:
            return false;
    }
}

export default {
    sqlJoin
}; 

本拼接工具只适用于单表操作，多表操作请自行在单表操作的基础上封装。

用法

SELECT

let options = {
        type: "select",
        fields: null,
        tableName: "tableA",
        where: [
            { expression: "name=?", valueField: "name", relation: "AND" },
            { expression: "age=?", valueField: "age", relation: "AND" },
        ],
        orderBy: null,
        limit: { begin: 0, end: 9 }
    }
    let params = {
        name:"张珊",
        age:""
    }
    let sql = sqlUtil.sqlJoin(options,params)
    console.log(sql)

{“sqlStr”:“SELECT * FROM tableA WHERE name=? limit 0,9”,“paramsArr”:[“张珊”]}

INSERT

let options = {
        type: "insert",
        fields: [
            { expression: "name", valueField: "name" },
            { expression: "age", valueField: "age" },
        ],
        tableName: "tableA"
    }
    let params = {
        name: "张珊",
        age: "27"
    }
    let sql = sqlUtil.sqlJoin(options, params)
    console.log(sql)

{“sqlStr”:“INSERT INTO tableA(name,age) VALUE(?,?)”,“paramsArr”:[“张珊”,“27”]}

修改

let options = {
        type: "update",
        fields: [
            { expression: "name", valueField: "name" },
            { expression: "age", valueField: "age" },
        ],
        tableName: "tableA",
        where: [
            { expression: "id=?", valueField: "id" }
        ]
    }
    let params = {
        id: 1,
        name: "张珊",
        age: "27"
    }
    let sql = sqlUtil.sqlJoin(options, params)
    console.log(sql)

{“sqlStr”:“UPDATE tableA SET name=?,age=? WHERE id=?”,“paramsArr”:[“张珊”,“27”,1]}

删除

let options = {
        type: "delete",
        fields:null,
        tableName: "tableA",
        where: [
            { expression: "id=?", valueField: "id" }
        ]
    }
    let params = {
        id: 1,
        name: "张珊",
        age: "27"
    }
    let sql = sqlUtil.sqlJoin(options, params)
    console.log(sql)

{“sqlStr”:“DELETE FROM tableA WHERE id=?”,“paramsArr”:[1]}

Express支持Typescript

1.创建Express根目录

新建server目录作为服务端根目录。

2.安装express依赖

npm i -g express 

可通过npx express-generator脚手架生成express项目结构

3.安装ts依赖

@types/express是express对ts的支持依赖
@types/node是node对ts的支持依赖

npm install @types/node --save
npm install @types/express --save-dev

4.ts编译配置

默认情况下，node是遵循CommonJS规范的，且不不能识别ts文件，所以需要将ts文件转化为可识别的es文件。

在server目录下添加tsconfig.json文件，内容如下：

{
    "compilerOptions": {
        "target": "es5",                //编译的语言版本
        "module": "commonjs",           //生成代码的规范标准
        "allowJs":true,                 //编译时允许有js
        "emitDecoratorMetadata": true,  //为注解声明加上元数据类型
        "experimentalDecorators": true, //允许注解语法
        "sourceMap":true,               //生成js的同时，生成对应的map文件
        "removeComments":true,          //编译时去除注释
        "esModuleInterop":true,         //允许export=导出，由import from 导入
        "allowSyntheticDefaultImports":true,    //允许引入没有默认导出的模块
        "outDir": "public",            //编译后的文件输出目录
        "lib": [                       //声明ts需引入的库
            "es6"
        ]
    },
    "exclude": [                       //排除内容
        "node_modules"
    ]
}

npm i
npm start
启动后，可在http://localhost:3000/下查看页面是否可访问，是否保错

项目参考地址

https://github.com/windSandEye/personal-homepage.git