JavaScript生成SQL查询表单的方法
2015-08-13 17:09作者：yezheng
本文实例讲述了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程序设计有所帮助。