001 //$HeadURL: https://svn.wald.intevation.org/svn/deegree/base/branches/2.3_testing/src/org/deegree/io/datastore/sql/oracle/OracleDatastore.java $ 002 /*---------------------------------------------------------------------------- 003 This file is part of deegree, http://deegree.org/ 004 Copyright (C) 2001-2009 by: 005 Department of Geography, University of Bonn 006 and 007 lat/lon GmbH 008 009 This library is free software; you can redistribute it and/or modify it under 010 the terms of the GNU Lesser General Public License as published by the Free 011 Software Foundation; either version 2.1 of the License, or (at your option) 012 any later version. 013 This library is distributed in the hope that it will be useful, but WITHOUT 014 ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS 015 FOR A PARTICULAR PURPOSE. See the GNU Lesser General Public License for more 016 details. 017 You should have received a copy of the GNU Lesser General Public License 018 along with this library; if not, write to the Free Software Foundation, Inc., 019 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA 020 021 Contact information: 022 023 lat/lon GmbH 024 Aennchenstr. 19, 53177 Bonn 025 Germany 026 http://lat-lon.de/ 027 028 Department of Geography, University of Bonn 029 Prof. Dr. Klaus Greve 030 Postfach 1147, 53001 Bonn 031 Germany 032 http://www.geographie.uni-bonn.de/deegree/ 033 034 e-mail: info@deegree.org 035 ----------------------------------------------------------------------------*/ 036 037 package org.deegree.io.datastore.sql.oracle; 038 039 import java.io.IOException; 040 import java.io.InputStream; 041 import java.lang.reflect.Field; 042 import java.sql.Connection; 043 import java.sql.PreparedStatement; 044 import java.sql.ResultSet; 045 import java.sql.SQLException; 046 import java.sql.Statement; 047 import java.util.Date; 048 import java.util.HashMap; 049 import java.util.Iterator; 050 import java.util.Map; 051 import java.util.Properties; 052 053 import oracle.spatial.geometry.JGeometry; 054 import oracle.sql.STRUCT; 055 import oracle.sql.TIMESTAMP; 056 057 import org.deegree.datatypes.Types; 058 import org.deegree.datatypes.UnknownTypeException; 059 import org.deegree.framework.log.ILogger; 060 import org.deegree.framework.log.LoggerFactory; 061 import org.deegree.framework.util.TimeTools; 062 import org.deegree.i18n.Messages; 063 import org.deegree.io.datastore.Datastore; 064 import org.deegree.io.datastore.DatastoreException; 065 import org.deegree.io.datastore.schema.MappedFeatureType; 066 import org.deegree.io.datastore.schema.MappedGeometryPropertyType; 067 import org.deegree.io.datastore.schema.TableRelation; 068 import org.deegree.io.datastore.schema.content.ConstantContent; 069 import org.deegree.io.datastore.schema.content.FieldContent; 070 import org.deegree.io.datastore.schema.content.FunctionParam; 071 import org.deegree.io.datastore.schema.content.MappingGeometryField; 072 import org.deegree.io.datastore.schema.content.SQLFunctionCall; 073 import org.deegree.io.datastore.sql.AbstractSQLDatastore; 074 import org.deegree.io.datastore.sql.StatementBuffer; 075 import org.deegree.io.datastore.sql.TableAliasGenerator; 076 import org.deegree.io.datastore.sql.VirtualContentProvider; 077 import org.deegree.io.datastore.sql.StatementBuffer.StatementArgument; 078 import org.deegree.io.datastore.sql.wherebuilder.WhereBuilder; 079 import org.deegree.model.crs.CoordinateSystem; 080 import org.deegree.model.feature.FeatureCollection; 081 import org.deegree.model.filterencoding.Filter; 082 import org.deegree.model.spatialschema.Geometry; 083 import org.deegree.model.spatialschema.GeometryException; 084 import org.deegree.ogcbase.SortProperty; 085 import org.deegree.ogcwebservices.wfs.operation.Query; 086 087 /** 088 * {@link Datastore} implementation for Oracle Spatial database systems. Supports Oracle Spatial for Oracle 10g. 089 * 090 * TODO Which Oracle spatial versions are supported exactly? 091 * 092 * @author <a href="mailto:schneider@lat-lon.de">Markus Schneider </a> 093 * @author <a href="mailto:tfr@users.sourceforge.net">Torsten Friebe </a> 094 * @author last edited by: $Author: mschneider $ 095 * 096 * @version $Revision: 20611 $, $Date: 2009-11-05 18:38:29 +0100 (Do, 05. Nov 2009) $ 097 */ 098 public class OracleDatastore extends AbstractSQLDatastore { 099 100 private static final ILogger LOG = LoggerFactory.getLogger( OracleDatastore.class ); 101 102 private static final String SRS_CODE_PROP_FILE = "srs_codes_oracle.properties"; 103 104 private static Map<String, Integer> nativeSrsCodeMap = new HashMap<String, Integer>(); 105 106 private static final int SRS_UNDEFINED = -1; 107 108 // used for identifying the last active connection (if changed, Reijer's JGeometry store workaround patch is 109 // applied) 110 private static Connection lastStoreConnection; 111 112 static { 113 try { 114 initSRSCodeMap(); 115 } catch ( IOException e ) { 116 String msg = "Cannot load native srs code file '" + SRS_CODE_PROP_FILE + "'."; 117 LOG.logError( msg, e ); 118 } 119 } 120 121 /** 122 * @param code 123 * an EPSG code 124 * @return the oracle code as stored in srs_codes_oracle.properties 125 */ 126 public static int getOracleSRIDCode( String code ) { 127 Integer res = nativeSrsCodeMap.get( code ); 128 if ( res != null ) { 129 return res.intValue(); 130 } 131 132 // only in Oracle 10, but what else to do? 133 return Integer.parseInt( code.split( ":" )[1] ); 134 } 135 136 /** 137 * @param srid 138 * @return an EPSG code or "-1", if none was found 139 */ 140 public static String fromOracleSRIDCode( int srid ) { 141 for ( String k : nativeSrsCodeMap.keySet() ) { 142 if ( nativeSrsCodeMap.get( k ).intValue() == srid ) { 143 return k; 144 } 145 } 146 147 return "-1"; 148 } 149 150 /** 151 * Returns a specific {@link WhereBuilder} implementation for Oracle Spatial. 152 * 153 * @param rootFts 154 * involved (requested) feature types 155 * @param aliases 156 * aliases for the feature types, may be null 157 * @param filter 158 * filter that restricts the matched features 159 * @param sortProperties 160 * sort criteria for the result, may be null or empty 161 * @param aliasGenerator 162 * used to generate unique table aliases 163 * @param vcProvider 164 * @return <code>WhereBuilder</code> implementation for Oracle Spatial 165 * @throws DatastoreException 166 */ 167 @Override 168 public WhereBuilder getWhereBuilder( MappedFeatureType[] rootFts, String[] aliases, Filter filter, 169 SortProperty[] sortProperties, TableAliasGenerator aliasGenerator, 170 VirtualContentProvider vcProvider ) 171 throws DatastoreException { 172 return new OracleSpatialWhereBuilder( rootFts, aliases, filter, sortProperties, aliasGenerator, vcProvider ); 173 } 174 175 /** 176 * Converts an Oracle specific geometry <code>Object</code> from the <code>ResultSet</code> to a deegree 177 * <code>Geometry</code>. 178 * 179 * @param value 180 * @param targetCS 181 * @param conn 182 * @return corresponding deegree geometry 183 * @throws SQLException 184 */ 185 @Override 186 public Geometry convertDBToDeegreeGeometry( Object value, CoordinateSystem targetCS, Connection conn ) 187 throws SQLException { 188 Geometry geometry = null; 189 if ( value != null ) { 190 LOG.logDebug( "Converting STRUCT to JGeometry." ); 191 JGeometry jGeometry = JGeometry.load( (STRUCT) value ); 192 try { 193 LOG.logDebug( "Converting JGeometry to deegree geometry ('" + targetCS + "')" ); 194 geometry = JGeometryAdapter.wrap( jGeometry, targetCS ); 195 } catch ( Exception e ) { 196 LOG.logError( "Error while converting STRUCT to Geometry: ", e ); 197 throw new SQLException( "Error converting STRUCT to Geometry: " + e.getMessage() ); 198 } 199 } 200 return geometry; 201 } 202 203 /** 204 * Converts a deegree <code>Geometry</code> to an Oracle specific geometry object. 205 * 206 * @param geometry 207 * @param nativeSRSCode 208 * @param conn 209 * @return corresponding Oracle specific geometry object 210 * @throws DatastoreException 211 */ 212 @Override 213 public STRUCT convertDeegreeToDBGeometry( Geometry geometry, int nativeSRSCode, Connection conn ) 214 throws DatastoreException { 215 216 JGeometry jGeometry = null; 217 LOG.logDebug( "Converting deegree geometry to JGeometry." ); 218 try { 219 jGeometry = JGeometryAdapter.export( geometry, nativeSRSCode ); 220 } catch ( GeometryException e ) { 221 throw new DatastoreException( "Error converting deegree geometry to JGeometry: " + e.getMessage(), e ); 222 } 223 224 LOG.logDebug( "Converting JGeometry to STRUCT." ); 225 STRUCT struct = null; 226 try { 227 struct = storeGeometryWithMultiConnHack( jGeometry, conn ); 228 } catch ( SQLException e ) { 229 throw new DatastoreException( "Error converting JGeometry to STRUCT: " + e.getMessage(), e ); 230 } 231 return struct; 232 } 233 234 /** 235 * Workaround for a known Oracle JDBC driver problem. 236 * <p> 237 * JGeometry#store() isn't working when invoked successively using different connections. This method applies a 238 * workaround (based on undocumented behaviour of the Oracle driver) to solve this problem. 239 * http://forums.oracle.com/forums/thread.jspa?messageID=1273670 240 * </p> 241 * 242 * @param geometry 243 * geometry to be stored 244 * @param connection 245 * jdbc connection 246 * @return a {@link STRUCT} to be used as query parameter 247 * @throws SQLException 248 */ 249 private synchronized STRUCT storeGeometryWithMultiConnHack( JGeometry geometry, Connection connection ) 250 throws SQLException { 251 if ( lastStoreConnection != null && lastStoreConnection != connection ) { 252 LOG.logDebug( "JGeometry#store(...) workaround (lastStoreConnection != connection)" ); 253 try { 254 Field geomDesc = JGeometry.class.getDeclaredField( "geomDesc" ); 255 geomDesc.setAccessible( true ); 256 geomDesc.set( null, null ); 257 } catch ( Exception e ) { 258 LOG.logWarning( "Exception caught applying JGeometr#store(...) workaround: " + e.getMessage(), e ); 259 } 260 } 261 lastStoreConnection = connection; 262 return JGeometry.store( geometry, connection ); 263 } 264 265 /** 266 * Converts the given object from a <code>java.sql.ResultSet</code> column to the common type to be used as a 267 * feature property. 268 * <p> 269 * NOTE: String- and boolean-valued results have a special conversion handling: 270 * <ul> 271 * <li><code>Strings:</code> because we encountered difficulties when inserting empty strings "" into String-type 272 * columns with NOT NULL constraints (for example in VARCHAR2 fields), "$EMPTY_STRING$" is used to mark them.</li> 273 * <li><code>Boolean:<code>because Oracle has no special boolean type, it is assumed that a CHAR(1) column is used 274 * instead (with values 'Y'=true and 'N'=false)</li> 275 * </ul> 276 * 277 * @param rsObject 278 * @param sqlTypeCode 279 * @return an object that is suitable for a table column of the specified SQL type 280 * @throws DatastoreException 281 */ 282 @Override 283 public Object convertFromDBType( Object rsObject, int sqlTypeCode ) 284 throws DatastoreException { 285 Object propertyValue = rsObject; 286 try { 287 if ( rsObject instanceof TIMESTAMP ) { 288 propertyValue = ( (TIMESTAMP) rsObject ).timestampValue(); 289 } 290 if ( rsObject instanceof String ) { 291 if ( rsObject.equals( "$EMPTY_STRING$" ) ) { 292 propertyValue = ""; 293 } 294 if ( sqlTypeCode == Types.BOOLEAN ) { 295 String val = rsObject.toString(); 296 297 if ( val.length() == 1 && val.charAt( 0 ) == 'Y' ) { 298 propertyValue = Boolean.TRUE; 299 } 300 if ( val.length() == 1 && val.charAt( 0 ) == 'N' ) { 301 propertyValue = Boolean.FALSE; 302 } 303 } 304 } 305 } catch ( SQLException e ) { 306 throw new DatastoreException( e.getMessage(), e ); 307 } 308 return propertyValue; 309 } 310 311 /** 312 * Returns the next value of the given SQL sequence. 313 * 314 * @param conn 315 * JDBC connection to be used. 316 * @param sequence 317 * name of the SQL sequence 318 * @return next value of the given SQL sequence 319 * @throws DatastoreException 320 * if the value could not be retrieved 321 */ 322 @Override 323 public Object getSequenceNextVal( Connection conn, String sequence ) 324 throws DatastoreException { 325 326 Object nextVal = null; 327 Statement stmt = null; 328 ResultSet rs = null; 329 330 try { 331 try { 332 stmt = conn.createStatement(); 333 rs = stmt.executeQuery( "SELECT " + sequence + ".nextval FROM dual" ); 334 if ( rs.next() ) { 335 nextVal = rs.getObject( 1 ); 336 } 337 } finally { 338 try { 339 if ( rs != null ) { 340 rs.close(); 341 } 342 } finally { 343 if ( stmt != null ) { 344 stmt.close(); 345 } 346 } 347 } 348 } catch ( SQLException e ) { 349 String msg = "Could not retrieve value for sequence '" + sequence + "': " + e.getMessage(); 350 throw new DatastoreException( msg, e ); 351 } 352 return nextVal; 353 } 354 355 /** 356 * Returns the current value (plus an offset) of the given SQL sequence. 357 * 358 * @param conn 359 * JDBC connection to be used. 360 * @param sequence 361 * name of the SQL sequence 362 * @param offset 363 * offset added to the sequence value 364 * @return current value (plus offset) of the given SQL sequence 365 * @throws DatastoreException 366 * if the value could not be retrieved 367 */ 368 @Override 369 public Object getSequenceCurrValPlusOffset( Connection conn, String sequence, int offset ) 370 throws DatastoreException { 371 372 Object nextVal = null; 373 Statement stmt = null; 374 ResultSet rs = null; 375 376 try { 377 try { 378 stmt = conn.createStatement(); 379 rs = stmt.executeQuery( "SELECT " + sequence + ".currval + " + offset + " FROM dual" ); 380 if ( rs.next() ) { 381 nextVal = rs.getObject( 1 ); 382 } 383 } finally { 384 try { 385 if ( rs != null ) { 386 rs.close(); 387 } 388 } finally { 389 if ( stmt != null ) { 390 stmt.close(); 391 } 392 } 393 } 394 } catch ( SQLException e ) { 395 String msg = "Could not retrieve value for sequence '" + sequence + "': " + e.getMessage(); 396 throw new DatastoreException( msg, e ); 397 } 398 return nextVal; 399 } 400 401 /** 402 * Converts the {@link StatementBuffer} into a {@link PreparedStatement}, which is initialized and ready to be 403 * performed. 404 * 405 * TODO remove this method (use super class method instead), change handling of JGeometry NOTE: String- and 406 * boolean-valued results have a special conversion handling: 407 * <ul> 408 * <li><code>Strings:</code> because we encountered difficulties when inserting empty strings "" into String-type 409 * columns with NOT NULL constraints (for example in VARCHAR2 fields), "$EMPTY_STRING$" is used to mark them.</li> 410 * <li><code>Boolean:<code>because Oracle has no special boolean type, it is assumed that a CHAR(1) column is used 411 * instead (with values 'Y'=true and 'N'=false)</li> 412 * </ul> 413 * 414 * @param conn 415 * connection to be used to create the <code>PreparedStatement</code> 416 * @param statementBuffer 417 * @return the <code>PreparedStatment</code>, ready to be performed 418 * @throws SQLException 419 * if a JDBC related error occurs 420 */ 421 @Override 422 public PreparedStatement prepareStatement( Connection conn, StatementBuffer statementBuffer ) 423 throws SQLException { 424 LOG.logDebug( "Preparing statement: " + statementBuffer.getQueryString() ); 425 426 PreparedStatement preparedStatement = conn.prepareStatement( statementBuffer.getQueryString() ); 427 428 Iterator<StatementArgument> it = statementBuffer.getArgumentsIterator(); 429 int i = 1; 430 while ( it.hasNext() ) { 431 StatementArgument argument = it.next(); 432 Object parameter = argument.getArgument(); 433 int targetSqlType = argument.getTypeCode(); 434 if ( parameter != null ) { 435 if ( targetSqlType == Types.DATE ) { 436 if ( parameter instanceof String ) { 437 parameter = TimeTools.createCalendar( (String) parameter ).getTime(); 438 } 439 parameter = new java.sql.Date( ( (Date) parameter ).getTime() ); 440 } else if ( targetSqlType == Types.TIMESTAMP ) { 441 if ( parameter instanceof String ) { 442 parameter = TimeTools.createCalendar( (String) parameter ).getTime(); 443 } 444 parameter = new java.sql.Timestamp( ( (Date) parameter ).getTime() ); 445 } else if ( parameter != null && parameter instanceof JGeometry ) { 446 parameter = storeGeometryWithMultiConnHack( (JGeometry) parameter, conn ); 447 } else if ( targetSqlType == Types.INTEGER || targetSqlType == Types.SMALLINT 448 || targetSqlType == Types.TINYINT ) { 449 parameter = Integer.parseInt( parameter.toString() ); 450 } else if ( targetSqlType == Types.DECIMAL || targetSqlType == Types.DOUBLE 451 || targetSqlType == Types.REAL || targetSqlType == Types.FLOAT ) { 452 parameter = Double.parseDouble( parameter.toString() ); 453 } else if ( targetSqlType == Types.NUMERIC ) { 454 try { 455 parameter = Integer.parseInt( parameter.toString() ); 456 } catch ( Exception e ) { 457 parameter = Double.parseDouble( parameter.toString() ); 458 } 459 } else if ( targetSqlType == Types.BOOLEAN ) { 460 // Oracle does not have a BOOLEAN datatype 461 // default maping to column of type CHAR(1) 462 // http://thinkoracle.blogspot.com/2005/07/oracle-boolean.html 463 targetSqlType = Types.CHAR; 464 if ( Boolean.parseBoolean( parameter.toString() ) ) { 465 parameter = "Y"; 466 } else { 467 parameter = "N"; 468 } 469 } else if ( parameter instanceof String ) { 470 // Using the empty string ("") for NOT NULL columns fails 471 // (at least using PreparedStatements) 472 // TODO implement a proper solution 473 if ( ( (String) parameter ).length() == 0 ) { 474 parameter = "$EMPTY_STRING$"; 475 } 476 } 477 if ( LOG.getLevel() == ILogger.LOG_DEBUG ) { 478 try { 479 String typeName = Types.getTypeNameForSQLTypeCode( targetSqlType ); 480 LOG.logDebug( "Setting argument " + i + ": type=" + typeName + ", value class=" 481 + parameter.getClass() ); 482 if ( parameter instanceof String || parameter instanceof Number 483 || parameter instanceof java.sql.Date ) { 484 LOG.logDebug( "Value: '" + parameter + "'" ); 485 } 486 } catch ( UnknownTypeException e ) { 487 throw new SQLException( e.getMessage() ); 488 } 489 } 490 preparedStatement.setObject( i, parameter, targetSqlType ); 491 } else { 492 setNullValue( preparedStatement, i, targetSqlType ); 493 } 494 i++; 495 } 496 return preparedStatement; 497 } 498 499 /** 500 * Transforms the incoming {@link Query} so that the {@link CoordinateSystem} of all spatial arguments (BBOX, etc.) 501 * in the {@link Filter} match the SRS of the targeted {@link MappingGeometryField}s. 502 * <p> 503 * NOTE: If this transformation can be performed by the backend (e.g. by Oracle Spatial), this method should be 504 * overwritten to return the original input {@link Query}. 505 * 506 * @param query 507 * query to be transformed 508 * @return query with spatial arguments transformed to target SRS 509 */ 510 @Override 511 protected Query transformQuery( Query query ) { 512 return query; 513 } 514 515 /** 516 * Transforms the {@link FeatureCollection} so that the geometries of all contained geometry properties use the 517 * requested SRS. 518 * 519 * @param fc 520 * feature collection to be transformed 521 * @param targetSRS 522 * requested SRS 523 * @return transformed FeatureCollection 524 */ 525 @Override 526 protected FeatureCollection transformResult( FeatureCollection fc, String targetSRS ) { 527 return fc; 528 } 529 530 /** 531 * Returns whether the datastore is capable of performing a native coordinate transformation (using an SQL function 532 * call for example) into the given SRS. 533 * 534 * @param targetSRS 535 * target spatial reference system (usually "EPSG:XYZ") 536 * @return true, if the datastore can perform the coordinate transformation, false otherwise 537 */ 538 @Override 539 protected boolean canTransformTo( String targetSRS ) { 540 return getNativeSRSCode( targetSRS ) != SRS_UNDEFINED; 541 } 542 543 /** 544 * Returns an {@link SQLFunctionCall} that refers to the given {@link MappingGeometryField} in the specified target 545 * SRS using a database specific SQL function. 546 * 547 * @param geoProperty 548 * geometry property 549 * @param targetSRS 550 * target spatial reference system (usually "EPSG:XYZ") 551 * @return an {@link SQLFunctionCall} that refers to the geometry in the specified srs 552 * @throws DatastoreException 553 */ 554 @Override 555 public SQLFunctionCall buildSRSTransformCall( MappedGeometryPropertyType geoProperty, String targetSRS ) 556 throws DatastoreException { 557 558 int nativeSRSCode = getNativeSRSCode( targetSRS ); 559 if ( nativeSRSCode == SRS_UNDEFINED ) { 560 String msg = Messages.getMessage( "DATASTORE_SQL_NATIVE_CT_UNKNOWN_SRS", this.getClass().getName(), 561 targetSRS ); 562 throw new DatastoreException( msg ); 563 } 564 565 MappingGeometryField field = geoProperty.getMappingField(); 566 FunctionParam param1 = new FieldContent( field, new TableRelation[0] ); 567 FunctionParam param2 = new ConstantContent( "" + nativeSRSCode ); 568 569 SQLFunctionCall transformCall = new SQLFunctionCall( "SDO_CS.TRANSFORM($1,$2)", field.getType(), param1, param2 ); 570 return transformCall; 571 } 572 573 @Override 574 public String buildSRSTransformCall( String geomIdentifier, int nativeSRSCode ) 575 throws DatastoreException { 576 String call = "SDO_CS.TRANSFORM(" + geomIdentifier + "," + nativeSRSCode + ")"; 577 return call; 578 } 579 580 @Override 581 public int getNativeSRSCode( String srsName ) { 582 Integer nativeSRSCode = nativeSrsCodeMap.get( srsName ); 583 if ( nativeSRSCode == null ) { 584 return SRS_UNDEFINED; 585 } 586 return nativeSRSCode; 587 } 588 589 private void setNullValue( PreparedStatement preparedStatement, int i, int targetSqlType ) 590 throws SQLException { 591 if ( LOG.getLevel() == ILogger.LOG_DEBUG ) { 592 try { 593 String typeName = Types.getTypeNameForSQLTypeCode( targetSqlType ); 594 LOG.logDebug( "Setting argument " + i + ": type=" + typeName ); 595 LOG.logDebug( "Value: null" ); 596 } catch ( UnknownTypeException e ) { 597 throw new SQLException( e.getMessage() ); 598 } 599 } 600 preparedStatement.setNull( i, targetSqlType ); 601 } 602 603 private static void initSRSCodeMap() 604 throws IOException { 605 InputStream is = OracleDatastore.class.getResourceAsStream( SRS_CODE_PROP_FILE ); 606 Properties props = new Properties(); 607 props.load( is ); 608 for ( Object key : props.keySet() ) { 609 String nativeCodeStr = props.getProperty( (String) key ).trim(); 610 try { 611 int nativeCode = Integer.parseInt( nativeCodeStr ); 612 nativeSrsCodeMap.put( (String) key, nativeCode ); 613 } catch ( NumberFormatException e ) { 614 String msg = Messages.getMessage( "DATASTORE_SRS_CODE_INVALID", SRS_CODE_PROP_FILE, nativeCodeStr, key ); 615 throw new IOException( msg ); 616 } 617 } 618 } 619 }