001: /*
002: * Geotools2 - OpenSource mapping toolkit
003: * http://geotools.org
004: * (C) 2002, Geotools Project Managment Committee (PMC)
005: *
006: * This library is free software; you can redistribute it and/or
007: * modify it under the terms of the GNU Lesser General Public
008: * License as published by the Free Software Foundation;
009: * version 2.1 of the License.
010: *
011: * This library is distributed in the hope that it will be useful,
012: * but WITHOUT ANY WARRANTY; without even the implied warranty of
013: * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
014: * Lesser General Public License for more details.
015: *
016: */
017: package org.geotools.data.oracle;
018:
019: import java.lang.reflect.Field;
020: import java.math.BigDecimal;
021: import java.sql.Types;
022: import java.util.HashMap;
023: import java.util.Iterator;
024: import java.util.Map;
025: import java.util.logging.Logger;
026:
027: import org.geotools.feature.AttributeType;
028: import org.geotools.feature.FeatureType;
029: import org.geotools.filter.Filter;
030: import org.geotools.filter.SQLEncoder;
031: import org.geotools.filter.SQLEncoderException;
032:
033: import com.vividsolutions.jts.geom.Envelope;
034: import com.vividsolutions.jts.geom.Geometry;
035:
036: /**
037: * Provides SQL encoding functions for the Oracle Datasource
038: *
039: * @author Sean Geoghegan, Defence Science and Technology Organisation
040: * @author $Author: seangeo $
041: * @source $URL: http://svn.geotools.org/geotools/tags/2.4.1/modules/unsupported/oracle-spatial/src/main/java/org/geotools/data/oracle/SqlStatementEncoder.java $
042: * @version $Id: SqlStatementEncoder.java 27862 2007-11-12 19:51:19Z desruisseaux $ Last Modified: $Date: 2003/11/05 00:53:37 $
043: */
044: final class SqlStatementEncoder {
045: /** A logger for logging */
046: private static final Logger LOGGER = org.geotools.util.logging.Logging
047: .getLogger("org.geotools.data.oracle");
048: /** SQL Where clause encoder */
049: private SQLEncoder whereEncoder;
050: /** FID column of the table */
051: private String fidColumn;
052: /** Name of the table */
053: private String tableName;
054:
055: // Copied from JDBCDataStore
056: protected static final TypeMapping TYPE_MAPPINGS = new TypeMapping();
057: static {
058: TYPE_MAPPINGS.put("CHAR", String.class);
059: TYPE_MAPPINGS.put("LONGVARCHAR", String.class);
060: TYPE_MAPPINGS.put("VARCHAR", String.class);
061:
062: TYPE_MAPPINGS.put("BIT", Boolean.class);
063: TYPE_MAPPINGS.put("BOOLEAN", Boolean.class);
064:
065: TYPE_MAPPINGS.put("TINYINT", Short.class);
066: TYPE_MAPPINGS.put("SMALLINT", Short.class);
067:
068: TYPE_MAPPINGS.put("INTEGER", Integer.class);
069: TYPE_MAPPINGS.put("BIGINT", Long.class);
070:
071: TYPE_MAPPINGS.put("REAL", Float.class);
072: TYPE_MAPPINGS.put("FLOAT", Double.class);
073: TYPE_MAPPINGS.put("DOUBLE", Double.class);
074:
075: TYPE_MAPPINGS.put("DECIMAL", BigDecimal.class);
076: TYPE_MAPPINGS.put("NUMERIC", BigDecimal.class);
077:
078: TYPE_MAPPINGS.put("DATE", java.sql.Date.class);
079: TYPE_MAPPINGS.put("TIME", java.sql.Time.class);
080: TYPE_MAPPINGS.put("TIMESTAMP", java.sql.Timestamp.class);
081: TYPE_MAPPINGS.put("MDSYS.SDO_GEOMETRY", Geometry.class);
082: }
083:
084: static class TypeMapping {
085: Map intMap = new HashMap();
086: Map sqlMap = new HashMap();
087: Map typeMap = new HashMap();
088:
089: public void put(String name, Class javaType) {
090: if (name.indexOf(".") == -1 && name.indexOf("_") == -1) {
091: try {
092: Field field = Types.class.getField(name);
093: Integer integer = (Integer) field.get(null);
094: intMap.put(integer, javaType);
095: } catch (Exception e) {
096: e.printStackTrace();
097: }
098: }
099: sqlMap.put(name, javaType);
100: typeMap.put(javaType, name);
101: }
102:
103: public String getName(Class type) {
104: for (Iterator i = typeMap.keySet().iterator(); i.hasNext();) {
105: Class t = (Class) i.next();
106: if (t == type) {
107: return (String) typeMap.get(t);
108: }
109: }
110: // okay now consider inheritance
111: for (Iterator i = typeMap.keySet().iterator(); i.hasNext();) {
112: Class t = (Class) i.next();
113: if (t.isAssignableFrom(type)) {
114: return (String) typeMap.get(t);
115: }
116: }
117: return "NIL";
118: }
119: };
120:
121: /**
122: * Creates a new SQL Statement encoder.
123: *
124: * @param whereEncoder This in the encoder used for where clauses.
125: * @param tablename This the table name to use in SQL statements.
126: * @param fidColumn The fid column for the table.
127: */
128: SqlStatementEncoder(SQLEncoder whereEncoder, String tablename,
129: String fidColumn) {
130: this .whereEncoder = whereEncoder;
131: this .tableName = tablename;
132: this .fidColumn = fidColumn;
133: }
134:
135: /**
136: * Creates a table for the provided schema.
137: * <p>
138: * CREATE TABLE tableName (fidColumn int, att1 type1, att2 type2, .... )
139: * </p>
140: * <p>
141: * You should consider calling makeCreateIndexSQL to set up for fid based
142: * indexing, and we should have something for spatial indexes.
143: * </p>
144: *
145: * @param schema
146: * @return SQL used to create the table
147: */
148: String makeCreateTableSQL(FeatureType schema) {
149: StringBuffer sql = new StringBuffer("CREATE TABLE ");
150: sql.append(tableName);
151: sql.append("(");
152: sql.append(fidColumn);
153: sql.append(" NUMBER,");
154:
155: AttributeType[] attributeTypes = schema.getAttributeTypes();
156:
157: for (int i = 0; i < attributeTypes.length; i++) {
158: sql.append(attributeTypes[i].getName());
159: sql.append(" ");
160: sql.append(makeType(attributeTypes[i].getType()));
161: if (i < (attributeTypes.length - 1)) {
162: sql.append(",");
163: } else {
164: sql.append(")");
165: }
166: }
167:
168: return sql.toString();
169: }
170:
171: public String makeCreateFidIndex() {
172: StringBuffer sql = new StringBuffer();
173:
174: // FID INDEX!
175: sql.append("CREATE UNIQUE INDEX ");
176: sql.append(tableName);
177: sql.append("_index ON (");
178: sql.append(fidColumn);
179: sql.append(" )");
180:
181: return sql.toString();
182: }
183:
184: public String makeCreateGeomIndex(FeatureType schema) {
185: StringBuffer sql = new StringBuffer();
186:
187: // SPATIAL INDEX (On default geometry)
188: String defaultGeometry = schema.getDefaultGeometry().getName();
189: sql.append("CREATE INDEX ");
190: sql.append(tableName);
191: sql.append("_sidx ON ");
192: sql.append(tableName);
193: sql.append("(");
194: sql.append(defaultGeometry);
195: sql.append(") INDEXTYPE IS mdsys.spatial_index");
196:
197: return sql.toString();
198: }
199:
200: /** Map from Java type space to Oracle typespace - for use by createTableSQL */
201: String makeType(Class type) {
202: return (String) TYPE_MAPPINGS.getName(type);
203: }
204:
205: /**
206: * Constructs an Insert SQL statement template for this feature type.
207: *
208: * @param featureType The feature type to construct the statement for.
209: *
210: * @return The SQL insert template. The FID column will always be first, followed by each
211: * feature attribute. The VALUES section will contain ?'s for each attribute of the
212: * feature type.
213: */
214: String makeInsertSQL(FeatureType featureType) {
215: StringBuffer sql = new StringBuffer("INSERT INTO ");
216:
217: sql.append(tableName);
218: sql.append("(");
219: sql.append(fidColumn);
220: sql.append(",");
221:
222: AttributeType[] attributeTypes = featureType
223: .getAttributeTypes();
224:
225: for (int i = 0; i < attributeTypes.length; i++) {
226: sql.append(attributeTypes[i].getName());
227: if (i < (attributeTypes.length - 1)) {
228: sql.append(",");
229: } else {
230: sql.append(")");
231: }
232: }
233:
234: sql.append(" VALUES (?,"); // fid column
235:
236: for (int i = 0; i < attributeTypes.length; i++) {
237: sql.append("?");
238: if (i < (attributeTypes.length - 1)) {
239: sql.append(",");
240: } else {
241: sql.append(")");
242: }
243: }
244:
245: return sql.toString();
246: }
247:
248: /**
249: * Makes an SQL statement for getFeatures. Constructs an SQL statement that will select the
250: * features from the table based on the filter.
251: *
252: * @param attrTypes The Attribute types for the select statement
253: * @param filter The filter to convert to a where statement.
254: * @param maxFeatures The max amount of features to return.
255: * @param useMax True if we are to use the maxFeature as the max.
256: *
257: * @return An SQL statement.
258: *
259: * @throws SQLEncoderException If an error occurs encoding the SQL
260: */
261: String makeSelectSQL(AttributeType[] attrTypes, Filter filter,
262: int maxFeatures, boolean useMax) throws SQLEncoderException {
263: LOGGER.finer("Creating sql for Query: mf=" + maxFeatures
264: + " filter=" + filter + " useMax=" + useMax);
265:
266: StringBuffer sqlBuffer = new StringBuffer();
267:
268: sqlBuffer.append("SELECT ");
269: sqlBuffer.append(fidColumn);
270:
271: for (int i = 0; i < attrTypes.length; i++) {
272: sqlBuffer.append(", ");
273: sqlBuffer.append(attrTypes[i].getName());
274: }
275:
276: sqlBuffer.append(" FROM ");
277: sqlBuffer.append(tableName);
278:
279: if (filter != null && filter != org.geotools.filter.Filter.NONE) {
280: String where = whereEncoder.encode(filter);
281:
282: sqlBuffer.append(" ");
283: sqlBuffer.append(where);
284:
285: if (useMax && (maxFeatures > 0)) {
286: sqlBuffer.append(" and ROWNUM <= ");
287: sqlBuffer.append(maxFeatures);
288: }
289: } else if (useMax && (maxFeatures > 0)) {
290: sqlBuffer.append(" WHERE ROWNUM <= ");
291: sqlBuffer.append(maxFeatures);
292: }
293:
294: String sqlStmt = sqlBuffer.toString();
295:
296: LOGGER.finer("sqlString = " + sqlStmt);
297:
298: return sqlStmt;
299: }
300:
301: /**
302: * Makes a template SQL statement for use in an update prepared statement. The template will
303: * have the form: <code>UPDATE <tablename> SET <type> = ?</code>
304: *
305: * @param attributeTypes The feature attributes that are being updated.
306: *
307: * @return An SQL template.
308: */
309: String makeModifyTemplate(AttributeType[] attributeTypes) {
310: StringBuffer buffer = new StringBuffer("UPDATE ");
311:
312: buffer.append(tableName);
313: buffer.append(" SET ");
314:
315: for (int i = 0; i < attributeTypes.length; i++) {
316: buffer.append(attributeTypes[i].getName());
317: buffer.append(" = ? ");
318: if (i < (attributeTypes.length - 1)) {
319: buffer.append(", ");
320: } else {
321: buffer.append(" ");
322: }
323: }
324:
325: return buffer.toString();
326: }
327:
328: String makeModifyTemplate(AttributeType[] attributeTypes,
329: Filter filter) throws SQLEncoderException {
330: String whereClause = whereEncoder.encode(filter);
331:
332: return makeModifyTemplate(attributeTypes) + " " + whereClause;
333: }
334:
335: String makeDeleteSQL(Filter filter) throws SQLEncoderException {
336: return "DELETE FROM " + tableName + " "
337: + whereEncoder.encode(filter);
338: }
339:
340: public String makeAddGeomMetadata(FeatureType featureType,
341: Envelope bounds, int srid) {
342: StringBuffer sql = new StringBuffer();
343:
344: // SPATIAL INDEX (On default geometry)
345: String defaultGeometry = featureType.getDefaultGeometry()
346: .getName();
347:
348: sql.append("INSERT INTO user_sdo_geom_metadata");
349: sql.append(" (TABLE_NAME, COLUMN_NAME,DIMINFO,SRID)");
350: sql.append("VALUES (");
351: sql.append(" '" + tableName + "',");
352: sql.append(" '" + defaultGeometry + "',");
353: sql.append(" MDSYS.SDO_DIM_ARRAY(");
354: sql.append(" MDSYS.SDO_DIM_ELEMENT('X', "
355: + bounds.getMinX() + "," + bounds.getMaxX()
356: + ", 0.005),"); // -- use appropriate values here ie. min and max x and y
357: sql.append(" MDSYS.SDO_DIM_ELEMENT('Y', "
358: + bounds.getMinY() + "," + bounds.getMaxY()
359: + ", 0.005)");
360: sql.append(" ),");
361: sql
362: .append(" "
363: + (srid == -1 ? "NULL" : String.valueOf(srid)));
364: sql.append(")");
365: return sql.toString();
366: }
367: }
|