一个typescript版本的dao(数据库访问)层的封装

直接看使用例子

example：

test01();
async function test01() {
    let dbmysql = require("./dbmysql");

    let dbConfig = {
        host: "rm-bp19kkjy1bne46g1cso.mysql.rds.aliyuncs.com",
        user: "chace",
        password: "Admin12345678",
        database: "school",
        port: 3306
    }

    global.db = new dbmysql();
    global.db.init(dbConfig);
    let sqlStr = "select ifnull(max(cast(tid as unsigned integer)),0) as tid from teacher;";
    let execResult = await global.db.renderExecute(sqlStr);
    console.log("execResult:", execResult);

    // transaction
    let transResult;
    let mysqlConnObj = await global.db.getConnection();
    try {
        await global.db.queryKeep(mysqlConnObj, "start transaction;");
        transResult = await global.db.queryKeep(mysqlConnObj, "insert into teacher(tname,sqrq) values(?,?);", ['宝宝', null]);
        transResult = await global.db.queryKeep(mysqlConnObj, "commit;");
    } catch (error) {
        await global.db.queryKeep(mysqlConnObj, "rollback;");
        console.log("error:", error);
    } finally {
        mysqlConnObj.release();
    }
    console.log("transResult:", transResult);
    process.exit(0);
};

执行结果

对mysql的dao层的封装dbmysql.ts

let mysql = require('mysql2');

module.exports = class dbmysql {
    private mysqlConnectPool: any;
    init(dbConfig: any): void {
        try {
            this.mysqlConnectPool = mysql.createPool(dbConfig);
            console.debug('MySql Adapter Pool generated successfully');
        } catch (error) {
            console.error('[mysql.connector][init][Error]: ', error);
            throw new Error('failed to initialized pool');
        }
    }
    //获取一个连接
    getConnection(): any {
        return new Promise((resolve, reject) => {
            this.mysqlConnectPool.getConnection(function (err: any, conn: any) {
                if (err) {
                    reject(err);
                } else {
                    resolve(conn);
                };
            });
        });
    }
    //拦截更改sql
    renderExecute(sql: any, params?: any): any {
        //在这可拦截sql进行更改
        return this.executeSQL(sql, params);
    }
    //执行sql并释放连接
    executeSQL(sql: any, params: any): any {
        return new Promise((resolve, reject) => {
            this.mysqlConnectPool.getConnection(function (err: any, conn: any) {
                if (err) {
                    reject(err);
                } else {
                    conn.release();
                    conn.execute(sql, params, function (err: any, rows: any) {
                        if (err) {
                            reject(err);
                        } else {
                            resolve(rows);
                        }
                    });
                }
            });
        });
    }
    //执行sql不释放连接 可用于事务
    queryKeep(conn: any, sql: any, params?: any): any {
        return new Promise((resolve, reject) => {
            conn.query(sql, params, function (err: any, rows: any) {
                if (err) {
                    reject(err);
                } else {
                    resolve(rows);
                }
            });
        });
    }
    //释放连接 可用于事务
    releaseConn(conn: any): any {
        return new Promise((resolve, reject) => {
            conn.release();
        });
    }
}