mysql，sql server等数据库连接集成库

大家好，今天本人给大家带来文章《mysql，sql server等数据库连接集成库》，文中内容主要涉及到MySQL、Node.js、MSSQL，如果你对数据库方面的知识点感兴趣，那就请各位朋友继续看下去吧~希望能真正帮到你们，谢谢！

地址: https://github.com/fmfe/lib-sql

Installation

$ npm install @fmfe/lib-sql

or

$ yarn add @fmfe/lib-sql

Usage

有两种传入配置信息的方式:

使用config来管理我们的配置文件.

假设我们的项目目录下有一个config目录,config目录里有一个dev.json文件.

config/dev.json

{
    "mysql": {
        "host": "127.0.0.1",
        "port": 3306,
        "database": "test",
        "user": "root",
        "password": "123456"
    },
    "mssql": {
        "user": "sa",
        "password": "123456",
        "server": "127.0.0.1",
        "database": "test",
        "port": 1433,
        "pool": {
            "min": 0,
            "max": 10,
            "idleTimeoutMillis": 3000
        }
    }
}

mysql.js

const { mysql } = require('@fmfe/lib-sql');

const mysqlPool = mysql.init();

const _getNewSqlParamEntity = mysql._getNewSqlParamEntity;

// 执行单条sql语句  
// mysql.exec(mysqlPool, sql, params);
async function exec() {
    const sql1 = 'select * from ?? limit 2';
    const data = await mysql.exec(mysqlPool, sql1, ['tbl_user']);
}

// 执行mysql事务,可以传入多条增/删/改sql语句 
// mysql.exectrans(mysqlpool, sqlParamsEntity);
async function execTrans() {
    const sqlParamsEntity = [];
    const sql1 = 'insert into ?? (name, age, sex) values (?, ?, ?)';
    const param1 = ['tbl_user', 'aaa', 20, 1];
    sqlParamsEntity.push(_getNewSqlParamEntity(sql1, param1));

    const sql2 = 'insert into ?? (name, age, sex) values (?, ?, ?)';
    const param2 = ['tbl_user', 'bbb', 22, 0];
    sqlParamsEntity.push(_getNewSqlParamEntity(sql2, param2));

    const sql3 = 'update ?? set age = ? where id = ?';
    const param3 = ['tbl_user', 10, 1];
    sqlParamsEntity.push(_getNewSqlParamEntity(sql3, param3));
    // ....
    const data = await mysql.execTrans(mysqlPool, sqlParamsEntity);  
}

mssql.js

const { mssql } = require('@fmfe/lib-sql');
const _getNewSqlParamEntity = mssql._getNewSqlParamEntity;

// 执行单条语句  
// mssql.exec(sql)
async function exec() {
    const sql1 = 'select Top 3 name, age, sex from tbl_user order by age desc';
    const data = await mssql.exec(sql1);  
}

// 执行sql server 事务, 最好执行增/删/改语句,这里只是用select演示使用方法
// mssql.exectrans(sqlParamsEntity); 
async function exectrans() {
  
    const sqlParamsEntity = [];
    const sql1 = 'select * from tbl_user where id = 1';
    sqlParamsEntity.push(_getNewSqlParamEntity(sql1));

    const sql2 = 'select * from tbl_user where id = 2';
    sqlParamsEntity.push(_getNewSqlParamEntity(sql2));
    // ...
    const data = await mssql.execTrans(sqlParamsEntity);  
}

由于使用

config

 NODE_ENV=dev node ...

@fmfe/lib-sql

通过传入配置文件来调用库

我们引用上边的代码示例,只需做一点改动:

mysql.js

只需在初始化时传入mysql数据库配置就好.

const { mysql } = require('@fmfe/lib-sql');

const mysqlPool = mysql.init({
    host: '127.0.0.1',
    port: 3306,
    database: 'test',
    user: 'root',
    password: '123456'
});

......

mssql.js

在每次调用方法时传入配置

const { mssql } = require('@fmfe/lib-sql');
const config = {
    user: 'sa',
    password: '123456',
    server: '127.0.0.1',
    database: 'test',
    port: 1433,
    pool: {
        min: 0,
        max: 10,
        idleTimeoutMillis: 3000
    }  
}

async function exec() {
    ......
    const data = await mssql.exec(sql1, config);  
}

async function exectrans() {
  
    ......
    const data = await mssql.execTrans(sqlParamsEntity, config);  
}

到这里，我们也就讲完了《mysql，sql server等数据库连接集成库》的内容了。个人认为，基础知识的学习和巩固，是为了更好的将其运用到项目中，欢迎关注golang学习网公众号，带你了解更多关于mysql的知识点！