oraclequerybuilder.java

实现网络GPS定位车辆的位置

		//add querytype and mask
		newQuery.append("'QUERYTYPE=WINDOW");
		if (m_mask != null) {
			newQuery.append(" MASK=" + m_mask);
		}
		newQuery.append("')='TRUE')");

		SpatialQueryDef result = new SpatialQueryDef(newQuery.toString(), m_outMetaData);
	  if(m_bVerbose) {
			System.out.println("QueryBuilder new queryInRegion: " + newQuery);
		}
		return result;
	}

	/**
	 * Construct a query string to be used when executing a query in radius operation.
	 *
	 * Approximate the circle as a polygon and use the queryInRegion method to construct the query.
	 * Note the special case addressed below when converting the radius value to its
	 * LinearUnit equivalent. Geodetic coordinate systems use AngularUnits but the
	 * method GeometryUtils.createCircularRegion() needs LinearUnits, so a conversion was necessary.
	 *
	 * @return The SpatialQueryDef defining the new query and its metadata
	 */
	public SpatialQueryDef queryInRadius(MapJ mapj, Layer layer, SpatialQueryDef queryDef, String[] columnNames,
																	QueryParams queryParams, DoublePoint point, double radius)
																	throws Exception {

		CoordSys csys = queryDef.getSpatialQueryMetaData().getCoordSys();
		LinearUnit linearUnit = csys.getLinearUnit();
		LinearUnit unit = linearUnit;
		//convert radius to LinearUnits if necessary
		if (linearUnit == null) {
			AngularUnit angularUnit = csys.getAngularUnit();
			unit = LinearUnit.mile;
			radius = UnitUtil.convert(radius, angularUnit, LinearUnit.mile);
		}
		//get region that approximates a circle
		VectorGeometry region = GeometryUtils.createCircularRegion(point, csys, radius, unit, 0);
		return queryInRegion(mapj, layer, queryDef, columnNames, queryParams, region);
	}

	/**
	 * Construct a query string to be used when executing a query at point.
	 *
	 * @return The SpatialQueryDef defining the new query and its metadata
	 */
	public SpatialQueryDef queryAtPoint(MapJ mapj, Layer layer, SpatialQueryDef queryDef, String[] columnNames,
																	QueryParams queryParams, DoublePoint point)
																	throws Exception {
	  //build SELECT clause
		TreeSet selectCols = findRequiredColumns(queryDef, columnNames, queryParams);
		String selectClause = buildSelectClause(selectCols);
		int id = OracleSRID.getSRIDFromCS(queryDef.getSpatialQueryMetaData().getCoordSys());
		String srid = String.valueOf(id);
		//build WHERE clause
		String spatialColumn = queryDef.getSpatialQueryMetaData().getGeometryColumn();
		StringBuffer whereClause = new StringBuffer();
		whereClause.append("WHERE MDSYS.SDO_RELATE(" + spatialColumn + ", MDSYS.SDO_GEOMETRY(1, " + srid + ", MDSYS.SDO_POINT_TYPE(0, 0, NULL), MDSYS.SDO_ELEM_INFO_ARRAY(1, 1, 1), MDSYS.SDO_ORDINATE_ARRAY(" + point.x + ", " + point.y + ")), 'mask=ANYINTERACT querytype=WINDOW') = 'TRUE'");
		//construct final query
		StringBuffer newQuery  = new StringBuffer();
		newQuery.append(selectClause + " FROM (" + queryDef.getQuery() + ") " + whereClause);
		SpatialQueryDef result = new SpatialQueryDef(newQuery.toString(), m_outMetaData);
	  if(m_bVerbose) {
			System.out.println("QueryBuilder new queryAtPoint: " + newQuery);
		}
		return result;
	}


	/**
	 * Construct a query string to be used when executing a query all operation.
	 *
	 * @return The SpatialQueryDef defining the new query and its metadata
	 */
	public SpatialQueryDef queryAll(MapJ mapj, Layer layer, SpatialQueryDef queryDef, String[] columnNames,
																	QueryParams queryParams)
																	throws Exception {
	  //build SELECT clause
		TreeSet selectCols = findRequiredColumns(queryDef, columnNames, queryParams);
		String selectClause = buildSelectClause(selectCols);
		//construct final query
		StringBuffer newQuery  = new StringBuffer();
		newQuery.append(selectClause + " FROM (" + queryDef.getQuery() + ")");
		SpatialQueryDef result = new SpatialQueryDef(newQuery.toString(), m_outMetaData);
	  if(m_bVerbose) {
			System.out.println("QueryBuilder new queryAll: " + newQuery);
		}
		return result;
	}

	/**
	 * Construct a query string to be used when executing a query by attribute.
	 *
	 * @return The SpatialQueryDef defining the new query and its metadata
	 */
	public SpatialQueryDef queryByAttribute(MapJ mapj, Layer layer, SpatialQueryDef queryDef, String[] columnNames,
																	QueryParams queryParams, String searchColumn, Attribute attribute)
																	throws Exception {
	  //build SELECT clause
		TreeSet selectCols = findRequiredColumns(queryDef, columnNames, queryParams);
		String selectClause = buildSelectClause(selectCols);
		//build WHERE clause, has form "WHERE searchColumn = attributeValue"
		StringBuffer whereClause = new StringBuffer();
		whereClause.append(" WHERE " + m_quoteChar + searchColumn + m_quoteChar + " = ");
		// if attribute type is STRING or DATE its value must be quoted with '
		if (attribute.getType() == Attribute.STRING ||
				attribute.getType() == Attribute.DATE) {
			whereClause.append("\'" + attribute.getString() + "\'");
		} else {
			whereClause.append(attribute.getString());
		}
		//construct final query
		StringBuffer newQuery  = new StringBuffer();
		newQuery.append(selectClause + " FROM (" + queryDef.getQuery() + ") " + whereClause);
		SpatialQueryDef result = new SpatialQueryDef(newQuery.toString(), m_outMetaData);
	  if(m_bVerbose) {
			System.out.println("QueryBuilder new queryByAttribute: " + newQuery);
		}
		return result;
	}

	/**
	 * Construct a query string to be used when executing a query by attributes.
	 *
	 * @return The SpatialQueryDef defining the new query and its metadata
	 */
	public SpatialQueryDef queryByAttributes(MapJ mapj, Layer layer, SpatialQueryDef queryDef, List columnNames,
													QueryParams queryParams, List attNames, List attOperators, List attValues)
	throws Exception {
		TableInfo ti = layer.getTableInfo();
		//build SELECT clause
		TreeSet selectCols = findRequiredColumns(queryDef, (String[])columnNames.toArray(), queryParams);
		String selectClause = buildSelectClause(selectCols);
		//build WHERE clause
		StringBuffer whereBuffer = new StringBuffer(" WHERE ");

		//find quote char for value on rhs of operator
		List valueQuotes = new ArrayList(attNames.size());
		String name;
		int type;
		Iterator iter = attNames.iterator();
		while (iter.hasNext()) {
			name = (String)iter.next();
			type = ti.getColumnType(ti.getColumnIndex(name));
			if (type == TableInfo.COLUMN_TYPE_STRING ||
			    type == TableInfo.COLUMN_TYPE_DATE) {
				valueQuotes.add("'");
			}
			else {
				valueQuotes.add("");
			}
		}

		//build where statement, must consider NULL attribute values
		if (attNames.size() == 1) {
			//only support equality operator for now
			AttOperator op = (AttOperator)attOperators.get(0);
			if (op != AttOperator.eq) {
				throw new UnsupportedOperationException("Operator: " + op.getOperator() +
				             " not supported for Attribute searches");
			}
			boolean bHaveNull = false;
			int numNotNull = 0;          //to know when to put in ','
			Attribute att;
			StringBuffer inClause = new StringBuffer();
			inClause.append(m_quoteChar + attNames.get(0) + m_quoteChar + " IN (");
			//keycol IN (keyvalue1, keyvalue2, ... keyvalueN) OR keycol IS NULL
			//iterate attValues, add non-null to IN clause, remember if got NULL
			iter = attValues.iterator();
			while (iter.hasNext()) {
				att = ((AttTuple)iter.next()).get(0);       //tuple has only one value
				if (att.isNull() == true) {
					bHaveNull = true;
				}
				else {
					//have non null value, put it in the IN expression
					if (numNotNull > 0) {
						inClause.append(", ");
					}
					inClause.append(valueQuotes.get(0) + att.getString() + valueQuotes.get(0));
					numNotNull++;
				}
			}

			//deal with 4 different cases
			if (numNotNull > 0  && bHaveNull == false) {   //x in (...)
				inClause.append(")");
				whereBuffer.append(inClause);
			}
			else
			if (numNotNull > 0  && bHaveNull == true) {   //x in (...) or x is null
				inClause.append(")");
				whereBuffer.append(inClause);
				whereBuffer.append(" OR ");
				whereBuffer.append(m_quoteChar + attNames.get(0) + m_quoteChar).append(" IS NULL");
			}
			else
			if (numNotNull == 0 && bHaveNull == false) {
				//degenerate case, no attribute values, leave whereBuffer empty
			}
			else
			if (numNotNull == 0 && bHaveNull == true) {    //x is null
				//discard IN clause, only NULL attribute value(s) that collapse to single IS NULL
				whereBuffer.append(m_quoteChar + attNames.get(0) + m_quoteChar).append(" IS NULL");
			}
		}
		else {
			// (att1 = val11 AND att2 = val12) OR (tt1 = val21 AND att2 = val22) OR ...
			AttTuple tuple;
			for (int j=0; j<attValues.size(); j++) {			//j iterates each AttTuple object in attValues
				tuple = (AttTuple)attValues.get(j);
				if (j>0) {																	//insert OR between successive (...)'s
					whereBuffer.append(" OR ");
				}
				whereBuffer.append("(");
				for (int k=0; k<attNames.size(); k++) {			//k iterates each column in attNames
					if (k>0) {
						whereBuffer.append(" AND ");						//insert AND between successive name=value pairs
					}
					whereBuffer.append(m_quoteChar).append((String)attNames.get(k)).append(m_quoteChar);
					if (tuple.get(k).isNull() == true) {
						whereBuffer.append(" IS NULL");
					}
					else {
						whereBuffer.append(" = ");
						whereBuffer.append((String)valueQuotes.get(k) +  tuple.get(k).getString() + (String)valueQuotes.get(k));
					}
				}
				whereBuffer.append(")");
			}
		}
		//construct final query
		StringBuffer newQuery  = new StringBuffer();
		newQuery.append(selectClause + " FROM (" + queryDef.getQuery() + ") " + whereBuffer);
		SpatialQueryDef result = new SpatialQueryDef(newQuery.toString(), m_outMetaData);
	  if(m_bVerbose) {
			System.out.println("QueryBuilder new queryByAttributes: " + newQuery);
		}
		return result;
	}

	/**
	 * Construct a query string to be used when executing a query by primary key.
	 *
	 * @return The SpatialQueryDef defining the new query and its metadata
	 */
	public SpatialQueryDef queryByPrimaryKey(MapJ mapj, Layer layer, SpatialQueryDef queryDef, String[] columnNames,
																	QueryParams queryParams, String[] idColumns, PrimaryKey[] keys)
																	throws Exception {
	  //build SELECT clause
		TreeSet selectCols = findRequiredColumns(queryDef, columnNames, queryParams);
		String selectClause = buildSelectClause(selectCols);
		//from the type of the data in each key column, determine its equality operator and if it must be quoted
		TableInfo ti = layer.getTableInfo();
		int[] keyIndex = ti.getPrimaryKeyInfo();          //column index of key column
		String[] equalOp = new String[keyIndex.length];
		String[] quoteVal = new String[keyIndex.length];
		int attType;
		for (int j=0; j<keyIndex.length; j++) {
			attType = ti.getColumnType(keyIndex[j]);
			switch (attType) {
				case TableInfo.COLUMN_TYPE_FLOAT:
				case TableInfo.COLUMN_TYPE_DOUBLE:
				case TableInfo.COLUMN_TYPE_DECIMAL:
				case TableInfo.COLUMN_TYPE_BYTE:
				case TableInfo.COLUMN_TYPE_SHORT:
				case TableInfo.COLUMN_TYPE_INT:
				case TableInfo.COLUMN_TYPE_BOOLEAN:
					equalOp[j] = " = ";
					quoteVal[j] = "";
					break;
				case TableInfo.COLUMN_TYPE_STRING:
				case TableInfo.COLUMN_TYPE_DATE:
					equalOp[j] = " = ";
					quoteVal[j] = "'";
					break;
				default:
					throw new Exception("Unknown data type: " + String.valueOf(attType) + ", in primary key definition column: " + String.valueOf(j));
			}
		}
		// Build WHERE clause, has form based on number of columns in primary key:
		// --if a single column in the key: