常用数据库转换语句

 以下是管理访问SQL函数，各数据库差异的函数 由于不同数据库其Sql函数不同，这里用函数封装了这种差异：

/** * instr字符替换脚本(列名)，暂时只支持iEnd为1的SQL Server脚本 * * @param sStr1 * oracle中为要搜索的表达式，SQL Server中为搜索表达式 * @param sStr2 * SQL Server中为要搜索的表达式，oracle中为搜索表达式 * @param iStart * 开始位置 * @param iEnd * 仅在Oracle中起作用 * @return */ public String sqlInstr(String sStr1, String sStr2, int iStart, int iEnd) { if (dbType == DB_ORA) return "instr(" + sStr1 + "," + sStr2 + "," + iStart + "," + iEnd + ")"; if (dbType == DB_SQL) { if (iStart < 0) { sStr1 = "REPLACE(REVERSE(" + sStr2 + "),REVERSE(" + sStr1 + ")," + sStr1 + ")"; } return "charindex(" + sStr2 + "," + sStr1 + " ,ABS(" + iStart + "))"; } if (dbType == DB_DB2) return "POSSTR(" + sStr1 + "," + sStr2 + ")";// db2不支持sStart return ""; }

 

 

	/**
	 * 把字符串转成日期格式 YYYY[token]MM[token]DD
	 * 
	 * @param _stringDate
	 * @return
	 */
	public String sqlFormatStringToDate(String _stringDate, String token) {
		String sql = "";
		if (dbType == DB_ORA) {
			sql += "TO_date('" + _stringDate + "','YYYY" + token + "MM" + token
					+ "DD')";
		} else if (dbType == DB_SQL) {
			sql += "convert(datetime,'" + _stringDate+"')";
		} else if (dbType == DB_DB2) {
			sql += "TO_date('" + _stringDate + "','YYYY" + token + "MM" + token
					+ "DD')";
		} else {
			// ERROR
		}
		return sql;
	}

	/**
	 * 日期函数转换（处理列名）
	 * 
	 * @param strColName
	 *            要转换的列名或日期类型数据
	 * @param sFormat
	 *            要转换的格式
	 * @param isColName
	 *            标识是否是列名
	 * @return 返回字符串类型
	 */
	public String sqlDateS(String strColName, String sFormat, boolean isColName) {
		if (isColName) {
			if (dbType == DB_ORA) {
				return "to_char(" + strColName + ",'" + sFormat + "')";
			} else if (dbType == DB_SQL)
				return "CONVERT(VARCHAR(" + sFormat.length() + "),"
						+ strColName + ",112)";
			else if (dbType == DB_DB2)
				return "cast(" + strColName + " as VARCHAR(10))";
		} else {
			return sqlDateS(strColName, sFormat);
		}

		return "";
	}

	/**
	 * 将字符列转为日期类型
	 * 
	 * @param strColName
	 *            要转换的列名或日期类型数据
	 * @param sFormat
	 *            要转换的格式
	 * @param isColName
	 *            标识是否是列名
	 * @return 返回字符串类型
	 */
	public String sqlSColToDate(String strColName, String sFormat) {
		if (dbType == DB_ORA) {
			return "to_date(" + strColName + ",'" + sFormat + "')";
		} else if (dbType == DB_SQL) {
			return "cast(" + strColName + " as DateTime)";
		} else if (dbType == DB_DB2) {
			return "cast(" + strColName + " as DateTime)";
		}

		return "";
	}

 

	/**
	 * 把字段或日期转成token格式
	 * 
	 * @param date
	 * @return
	 */
	public String sqlFormatDate(String date, String token) {
		String SQL = "";
		if (dbType == DB_ORA) {
			SQL += "TO_CHAR(" + date + ",'YYYY" + token
					+ "MM" + token + "DD')";
		} else if (dbType == DB_SQL) {
			if (token == "") {
				SQL += "convert(varchar(120),convert(datetime," + date
						+ ") ,112)";
			} else if (token == "/") {
				SQL += "convert(varchar(120),convert(datetime," + date
						+ ") ,111)";
			} else if (token == "-") {
				SQL += "convert(varchar(120),convert(datetime," + date
						+ ") ,120)";
			} else {
				SQL += "convert(varchar(120),convert(datetime," + date
						+ ") ,112)";
			}
		} else if (dbType == DB_DB2) {
			SQL += "TO_CHAR(" + date + ",'YYYY" + token
					+ "MM" + token + "DD')";
		} else {
			// ERROR
		}
		return SQL;
	}

	
	public String sqlFToDate (String date,String token)
	{
		String SQL = "";
		if (dbType == DB_ORA) {
			SQL += "to_date('" + date + "')";
					
		} else if (dbType == DB_SQL) {
			
				SQL += "convert(datetime,'" + date
						+ "')";
			
		} else if (dbType == DB_DB2) {
			SQL += "to_date('" + date + "')";
		} else {
			// ERROR
		}
		return SQL;

  

/*
	 * 处理自增列
	 */
	public String SQLSEQUENCE(String que) {
		if (dbType == DB_ORA)
			return que + ".nextval, ";
		else if (dbType == DB_SQL) {
			return "";
		} else if (dbType == DB_DB2)
			return "NEXTVAL FOR " + que + ",";
		return "";
	}

 

	/**
	 * 按日期返回星期几
	 * 
	 * @param date
	 * @return
	 * @throws YssException
	 */
	public String getWeek(String date) throws YssException {
		// date=sqlDateS(date,"yyyy-mm-dd");
		if (dbType == DB_ORA)
			return "to_char(" + date + ",'d')";
		else if (dbType == DB_SQL)
			return "DATEPART(wk," + date + ")";
		else if (dbType == DB_DB2)
			return "DAYOFWEEK(" + date + ")";
		else
			throw new YssException("数据库类型异常！");
	}

	/**
	 * 求指定日期在一年中是第几周
	 * 
	 * @param date
	 * @return
	 * @throws YssException
	 */
	public String getWeekth(String date) throws YssException {
		// date=sqlDateS(date,"yyyy-mm-dd");
		if (dbType == DB_ORA)
			return "to_char(" + date + ",'IW')";
		else if (dbType == DB_SQL) {
			return "DATEPART(week," + date + ")";
		} else if (dbType == DB_DB2)
			return "WEEK(" + date + ")";
		else
			throw new YssException("数据库类型异常！");
	}

	/**
	 * 日期在加减时不同数据库的处理
	 * 
	 * @return
	 * @throws YssException
	 */
	public String getDay() throws YssException {
		// date=sqlDateS(date,"yyyy-mm-dd");
		if (dbType == DB_ORA)
			return "+0";
		else if (dbType == DB_SQL)
			return "+0";
		else if (dbType == DB_DB2)
			return "day";
		else
			throw new YssException("数据库类型异常！");
	}

	/**
	 * 取日期的年份
	 * 
	 * @param date
	 * @return
	 * @throws YssException
	 */
	public String getYear(String date) throws YssException {
		// date=sqlDateS(date,"yyyy-mm-dd");

		if (dbType == DB_ORA) {
			Pattern p = Pattern.compile("^\\d{4}[-]{1}\\d{1,2}[-]{1}\\d{1,2}$");
			Matcher m = p.matcher(date);// 正则匹配
			if (m.matches())
				return "to_number(to_char(to_date('" + date
						+ "','yyyy-mm-dd'),'yyyy'))";
			else
				return "to_number(to_char(" + date + ",'yyyy'))";
		} else if (dbType == DB_SQL)
			return "DATENAME(year," + date + ")";
		else if (dbType == DB_DB2)
			return "YEAR(" + date + ")";
		else
			throw new YssException("数据库类型异常！");
	}

 

/** 取模 */
	public String sqlMod(String sStr1, String sStr2, boolean bAbs) {
		if (dbType == DB_ORA)
			return (bAbs ? "abs(" : "") + "mod(" + sStr1 + "," + sStr2 + ")"
					+ (bAbs ? ")" : "");

		if (dbType == DB_SQL)
			return (bAbs ? "abs(" : "") + "(" + sStr1 + ") % (" + sStr2 + ")"
					+ (bAbs ? ")" : "");

		return (bAbs ? "abs(" : "") + "mod(" + sStr1 + ", " + sStr2 + ")"
				+ (bAbs ? ")" : "");
	}

 

	/** sqlTrim */
	public String sqlTrim(String sStr) {

		if (dbType == DB_ORA)
			return "Trim(" + sStr + ")";
		else
			return "RTrim(LTrim(" + sStr + "))";
	}

	/**
	 * 参看Oracle的NVL(Sqlserver的isnull)函数
	 */
	public String sqlIsNull(String str1, String str2) {
		if (dbType == DB_ORA)
			return "NVL(" + str1 + "," + str2 + ")";

		if (dbType == DB_SQL)
			return "IsNull(" + str1 + "," + str2 + ")";

		return "(case when " + str1 + " is null then " + str2 + " else " + str1
				+ " end)";
	}

	public String sqlIsNull(String str1) {
		return sqlIsNull(str1, "0");
	}

	// 取整 (截取) 2008-8-2
	public String sqlInt(String str) {

		if (dbType == DB_SQL) {

			return "cast(" + str + " as int)";

		} else {

			return "trunc(" + str + ")";
		}
	}

	// cxd 20090108把字符转换成double
	public String sqlDouble(String str) {

		if (dbType == DB_SQL) {

			return "cast(" + str + " as numeric(18,4))";

		}
		if (dbType == DB_DB2) {

			return "cast(" + str + " as decimal(17,4))";
		} else
			return "trunc(" + str + ",4)";

	}

	/**
	 * '整除和取模,sstr1和sstr2是整数表达式
	 */
	public String sqlIntDiv(String sStr1, String sStr2, boolean bAbs) {
		if (dbType == DB_ORA)
			return "floor(abs("
					+ sStr1
					+ ")/abs("
					+ sStr2
					+ ")) "
					+ (bAbs ? ""
							: ("* sign(" + sStr1 + ") * sign(" + sStr2 + ")"));

		return (bAbs ? "abs(" : "") + "(" + sStr1 + ") / (" + sStr2 + ")"
				+ (bAbs ? ")" : "");
	}

	/**
	 * 取日期年、月、日
	 * 
	 * @param cloumnname
	 *            String
	 * @param i
	 *            int 取年份时i从1开始
	 * @param j
	 *            int
	 * @return String
	 */
	public String sqlSubstr(String cloumn_date, int i, int j) {
		String ss = "";
		if (dbType == DB_ORA)// ora 中取年份时可以从0-4 也可以从1-4
			ss = "subStr(to_char(" + cloumn_date + ",'yyyy-mm-dd')," + i + ","
					+ j + ")";
		else if (dbType == DB_DB2)// db2 中只能从1-4
			ss = "subStr(char(" + cloumn_date + ")," + i + "," + j + ")";
		else
			ss = "SUBSTRING(CONVERT(VARCHAR(10)," + cloumn_date + ",120)," + i
					+ "," + j + ")";
		return ss;
	}

 

/**
	 * 日期加减天数
	 * 
	 * @param sDate
	 *            String：日期，如果是字符串字段，则要调用函数转换成sql日期
	 * @param sDays
	 *            String：天数
	 * @return String
	 */
	public String sqlDateAdd(String sDate, String sDays) {
		if (dbType == DB_DB2) {
			return sDate + " " + sDays + " days ";
		} else if (dbType == DB_ORA) {
			return ("to_char(to_date('" + sDate + "','"
					+ YssCons.YSS_DATEFORMAT + "')" + sDays + ")");
			// return sDate + " " + sDays;
		} else {// DATEADD (day,-1,fdate)
			return ("DATEADD (day," + sDays + "," + sDate + ")");
		}

	}

	/**
	 * 日期相减
	 * 
	 * @param sDate1
	 *            String：日期1，如果是字符串字段，则要调用函数转换成sql日期
	 * @param sDate2
	 *            String：日期同上
	 * @return String：返回date2-date1的sql表达式
	 */
	public String sqlDateDiff(String sDate1, String sDate2) {
		if (dbType == DB_DB2)
			return "days(" + sDate1 + ")-days(" + sDate2 + ")";
		else if (dbType == DB_SQL) {
			return "DATEDIFF(day," + sDate1 + "," + sDate2 + ")";
		} else
			return sDate1 + "-" + sDate2;
	}