001: /*
002: * GeoTools - OpenSource mapping toolkit
003: * http://geotools.org
004: * (C) 2004-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.PreparedStatement;
021: import java.sql.SQLException;
022: import java.util.logging.Level;
023: import java.util.logging.Logger;
024:
025: import org.geotools.data.DataSourceException;
026: import org.geotools.data.FeatureReader;
027: import org.geotools.data.jdbc.attributeio.AttributeIO;
028: import org.geotools.data.jdbc.fidmapper.FIDMapper;
029: import org.geotools.feature.AttributeType;
030: import org.geotools.feature.Feature;
031: import org.geotools.feature.FeatureType;
032: import org.geotools.feature.GeometryAttributeType;
033:
034: /**
035: * An abstract class that uses prepared statements to insert, update and delete
036: * features from the database. Useful when the resultset got from the database
037: * is not updatable, and to get peak performance thru the use of prepared
038: * statements and batch updates.
039: *
040: * <p>
041: * Assumptions made by the code:
042: *
043: * <ul>
044: * <li>
045: * if the primary key contains auto-increment columns, it is composed solely of
046: * auto-increment columns;
047: * </li>
048: * <li>
049: * the primary key never contains geometric attributes;
050: * </li>
051: * </ul>
052: * </p>
053: *
054: * @author Andrea Aime
055: * @source $URL: http://svn.geotools.org/geotools/tags/2.4.1/modules/library/jdbc/src/main/java/org/geotools/data/jdbc/JDBCPSFeatureWriter.java $
056: */
057: public abstract class JDBCPSFeatureWriter extends JDBCFeatureWriter {
058: /** The logger for the jdbc module. */
059: private static final Logger LOGGER = org.geotools.util.logging.Logging
060: .getLogger("org.geotools.data.jdbc");
061: FIDMapper mapper = null;
062: PreparedStatement insertStatement;
063: PreparedStatement deleteStatement;
064: PreparedStatement updateStatement;
065:
066: /**
067: * Creates a new instance of JDBCFeatureWriter
068: *
069: * @param fReader
070: * @param queryData
071: *
072: * @throws IOException
073: */
074: public JDBCPSFeatureWriter(FeatureReader fReader,
075: QueryData queryData) throws IOException {
076: super (fReader, queryData);
077: mapper = queryData.getMapper();
078: }
079:
080: /**
081: * Override that uses prepared statements to perform the operation.
082: *
083: * @see org.geotools.data.jdbc.JDBCFeatureWriter#doInsert(org.geotools.data.jdbc.MutableFIDFeature)
084: */
085: protected void doInsert(MutableFIDFeature current)
086: throws IOException, SQLException {
087: LOGGER.fine("inserting into database feature " + current);
088:
089: // lazily create the insert statement
090: if (insertStatement != null) {
091: insertStatement = createInsertStatement(queryData
092: .getConnection(), queryData.getFeatureType());
093: }
094:
095: try {
096: fillInsertParameters(insertStatement, current);
097: insertStatement.executeUpdate();
098:
099: // should the ID be generated during an insert, we need to read it back
100: // and set it into the feature
101: if (((mapper.getColumnCount() > 0) && mapper
102: .hasAutoIncrementColumns())) {
103: current.setID(mapper.createID(
104: queryData.getConnection(), current,
105: insertStatement));
106: }
107: } catch (SQLException sqle) {
108: String msg = "SQL Exception writing geometry column";
109: LOGGER.log(Level.SEVERE, msg, sqle);
110: queryData.close(sqle);
111: throw new DataSourceException(msg, sqle);
112: }
113: }
114:
115: /**
116: * Fills the insert parameters
117: *
118: * @param statement
119: * @param feature
120: *
121: * @throws IOException
122: * @throws SQLException
123: */
124: private void fillInsertParameters(PreparedStatement statement,
125: MutableFIDFeature feature) throws IOException, SQLException {
126: int baseIndex = fillPrimaryKeyParameters(statement, feature, 1);
127:
128: Object[] attributes = feature.getAttributes(null);
129: AttributeType[] attributeTypes = feature.getFeatureType()
130: .getAttributeTypes();
131: AttributeIO[] aios = queryData.getAttributeHandlers();
132: FeatureTypeInfo ftInfo = queryData.getFeatureTypeInfo();
133:
134: for (int i = 0; i < attributeTypes.length; i++) {
135: if (attributeTypes[i] instanceof GeometryAttributeType) {
136: String geomName = attributeTypes[i].getName();
137: int srid = ftInfo.getSRID(geomName);
138: // ((Geometry) attributes[i]).setSRID(srid); // SRID is a bad assumption
139: aios[i].write(statement, baseIndex + i, attributes[i]);
140: } else {
141: aios[i].write(statement, baseIndex + i, attributes[i]);
142: }
143: }
144: }
145:
146: /**
147: * Creates the prepared statement for feature inserts
148: *
149: * @param conn
150: * @param featureType
151: *
152: *
153: * @throws SQLException
154: */
155: protected PreparedStatement createInsertStatement(Connection conn,
156: FeatureType featureType) throws SQLException {
157: AttributeType[] attributeTypes = featureType
158: .getAttributeTypes();
159: String tableName = featureType.getTypeName();
160:
161: StringBuffer statementSQL = new StringBuffer("INSERT INTO "
162: + tableName + "(");
163:
164: if (!mapper.returnFIDColumnsAsAttributes()) {
165: for (int i = 0; i < mapper.getColumnCount(); i++) {
166: if (!mapper.isAutoIncrement(i)) {
167: statementSQL.append(mapper.getColumnName(i))
168: .append(",");
169: }
170: }
171: }
172:
173: for (int i = 0; i < attributeTypes.length; i++) {
174: statementSQL.append(attributeTypes[i].getName())
175: .append(",");
176: }
177:
178: statementSQL.setCharAt(statementSQL.length() - 1, ')');
179: statementSQL.append(" VALUES (");
180:
181: // append primary key placeholders, if any
182: if (!mapper.returnFIDColumnsAsAttributes()
183: && !mapper.hasAutoIncrementColumns()) {
184: for (int i = 0; i < mapper.getColumnCount(); i++) {
185: statementSQL.append("?,");
186: }
187: }
188:
189: // append attribute columns placeholders
190: for (int i = 0; i < attributeTypes.length; i++) {
191: if (attributeTypes[i] instanceof GeometryAttributeType) {
192: statementSQL.append("?");
193: } else {
194: statementSQL
195: .append(getGeometryPlaceHolder(attributeTypes[i]));
196: }
197:
198: statementSQL.append(",");
199: }
200:
201: statementSQL.setCharAt(statementSQL.length() - 1, ')');
202:
203: String sql = statementSQL.toString();
204:
205: return conn.prepareStatement(sql);
206: }
207:
208: /**
209: * Returns the placeholder for the geometry in the insert/update statement.
210: * May be something like "?", "geomFromBinary(?)" and so on, that is, the
211: * geometry itself of some function that turns whatever the geometric
212: * AttributeIO generates into a geometry for the database.
213: *
214: * @param type
215: *
216: */
217: protected abstract String getGeometryPlaceHolder(AttributeType type);
218:
219: /**
220: * Override that uses prepared statements to perform the operation.
221: *
222: * @see org.geotools.data.jdbc.JDBCFeatureWriter#doInsert(org.geotools.data.jdbc.MutableFIDFeature)
223: */
224: protected void remove(MutableFIDFeature current)
225: throws IOException, SQLException {
226: LOGGER.fine("inserting into database feature " + current);
227:
228: // lazily create the delete statement
229: if (deleteStatement != null) {
230: deleteStatement = createDeleteStatement(queryData
231: .getConnection(), queryData.getFeatureType());
232: }
233:
234: try {
235: fillDeleteParameters(deleteStatement, current);
236: deleteStatement.executeUpdate();
237: } catch (SQLException sqle) {
238: String msg = "SQL Exception writing geometry column";
239: LOGGER.log(Level.SEVERE, msg, sqle);
240: queryData.close(sqle);
241: throw new DataSourceException(msg, sqle);
242: }
243: }
244:
245: /**
246: * Fills the delete statement parameters
247: *
248: * @param statement
249: * @param feature
250: *
251: * @throws IOException
252: * @throws SQLException
253: */
254: private void fillDeleteParameters(PreparedStatement statement,
255: MutableFIDFeature feature) throws IOException, SQLException {
256: fillPrimaryKeyParameters(statement, feature, 1);
257: }
258:
259: /**
260: * Fills into a prepared statement the primary key values starting from the
261: * baseIndex index.
262: *
263: * @param statement
264: * @param feature
265: * @param baseIndex
266: *
267: *
268: * @throws IOException
269: * @throws SQLException
270: */
271: private int fillPrimaryKeyParameters(PreparedStatement statement,
272: Feature feature, int baseIndex) throws IOException,
273: SQLException {
274: if (!mapper.returnFIDColumnsAsAttributes()
275: && !mapper.hasAutoIncrementColumns()) {
276: String FID = mapper.createID(queryData.getConnection(),
277: feature, null);
278: Object[] primaryKey = mapper.getPKAttributes(FID);
279:
280: for (int i = 0; i < primaryKey.length; i++) {
281: statement.setObject(i + 1, primaryKey[i]);
282: }
283:
284: baseIndex += primaryKey.length;
285: }
286:
287: return baseIndex;
288: }
289:
290: /**
291: * Creates the prepared statement for feature deletes
292: *
293: * @param conn
294: * @param featureType
295: *
296: *
297: * @throws SQLException
298: */
299: protected PreparedStatement createDeleteStatement(Connection conn,
300: FeatureType featureType) throws SQLException {
301: AttributeType[] attributeTypes = featureType
302: .getAttributeTypes();
303: String tableName = featureType.getTypeName();
304:
305: StringBuffer statementSQL = new StringBuffer("DELETE "
306: + tableName + "WHERE ");
307:
308: if (!mapper.returnFIDColumnsAsAttributes()) {
309: for (int i = 0; i < mapper.getColumnCount(); i++) {
310: if (!mapper.isAutoIncrement(i)) {
311: statementSQL.append(mapper.getColumnName(i))
312: .append(" = ?");
313:
314: if (i < (mapper.getColumnCount() - 1)) {
315: statementSQL.append(" AND ");
316: }
317: }
318: }
319: }
320:
321: String sql = statementSQL.toString();
322:
323: return conn.prepareStatement(sql);
324: }
325:
326: /**
327: * Fills the insert parameters
328: *
329: * @param statement
330: * @param current
331: * @param live DOCUMENT ME!
332: *
333: * @throws IOException
334: * @throws SQLException
335: */
336: private void fillUpdateParameters(PreparedStatement statement,
337: Feature current, Feature live) throws IOException,
338: SQLException {
339: Object[] attributes = current.getAttributes(null);
340: AttributeType[] attributeTypes = current.getFeatureType()
341: .getAttributeTypes();
342: AttributeIO[] aios = queryData.getAttributeHandlers();
343: FeatureTypeInfo ftInfo = queryData.getFeatureTypeInfo();
344:
345: // set new vales for other fields
346: for (int i = 0; i < attributeTypes.length; i++) {
347: if (attributeTypes[i] instanceof GeometryAttributeType) {
348: String geomName = attributeTypes[i].getName();
349: int srid = ftInfo.getSRID(geomName);
350: // ((Geometry) attributes[i]).setSRID(srid); // SRID is a bad assumption
351: aios[i].write(statement, i + 1, attributes[i]);
352: } else {
353: aios[i].write(statement, i + 1, attributes[i]);
354: }
355: }
356:
357: // set new values for the primary key
358: int baseIndex = attributeTypes.length + 1;
359:
360: if (!mapper.returnFIDColumnsAsAttributes()
361: && !mapper.hasAutoIncrementColumns()) {
362: baseIndex = fillPrimaryKeyParameters(statement, current,
363: baseIndex);
364: }
365:
366: // set the old values of the primary key in order to look up for the right tuple
367: fillPrimaryKeyParameters(statement, live, baseIndex + 1);
368: }
369:
370: /**
371: * Creates the prepared statement for feature updates
372: *
373: * @param conn
374: * @param featureType
375: *
376: *
377: * @throws SQLException
378: */
379: protected PreparedStatement createUpdateStatement(Connection conn,
380: FeatureType featureType) throws SQLException {
381: AttributeType[] attributeTypes = featureType
382: .getAttributeTypes();
383: String tableName = featureType.getTypeName();
384:
385: // create statement piecewise on a string buffer
386: StringBuffer statementSQL = new StringBuffer("UPDATE "
387: + tableName + " SET ");
388:
389: // the "SET" part updating the fields, and the primary key too, if it's
390: // not generated by the DBMS
391: for (int i = 0; i < attributeTypes.length; i++) {
392: statementSQL.append(attributeTypes[i].getName()).append(
393: " = ");
394:
395: if (attributeTypes[i] instanceof GeometryAttributeType) {
396: statementSQL.append("?");
397: } else {
398: statementSQL
399: .append(getGeometryPlaceHolder(attributeTypes[i]));
400: }
401:
402: statementSQL.append(",");
403: }
404:
405: // ... the updated primary keys, if any...
406: if (!mapper.returnFIDColumnsAsAttributes()
407: && !mapper.hasAutoIncrementColumns()) {
408: for (int i = 0; i < mapper.getColumnCount(); i++) {
409: statementSQL.append(mapper.getColumnName(i)).append(
410: " = ?,");
411: }
412: }
413:
414: statementSQL.setCharAt(statementSQL.length() - 1, ' ');
415: statementSQL.append(" WHERE ");
416:
417: // now append primary key placeholders
418: if (!mapper.returnFIDColumnsAsAttributes()
419: && !mapper.hasAutoIncrementColumns()) {
420: for (int i = 0; i < mapper.getColumnCount(); i++) {
421: statementSQL.append(mapper.getColumnName(i)).append(
422: " = ?");
423:
424: if (i < (mapper.getColumnCount() - 1)) {
425: statementSQL.append(" AND ");
426: }
427: }
428: }
429:
430: String sql = statementSQL.toString();
431:
432: return conn.prepareStatement(sql);
433: }
434:
435: /**
436: * Override that uses sql statements to perform the operation.
437: *
438: * @see org.geotools.data.jdbc.JDBCFeatureWriter#doUpdate(org.geotools.feature.Feature,
439: * org.geotools.feature.Feature)
440: */
441: protected void doUpdate(Feature live, Feature current)
442: throws IOException, SQLException {
443: LOGGER.fine("updating postgis feature " + current);
444:
445: // lazily create the insert statement
446: if (updateStatement != null) {
447: updateStatement = createUpdateStatement(queryData
448: .getConnection(), queryData.getFeatureType());
449: }
450:
451: try {
452: fillUpdateParameters(updateStatement, current, live);
453: updateStatement.executeUpdate();
454: } catch (SQLException sqle) {
455: String msg = "SQL Exception writing geometry column";
456: LOGGER.log(Level.SEVERE, msg, sqle);
457: queryData.close(sqle);
458: throw new DataSourceException(msg, sqle);
459: }
460: }
461:
462: /**
463: * This version does not use QueryData udpate/insert/remove methods, but
464: * uses separate prepared statements instead
465: *
466: * @see org.geotools.data.jdbc.JDBCFeatureWriter#useQueryDataForInsert()
467: */
468: protected boolean useQueryDataForInsert() {
469: return false;
470: }
471:
472: public void close() throws IOException {
473: JDBCUtils.close(insertStatement);
474: JDBCUtils.close(updateStatement);
475: JDBCUtils.close(deleteStatement);
476: super.close();
477: }
478: }
|