sqlfunc.java

来自「采用web2.0技术,采用动态标签,sql语句全部存储在数据库里面.开发速度快.」· Java 代码 · 共 241 行

package com.sztheater.framework.util;
import java.sql.*;
import java.util.*;
/**
 *	公共函数
 */
public class SqlFunc {
	public static String TYPE_NUMBER = ",number,numeric,real,smallmoney,num,float,decimal,double,int,integer,long,longint,money,bigint,binary,bit,smallint,tinyint,uniqueidentifier,";
	public static String TYPE_DATE   = ",date,datetime,timestamp,smalldatetime,time,";
	public static String TYPE_STRING = ",str,string,char,nchar,ntext,nvarchar,sql_variant,sysname,text,varchar,varchar2,";
	public static String TYPE_LOGIC  = ",like,like%,%like,%like%,>,<,=,>=,<=,";
	
	public static String getSqlLogic(String f_logic){
		if(f_logic==null) f_logic = "";
		f_logic = f_logic.trim().toLowerCase();
		String s_logic  = ","+f_logic+",";
		if(TYPE_LOGIC.indexOf(s_logic)>-1) return f_logic;
		return "=";
	}
	public static String getDataType(String data_type) {
		String Data_Type = data_type;
		if(data_type==null) return "string";
		data_type = Data_Type.trim().toLowerCase();
		Data_Type = ","+data_type +",";
		if(TYPE_NUMBER.indexOf(Data_Type)>-1) return "number";
		if(TYPE_DATE.indexOf(Data_Type)  >-1) return "date"  ;
		if(TYPE_STRING.indexOf(Data_Type)>-1) return "string";
		return data_type;
	}
	public static String getSqlValue(String f_name,String strType,String strValue,String f_logic) throws Exception{
		strType = getDataType(strType); 
		strType = strType.toLowerCase();
		if(strValue==null) return null;
		
		f_logic  = getSqlLogic(f_logic);
		
		if(!strType.equals("string")){
			strValue = strValue.trim();
			if(strValue.equals("")) return null;
		}
		
		String  strMsg    = null;
		boolean bIsStr    = true;
		try{
			if(strType.equals("date")){
				strValue = isDate(strValue,true);
				bIsStr   = false;
			}
			if(strType.equals("number")){
				strValue = isNumber(strValue);
				bIsStr   = false;
			}
		}catch(Exception e1){
			strMsg = "【"+f_name+"】"+ e1.getMessage();
			throw new Exception(strMsg);
		}
		boolean bLike = false;
		if(strType.equals("number")){
			if(strValue.equals("")) return null;
		}else{
			if(bIsStr) strValue = strValue.replaceAll("'","''");
			if(strType.equals("string") && f_logic.indexOf("like")>-1){
				bLike = true;
				if(f_logic.equals("like" )) strValue = "'%" + strValue + "%'";
				if(f_logic.equals("like%")) strValue = "'"  + strValue + "%'";
				if(f_logic.equals("%like")) strValue = "'%" + strValue + "'";
				if(f_logic.equals("%like%")) strValue = "'%" + strValue + "%'";
			}
			if(!bLike) strValue = "'"+strValue+"'";
		}
		if(strType.equals("date")) {
			strValue = "to_date( "+strValue + ") ";
		}
		return strValue;
	}
	public static String isNumber(String str) throws Exception{
		if(str==null || str.trim().equals("")) return "";
		int iLoopNum = 0;
		int iLoop    = 0;
		String ch    = "";
		String strRet= "";
		int    iDotNum = 0;
		int    iNotNum = 0;
		try{
			
			str  = str.trim();
			ch   = str.substring(0,1);
			if( ch.equals("+") || ch.equals("-")) str = str.substring(1);
			if( ch.equals("-")) strRet = "-";
			str      = str.trim();
			//不能为 + - 或者其后面一个多个空格, 如果用单引号括起来，在SQL Server中容许，但是ORACLE中不容许,例如'- ','+ '
			if(str.equals("") || str.equals(".")) return "";
			
			iLoopNum = str.length();
			for(iLoop=0;iLoop<iLoopNum;iLoop++){
				ch  = str.substring(iLoop,iLoop+1);
				if(ch.equals(".")) {
					strRet += ch;
					iDotNum++;
				}
				if("0123456789".indexOf(ch)<0) {
					iNotNum ++ ;
					continue;
				}
				strRet += ch;
			}
		}catch(Exception e){
			throw new Exception( e.getMessage());
		}
		if(iDotNum>1) throw new Exception("包含有一个以上的小数点");
		if(iNotNum>1) throw new Exception("包含"+iNotNum+"个非数字字符");
		if(strRet.equals(".") ||strRet.equals("-.")) strRet = "";
		return strRet;
	}
	public static String isDate(String str,boolean bFormat) throws Exception{
		String strDate = "";
		if(str==null || str.trim().equals("")) return strDate;
		int iPos = str.indexOf("-");
		//取得年
		if(iPos<0) throw new Exception("日期格式不对,请用【-】分隔符号");
		String yy = str.substring(0,iPos).trim();
		str = str.substring(iPos+1).trim();
		//取得月
		iPos = str.indexOf("-");
		if(iPos<0) throw new Exception("日期格式不对,请用【-】分隔符号");
		String mm = str.substring(0,iPos).trim();
		str = str.substring(iPos+1).trim();
		
		//取得月
		String dd  =str.trim();
		iPos = str.indexOf(" ");
		if(iPos<0){
			iPos = str.indexOf(":");
			if(iPos>-1) throw new Exception("日期格式不对,请用空格来分隔日期与时间");
			str = "";
		}else{
			dd  = str.substring(0,iPos).trim();
			str = str.substring(iPos+1).trim();	
		}
		//空值判断
		if(yy.equals("")) throw new Exception("【年】不能为空");
		if(mm.equals("")) throw new Exception("【月】不能为空");
		if(dd.equals("")) throw new Exception("【日】不能为空");

		String hh = "";
		String mi = "";
		String ss = "";
		if(!str.equals("")){
			iPos = str.indexOf(":");
			if(iPos>-1){
				hh  = str.substring(0,iPos).trim();
				str = str.substring(iPos+1).trim();
				iPos = str.indexOf(":");
				if(iPos>-1){
					mi  = str.substring(0,iPos).trim();
					ss  = str.substring(iPos+1).trim();
				}else{
					mi  =str ;
				}
			}else{
				hh = str;
			}
		}
		int iYY = 0;
		int iMM = 0;
		int iDD = 0;
		try{
			iYY = Integer.parseInt(yy);
		}catch(Exception e){
			throw new Exception("【年】含有非数字字符");
		}
		try{
			iMM = Integer.parseInt(mm);
		}catch(Exception e){
			throw new Exception("【月】含有非数字字符");
		}
		try{
			iDD = Integer.parseInt(dd);
		}catch(Exception e){
			throw new Exception("【日】含有非数字字符");
		}
		if(iMM<1 || iMM>12 ) throw new Exception("【月】必须在【1-12】,你的值为【"+iMM+"】");
		int iMax = 31;
		if(iMM==4 || iMM==6 || iMM==9 || iMM==11) iMax = 30;
		if(iMM==2) {
			iMax = 28;
			if(iYY %400== 0 || (iYY%4==0 && iYY %100 !=0 )) iMax = 29;
		}
		if(iDD<1 || iDD>iMax ) throw new Exception("【日】必须在【1-"+iMax+"】,你的值为【"+iDD+"】");
		strDate = iYY + "-" ;
		if(iMM<10) strDate += "0";
		strDate += iMM + "-" ;
		if(iDD<10) strDate += "0";
		strDate += iDD;
		
		//判断时间
		int iHH = -1;
		int iMI = -1;
		int iSS = -1;
		try{
			if(!hh.equals("")) iHH = Integer.parseInt(hh);
		}catch(Exception e){
			throw new Exception("【时】含有非数字字符");
		}
		try{
			if(!mi.equals("")) iMI = Integer.parseInt(mi);
		}catch(Exception e){
			throw new Exception("【分】含有非数字字符");
		}
		try{
			if(!ss.equals("")) iSS = Integer.parseInt(ss);
		}catch(Exception e){
			throw new Exception("【秒】含有非数字字符");
		}
		String strFormat = "yyyy-mm-dd";
		if(iHH>-1){
			if(iHH>23 ) throw new Exception("【时】必须在【0-23】,你的值为【"+iHH+"】");
			strDate += " ";
			if(iHH<10) strDate += "0";
			strDate += iHH;
			strFormat += " HH24";
			if(iMI>-1){
				if(iMI>59 ) throw new Exception("【分】必须在【0-59】,你的值为【"+iMI+"】");
				strDate += ":";
				if(iMI<10) strDate += "0";
				strDate += iMI;
				strFormat += ":MI";
				if(iSS>-1){
					if(iSS>59 ) throw new Exception("【秒】必须在【0-59】,你的值为【"+iSS+"】");
					strDate += ":";
					if(iSS<10) strDate += "0";
					strDate += iSS;
					strFormat += ":ss";
				}
			}
		}
		if(bFormat) strDate = strDate + "','" + strFormat ;
		return strDate;
	}

}