NodeJS操作Mysql示例

//config.js
module.exports={
	
	mysql:{
		host:'localhost',
		port:3306,
		user:'Joyous',
		password:'123456',
		database:'html5',
		charset:'utf8',
		connectionLimit:100,
	}
}
//mysql.js
var mysql=require('mysql');
var config = require('./config.js');
var DB  = mysql.createPool(config.mysql);
var DB_MYSQL = function(){
	__constructor();
	//数据查询接口
	this.fetchRow = function(tableName, idJson, callback) {
		DB.query('SELECT * FROM ' + tableName + ' where ? ', idJson, function(error, results){
			if(error) {
				console.log('ERROR :' + error.message);
				DB.end();
				callback(false);
			} else {
				if(results) {
					callback(results.pop());
				} else {
					callback(results);
				}
			}
		});
		
	};
	//数据插入接口
	this.insert = function(tableName, rowInfo, callback) {
		DB.query('INSERT INTO ' + tableName + ' SET ?', rowInfo, function(err, result){
			if(err) throw err;
			callback(result.insertId);
		});
		
	};
	//数据修改接口
	this.update = function(tableNmae, idJson, rowInfo, callback) {
		DB.query('UPDATE ' + tableName + ' SET ? where ?', [rowInfo, idJson], function(error, result){
			if(error) {
				console.log('UPDATE ERROR :' + error.message);
				callback(false);
			} else {
				callback(result)
			}
		});
	};
	//数据删除接口
	this.remove = function(tableName, idJson, callback){
		DB.query('DELETE ' + tableName + ' where ? ', idJson, function(err, results){
			if(err) {
				console.log('DELETE ERROR :' + err.message);
				DB.end();
				callback(false);
			} else {
				callback(true);
			}
		});
	};
	//自定义查询
	this.queryStr = function(sql,callback){
		DB.query(sql, function(error, result){
			if(error) {
				console.log('QUERY'+ error.message);
				DB.end();
				callback(false);
			} else {
				callback(result);
			}
		});
	};
	//条件查询
	this.fetchAll = function(tableName, whereJson, orderByjson, limitArr, selectStr, callback){
		var andWhere=whereJson['and'],orWhere=whereJson['or'],andArr=[],orArr=[];
		for(var i =  0;i0 ? andArr.join(' and ') : '',
			orStr = orArr.length > 0 ? orArr.join(' or ') : '',
			limitStr = limitArr.length > 0 ? ' limit '+limitArr.join(",") : "",
			orderStr = orderByjson['type'] ? ' order by ' + orderByjson['key'] + ' ' + orderByjson['type'] : '';
			var sql = 'SELECT ' + selectStr + ' FROM ' + tableName + ' where ' + andStr + orStr + orderStr + limitStr;
			DB.query(sql,function(err, results) {
				if(err) {
					console.log('FETCHALL ERROR :' + err.message);
					DB.end();
					callback(false);
				} else {
					callback(results);
				}
			});
	};
	//构造函数
	function __constructor(){

	}
};
module.exports = new DB_MYSQL();
//test.js
var DB=require('./lib/mysql.js');
DB.fetchRow('cj_sys',{id:1},function(res){
	console.log(res.id);
});

 

Tagged . Bookmark the permalink.

Comments are closed.