初学nodejs+express,打造cms系统（3）
上次聊到了我的base.js 插入式编程，接着聊编程正题
首先是数据库操作类
【db_mysql.js】
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();
cb(null, result);
});
})
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;直接从php改过来的，主要的几个试过了没问题，有些没试可能有小问题。select_all，select_one 一看就明白用法。showpage是分页，myinsertsql、myupsql是表单批量提交，这个可能是有些问题的，因为与php表单post有本质区别，所以这部分代码改得很乱，php在方法里是可以直接使用$_POST取值的，nodejs里不行，只能后来硬加了个 req。作为学习练习代码，先这么用着，把后面的坑踩完了再优化调整吧。
【req_res.js】因为很多功能依赖于req、res两个变量。像req.session也跟php完全不同，没有全局变量。只能整个管理类。处理一下常用的req、res事情
var req_res={
req:null
,res:null
,init(obj){
this.res=obj.res;
this.req=obj.req;
}
,get_session(key)
{
try{
var result=this.req.session[key];
if(typeof(result)=='undefined')
{
var result='';
}
}catch(e){
var result='';
}
return result;
}
,set_session(key,value)
{
try{
this.req.session[key]=value;
}catch(e){
}
}
,admin_checklogin(){
if(this.get_session('adminname')!='' && this.get_session('adminislogin')!='')
{
return true;
}else{
return false;
}
}
,admin_logingo(username){
this.set_session('adminname',username);
this.set_session('adminislogin',1);
}
,admin_loginout(){
this.set_session('adminname','');
this.set_session('adminislogin',0);
}
,admin_username()
{
if(this.admin_checklogin())
{
return this.get_session('adminname');
}else{
return '';
}
}
,gourl(url){
var html=`
<script>
location.href='${url}';
</script>
`;
this.res.send(url);
}
};上面代码也是通过base装载插入；base.use('req_res',{req:req,res:res});
一个框架基本改造好了，base也挂载了最最基础的处理功能，下面开始制作页面。
