com.app.common.condition.buildcondition.OracleSqlBuildCondition Maven / Gradle / Ivy

Go to download
Show more of this group Show more artifacts with this name
Show all versions of com.app.common Show documentation
com.app.common
The newest version!
package com.app.common.condition.buildcondition;

import java.util.ArrayList;
import java.util.Hashtable;
import java.util.List;
import java.util.Map.Entry;

import com.app.common.condition.Condition;
import com.app.common.condition.ConditionInfo;
import com.app.common.condition.ConditionOperator;
import com.app.common.condition.IBuildCondition;
import com.app.common.condition.OrderInfo;

public class OracleSqlBuildCondition implements IBuildCondition {
	int inCount = 1000;
	private static String pageSqlTemplNoOrderBy = "select b.* from (select rownum rn, t.* from %s t where (1=1) %s  and rownum <=%s) b where b.rn> %s";
	private static String pageSqlTemplWithOrderBy = "select c.* from (select rownum rn,b.* from (select t.* from %s t where (1=1) %s %s) b where rownum <=%s ) c where   c.rn> %s";
	private static String normalSqlTempl = "select * from %s where (1=1) %s %s";

	@Override
	public Object Build(Condition condition) throws Exception {
		List queryInfos = condition.getConditionInfos();
		String sql = "";
		StringBuilder where = new StringBuilder();
		Hashtable> ht = new Hashtable>();
		for (ConditionInfo conditionInfo : queryInfos) {
			List lt = new ArrayList();
			if (!ht.containsKey(conditionInfo.getGroupName())) {
				ht.put(conditionInfo.getGroupName(), lt);
			} else {
				lt = ht.get(conditionInfo.getGroupName());
			}
			lt.add(conditionInfo);
		}
		int j = 0;
		for (Entry> entry : ht.entrySet()) {
			List lts = entry.getValue();
			if (j != 0) {
				where.append("or (");
			} else if (j == 0) {
				where.append("and (");
			}
			int i = 0;
			for (ConditionInfo conditionInfo : lts) {
				String conditionLogic = conditionInfo.getConditionLogic().toString();
				String conditionOperator = convertSqlOperator(conditionInfo.getConditionOperator());
				if (i == 0) {
					conditionLogic = "";
				}
				switch (conditionInfo.getConditionOperator()) {
				case Like:
				case NotLike:
					where.append(String.format(" %s %s %s '%s'", conditionLogic, conditionInfo.getFieldName(),
							conditionOperator, conditionInfo.getFieldValue()));
					break;
				case In:
				case NotIn:
					List lt = (List) conditionInfo.getFieldValue();
					if (lt != null && lt.size() > 0) {

						StringBuilder inSb2 = new StringBuilder();
						int count = lt.size() / inCount;
						int mCount = lt.size() % inCount;
						if (mCount != 0) {
							count = count + 1;
						}
						for (int m = 0; m < count;) {

							String in = "";
							StringBuilder inSb = new StringBuilder();
							for (int g = m * inCount, k = 0; g < lt.size() && k < inCount; g++, k++) {
								inSb.append("'").append(lt.get(g)).append("',");
							}
							in = inSb.substring(0, inSb.length() - 1);
							inSb2.append(String.format(" %s %s %s (%s)", "", conditionInfo.getFieldName(),
									conditionOperator, in));
							m++;
							if (m < count) {
								inSb2.append(" or ");
							}

						}
						where.append(String.format(" %s (%s)", conditionLogic, inSb2.toString()));
					}

					break;
				default:
					where.append(String.format(" %s %s %s %s", conditionLogic, conditionInfo.getFieldName(),
							conditionOperator, conditionInfo.isField() ? conditionInfo.getFieldValue()
									: "'" + conditionInfo.getFieldValue() + "'"));
					break;
				}
				i++;
			}

			j++;
			where.append(")");
		}
		String orders = "";
		StringBuilder orderSb = new StringBuilder();
		if (condition.getOrderInfos() != null && condition.getOrderInfos().size() > 0) {
			orderSb.append(" order by ");
			for (OrderInfo orderInfo : condition.getOrderInfos()) {
				orderSb.append(String.format("%s %s,", orderInfo.getFieldName(), orderInfo.getOrderType()));
			}
			orders = orderSb.substring(0, orderSb.length() - 1);
		}

		if (condition.isPage()) {
			if (orders.length() == 0) {
				sql = String.format(pageSqlTemplNoOrderBy, condition.getTableName(), where.toString(),
						condition.getPageIndex() * condition.getPageSize(),
						(condition.getPageIndex() - 1) * condition.getPageSize());
			} else {
				sql = String.format(pageSqlTemplWithOrderBy, condition.getTableName(), where.toString(), orders,
						condition.getPageIndex() * condition.getPageSize(),
						(condition.getPageIndex() - 1) * condition.getPageSize());
			}

		} else {
			sql = String.format(normalSqlTempl, condition.getTableName(), where.toString(), orders);

		}
		return sql;
	}

	private String convertSqlOperator(ConditionOperator conditonOperator) {
		String stringOperator = " = ";
		switch (conditonOperator) {
		case EqualTo:
			stringOperator = " = ";
			break;
		case NotEqualTo:
			stringOperator = " <> ";
			break;
		case LessThan:
			stringOperator = " < ";
			break;
		case LessThanOrEqualTo:
			stringOperator = " <= ";
			break;
		case Like:
			stringOperator = " Like ";
			break;
		case GreaterThan:
			stringOperator = " > ";
			break;
		case GreaterThanOrEqualTo:
			stringOperator = " >= ";
			break;
		case NotLike:
			stringOperator = " Not like ";
			break;
		case In:
			stringOperator = " In ";
			break;
		case NotIn:
			stringOperator = " Not In ";
			break;
		default:
			break;
		}

		return stringOperator;
	}

}