oraclequery.java

EasyDBO是一个超轻量级对象-关系映射（Object/Relation Mapping

package com.easyjf.dbo.sql;

import java.util.*;

import com.easyjf.dbo.*;

/**
 * 
 * <p>
 * Title: ORACLE 方言实现
 * </p>
 * 
 * <p>
 * Description:
 * </p>
 * 
 * <p>
 * Copyright: Copyright (c) 2006
 * </p>
 * 
 * <p>
 * Company: EasyJF开源团队-EasyDBO项目组
 * </p>
 * 
 * @author piginzoo、大峡、clyyu
 * @version 1.0
 */
public class OracleQuery extends AbstractSqlQuery {
	
	/**
	 * 
	 * 获取分页SQL
	 * 
	 * @param sql
	 *            String SQL语句
	 * @param number
	 *            int
	 * @return String
	 */

	public String getTopSql(String sql, int number) {
		// 这里只能处理简单的SQL不能支持复杂的SQL,例如
		/*
		 * SELECT FROM (SELECT A FROM B GROUBY BY ) C,D WHERE 等类似的嵌套SQL
		 */
		StringBuffer s = new StringBuffer();
		if (sql == null) {
			return "";
		}
		// s.append(sql);
		String s1 = sql.toLowerCase();
		int li_order = 0;
		int li_group = 0;
		li_order = sql.toLowerCase().indexOf("order by");
		li_group = sql.toLowerCase().indexOf("group by");
		if (li_order == li_group) {
			// 没有order by 或者 group by 的语句
			// s.append(sql);
			if (sql.toLowerCase().indexOf("where ") < 0) {
				s.append(sql + " where ROWNUM<" + number);
			} else {
				s.append(sql + " and ROWNUM<" + number);
			}
		}
		// 第二种情况(也只有这一张情况发生 group by 一定在order by 前
		if (li_group > 0) {
			s1 = s1.substring(0, li_group);

			if (sql.toLowerCase().indexOf("where ") < 0) {
				s.append(s1 + " where ROWNUM<" + number);
			} else {
				s.append(s1 + " and ROWNUM<" + number);
			}
		}
		if (li_group < 0 && li_order > 0) {

			s1 = s1.substring(0, li_order);

			if (sql.toLowerCase().indexOf("where ") < 0) {
				s.append(s1 + " where ROWNUM<" + number + " "
						+ sql.substring(li_order));
			} else {
				s.append(s1 + " and ROWNUM<" + number + " "
						+ sql.substring(li_order));
			}
			//System.out.println(sql.substring(li_order));
		}

		return s.toString();
	}

	/**
	 * 
	 * 获取分页SQL，快速查询支持
	 * 
	 * @param sql
	 *            String SQL语句
	 * @param first
	 *            int 开始行
	 * @param max
	 *            int 页面显示行数
	 * @return String
	 */
	public String getTopSql(String sql, int first, int max) {
		int number = first + max;

		StringBuffer s = new StringBuffer();
		if (sql == null) {
			return "";
		}
		if (sql.toLowerCase().indexOf("rownum rowseq ") < 0) {
			s.append(" select * from( select rownum rowseq,x.* from ( ");
			s.append(sql);
			s.append(") x) where rowseq between " + first + " and " + number);
		} else {
			s.append(sql);
		}
		return s.toString();
	}

}