001: /*
002: * GeoTools - OpenSource mapping toolkit
003: * http://geotools.org
004: * (C) 2003-2006, 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: package org.geotools.data.jdbc;
017:
018: import java.io.IOException;
019: import java.sql.Connection;
020: import java.sql.ResultSet;
021: import java.sql.SQLException;
022: import java.sql.Statement;
023: import java.util.Collection;
024: import java.util.Collections;
025: import java.util.HashSet;
026: import java.util.Set;
027: import java.util.logging.Level;
028: import java.util.logging.Logger;
029:
030: import org.geotools.data.DataSourceException;
031: import org.geotools.data.DataUtilities;
032: import org.geotools.data.FeatureListenerManager;
033: import org.geotools.data.FeatureLockException;
034: import org.geotools.data.FeatureReader;
035: import org.geotools.data.jdbc.fidmapper.FIDMapper;
036: import org.geotools.feature.AttributeType;
037: import org.geotools.feature.Feature;
038: import org.geotools.feature.FeatureType;
039: import org.geotools.feature.GeometryAttributeType;
040:
041: import com.vividsolutions.jts.geom.Envelope;
042: import com.vividsolutions.jts.geom.Geometry;
043:
044: /**
045: * An abstract class that uses sql statements to insert, update and delete
046: * features from the database. Useful when the resultset got from the database
047: * is not updatable, for example.
048: *
049: * @task TODO: Use prepared statements for inserts. Jody says that oracle
050: * at least will perform faster, and I imagine postgis will
051: * too. This will require a bit of rearchitecture, since the
052: * statement should just be made once, right now even if there
053: * were many features coming in they would all have to make
054: * a new prepared statement - should be able to do it before
055: * and then just fill it up for each feature. And for oracle
056: * Jody has some convenience methods in his SDO stuff that
057: * works with prepared statements and STRUCTS directly.
058: * See http://jira.codehaus.org/browse/GEOT-219 (close when done).
059: *
060: * @author Andrea Aime
061: * @author chorner
062: * @source $URL: http://svn.geotools.org/geotools/tags/2.4.1/modules/library/jdbc/src/main/java/org/geotools/data/jdbc/JDBCTextFeatureWriter.java $
063: * @version $Id: JDBCTextFeatureWriter.java 27862 2007-11-12 19:51:19Z desruisseaux $
064: */
065: public abstract class JDBCTextFeatureWriter extends JDBCFeatureWriter {
066: /** The logger for the jdbc module. */
067: private static final Logger LOGGER = org.geotools.util.logging.Logging
068: .getLogger("org.geotools.data.jdbc");
069: protected FIDMapper mapper = null;
070:
071: /** indicates the lock attempt is in progress */
072: final int STATE_WAIT = 1;
073:
074: /** indicates the lock attempt was successful */
075: final int STATE_SUCCESS = 2;
076:
077: /** indicates the lock attempt failed horribly */
078: final int STATE_FAILURE = 3;
079: private FeatureListenerManager listenerManager;
080:
081: /**
082: * Creates a new instance of JDBCFeatureWriter
083: *
084: * @param fReader
085: * @param queryData
086: *
087: * @throws IOException
088: */
089: public JDBCTextFeatureWriter(FeatureReader fReader,
090: QueryData queryData) throws IOException {
091: super (fReader, queryData);
092: mapper = queryData.getMapper();
093: listenerManager = queryData.getListenerManager();
094: }
095:
096: /**
097: * Override that uses sql statements to perform the operation.
098: *
099: * @see org.geotools.data.jdbc.JDBCFeatureWriter#doInsert(org.geotools.data.jdbc.MutableFIDFeature)
100: */
101: protected void doInsert(MutableFIDFeature current)
102: throws IOException, SQLException {
103: if (LOGGER.isLoggable(Level.FINE))
104: LOGGER.fine("inserting into postgis feature " + current);
105:
106: Statement statement = null;
107: Connection conn = null;
108:
109: try {
110: conn = queryData.getConnection();
111: statement = conn.createStatement();
112:
113: String sql = makeInsertSql(current);
114: if (LOGGER.isLoggable(Level.FINE))
115: LOGGER.fine(sql);
116: statement.executeUpdate(sql);
117:
118: // should the ID be generated during an insert, we need to read it back
119: // and set it into the feature
120: if (((mapper.getColumnCount() > 0) && mapper
121: .hasAutoIncrementColumns())) {
122: // if (((mapper.getColumnCount() > 0))) {
123: current
124: .setID(mapper
125: .createID(conn, current, statement));
126: }
127: } catch (SQLException sqle) {
128: String msg = "SQL Exception writing geometry column"
129: + sqle.getLocalizedMessage();
130: LOGGER.log(Level.SEVERE, msg, sqle);
131: queryData.close(sqle);
132: throw new DataSourceException(msg, sqle);
133: } finally {
134: if (statement != null) {
135: try {
136: statement.close();
137: } catch (SQLException e) {
138: String msg = "Error closing JDBC Statement";
139: LOGGER.log(Level.WARNING, msg, e);
140: }
141: }
142: }
143: }
144:
145: /**
146: * Creates a sql insert statement. Uses each feature's schema, which makes
147: * it possible to insert out of order, as well as inserting less than all
148: * features.
149: *
150: * @param feature the feature to add.
151: *
152: * @return an insert sql statement.
153: *
154: * @throws IOException
155: */
156: protected String makeInsertSql(Feature feature) throws IOException {
157: FeatureTypeInfo ftInfo = queryData.getFeatureTypeInfo();
158: FeatureType featureType = ftInfo.getSchema();
159:
160: String tableName = encodeName(featureType.getTypeName());
161: AttributeType[] attributeTypes = featureType
162: .getAttributeTypes();
163:
164: String attrValue;
165:
166: StringBuffer statementSQL = new StringBuffer("INSERT INTO "
167: + tableName + " (");
168:
169: // either add statements to append non autoincrement colums, or gather
170: // the auto-increment ones
171: Set autoincrementColumns = null;
172: if (!mapper.returnFIDColumnsAsAttributes()) {
173: autoincrementColumns = Collections.EMPTY_SET;
174: for (int i = 0; i < mapper.getColumnCount(); i++) {
175: if (!(mapper.isAutoIncrement(i) && feature
176: .getAttribute(mapper.getColumnName(i)) == null)) {
177: statementSQL.append(mapper.getColumnName(i))
178: .append(",");
179: }
180: }
181: } else {
182: autoincrementColumns = new HashSet();
183: for (int i = 0; i < mapper.getColumnCount(); i++) {
184: if (mapper.isAutoIncrement(i)) {
185: autoincrementColumns.add(mapper.getColumnName(i));
186: }
187: }
188: }
189:
190: // encode insertion for attributes, but remember to avoid auto-increment ones,
191: // they may be included in the feature type as well
192: for (int i = 0; i < attributeTypes.length; i++) {
193: String attName = attributeTypes[i].getName();
194: if (!autoincrementColumns.contains(attName)
195: || feature.getAttribute(attName) != null) {
196: String colName = encodeColumnName(attName);
197: statementSQL.append(colName).append(",");
198: }
199: }
200:
201: statementSQL.setCharAt(statementSQL.length() - 1, ')');
202: statementSQL.append(" VALUES (");
203:
204: if (!mapper.returnFIDColumnsAsAttributes()
205: && !mapper.hasAutoIncrementColumns()) {
206: String FID = mapper.createID(queryData.getConnection(),
207: feature, null);
208: if (current instanceof MutableFIDFeature) {
209: ((MutableFIDFeature) current).setID(FID);
210: }
211: Object[] primaryKey = mapper.getPKAttributes(FID);
212:
213: for (int i = 0; i < primaryKey.length; i++) {
214: if (!mapper.isAutoIncrement(i) || primaryKey[i] != null) {
215: attrValue = addQuotes(primaryKey[i]);
216: statementSQL.append(attrValue).append(",");
217: }
218: }
219: }
220:
221: Object[] attributes = feature.getAttributes(null);
222:
223: for (int i = 0; i < attributeTypes.length; i++) {
224: attrValue = null;
225: if (attributeTypes[i] instanceof GeometryAttributeType) {
226: String geomName = attributeTypes[i].getName();
227: int srid = ftInfo.getSRID(geomName);
228: Geometry geometry = (Geometry) attributes[i];
229: if (geometry == null) {
230: attrValue = "NULL";
231: } else
232: attrValue = getGeometryInsertText(geometry, srid);
233: } else {
234: if (!autoincrementColumns.contains(attributeTypes[i]
235: .getName())
236: || attributes[i] != null)
237: attrValue = addQuotes(attributes[i]);
238: }
239:
240: if (attrValue != null)
241: statementSQL.append(attrValue + ",");
242: }
243:
244: statementSQL.setCharAt(statementSQL.length() - 1, ')');
245:
246: return (statementSQL.toString());
247: }
248:
249: /**
250: * Adds quotes to an object for storage in postgis. The object should be a
251: * string or a number. To perform an insert strings need quotes around
252: * them, and numbers work fine with quotes, so this method can be called
253: * on unknown objects.
254: *
255: * @param value The object to add quotes to.
256: *
257: * @return a string representation of the object with quotes.
258: */
259: protected String addQuotes(Object value) {
260: String retString;
261:
262: if (value != null) {
263: if (value instanceof Number)
264: retString = value.toString();
265: else
266: retString = "'" + doubleQuote(value) + "'";
267: } else {
268: retString = "null";
269: }
270:
271: return retString;
272: }
273:
274: String doubleQuote(Object obj) {
275: return obj.toString().replaceAll("'", "''");
276: }
277:
278: /**
279: * Encodes the tableName, default is to do nothing, but postgis will
280: * override and put double quotes around the tablename.
281: */
282: protected String encodeName(String tableName) {
283: return tableName;
284: }
285:
286: /**
287: * Encodes the colName, default just calls {@link #encodeName(String)}.
288: */
289: protected String encodeColumnName(String colName) {
290: return encodeName(colName);
291: }
292:
293: /**
294: * Turns a geometry into the textual version needed for the sql statement
295: *
296: * @param geom
297: * @param srid
298: *
299: */
300: protected abstract String getGeometryInsertText(Geometry geom,
301: int srid) throws IOException;
302:
303: /**
304: * Override that uses sql statements to perform the operation.
305: *
306: * @see org.geotools.data.FeatureWriter#remove()
307: */
308: public void remove() throws IOException {
309: if (LOGGER.isLoggable(Level.FINE))
310: LOGGER.fine("inserting into postgis feature " + current);
311:
312: Statement statement = null;
313: Connection conn = null;
314:
315: try {
316: conn = queryData.getConnection();
317: statement = conn.createStatement();
318: Envelope bounds = this .live.getBounds();
319: String sql = makeDeleteSql(current);
320: if (LOGGER.isLoggable(Level.FINE))
321: LOGGER.fine(sql);
322:
323: //System.out.println(sql);
324: statement.executeUpdate(sql);
325:
326: listenerManager.fireFeaturesRemoved(getFeatureType()
327: .getTypeName(), queryData.getTransaction(), bounds,
328: false);
329: } catch (SQLException sqle) {
330: String msg = "SQL Exception writing geometry column";
331: LOGGER.log(Level.SEVERE, msg, sqle);
332: queryData.close(sqle);
333: throw new DataSourceException(msg, sqle);
334: } finally {
335: if (statement != null) {
336: try {
337: statement.close();
338: } catch (SQLException e) {
339: String msg = "Error closing JDBC Statement";
340: LOGGER.log(Level.WARNING, msg, e);
341: }
342: }
343: }
344: }
345:
346: /**
347: * Generates the query for the sql delete statement
348: *
349: * @param feature
350: *
351: *
352: * @throws IOException
353: */
354: protected String makeDeleteSql(Feature feature) throws IOException {
355: FeatureTypeInfo ftInfo = queryData.getFeatureTypeInfo();
356: FeatureType fetureType = ftInfo.getSchema();
357:
358: String tableName = encodeName(fetureType.getTypeName());
359:
360: StringBuffer statementSQL = new StringBuffer("DELETE FROM "
361: + tableName + " WHERE ");
362: Object[] pkValues = mapper.getPKAttributes(feature.getID());
363:
364: for (int i = 0; i < mapper.getColumnCount(); i++) {
365: statementSQL.append(
366: encodeColumnName(mapper.getColumnName(i))).append(
367: " = ").append(addQuotes(pkValues[i]));
368:
369: if (i < (mapper.getColumnCount() - 1)) {
370: statementSQL.append(" AND ");
371: }
372: }
373:
374: return (statementSQL.toString());
375: }
376:
377: /**
378: * Override that uses sql statements to perform the operation.
379: *
380: * @see org.geotools.data.jdbc.JDBCFeatureWriter#doUpdate(org.geotools.feature.Feature,
381: * org.geotools.feature.Feature)
382: */
383: protected void doUpdate(Feature live, Feature current)
384: throws IOException, SQLException {
385:
386: if (LOGGER.isLoggable(Level.FINE))
387: LOGGER.fine("updating postgis feature " + current);
388:
389: Statement statement = null;
390: Connection conn = null;
391:
392: try {
393: conn = queryData.getConnection();
394: statement = conn.createStatement();
395:
396: boolean hasLock = false;
397: String sql = makeSelectForUpdateSql(current);
398: if (sql == null) {
399: LOGGER
400: .fine("Lock acquisition not attempted, JDBCTextFeatureWriter may block during concurrent updates");
401: } else { //we have a statement, let's use it
402: ResultSet result = null;
403: try {
404: result = statement.executeQuery(sql);
405: //TODO: read the result
406: // if (result != null) {
407: // System.out.println(result.toString());
408: // }
409: hasLock = true;
410: } catch (SQLException e) {
411: LOGGER.severe(e.getLocalizedMessage());
412: throw new FeatureLockException(
413: "Your feature is locked!", current.getID(),
414: e); //do not catch
415: } finally {
416: if (result != null) {
417: try {
418: result.close();
419: } catch (SQLException e) {
420: }
421: result = null;
422: }
423: }
424: }
425:
426: if (sql == null || hasLock) {
427: //attempt the update if we have a lock, or we are too lazy to check
428: sql = makeUpdateSql(live, current);
429: if (LOGGER.isLoggable(Level.FINE))
430: LOGGER.fine(sql);
431: statement.executeUpdate(sql);
432: } else { //shouldn't be called?
433: throw new IOException("Feature Lock failed; giving up");
434: }
435: } catch (SQLException sqle) {
436: String msg = "SQL Exception writing geometry column";
437: LOGGER.log(Level.SEVERE, msg, sqle);
438: queryData.close(sqle);
439: throw new DataSourceException(msg, sqle);
440: } finally {
441: if (statement != null) {
442: try {
443: statement.close();
444: } catch (SQLException e) {
445: String msg = "Error closing JDBC Statement";
446: LOGGER.log(Level.WARNING, msg, e);
447: }
448: }
449: }
450: }
451:
452: /**
453: * Generate the select for update statement, which will attempt to
454: * lock features for update. This should be overwritten by databases
455: * which want to take advantage of this method.
456: *
457: * This method is called in a timer thread, to prevent blocking.
458: *
459: * @since 2.2.0
460: * @param current
461: * @return sql string or null
462: */
463: protected String makeSelectForUpdateSql(Feature current) {
464: return null;
465: }
466:
467: /**
468: * Generate the update sql statement
469: *
470: * @param live
471: * @param current
472: *
473: *
474: * @throws IOException
475: */
476: protected String makeUpdateSql(Feature live, Feature current)
477: throws IOException {
478: FeatureTypeInfo ftInfo = queryData.getFeatureTypeInfo();
479: FeatureType featureType = ftInfo.getSchema();
480: AttributeType[] attributes = featureType.getAttributeTypes();
481:
482: String tableName = encodeName(featureType.getTypeName());
483:
484: StringBuffer statementSQL = new StringBuffer("UPDATE "
485: + tableName + " SET ");
486:
487: for (int i = 0; i < current.getNumberOfAttributes(); i++) {
488: Object currAtt = current.getAttribute(i);
489: Object liveAtt = live.getAttribute(i);
490:
491: if (!DataUtilities.attributesEqual(liveAtt, currAtt)) {
492: if (LOGGER.isLoggable(Level.INFO)) {
493: LOGGER.fine("modifying att# " + i + " to "
494: + currAtt);
495: }
496:
497: String attrValue = null;
498: if (attributes[i] instanceof GeometryAttributeType) {
499: String geomName = attributes[i].getName();
500: int srid = ftInfo.getSRID(geomName);
501: Geometry geometry = (Geometry) currAtt;
502: if (geometry == null)
503: attrValue = "NULL";
504: else
505: attrValue = getGeometryInsertText(geometry,
506: srid);
507: } else {
508: attrValue = addQuotes(currAtt);
509: }
510:
511: String colName = encodeColumnName(attributes[i]
512: .getName());
513: statementSQL.append(colName).append(" = ").append(
514: attrValue).append(", ");
515: }
516: }
517:
518: //erase the last comma
519: statementSQL.setLength(statementSQL.length() - 2);
520: statementSQL.append(" WHERE ");
521:
522: Object[] pkValues = mapper.getPKAttributes(current.getID());
523:
524: for (int i = 0; i < mapper.getColumnCount(); i++) {
525: statementSQL.append(mapper.getColumnName(i)).append(" = ")
526: .append(addQuotes(pkValues[i]));
527:
528: if (i < (mapper.getColumnCount() - 1)) {
529: statementSQL.append(" AND ");
530: }
531: }
532:
533: return (statementSQL.toString());
534: }
535:
536: /**
537: * This version does not use QueryData udpate/insert/remove methods, but
538: * uses separate queries instead
539: *
540: * @see org.geotools.data.jdbc.JDBCFeatureWriter#useQueryDataForInsert()
541: */
542: protected boolean useQueryDataForInsert() {
543: return false;
544: }
545: }
|