nodejs操作MySQL，mysql连接池及事务的使用 (原创)

本篇，我们介绍下，nodejs中访问mysql数据库，采用mysql官方驱动

npm  install mysql  --save 

02_curd.js 

var mysql  = require('mysql');

var connection = mysql.createConnection({
    host     : 'localhost',
    user     : 'root',
    password : 'winmshl',
    port: '3316',
    database: 'db_test',
});

//connection.connect();

//查询
function query() {
    var  sql = 'SELECT * FROM t_student';

    connection.query(sql,function (err, result,fields) {
        if(err){
            throw err;
        }
        console.log(result);
        console.log(fields);
        // [ RowDataPacket {
        //     studentId: 1,
        //     name: '张三',
        //     sex: 1,
        //     birthday: '1990-08-07',
        //     description: '一个好学生',
        //     createtime: '2018-02-26 07:22:49' },
        //     RowDataPacket {
        //     studentId: 2,
        //     name: '李四',
        //     sex: 2,
        //     birthday: '1993-03-07',
        //     description: '一个好学生',
        //     createtime: '2018-02-26 07:23:12' }
        // ]


        //     [ FieldPacket {
        //     catalog: 'def',
        //         db: 'db_test',
        //         table: 't_student',
        //         orgTable: 't_student',
        //         name: 'studentId',
        //         orgName: 'studentId',
        //         charsetNr: 63,
        //         length: 11,
        //         type: 3,
        //         flags: 16899,
        //         decimals: 0,
        // default: undefined,
        //         zeroFill: false,
        //         protocol41: true },

        // FieldPacket {
        //     catalog: 'def',
        //         db: 'db_test',
        //         table: 't_student',
        //         orgTable: 't_student',
        //         name: 'name',
        //         orgName: 'name',
        //         charsetNr: 33,
        //         length: 96,
        //         type: 253,
        //         flags: 0,
        //         decimals: 0,
        // default: undefined,
        //         zeroFill: false,
        //         protocol41: true },
        // FieldPacket {
        //     catalog: 'def',
        //         db: 'db_test',
        //         table: 't_student',
        //         orgTable: 't_student',
        //         name: 'sex',
        //         orgName: 'sex',
        //         charsetNr: 63,
        //         length: 11,
        //         type: 3,
        //         flags: 0,
        //         decimals: 0,
        // default: undefined,
        //         zeroFill: false,
        //         protocol41: true },
        // FieldPacket {
        //     catalog: 'def',
        //         db: 'db_test',
        //         table: 't_student',
        //         orgTable: 't_student',
        //         name: 'birthday',
        //         orgName: 'birthday',
        //         charsetNr: 33,
        //         length: 72,
        //         type: 253,
        //         flags: 0,
        //         decimals: 0,
        // default: undefined,
        //         zeroFill: false,
        //         protocol41: true },
        // FieldPacket {
        //     catalog: 'def',
        //         db: 'db_test',
        //         table: 't_student',
        //         orgTable: 't_student',
        //         name: 'description',
        //         orgName: 'description',
        //         charsetNr: 33,
        //         length: 768,
        //         type: 253,
        //         flags: 0,
        //         decimals: 0,
        // default: undefined,
        //         zeroFill: false,
        //         protocol41: true },
        // FieldPacket {
        //     catalog: 'def',
        //         db: 'db_test',
        //         table: 't_student',
        //         orgTable: 't_student',
        //         name: 'createtime',
        //         orgName: 'createtime',
        //         charsetNr: 33,
        //         length: 72,
        //         type: 253,
        //         flags: 0,
        //         decimals: 0,
        // default: undefined,
        //         zeroFill: false,
        //         protocol41: true } ]
    });
}

// 增加
function insert() {
    var sql="INSERT  INTO  t_student(NAME,sex,birthday,description,createtime)\n" +
        "VALUES(?,?,?,?,NOW() )";

    connection.query(sql,['王五',1,'1993-08-06','一个好学生'],function (err,results) {
        if(err) throw err ;
        console.log(results);
        console.log("返回主键id："+results.insertId);
        // OkPacket {
        //     fieldCount: 0,
        //         affectedRows: 1,
        //         insertId: 3,
        //         serverStatus: 2,
        //         warningCount: 0,
        //         message: '',
        //         protocol41: true,
        //         changedRows: 0 }
        // 返回主键id：3

    });

}

//修改
function update() {
    console.log('更新操作');
    var sql ="UPDATE  t_student t SET  t.`description`=? \n" +
        "WHERE t.`studentId`=?";

    connection.query(sql,['xxxx2',3],function (err,results) {
       if(err) throw err;
        console.log(results);
    });

    // OkPacket {
    //     fieldCount: 0,
    //         affectedRows: 1,
    //         insertId: 0,
    //         serverStatus: 2,
    //         warningCount: 0,
    //         message: '(Rows matched: 1  Changed: 1  Warnings: 0',
    //         protocol41: true,
    //         changedRows: 1 }


}
//删除
function del() {
    console.log('删除操作');
    var sql="DELETE FROM t_student WHERE   studentId=?";
    connection.query(sql,[3],function (err,results) {
       if(err) throw err;
        console.log(results);
    });

    // OkPacket {
    //     fieldCount: 0,
    //         affectedRows: 1,
    //         insertId: 0,
    //         serverStatus: 2,
    //         warningCount: 0,
    //         message: '',
    //         protocol41: true,
    //         changedRows: 0 }

}

query();
//insert();
//update();
//del();

connection.end();

mysqlPool.js 

var mysql=require('mysql');

var pool  = mysql.createPool({
    connectionLimit : 10,//缺省也是10
    host            : 'localhost',
    user            : 'root',
    password        : 'winmshl',
    port:  '3316',
    database        : 'db_test'
});


module.exports=pool;

mysql pool  增删改查及事务的使用

// 学生管理dao 对象

var pool=require('./mysqlPool.js');


//var tableName="t_student";

//保存  插入
function save(student,callback) {

    pool.query("insert into t_student set ?",student,function (err,results,fields) {
        if(err) throw  err;
        console.log("返回自生成的主键id："+results.insertId);
        console.log(results.affectedRows+" 行受影响");
     //  results.changedRows
        if(callback){
            callback(results);
        }


    });

}

//根据id 查询
function findById(id,callback) {

    pool.query("select  * from t_student a where a.studentId= ?",[id], function (err,results) {
        if(err) throw  err;
        callback(results[0]);// 根据id查询得到唯一的一条记录
    });

}

//根据id删除
function deleteById(id,callback) {
    pool.query("delete from t_student  where studentId=? ",[id],function (err,results) {
        if(err) throw  err;
        console.log(results.affectedRows+" 行受影响");

        if(callback){
            callback(results);
        }
    });
}
//更新
function update(student ,callback) {


    var sql ="UPDATE `t_student` a SET a.`description`= ?  WHERE a.`studentId`=?";

    pool.query( sql
        ,[student.description,student.studentId],function (err,results) {
        if(err) throw  err;
        console.log(results.affectedRows+" 行受影响");
        console.log(results.affectedRows+" 行受影响");
        if(callback){
            callback(results);
        }
    });
}

//查询所有
function findAll(callback) {
    pool.query("select  * from t_student a order by a.createtime desc",function (err,results) {
        if(err) throw  err;

            callback(results);


    });

}


function saveStudentAndIdCard() {
   pool.getConnection(function (err,conn) {
       if (err) throw err;

       conn.beginTransaction(function (err) {
           try{
               if (err) throw err;
               var sql1="INSERT INTO t_student SET  NAME='陈六', " +
                   "sex=1,birthday='1993-09-08',createtime='2018-01-23 10:20:20'";

               var sql2="INSERT INTO t_student_idcard  SET   cardNum='421281199101234563' ," +
                   " cardCity='北京'  ,cardAddress='中关村110号'";

               conn.query(sql1,function (err,results) {
                   if (err) {
                       console.log(err)
                       //回滚事务
                       conn.rollback(function () {
                       });
                   }
                   console.log("11111");
                   console.log("22222");
                   conn.query(sql2,function (err,results) {

                       if (err) {
                           conn.rollback(function () {
                           });

                       }else{
                           console.log('提交事务');
                           conn.commit(function() {
                               console.log("success  ok ！！")
                           });
                       }
                   });
               })
           }finally {
               conn.release();//返回连接对象到 连接池中
           }
       });
   });
}

//测试nodejs中mysql 事务，在插入学生对象时同时插入学生的身份证对象
//saveStudentAndIdCard();


module.exports={
    save,update,deleteById,findById,findAll,

};