JavaScript生成复杂的SQL查询表单

小提示

Ctrl+/代码提示(需IE11)
F11键放大 ESC键缩小
PgUp上页 PgDn下页

推荐代码

运行代码缩小

引入JS
- 3.0.0 2.2.0 2.1.4 2.1.3 2.1.2 2.1.1 2.1.0 2.0.3 2.0.2 2.0.1 2.0.0 1.12.0 1.11.3 1.11.2 1.11.1 1.11.0 1.10.2 1.10.1 1.10.0 1.9.1 1.9.0 1.8.3 1.8.2 1.8.1 1.8.0 1.7.2 1.7.1 1.7 1.6.4 1.6.2 1.6.1 1.4.4 1.4.3 1.4.2 1.4.1 1.4.0
- 1.7.3 1.7.2 1.7.1.0 1.7.0.0
- 2.0.0-beta.0 2.0.0-alpha.55 1.5.0-rc.0 1.4.8 1.4.0 1.3.20 1.2.29 1.1.5 1.0.8
- 1.8.3 1.7.0 1.6.0 1.5.2 1.4.4 1.3.3 1.2.4 1.1.7
- 1.10.4 1.9.7 1.8.10 1.7.8 1.6.3 1.5.4 1.4.6 1.3.3
- core1.6.0 core-compat1.6.0 core1.5.2 core-compat1.5.2 core-full-compat-yc1.4.5 core-full-compat1.4.5 core-full-nocompat-yc1.4.5 core-full-nocompat1.4.5
- 1.4.2 1.4.1 1.4.0 1.3.0 1.2.0 1.1.0
- 3.7.3 3.7.2 3.7.1 3.7.0 3.7 3.6.2 3.6.1 3.6

引入框架
- 1.4.5 1.4.4 1.4.3 1.4.2 1.4.1 1.4.0 1.3.2
- 1.11.4 1.11.3 1.11.2 1.11.1 1.11.0 1.10.4 1.10.3 1.10.2
- 3.3.6 3.3.5 3.3.0 3.2.0 2.3.2

汉王

HTML代码

复制格式化注释注释清空

放大

<!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>

CSS代码

复制格式化注释注释颜色清空

放大

JS代码

复制格式化注释注释清空

放大

运行代码

简介

请输入代码名称！

名称

JavaScript生成复杂的SQL查询表单

分类

表单按钮

描述

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