JavaScript生成SQL查询表单的方法

前端技术 2023/09/02 JavaScript

本文实例讲述了JavaScript生成SQL查询表单的方法。分享给大家供大家参考。具体如下：

这里使用JavaScript生成复杂的SQL查询表单，运行一下就明白了，它可以根据选择的查询条件，自动修改你的SQL语句，是一个很典型的应用。

运行效果截图如下：

具体代码如下：

<!DOCTYPE html PUBLIC \"-//W3C//DTD XHTML 1.0 Transitional//EN\"
\"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd\">
<head>
<meta http-equiv=\"Content-Type\" content=\"text/html; charset=gb2312\" />
<title>查询条件表单</title>
<style>
*{
 font-size:12px;
 padding:0;
 margin:0;
}
body{
 padding:40px;
}
#MainBox{
 border:#666 1px solid;
 background-color:#eee;
 width:700px;
}
#MainBox td{
 padding:4px;
}
#ConditionBox{
 height:150px;
 width:100%;
 overflow-y:auto;
 border:#bbb 1px solid;
 padding:2px;
 background-color:#fff;
}
.tmFrame{
 border:#eee 1px solid;
 padding:2px;
 width:100%;
}
.tmFrame_highlight{
 border:#666 1px solid;
 padding:2px;
 width:100%;
 background-color:#f7f7f7;
}
.fname{
 float:left;
 width:200px;
}
.conn{
 float:left;
 width:100px;
}
.fvalue{
 float:left;
 width:100px;
}
.handlebox{
 float:right;
 width:180px;
 display:none;
}
.handlebox_view{
 float:right;
 width:180px;
 display:block;
}
.rbox{
 float:right;
 margin:1px;
 background-color:#999;
 color:#fff;
 padding:1px;
 width:15px;
 cursor:hand;
}
legend{
 border:#bbb 1px solid;
 padding:4px;
}
fieldset{
 border:#bbb 1px solid;
 padding:4px;
}
.sqlwords{
 margin:2px;
 border:#bbb 1px solid;
 width:100%;
}
</style>
<script>
////构造函数
function ce(e){return document.createElement(e)}
/* Example:
* var a = cex(\"DIV\", {onmouseover:foo, name:\'div1\', id:\'main\'});
*/
function cex(e, x){
 var a = ce(e);
 for (prop in x){
  a[prop] = x[prop];
 }
 return a;
}
/*
* function ge
* Shorthand function for document.getElementById(i)
*/
function ge(i){return document.getElementById(i)}
/*
* function ac
* Example: ac( house, ac(roof, shingles), ac(floor, ac(tiles, grout)))
*/
function ac(){
 if (ac.arguments.length > 1){
  var a = ac.arguments[0];
  for (i=1; i<ac.arguments.length; i++){
   if (arguments[i])
    a.appendChild(ac.arguments[i]);
  }
  return a;
 } else {
  return null;
 }
}
/////ID增量
function guid(){
 if (!window.__id) window.__id = 0;
 return ++window.__id;
}
//======建立条件类
function term(tname,fname,conn,fvalue,ttype){
 this.tname=tname;
 this.fname=fname;
 this.conn=conn;
 this.fvalue=fvalue;
 this.id= guid();
 this.ttype=ttype;
}
term.prototype.getHTML = function(){
 var termFrame = cex(\"DIV\", {
  id:this.id,
  className:\'tmframe\',
  onmouseover:this.fc_term_onmouseover(),
  onmouseout:this.fc_term_onmouseout()
  });
 //var module = cex(\"DIV\", {
  //id:\'module\'+this.id,
  //className:\'module\'
  //});
 var tttt=this.tname+\".\"+this.fname;
 if(this.ttype!=\'fset\')
  tttt=this.tname;
 var mtt = cex(\"input\", {
  id:\'tp\'+this.id,
  name:\'fname\'+this.id,
  type:\"hidden\",
  value:this.ttype
  });
 var fname = cex(\"DIV\", {
  id:\'fname\'+this.id,
  className:\'fname\',
  innerHTML:tttt
  });
 var conn = cex(\"DIV\", {
  id:\'conn\'+this.id,
  className:\'conn\',
  innerHTML:this.conn
  });
 var fvalue = cex(\"DIV\", {
  id:\'fvalue\'+this.id,
  className:\'fvalue\',
  innerHTML:this.fvalue
  });
 var handlebox = cex(\"div\", {
  id:\'handlebox\'+this.id,
  className:\"handlebox\"
  });
 var mdel = cex(\"div\", {
  id:\'tmdel\'+this.id,
  onclick:this.fc_mdel_onclick(),
  className:\"rbox\",
  title:\"删除此条件\",
  innerHTML: \'X\'
  });
 var mup = cex(\"div\", {
  id:\'tmup\'+this.id,
  onclick:this.fc_mup_onclick(),
  className:\"rbox\",
  title:\"向上移动\",
  innerHTML: \'↑\'
  });
 var mdown = cex(\"div\", {
  id:\'tmdown\'+this.id,
  onclick:this.fc_mdown_onclick(),
  className:\"rbox\",
  title:\"向下移动\",
  innerHTML: \'↓\'
  });
 var mzkh = cex(\"div\", {
  id:\'tzkh\'+this.id,
  onclick:this.fc_mzkh_onclick(),
  className:\"rbox\",
  title:\"添加左括号\",
  innerHTML: \'(\'
  });
 var mykh = cex(\"div\", {
  id:\'tykh\'+this.id,
  onclick:this.fc_mykh_onclick(),
  className:\"rbox\",
  title:\"添加右括号\",
  innerHTML: \')\'
  });
 var mand = cex(\"div\", {
  id:\'tand\'+this.id,
  onclick:this.fc_mand_onclick(),
  className:\"rbox\",
  title:\"添加并条件\",
  innerHTML: \'and\'
  });
 var mor = cex(\"div\", {
  id:\'tor\'+this.id,
  onclick:this.fc_mor_onclick(),
  className:\"rbox\",
  title:\"添加或条件\",
  innerHTML: \'or\'
  });
 // Build DIV
 ac (termFrame,
   mtt,
   ac (handlebox,
   mdel,
   mup,
   mdown,
   mykh,
   mzkh,
   mand,
   mor
   ),
   fname,
   conn,
   fvalue
  );
 return termFrame;
}
term.prototype.highlight = function(){
 ge(\"handlebox\"+this.id).className = \'handlebox_view\'; 
 ge(this.id).className = \'tmFrame_highlight\';
}
term.prototype.lowlight = function(){
 ge(\"handlebox\"+this.id).className = \'handlebox\';
 ge(this.id).className = \'tmFrame\';
}
term.prototype.remove = function(){
 var _this = ge(this.id);
 _this.parentNode.removeChild(_this);
}
term.prototype.moveup = function(){
 var _this = ge(this.id);
 var pre_this = _this.previousSibling;
 if(pre_this!=null){
  _this.parentNode.insertBefore(_this,pre_this);
  this.lowlight();
 }
}
term.prototype.movedown = function(){
 var _this = ge(this.id);
 var next_this = _this.nextSibling;
 if(next_this!=null){
  _this.parentNode.insertBefore(next_this,_this);
  this.lowlight();
 }
}
term.prototype.addzkh = function(){
 var _this = ge(this.id);
 var tzkh = new term(\'╭----------------\',\'\',\'\',\'\',\'zkh\');
 var node_zkh = tzkh.getHTML();
 _this.parentNode.insertBefore(node_zkh,_this);
}
term.prototype.addykh = function(){
 var _this = ge(this.id);
 var tykh = new term(\'╰----------------\',\'\',\'\',\'\',\'ykh\');
 var node_ykh = tykh.getHTML();
 if(_this.nextSibling!=null)
  _this.parentNode.insertBefore(node_ykh,_this.nextSibling);
 else
  _this.parentNode.appendChild(node_ykh);
}
term.prototype.addand = function(){
 var _this = ge(this.id);
 var tand = new term(\' 并且\',\'\',\'\',\'\',\'tand\');
 var node_and = tand.getHTML();
 if(_this.nextSibling!=null)
  _this.parentNode.insertBefore(node_and,_this.nextSibling);
 else
  _this.parentNode.appendChild(node_and);
}
term.prototype.addor = function(){
 var _this = ge(this.id);
 var tor = new term(\' 或者\',\'\',\'\',\'\',\'tor\');
 var node_or = tor.getHTML();
 if(_this.nextSibling!=null)
  _this.parentNode.insertBefore(node_or,_this.nextSibling);
 else
  _this.parentNode.appendChild(node_or);
}
///对象控制函数
term.prototype.fc_term_onmouseover = function(){
 var _this = this;
 return function(){
  //if (!_this.isDragging)
   _this.highlight();
 }
}
term.prototype.fc_term_onmouseout = function(){
 var _this = this;
 return function(){
  //if (!_this.isDragging)
   _this.lowlight();
 }
}
term.prototype.fc_mdel_onclick = function(){
 var _this = this;
 return function(){
  _this.remove();
 }
}
term.prototype.fc_mup_onclick = function(){
 var _this = this;
 return function(){
  _this.moveup();
 }
}
term.prototype.fc_mdown_onclick = function(){
 var _this = this;
 return function(){
  _this.movedown();
 }
}
term.prototype.fc_mzkh_onclick = function(){
 var _this = this;
 return function(){
  _this.addzkh();
 }
}
term.prototype.fc_mykh_onclick = function(){
 var _this = this;
 return function(){
  _this.addykh();
 }
}
term.prototype.fc_mand_onclick = function(){
 var _this = this;
 return function(){
  _this.addand();
 }
}
term.prototype.fc_mor_onclick = function(){
 var _this = this;
 return function(){
  _this.addor();
 }
}
/////插入页面
function insertterm(){
 var tname = document.all.tname.value;
 var fname = document.all.fname.value;
 var conn = document.all.conn.value;
 var fvalue = document.all.fvalue.value;
 //xl(tname+\"|\"+fname+\"|\"+conn+\"|\"+fvalue);
 var tm = new term(tname,fname,conn,fvalue,\"fset\");
 var tmHTML = tm.getHTML();
 ac(ge(\"ConditionBox\"),tmHTML);
 //ZA.addterm(tm);
 addtofrom(tname);
}
var tt = new Array();
function addtofrom(tname){
  var ttexit=\"no\";
  for(var i=0;i<tt.length;i++){
   if(tt[i]==tname)
    ttexit=\"yes\";     
  }
  if(ttexit==\"no\"){
   tt[i]=tname;
   //alert(tt[i]);
  }
}
//====条件控制窗口函数
function CBadd(){
 var h = document.all.ConditionBox.offsetHeight;
 document.all.ConditionBox.style.height = h + 20 + \"px\";
}
function CBcut(){
 var h = document.all.ConditionBox.offsetHeight;
 if(h>=150)
  document.all.ConditionBox.style.height = h - 20 + \"px\";
 else
  return false;
}
function getSQL(){
 var sql=\"\";
 var ma = ge(\"ConditionBox\").childNodes;
 for(i=0;i<ma.length;i++){
  var id = ma[i].getAttribute(\"id\");
  var tp = ge(\"tp\"+id).value;
  if(tp==\"fset\"){
   //sql+=\" \"+ge(\"fname\"+id).innerHTML;
   //sql+=\" \"+ge(\"conn\"+id).innerHTML;
   //sql+=\" \\\"\"+ge(\"fvalue\"+id).innerHTML+\"\\\"\";
   var fname=ge(\"fname\"+id).innerHTML;
   var conn=ge(\"conn\"+id).innerHTML;
   var fvalue=ge(\"fvalue\"+id).innerHTML;
   sql+=\" \"+fname;
   if(conn==\"等于\")
    sql+=\" = \"+\"\\\'\"+fvalue+\"\\\'\";
   if(conn==\"大于\")
    sql+=\" > \"+\"\\\'\"+fvalue+\"\\\'\";
   if(conn==\"小于\")
    sql+=\" < \"+\"\\\'\"+fvalue+\"\\\'\";
   if(conn==\"不等于\")
    sql+=\" <> \"+\"\\\'\"+fvalue+\"\\\'\";
   if(conn==\"为空\")
    sql+=\" is null \";
   if(conn==\"不为空\")
    sql+=\" is not null \";
   if(conn==\"包含\")
    sql+=\" like \\\'%\"+fvalue+\"%\\\'\";
  }
  else{
   //sql+=\" \"+ge(\"fname\"+id).innerHTML;
   if(tp==\"zkh\")
    sql+=\" (\";
   if(tp==\"ykh\")
    sql+=\" )\";
   if(tp==\"tand\")
    sql+=\" and\";
   if(tp==\"tor\")
    sql+=\" or\";
  }
  //var mn = ma.childNodes;
 }
 var ffrom = \"FROM \"+getFrom();
 ge(\"sqlwords\").value =\"Select * \"+ ffrom+\" Where \"+sql;
}
function getFrom(){
 var ff=tt.toString();
 return ff;
}
</script>
</head>
<body>
<table border=\"0\" cellspacing=\"0\" cellpadding=\"0\" id=\"MainBox\">
 <tr> 
 <td colspan=\"2\" style=\"background-color:#999;color:#000;font-weight:bolder;font-size:14px\">复杂查询表单</td>
 </tr>
 <tr> 
 <td><div id=\"ConditionBox\"></div>
 <div style=\"width:100%\"><SPAN title=\'放大显示框\' style=\'float:right;FONT-SIZE: 14px; CURSOR: hand;FONT-FAMILY: webdings\' onclick=\'CBadd()\'>6</SPAN><SPAN title=\'缩小显示\' style=\'float:right;FONT-SIZE: 14px; CURSOR: hand;FONT-FAMILY: webdings\' onclick=\'CBcut()\'>5</SPAN></div></td>
 </tr>
 <tr> 
 <td>
 <fieldset>
 <legend>SQL表达式</legend>
  <input type=\"text\" id=\"sqlwords\" class=\"sqlwords\" /><input type=\"submit\" name=\"Submit\" value=\"GET SQL\" onclick=\"getSQL()\" style=\"float:right\"/>
  </fieldset>
  </td>
 </tr>
 <tr> 
 <td>
 <fieldset>
 <legend>定义条件</legend>
 <table width=\"100%\" border=\"0\" cellspacing=\"0\" cellpadding=\"0\">
  <tr>
   <td>表</td>
   <td><select name=\"tname\" id=\"tname\">
    <option value=\"table1\" selected=\"selected\">表1</option>
    <option value=\"table2\">表2</option>
    <option value=\"table3\">表3</option>
    <option value=\"table4\">表4</option>
    <option value=\"table5\">表5</option>
   </select></td>
   <td>字段</td>
   <td><select name=\"fname\" id=\"fname\">
    <option value=\"f1\">字段1</option>
    <option value=\"f2\">字段2</option>
    <option value=\"f3\">字段3</option>
    <option value=\"f4\">字段4</option>
    <option value=\"f5\">字段5</option>
    <option value=\"f6\">字段6</option>
    <option value=\"f7\">字段7</option>
   </select></td>
   <td>关系</td>
   <td><select name=\"conn\" id=\"conn\">
    <option value=\"大于\">大于</option>
    <option value=\"等于\">等于</option>
    <option value=\"小于\">小于</option>
    <option value=\"不等于\">不等于</option>
    <option value=\"为空\">为空</option>
    <option value=\"不为空\">不为空</option>
    <option value=\"包含\">包含</option>
   </select></td>
   <td>值</td>
   <td><input name=\"fvalue\" type=\"text\" id=\"fvalue\" value=\"111111\" /></td>
   <td><input type=\"submit\" name=\"Submit\" value=\"增加新条件\" onclick=\"insertterm()\"/></td>
  </tr>
  </table>
  </fieldset>
  </td>
 </tr>
</table>
</body>
</html>

希望本文所述对大家的javascript程序设计有所帮助。

本文地址：https://www.stayed.cn/item/3414

转载请注明出处。

本站部分内容来源于网络,如侵犯到您的权益,请 联系我

我的博客

人生若只如初见，何事秋风悲画扇。

我的标签

随笔档案