oraclequerybuilder.java

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

		// keycol IN {keyvalue1, keyvalue2, ... keyvalueN}
		// --if multiple columns in the key:
		// (keycol1 = keyvalue11 AND keycol2 = keyvalue12) OR (keycol1 = keyvalue21 AND keycol2 = keyvalue22) OR ...
		StringBuffer whereClause = new StringBuffer();
		whereClause.append(" WHERE ");
		if (keyIndex.length == 1) {
			//keycol IN {keyvalue1, keyvalue2, ... keyvalueN}
			whereClause.append(m_quoteChar + ti.getColumnName(keyIndex[0]) + m_quoteChar + " IN (");
			String attQuote = quoteVal[0];
			for (int k=0; k<keys.length; k++) {
				if (k>0) {
					whereClause.append(", ");
				}
				whereClause.append(attQuote + keys[k].getAttribute(0).toString() + attQuote);
			}
			whereClause.append(")");
		}
		else {
			// (keycol1 = keyval11 AND keycol2 = keyval12) OR (keycol1 = keyval21 AND keycol2 = keyval22) OR ...
			PrimaryKey pk;
			for (int j=0; j<keys.length; j++) {						//j iterates each PrimaryKey object
				pk = keys[j];
				if (j>0) {																	//insert OR between successive (...)'s
					whereClause.append(" OR ");
				}
				whereClause.append("(");
				for (int k=0; k<keyIndex.length; k++) {			//k iterates each column in the PrimaryKey
					if (k>0) {
						whereClause.append(" AND ");						//insert AND between successive name=value pairs
					}
					whereClause.append(m_quoteChar + ti.getColumnName(keyIndex[k]) + m_quoteChar + equalOp[k]);
					whereClause.append(quoteVal[k] +  pk.getAttribute(k).toString() + quoteVal[k]);
				}
				whereClause.append(")");
			}
		}
		//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 queryByPrimaryKey: " + newQuery);
		}
		return result;
	}

	/*
	 *Identify complete set of columns that the query must return. This is the union of the
	 *columns specifically reqested and the columns implied by the QueryParams. Side effect of
	 *this function is to build the SpatialQueryMetaData for the new query.
	 */
	private TreeSet findRequiredColumns(SpatialQueryDef queryDef, String[] columnNames, QueryParams queryParams) {
		//collect data needed to construct new SpatialQueryMetadata
		String geometryColumn = null;
		String xColumn = null;
		String yColumn = null;
		String rasterColumn = null;
		String renditionColumn = null;
		RenditionType renditionType;
		String labelRenditionColumn = null;
		RenditionType labelRenditionType;
		String labelGeometryColumn = null;
		//TreeSet eliminates duplicate column names
		TreeSet selectCols = new TreeSet();
		//add all columns initially identified as needed
		for (int k=0; columnNames!=null && k<columnNames.length; k++) {
			selectCols.add(columnNames[k]);
		}
		//add columns as indicated in QueryParams
		SpatialQueryMetaData metaData = queryDef.getSpatialQueryMetaData();

		if(queryParams.getGeometry()) {
			int spatialType = metaData.getSpatialDataType();
			switch (spatialType) {
				case SpatialQueryMetaData.XYCOORDS:
					xColumn = metaData.getXColumn();
					yColumn = metaData.getYColumn();
					selectCols.add(xColumn);
					selectCols.add(yColumn);
					break;
				case SpatialQueryMetaData.GEOMETRYOBJECT:
					geometryColumn = metaData.getGeometryColumn();
					selectCols.add(geometryColumn);
					break;
				case SpatialQueryMetaData.NONE:
				default:
					//do nothing, ok to have no geometry
					break;
			}
		}

		//rendition column name may be null
		if(queryParams.getRendition() && metaData.getRenditionColumn() != null) {
			renditionColumn = metaData.getRenditionColumn();
			selectCols.add(renditionColumn);
		}
		renditionType = renditionColumn == null ? RenditionType.none : metaData.getRendType();

		//label rendition column name may be null
		if(queryParams.getLabelRendition() && metaData.getLabelRenditionColumn() != null) {
			labelRenditionColumn = metaData.getLabelRenditionColumn();
			selectCols.add(labelRenditionColumn);
		}
		labelRenditionType = labelRenditionColumn == null ? RenditionType.none : metaData.getLabelRendType();

		//primary key columns can not be null
		if(queryParams.getPrimaryKey()) {
			String[] keyCols = metaData.getPrimaryKeyColumns();
			for (int k=0; k<keyCols.length; k++) {
				selectCols.add(keyCols[k]);
			}
		}

		//label gemoetry column name may be null
		if(queryParams.getLabelPoint() && metaData.getLabelPointGeometryColumn() != null) {
			labelGeometryColumn = metaData.getLabelPointGeometryColumn();
			selectCols.add(labelGeometryColumn);
		}

		//raster column name may be null
		if(queryParams.getRaster() && metaData.getRasterColumn() != null) {
			rasterColumn = metaData.getRasterColumn();
			selectCols.add(rasterColumn);
		}

		if (xColumn != null) {
			m_outMetaData = new SpatialQueryMetaData(xColumn, yColumn, metaData.getPrimaryKeyColumns(), rasterColumn,
			                                       renditionColumn, renditionType,
																						 labelRenditionColumn, labelRenditionType,
																						 metaData.getCoordSys());
		}
		else
		if (geometryColumn != null) {
			m_outMetaData = new SpatialQueryMetaData(geometryColumn, metaData.getPrimaryKeyColumns(), rasterColumn,
			                                       renditionColumn, renditionType,
																						 labelRenditionColumn, labelRenditionType,
																						 metaData.getCoordSys(),
			                                       metaData.getDimension(), labelGeometryColumn);
		}
		else {
			m_outMetaData = new SpatialQueryMetaData(SpatialQueryMetaData.NONE, xColumn, yColumn, geometryColumn,
			                                       metaData.getPrimaryKeyColumns(), rasterColumn,
			                                       renditionColumn, renditionType,
																						 labelRenditionColumn, labelRenditionType,
																						 metaData.getCoordSys(),
			                                       metaData.getDimension(), labelGeometryColumn);
		}
	  return selectCols;
	}

	/*
	 * use list of all required columns to create outer select clause
	 */
	private String buildSelectClause(TreeSet selectCols) {
		StringBuffer selectClause = new StringBuffer();
		Iterator iter = selectCols.iterator();
		int size = selectCols.size();
		String col;
		for (int k=0; k<size; k++) {
			col = m_quoteChar + (String)iter.next() + m_quoteChar;
			if (k==0) {
				selectClause.append("SELECT " + col);
			}
			else {
				selectClause.append(", " + col);
			}
		}
		return selectClause.toString();
	}

	private boolean isOracle8() {
		boolean ret = false;
		if (m_dbVersion == null || m_dbVersion.startsWith("8.")) {
			ret = true;
		}
		return ret;
	}

/**
	 * Get search rectangle for Oracle 9.x database after doing necessary workarounds for Oracle bugs.
	 * <p>
	 * fix 1: bounds for geodetic coordsys must be less than the maximum extent for each x,y value.
	 * If too big, reduce bounds to intersection of input bounds and maximum bounds
	 * <p>
	 * fix 2: Adds VIEWPORT_TRANSFORM to all rectangles to compensate for Oracle not allowing
	 * a search rect for geodetic coordsys to be larger that 1/2 the area of the earth.
	 * Apply transform for all coordsys regardless as it is a no-op in Oracle for non-geodetic.
	 *
	 * @param csys CoordSys of the data in the database table
	 * @param dbRect search rectangle in the database table coordinate system (csys)
	 */
	private String getSearchRect9(CoordSys csys, DoubleRect dbRect)
	throws Exception {
		DoubleRect fixRect = dbRect;
		//fix 1
		if ((OracleSRID.isGeodetic(csys) == true) &&
		    (GEXTENTS.contains(dbRect) == false)) {
			fixRect = GEXTENTS.intersection(dbRect);
			if (fixRect.isEmpty() == true) {
				throw new Exception("Invalid search rectangle (" + dbRect.xmin + ',' + dbRect.ymin + ',' +
				                     dbRect.xmax + ',' + dbRect.ymax + ") for Oracle geodetic coordinate system");
			}
		}
		//fix 2
		StringBuffer queryBuffer = new StringBuffer();
		queryBuffer.append("SDO_CS.VIEWPORT_TRANSFORM(");
		queryBuffer.append("MDSYS.SDO_GEOMETRY(2003,0,null,");  //SRID must be 0 when use VIEWPORT_TRANSFORM
		queryBuffer.append("MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),");
		queryBuffer.append("MDSYS.SDO_ORDINATE_ARRAY(");
		queryBuffer.append(fixRect.xmin).append(",").append(fixRect.ymin).append(",");
		queryBuffer.append(fixRect.xmax).append(",").append(fixRect.ymax).append(")");
		queryBuffer.append("),");
		queryBuffer.append(OracleSRID.getSRIDFromCS(csys));
		queryBuffer.append(")");
		return queryBuffer.toString();
	}

	/**
	 * Get search rectangle for Oracle database where
	 *     8.1.6 <= database < 9.x
	 * Oracle 8.1.5 is no longer suppoerted.
	 *
	 * @param csys CoordSys of the data in the database table
	 * @param dbRect search rectangle in the DB table coordinate system
	 */
	private String getSearchRect8(CoordSys csys, DoubleRect dbRect)
	throws Exception {
		double points[] = new double[4];
		dbRect.get(points, 2);
		int srid = OracleSRID.getSRIDFromCS(csys);   //returns 0 if not found

		StringBuffer queryBuffer = new StringBuffer();
		queryBuffer.append("MDSYS.SDO_GEOMETRY(2003,");
    if (srid != 0) {
      queryBuffer.append(srid);
    }
    else {
      queryBuffer.append("null");         //might work?
    }
    queryBuffer.append(",null,");
		queryBuffer.append("MDSYS.SDO_ELEM_INFO_ARRAY(1,");
    queryBuffer.append("1003");
    queryBuffer.append(",3");
		queryBuffer.append("),");
		queryBuffer.append("MDSYS.SDO_ORDINATE_ARRAY(");
		queryBuffer.append(points[0]).append(",").append(points[1]).append(",");
		queryBuffer.append(points[2]).append(",").append(points[3]).append(")");
		queryBuffer.append(")");
		return queryBuffer.toString();
	}

	/**
	 *  Get string for proper Oracle filter based
	 *  on the search type. Also remember the optional mask
	 *  to be put at end of query.
	 */
	private String getSearchMethod(QueryParams qp)
		throws Exception	{
		StringBuffer buf = new StringBuffer();
		SearchType st = qp.getSearchType();
		if (st == SearchType.mbr) {
			buf.append("(MDSYS.SDO_FILTER(");
			m_mask = null;
		}
		else
		if (st == SearchType.partial) {
			buf.append("(MDSYS.SDO_RELATE(");
			m_mask = "ANYINTERACT";
		}
		else
		if (st == SearchType.entire) {
			buf.append("(MDSYS.SDO_RELATE(");
			m_mask = "INSIDE+COVEREDBY";
		}
		else {
			throw new Exception("Invalid search type: " + st.getValue());
		}
		return buf.toString();
	}

}