mysql - 如何在node.js中实现一个promise代码片段代替Mysql客户端

Question

我寻求解决方案的最初目的是因为我想使用 Observables，因为我想获得有关 Angular UI 的实时数据。但这里的问题是我得到 sql 错误 -> 太多 sql 连接。因此，每次查询命中数据库后，我都需要关闭连接。这在我的代码中没有发生。所以我搜索了可用的代码并找到了这个 -

https://codeburst.io/node-js-mysql-and-promises-4c3be599909b

节点代码 -

 const mysql = require( 'mysql' );
  class Database {
   constructor( config ) {
    this.connection = mysql.createConnection( config );
    }
   query( sql, args ) {
    return new Promise( ( resolve, reject ) => {
        this.connection.query( sql, args, ( err, rows ) => {
            if ( err )
                return reject( err );
            resolve( rows );
        } );
     } );
    }
   close() {
    return new Promise( ( resolve, reject ) => {
        this.connection.end( err => {
            if ( err )
                return reject( err );
            resolve();
        } );
    } );
    }
    }

现在我想在我的nodejs文件中实现上面的代码，它的代码是 -

[来自我的 Angular 的 http 请求首先访问 controller.js 文件，然后从那里访问 service.js 文件]

更新

仪表板.controller.js

  var express = require('express');
  var router = express.Router();
  var dashboardService = require('../../services/SYSTEM
   /dashboard.service');
   var app = express();


    router.post('/abcEntireSuccess',abcEntireSuccess);

    module.exports = router;




  function abcEntireSuccess(req, res) {


    dashboardService.abcEntireSuccess(req.body)
          .then(function (result) {
          if (result.length > 0) {
            console.log("we get data in node :: " + 
        Object.values(result));
            console.log(result.length + " record found  ");
            res.send(result);
           } 
         else {
            result=[];
            res.send(result);
         }
       })
       .catch(function (err) {
        res.status(400).send(err);
        console.log("some error occured");
       });
       }

仪表板.service.js

    var mysql = require('mysql');
    var Q = require('q');
    var express = require('express');
    var service = {};
    var config = require('../../config.json');
    const async = require('async');


       service.abcEntireSuccess = abcEntireSuccess;

        module.exports = service;


 function abcEntireSuccess() {

  var sqlQuery = `select * from TRANSACTION_PAYLOAD where INTERFACE_NAME =  
   'abc' AND (STATUS ='SUCCESS_RESPONSE')`



    var deferred = Q.defer();



    var host = config.host;
    var user = config.user;
    var password = config.password;
    var database = config.database;


     var con = mysql.createConnection({
     host: host,
     user: user,
     password: password,
     database: database
       });


      con.connect(function (err) {
       if (err) deferred.reject(err.name + ': ' + err.message);

       con.query(sqlQuery,
           function (err, result, fields) {
            if (err) deferred.reject(err.name + ': ' + err.message);

            console.log("my result => ",result);

            deferred.resolve(result);

        });


       })

     console.log(" deferred.promise; ");
    return deferred.promise;



  }

请帮助实施。

@skipperhoa

更新

当我实现您的代码时...我在 angular UI 即 html 中收到此错误，

POST http://localhost:8096/dashboard/abcEntireSuccess/ 404（未找到）

请帮忙解决

标签： mysqlnode.jspromiseasync-await

Answer 1

您可以在代码之后尝试以下操作

//dashboard.controller.js
var express = require('express')
var router = express.Router()
var dashboardService = require('./dashboard.service.js');

router.get('/test2',function(req,res){
  console.log(dashboardService.abcEntireSuccess());
});

module.exports = router


//dashboard.service.js
var mysql = require('mysql');
var express = require('express');
var config = require('../config/config.json');
var exports = module.exports = {};

exports.abcEntireSuccess = function() {
    const db = new Database(config);
    db.query( 'SELECT * FROM users' ).then( rows => {
        return rows;
    } );
db.close();
};

class Database {
    constructor( config ) {
        this.connection = mysql.createConnection( config.values);
    }
    query( sql, args ) {
        return new Promise( ( resolve, reject ) => {
            this.connection.query( sql, args, ( err, rows ) => {
                if ( err )
                    return reject( err );
                console.log(rows);
                resolve( rows );
            } );
        } );
    }
    close() {
        return new Promise( ( resolve, reject ) => {
            this.connection.end( err => {
                if ( err )
                    return reject( err );
                resolve();
            } );
        } );
    }
 }  

//congfig/config.json
{
    "values" : {
        "host" : "localhost",
        "user" : "root",
        "password" : "",
        "database" : "demo"
    }
}