denet-node-server/model/mysql_db.js

// mysql 学习资料
//https://www.runoob.com/nodejs/nodejs-mysql.html
//https://blog.csdn.net/hbiao68/article/details/89097853

var mysql = require('mysql');
var { db_config } = require('../config/config.js')
const logger = require('./logger')

var port = db_config.mysql.PORT
var host = db_config.mysql.HOST
var username = db_config.mysql.USERNAME
var password = db_config.mysql.PASSWORD
var database = db_config.mysql.DATABASE
var tablename = db_config.mysql.TABLENAME
var MYSQL_INSTANCE = mysql.createConnection({
    host: host,
    user: username,
    password: password,
    port: port,
    database: database,
});

logger.log('connected to mysql ps=', db_config.mysql)
MYSQL_INSTANCE.on('connect', () => {
    logger.log('connected to mysql')
})

MYSQL_INSTANCE.on('error', function (err) {
    logger.log('mysql  Error =>', err);
});
MYSQL_INSTANCE.connect();

function queryCompanyInfoFromId(id) {
    var sql_main = 'select * from '
    var sql_table_name = 'company_key_manage'
    var sql_where = ' WHERE '
    var sql_where_name = ' id= ? '
    var sql_where_value = id
    var new_sql = sql_main.concat(sql_table_name, sql_where, sql_where_name);
    logger.debug('queryCompanyPrivateKeyFromId new_sql=', new_sql);
    return new Promise((resolve) => {
        MYSQL_INSTANCE.query(
            new_sql, [sql_where_value],
            function selectCb(err, results) {
                logger.log('queryCompanyPrivateKeyFromId ret=', err, results);
                var ret;
                if (results && Array.isArray(results) && results.length > 0) {
                    ret = results[0]
                } else {
                    ret = null
                }
                // resolve 只能传递一个参数
                resolve({
                    err: err,
                    results: ret
                });
            }
        );
    })
}


/**
 * 查询用户私钥
 * 
 * @param {*} user_address 
 * @returns 
 */
function queryUserPrivateKeyFromUserAddress(user_address) {
    var sql_main = 'select * from '
    var sql_table_name = tablename
    var sql_where = ' WHERE '
    var sql_where_name = ' user_address= ? '
    var sql_where_value = user_address
    var new_sql = sql_main.concat(sql_table_name, sql_where, sql_where_name);
    logger.debug('queryUserPrivateKeyFromUserAddress new_sql=', new_sql);
    return new Promise((resolve) => {
        MYSQL_INSTANCE.query(
            new_sql, [sql_where_value],
            function selectCb(err, results) {
                logger.log('queryUserPrivateKeyFromUserAddress ret=', err, results);
                var ret;
                if (results && Array.isArray(results) && results.length > 0) {
                    ret = results[0].user_private_key
                } else {
                    ret = null
                }
                // resolve 只能传递一个参数
                resolve({
                    err: err,
                    results: ret
                });
            }
        );
    })
}


/**
 * 查询用户私钥
 * 
 * @param {*} user_address 
 * @returns 
 */
function queryAllUserPrivateKeyAndUserAddress() {
    var sql_main = 'select * from '
    var sql_table_name = db_config.mysql.TABLENAME
    var sql_where = ' order by id desc limit 3'
    var sql_where_name = '  '
    var new_sql = sql_main.concat(sql_table_name, sql_where, sql_where_name);
    logger.debug('queryAllUserPrivateKeyAndUserAddress new_sql=', new_sql);
    return new Promise((resolve) => {
        MYSQL_INSTANCE.query(
            new_sql, [],
            function selectCb(err, results) {
                if (results && Array.isArray(results) && results.length > 0) {
                    resolve(results);
                } else {
                }
            }
        );
    })
}

//查询语句
//SELECT * FROM user_key_manage WHERE user_address='0x049D33EEE7432DB14c50911fAE4a7C38624313aA'

module.exports = {
    queryUserPrivateKeyFromUserAddress,
    queryAllUserPrivateKeyAndUserAddress,
    queryCompanyInfoFromId,
};