📄 querysqltransition.java
字号:
package com.gs.pageBuilder.util;
/**
* <p>Title: </p>
* <p>Description: </p>
* <p>Copyright: Copyright (c) 2003</p>
* <p>Company: </p>
* @author not attributable
* @version 1.0
*/
import java.util.*;
import java.sql.*;
import com.gs.util.*;
public class querySqlTransition{
public querySqlTransition(){
}
//取得页面变量做sql规则转换，应用于模糊查询
public static String getQuerySql(HashMap pageParMap){
//接收页面参数
HashMap pageParMapTemp = new HashMap();
ArrayList sub = null;
String xname;
String xValue;
String xValue1;
String xValue2;
String tempStr;
String result = "";
String orderbyStr="";
String orderbyState="0";
try{
if(pageParMap == null){
return result;
}
Debug.println("[querySqlTransition]---->getQuerySql pageParMap 'size():" + pageParMap.size());
for(int i = 0;i < pageParMap.size();i++){
// sub = (ArrayList) pageParMap.get((Object)new Integer(i));
sub = (ArrayList) (pageParMap.get((Object)new Integer(i)));
xname = (String) sub.get(0);
xValue = (String) sub.get(1);
Debug.println("i="+i+" usercbg xname:"+xname);
Debug.println("usercbg xValue:"+xValue);
//异常处理少于9个的变量不做串处理
if(xname.length() > 9){
//1.一般查询条件 如 字段表示 eg: query0101field004 and field004>?
if("query".equals(xname.substring(0,5))){
tempStr = xname.substring(9,xname.length());
//01并且 02或
if("01".equals(xname.substring(5,7))){
result += " and ";
//01数字大于 ,02 数字小于 ,03数字 大于等于 ,04数字 小于等于, 05字符等于, *06 在之间 ,07不等于 ,08 全包含，09，左包含,10,右包含,11字符大于，12字符小于，13字符大于等于，14字符小于等于
if("01".equals(xname.subSequence(7,9))){
result += " " + tempStr + " > " + xValue + " ";
} else
if("02".equals(xname.subSequence(7,9))){
result += " " + tempStr + " < " + xValue + " ";
} else
if("03".equals(xname.subSequence(7,9))){
result += " " + tempStr + " >= " + xValue + " ";
} else
if("04".equals(xname.subSequence(7,9))){
result += " " + tempStr + " <= " + xValue + " ";
} else
if("05".equals(xname.subSequence(7,9))){
result += " " + tempStr + " = '" + xValue + "' ";
} else
if("06".equals(xname.subSequence(7,9))){
//result+=" "+tempStr+" > '"+xValue+"' ";
} else
if("07".equals(xname.subSequence(7,9))){
result += " " + tempStr + " <> '" + xValue + "' ";
} else
if("08".equals(xname.subSequence(7,9))){
result += " " + tempStr + " like '%" + xValue + "%' ";
}
if("09".equals(xname.subSequence(7,9))){
result += " " + tempStr + " like '" + xValue + "%' ";
}else
if("10".equals(xname.subSequence(7,9))){
result += " " + tempStr + " like '%" + xValue + "' ";
}else
if("11".equals(xname.subSequence(7,9))){
result += " " + tempStr + " > '" + xValue + "' ";
} else
if("12".equals(xname.subSequence(7,9))){
result += " " + tempStr + " < '" + xValue + "' ";
} else
if("13".equals(xname.subSequence(7,9))){
result += " " + tempStr + " >= '" + xValue + "' ";
} else
if("14".equals(xname.subSequence(7,9))){
result += " " + tempStr + " <= '" + xValue + "' ";
}
} else
if("02".equals(xname.substring(5,7))){
if(xValue.length()<2 || xValue.indexOf("/") ==-1)continue;
StringTokenizer subTokenizer = new StringTokenizer(xValue, "/");
result += " and ( 2=1 ";
while (subTokenizer.hasMoreTokens()) {
xValue=(String) (subTokenizer.nextToken());
result += " or ";
//01数字大于 ,02 数字小于 ,03数字 大于等于 ,04数字 小于等于, 05字符等于, *06 在之间 ,07不等于 ,08 全包含，09，左包含,10,右包含,11字符大于，12字符小于，13字符大于等于，14字符小于等于
if("01".equals(xname.subSequence(7,9))){
result += " " + tempStr + " > " + xValue + " ";
} else
if("02".equals(xname.subSequence(7,9))){
result += " " + tempStr + "< " + xValue + " ";
} else
if("03".equals(xname.subSequence(7,9))){
result += " " + tempStr + " >= " + xValue + " ";
} else
if("04".equals(xname.subSequence(7,9))){
result += " " + tempStr + " <= " + xValue + " ";
} else
if("05".equals(xname.subSequence(7,9))){
result += " " + tempStr + " = '" + xValue + "' ";
} else
if("06".equals(xname.subSequence(7,9))){
//result+=" "+tempStr+" > '"+xValue+"' ";
} else
if("07".equals(xname.subSequence(7,9))){
result += " " + tempStr + " <> '" + xValue + "' ";
} else
if("08".equals(xname.subSequence(7,9))){
result += " " + tempStr + " like '%" + xValue + "%' ";
} else
if("09".equals(xname.subSequence(7,9))){
result += " " + tempStr + " like '" + xValue + "%' ";
} else
if("10".equals(xname.subSequence(7,9))){
result += " " + tempStr + " like '%" + xValue + "' ";
}else
if("11".equals(xname.subSequence(7,9))){
result += " " + tempStr + " > '" + xValue + "' ";
} else
if("12".equals(xname.subSequence(7,9))){
result += " " + tempStr + " < '" + xValue + "' ";
} else
if("13".equals(xname.subSequence(7,9))){
result += " " + tempStr + " >= '" + xValue + "' ";
} else
if("14".equals(xname.subSequence(7,9))){
result += " " + tempStr + " <= '" + xValue + "' ";
}
}
result += " ) ";
}
} else
//2.两个同时比较 compare01field049_field049a_field049b
if("compare".equals(xname.substring(0,7))){
//01数字并且 ,02数字或 ，03字符并且，04字符或
if("01".equals(xname.substring(7,9))){
result += " and ";
tempStr = xname.substring(9,xname.length());
Debug.println("[querySqlTransition]-getQuerySql--->compare:" + tempStr);
//01 数字大于,02 数字小于,03 数字大于等于,04 数字小于等于,05 等于,06 在....之间
//取变量组合 and field049 > 'field049a' and (field049>='field049a' and )
StringTokenizer subTokenizer = new StringTokenizer(tempStr,"_");
String tempStr1 = "";
String tempStr2 = "";
if(subTokenizer.hasMoreTokens()){
tempStr = (String) (subTokenizer.nextToken());
}
if(subTokenizer.hasMoreTokens()){
//取得数值
tempStr1 = (String) (subTokenizer.nextToken());
tempStr1 = getMapValue(pageParMap,tempStr1);
}
if(subTokenizer.hasMoreTokens()){
tempStr2 = (String) (subTokenizer.nextToken());
tempStr2 = getMapValue(pageParMap,tempStr2);
}
//获得sql
if(!"".equals(tempStr) || !"".equals(tempStr1) || !"".equals(tempStr2)){
if("01".equals(xValue)){
result += " " + tempStr + " >" + tempStr1 + " ";
} else
if("02".equals(xValue)){
result += " " + tempStr + " < " + tempStr1 + " ";
} else
if("03".equals(xValue)){
result += " " + tempStr + " >= " + tempStr1 + " ";
} else
if("04".equals(xValue)){
result += " " + tempStr + " <= " + tempStr1 + " ";
} else
if("05".equals(xValue)){
result += " " + tempStr + " = '" + tempStr1 + "' ";
} else
if("06".equals(xValue)){
result += " " + tempStr + " >= " + tempStr1 + " and " + tempStr + " <= " + tempStr2 + " ";
}
}
} else
//01数字并且 ,02数字或 ，03字符并且，04字符或
if("02".equals(xname.substring(7,9))){
result += " or ";
tempStr = xname.substring(9,xname.length());
//01 数字大于,02 数字小于,03 数字大于等于,04 数字小于等于,05 等于,06 在....之间
//取变量组合 and field049 > 'field049a' and (field049>='field049a' and )
StringTokenizer subTokenizer = new StringTokenizer(tempStr,"_");
String tempStr1 = "";
String tempStr2 = "";
if(subTokenizer.hasMoreTokens()){
tempStr = (String) (subTokenizer.nextToken());
}
if(subTokenizer.hasMoreTokens()){
//取得数值
tempStr1 = (String) (subTokenizer.nextToken());
tempStr1 = getMapValue(pageParMap,tempStr1);
}
if(subTokenizer.hasMoreTokens()){
tempStr2 = (String) (subTokenizer.nextToken());
tempStr2 = getMapValue(pageParMap,tempStr2);
}
//获得sql
if(!"".equals(tempStr) || !"".equals(tempStr1) || !"".equals(tempStr2)){
if("01".equals(xValue)){
result += " " + tempStr + " > " + tempStr1 + " ";
} else
if("02".equals(xValue)){
result += " " + tempStr + " < " + tempStr1 + " ";
} else
if("03".equals(xValue)){
result += " " + tempStr + " >= " + tempStr1 + " ";
} else
if("04".equals(xValue)){
result += " " + tempStr + " <= " + tempStr1 + " ";
} else
if("05".equals(xValue)){
result += " " + tempStr + " = '" + tempStr1 + "' ";
} else
if("06".equals(xValue)){
result += " " + tempStr + " >= " + tempStr1 + " and " + tempStr + " <= " + tempStr1 + " ";
}
}
}else
//字符并且
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -