一种新的编程思路(上): 难得糊涂编程法 GBDP (适用jsp、asp等编程)
GBDP技术实现设想:(General Blackbox Dynamic Programming) 开发速度快、代码量少、通用,数据库表自动生成,记录自动增加, 修改,删除,系统设计工作量少,可以在需求不明确的情况下开工, 渐进式开发,拥有需求频繁更改、代码基本不动的自适应能力。特 别适合国内软件乱序开发的国情
主要解决问题: 系统设计工作量大,需求更改影响大,项目交接困难,编程风格各 异,数据库备份困难的问题
已经实现功能: 数据表自动生成,记录自动增加,修改,删除,自动文件上传
GBDP功能示例: 无须建表,不用关心表结构、字段、值,保存更新全部自动化, 所有表结构统一使用id,pid,k,v四个字段(int,int,varchar(255), varchar(255)) 自动事务处理 create table ntTest( id int primary key , pid int not null , k varchar(255) not null , v varchar(255) not null ) -- Oracle和MSSQL支持varchar到4000-8000 因为Sybase的varchar(255)是最大长度。
写入数据库和读出数据到javascript使用了 ntEncodeSimple和ntEncodeDB的方法,否则特殊字符将导致错误
指定submit的name为edit,add来控制动作方式, 数据k(key)必须避开edit,add,submit,nouse等关键字
必须使网页元素的名称和字段名称相同,控件形式由程序自动处理, 不论text,radio,textarea 例如: 意见: 未处理 同意 不同意 这3个radio的名称都必须是"意见",然后值分别为"未处理 同意 不同意"
以下是表中内容的示例:(id,pid , k , v) -8 1 意见 不同意 -7 1 份数 1 -6 1 公文种类 通知 -3 1 印发时间 2002-05-10 -2 1 保管期限 一年 -1 1 发文日期 2002-05-10
数据增删改页面: <%@page contentType="text/html;charset=GBK" %> <%@include file="ntHead.jsp" %> <%@include file="ntGBDPDefine.jsp" %> <% file://全局变量定义在ntGBDPDefine.jsp中 globalTableName = "xxx"; // 将自动生成此表,并建立索引提高速度 parentKey = "文章发布"; parentValue = ""; dbName = "myDB"; isUpload = true; // 如果需要上传,指定为true %> <%@include file="ntGBDPUpload2-1.jsp" %> <% if( isPost ){ out.println("<script>alert('成功!');window.opener.location.reload();window.close();</script>"); return; } %> <%@include file="ntGBDPUpload2-2.jsp" %>
…… 网页部分开始……
form表单 <form name="formSend" method="post" enctype="multipart/form-data" action="<%= request.getRequestURI() %><%= hasId?( "?id="+id ):"" %>" onsubmit="return checkOnSubmit()" >
交互元件: <input type="radio" name="意见" value="同意"> <input type="submit" name="edit" value=" 确定 "> <input type="file" name="附件" size="48" > GBDP确认钮 ,ok , 确定, 所有页面统一使用 type="submit" name="<%= hasId?"edit":"add" %>"
id隐含,所有页面统一使用,用于自动载入相关记录 <input type="hidden" name="<%= hasId?"id":"nouse" %>" value="<%= id %>" >
删除显示 修改页面显示“删除”按钮,用hasId判断 <% if( hasId ){ %> <tr> <td width="78">删 除</td> <td width="462"> <input type="checkbox" name="delete" value="nouse"> </td> </tr> <% } %> …… 网页部分结束…… <%@include file="ntGBDPScript.jsp" %>
以上是所有需要的代码,不用再写任何其他的代码,就可以完成记录 的增删改等功能 ---------------------------------------------------------------- 下面查询语句约2秒, 100万/10万记录 select top 100 * from xxx_main where 1=1 and pid =0 and k = '文章发布' and v = '' and id in ( select pid from xxx_numeric where k='numeric数值测试' and v >= 131129 ) and id in ( select pid from xxx_datetime where k='date日期测试' and v > '2002-01-02' ) and id in ( select pid from xxx where k='秘密等级' and v = '秘密' ) and id in ( select pid from xxx where k='紧急程度' and v = '一般' ) order by id desc ---------------------------------------------------------------- 所需文件: ntGBDPDefine.jsp
<% file://注意:本JSP被其他页面调用,不能产生额外的空格和回车
file://全局变量定义 String globalTableName ; String parentKey ="" ; String parentValue ="" ; String dbName = "eweb"; ntDB ntP = new ntDB(); ntDB nt = new ntDB(); String sql ; String sql_trans = "" ; file://用来插入后面的保存到数据库的sql语句中,使其中在事务之中间 String sql_TRANS_ERROR = " if @@error<>0 set @ntE=@ntE+1 \n";
boolean isPost = false ; { isPost = request.getMethod().equals("POST"); } boolean isEdit = false ; boolean isAdd = false ; boolean isDelete = false ; boolean isUpload = false ; boolean hasId = false; boolean hasMasterId = false; int maxReturnRows = 1000 ; int id = 0 ; // 表示主记录id号,一般为1,2,3......,必须在ntGBDPUpload.jsp // 中获取,因为当upload的情况无法从request中获取参数 int masterId = 0 ; // 同上 , 用于主从表结构的实现 file://绝对路径 String realPath ; // like 'c:\a\' { realPath = getServletConfig().getServletContext().getRealPath(""); } file://自动文件上传目录指定 String uploadDir; { uploadDir = realPath + "upload"; }
file://存放所有request的信息,使用它的原因是后面如果有upload的时候, // request将没有任何参数和值,必须从upload中重新找到 java.util.Hashtable requestHt; { requestHt = new java.util.Hashtable(); Enumeration e = request.getParameterNames(); while( e.hasMoreElements() ) { String s = (String)e.nextElement(); String sValue = request.getParameter(s); requestHt.put( s , sValue ); } } %> ---------------------------------------------------------------- 所需文件: ntGBDPUpload2-1.jsp
<%@page contentType="text/html;charset=GBK" %> <%@page import="java.lang.*,java.net.*,java.sql.*,java.text.*,java.util.*" %> <%@page import="dba.ntDB" %> <%@page import="com.jspsmart.upload.*"%> <%@page session="true" %> <%@ page errorPage="error.jsp"%>
<% file://上传文件 if( isUpload && isPost ) { java.io.File mydir=new java.io.File( uploadDir ); file://uploadDir is GlobalVar if( !mydir.exists() ) if( !mydir.mkdir() ) throw new Exception("创建上传文件夹失败![" + uploadDir + "]");
SmartUpload mySmartUpload=new SmartUpload(); mySmartUpload.initialize(pageContext);//初始化 mySmartUpload.upload();//上传 Request res = mySmartUpload.getRequest(); { file://将request参数传入requestHt全局变量 Enumeration e = res.getParameterNames(); while( e.hasMoreElements() ) { String s = ((String)e.nextElement()).trim(); String sValue = (res.getParameter(s)).trim(); requestHt.put( s , sValue ); } } Files files = mySmartUpload.getFiles(); String sfilename =""; for(int i=0;i<files.getCount();i++){ com.jspsmart.upload.File file = files.getFile(i); sfilename = file.getFileName(); // String s = sfilename ; if( !s.trim().equals("") ) requestHt.put( "uploadFileName"+i , s ); }
try{ mySmartUpload.save(uploadDir); }catch(Exception e){ throw new Exception("上传文件失败!"+ e.getMessage() ); }
} // end if upload %>
<% isEdit = isPost && requestHt.get("edit")!=null ; isAdd = isPost && requestHt.get("add")!=null ; isDelete = requestHt.get("delete")!=null ; // 这里当没有命令发出的时候,所有操作跳过 hasId = requestHt.get("id")!=null ; id = 0 ; if( requestHt.get("id")!=null ) id = Integer.parseInt( (String)requestHt.get("id") ); hasMasterId = requestHt.get("mid")!=null ; masterId = 0 ; if( requestHt.get("mid")!=null ) masterId = Integer.parseInt( (String)requestHt.get("mid") );
%>
<% file://数据检查 %>
<% file://检查表是否存在,如果没有,建5表, t_main , t, t_numeric, t_datetime, t_text sql = ""; for(int i=0;i<5;i++){ String sTableName = globalTableName ; file://default String sType = "varchar(255)"; file://default if( i==1 ) { sTableName = globalTableName + "_numeric" ; sType = "numeric(38,8)" ;} else if( i==2 ) { sTableName = globalTableName + "_datetime"; sType = "datetime"; } else if( i==3 ) { sTableName = globalTableName + "_text" ; sType = "text"; } else if( i==4 ) { sTableName = globalTableName + "_main" ; } sql = sql + " if not exists( select name from sysobjects \n"+ " where name ='"+ sTableName +"' and type='U' ) \n"+ " begin \n"+ " create table "+ sTableName +"( \n"+ " id int primary key \n"+ " , pid int not null \n"+ " , v "+ sType +" not null \n"+ " , k varchar(255) not null \n"+ " ) \n"+ " end \n"+ ""+ " if not exists( select name from sysindexes \n"+ " where name like '%idx_"+ sTableName +"_pid%' ) \n"+ " begin \n"+ " create index idx_"+sTableName+"_pid on "+sTableName+"(pid) \n"+ " end \n"+ ""+ " if not exists( select name from sysindexes \n"+ " where name like '%idx_"+ sTableName +"_k%' ) \n"+ " begin \n"+ " create index idx_"+sTableName+"_k on "+sTableName+"(k) \n"+ " end \n"+ ""; if( !sType.equals("text") ){ sql = sql + " if not exists( select name from sysindexes \n"+ " where name like '%idx_"+ sTableName +"_v%' ) \n"+ " begin \n"+ " create index idx_"+sTableName+"_v on "+sTableName+"(v) \n"+ " end \n"+ ""; } sql = sql + ""; } // end for create table nt.executeUpdate(dbName,sql); %> ---------------------------------------------------------------- 所需文件: ntGBDPUpload2-2.jsp
<% file://修改 if( isEdit && hasId ) { //... file://检查是否存在 nt.executeQuery(dbName, "select * from "+ globalTableName +"_main where id = "+id ); if( nt.rowCount <= 0 ) throw new Exception("修改"+ globalTableName +"出错:没有找到id号'"+ id +"'"); // sql = ""+ " declare @i int \n"+ " declare @ntE int \n"+ " select @ntE=0 \n"+ " begin tran \n"+ " "; file://加入额外需要在事务内执行的语句,共有3处加入, add, edit , delete file://String sql_TRANS_ERROR = " if @@error<>0 set @ntE=@ntE+1 \n"; file://String sql_trans = "" ; file://用来插入后面的保存到数据库的sql语句中,使其中在事务之中间 if( sql_trans != null && !sql_trans.equals("") ) sql = sql + sql_trans + sql_TRANS_ERROR;
Enumeration er37 = requestHt.keys(); while (er37.hasMoreElements()) { String s = ((String)er37.nextElement()).trim(); String sValue = ((String)requestHt.get(s)).trim(); if( sValue == null ) sValue = ""; sValue = nt.ntEncodeDB( sValue ); String sTableName = globalTableName ; if( s.indexOf("numeric") == 0 ) { sTableName = globalTableName + "_numeric" ; } else if( s.indexOf("date") == 0 ) { sTableName = globalTableName + "_datetime"; sValue = "'"+sValue+"'"; } else if( s.indexOf("text") == 0 ) { sTableName = globalTableName + "_text" ; sValue = "'"+sValue+"'"; } else sValue = "'"+sValue+"'"; file://必须放在这里,因为只有修改和增加数值型字段才满足sValue.equals("") if( sValue.equals("") ) continue; if( s.equals("edit") || s.equals("add") || s.equals("delete") || s.equals("id") || s.toLowerCase().indexOf("submit") >= 0 || s.toLowerCase().indexOf("nouse") >= 0 || s.toLowerCase().indexOf("useless") >= 0 ) {} else { sql = sql + " if exists ( select * from "+ sTableName +" where pid= "+ id +" and k='"+ s +"'" +" ) \n"+ " begin \n"+ " update "+ sTableName +" set v = "+ sValue +" \n"+ " where pid= "+ id +" and k='"+ s +"' \n" + " if @@error<>0 set @ntE=@ntE+1 \n"+ " end \n"+ " else \n"+ " begin \n"+ " select @i=@i "+ ""; if( !sValue.equals("") && !sValue.equals("''") ) sql = sql + " select @i = ( select isNull(min(id),0)-1 from "+ sTableName +" ) \n"+ " if @i >= 0 select @i = -1 \n"+ " insert into "+ sTableName +" (id,pid,k,v) \n"+ " values( @i, "+ id + " \n"+ " , '"+ s +"', "+ sValue +") \n"+ " if @@error<>0 set @ntE=@ntE+1 \n"+ "";
sql = sql + " "+ " end \n"+ " if @@error<>0 set @ntE=@ntE+1 \n"+ " "; } }//end while sql = sql + " if @ntE = 0 \n"+ " commit tran \n"+ " else \n"+ " begin \n"+ " rollback tran \n"+ " raiserror ('数据库执行出错! ',16,1) \n"+ " end \n"+ " "; nt.executeUpdate( dbName,sql ); } // end if edit %>
<% file://将发文单添加保存入数据库 if( isAdd ) { // 隔离局部变量 file://int rowNo = -9999; file://int formNo = -9999; file://nt.executeQuery(dbName,"select isNull(max(id),0)+1 from "+ globalTableName +""); file://formNo = Integer.parseInt( nt.data[0][0] ); file://nt.executeQuery(dbName,"select isNull(min(id),0)-1 from "+ globalTableName +""); file://rowNo = Integer.parseInt( nt.data[0][0] );
sql = ""+ " declare @ntE int \n"+ " declare @i int \n"+ " declare @formNo int \n"+ " select @ntE=0 \n"+ " begin tran \n"+ ""; file://加入额外需要在事务内执行的语句,共有3处加入, add, edit , delete file://String sql_TRANS_ERROR = " if @@error<>0 set @ntE=@ntE+1 \n"; file://String sql_trans = "" ; file://用来插入后面的保存到数据库的sql语句中,使其中在事务之中间 if( sql_trans != null && !sql_trans.equals("") ) sql = sql + sql_trans + sql_TRANS_ERROR; sql = sql + " select @i = ( select isNull(max(id),0)+1 from "+ globalTableName +"_main ) \n"+ " select @formNo = @i \n"+ " insert into "+ globalTableName +"_main (id,pid,k,v) \n"+ " values( @i, "+ (hasMasterId?masterId:0) +", '"+ parentKey +"', '"+ parentValue +"') \n"+ " if @@error<>0 set @ntE=@ntE+1 \n"+ " ";
Enumeration er44 = requestHt.keys(); while (er44.hasMoreElements()) { String s = ((String)er44.nextElement()).trim(); String sValue = ((String)requestHt.get(s)); if( sValue == null ) sValue = ""; sValue = sValue.trim(); if( sValue.equals("") ) continue; sValue = nt.ntEncodeDB( sValue ); String sTableName = globalTableName ; if( s.indexOf("numeric") == 0 ) { sTableName = globalTableName + "_numeric" ; } else if( s.indexOf("date") == 0 ) { sTableName = globalTableName + "_datetime"; sValue = "'"+sValue+"'"; } else if( s.indexOf("text") == 0 ) { sTableName = globalTableName + "_text" ; sValue = "'"+sValue+"'"; } else sValue = "'"+sValue+"'"; if( s.equals("edit") || s.equals("add") || s.equals("delete") || s.equals("id") || s.toLowerCase().indexOf("submit") >= 0 || s.toLowerCase().indexOf("nouse") >= 0 || s.toLowerCase().indexOf("useless") >= 0 ) {} else { sql = sql + " select @i = ( select isNull(min(id),0)-1 from "+ sTableName +" ) \n"+ " if @i >= 0 select @i = -1 \n"+ " insert into "+ sTableName +" (id,pid,k,v) "+ " values( @i, @formNo , '"+ s +"', "+ sValue +") "+ " if @@error<>0 set @ntE=@ntE+1 "+ " "; } }//end while sql = sql + " if @ntE = 0 "+ " commit tran "+ " else "+ " begin "+ " rollback tran "+ " raiserror ('数据库执行出错!',16,1) "+ " end "+ " "; nt.executeUpdate( dbName,sql );
} // 隔离局部变量 %>
<% file://将pid 为 id 的删除 if( isDelete ) { // file://检查pid是否存在 nt.executeQuery(dbName,"select * from "+ globalTableName +"_main where id = "+id ); if( nt.rowCount <= 0 ) throw new Exception("删除"+ globalTableName +"出错:没有找到id号'"+ id +"'");
sql = ""+ " declare @ntE int \n"+ " declare @i int \n"+ " select @ntE=0 \n"+ " begin tran \n"+ ""; file://加入额外需要在事务内执行的语句,共有3处加入, add, edit , delete file://String sql_TRANS_ERROR = " if @@error<>0 set @ntE=@ntE+1 \n"; file://String sql_trans = "" ; file://用来插入后面的保存到数据库的sql语句中,使其中在事务之中间 if( sql_trans != null && !sql_trans.equals("") ) sql = sql + sql_trans + sql_TRANS_ERROR; sql = sql + " delete from "+ globalTableName +" where pid = "+ id + "\n"+ " if @@error<>0 set @ntE=@ntE+1 \n"+ " delete from "+ globalTableName +"_numeric where pid = "+ id + "\n"+ " if @@error<>0 set @ntE=@ntE+1 \n"+ " delete from "+ globalTableName +"_datetime where pid = "+ id + "\n"+ " if @@error<>0 set @ntE=@ntE+1 \n"+ " delete from "+ globalTableName +"_text where pid = "+ id + "\n"+ " if @@error<>0 set @ntE=@ntE+1 \n"+ " delete from "+ globalTableName +"_main where id = "+ id + "\n"+ " if @@error<>0 set @ntE=@ntE+1 \n"+ " if @ntE = 0 \n"+ " commit tran \n"+ " else \n"+ " begin \n"+ " rollback tran \n"+ " raiserror ('数据库执行出错! ',16,1) \n"+ " end \n"+ ""; nt.executeUpdate(dbName, sql );
} // 隔离局部变量 %> -------------------------------------------------------------- 所需文件: ntGBDPScript.jsp
<%-- GBDP方法通用模块 本单元没有采用javascript的数组,因为采用数组后速度非常慢 --%>
<%@page contentType="text/html;charset=GBK" %> <%@page import="java.lang.*,java.net.*,java.sql.*,java.text.*,java.util.*" %> <%@page import="dba.ntDB" %> <%@page session="true" %> <%@ page errorPage="error.jsp"%>
<% if( hasId && !isDelete && !isAdd ) {
%>
<script> <% ntDB ntSV = new ntDB(); ntDB ntSV1 = new ntDB(); ntDB ntSV2 = new ntDB(); ntDB ntSV3 = new ntDB(); ntSV.executeQuery(dbName, " select * from "+ globalTableName +" where pid = "+ id + ""); ntSV1.executeQuery(dbName, " select * from "+ globalTableName +"_numeric where pid = "+ id + ""); ntSV2.executeQuery(dbName, " select * from "+ globalTableName +"_datetime where pid = "+ id + ""); ntSV3.executeQuery(dbName, " select * from "+ globalTableName +"_text where pid = "+ id + ""); for(int i=0;i<ntSV.rowCount;i++) { %> var k_<%= i%> = "<%= ntSV.data(i,"k") %>"; var v_<%= i%> = "<%= ntSV.ntEncodeSimple((ntSV.data(i,"v"))) %>"; <% } // end for for(int i=0;i<ntSV1.rowCount;i++) { String s = ntSV1.ntEncodeSimple((ntSV1.data(i,"v"))); String s_old = s; // try{ s = (new DecimalFormat("#0")).format(Double.parseDouble(s)); if( Double.parseDouble(s) == Double.parseDouble(s_old) ) ; else s = ""+Double.parseDouble(s_old); }catch(Exception e551055){ s = ""+Double.parseDouble(s_old); } %> var k_<%= i + ntSV.rowCount %> = "<%= ntSV1.data(i,"k") %>"; var v_<%= i + ntSV.rowCount %> = "<%= s %>"; <% } // end for
for(int i=0;i<ntSV2.rowCount;i++) { String s54 = ntSV2.ntEncodeSimple((ntSV2.data(i,"v",0,10))); if( ntSV2.data(i,"k").indexOf("datetime") == 0 ) s54 = ntSV2.ntEncodeSimple((ntSV2.data(i,"v",0,19))); %> var k_<%= i + ntSV.rowCount + ntSV1.rowCount %> = "<%= ntSV2.data(i,"k") %>"; var v_<%= i + ntSV.rowCount + ntSV1.rowCount %> = "<%= s54 %>"; <% } // end for for(int i=0;i<ntSV3.rowCount;i++) { %> var k_<%= i + ntSV.rowCount + ntSV1.rowCount + ntSV2.rowCount %> = "<%= ntSV3.data(i,"k") %>"; var v_<%= i + ntSV.rowCount + ntSV1.rowCount + ntSV2.rowCount %> = "<%= ntSV3.ntEncodeSimple((ntSV3.data(i,"v"))) %>"; <% } // end for %>
var ntLength = <%= ntSV.rowCount + ntSV1.rowCount + ntSV2.rowCount + ntSV3.rowCount %>; for(var ntLoop=0;ntLoop<ntLength;ntLoop++) { file://try{ eval(" var k = k_"+ ntLoop +" ; "); eval(" var v = v_"+ ntLoop +" ; "); var x = document.all( eval("k_" + ntLoop) ); if( x == null ) continue; if( x.type == "select-one" ) { for(i=0;i<x.options.length;i++) { if( x.options[i].text == v || x.options[i].value == v ) { x.selectedIndex = i; break; } } } if( x.type == "checkbox" ) { if( x.value == v ) { x.checked = true; } }
if( x.type == "text" || x.type =="textarea" ) document.all( k ).value = v ;
if( x.length > 1 && x[0].type == "radio" ) { for(i=0;i<x.length;i++) { if( x[i].value == v ) { x[i].checked = true; break; } } }
if( x.type == "undefined" && x.length == 1 ) { document.all( k ).value = v ; }
if( x.type == "undefined" && x.length > 1 ) { for(i=0;i<x.length;i++) { x[i].value = v ; } } file://test file://if( x.type == "file" ) file://{ // alert("has file type"); file://} file://}catch(e){} } // end loop
</script>
<% } // end if %> 
|