本篇,我们介绍下,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,
};
?