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();

 

mysql 连接池的封装 。

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,
 
};
?