nodejs同步mysql操作类
nodejs越来越火了，打算稍微在深入学习一下，于是把php的mysql操作类库稍微改了一下，为了保留php思路，这里加入了同步的方法。简单测了一下，本地 没有问题，不知道同步在多人访问时候是否有坑，暂时先本地学习用着吧。
var mysql=require('mysql');
var deasync=require('deasync');
var db_mysql={
connection:null
,config:{}
,init(config){
this.config=config;
}
,strtosql(value){
try{
if(typeof(value)=='undefined' )
{
var result='';
}else{
var result=value;
}
}catch(e){
var result='';
}
result=result.replace(/\'/g,"''");
result=result.replace(/\\/g,"\\\\");
return result;
}
,open(){
this.connection = mysql.createConnection(this.config);
this.connection.connect();
}
,close(){
this.connection.end();
}
,select_all(sql)
{
var me=this;
var temp_select_all=deasync((sql,cb) => {
me.open();
me.connection.query(sql,function (err, result) {
if(err){
var result=[];
}
me.close();
//setTimeout(function(){
cb(null, result);
//},500);
});
})
return temp_select_all(sql);
}
,select_one(sql)
{
var sql=sql+' limit 0,1';
var this_result=this.select_all(sql);
try{
var result=this_result[0];
if(!result)
{
var result={};
}
}catch(e){
var result={};
}
return result;
}
,select_onebyone(sql,key){
var select_result=this.select_one(sql);
try{
var result=select_result[key];
if(typeof(result)=='undefined' )
{
var result='';
}
}catch(e){
var result='';
}
return result;
}
,dosql(sql)
{
var me=this;
var temp_dosql=deasync((sql,cb) => {
me.open();
me.connection.query(sql,[],function (err, result) {
if(err){
var result=false;
}else{
var result=true;
}
me.close();
cb(null, result);
});
})
return temp_dosql(sql);
}
,havetab(tab_name)
{
var me=this;
var sql=`select * from ${tab_name}`;
var temp_havetab=deasync((sql,cb) => {
me.open();
me.connection.query(sql,function (err, result) {
if(err){
var result=false;
}else{
var result=true;
}
me.close();
//setTimeout(function(){
cb(null, result);
//},500);
});
})
return temp_havetab(sql);
}
,havefield(tab_name,fieldname){
var sql=`Describe ${tab_name} ${fieldname}`;
var this_list=this.select_all(sql);
if(this_list.length>0)
{
return true;
}else{
return false;
}
}
,myinsertsql(req,str,notin,gettype)
{
console.log(notin);
if(!notin)
{
var notin=[];
}
if(!gettype)
{
var gettype='get';
}
var arr = str.split('|');
var obj={};
var obj_notin={};
var arr_length=arr.length;
var sql1_arr=[];
var sql2_arr=[];
for(var i in arr)
{
obj[arr[i]]=1;
}
for(var i in notin)
{
obj_notin[notin[i]]=1;
}
console.log(obj_notin);
for(var key in obj)
{
if(obj_notin[key]!=1)
{
try{
if(gettype=="get")
{
var this_value=this.strtosql(req.query[key]);
}else{
var this_value=this.strtosql(req.body[key]);
}
}catch(e){
var this_value='';
}
sql1_arr.push(key);
sql2_arr.push(`'${this_value}'`);
}
}
var sql1=sql1_arr.join(',');
var sql2=sql2_arr.join(',');
var result=[sql1,sql2];
return result;
}
,myupsql(req,str,notin,gettype)
{
if(!notin)
{
var notin=[];
}
//console.log(notin);
if(!gettype)
{
var gettype='get';
}
var arr = str.split('|');
var obj={};
var obj_notin={};
var arr_length=arr.length;
var sql1_arr=[];
var sql2_arr=[];
for(var i in arr)
{
obj[arr[i]]=1;
}
for(var i in notin)
{
obj_notin[notin[i]]=1;
}
for(var key in obj)
{
if(obj_notin[key]!=1)
{
try{
if(gettype=="get")
{
var this_value=this.strtosql(req.query[key]);
}else{
var this_value=this.strtosql(req.body[key]);
}
}catch(e){
var this_value='';
}
var this_html=`${key}='${this_value}'`;
sql1_arr.push(this_html);
}
}
var sql1=sql1_arr.join(',');
var result=[sql1];
return result;
}
,showpage(req,sql,pagename,perpage=20,pernumber)
{
if(!pagename)
{
var pagename='pagenum';
}
if(!perpage)
{
var perpage=20;
}
if(!pernumber)
{
var pernumber=5;
}
var pageurl='';
if(!req.query)
{
req.query={};
}
try{
var page=req.query[pagename];
if(typeof(page)=='undefined')
{
var page='';
}
}catch(e){
var page='';
}
if (page=="")
{
page=1;
}else{
page=page*1;
}
//$perpage
var start=perpage*(page-1);
var end=perpage*page;
var newsql=`${sql} limit ${start} ,${perpage}`;
var numsql=`select count(*) as num from (${sql}) TAB_GG_UU_YY`;
//console.log(numsql);
var allnum=this.select_onebyone(numsql,'num');
allnum=allnum*1;
var allpagenum=Math.ceil(allnum/perpage);
var query_arr=[];
for(var i in req.query)
{
console.log(` ******** i -> ${i}`)
if(i!=pagename)
{
var this_query=`${i}=${req.query[i]}`;
query_arr.push(this_query);
}
}
var get_page_url=function(query_arr,page)
{
if(query_arr.length>0)
{
pageurl='?'+query_arr.join('&')+`&${pagename}=${page}`;
}else{
pageurl='?'+`&${pagename}=${page}`;
}
return pageurl;
}
pageurl=get_page_url(query_arr,page);
var shang_url=pageurl=get_page_url(query_arr,page-1);
var xia_url=pageurl=get_page_url(query_arr,page+1);
//上一页
if(page>1)
{
var shang_html_layui=`<a href="${shang_url}" class="layui-laypage-prev" ><i class="layui-icon"></i></a>`;
}else{
var shang_html_layui=`<a href="javascript:;" class="layui-laypage-prev layui-disabled" ><i class="layui-icon"></i></a>`;
}
//下一页
if(page<allpagenum)
{
var xia_html_layui=`<a href="${xia_url}" class="layui-laypage-next" ><i class="layui-icon"></i></a>`;
}else{
var xia_html_layui='<a href="javascript:;" class="layui-laypage-next layui-disabled" ><i class="layui-icon"></i></a>';
}
//当中分页
var chaju=Math.ceil(pernumber/2);
var startpage=page-pernumber+chaju;
if(startpage<=0)
{
startpage=1;
}
var endpage=startpage+pernumber-1;
if(endpage>allpagenum)
{
endpage=allpagenum;
startpage=endpage-pernumber+1;
if (startpage<=0)
{
startpage=1;
}
}
var all_html="";
var all_html_easyweb="";
for(var index=startpage;index<=endpage;index++)
{
if(page!=index)
{
var this_url=get_page_url(query_arr,index);
var all_html_easyweb=all_html_easyweb+`<a href='${this_url}' data-page='${index}'>{index}</a>`;
var all_html_layui=all_html_layui+`<a href="${this_url}" >${index}</a>`;
}else{
var all_html_easyweb=all_html_easyweb+`<span class='layui-laypage-curr'><em class='layui-laypage-em'></em><em>${index}</em></span>`;
var all_html_layui=all_html_layui+`<span class="layui-laypage-curr"><em class="layui-laypage-em"></em><em>${index}</em></span>`;
}
}
//跳转页
var tiao_zhuanurl_easyweb="";
for(var index=1;index<=allpagenum;index++)
{
var this_url=get_page_url(query_arr,index);
var this_selected="";
if(index==page)
{
var this_selected=" selected='selected' ";
}
tiao_zhuanurl_easyweb=tiao_zhuanurl_easyweb+`<option value='${this_url}' ${this_selected}>第${index}页</option>`;
}
var tiao_zhuanurl_easyweb=` <span class='layui-laypage-limits'>共 ${allnum} 条</span><span class='layui-laypage-skip'>转到</span> <span class='layui-laypage-limits my_page_select'><select onchange='window.location=this.value;'>${tiao_zhuanurl_easyweb}</select></span>`;
if(allnum==0)
{
var easyweb_pagehtml='<span class="layui-laypage-count">暂无数据</span>';
}else{
var easyweb_pagehtml=`${shang_html_layui} ${all_html_layui} ${xia_html_layui} ${tiao_zhuanurl_easyweb}`;
}
var result={
sql:newsql
,allnum:allnum
,page:page
,allpagenum:allpagenum
,easyweb_pagehtml:easyweb_pagehtml
};
return result;
}
};
module.exports = db_mysql;
