Mercurial > dive4elements > gnv-client
changeset 557:05fcb3c553fd 0.4
merged geo-backend/0.4
| author | Thomas Arendsen Hein <thomas@intevation.de> |
|---|---|
| date | Fri, 28 Sep 2012 12:13:50 +0200 |
| parents | 80630520e25a (current diff) 48121fa4dc95 (diff) |
| children | 12abbf15f18a |
| files | |
| diffstat | 47 files changed, 7418 insertions(+), 0 deletions(-) [+] |
line wrap: on
line diff
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/geo-backend/ChangeLog Fri Sep 28 12:13:50 2012 +0200 @@ -0,0 +1,439 @@ +2010-01-27 Hans Plum <hans@intevation.de> + + RELEASE 0.4 + + * Changes, NEWS, ChangeLog: Summarized changes. + +2009-01-25 Sascha L. Teichmann <sascha.teichmann@intevation.de> + + Fix for gnv/issue107 + + * src/main/java/de/intevation/gnv/geobackend/util/RedundancyRemover.java: + New. Used to reduce the redundancy of data in SQL result sets. Column + values are very distinct so the degree or redundancy is very high. E.g + the SDE produces for each date value a java.util.GregorianCalendar object + which are very heavy weighted. We only use them r/o so we can hash each date + value to one unique representative. Same logic applys to integer values + and so on. + + With this technique we are able to reduce the memory consumption by + over 90%. Because it is not very feasible to store the whole history + of column we only have a limited number of cached values per column. + This is controlled via the system property + "de.intevation.gnv.geobackend.util.RedundancyRemover.lookback" which + defaults to 1029. + + * src/main/java/de/intevation/gnv/geobackend/sde/datasources/ArcSDEStatement.java: + Use RedundancyRemovers to filter the incoming traffic from SDE. + +2010-01-26 Tim Englich <tim.englich@intevation.de> + + * src/test/java/de/intevation/gnv/geobackend/base/query/ToCharSample.java : + Added Sample for bug in ArcSDE 9.3 using to_char-Function with SpatialQuery + * src/test/java/de/intevation/gnv/geobackend/base/query/GroupBySample.java : + Added Sample for Bug in ArcSDE using group by with SpatialQuery + +2009-01-25 Sascha L. Teichmann <sascha.teichmann@intevation.de> + + Another attempt to fix gnv/issue34 + + * src/main/java/de/intevation/gnv/geobackend/base/DefaultResultDescriptor.java: + Add a toString() method. + + * src/main/java/de/intevation/gnv/geobackend/sde/datasources/ArcSDEConnection.java: + Added an idle time detection mechanism. You can call touch() on + a connection to refresh the internal timestamp and ask with isActive() + if a constructor given time interval in milliseconds is exceeded. + + * src/main/java/de/intevation/gnv/geobackend/sde/connectionpool/ArcSDEPoolableObjectFactory.java: + Added a check of inactivity to validateObject() before relying on SDE logic. + This is the last line of defence if the testServer() call is going not to respond. + This duplicates the eviction policy of Apache Commons pool a bit but I found + this way to be more trustworthy. + + The idle time is configure with the property 'serverInactiveInterval'. + units: seconds. Defaults to 5 minutes. + + The unit of 'serverRoundtripInterval' (parameter of testServer()) is now + in seconds, too. Default: 5 seconds. This is the value used in GeoTools. + +2009-01-09 Sascha L. Teichmann <sascha.teichmann@intevation.de> + + * src/main/java/de/intevation/gnv/geobackend/sde/datasources/SDEQuery.java, + src/main/java/de/intevation/gnv/geobackend/sde/datasources/ArcSDEStatement.java, + src/main/java/de/intevation/gnv/geobackend/sde/datasources/RasterObject.java: + Cleanup imports. + +2010-01-15 Tim Englich <tim.englich@intevation.de> + + * src/main/java/de/intevation/gnv/geobackend/sde/datasources/ArcSDEConnection.java (isValid): + Implemented the isValid-method of the ArcSDEConnection for + getting a propper Validation of the used SEConnection. + For the Validation the Method testServer of the SEConnection is used. + + * src/main/java/de/intevation/gnv/geobackend/sde/connectionpool/ArcSDEPoolableObjectFactory.java (ArcSDEPoolableObjectFactory): + Added Member serverRoundtripInterval to represent the Time in + Milliseconds which is allowed to reused a returned Connection. + Also used Connection.isValid Method instead of Connection.isClosed + to validate the Connection befor it could be borrowed from the Pool. + +2009-01-12 Sascha L. Teichmann <sascha.teichmann@intevation.de> + + * src/main/java/de/intevation/gnv/geobackend/sde/datasources/RasterObject.java: + Added bicubic spline interpolation on raster tiles. This + should increase the 'virtual' resolution more than the + bilinear interpolation. To enable it write 'bicubic' into + the configuration file. + + * pom.xml: Added dependency to Apache Common Math 2.0 for + the cubic spline interpolation. + +2009-01-09 Sascha L. Teichmann <sascha.teichmann@intevation.de> + + * src/main/java/de/intevation/gnv/geobackend/sde/datasources/RasterObject.java: + Added static method to fetch matching interpolation type for + given string. Defaults to nearest neighbor if no string is matching. + +2009-01-09 Sascha L. Teichmann <sascha.teichmann@intevation.de> + + * src/main/java/de/intevation/gnv/geobackend/sde/datasources/RasterObject.java: + Implemented bilinear interpolation. + +2009-01-09 Sascha L. Teichmann <sascha.teichmann@intevation.de> + + * src/main/java/de/intevation/gnv/geobackend/sde/datasources/ArcSDEStatement.java: + Fixed transformations to figure out the right tile in the SDE. + Taking the tile origin into account now and does not do wrong rounding + of column and row indices. Simplified the code a lot. + + * src/main/java/de/intevation/gnv/geobackend/sde/datasources/RasterObject.java: + Fixed access to right right pixel now. Removed the envelope code because + its not needed any longer. You can ask a RasterObject directly now if + a given coordinate is on the tile now. The tile space is now correctly + spanned between (0, 0) and (tile width - 1, tile height - 1) now which + allows other access patterns like bilinear interpolation. TODO: implement + bilinear interpolation. + +2010-01-08 Tim Englich <tim.englich@intevation.de> + + * src/main/java/de/intevation/gnv/geobackend/sde/datasources/SDEQuery.java, + src/main/java/de/intevation/gnv/geobackend/sde/datasources/Row.java, + src/main/java/de/intevation/gnv/geobackend/sde/datasources/ArcSDEStatement.java, + src/main/java/de/intevation/gnv/geobackend/sde/datasources/ArcSDEConnection.java, + src/main/java/de/intevation/gnv/geobackend/base/connectionpool/ConnectionPoolFactory.java (initializeConnectionPool): + Removed Encodingerrors from listed Files. All Files are now UTF-8 compliant. + +2010-01-05 Tim Englich <tim.englich@intevation.de> + + * src/main/java/de/intevation/gnv/geobackend/sde/datasources/RasterObject.java: + Made it serializable to be cachable beyond reboot. + +2010-01-05 Tim Englich <tim.englich@intevation.de> + + * src/main/java/de/intevation/gnv/geobackend/sde/datasources/ArcSDEStatement.java (handleResultSet): + Switched to return the RasterObject instead of the depth in an Rasterquery. + This should help to get a better performance. + * src/main/java/de/intevation/gnv/geobackend/sde/datasources/RasterObject.java (RasterObject): + Added new Object for Caching Rastertiles and all Informations that are + required for calculating the Depth to a given Coordinate. + +2010-01-04 Tim Englich <tim.englich@intevation.de> + + * src/main/java/de/intevation/gnv/geobackend/sde/datasources/ArcSDEStatement.java (handleResultSet): + Added Rasterdatasupport to the ARCSDESTATEMENT. + Now it is possible to Query the Value of an Pixel like that: + SELECT ST_ASTEXT(RASTER) FROM MEDIAN.TOPO_BS_D_6X10SEC where INTERSECTS(RASTER, "POINT( 12.084721594184593 54.35583351483295)") + +2009-12-19 Sascha L. Teichmann <sascha.teichmann@intevation.de> + + * contrib/sql-cache.xml: Configured disk storage to survive restarts. + + * src/main/java/de/intevation/gnv/geobackend/base/query/QueryExecutorFactory.java: + Added explicit shutdown hook. + + * src/main/java/de/intevation/gnv/geobackend/base/query/CachingQueryExecutorFactory.java: + Fixed spelling of cacheResults() which prevented results to be stored in cache. + Added some logging. + +2009-12-18 Sascha L. Teichmann <sascha.teichmann@intevation.de> + + Experimental caching of SQL results via Ehcache + + * pom.xml: Added dependency to Ehcache + + * src/main/java/de/intevation/gnv/geobackend/base/query/QueryExecutor.java: + Extended interface to support caching by SQL statements as Strings. + + * src/main/java/de/intevation/gnv/geobackend/base/query/QueryExecutorBase.java: + Implements new interface with no caching + + * src/main/java/de/intevation/gnv/geobackend/base/query/container/QueryContainerFactory.java: + synchronized access to singleton. + + * src/main/java/de/intevation/gnv/geobackend/base/query/DefaultQueryExceutor.java: + calls caching methods of base class. + + * src/main/java/de/intevation/gnv/geobackend/base/query/QueryExecutorFactory.java: + When system property 'query.executor.factory' is given the value is used as + a class name to be instantiated as a sub class of QueryExecutorFactory. + This happens if the singleton of QueryExecutorFactory is created. With the + mechanism it is possible to replace the standard behavior of creating QueryExecutors. + + * src/main/java/de/intevation/gnv/geobackend/base/query/CachingQueryExecutorFactory.java: + New: This class extends QueryExecutorFactory and can be used as replacement for + the standard QueryExecutorFactory. Usage: + + -Dquery.executor.factory=de.intevation.gnv.geobackend.base.query.CachingQueryExecutorFactory + + If you pass in another system property 'caching.query.executor.config' you can + customize the caching. The value is used as a file name to an XML file with + Ehcache configuration. + + * contrib/sql-cache.xml: Demo configuration for sql caching. Usage: + + -Dquery.executor.factory=de.intevation.gnv.geobackend.base.query.CachingQueryExecutorFactory + -Dcaching.query.executor.config=contrib/sql-cache.xml + +2009-12-17 Sascha L. Teichmann <sascha.teichmann@intevation.de> + + RELEASE 0.3 + + * Changes, NEWS, ChangeLog: Summarized changes. + +2009-12-17 Ingo Weinzierl <ingo.weinzierl@intevation.de> + + * src/main/java/de/intevation/gnv/geobackend/base/DefaultResult.java: + Fixed bug in DefaultResult when querying an index out of bounds. + +2009-12-15 Tim Englich <tim.englich@intevation.de> + + * src/main/java/de/intevation/gnv/geobackend/sde/datasources/ArcSDEStatement.java (executeQuery): + We have to use a different SpatialFilter for Point in Polygon Queries as + in AreaIntersection Statements. + So we have to include a switch which will do that. + * src/main/java/de/intevation/gnv/geobackend/sde/datasources/ArcSDEUtils.java (createPoints): + Added Support of Point-Objects for the possibility to execute a Point in Pologon + Statement using the ArcSDE. + +2009-12-11 Ingo Weinzierl <ingo.weinzierl@intevation.de> + + * src/main/java/de/intevation/gnv/geobackend/base/DefaultResult.java: + DefaultResult will return null if it doesn't contain the specified column + name in method getString(String columnName), instead of throwing an + exception. + +2009-12-11 Sascha L. Teichmann <sascha.teichmann@intevation.de> + + * src/main/java/de/intevation/gnv/geobackend/base/DefaultResultDescriptor.java, + src/main/java/de/intevation/gnv/geobackend/base/query/container/QueryContainer.java, + src/main/java/de/intevation/gnv/geobackend/base/query/container/DefaultQueryContainer.java: + Removed needless imports. + +2009-11-30 Tim Englich <tim.englich@intevation.de> + + * src/main/java/de/intevation/gnv/geobackend/sde/datasources/SDEResultSet.java, + src/main/java/de/intevation/gnv/geobackend/sde/datasources/ArcSDEStatement.java, + src/main/java/de/intevation/gnv/geobackend/sde/datasources/ArcSDEResultSetMetaData.java, + src/main/java/de/intevation/gnv/geobackend/sde/datasources/ArcSDEConnection.java: + Added missing Methodstubs for Java 1.6 compability. + ################################################### + ###### ATTENTION: ONLY USE JAVA 1.6 FOR THIS ###### + ###### AND ALL USING MODULES. ###### + ################################################### + +2009-11-27 Tim Englich <tim.englich@intevation.de> + + * src/test/ressources/QueryExecutorTestCase.properties, + src/test/java/de/intevation/gnv/geobackend/base/query/QueryExecutorTestCase.java: + Revert Changes of the commit of Revision 377 which was unintended + * src/main/java/de/intevation/gnv/geobackend/sde/datasources/ArcSDEStatement.java (executeQuery): + Changed Splitalgorithm of Statements so that it is Possible to add to_date Colums into a Statement + with Spatial-Columns or Spatial-Filters + +2009-11-13 Hans Plum <hans@intevation.de> + + RELEASE 0.2 + + * Changes, NEWS: Summarized changes. + +2009-11-13 Hans Plum <hans@intevation.de> + + * Made some ChangeLog entries a bit clearer. + +2009-11-10 Tim Englich <tim.englich@intevation.de> + + * src/main/java/de/intevation/gnv/geobackend/sde/datasources/ArcSDEStatement.java (executeQuery): + Changed the splitting of the Statements for ARCSDE-Transformation. + Now it is possible to use a Select-Statement as an Table. + +2009-11-06 Ingo Weinzierl <ingo.weinzierl@intevation.de> + + * target/**: Removed. Should not be in version control. + +2009-11-05 Tim Englich <tim.englich@intevation.de> + + * src/test/ressources/QueryExecutorTestCase.properties, + src/test/java/de/intevation/gnv/geobackend/base/query/QueryExecutorTestCase.java (testSpatialQueryWithInnerSelect): + Added an Tescase to test the InnerSelect Statments in Spatial-Queries. + * src/main/java/de/intevation/gnv/geobackend/sde/datasources/Row.java (getPosValue): + Bugfix. An Point could not have an Z-Coordinate-Value + * src/main/java/de/intevation/gnv/geobackend/sde/datasources/ArcSDEStatement.java (executeQuery): + Added the possibility to execute Spatial-Queries using InnerStatements instead + of WKT in Intersects-where-clauses. + +2009-11-02 Tim Englich <tim.englich@intevation.de> + + * src/main/java/de/intevation/gnv/geobackend/sde/datasources/ArcSDEConnection.java (createStatement): + Changed TODO to a question + * src/main/java/de/intevation/gnv/geobackend/sde/datasources/ArcSDEStatement.java (executeQuery): + CodeCleanup: Removed deprecated TODO + * src/main/java/de/intevation/gnv/geobackend/sde/datasources/SDEResultSet.java (getColumnIndex): + Removed Typo. + * src/main/java/de/intevation/gnv/geobackend/sde/connectionpool/ArcSDEPoolableObjectFactory.java: + CodeCleanup: Removed deprecated TODOS and replace Tabs with whitespaces. + * src/main/java/de/intevation/gnv/geobackend/base/query/container/DefaultQueryContainer.java:, + src/main/java/de/intevation/gnv/geobackend/base/query/container/QueryContainer.java: + CodeCleanup: Removed unused Method getQueryIds + * src/main/java/de/intevation/gnv/geobackend/sde/connectionpool/ArcSDEConnectionPool.java (closeConnection): + CodeCleanup: Inspect TODO and ansewerd the Question that the Connection has not to + be closed at that position but perhaps insite the ConnectionPool. + +2009-10-30 Tim Englich <tim.englich@intevation.de> + + * src/main/java/de/intevation/gnv/geobackend/sde/datasources/Row.java (getPosValue): + Added Support for Polygons without interior Rings. This is required for generating + Horizontal Cross-Section Outputs. + The Z-Value of an Point was also added to the WKT-Represantation. + +2009-10-29 Tim Englich <tim.englich@intevation.de> + + Issue 45: Defintion of gaps in temporal and spatial data + + * src/main/java/de/intevation/gnv/geobackend/base/DefaultResult.java (getInteger): + Added Integer-Support for DoubleValues because the ArcSDE does + not read values like -1 as Integer-values. issue45 + +2009-10-20 Tim Englich <tim.englich@intevation.de> + + Issue 57: Improving memory usage of data objects + + * src/main/java/de/intevation/gnv/geobackend/base/query/QueryExecutorBase.java (QueryExecutorBase), + src/main/java/de/intevation/gnv/geobackend/base/ResultDescriptor.java (getColumnClassName), + src/main/java/de/intevation/gnv/geobackend/base/Result.java, + src/main/java/de/intevation/gnv/geobackend/base/DefaultResultDescriptor.java (serialVersionUID), + src/main/java/de/intevation/gnv/geobackend/base/DefaultResult.java (getDate): + Integrated Patch of issue57 to get some Memoryusage-improvements + +2009-10-19 Sascha L. Teichmann <sascha.teichmann@intevation.de> + + * src/main/java/de/intevation/gnv/geobackend/base/DefaultResult.java: + Extends from HashMap directly to avoid one level of indirection and + do not create so much needless objects. + + TODO: The representation in form of HashMaps is not very efficent! + Use array with index instead. + +2009-10-14 Tim Englich <tim.englich@intevation.de> + + * src/test/java/de/intevation/gnv/geobackend/base/query/QueryExecutorTestCase.java (testSpatialQueryWithoutIntersects), + src/test/ressources/QueryExecutorTestCase.properties: + Added further Queries for Testing Spatial-Query-Support + +2009-10-14 Tim Englich <tim.englich@intevation.de> + + * src/main/java/de/intevation/gnv/geobackend/sde/datasources/Row.java (getPosValue): + Add WKT-Syntaxt to Shape ReturnValue + * src/test/ressources/QueryExecutorTestCase.properties: + Changed Test-Query-Statement for Spatial-Queries with INNERJOINS and ORDER BY + * src/test/java/de/intevation/gnv/geobackend/base/query/QueryExecutorTestCase.java (testSpatialQueryWithoutIntersects): + Changed expected Result-Size because of Changes in the QueryStatement which is used. + * src/main/java/de/intevation/gnv/geobackend/sde/datasources/ArcSDEStatement.java (executeQuery): + Added INNERJOIN, ORDER BY and GROUP BY support to Spatial Queries + +2009-10-13 Tim Englich <tim.englich@intevation.de> + + * src/test/java/de/intevation/gnv/geobackend/base/query/QueryExecutorTestCase.java (testSpatialQueryWithoutIntersects): + Added an TaesCase for Querying spatial Data without an spatial restriction + * src/test/ressources/QueryExecutorTestCase.properties: + Added TestQuery with no spatial restriction + * src/main/java/de/intevation/gnv/geobackend/sde/datasources/ArcSDEStatement.java (executeQuery): + Now it is possible to send spatial Quieries without an spatial restriction in the where clause. + +2009-10-13 Tim Englich <tim.englich@intevation.de> + + Issue 34: SQL Exception in REST Server when accessing geo-backend + + * src/main/java/de/intevation/gnv/geobackend/sde/connectionpool/ArcSDEPoolableObjectFactory.java (validateObject) Edited: + Added Tests if the Object is Closed. And add propper instance Tests + * src/main/java/de/intevation/gnv/geobackend/sde/connectionpool/ArcSDEConnectionPool.java (initialize): + Added further ConfigurationParameters that could be set in the *.properties-File. + maxWait, maxIdle, minIdle, testOnBorrow, testOnReturn, testWhileIdle, numTestsPerEvictionRun, + timeBetweenEvictionRunsMillis, testWhileIdle + * src/test/ressources/ArcSDEConnectionPoolTestCase.properties: + Added som Configurationproperties for testing. + + +2009-10-13 Tim Englich <tim.englich@intevation.de> + * src/test/java/de/intevation/gnv/geobackend/base/query/QueryExecutorTestCase.java (QueryExecutorTestCase): + Changed expected Result of Spatial-Query-TestCase because of Changes to the used Query + * src/test/ressources/QueryExecutorTestCase.properties: + Added SFS-Conformal Query + * pom.xml: + Added dependency to JTS 1.9 + * src/main/java/de/intevation/gnv/geobackend/sde/datasources/ArcSDEStatement.java (executeQuery): + Added Spatial-Query-Support for ArcSDE + * src/main/java/de/intevation/gnv/geobackend/sde/datasources/ArcSDEUtils.java (createPoints): + Switched Method Signature to JTS-Geometry-Support + * src/main/java/de/intevation/gnv/geobackend/sde/datasources/Row.java (getStringValue): + Added String-Support for GeometryObjects + +2009-10-07 Sascha L. Teichmann <sascha.teichmann@intevation.de> + + RELEASE 0.1 + + * Changes, NEWS: Summarized changes. + +2009-10-06 Sascha L. Teichmann <sascha.teichmann@intevation.de> + + * Changelog -> ChangeLog: Renamed to make eclipse happy. Unified changelog style. + + * src/main/java/de/intevation/gnv/geobackend/sde/datasources/SDEQuery.java, + src/main/java/de/intevation/gnv/geobackend/sde/datasources/ArcSDEStatement.java: + Removed needless imports. + + * pom.xml: xmllint-ed + +2009-09-29 Tim Englich <tim.englich@intevation.de> + + * src/main/java/de/intevation/gnv/geobackend/util/DateUtils.java (DATE_PATTERN) Edited: + Make the Constants assessible for other Parts of the Project to reduce the TouchPoints for + Dateformats. + +2009-09-24 Tim Englich <tim.englich@intevation.de> + + * src/main/java/de/intevation/gnv/geobackend/base/ResultDescriptor.java Edited, + src/main/java/de/intevation/gnv/geobackend/base/Result.java Edited, + src/main/java/de/intevation/gnv/geobackend/base/DefaultResultDescriptor.java Edited, + src/main/java/de/intevation/gnv/geobackend/base/DefaultResult.java Edited: + Make Classes Serializable for Storing in ArtifactDatabase + +2009-09-21 Tim Englich <tim.englich@intevation.de> + + * src/main/java/de/intevation/gnv/geobackend/base/DefaultResult.java: + Added formatted Date Handling in getString Method + * src/test/ressources/QueryExecutorTestCase.properties Edited, + src/test/java/de/intevation/gnv/geobackend/base/query/QueryExecutorTestCase.java Edited: + Some tests for Spatialqueries added + * src/main/java/de/intevation/gnv/geobackend/base/query/DefaultQueryExceutor.java Edited: + Put the final Querystring into a Logmessage + +2009-09-09 Tim Englich <tim.englich@intevation.de> + + * src/main/java/de/intevation/gnv/geobackend/base/DefaultResult.java (getDate): + . Intance Check for DateValues + * src/main/java/de/intevation/gnv/geobackend/sde/datasources/SDEResultSet.java (next): + Logging removed because the performance slowdown fetching many ResultSets + +2009-09-04 Tim Englich <tim.englich@intevation.de> + + * pom.xml:
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/geo-backend/Changes Fri Sep 28 12:13:50 2012 +0200 @@ -0,0 +1,66 @@ +2010-01-27 RELEASE 0.4 + + New: + + * Experimental Cache for database queries and + results of parameterisation: Database queries are + transparent against cache. + + * Support for raster data access to SDE via RasterObject + + * Added interpolation for bilinear and bicubic + spline interpolation on rasters + + * Improved connection pooling for re-use of connections (#34) + + * Improved memory consumption of SQL results produced by SDE by + over 90% while removing redundancies for objects (especially + date objects): e.g. from 256 MB per Interpolation to 15 MB. (#107) + + +2009-12-17 RELEASE 0.3 + + New: + + * !!!! Java6 is mandatory now! Older version are not supported any longer !!! + * Added "point on polygon" spatial query support. + + Fixed: + + * Avoid index out of bounds exception in some case when accessing database + columns that are not provided by the database result set. + + +2009-11-13 RELEASE 0.2 + + New: + + * Added spatial query support for the first use cases. It uses OGC Simple Features for + SQL Syntax (SFS) to the inside and OGC WKT (Well Known Text) for + geometry respresentations + + * Enhanced configuration to ArcSDE Backend in Pool-Mechanism for + fine-tuning interaction and pooling. + + * Added support for INNERJOIN, ORDER BY and GROUP BY for spatial + queries (needs more testing) + + * Support for gaps in temporal and spatial data added. + + Improvements: + + * Improved memory handling of Backend-results in geo-backend + + * Stabilized binding to ArcSDE Backend (issue 34), + https://roundup-intern.intevation.de/gnv/issue34 + + + +2009-10-07 RELEASE 0.1 + + New: + + * Imported proto type database access layer. + * Made access layer look like java.sql.* + +
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/geo-backend/NEWS Fri Sep 28 12:13:50 2012 +0200 @@ -0,0 +1,45 @@ +2010-01-27 RELEASE 0.4 + + * Support for raster data access to SDE via RasterObject + + * Added interpolation for bilinear and bicubic + spline interpolation on rasters + + * Improved connection pooling for re-use of connections (#34) + + * Improved memory consumption of SQL results produced by SDE by + over 90% while removing redundancies for objects (especially + date objects): e.g. from 256 MB per Interpolation to 15 MB. (#107) + + * Experimental Cache for database queries and + results of parameterisation: Database queries are + transparent against cache. + Acceleration by at least factor 5-8 depending on circumstances. + + +2009-12-17 RELEASE 0.3 + + New: + + * !!!! Java6 is mandatory now! Older version are not supported any longer !!! + The backend now stubs the Java6 SQL API which is nor compability with + earlier versions. + +2009-11-13 RELEASE 0.2 + + New: + + * Added first support for spatial queries for time series, + vertical profile, horizontal profile, vertical cross section + and horizontal cross section. + + * Improved memory footprint of data objects in geo-backend. + + * Stabilized binding to ArcSDE datasources. + + +2009-10-07 RELEASE 0.1 + + New: + + * Initial version of the database access layer.
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/geo-backend/contrib/sql-cache.xml Fri Sep 28 12:13:50 2012 +0200 @@ -0,0 +1,15 @@ +<?xml version="1.0" encoding="UTF-8"?> +<ehcache> + <diskStore path="java.io.tmpdir/sql-cache"/> + <defaultCache + maxElementsInMemory="100" + eternal="false" + overflowToDisk="true" + memoryStoreEvictionPolicy="LFU" + diskPersistent="true" + maxElementsOnDisk="512" + timeToIdleSeconds="10800" + timeToLiveSeconds="14400" + /> +</ehcache> +
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/geo-backend/pom.xml Fri Sep 28 12:13:50 2012 +0200 @@ -0,0 +1,87 @@ +<?xml version="1.0"?> +<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/maven-v4_0_0.xsd"> + <modelVersion>4.0.0</modelVersion> + <groupId>de.intevation.bsh</groupId> + <artifactId>geo-backend</artifactId> + <packaging>jar</packaging> + + <version>1.0-SNAPSHOT</version> + <name>geo-backend</name> + <url>http://maven.apache.org</url> + <build> + <plugins> + <plugin> + <groupId>org.apache.maven.plugins</groupId> + <artifactId>maven-eclipse-plugin</artifactId> + <version>2.5.1</version> + </plugin> + <plugin> + <groupId>org.apache.maven.plugins</groupId> + <artifactId>maven-compiler-plugin</artifactId> + <version>2.0.2</version> + <configuration> + <source>1.5</source> + <target>1.5</target> + </configuration> + </plugin> + </plugins> + </build> + <dependencies> + <dependency> + <groupId>junit</groupId> + <artifactId>junit</artifactId> + <version>3.8.1</version> + <scope>test</scope> + </dependency> + <dependency> + <groupId>com.esri.sde</groupId> + <artifactId>jsde_sdk</artifactId> + <version>9.2.5</version> + <scope>compile</scope> + </dependency> + <dependency> + <groupId>com.esri.sde</groupId> + <artifactId>jpe_sdk</artifactId> + <version>9.2.5</version> + <scope>compile</scope> + </dependency> + <dependency> + <groupId>com.ibm</groupId> + <artifactId>icu4j</artifactId> + <version>3.2</version> + <scope>compile</scope> + </dependency> + <dependency> + <groupId>log4j</groupId> + <artifactId>log4j</artifactId> + <version>[1.2,)</version> + </dependency> + <dependency> + <groupId>commons-pool</groupId> + <artifactId>commons-pool</artifactId> + <version>1.5.2</version> + </dependency> + <dependency> + <groupId>org.apache.commons</groupId> + <artifactId>commons-math</artifactId> + <version>2.0</version> + </dependency> + <dependency> + <groupId>com.vividsolutions</groupId> + <artifactId>jts</artifactId> + <version>1.9</version> + </dependency> + <dependency> + <groupId>net.sf.ehcache</groupId> + <artifactId>ehcache</artifactId> + <version>1.6.2</version> + </dependency> + </dependencies> + <repositories> + <repository> + <id>gt2.repo</id> + <name>GeoTools2 Repository including JTS</name> + <url>http://maven.geotools.fr/repository</url> + </repository> + </repositories> +</project>
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/geo-backend/src/main/java/de/intevation/gnv/geobackend/base/DefaultResult.java Fri Sep 28 12:13:50 2012 +0200 @@ -0,0 +1,153 @@ +/** + * + */ +package de.intevation.gnv.geobackend.base; + +import java.util.Date; +import java.util.GregorianCalendar; +import java.util.HashMap; + +import de.intevation.gnv.geobackend.util.DateUtils; + +/** + * Defaultimplementation of the Interface Result. + * This Class stores the Attributevalues of one Result. + * @author Tim Englich <tim.englich@intevation.de> + * + */ +public class DefaultResult +implements Result +{ + /** + * THE UID of this Classe + */ + private static final long serialVersionUID = -6886218808840982766L; + + + /** + * HashMap which stores the Columnvalues identified by the unique Columnname + */ + + private Object [] values; + + /** + * The ResultDescriptor which describes the ColumnValues + */ + private ResultDescriptor resultDescriptor = null; + + /** + * Constructor + */ + public DefaultResult(ResultDescriptor resultDescriptor) { + this.resultDescriptor = resultDescriptor; + values = new Object[resultDescriptor.getColumnCount()]; + } + + /** + * @see de.intevation.gnv.geobackend.base.Result#getDate(java.lang.String) + */ + public Date getDate(String columnName) { + return getDate(resultDescriptor.getColumnIndex(columnName)); + } + + public Date getDate(int column) { + Object o = values[column]; + Date d = null; + if(o instanceof Date){ + d = (Date)o; + }else if (o instanceof GregorianCalendar){ + d = ((GregorianCalendar)o).getTime(); + } + return d; + } + + /** + * @see de.intevation.gnv.geobackend.base.Result#getDouble(java.lang.String) + */ + public Double getDouble(String columnName) { + return getDouble(resultDescriptor.getColumnIndex(columnName)); + } + + public Double getDouble(int column) { + return (Double)values[column]; + } + + /** + * @see de.intevation.gnv.geobackend.base.Result#getFloat(java.lang.String) + */ + public Float getFloat(String columnName) { + return getFloat(resultDescriptor.getColumnIndex(columnName)); + } + + public Float getFloat(int column) { + return (Float)values[column]; + } + + /** + * @see de.intevation.gnv.geobackend.base.Result#getInteger(java.lang.String) + */ + public Integer getInteger(String columnName) { + int idx = resultDescriptor.getColumnIndex(columnName); + + return idx > -1 ? getInteger(idx) : -1; + //return getInteger(resultDescriptor.getColumnIndex(columnName)); + } + + public Integer getInteger(int column) { + Object value = values[column]; + if (value instanceof Double){ + value = new Integer(((Double)value).intValue()); + } + return (Integer)value; + } + /** + * @see de.intevation.gnv.geobackend.base.Result#getResultDescriptor() + */ + public ResultDescriptor getResultDescriptor() { + return this.resultDescriptor; + } + + /** + * @see de.intevation.gnv.geobackend.base.Result#getString(java.lang.String) + */ + public String getString(String columnName) { + int idx = resultDescriptor.getColumnIndex(columnName); + + return idx > -1 ? getString(idx) : null; + } + + public String getString(int column) { + + Object o = values[column]; + + if (o instanceof Date){ + return DateUtils.getPatternedDateAmer((Date)o); + } + + if (o instanceof GregorianCalendar){ + Date d = ((GregorianCalendar)o).getTime(); + return DateUtils.getPatternedDateAmer(d); + } + + return o != null ? o.toString() : null; + } + + /** + * @see de.intevation.gnv.geobackend.base.Result#addColumnValue(java.lang.String, java.lang.Object) + */ + public void addColumnValue(int column, Object value) { + values[column] = value; + } + + /** + * @see de.intevation.gnv.geobackend.base.Result#getObject(java.lang.String) + */ + public Object getObject(String columnName) { + return getObject(resultDescriptor.getColumnIndex(columnName)); + } + + public Object getObject(int column) { + return values[column]; + } + +}
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/geo-backend/src/main/java/de/intevation/gnv/geobackend/base/DefaultResultDescriptor.java Fri Sep 28 12:13:50 2012 +0200 @@ -0,0 +1,116 @@ +/** + * + */ +package de.intevation.gnv.geobackend.base; + +import java.util.ArrayList; +import java.util.HashMap; +import java.util.List; +import java.util.Map; + +/** + * This Class is the DefaultImplementation of the + * Interface ResultDescriptor + * @author Tim Englich <tim.englich@intevation.de> + * + */ +public class DefaultResultDescriptor implements ResultDescriptor { + + /** + *The Uid of this Class + */ + private static final long serialVersionUID = 7016889733270716130L; + + /** + * The Names of the Columns of one Result + */ + private List<String> columnNames = new ArrayList<String>(); + + /** + * The Name of the Classes of the Values of one Result. + */ + private List<String> columnClassNames = new ArrayList<String>(); + + /** + * + */ + private Map<String, Integer> columnIndexLookup = new HashMap<String, Integer>(); + + /** + * Constructor + */ + public DefaultResultDescriptor() { + super(); + } + + /** + * @see de.intevation.gnv.geobackend.base.ResultDescriptor#getColumnClassName(int) + */ + public String getColumnClassName(int column) { + return this.columnClassNames.get(column); + } + + /** + * @see de.intevation.gnv.geobackend.base.ResultDescriptor#getColumnCount() + */ + public int getColumnCount() { + return this.columnClassNames.size(); + } + + /** + * @see de.intevation.gnv.geobackend.base.ResultDescriptor#getColumnName(int) + */ + public String getColumnName(int column) { + return this.columnNames.get(column); + } + + /** + * @see de.intevation.gnv.geobackend.base.ResultDescriptor#addColumn(java.lang.String, java.lang.String) + */ + public void addColumn(String name, String className) { + this.columnClassNames.add(className); + this.columnNames.add(name); + this.columnIndexLookup.put(name,this.columnNames.size()-1); + } + + /** + * @see de.intevation.gnv.geobackend.base.ResultDescriptor#getColumnIndices(java.lang.String[]) + */ + public int [] getColumnIndices(String [] columnNames) { + if (columnNames == null) { + return null; + } + + int [] indices = new int[columnNames.length]; + + for (int i = 0; i < indices.length; ++i) { + indices[i] = this.getColumnIndex(columnNames[i]); + } + + return indices; + } + + /** + * @see de.intevation.gnv.geobackend.base.ResultDescriptor#getColumnIndex(java.lang.String) + */ + public int getColumnIndex(String columnName) { + + Integer value = this.columnIndexLookup.get(columnName); + return value != null ? value.intValue() : -1; + } + + public String toString() { + StringBuilder sb = new StringBuilder("["); + int N = Math.min(columnNames.size(), columnClassNames.size()); + for (int i = 0; i < N; ++i) { + if (i > 0) { + sb.append(", "); + } + sb.append(columnNames.get(i)) + .append(": ") + .append(columnClassNames.get(i)); + } + sb.append(']'); + return sb.toString(); + } +}
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/geo-backend/src/main/java/de/intevation/gnv/geobackend/base/Result.java Fri Sep 28 12:13:50 2012 +0200 @@ -0,0 +1,86 @@ +/** + * + */ +package de.intevation.gnv.geobackend.base; + +import java.io.Serializable; +import java.util.Date; + + +/** + * Interface which provides the Methods for Accessing + * the Data of an ResultEntry + * @author Tim Englich <tim.englich@intevation.de> + * + */ +public interface Result extends Serializable{ + + /** + * Returns the ResultDescriptor which provides the + * ResultMetadatse + * @return the ResultDescriptor + */ + public ResultDescriptor getResultDescriptor(); + + /** + * Returns the Columnvalue as a String + * @param columnName the Name of the column + * @return the Columnvalue as a String + */ + public String getString(String columnName); + + + public String getString(int column); + + /** + * Returns the Columnvalue as a Date + * @param columnName the Name of the column + * @return the Columnvalue as a Date + */ + public Date getDate(String columnName); + + public Date getDate(int column); + + /** + * Returns the Columnvalue as a Integer + * @param columnName the Name of the column + * @return the Columnvalue as a Integer + */ + public Integer getInteger(String columnName); + + public Integer getInteger(int column); + + /** + * Returns the Columnvalue as a Float + * @param columnName the Name of the column + * @return the Columnvalue as a Float + */ + public Float getFloat(String columnName); + + public Float getFloat(int column); + + /** + * Returns the Columnvalue as a Double + * @param columnName the Name of the column + * @return the Columnvalue as a Double + */ + public Double getDouble(String columnName); + + public Double getDouble(int column); + + /** + * Returns the Columnvalue as a Object + * @param columnName the Name of the column + * @return the Columnvalue as a Object + */ + public Object getObject(String columnName); + + public Object getObject(int column); + + /** + * Adds an new Coumnvalue to the Result. + * @param columnName the Name of the Column + * @param value the Value of the Column + */ + public void addColumnValue(int column, Object value); +}
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/geo-backend/src/main/java/de/intevation/gnv/geobackend/base/ResultDescriptor.java Fri Sep 28 12:13:50 2012 +0200 @@ -0,0 +1,45 @@ +/** + * + */ +package de.intevation.gnv.geobackend.base; + +import java.io.Serializable; + +/** + * Interface which provides the Methods for Accessing + * the MetaData of an ResultEntry + * @author Tim Englich <tim.englich@intevation.de> + */ +public interface ResultDescriptor extends Serializable{ + + /** + * Returns the Classname of the Column + * @param column the id of the Column which is requested + * @return the Classname of the Column + */ + public String getColumnClassName(int column); + + /** + * Returns the Number of Columns + * @return the Number of Columns + */ + public int getColumnCount(); + + /** + * Returns the Name of the Column which belongs to the given Columnindex + * @param column the Index of the Column + * @return the Name of the Column + */ + public String getColumnName(int column); + + /** + * Adds a new ColumnDescription into the Descriptor + * @param name the Name of the Column (unique) + * @param className the Name of the Class (e.g. Double, Integer + */ + public void addColumn(String name, String className); + + public int [] getColumnIndices(String [] columnNames); + + public int getColumnIndex(String columnName); +}
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/geo-backend/src/main/java/de/intevation/gnv/geobackend/base/connectionpool/ConnectionPool.java Fri Sep 28 12:13:50 2012 +0200 @@ -0,0 +1,35 @@ +package de.intevation.gnv.geobackend.base.connectionpool; + +import java.sql.Connection; +import java.util.Properties; + +import de.intevation.gnv.geobackend.base.connectionpool.exception.ConnectionException; + +/** + * Interfacedefinition for an Generic ConnectionPool + * @author Tim Englich <tim.englich@intevation.de> + */ +public interface ConnectionPool { + + /** + * Delivers the Connection to the Database + * @param connectionID the ID of the Connection + * @return the Connection to the Database + * @throws ConnectionException + */ + public Connection getConnection(String connectionID) throws ConnectionException; + + /** + * Returns the Connection to the Pool + * @param connection the Connection which should be returned to the Pool + * @throws ConnectionException + */ + public void closeConnection(Connection connection) throws ConnectionException; + + /** + * Initializes the ConnectionPool + * @param properties The Properties which should be used for initializing theConnectionPool + */ + public void initialize(Properties properties); + +}
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/geo-backend/src/main/java/de/intevation/gnv/geobackend/base/connectionpool/ConnectionPoolFactory.java Fri Sep 28 12:13:50 2012 +0200 @@ -0,0 +1,85 @@ +/** + * + */ +package de.intevation.gnv.geobackend.base.connectionpool; + +import java.util.Properties; + +import org.apache.log4j.Logger; + +import de.intevation.gnv.geobackend.sde.connectionpool.ArcSDEConnectionPool; + +/** + * Factoryimplementation for the Interface ConnectionPool. + * This factory delivers Instances of the Interface ConnectionPool. + * @author Tim Englich <tim.englich@intevation.de> + */ +public class ConnectionPoolFactory { + + /** + * the logger, used to log exceptions and additonaly information + */ + private static Logger log = Logger.getLogger(ConnectionPoolFactory.class); + + + /** + * The singleton Instance of this Factory. + */ + private static ConnectionPoolFactory instance = null; + + /** + * The ConnectionPool providing the Connections to the DatabaseBackends + */ + private ConnectionPool connectionPool = null; + + /** + * Basic-Constructor of this Class + */ + private ConnectionPoolFactory() { + super(); + } + + /** + * This Method provides an singleton Instance of this Class. + * @return an singleton Instance of this Class + */ + public static ConnectionPoolFactory getInstance(){ + if (instance == null){ + instance = new ConnectionPoolFactory(); + } + return instance; + } + + + /** + * Getting the ConnectionPool + * @return the ConnectionPool + */ + public ConnectionPool getConnectionPool(){ + return this.connectionPool; + } + + /** + * Initializes the ConnectionPool. + * Should only be called once on system startup + * @param properties the Properties for the Individual Configuration of the ConnectionPool + */ + public void initializeConnectionPool(Properties properties){ + log.debug("ConnectionPoolFactory.initializeConnectionPool"); + if (this.connectionPool == null){ + //TODO: Here it might be possible to switch the ConnectionPoolInstance using a FLAG. + this.connectionPool = new ArcSDEConnectionPool(); + this.connectionPool.initialize(properties); + }else{ + log.warn("ConnectionPool already initialized"); + } + } + + /** + * Checks if the ConnectionPool has already been initialized. + * @return true if the ConnectionPool is initialized. + */ + public boolean isInitialized(){ + return this.connectionPool != null; + } +}
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/geo-backend/src/main/java/de/intevation/gnv/geobackend/base/connectionpool/exception/ConnectionException.java Fri Sep 28 12:13:50 2012 +0200 @@ -0,0 +1,114 @@ +/** + * Title: lConnectionException, $Header: /share/gdi/SDI-Suite/Repository/projekte/BSH-GDI/genericViewer/src/main/java/de/conterra/bsh/gdi/gnviewer/exception/ConnectionException.java,v 1.2 2008/08/18 14:50:33 drewnak Exp $ + * Source: $Source: /share/gdi/SDI-Suite/Repository/projekte/BSH-GDI/genericViewer/src/main/java/de/conterra/bsh/gdi/gnviewer/exception/ConnectionException.java,v $ + * created by: Stefan Blume (blume) + * erstellt am: 21.11.2007 + * Copyright: con terra GmbH, 2005 + * + * modified by: $Author: drewnak $ + * modified on: $Date: 2008/08/18 14:50:33 $ + * Version: $Revision: 1.2 $ + * TAG: $Name: $ + * locked from: $Locker: $ + * CVS State: $State: Exp $ + * Project: $ProjectName$ + */ +package de.intevation.gnv.geobackend.base.connectionpool.exception; + +import com.esri.sde.sdk.client.SeError; +import com.esri.sde.sdk.client.SeException; + +import de.intevation.gnv.geobackend.sde.datasources.exception.TechnicalException; + +import org.apache.log4j.Logger; + +/** + * The class <code>lConnectionException</code> fulfills the following purposes: + * <ol> + * <li></li> + * </ol> + * + * @author blume + * @version 1.0 + * @serial 1.0 + * @see + * @since 21.11.2007 08:13:05 + */ +public class ConnectionException extends TechnicalException { + + /** + * Default Logging instance + */ + private static Logger sLogger = Logger.getLogger(ConnectionException.class); + + /** + * Constructs a new exception with <code>null</code> as its detail message. + * The cause is not initialized, and may subsequently be initialized by a + * call to {@link #initCause}. + */ + public ConnectionException() { + } + + /** + * Constructs a new exception with the specified detail message. The + * cause is not initialized, and may subsequently be initialized by + * a call to {@link #initCause}. + * + * @param message the detail message. The detail message is saved for + * later retrieval by the {@link #getMessage()} method. + */ + public ConnectionException(String message) { + super(message); + } + public ConnectionException(Throwable pCause) { + super(pCause); + } + + /** + * Constructs a new exception with the specified detail message and + * cause. <p>Note that the detail message associated with + * <code>cause</code> is <i>not</i> automatically incorporated in + * this exception's detail message. + * + * @param message the detail message (which is saved for later retrieval + * by the {@link #getMessage()} method). + * @param cause the cause (which is saved for later retrieval by the + * {@link #getCause()} method). (A <tt>null</tt> value is + * permitted, and indicates that the cause is nonexistent or + * unknown.) + * @since 1.4 + */ + public ConnectionException(String message, Throwable cause) { + super(message, cause); + } + + /** + * Displays the details of an SeException (if available). + * + */ + public void printError() { + if (getCause() != null && getCause() instanceof SeException) { + SeException exception = (SeException) getCause(); + SeError error = exception.getSeError(); + + sLogger.debug("\n ArcSDE Error Number : " + error.getSdeError()); + sLogger.debug(" Error Description : " + error.getErrDesc()); + + int extError = error.getExtError(); + if (extError != 0) + sLogger.debug(" Extended Error Number : " + extError); + + String desc = error.getSdeErrMsg(); + if (desc != null && desc.length() != 0) + sLogger.debug(" Extended Error Description : " + desc); + + desc = error.getExtErrMsg(); + if (desc != null && desc.length() != 0) + sLogger.debug(" Extended Error Description : " + desc); + + sLogger.debug(exception); + + } + + } +}
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/geo-backend/src/main/java/de/intevation/gnv/geobackend/base/query/CachingQueryExecutorFactory.java Fri Sep 28 12:13:50 2012 +0200 @@ -0,0 +1,64 @@ +package de.intevation.gnv.geobackend.base.query; + +import java.util.Collection; + +import net.sf.ehcache.Cache; +import net.sf.ehcache.Element; +import net.sf.ehcache.CacheManager; + +import org.apache.log4j.Logger; + +import de.intevation.gnv.geobackend.base.Result; + +/** + * @author Sascha L. Teichmann <sascha.teichmann@intevation.de> + * + */ +public class CachingQueryExecutorFactory +extends QueryExecutorFactory +{ + public static final String QUERY_EXECUTOR_FACTORY = "caching.query.executor.config"; + public static final String CACHE_NAME = "sql.cache"; + + private static Logger log = Logger.getLogger(CachingQueryExecutorFactory.class); + + protected CacheManager manager; + + public CachingQueryExecutorFactory() { + log.info("using SQL cache"); + String configFile = System.getProperty(QUERY_EXECUTOR_FACTORY); + manager = configFile != null + ? new CacheManager(configFile) + : new CacheManager(); + manager.addCache(CACHE_NAME); + } + + public QueryExecutor getQueryExecutor() { + return new DefaultQueryExceutor() { + + public Collection<Result> cachedResults(String query) { + Cache cache = manager.getCache(CACHE_NAME); + Element element = cache.get(query); + if (log.isDebugEnabled()) { + log.debug("found results in SQL cache: " + (element != null)); + } + return element != null + ? (Collection<Result>)element.getObjectValue() + : null; + } + + public void cacheResults(String query, Collection<Result> results) { + log.debug("store results in SQL cache"); + Cache cache = manager.getCache(CACHE_NAME); + cache.put(new Element(query, results)); + } + }; + } + + public void shutdown() { + log.info("shutting down SQL cache"); + manager.getCache(CACHE_NAME).flush(); + manager.shutdown(); + } +} +// vim:set ts=4 sw=4 si et sta sts=4 fenc=utf8:
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/geo-backend/src/main/java/de/intevation/gnv/geobackend/base/query/DefaultQueryExceutor.java Fri Sep 28 12:13:50 2012 +0200 @@ -0,0 +1,118 @@ +/** + * + */ +package de.intevation.gnv.geobackend.base.query; + +import java.sql.Connection; +import java.sql.ResultSet; +import java.sql.SQLException; +import java.sql.Statement; +import java.util.Collection; + +import org.apache.log4j.Logger; + +import de.intevation.gnv.geobackend.base.Result; +import de.intevation.gnv.geobackend.base.connectionpool.ConnectionPool; +import de.intevation.gnv.geobackend.base.connectionpool.ConnectionPoolFactory; +import de.intevation.gnv.geobackend.base.connectionpool.exception.ConnectionException; +import de.intevation.gnv.geobackend.base.query.container.QueryContainerFactory; +import de.intevation.gnv.geobackend.base.query.container.exception.QueryContainerException; +import de.intevation.gnv.geobackend.base.query.exception.QueryException; + +/** + * This is an Standard Implementation of the Interface QueryExecutor. + * It fetchs the Query from the Querycontainer an put the Filtervalues into the Query. + * @author Tim Englich <tim.englich@intevation.de> + * + */ +public class DefaultQueryExceutor extends QueryExecutorBase{ + + + /** + * the logger, used to log exceptions and additonaly information + */ + private static Logger log = Logger.getLogger(DefaultQueryExceutor.class); + + /** + * The ConnectionID identifing the Connection to use executing the Query. + */ + private String connectionID = "N/N"; + + /** + * Constructor + */ + public DefaultQueryExceutor() { + super(); + } + + /** + * @see de.intevation.gnv.geobackend.base.query.QueryExecutor#executeQuery(java.lang.String, java.lang.String[]) + */ + public Collection<Result> executeQuery(String queryID, String[] filter) throws QueryException { + Collection<Result> returnValue = null; + try { + String queryString = QueryContainerFactory.getInstance().getQueryContainer().getQuery(queryID); + if (queryString != null){ + if (filter != null && filter.length > 0){ + //Insert the Filtervalues into the QueryString + queryString = super.setFilterValues(queryString, filter); + } + + if (log.isDebugEnabled()) { + log.debug("############ QUERY ##################"); + log.debug(queryString); + log.debug("#######################################"); + } + + returnValue = cachedResults(queryString); + + if (returnValue != null) { + return returnValue; + } + + Connection connection = null; + ConnectionPool connectionPool = ConnectionPoolFactory.getInstance().getConnectionPool(); + try { + // Submit the Query + connection = connectionPool.getConnection(this.connectionID); + if (connection != null){ + Statement stmt = connection.createStatement(); + ResultSet rs = stmt.executeQuery(queryString); + returnValue = super.createResultCollection(rs); + + }else{ + log.error("Could not establish Databaseconnection."); + throw new QueryException("Could not establish Databaseconnection."); + } + + cacheResults(queryString, returnValue); + + } catch (ConnectionException e) { + log.error(e,e); + throw new QueryException("Could not establish Databaseconnection.",e); + } catch (SQLException e) { + log.error(e,e); + throw new QueryException(e); + }finally{ + if (connection != null){ + try { + connectionPool.closeConnection(connection); + } catch (ConnectionException e) { + log.error("Connection could not be returned to ConnectionPool."); + log.error(e,e); + } + } + } + }else{ + log.error("No QueryString defined for "+queryID); + throw new QueryException("Cannot get the Querystring"); + } + + } catch (QueryContainerException e) { + log.error(e,e); + throw new QueryException("Cannot get the Querystring",e); + } + + return returnValue; + } +}
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/geo-backend/src/main/java/de/intevation/gnv/geobackend/base/query/QueryExecutor.java Fri Sep 28 12:13:50 2012 +0200 @@ -0,0 +1,33 @@ +/** + * + */ +package de.intevation.gnv.geobackend.base.query; + +import java.util.Collection; + +import de.intevation.gnv.geobackend.base.Result; +import de.intevation.gnv.geobackend.base.query.exception.QueryException; + +/** + * This Interface provides the Method to execute Queries + * against a Datastore eg. Databases + * @author Tim Englich <tim.englich@intevation.de> + * + */ +public interface QueryExecutor { + + + /** + * This Method executes the Query an returns the Result + * in an Collection. + * @param queryID the ID of the Query which should be used. + * @param filter the Filterarguments to limit the Data + * @return the fetched Values + * @throws QueryException + */ + Collection<Result> executeQuery(String queryID, String[] filter) throws QueryException; + + Collection<Result> cachedResults(String query); + + void cacheResults(String query, Collection<Result> results); +}
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/geo-backend/src/main/java/de/intevation/gnv/geobackend/base/query/QueryExecutorBase.java Fri Sep 28 12:13:50 2012 +0200 @@ -0,0 +1,104 @@ +/** + * + */ +package de.intevation.gnv.geobackend.base.query; + +import java.sql.ResultSet; +import java.sql.ResultSetMetaData; +import java.sql.SQLException; +import java.util.ArrayList; +import java.util.Collection; +import java.util.List; + +import de.intevation.gnv.geobackend.base.DefaultResult; +import de.intevation.gnv.geobackend.base.DefaultResultDescriptor; +import de.intevation.gnv.geobackend.base.Result; +import de.intevation.gnv.geobackend.base.ResultDescriptor; + +/** + * This is an abstract Basicimplementation of the Interface + * QueryExecutor providing several Helpermethods. + * @author Tim Englich <tim.englich@intevation.de> + * + */ +public abstract class QueryExecutorBase implements QueryExecutor { + + /** + * Constructor + */ + public QueryExecutorBase() { + super(); + } + + /** + * This Method puts the Filtervalues into the Querystring + * @param queryString the Query which should be manipulated + * @param filter the values which should be put into the Query + * @return the manipulated Query + */ + protected String setFilterValues(String queryString, String[] filter){ + String returnValue = queryString; + for (int i = 0; i < filter.length; i++){ + returnValue = returnValue.replaceFirst("[?]", filter[i]); + } + return returnValue; + } + + /** + * This Methods converts the ResultSet into an Collection of Result-objects + * @param resultSet the ResultSet which should be converted + * @return an Collection containing Result-Objects + * @throws SQLException + */ + protected Collection<Result> createResultCollection(ResultSet resultSet) throws SQLException{ + Collection<Result> returnValue = new ArrayList<Result>(); + + ResultDescriptor resultDescriptor = null; + int columns = -1; + + List<String> columnNames = null; + + while (resultSet.next()){ + if (resultDescriptor == null){ + resultDescriptor = new DefaultResultDescriptor(); + ResultSetMetaData rsmd = resultSet.getMetaData(); + columns = rsmd.getColumnCount(); + + for (int i = 1; i <= columns; i++){ + resultDescriptor.addColumn(rsmd.getColumnName(i), rsmd.getColumnClassName(i)); + } + } + + Result result = convertResult(resultSet, resultDescriptor); + + returnValue.add(result); + } + return returnValue; + } + + /** + * This Method converts one Singel ResultSetEntry into an Result-Object + * @param resultSet the ResultSet where the Entry should be took from. + * @param resultDescriptor the ResultsetDescriptor which describes the Entry + * @return an new Result-Objects containing the Values of the ResultSet-Entry + * @throws SQLException + */ + private Result convertResult(ResultSet resultSet, ResultDescriptor resultDescriptor) + throws SQLException { + + Result result = new DefaultResult(resultDescriptor); + + for (int i = 0, N = resultDescriptor.getColumnCount(); i < N; i++) { + result.addColumnValue(i, resultSet.getObject(i+1)); + } + return result; + } + + public Collection<Result> cachedResults(String query) { + return null; + } + + public void cacheResults(String query, Collection<Result> result) { + } + +}
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/geo-backend/src/main/java/de/intevation/gnv/geobackend/base/query/QueryExecutorFactory.java Fri Sep 28 12:13:50 2012 +0200 @@ -0,0 +1,82 @@ +/** + * + */ +package de.intevation.gnv.geobackend.base.query; + +import org.apache.log4j.Logger; + +/** + * @author Tim Englich <tim.englich@intevation.de> + * + */ +public class QueryExecutorFactory { + + public static final String QUERY_EXECUTOR_FACTORY = "query.executor.factory"; + + /** + * the logger, used to log exceptions and additonaly information + */ + private static Logger log = Logger.getLogger(QueryExecutorFactory.class); + + + /** + * The singleton Instance of this Factory. + */ + private static QueryExecutorFactory instance = null; + + /** + * Basic-Constructor of this Class + */ + public QueryExecutorFactory() { + super(); + } + + /** + * This Method provides an singleton Instance of this Class. + * @return an singleton Instance of this Class + */ + public static synchronized QueryExecutorFactory getInstance(){ + if (instance == null) { + String className = System.getProperty(QUERY_EXECUTOR_FACTORY); + if (className != null) { + try { + Class clazz = Class.forName(className); + final QueryExecutorFactory factory = + (QueryExecutorFactory)clazz.newInstance(); + Runtime.getRuntime().addShutdownHook(new Thread() { + public void run() { + factory.shutdown(); + } + }); + return instance = factory; + } + catch (ClassNotFoundException cnfe) { + log.error(cnfe); + } + catch (InstantiationException ie) { + log.error(ie); + } + catch(IllegalAccessException iae) { + log.error(iae); + } + catch (ClassCastException cce) { + log.error(cce); + } + } + + instance = new QueryExecutorFactory(); + } + return instance; + } + + public void shutdown() { + } + + /** + * Getting the QueryExecutor + * @return the QueryExecutor + */ + public QueryExecutor getQueryExecutor(){ + return new DefaultQueryExceutor(); + } +}
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/geo-backend/src/main/java/de/intevation/gnv/geobackend/base/query/container/DefaultQueryContainer.java Fri Sep 28 12:13:50 2012 +0200 @@ -0,0 +1,47 @@ +/** + * + */ +package de.intevation.gnv.geobackend.base.query.container; + +import java.util.Properties; + +import de.intevation.gnv.geobackend.base.query.container.exception.QueryContainerException; + +/** + * Defaultimplementation of an QueryContainer. + * All Queries are provided using a Propertiescontainer. + * @author Tim Englich <tim.englich@intevation.de> + */ +public class DefaultQueryContainer implements QueryContainer { + + /** + * The Properties-Object all Queries are provided in. + */ + private Properties queries = null; + + /** + * Constructor + */ + public DefaultQueryContainer() { + super(); + } + + /** + * @see de.intevation.gnv.geobackend.base.query.container.QueryContainer#getQuery(java.lang.String) + */ + public String getQuery(String queryID) throws QueryContainerException { + String returnValue = null; + if (this.queries != null){ + returnValue = this.queries.getProperty(queryID); + } + return returnValue; + } + + /** + * @see de.intevation.gnv.geobackend.base.query.container.QueryContainer#initialize(java.util.Properties) + */ + public void initialize(Properties properties) throws QueryContainerException { + this.queries = properties; + } + +}
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/geo-backend/src/main/java/de/intevation/gnv/geobackend/base/query/container/QueryContainer.java Fri Sep 28 12:13:50 2012 +0200 @@ -0,0 +1,31 @@ +/** + * + */ +package de.intevation.gnv.geobackend.base.query.container; + +import java.util.Properties; + +import de.intevation.gnv.geobackend.base.query.container.exception.QueryContainerException; + +/** + * @author Tim Englich <tim.englich@intevation.de> + * + */ +public interface QueryContainer { + + + /** + * Returns the Query specified by the given queryID. + * @param queryID the ID which speciogies the Query which should be returned + * @return the Query + * @throws QueryContainerException + */ + public String getQuery(String queryID) throws QueryContainerException; + + /** + * Initializes the QueryContainer + * @param properties The Properties which should be used for initializing QueryContainer + * @throws QueryContainerException + */ + public void initialize(Properties properties) throws QueryContainerException; +}
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/geo-backend/src/main/java/de/intevation/gnv/geobackend/base/query/container/QueryContainerFactory.java Fri Sep 28 12:13:50 2012 +0200 @@ -0,0 +1,84 @@ +/** + * + */ +package de.intevation.gnv.geobackend.base.query.container; + +import java.util.Properties; + +import org.apache.log4j.Logger; + +import de.intevation.gnv.geobackend.base.query.container.exception.QueryContainerException; + +/** + * @author Tim Englich <tim.englich@intevation.de> + * + */ +public class QueryContainerFactory { + + /** + * the logger, used to log exceptions and additonaly information + */ + private static Logger log = Logger.getLogger(QueryContainerFactory.class); + + + /** + * The singleton Instance of this Factory. + */ + private static QueryContainerFactory instance = null; + + /** + * The ConnectionPool providing the Connections to the DatabaseBackends + */ + private QueryContainer queryContainer = null; + + /** + * Basic-Constructor of this Class + */ + private QueryContainerFactory() { + super(); + } + + /** + * This Method provides an singleton Instance of this Class. + * @return an singleton Instance of this Class + */ + public static synchronized QueryContainerFactory getInstance(){ + if (instance == null){ + instance = new QueryContainerFactory(); + } + return instance; + } + + + /** + * Getting the QueryContainer + * @return the QueryContainer + */ + public QueryContainer getQueryContainer(){ + return this.queryContainer; + } + + /** + * Initializes the QueryContainer. + * Should only be called once on system startup + * @param properties the Properties for the Individual Configuration of the QueryContainerbb + * @throws QueryContainerException + */ + public synchronized void initializeQueryContainer(Properties properties) throws QueryContainerException{ + log.debug("ConnectionPoolFactory.initializeConnectionPool"); + if (this.queryContainer == null){ + this.queryContainer = new DefaultQueryContainer(); + this.queryContainer.initialize(properties); + }else{ + log.warn("ConnectionPool already initialized"); + } + } + + /** + * Checks if the QueryContainer has already been initialized. + * @return true if the QueryContainer is initialized. + */ + public boolean isInitialized(){ + return this.queryContainer != null; + } +}
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/geo-backend/src/main/java/de/intevation/gnv/geobackend/base/query/container/exception/QueryContainerException.java Fri Sep 28 12:13:50 2012 +0200 @@ -0,0 +1,49 @@ +/** + * + */ +package de.intevation.gnv.geobackend.base.query.container.exception; +/** + * Exceptioncalls which will be used to retun Exception in + * the QuerContainer-Package. + * @author Tim Englich <tim.englich@intevation.de> + * + */ +public class QueryContainerException extends Exception { + + /** + * The UID auf this Class + */ + private static final long serialVersionUID = -1811252311941938708L; + + /** + * Constructor + */ + public QueryContainerException() { + } + + /** + * Constructor + * @param arg0 + */ + public QueryContainerException(String arg0) { + super(arg0); + } + + /** + * Constructor + * @param arg0 + */ + public QueryContainerException(Throwable arg0) { + super(arg0); + } + + /** + * Constructor + * @param arg0 + * @param arg1 + */ + public QueryContainerException(String arg0, Throwable arg1) { + super(arg0, arg1); + } + +}
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/geo-backend/src/main/java/de/intevation/gnv/geobackend/base/query/exception/QueryException.java Fri Sep 28 12:13:50 2012 +0200 @@ -0,0 +1,47 @@ +/** + * + */ +package de.intevation.gnv.geobackend.base.query.exception; +/** + * @author Tim Englich <tim.englich@intevation.de> + * + */ +public class QueryException extends Exception { + + /** + * The UID for this Class + */ + private static final long serialVersionUID = -1337567603858112424L; + + /** + * Constructor + */ + public QueryException() { + } + + /** + * Constructor + * @param message + */ + public QueryException(String message) { + super(message); + } + + /** + * Constructor + * @param cause + */ + public QueryException(Throwable cause) { + super(cause); + } + + /** + * Constructor + * @param message + * @param cause + */ + public QueryException(String message, Throwable cause) { + super(message, cause); + } + +}
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/geo-backend/src/main/java/de/intevation/gnv/geobackend/sde/connectionpool/ArcSDEConnectionPool.java Fri Sep 28 12:13:50 2012 +0200 @@ -0,0 +1,124 @@ +package de.intevation.gnv.geobackend.sde.connectionpool; + +import java.sql.Connection; +import java.util.Properties; + +import org.apache.commons.pool.PoolableObjectFactory; +import org.apache.commons.pool.impl.GenericObjectPool; +import org.apache.log4j.Logger; + +import de.intevation.gnv.geobackend.base.connectionpool.ConnectionPool; +import de.intevation.gnv.geobackend.base.connectionpool.exception.ConnectionException; + +/** + *This is the ArcSDE specific implementation of the Interface ConnectionPool + * + * @author Tim Englich <tim.englich@intevation.de> + */ +public class ArcSDEConnectionPool implements ConnectionPool { + + /** + * the logger, used to log exceptions and additonaly information + */ + private static Logger log = Logger + .getLogger(ArcSDEPoolableObjectFactory.class); + + /** + * The Pool which stores the Connections to the ArcSDE Backend + */ + private GenericObjectPool pool = null; + + /** + * @see de.intevation.gnv.geobackend.base.connectionpool.ConnectionPool#closeConnection(java.lang.Object) + */ + public void closeConnection(Connection connection) + throws ConnectionException { + try { + this.pool.returnObject(connection); + } catch (Exception e) { + log.error(e, e); + throw new ConnectionException(e); + } + + } + + /** + * @see de.intevation.gnv.geobackend.base.connectionpool.ConnectionPool#getConnection(java.lang.String) + */ + public synchronized Connection getConnection(String connectionID) + throws ConnectionException { + try { + Object object = this.pool.borrowObject(); + + if (object instanceof Connection) { + return (Connection) object; + } else { + throw new ConnectionException( + "Created Object is not an java.sql.Connection"); + } + + } catch (Exception e) { + log.error(e, e); + throw new ConnectionException(e); + } + } + + /** + * @see de.intevation.gnv.geobackend.base.connectionpool.ConnectionPool#initialize(java.util.Properties) + */ + public void initialize(Properties properties) { + log.info("ArcSDEConnectionPool.initialize has been called"); + log.info("ConnectionPool will be initialized"); + + PoolableObjectFactory poolableObjectFactory = new ArcSDEPoolableObjectFactory( + properties); + + int maxActive = Integer.parseInt(properties.getProperty("maxActive", + "10")); + long maxWait = Long.parseLong(properties.getProperty("maxWait", + "" + GenericObjectPool.DEFAULT_MAX_WAIT)); + int maxIdle = Integer.parseInt(properties.getProperty("maxIdle", + "" + GenericObjectPool.DEFAULT_MAX_IDLE)); + int minIdle = Integer.parseInt(properties.getProperty("minIdle", + "" + GenericObjectPool.DEFAULT_MIN_IDLE)); + boolean testOnBorrow = Boolean.parseBoolean(properties.getProperty( + "testOnBorrow", "" + GenericObjectPool.DEFAULT_TEST_ON_BORROW)); + boolean testOnReturn = Boolean.parseBoolean(properties.getProperty( + "testOnReturn", "" + GenericObjectPool.DEFAULT_TEST_ON_RETURN)); + long timeBetweenEvictionRunsMillis = Long + .parseLong(properties + .getProperty( + "timeBetweenEvictionRunsMillis", + "" + + GenericObjectPool.DEFAULT_TIME_BETWEEN_EVICTION_RUNS_MILLIS)); + int numTestsPerEvictionRun = Integer.parseInt(properties.getProperty( + "numTestsPerEvictionRun", + "" + GenericObjectPool.DEFAULT_NUM_TESTS_PER_EVICTION_RUN)); + long minEvictableIdleTimeMillis = Long + .parseLong(properties + .getProperty( + "minEvictableIdleTimeMillis", + "" + + GenericObjectPool.DEFAULT_SOFT_MIN_EVICTABLE_IDLE_TIME_MILLIS)); + boolean testWhileIdle = Boolean.parseBoolean(properties + .getProperty("testWhileIdle", + "" + GenericObjectPool.DEFAULT_TEST_WHILE_IDLE)); + + log.info("Maximum Number of active Connections: " + maxActive); + log.info("Maximum Number of waiting: " + maxWait); + log.info("Maximum Number of idle: " + maxIdle); + log.info("Minimum Number of idle: " + minIdle); + log.info("TestOnBorrow: " + testOnBorrow); + log.info("TestOnReturn: " + testOnReturn); + log.info("TimeBetweenEvictionRunsMillis: " + timeBetweenEvictionRunsMillis); + log.info("NumTestsPerEvictionRun: " + numTestsPerEvictionRun); + log.info("MinEvictableIdleTimeMillis: " + minEvictableIdleTimeMillis); + log.info("TestWhileIdle: " + testWhileIdle); + + this.pool = new GenericObjectPool(poolableObjectFactory, maxActive, + GenericObjectPool.DEFAULT_WHEN_EXHAUSTED_ACTION, maxWait, + maxIdle, minIdle, testOnBorrow, testOnReturn, + timeBetweenEvictionRunsMillis, numTestsPerEvictionRun, + minEvictableIdleTimeMillis, testWhileIdle); + } +}
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/geo-backend/src/main/java/de/intevation/gnv/geobackend/sde/connectionpool/ArcSDEPoolableObjectFactory.java Fri Sep 28 12:13:50 2012 +0200 @@ -0,0 +1,196 @@ +package de.intevation.gnv.geobackend.sde.connectionpool; + +import de.intevation.gnv.geobackend.base.connectionpool.exception.ConnectionException; + +import de.intevation.gnv.geobackend.sde.datasources.ArcSDEConnection; + +import java.sql.Connection; +import java.sql.SQLException; + +import java.util.Properties; + +import org.apache.commons.pool.PoolableObjectFactory; + +import org.apache.log4j.Logger; + +/** + * @author Tim Englich (tim.englich@intevation.de) + * @author Sascha L. Teichmann (sascha.teichmann@intevation.de) + */ +public class ArcSDEPoolableObjectFactory implements PoolableObjectFactory { + + /** + * the logger, used to log exceptions and additonaly information + */ + private static Logger log = Logger.getLogger( + ArcSDEPoolableObjectFactory.class); + + // The 5 seconds are inspired by GeoTools's testServer() usage. + private int serverRoundtripInterval = 5; + + private long serverInactiveInterval = 5L*60L*1000L; // 5 minutes + /** + * The URL to the ArcSDE Server + */ + private String server = null; + /** + * The Port the ArcSDE Server is connected to. + */ + private String port = null; + /** + * The Name of the Database + */ + private String database = null; + /** + * The Username for the Authentication + */ + private String username = null; + /** + * The Credentials which belongs to the User + */ + private String credentials = null; + + /** + * Constructor of this Class + * @param properties the Properties which includes the ConnectionParams to the Database + */ + public ArcSDEPoolableObjectFactory(Properties properties) { + + log.debug("ArcSDEPoolableObjectFactory.Constructor"); + + server = properties.getProperty("server"); + port = properties.getProperty("port"); + database = properties.getProperty("database"); + username = properties.getProperty("username"); + credentials = properties.getProperty("credentials"); + + String serverRoundtripIntervalValue = + properties.getProperty("serverRoundtripInterval"); + String serverInactiveIntervalValue = + properties.getProperty("serverInactiveInterval"); + + try { + if (serverRoundtripIntervalValue != null) { + serverRoundtripInterval = + Integer.parseInt(serverRoundtripIntervalValue); + } + } + catch (NumberFormatException e) { + log.error(e,e); + } + + try { + if (serverInactiveIntervalValue != null) { + serverInactiveInterval = 1000L * // input in seconds! + Long.parseLong(serverInactiveIntervalValue); + } + } + catch (NumberFormatException e) { + log.error(e,e); + } + + log.info("ArcSDEPoolableObjectFactory initialized"); + log.info("Server: " + server); + log.info("Port: " + port); + log.info("Database: " + database); + log.info("User: " + username); + log.info("Roundtrip check interval: " + serverRoundtripInterval); + log.info("Inactive check interval: " + serverInactiveInterval); + } + + /** + * @see org.apache.commons.pool.PoolableObjectFactory#activateObject(java.lang.Object) + */ + public void activateObject(Object arg0) throws Exception { + log.debug("ArcSDEPoolableObjectFactory.activateObject"); + } + + /** + * @see org.apache.commons.pool.PoolableObjectFactory#destroyObject(java.lang.Object) + */ + public void destroyObject(Object arg0) throws Exception { + log.debug("ArcSDEPoolableObjectFactory.destroyObjectb"); + if (arg0 instanceof ArcSDEConnection) { + ((ArcSDEConnection)arg0).close(); + }else{ + log.warn("Object cannot be handled"); + } + } + + /** + * @see org.apache.commons.pool.PoolableObjectFactory#makeObject() + */ + public Object makeObject() throws Exception { + log.debug("ArcSDEPoolableObjectFactory.makeObject"); + Connection con; + try { + con = new ArcSDEConnection( + server, + port, + database, + username, + credentials, + serverRoundtripInterval, + serverInactiveInterval); + } + catch (ConnectionException e) { + throw new ConnectionException( + "Establishing a connection to database failed: " + + e.toString(), e); + } + return con; + } + + /** + * @see org.apache.commons.pool.PoolableObjectFactory#passivateObject(java.lang.Object) + */ + public void passivateObject(Object arg0) throws Exception { + + boolean debug = log.isDebugEnabled(); + + if (debug) { + log.debug("ArcSDEPoolableObjectFactory.passivateObject"); + } + + if (arg0 instanceof ArcSDEConnection) { + if (debug) { + log.debug(" touching connection"); + } + ((ArcSDEConnection)arg0).touch(); + } + } + + /** + * @see org.apache.commons.pool.PoolableObjectFactory#validateObject(java.lang.Object) + */ + public boolean validateObject(Object arg0) { + + boolean debug = log.isDebugEnabled(); + + if (debug) { + log.debug("ArcSDEPoolableObjectFactory.validateObject"); + } + + if (!(arg0 instanceof ArcSDEConnection)) { + return false; + } + + try { + ArcSDEConnection con = (ArcSDEConnection)arg0; + + boolean isValid = + con.isActive() && con.isValid(serverRoundtripInterval); + + if (!isValid && debug) { + log.debug("connection is invalid!"); + } + + return isValid; + } + catch (SQLException sqle) { + log.error(sqle, sqle); + } + + return false; + } +}
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/geo-backend/src/main/java/de/intevation/gnv/geobackend/sde/datasources/ArcSDEConnection.java Fri Sep 28 12:13:50 2012 +0200 @@ -0,0 +1,454 @@ +/** + * + */ + package de.intevation.gnv.geobackend.sde.datasources; + +import java.sql.Array; +import java.sql.Blob; +import java.sql.CallableStatement; +import java.sql.Clob; +import java.sql.Connection; +import java.sql.DatabaseMetaData; +import java.sql.NClob; +import java.sql.PreparedStatement; +import java.sql.SQLClientInfoException; +import java.sql.SQLException; +import java.sql.SQLWarning; +import java.sql.SQLXML; +import java.sql.Savepoint; +import java.sql.Statement; +import java.sql.Struct; +import java.util.Map; +import java.util.Properties; + +import org.apache.log4j.Logger; + +import com.esri.sde.sdk.client.SeConnection; +import com.esri.sde.sdk.client.SeException; + +import de.intevation.gnv.geobackend.base.connectionpool.exception.ConnectionException; +import de.intevation.gnv.geobackend.sde.connectionpool.ArcSDEPoolableObjectFactory; + +/** + * @author Tim Englich <tim.englich@intevation.de> + * + */ +public class ArcSDEConnection implements Connection { + + /** + * the logger, used to log exceptions and additonaly information + */ + private static Logger log = Logger.getLogger(ArcSDEPoolableObjectFactory.class); + + private SeConnection seConnection = null; + + private long serverRoundtripInterval; + + private long inactiveInterval; + + private long lastTouch; + + + /** + * Constructor + */ + public ArcSDEConnection( + String server, + String port, + String database, + String username, + String credentials, + long serverRoundtripInterval, + long inactiveInterval + ) + throws ConnectionException + { + this.serverRoundtripInterval = serverRoundtripInterval; + this.inactiveInterval = inactiveInterval; + lastTouch = System.currentTimeMillis(); + + try { + seConnection = new SeConnection( server, port, database, username, credentials); + } + catch (SeException e) { + log.error(e,e); + throw new ConnectionException(e); + } + } + + public boolean isActive() { + long current = System.currentTimeMillis(); + long last; + synchronized (this) { + last = lastTouch; + } + return Math.abs(current - last) < inactiveInterval; + } + + public void touch() { + long time = System.currentTimeMillis(); + synchronized (this) { + lastTouch = time; + } + } + + /** + * @see java.sql.Connection#clearWarnings() + */ + public void clearWarnings() throws SQLException { + } + + /** + * @see java.sql.Connection#close() + */ + public void close() throws SQLException { + try { + this.seConnection.close(); + } catch (SeException e) { + log.error(e,e); + throw new SQLException(e.getMessage()); + } + } + + /** + * @see java.sql.Connection#commit() + */ + public void commit() throws SQLException { + try{ + this.seConnection.commitTransaction(); + } catch (SeException e) { + log.error(e,e); + throw new SQLException(e.getMessage()); + } + } + + /** + * @see java.sql.Connection#createStatement() + */ + public Statement createStatement() throws SQLException { + + return new ArcSDEStatement(this); + } + + /** + * @see java.sql.Connection#createStatement(int, int) + */ + public Statement createStatement(int resultSetType, int resultSetConcurrency) + throws SQLException { + return new ArcSDEStatement(this); + } + + /** + * @see java.sql.Connection#createStatement(int, int, int) + */ + public Statement createStatement(int resultSetType, + int resultSetConcurrency, int resultSetHoldability) + throws SQLException { + return new ArcSDEStatement(this); + } + + /** + * @see java.sql.Connection#getAutoCommit() + */ + public boolean getAutoCommit() throws SQLException { + return false; + } + + /** + * @see java.sql.Connection#getCatalog() + */ + public String getCatalog() throws SQLException { + + return null; + } + + /** + * @see java.sql.Connection#getHoldability() + */ + public int getHoldability() throws SQLException { + + return 0; + } + + /** + * @see java.sql.Connection#getMetaData() + */ + public DatabaseMetaData getMetaData() throws SQLException { + + return null; + } + + /** + * @see java.sql.Connection#getTransactionIsolation() + */ + public int getTransactionIsolation() throws SQLException { + + return 0; + } + + /** + * @see java.sql.Connection#getTypeMap() + */ + public Map<String, Class<?>> getTypeMap() throws SQLException { + + return null; + } + + /** + * @see java.sql.Connection#getWarnings() + */ + public SQLWarning getWarnings() throws SQLException { + + return null; + } + + /** + * @see java.sql.Connection#isClosed() + */ + public boolean isClosed() throws SQLException { + try{ + return this.seConnection.isClosed(); + } catch (Exception e) { + log.error(e,e); + throw new SQLException(e.getMessage()); + } + } + + /** + * @see java.sql.Connection#isReadOnly() + */ + public boolean isReadOnly() throws SQLException { + return false; + } + + /** + * @see java.sql.Connection#nativeSQL(java.lang.String) + */ + public String nativeSQL(String sql) throws SQLException { + return null; + } + + /** + * @see java.sql.Connection#prepareCall(java.lang.String) + */ + public CallableStatement prepareCall(String sql) throws SQLException { + return null; + } + + /** + * @see java.sql.Connection#prepareCall(java.lang.String, int, int) + */ + public CallableStatement prepareCall(String sql, int resultSetType, + int resultSetConcurrency) throws SQLException { + return null; + } + + /** + * @see java.sql.Connection#prepareCall(java.lang.String, int, int, int) + */ + public CallableStatement prepareCall(String sql, int resultSetType, + int resultSetConcurrency, int resultSetHoldability) + throws SQLException { + + return null; + } + + /** + * @see java.sql.Connection#prepareStatement(java.lang.String) + */ + public PreparedStatement prepareStatement(String sql) throws SQLException { + + return null; + } + + /** + * @see java.sql.Connection#prepareStatement(java.lang.String, int) + */ + public PreparedStatement prepareStatement(String sql, int autoGeneratedKeys) + throws SQLException { + + return null; + } + + /** + * @see java.sql.Connection#prepareStatement(java.lang.String, int[]) + */ + public PreparedStatement prepareStatement(String sql, int[] columnIndexes) + throws SQLException { + + return null; + } + + /** + * @see java.sql.Connection#prepareStatement(java.lang.String, java.lang.String[]) + */ + public PreparedStatement prepareStatement(String sql, String[] columnNames) + throws SQLException { + + return null; + } + + /** + * @see java.sql.Connection#prepareStatement(java.lang.String, int, int) + */ + public PreparedStatement prepareStatement(String sql, int resultSetType, + int resultSetConcurrency) throws SQLException { + + return null; + } + + /** + * @see java.sql.Connection#prepareStatement(java.lang.String, int, int, int) + */ + public PreparedStatement prepareStatement(String sql, int resultSetType, + int resultSetConcurrency, int resultSetHoldability) + throws SQLException { + + return null; + } + + /** + * @see java.sql.Connection#releaseSavepoint(java.sql.Savepoint) + */ + public void releaseSavepoint(Savepoint savepoint) throws SQLException { + } + + /** + * @see java.sql.Connection#rollback() + */ + public void rollback() throws SQLException { + try { + this.seConnection.rollbackTransaction(); + } catch (SeException e) { + log.error(e,e); + throw new SQLException(e.getMessage()); + } + } + + /** + * @see java.sql.Connection#rollback(java.sql.Savepoint) + */ + public void rollback(Savepoint savepoint) throws SQLException { + this.rollback(); + } + + /** + * @see java.sql.Connection#setAutoCommit(boolean) + */ + public void setAutoCommit(boolean autoCommit) throws SQLException { + } + + /** + * @see java.sql.Connection#setCatalog(java.lang.String) + */ + public void setCatalog(String catalog) throws SQLException { + } + + /** + * @see java.sql.Connection#setHoldability(int) + */ + public void setHoldability(int holdability) throws SQLException { + } + + /** + * @see java.sql.Connection#setReadOnly(boolean) + */ + public void setReadOnly(boolean readOnly) throws SQLException { + } + + /** + * @see java.sql.Connection#setSavepoint() + */ + public Savepoint setSavepoint() throws SQLException { + + return null; + } + + /** + * @see java.sql.Connection#setSavepoint(java.lang.String) + */ + public Savepoint setSavepoint(String name) throws SQLException { + + return null; + } + + /** + * @see java.sql.Connection#setTransactionIsolation(int) + */ + public void setTransactionIsolation(int level) throws SQLException { + } + + /** + * @see java.sql.Connection#setTypeMap(java.util.Map) + */ + public void setTypeMap(Map<String, Class<?>> map) throws SQLException { + } + + /** + * @return the seConnection + */ + public SeConnection getSeConnection() { + return seConnection; + } + + public Array createArrayOf(String arg0, Object[] arg1) throws SQLException { + return null; + } + + public Blob createBlob() throws SQLException { + return null; + } + + public Clob createClob() throws SQLException { + return null; + } + + public NClob createNClob() throws SQLException { + return null; + } + + public SQLXML createSQLXML() throws SQLException { + return null; + } + + public Struct createStruct(String arg0, Object[] arg1) throws SQLException { + return null; + } + + public Properties getClientInfo() throws SQLException { + return null; + } + + public String getClientInfo(String arg0) throws SQLException { + return null; + } + + public boolean isValid(int arg0) throws SQLException { + + boolean valid = true; + try { + this.seConnection.testServer(serverRoundtripInterval); + } catch (SeException e) { + log.debug("The validation of the Connection has occured an Error. The connection is invalid."); + valid = false; + } + + return valid; + } + + public void setClientInfo(Properties arg0) throws SQLClientInfoException { + } + + public void setClientInfo(String arg0, String arg1) + throws SQLClientInfoException { + } + + public boolean isWrapperFor(Class<?> iface) throws SQLException { + return false; + } + + public <T> T unwrap(Class<T> iface) throws SQLException { + return null; + } + +}
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/geo-backend/src/main/java/de/intevation/gnv/geobackend/sde/datasources/ArcSDEConnectionParams.java Fri Sep 28 12:13:50 2012 +0200 @@ -0,0 +1,92 @@ +/** + * Title: ArcSDEConnectionParams, $Header: /share/gdi/SDI-Suite/Repository/projekte/BSH-GDI/genericViewer/src/main/java/de/conterra/bsh/gdi/gnviewer/datasources/sde/ArcSDEConnectionParams.java,v 1.1 2007/11/22 07:40:11 blume Exp $ + * Source: $Source: /share/gdi/SDI-Suite/Repository/projekte/BSH-GDI/genericViewer/src/main/java/de/conterra/bsh/gdi/gnviewer/datasources/sde/ArcSDEConnectionParams.java,v $ + * created by: Stefan Blume (blume) + * erstellt am: 22.11.2007 + * Copyright: con terra GmbH, 2005 + * + * modified by: $Author: blume $ + * modified on: $Date: 2007/11/22 07:40:11 $ + * Version: $Revision: 1.1 $ + * TAG: $Name: $ + * locked from: $Locker: $ + * CVS State: $State: Exp $ + * Project: $ProjectName$ + */ +package de.intevation.gnv.geobackend.sde.datasources; + +import org.apache.log4j.Logger; + +/** + * The class <code>ArcSDEConnectionParams</code> fulfills the following purposes: + * <ol> + * <li></li> + * </ol> + * + * @author blume + * @version 1.0 + * @serial 1.0 + * @see + * @since 22.11.2007 08:10:11 + */ +public class ArcSDEConnectionParams { + + /** + * Default Logging instance + */ + private static Logger sLogger = Logger.getLogger(ArcSDEConnectionParams.class); + private static boolean sDebug = sLogger.isDebugEnabled(); + + private String mServer; + private int mInstance; + private String mDatabase; + private String mUser; + private String mPwd; + private int mInitConnections; + private int mMaxConnections; + private long mTimeToHold; + + public ArcSDEConnectionParams(String pServer, int pInstance, String pDatabase, String pUser, String pPwd, int pInitConnections, int pMaxConnections, long pTimeToHold) { + mServer = pServer; + mInstance = pInstance; + mDatabase = pDatabase; + mUser = pUser; + mPwd = pPwd; + mMaxConnections = pMaxConnections; + mInitConnections = pInitConnections; + mTimeToHold = pTimeToHold; + } + + + public String getServer() { + return mServer; + } + + public int getInstance() { + return mInstance; + } + + public String getDatabase() { + return mDatabase; + } + + public String getUser() { + return mUser; + } + + public String getPwd() { + return mPwd; + } + + public int getInitConnections() { + return mInitConnections; + } + + public int getMaxConnections() { + return mMaxConnections; + } + + public long getTimeToHold() { + return mTimeToHold; + } +}
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/geo-backend/src/main/java/de/intevation/gnv/geobackend/sde/datasources/ArcSDEResultSetMetaData.java Fri Sep 28 12:13:50 2012 +0200 @@ -0,0 +1,200 @@ +/** + * + */ +package de.intevation.gnv.geobackend.sde.datasources; + +import java.sql.ResultSetMetaData; +import java.sql.SQLException; +import java.util.List; + +/** + * @author Tim Englich <tim.englich@intevation.de> + * + */ +public class ArcSDEResultSetMetaData implements ResultSetMetaData { + + private List<ColDefinition> columnDefinitions = null; + /** + * Constructor + */ + public ArcSDEResultSetMetaData(List<ColDefinition> columnDefinitions) { + this.columnDefinitions = columnDefinitions; + } + + /** + * @see java.sql.ResultSetMetaData#getCatalogName(int) + */ + public String getCatalogName(int column) throws SQLException { + + return null; + } + + /** + * @see java.sql.ResultSetMetaData#getColumnClassName(int) + */ + public String getColumnClassName(int column) throws SQLException { + + int type = this.getColumnType(column); + Class clazz = java.lang.Object.class; + if (type == 2){ + clazz = int.class; + } + // TODO weitere Klassen Implementieren + return clazz.getName(); + } + + /** + * @see java.sql.ResultSetMetaData#getColumnCount() + */ + public int getColumnCount() throws SQLException { + return columnDefinitions.size(); + } + + /** + * @see java.sql.ResultSetMetaData#getColumnDisplaySize(int) + */ + public int getColumnDisplaySize(int column) throws SQLException { + + return 0; + } + + /** + * @see java.sql.ResultSetMetaData#getColumnLabel(int) + */ + public String getColumnLabel(int column) throws SQLException { + return columnDefinitions.get(column-1).getName(); + } + + /** + * @see java.sql.ResultSetMetaData#getColumnName(int) + */ + public String getColumnName(int column) throws SQLException { + return columnDefinitions.get(column-1).getName(); + } + + /** + * @see java.sql.ResultSetMetaData#getColumnType(int) + */ + public int getColumnType(int column) throws SQLException { + return columnDefinitions.get(column-1).getType(); + } + + /** + * @see java.sql.ResultSetMetaData#getColumnTypeName(int) + */ + public String getColumnTypeName(int column) throws SQLException { + + return null; + } + + /** + * @see java.sql.ResultSetMetaData#getPrecision(int) + */ + public int getPrecision(int column) throws SQLException { + + return 0; + } + + /** + * @see java.sql.ResultSetMetaData#getScale(int) + */ + public int getScale(int column) throws SQLException { + + return 0; + } + + /** + * @see java.sql.ResultSetMetaData#getSchemaName(int) + */ + public String getSchemaName(int column) throws SQLException { + + return null; + } + + /** + * @see java.sql.ResultSetMetaData#getTableName(int) + */ + public String getTableName(int column) throws SQLException { + + return null; + } + + /** + * @see java.sql.ResultSetMetaData#isAutoIncrement(int) + */ + public boolean isAutoIncrement(int column) throws SQLException { + + return false; + } + + /** + * @see java.sql.ResultSetMetaData#isCaseSensitive(int) + */ + public boolean isCaseSensitive(int column) throws SQLException { + + return false; + } + + /** + * @see java.sql.ResultSetMetaData#isCurrency(int) + */ + public boolean isCurrency(int column) throws SQLException { + + return false; + } + + /** + * @see java.sql.ResultSetMetaData#isDefinitelyWritable(int) + */ + public boolean isDefinitelyWritable(int column) throws SQLException { + + return false; + } + + /** + * @see java.sql.ResultSetMetaData#isNullable(int) + */ + public int isNullable(int column) throws SQLException { + + return 0; + } + + /** + * @see java.sql.ResultSetMetaData#isReadOnly(int) + */ + public boolean isReadOnly(int column) throws SQLException { + + return true; + } + + /** + * @see java.sql.ResultSetMetaData#isSearchable(int) + */ + public boolean isSearchable(int column) throws SQLException { + + return true; + } + + /** + * @see java.sql.ResultSetMetaData#isSigned(int) + */ + public boolean isSigned(int column) throws SQLException { + return false; + } + + /** + * @see java.sql.ResultSetMetaData#isWritable(int) + */ + public boolean isWritable(int column) throws SQLException { + return false; + } + + public boolean isWrapperFor(Class<?> iface) throws SQLException { + return false; + } + + public <T> T unwrap(Class<T> iface) throws SQLException { + return null; + } + +}
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/geo-backend/src/main/java/de/intevation/gnv/geobackend/sde/datasources/ArcSDEStatement.java Fri Sep 28 12:13:50 2012 +0200 @@ -0,0 +1,702 @@ +package de.intevation.gnv.geobackend.sde.datasources; + +import com.esri.sde.sdk.client.SDEPoint; +import com.esri.sde.sdk.client.SeColumnDefinition; +import com.esri.sde.sdk.client.SeConnection; +import com.esri.sde.sdk.client.SeException; +import com.esri.sde.sdk.client.SeExtent; +import com.esri.sde.sdk.client.SeFilter; +import com.esri.sde.sdk.client.SeLayer; +import com.esri.sde.sdk.client.SeQuery; +import com.esri.sde.sdk.client.SeQueryInfo; +import com.esri.sde.sdk.client.SeRaster; +import com.esri.sde.sdk.client.SeRasterAttr; +import com.esri.sde.sdk.client.SeRasterBand; +import com.esri.sde.sdk.client.SeRasterConstraint; +import com.esri.sde.sdk.client.SeRasterTile; +import com.esri.sde.sdk.client.SeRow; +import com.esri.sde.sdk.client.SeShape; +import com.esri.sde.sdk.client.SeShapeFilter; +import com.esri.sde.sdk.client.SeSqlConstruct; + +import com.vividsolutions.jts.geom.Geometry; +import com.vividsolutions.jts.geom.Point; +import com.vividsolutions.jts.geom.Polygon; + +import com.vividsolutions.jts.io.WKTReader; + +import de.intevation.gnv.geobackend.util.RedundancyRemover; + +import java.sql.Connection; +import java.sql.ResultSet; +import java.sql.SQLException; +import java.sql.SQLWarning; +import java.sql.Statement; + +import org.apache.log4j.Logger; + +/** + * @author Tim Englich (tim.englich@intevation.de) + * @author Sascha L. Teichmann (sascha.teichmann@intevation.de) + */ +public class ArcSDEStatement implements Statement { + + /** + * the logger, used to log exceptions and additonaly information + */ + private static Logger log = Logger.getLogger(ArcSDEStatement.class); + + private ArcSDEConnection connection = null; + + /** + * Constructor + */ + public ArcSDEStatement(ArcSDEConnection connection) { + this.connection = connection; + } + + /** + * @see java.sql.Statement#addBatch(java.lang.String) + */ + public void addBatch(String arg0) throws SQLException { + } + + /** + * @see java.sql.Statement#cancel() + */ + public void cancel() throws SQLException { + } + + /** + * @see java.sql.Statement#clearBatch() + */ + public void clearBatch() throws SQLException { + } + + /** + * @see java.sql.Statement#clearWarnings() + */ + public void clearWarnings() throws SQLException { + } + + /** + * @see java.sql.Statement#close() + */ + public void close() throws SQLException { + } + + /** + * @see java.sql.Statement#execute(java.lang.String) + */ + public boolean execute(String arg0) throws SQLException { + + return false; + } + + /** + * @see java.sql.Statement#execute(java.lang.String, int) + */ + public boolean execute(String arg0, int arg1) throws SQLException { + + return false; + } + + /** + * @see java.sql.Statement#execute(java.lang.String, int[]) + */ + public boolean execute(String arg0, int[] arg1) throws SQLException { + + return false; + } + + /** + * @see java.sql.Statement#execute(java.lang.String, java.lang.String[]) + */ + public boolean execute(String arg0, String[] arg1) throws SQLException { + + return false; + } + + /** + * @see java.sql.Statement#executeBatch() + */ + public int[] executeBatch() throws SQLException { + + return null; + } + + /** + * @see java.sql.Statement#executeQuery(java.lang.String) + */ + public ResultSet executeQuery(String statement) throws SQLException { + try { + SeQuery query = null; + if (statement.toLowerCase().contains("st_astext") || statement.toLowerCase().contains("intersects")){ + + String[] values = statement.toLowerCase().split("where", 2); + String where = values.length > 1 ? values[1].trim() : ""; + String[] tableNames = values[0].substring(values[0].indexOf("from")).replaceFirst("from", "").toUpperCase().trim().split(", "); + String columnValueString = values[0].substring(0, values[0].indexOf("from")).trim(); + columnValueString = columnValueString.replaceFirst("select", "").trim(); + String[] returnFields = columnValueString.split(", "); + String geometryColumnName = null; + String byClause = null; + int byClausePos = where.indexOf("group by"); + if (byClausePos < 0){ + byClausePos = where.indexOf("order by"); + } + if (byClausePos > 0){ + byClause = where.substring(byClausePos); + where = where.substring(0,byClausePos); + } + + for (int i = 0; i < returnFields.length; i++){ + returnFields[i] = returnFields[i].trim(); + if (returnFields[i].startsWith("st_astext(")){ + returnFields[i] = returnFields[i].replaceAll("st_astext", ""); + returnFields[i] = returnFields[i].substring(1, returnFields[i].length()-1); + geometryColumnName = returnFields[i]; + } + } + + for (int i = 0; i < tableNames.length; i++){ + tableNames[i] = tableNames[i].trim(); + } + + Geometry g = null; + int pos = where.indexOf("intersects"); + if (pos >= 0 ){ + String substr = where.substring(pos); + where = where.substring(0,where.lastIndexOf("intersects")); + int andPos = where.lastIndexOf("and"); + if (andPos < 0){ + andPos = 0; + } + where = where.substring(0,andPos); // TODO support or also + String intersectsStmt = substr.substring(0, substr.lastIndexOf(")")); // Spatial-Statment is the last one + String wkt = null; + if (intersectsStmt.contains("select")){ + // Anstelle eines WKT ist ein InnerSelect zum Bestimmen der Comparatorgeometrie gegeben. + String stmt = intersectsStmt.substring(intersectsStmt.indexOf("select"),intersectsStmt.lastIndexOf(")")); + ResultSet resultSet = this.executeQuery(stmt); + if (resultSet != null && resultSet.next()){ + // TODO: Support the selection of more than one Geometry + wkt = resultSet.getString("SHAPE"); + } + }else{ + wkt = intersectsStmt.substring(intersectsStmt.indexOf("\""), intersectsStmt.lastIndexOf("\"")).replace("\"", "").trim(); + } + g = new WKTReader().read(wkt.toUpperCase()); + + if (geometryColumnName == null){ + geometryColumnName = "SHAPE"; // TODO dynamisch aus Intersects auslesen. + } + + } + + return this.executeQuery(this.connection.getSeConnection(), tableNames, geometryColumnName, where, g, returnFields,byClause); + }else{ + query = new SeQuery(this.connection.getSeConnection()); + query.prepareSql(statement); + query.execute(); + return this.handleResultSet(query,false,null); + } + + } catch (Exception e) { + log.error(e,e); + throw new SQLException(e.getMessage()); + } + + + } + + private ResultSet executeQuery(SeConnection con, String[] pLayername, + String pSpatialColumnName, String pWhere, + Geometry g, String[] pReturnFields, String byClause) + throws SQLException { + log.debug("executeQuery()"); + try { + // get the layer for querying + boolean isRaster = pSpatialColumnName.equalsIgnoreCase("raster"); + + SeShapeFilter[] filters = null; + if (g != null){ + if (!isRaster){ + SeLayer lLayer = new SeLayer(con, pLayername[0], pSpatialColumnName); + SeShape shape = new SeShape(); + shape.setCoordRef(lLayer.getCoordRef()); + + SDEPoint[] lPoints = new ArcSDEUtils().createPoints(g); + + int searchMode = SeFilter.METHOD_AI; + if (g instanceof Polygon){ + shape.generatePolygon(lPoints.length, 1, null, lPoints); + }else if (g instanceof Point){ + shape.generatePoint(1, lPoints); + searchMode = SeFilter.METHOD_PC; + } + + SeShapeFilter filter = new SeShapeFilter(pLayername[0], + pSpatialColumnName, shape,searchMode); + filters = new SeShapeFilter[1]; + filters[0] = filter; + } + } + + SeQuery spatialQuery = null; + SeSqlConstruct sqlCons = new SeSqlConstruct(pLayername, pWhere); + spatialQuery = new SeQuery(con);//, pReturnFields, sqlCons); + + SeQueryInfo queryInfo = new SeQueryInfo(); + queryInfo.setColumns(pReturnFields); + + if (byClause != null){ + queryInfo.setByClause(byClause); + } + + queryInfo.setConstruct(sqlCons); + spatialQuery.prepareQueryInfo(queryInfo); + + /* + * Set spatial constraints + */ + if (filters != null){ + spatialQuery.setSpatialConstraints(SeQuery.SE_OPTIMIZE, false, + filters); + } + + if (!isRaster){ + spatialQuery.execute(); + } + + return this.handleResultSet(spatialQuery,isRaster,g); + + } catch (Exception e) { + if (e instanceof SeException){ + ArcSDEUtils.printError((SeException)e); + }else{ + log.error(e.getMessage(), e); + } + + throw new SQLException(e.getMessage()); + } + } + + /** + * @see java.sql.Statement#executeUpdate(java.lang.String) + */ + public int executeUpdate(String arg0) throws SQLException { + + return 0; + } + + /** + * @see java.sql.Statement#executeUpdate(java.lang.String, int) + */ + public int executeUpdate(String arg0, int arg1) throws SQLException { + + return 0; + } + + /** + * @see java.sql.Statement#executeUpdate(java.lang.String, int[]) + */ + public int executeUpdate(String arg0, int[] arg1) throws SQLException { + + return 0; + } + + /** + * @see java.sql.Statement#executeUpdate(java.lang.String, java.lang.String[]) + */ + public int executeUpdate(String arg0, String[] arg1) throws SQLException { + + return 0; + } + + /** + * @see java.sql.Statement#getConnection() + */ + public Connection getConnection() throws SQLException { + return this.connection; + } + + /** + * @see java.sql.Statement#getFetchDirection() + */ + public int getFetchDirection() throws SQLException { + + return 0; + } + + /** + * @see java.sql.Statement#getFetchSize() + */ + public int getFetchSize() throws SQLException { + + return 0; + } + + /** + * @see java.sql.Statement#getGeneratedKeys() + */ + public ResultSet getGeneratedKeys() throws SQLException { + + return null; + } + + /** + * @see java.sql.Statement#getMaxFieldSize() + */ + public int getMaxFieldSize() throws SQLException { + + return 0; + } + + /** + * @see java.sql.Statement#getMaxRows() + */ + public int getMaxRows() throws SQLException { + + return 0; + } + + /** + * @see java.sql.Statement#getMoreResults() + */ + public boolean getMoreResults() throws SQLException { + + return false; + } + + /** + * @see java.sql.Statement#getMoreResults(int) + */ + public boolean getMoreResults(int arg0) throws SQLException { + + return false; + } + + /** + * @see java.sql.Statement#getQueryTimeout() + */ + public int getQueryTimeout() throws SQLException { + + return 0; + } + + /** + * @see java.sql.Statement#getResultSet() + */ + public ResultSet getResultSet() throws SQLException { + + return null; + } + + /** + * @see java.sql.Statement#getResultSetConcurrency() + */ + public int getResultSetConcurrency() throws SQLException { + + return 0; + } + + /** + * @see java.sql.Statement#getResultSetHoldability() + */ + public int getResultSetHoldability() throws SQLException { + + return 0; + } + + /** + * @see java.sql.Statement#getResultSetType() + */ + public int getResultSetType() throws SQLException { + + return 0; + } + + /** + * @see java.sql.Statement#getUpdateCount() + */ + public int getUpdateCount() throws SQLException { + + return 0; + } + + /** + * @see java.sql.Statement#getWarnings() + */ + public SQLWarning getWarnings() throws SQLException { + + return null; + } + + /** + * @see java.sql.Statement#setCursorName(java.lang.String) + */ + public void setCursorName(String arg0) throws SQLException { + } + + /** + * @see java.sql.Statement#setEscapeProcessing(boolean) + */ + public void setEscapeProcessing(boolean arg0) throws SQLException { + } + + /** + * @see java.sql.Statement#setFetchDirection(int) + */ + public void setFetchDirection(int arg0) throws SQLException { + } + + /** + * @see java.sql.Statement#setFetchSize(int) + */ + public void setFetchSize(int arg0) throws SQLException { + } + + /** + * @see java.sql.Statement#setMaxFieldSize(int) + */ + public void setMaxFieldSize(int arg0) throws SQLException { + } + + /** + * @see java.sql.Statement#setMaxRows(int) + */ + public void setMaxRows(int arg0) throws SQLException { + } + + /** + * @see java.sql.Statement#setQueryTimeout(int) + */ + public void setQueryTimeout(int arg0) throws SQLException { + } + + /** + * Copied from de.intevation.gnv.geobackend.sde.datasources.SDEQuery + * @param pSeQuery + * @return + * @throws SeException + */ + private ResultSet handleResultSet(SeQuery pSeQuery, boolean isRaster, Geometry geometry) throws SeException { + + boolean debug = log.isDebugEnabled(); + + if (debug) { + log.debug("ArcSDEStatement.handleResultSet()"); + } + + SDEResultSet lSet = new SDEResultSet(); + SeRow row = null; + int lCount; + if (!isRaster){ + RedundancyRemover [] removers = null; + SeColumnDefinition [] lCols = null; + + for (lCount = 0; (row = pSeQuery.fetch()) != null; lCount++) { + // one time execution + if (lCount == 0) { + // analyze cols of result set + lCols = row.getColumns(); + for (SeColumnDefinition lCol : lCols) { + lSet.addCol(new ColDefinition(lCol.getName(), lCol.getType())); + // notice: esri-types have been copied into colDefinition class! + } + removers = new RedundancyRemover[lCols.length]; + for (int i = 0; i < removers.length; ++i) { + removers[i] = new RedundancyRemover(); + } + } + short lNumCols = row.getNumColumns(); + Row lBackingRow = new Row(lNumCols); + for (int i = 0; i < lNumCols; i++) { + lBackingRow.addObject( + removers[i].filter(row.getObject(i)), + i); + } + lSet.addRow(lBackingRow); + } + + if (debug && removers != null) { + log.debug("datasets: " + lCount); + StringBuilder sb = new StringBuilder("removed redundancy: "); + float percent = 100f/lCount; + for (int i = 0; i < removers.length; ++i) { + if (i > 0) sb.append(", "); + sb.append(lCols[i].getName()) + .append(": ").append(removers[i].numRemoved()) + .append(" (").append(removers[i].numRemoved()*percent) + .append("%)"); + } + log.debug(sb.toString()); + } + }else{ + try { + pSeQuery.execute(); + row = pSeQuery.fetch(); + SeRasterAttr attr = row.getRaster(0); + SeRaster raster = attr.getRasterInfo(); + SeRasterBand [] bands = raster.getBands(); + SeRasterBand rasterBand = bands[0]; + + SeExtent extent = rasterBand.getExtent(); + + /* + if (debug) { + log.debug("Extent: " + + extent.getMinX() + " " + extent.getMinY() + " "+ + extent.getMaxX() + " " + extent.getMaxY()); + log.debug("Query geometry: "+geometry.toText()); + } + */ + + double x = ((Point)geometry).getX(); + double y = ((Point)geometry).getY(); + + boolean isPointInRaster = + x >= extent.getMinX() && x <= extent.getMaxX() + && y >= extent.getMinY() && y <= extent.getMaxY(); + + if (isPointInRaster){ + + if (row == null){ + pSeQuery.execute(); + row = pSeQuery.fetch(); + } + + double midX = 0.5d*(extent.getMinX() + extent.getMaxX()); + double midY = 0.5d*(extent.getMinY() + extent.getMaxY()); + + SDEPoint origin = rasterBand.getTileOrigin(); + + double maxX = origin.getX() < midX + ? extent.getMaxX() + : extent.getMinX(); + + double maxY = origin.getY() < midY + ? extent.getMaxY() + : extent.getMinY(); + + /* + 0 = origin.getX()*mx + bx + rasterBand.getBandWidth() = maxX + bx + + rasterBand.getBandWidth() = (maxX-origin.getX())*mx + */ + + double mx = rasterBand.getBandWidth()/(maxX-origin.getX()); + double bx = -origin.getX()*mx; + double px = mx*x + bx; + + double my = rasterBand.getBandHeight()/(maxY-origin.getY()); + double by = -origin.getY()*my; + double py = my*y + by; + + /* + if (debug) { + log.debug("p(x, y): " + px + " / " + py); + } + */ + + SeRasterConstraint constraint = new SeRasterConstraint(); + constraint.setLevel(0); // best resolution + constraint.setBands(rasterBand.getBandNumber()); + int tx = (int)Math.floor(px / rasterBand.getTileWidth()); + int ty = (int)Math.floor(py / rasterBand.getTileHeight()); + constraint.setEnvelope(tx, ty, tx, ty); + + pSeQuery.queryRasterTile(constraint); + SeRasterTile tile = row.getRasterTile(); + + if (tile != null){ + double[] tileValues = new double[tile.getNumPixels()]; + tileValues = tile.getPixels(tileValues); + lSet.addCol(new ColDefinition("tile", ColDefinition.FLOAT64)); + Row lBackingRow = new Row(1); + + /* + 0 = wx1*mt + bt + tileWidth-EPS = wx2*mt + bt + tileWidth-EPS = mt*(wx2 - wx1) + */ + + double wx1 = (rasterBand.getTileWidth()*tx - bx)/mx; + double wx2 = (rasterBand.getTileWidth()*(tx+1) - bx)/mx; + double mxt = (rasterBand.getTileWidth()-1e-5d)/(wx2 - wx1); + double bxt = -wx1*mxt; + + double wy1 = (rasterBand.getTileHeight()*ty - by)/my; + double wy2 = (rasterBand.getTileHeight()*(ty+1) - by)/my; + double myt = (rasterBand.getTileHeight()-1e-5d)/(wy2 - wy1); + double byt = -wy1*myt; + + RasterObject ro = new RasterObject( + mxt, bxt, + myt, byt, + tile.getColumnIndex(), + tile.getRowIndex(), + tileValues, + rasterBand.getTileWidth(), + rasterBand.getTileHeight()); + lBackingRow.addObject(ro, 0); + lSet.addRow(lBackingRow); + + /* + if (debug) { + log.debug("x / y: " + x + " / " + y); + log.debug("wx1: " + wx1); + log.debug("wx1 -> " + (wx1*mxt+bxt)); + log.debug("wx2: " + wx2); + log.debug("wx2 -> " + (wx2*mxt+bxt)); + log.debug("wx2 - wx1: " + Math.abs(wx2-wx1)); + log.debug("wy2 - wy1: " + Math.abs(wy2-wy1)); + log.debug("pix: " + (x*mxt + bxt)+ " " + (y*myt + byt)); + log.debug("requesting tile: " + tx + " / " + ty); + log.debug("got tile: " + tile.getColumnIndex() + " / " + tile.getRowIndex()); + log.debug("tile orig: " + origin.getX() + " / " + origin.getY()); + log.debug("tile width: " + rasterBand.getTileWidth()); + log.debug("tile height: " + rasterBand.getTileHeight()); + log.debug("Rasterdimesion (Pixel) " + + rasterBand.getBandWidth() + " / " + rasterBand.getBandHeight()); + log.debug("BAND-ID "+rasterBand.getId().longValue()); + log.debug("Pixels: "+ tile.getNumPixels()); + log.debug("Column / Row "+tile.getColumnIndex()+" / "+tile.getRowIndex()); + } + */ + } + } + else{ + log.debug("The Query doesn't deliver any Information " + + "because the Point is located outside the Raster."); + } + } catch (Exception e) { + log.error(e,e); + } + } + pSeQuery.close(); + return lSet; + } + + public boolean isClosed() throws SQLException { + return false; + } + + public boolean isPoolable() throws SQLException { + return false; + } + + public void setPoolable(boolean arg0) throws SQLException { + } + + public boolean isWrapperFor(Class<?> iface) throws SQLException { + return false; + } + + public <T> T unwrap(Class<T> iface) throws SQLException { + return null; + + } +}
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/geo-backend/src/main/java/de/intevation/gnv/geobackend/sde/datasources/ArcSDEUtils.java Fri Sep 28 12:13:50 2012 +0200 @@ -0,0 +1,163 @@ +/** + * Title: ArcSDEUtils, $Header: /share/gdi/SDI-Suite/Repository/projekte/BSH-GDI/genericViewer/src/main/java/de/conterra/bsh/gdi/gnviewer/datasources/sde/ArcSDEUtils.java,v 1.7 2007/11/28 14:05:39 blume Exp $ + * Source: $Source: /share/gdi/SDI-Suite/Repository/projekte/BSH-GDI/genericViewer/src/main/java/de/conterra/bsh/gdi/gnviewer/datasources/sde/ArcSDEUtils.java,v $ + * created by: Stefan Blume (blume) + * erstellt am: 21.11.2007 + * Copyright: con terra GmbH, 2005 + * + * modified by: $Author: blume $ + * modified on: $Date: 2007/11/28 14:05:39 $ + * Version: $Revision: 1.7 $ + * TAG: $Name: $ + * locked from: $Locker: $ + * CVS State: $State: Exp $ + * Project: $ProjectName$ + */ +package de.intevation.gnv.geobackend.sde.datasources; + +import java.util.Vector; + +import org.apache.log4j.Logger; + +import com.esri.sde.sdk.client.SDEPoint; +import com.esri.sde.sdk.client.SeColumnDefinition; +import com.esri.sde.sdk.client.SeError; +import com.esri.sde.sdk.client.SeException; +import com.esri.sde.sdk.client.SeTable; +import com.vividsolutions.jts.geom.Coordinate; +import com.vividsolutions.jts.geom.Geometry; +import com.vividsolutions.jts.geom.Point; +import com.vividsolutions.jts.geom.Polygon; + +/** + * The class <code>ArcSDEUtils</code> fulfills the following purposes: + * <ol> + * <li></li> + * </ol> + * + * @author blume + * @version 1.0 + * @serial 1.0 + * @see + * @since 21.11.2007 08:20:40 + */ +public class ArcSDEUtils { + + /** + * Default Logging instance + */ + private static Logger sLogger = Logger.getLogger(ArcSDEUtils.class); + + public void displayStats(SeTable.SeTableStats tableStats) { + + sLogger.debug("\n--> Table Statistics\n"); + if (tableStats != null) { + + sLogger.debug("Average - " + tableStats.getAverage()); + sLogger.debug("No of rows - " + tableStats.getCount()); + sLogger.debug("Maximum Value - " + tableStats.getMax()); + sLogger.debug("Minimum Value - " + tableStats.getMin()); + sLogger.debug("No of distinct values - " + + tableStats.getNumDistinct()); + sLogger.debug("Standard Deviation - " + tableStats.getStdDev()); + + sLogger.debug("Distinct type - " + + ArcSDEUtils.resolveType(tableStats + .getDistinctType())); + + int[] distinctFreq = tableStats.getDistinctValueFrequencies(); + Vector distinctValues = tableStats.getDistinctValues(); + sLogger.debug("Distinct values & their frequencies : "); + for (int i = 0; i < distinctFreq.length; i++) + sLogger.debug(distinctValues.elementAt(i) + " - " + + distinctFreq[i]); + }// End if + + }// End displayStats + + /** + * Takes an integer corresponding to an ArcSDE data type and returns a + * string description of the type. + * + * @param type + * SDE data type bit-mask. + */ + public static String resolveType(int type) { + + String typeName = "Invalid Type"; + switch (type) { + + case SeColumnDefinition.TYPE_SMALLINT: + typeName = "Small Int"; + break; + case SeColumnDefinition.TYPE_INTEGER: + typeName = "Int"; + break; + case SeColumnDefinition.TYPE_FLOAT: + typeName = "Float"; + break; + case SeColumnDefinition.TYPE_DOUBLE: + typeName = "Double"; + break; + case SeColumnDefinition.TYPE_STRING: + typeName = "String"; + break; + case SeColumnDefinition.TYPE_BLOB: + typeName = "Blob"; + break; + case SeColumnDefinition.TYPE_DATE: + typeName = "Date"; + break; + case SeColumnDefinition.TYPE_SHAPE: + typeName = "Shape"; + break; + case SeColumnDefinition.TYPE_RASTER: + typeName = "Raster"; + break; + } + return typeName; + }// End method resolveType + + public static void printError(SeException exception) { + + SeError error = exception.getSeError(); + + sLogger.debug("\n ArcSDE Error Number : " + error.getSdeError()); + sLogger.debug(" Error Description : " + error.getErrDesc()); + + int extError = error.getExtError(); + if (extError != 0) + sLogger.debug(" Extended Error Number : " + extError); + + String desc = error.getSdeErrMsg(); + if (desc != null && desc.length() != 0) + sLogger.debug(" Extended Error Description : " + desc); + + desc = error.getExtErrMsg(); + if (desc != null && desc.length() != 0) + sLogger.debug(" Extended Error Description : " + desc); + + sLogger.debug(exception); + + }// End printError + + public SDEPoint[] createPoints(Geometry g) { + sLogger.debug("createPoints()"); + Coordinate[] coords = null; + if (g instanceof Polygon) { + coords = ((Polygon) g).getCoordinates(); + }else if (g instanceof Point){ + coords = ((Point)g).getCoordinates(); + } + if (coords != null){ + SDEPoint[] lSDEPoints = new SDEPoint[coords.length]; + for (int i = 0; i < coords.length; i++) { + lSDEPoints[i] = new SDEPoint(coords[i].x, coords[i].y); + } + return lSDEPoints; + }else{ + return null; + } + + } +}
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/geo-backend/src/main/java/de/intevation/gnv/geobackend/sde/datasources/ColDefinition.java Fri Sep 28 12:13:50 2012 +0200 @@ -0,0 +1,86 @@ +/** + * Title: ColDefinition, $Header: /share/gdi/SDI-Suite/Repository/projekte/BSH-GDI/genericViewer/src/main/java/de/conterra/bsh/gdi/gnviewer/datasources/ColDefinition.java,v 1.2 2007/12/21 12:31:15 blume Exp $ + * Source: $Source: /share/gdi/SDI-Suite/Repository/projekte/BSH-GDI/genericViewer/src/main/java/de/conterra/bsh/gdi/gnviewer/datasources/ColDefinition.java,v $ + * created by: Stefan Blume (blume) + * erstellt am: 21.11.2007 + * Copyright: con terra GmbH, 2005 + * + * modified by: $Author: blume $ + * modified on: $Date: 2007/12/21 12:31:15 $ + * Version: $Revision: 1.2 $ + * TAG: $Name: $ + * locked from: $Locker: $ + * CVS State: $State: Exp $ + * Project: $ProjectName$ + */ +package de.intevation.gnv.geobackend.sde.datasources; + +import org.apache.log4j.Logger; + +/** + * The class <code>ColDefinition</code> fulfills the following purposes: + * <ol> + * <li></li> + * </ol> + * + * @author blume + * @version 1.0 + * @serial 1.0 + * @see + * @since 21.11.2007 10:59:45 + */ +public class ColDefinition { + + /** + * Default Logging instance + */ + private static Logger sLogger = Logger.getLogger(ColDefinition.class); + + /** + * @deprecated + */ + public static final int SMALLINT = 1; + /** + * @deprecated + */ + public static final int INTEGER = 2; + /** + * @deprecated + */ + public static final int FLOAT = 3; + /** + * @deprecated + */ + public static final int DOUBLE = 4; + public static final int INT16 = 1; + public static final int INT32 = 2; + public static final int FLOAT32 = 3; + public static final int FLOAT64 = 4; + public static final int STRING = 5; + public static final int BLOB = 6; + public static final int DATE = 7; + public static final int SHAPE = 8; + public static final int RASTER = 9; + public static final int XML = 10; + public static final int INT64 = 11; + public static final int UUID = 12; + public static final int CLOB = 13; + public static final int NSTRING = 14; + public static final int NCLOB = 15; + + private String mName; + private int mType; + + public ColDefinition(String pName, int pType) { + mName = pName; + mType = pType; + } + + public String getName() { + return mName; + } + + public int getType() { + return mType; + } +}
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/geo-backend/src/main/java/de/intevation/gnv/geobackend/sde/datasources/Datasource.java Fri Sep 28 12:13:50 2012 +0200 @@ -0,0 +1,92 @@ +/* Copyright (C) 2007 con terra GmbH (http://www.conterra.de) + * All rights reserved + * + * $Id: Datasource.java,v 1.4 2008/01/30 12:38:34 blume Exp $ + * + * created by: drewnak + * created at : 21.11.2007 + * created at : 14:25:50 + * + * modified by: $Author: blume $ + * modified at: $Date: 2008/01/30 12:38:34 $ + */ +package de.intevation.gnv.geobackend.sde.datasources; + +import java.util.Map; + +import de.intevation.gnv.geobackend.sde.datasources.exception.DatasourceException; + +/** + * Abstract class for handling different datasources. + * This method provides a static method for creating dataosurce objects. + * + * @author drewnak + */ +public abstract class Datasource { + + protected Map mParameters; + + protected String mName; + + + /** + * Triggers the creation of a dataosurce object. Following the guidelines, the parameters for creating this datasource are delivered by the configuration file. + * + * @param pName a name for the datasource. + * @param pClassname The name of the class extending this abstract datasource class. Used to create a new instance. + * @param pParams a Map of Parameters needed for the initialization of the datasource. + * @return a datasourceobject + * @throws DatasourceException if the class could not be found or the initialization of the datasource fails. + */ + public static final Datasource create(String pName, String pClassname, + Map pParams) throws DatasourceException { + Class lDatasourceClass; + try { + lDatasourceClass = Class.forName(pClassname); + } catch (ClassNotFoundException e) { + throw new DatasourceException("Could not load class " + pClassname); + } + Datasource lNewInstance; + try { + lNewInstance = (Datasource) lDatasourceClass.newInstance(); + } catch (Exception e) { + throw new DatasourceException("Could not create instance of " + pClassname, e); + } + lNewInstance.init(pName, pParams); + + return lNewInstance; + } + + /** + * An abstract method for the initialization of a datasource instance. + * Of course, only the concrete implementation knwos about the ncessary parameters for the initialization. + * + * @param pName a name for the datasource. + * @param pParams a Map of Parameters needed for the initialization of the datasource. + */ + protected abstract void init(String pName, Map pParams) throws DatasourceException; + + /** + * @return + */ + public String getName() { + return mName; + } + + + /** + * This method returns a datasourceconnection. + * Only the concrete implementation of the Datasource knwos about the DatasourceConnection-Implementation to return. + * + * @return a DatasourceConnection + */ + public abstract DatasourceConnection getConnection(); + + /** + * Returns a DatasourceConnection. This method is usualy called after an executed query. + * See: Some datasource implementations need to explicitly close an existing connection. + * + * @param pConnection the connection to be returned. + */ + public abstract void returnConnection(DatasourceConnection pConnection); +}
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/geo-backend/src/main/java/de/intevation/gnv/geobackend/sde/datasources/DatasourceConnection.java Fri Sep 28 12:13:50 2012 +0200 @@ -0,0 +1,31 @@ +/** + * Title: IDatasourceConnection, $Header: /share/gdi/SDI-Suite/Repository/projekte/BSH-GDI/genericViewer/src/main/java/de/conterra/bsh/gdi/gnviewer/datasources/DatasourceConnection.java,v 1.2 2008/01/30 12:38:34 blume Exp $ + * Source: $Source: /share/gdi/SDI-Suite/Repository/projekte/BSH-GDI/genericViewer/src/main/java/de/conterra/bsh/gdi/gnviewer/datasources/DatasourceConnection.java,v $ + * created by: Stefan Blume (blume) + * erstellt am: 21.11.2007 + * Copyright: con terra GmbH, 2005 + * + * modified by: $Author: blume $ + * modified on: $Date: 2008/01/30 12:38:34 $ + * Version: $Revision: 1.2 $ + * TAG: $Name: $ + * locked from: $Locker: $ + * CVS State: $State: Exp $ + * Project: $ProjectName$ + */ +package de.intevation.gnv.geobackend.sde.datasources; + +/** + * The interface <code>DatasourceConnection</code> fulfills the following purposes: + * <ol> + * <li>Beeing a marker interface for datasource connections.</li> + * </ol> + * + * @author blume + * @version 1.0 + * @serial 1.0 + * @see + * @since 21.11.2007 08:50:20 + */ +public interface DatasourceConnection { +}
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/geo-backend/src/main/java/de/intevation/gnv/geobackend/sde/datasources/RasterObject.java Fri Sep 28 12:13:50 2012 +0200 @@ -0,0 +1,306 @@ +/** + * + */ +package de.intevation.gnv.geobackend.sde.datasources; + +import com.vividsolutions.jts.geom.Coordinate; + +import java.io.Serializable; + +import org.apache.commons.math.ArgumentOutsideDomainException; + +import org.apache.commons.math.analysis.interpolation.SplineInterpolator; + +import org.apache.commons.math.analysis.polynomials.PolynomialSplineFunction; + +import org.apache.log4j.Logger; + +/** + * @author Tim Englich (tim.englich@intevation.de) + * @author Sascha L. Teichmann (sascha.teichmann@intevation.de) + */ +public class RasterObject +implements Serializable +{ + private static Logger log = Logger.getLogger(RasterObject.class); + + public static final int NEAREST_NEIGHBOR = 0; + public static final int BILINEAR = 1; + public static final int BICUBIC = 2; + + private double mx; + private double bx; + private double my; + private double by; + + private int columnIndex; + private int rowIndex; + + private int tileWidth; + private int tileHeight; + + private double [] rasterData; + + private transient PolynomialSplineFunction [] splines; + private transient double [] columns; + + public RasterObject() { + } + + public RasterObject( + double mx, double bx, + double my, double by, + int columnIndex, + int rowIndex, + double [] rasterData, + int tileWidth, + int tileHeight + ){ + this.mx = mx; + this.bx = bx; + this.my = my; + this.by = by; + this.columnIndex = columnIndex; + this.rowIndex = rowIndex; + this.rasterData = rasterData; + this.tileWidth = tileWidth; + this.tileHeight = tileHeight; + } + + public boolean contains(Coordinate coordinate) { + double px = mx*coordinate.x + bx; + double py = my*coordinate.y + by; + return px >= 0d && py >= 0d && px < tileWidth && py < tileHeight; + } + + public final double get(int posX, int posY) { + return rasterData[posY*tileWidth + posX]; + } + + public int getTileWidth() { + return tileWidth; + } + + public int getTileHeight() { + return tileWidth; + } + + public int getColumnIndex() { + return columnIndex; + } + + public int getRowIndex() { + return rowIndex; + } + + public double getValue(Coordinate coordinate) { + return getValue(coordinate, NEAREST_NEIGHBOR); + } + + public final double interpolateBilinear(double px, double py) { + + if (px < 0.5d) { // left border + if (py < 0.5d) { // upper left edge + return rasterData[0]; + } + if (py > tileHeight-0.5d) { // lower left edge + return rasterData[rasterData.length-tileWidth]; + } + // center left + int i = (int)(py -= 0.5d); + double t = py - i; + i *= tileWidth; + return (1d-t)*rasterData[i] + t*rasterData[i+tileWidth]; + } + + if (px > tileWidth-0.5d) { // right border + if (py < 0.5d) { // upper right edge + return rasterData[tileWidth-1]; + } + if (py > tileHeight-0.5d) { // lower right edge + return rasterData[rasterData.length-1]; + } + // center right + int i = (int)(py -= 0.5d); + double t = py - i; + i = i*tileWidth + tileWidth-1; + return (1d-t)*rasterData[i] + t*rasterData[i+tileWidth]; + } + + if (py < 0.5d) { // top border + int i = (int)(px -= 0.5d); + double t = px - i; + return (1d-t)*rasterData[i] + t*rasterData[i+1]; + } + + if (py > tileHeight-0.5d) { // bottom border + int i = (int)(px -= 0.5d); + double t = px - i; + i += rasterData.length-tileWidth; + return (1d-t)*rasterData[i] + t*rasterData[i+1]; + } + + // center + int i = (int)(py -= 0.5d); + int j = (int)(px -= 0.5d); + double ti = py - i; + double tj = px - j; + + int idx = i*tileWidth + j; + + double v1j1 = rasterData[idx]; + double v2j1 = rasterData[idx+1]; + + idx += tileWidth; + + double v1j2 = rasterData[idx]; + double v2j2 = rasterData[idx+1]; + + double v1 = (1d-tj)*v1j1 + tj*v2j1; + double v2 = (1d-tj)*v1j2 + tj*v2j2; + + return (1d-ti)*v1 + ti*v2; + } + + protected PolynomialSplineFunction [] createSplines() { + PolynomialSplineFunction [] splines = + new PolynomialSplineFunction[tileHeight]; + + double [] x = new double[tileWidth]; + double [] y = new double[tileWidth]; + + for (int i = 0; i < tileWidth; ++i) { + x[i] = i + 0.5d; + } + + columns = new double[tileHeight]; + + SplineInterpolator interpolator = new SplineInterpolator(); + + for (int i = 0; i < tileHeight; ++i) { + int row = i*tileWidth; + for (int j = 0; j < tileWidth; ++j) { + y[j] = rasterData[row + j]; + } + splines[i] = interpolator.interpolate(x, y); + columns[i] = i + 0.5d; + } + + return splines; + } + + protected synchronized PolynomialSplineFunction [] getSplines() { + if (splines == null) { + splines = createSplines(); + } + return splines; + } + + protected double [] columnValues(double x) { + PolynomialSplineFunction [] splines = getSplines(); + double [] y = new double[tileHeight]; + try { + for (int i = 0; i < tileHeight; ++i) { + y[i] = splines[i].value(x); + } + } + catch (ArgumentOutsideDomainException aode) { + log.error(aode.getLocalizedMessage(), aode); + } + return y; + } + + private final double evaluateBicubicVertical(double x, double y) { + try { + SplineInterpolator interpolator = new SplineInterpolator(); + double [] cs = columnValues(x); + PolynomialSplineFunction spline = interpolator.interpolate( + columns, cs); + return spline.value(y); + } + catch (ArgumentOutsideDomainException aode) { + log.error(aode.getLocalizedMessage(), aode); + } + return 0d; + } + + private final double evaluateBicubicHorizonal(int index, double x) { + try { + PolynomialSplineFunction spline = getSplines()[index]; + return spline.value(x); + } + catch (ArgumentOutsideDomainException aode) { + log.error(aode.getLocalizedMessage(), aode); + } + return 0d; + } + + public double interpolateBicubic(double px, double py) { + + if (px <= 0.5d) { // left + if (py <= 0.5d) { // upper left + return rasterData[0]; + } + if (py >= tileHeight-0.5d) { + return rasterData[rasterData.length-tileWidth]; + } + return evaluateBicubicVertical(0.5d, py); + } + + if (px >= tileWidth-0.5d) { // right + if (py < 0.5d) { // upper right edge + return rasterData[tileWidth-1]; + } + if (py > tileHeight-0.5d) { // lower right edge + return rasterData[rasterData.length-1]; + } + return evaluateBicubicVertical(tileHeight-0.5d, py); + } + + if (py <= 0.5d) { // top + return evaluateBicubicHorizonal(0, px); + } + + if (py >= tileHeight-0.5d) { // bottom + return evaluateBicubicHorizonal(tileHeight-1, px); + } + + return evaluateBicubicVertical(px, py); + } + + public double getValue(Coordinate coordinate, int interpolationType) { + + double px = mx*coordinate.x + bx; + double py = my*coordinate.y + by; + + if (px < 0d || py < 0d || px >= tileWidth || py >= tileHeight) { + return Double.NaN; + } + + switch (interpolationType) { + case BILINEAR: return interpolateBilinear(px, py); + case BICUBIC : return interpolateBicubic (px, py); + } + + int posX = Math.min(tileWidth-1, (int)Math.round(px)); + int posY = Math.min(tileHeight-1, (int)Math.round(py)); + + return get(posX, posY); + } + + public static final int getInterpolationType(String key) { + if (key == null || (key = key.trim().toLowerCase()).length() == 0) { + return NEAREST_NEIGHBOR; + } + + if ("bilinear".equals(key)) { + return BILINEAR; + } + + if ("bicubic".equals(key)) { + return BICUBIC; + } + + return NEAREST_NEIGHBOR; + } +}
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/geo-backend/src/main/java/de/intevation/gnv/geobackend/sde/datasources/ResultSet.java Fri Sep 28 12:13:50 2012 +0200 @@ -0,0 +1,68 @@ +/** + * Title: ResultSet, $Header: /share/gdi/SDI-Suite/Repository/projekte/BSH-GDI/genericViewer/src/main/java/de/conterra/bsh/gdi/gnviewer/datasources/ResultSet.java,v 1.2 2008/01/30 12:38:34 blume Exp $ + * Source: $Source: /share/gdi/SDI-Suite/Repository/projekte/BSH-GDI/genericViewer/src/main/java/de/conterra/bsh/gdi/gnviewer/datasources/ResultSet.java,v $ + * created by: Stefan Blume (blume) + * erstellt am: 21.11.2007 + * Copyright: con terra GmbH, 2005 + * + * modified by: $Author: blume $ + * modified on: $Date: 2008/01/30 12:38:34 $ + * Version: $Revision: 1.2 $ + * TAG: $Name: $ + * locked from: $Locker: $ + * CVS State: $State: Exp $ + * Project: $ProjectName$ + */ +package de.intevation.gnv.geobackend.sde.datasources; + +import org.apache.log4j.Logger; + +/** + * The class <code>ResultSet</code> fulfills the following purposes: + * <ol> + * <li>Providing an abstract object for dealing with result sets.</li> + * </ol> + * + * @author blume + * @version 1.0 + * @serial 1.0 + * @see + * @since 21.11.2007 09:26:53 + */ +public abstract class ResultSet implements java.sql.ResultSet { + + /** + * Default Logging instance + */ + private static Logger sLogger = Logger.getLogger(ResultSet.class); + + /** + * Returns the number of hits. + * + * @return count of results. + */ + public abstract int getCount(); + + /** + * Returns the number of columns returned through the query. + * + * @return the number of columns. + */ + public abstract int getNumberOfColumns(); + + /** + * A ResultSet can tell about the concrete definition of each column. + * This method returns an array of {@link de.conterra.bsh.gdi.gnviewer.datasources.ColDefinition}-Objects. + * + * @return the definitions describing the result set. + */ + public abstract ColDefinition[] getColumnDefinitions(); + + /** + * Returns an array of Rows. The rows contain the "content" of the current ResultSet. + * + * @return the results itself. + */ + public abstract Row[] getResults(); + +}
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/geo-backend/src/main/java/de/intevation/gnv/geobackend/sde/datasources/Row.java Fri Sep 28 12:13:50 2012 +0200 @@ -0,0 +1,312 @@ +/** + * Title: Row, $Header: /share/gdi/SDI-Suite/Repository/projekte/BSH-GDI/genericViewer/src/main/java/de/conterra/bsh/gdi/gnviewer/datasources/Row.java,v 1.4 2008/01/30 12:38:34 blume Exp $ + * Source: $Source: /share/gdi/SDI-Suite/Repository/projekte/BSH-GDI/genericViewer/src/main/java/de/conterra/bsh/gdi/gnviewer/datasources/Row.java,v $ + * created by: Stefan Blume (blume) + * erstellt am: 21.11.2007 + * Copyright: con terra GmbH, 2005 + * + * modified by: $Author: blume $ + * modified on: $Date: 2008/01/30 12:38:34 $ + * Version: $Revision: 1.4 $ + * TAG: $Name: $ + * locked from: $Locker: $ + * CVS State: $State: Exp $ + * Project: $ProjectName$ + */ +package de.intevation.gnv.geobackend.sde.datasources; + +import java.util.ArrayList; +import java.util.Calendar; +import java.util.Date; + +import org.apache.log4j.Logger; + +import com.esri.sde.sdk.client.SDEPoint; +import com.esri.sde.sdk.client.SeException; +import com.esri.sde.sdk.client.SeShape; + +import de.intevation.gnv.geobackend.sde.datasources.exception.TechnicalException; +import de.intevation.gnv.geobackend.util.DateUtils; + +/** + * A Row represents a set of values. + * In a technical manner (e.g. database manner) a row contains all attributes of a single "hit". + * + * @author blume + * @version 1.0 + * @serial 1.0 + * @see + * @since 21.11.2007 11:00:54 + */ +public class Row { + + /** + * Default Logging instance + */ + private static Logger sLogger = Logger.getLogger(Row.class); + + /** + * Data container. + */ + private Object[] mObjects; + + /** + * Constructor. + * + * @param pRowSize the number of attributes contained by this row. + */ + public Row(int pRowSize) { + mObjects = new Object[pRowSize]; + } + + /** + * Constructor. + * + * @param ArrayStr a line from CSV-File. + */ + public Row (String[] ArrayStr){ + this (ArrayStr.length); + int nLength = ArrayStr.length; + for (int i=0; i < nLength; i++){ + addObject(ArrayStr[i], i); + + } + + } + + + /** + * Adds an attribute value to a specific position of this row. + * + * @param pObject the object to be stored. + * @param pPos the postion the value to be saved + */ + public void addObject(Object pObject, int pPos) { + mObjects[pPos] = pObject; + } + + /** + * Returns a Value out of the Row. + * + * @param pPos the position of the value to be returned. + * @return an Object! (not strongly typed) + * @throws TechnicalException + */ + public Object getValue(int pPos) throws TechnicalException { + if (pPos < mObjects.length) { + + Object o = mObjects[pPos]; + if (o instanceof SeShape){ + return this.getPosValue(pPos); + }else{ + return o; + } + } else { + throw new TechnicalException("Cannot access this field position. Size is: " + mObjects.length); + } + } + + + /** + * This is a covenient method for getting strongly typed objects out of the row. + * It has to be ensured, that the type of the requested row position has been resolved out of the ColumnDefinition ({@link ResultSet#getColumnDefinitions()}). + * In fact, this method executes a simple cast to the desired type. + * + * @param pPos the position of the object to be resolved. + * @return a strongly typed Date + * @throws TechnicalException + * @see #getValue(int) + */ + public Date getDateValue(int pPos) throws TechnicalException { + Date date = null; + try { + Calendar lCalendar = (Calendar) getValue(pPos); + date = lCalendar.getTime(); + } + catch (ClassCastException e) { + try{ + //SimpleDateFormat formatter = new SimpleDateFormat("dd-MMM-yyyy HH:mm:ss"); + date = DateUtils.getDateFromString ((String)getValue(pPos)); //(Date)formatter.parse((String)getValue(pPos)); + + + } + catch (Exception ex){ + sLogger.error(getValue(pPos) + " " + ex.getMessage(), ex); + throw new TechnicalException("Could not cast this value to the Date Type. Object is of value type: " + getValue(pPos).getClass().getName()); + } + } + + return date; + } + + /** + * This is a covenient method for getting strongly typed objects out of the row. + * It has to be ensured, that the type of the requested row position has been resolved out of the ColumnDefinition ({@link ResultSet#getColumnDefinitions()}). + * In fact, this method executes a simple cast to the desired type. + * + * @param pPos the position of the object to be resolved. + * @return a strongly typed String + * @throws TechnicalException + * @see #getValue(int) + */ + public String getStringValue(int pPos) throws TechnicalException { + try { + Object o = this.getValue(pPos); + String returnValue = null; + if (o instanceof SeShape){ + returnValue = this.getPosValue(pPos); + }else{ + returnValue = (String)o; + } + return returnValue; + } catch (ClassCastException e) { + throw new TechnicalException("Could not cast this value to the String Type. Object is of value type: " + getValue(pPos).getClass().getName()); + } + } + + /** + * This is a covenient method for getting strongly typed objects out of the row. + * It has to be ensured, that the type of the requested row position has been resolved out of the ColumnDefinition ({@link ResultSet#getColumnDefinitions()}). + * In fact, this method executes a simple cast to the desired type. + * + * @param pPos the position of the object to be resolved. + * @throws TechnicalException + * @see #getValue(int) + * * @return a strongly typed int + */ + public int getIntValue(int pPos) throws TechnicalException { + try { + return (Integer) getValue(pPos); + } catch (ClassCastException e) { + throw new TechnicalException("Could not cast this value to the Integer Type. Object is of value type: " + getValue(pPos).getClass().getName()); + } + } + + /** + * This is a covenient method for getting strongly typed objects out of the row. + * It has to be ensured, that the type of the requested row position has been resolved out of the ColumnDefinition ({@link ResultSet#getColumnDefinitions()}). + * In fact, this method executes a simple cast to the desired type. + * + * @param pPos the position of the object to be resolved. + * @throws TechnicalException + * @see #getValue(int) + * * @return a strongly typed Double + */ + public Double getDoubleValue(int pPos) throws TechnicalException { + try { + return (Double) getValue(pPos); + } catch (ClassCastException e) { + try{ + return new Double ((String)getValue(pPos)); + } + catch(Exception ex){ + sLogger.error(getValue(pPos) + " " + ex.getMessage(), ex); + throw new TechnicalException("Could not cast this value to the Double Type. Object is of value type: " + getValue(pPos).getClass().getName()); + } + } + } + + /** + * This is a covenient method for getting strongly typed objects out of the row. + * It has to be ensured, that the type of the requested row position has been resolved out of the ColumnDefinition ({@link ResultSet#getColumnDefinitions()}). + * In fact, this method executes a simple cast to the desired type. + * + * @param pPos the position of the object to be resolved. + * @return a strongly typed Float + * @throws TechnicalException + * @see #getValue(int) + */ + public Float getFloatValue(int pPos) throws TechnicalException { + try { + return (Float) getValue(pPos); + } catch (ClassCastException e) { + throw new TechnicalException("Could not cast this value to the Float Type. Object is of value type: " + getValue(pPos).getClass().getName()); + } + } + /** + * This is a covenient method for getting strongly typed objects out of the row. + * It has to be ensured, that the type of the requested row position has been resolved out of the ColumnDefinition ({@link ResultSet#getColumnDefinitions()}). + * In fact, this method executes a simple cast to the desired type. + * + * @param pPos the position of the object to be resolved. + * @return a strongly typed Float + * @throws TechnicalException + * @see #getValue(int) + */ + public String getPosValue(int pPos)throws TechnicalException{ + SeShape val; + ArrayList aList; + SDEPoint mPoint[]; + StringBuffer returnValue = new StringBuffer(); + synchronized (returnValue) { + try { + val = (SeShape) this.mObjects[pPos]; + aList = val.getAllPoints(0,false); + mPoint = (SDEPoint[])aList.get(0); + if (val.isPoint()){ + returnValue.append("POINT(") + .append(mPoint[0].getX()) + .append(" ") + .append(mPoint[0].getY()); + if (mPoint[0].is3D()){ + returnValue.append(" ") + .append(mPoint[0].getZ()); + } + returnValue.append(")"); + }else if (val.isLine()){ + returnValue.append("LINESTRING("); + for (int i = 0; i< mPoint.length;i++){ + returnValue.append(mPoint[i].getX()) + .append(" ") + .append(mPoint[i].getY()); + if (mPoint[i].is3D()){ + returnValue.append(" ") + .append(mPoint[i].getZ()); + } + if (i < mPoint.length-1){ + returnValue.append(" , "); + } + } + returnValue.append(")"); + } else if (val.isPolygon()){ + returnValue.append("POLYGON(("); + int length = mPoint.length; + for (int i = 0; i< length ;i++){ + SDEPoint p = mPoint[i]; + returnValue.append(p.getX()) + .append(" ") + .append(p.getY()); + if (p.is3D()){ + returnValue.append(" ") + .append(p.getZ()); + } + if (i < length-1){ + returnValue.append(" , "); + } + } + returnValue.append(")"); + // TODO How are Rings supported +// for (int i =1 ; i < aList.size(); i++){ +// returnValue.append("("); +// mPoint = (SDEPoint[])aList.get(i); +// for (int j = 0; j < mPoint.length;j++){ +// returnValue.append(mPoint[j].getX()) +// .append(" ") +// .append(mPoint[j].getY()) +// .append(" ") +// .append(mPoint[i].getZ()); +// if (i < mPoint.length-1){ +// returnValue.append(" , "); +// } +// } +// returnValue.append(")"); +// } + returnValue.append(")"); + } + } catch (SeException e) { + throw new TechnicalException("Could not cast this value to the Float Type. Object is of value type: " + getValue(pPos).getClass().getName()); + } + } + return returnValue.toString(); + } +}
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/geo-backend/src/main/java/de/intevation/gnv/geobackend/sde/datasources/SDEQuery.java Fri Sep 28 12:13:50 2012 +0200 @@ -0,0 +1,325 @@ +/** + * Title: SdeQuery, $Header: /share/gdi/SDI-Suite/Repository/projekte/BSH-GDI/genericViewer/src/main/java/de/conterra/bsh/gdi/gnviewer/datasources/sde/SDEQuery.java,v 1.3 2008/01/30 12:38:34 blume Exp $ + * Source: $Source: /share/gdi/SDI-Suite/Repository/projekte/BSH-GDI/genericViewer/src/main/java/de/conterra/bsh/gdi/gnviewer/datasources/sde/SDEQuery.java,v $ + * created by: Stefan Blume (blume) + * erstellt am: 21.11.2007 + * Copyright: con terra GmbH, 2005 + * + * modified by: $Author: blume $ + * modified on: $Date: 2008/01/30 12:38:34 $ + * Version: $Revision: 1.3 $ + * TAG: $Name: $ + * locked from: $Locker: $ + * CVS State: $State: Exp $ + * Project: $ProjectName$ + */ +package de.intevation.gnv.geobackend.sde.datasources; + +import com.esri.sde.sdk.client.SeColumnDefinition; +import com.esri.sde.sdk.client.SeConnection; +import com.esri.sde.sdk.client.SeException; +import com.esri.sde.sdk.client.SeQuery; +import com.esri.sde.sdk.client.SeRow; +import com.esri.sde.sdk.client.SeShapeFilter; +import com.esri.sde.sdk.client.SeSqlConstruct; + +import de.intevation.gnv.geobackend.base.connectionpool.ConnectionPool; + +import de.intevation.gnv.geobackend.base.connectionpool.exception.ConnectionException; + +import de.intevation.gnv.geobackend.sde.datasources.exception.TechnicalException; + +import java.sql.Connection; +import java.sql.Statement; + +import org.apache.log4j.Logger; +/** + * // todo: supply a general interface for Query-Objects (Query = read access to datasource implementation) + * + * @author blume + * @version 1.0 + * @serial 1.0 + * @see + * @since 21.11.2007 11:32:19 + */ +public class SDEQuery { + + /** + * Default Logging instance + */ + private static final Logger sLogger = Logger.getLogger(SDEQuery.class); + private static boolean sDebug = sLogger.isDebugEnabled(); + + + private ConnectionPool connectionPool = null; + private String connectionID = "N/N"; + + public SDEQuery(ConnectionPool connectionPool) { + if (sDebug) sLogger.debug("SDEQuery()"); + this.connectionPool = connectionPool; + } + + /** + * Execute a query against a sde datasource. + * This Query method is responsible for getting and returning a valid {@link SeConnection}-Object. + * + * @param pTables + * @param pCols + * @param pWhere the where-clause to be executed. + * @return a ResultSet + * @see SeSqlConstruct + * @see SeQuery + */ + public ResultSet executeQuery(String pTables[], String pCols[], String pWhere) throws TechnicalException { + sLogger.debug("executeQuery()"); + Connection con = null; + try { + con = getConnection(); +// SeSqlConstruct lSeSqlConstruct = new SeSqlConstruct(pTables, pWhere); +// SeQuery lSeQuery = new SeQuery(con, pCols, lSeSqlConstruct); +// long lStart = System.currentTimeMillis(); +// lSeQuery.prepareQuery(); + ResultSet lSet = null;//handleResultSet(lSeQuery); +// long lEnd = System.currentTimeMillis(); +// if (sDebug) +// sLogger.debug((new StringBuilder()).append("lSeQuery lasts ").append((double) (lEnd - lStart) / 1000D).append(" seconds").toString()); + return lSet; + } catch (TechnicalException e) { + sLogger.error(e.getMessage(), e); + throw e; + } catch (Exception e) { + sLogger.error(e.getMessage(), e); + throw new TechnicalException("Error during executeQuery", e); + } finally { + returnConnection(con); + } + + } + + /** + * Execute a query against a sde datasource. + * This Query method is responsible for getting and returning a valid {@link SeConnection}-Object. + * + * @param pSQLStatement the SQL-Statement to be executed. + * @return a ResultSet + * @see SeQuery + */ + public ResultSet executeQuery(String pSQLStatement) throws TechnicalException { + if (sDebug) sLogger.debug("executeQuery():\n" + pSQLStatement); + Connection con = null; + try { + con = getConnection(); + + + long lStart = System.currentTimeMillis(); + + Statement stmt = con.createStatement(); + java.sql.ResultSet rs = stmt.executeQuery(pSQLStatement); + + long lEnd = System.currentTimeMillis(); + if (sDebug) + sLogger.debug((new StringBuilder()).append("lSeQuery lasts ").append((double) (lEnd - lStart) / 1000D).append(" seconds").toString()); + return (ResultSet)rs; + } catch (TechnicalException e) { + sLogger.error(e.getMessage(), e); + throw e; + } catch (Exception e) { + sLogger.error(e.getMessage(), e); + throw new TechnicalException("Error during executeQuery", e); + } finally { + returnConnection(con); + } + + + } + +// /** +// * Execute a query against a sde datasource. +// * This Query method is responsible for getting and returning a valid {@link SeConnection}-Object. +// * +// * @param pSQLStatement the SQL-Statement to be executed. +// * @return File +// * @see SeQuery +// */ +// public TempFile executeQueryDiagram (String pSQLStatement) throws TechnicalException { +// if (sDebug) sLogger.debug("executeQueryDiagram():\n" + pSQLStatement); +// SeConnection con = null; +// TempFile lImageFile = null; +// try { +// con = getConnection(); +// +// SeQuery lSeQuery = new SeQuery(con); +// long lStart = System.currentTimeMillis(); +// lSeQuery.prepareSql(pSQLStatement); +// lSeQuery.execute(); +// +// //ResultSet lSet = handleResultSet(lSeQuery); +// lImageFile = handleRowToCSVFile(lSeQuery); +// +// +// long lEnd = System.currentTimeMillis(); +// if (sDebug) +// sLogger.debug((new StringBuilder()).append("lSeQuery lasts ").append((double) (lEnd - lStart) / 1000D).append(" seconds").toString()); +// +// } catch (TechnicalException e) { +// sLogger.error(e.getMessage(), e); +// throw e; +// } catch (SeException e) { +// sLogger.error(e.getMessage(), e); +// throw new TechnicalException("Error during executeQuery", e); +// } finally { +// returnConnection(con); +// } +// return lImageFile; +// +// } + + /** + * Execute a spatial query against a sde datasource. + * This Query method is responsible for getting and returning a valid {@link SeConnection}-Object. + * // todo: there is work needed to fully implement the wanted functionality. + * + * @param pLayername + * @param pSpatialColumnName + * @param pPoints + * @param pReturnFields + * @return a ResultSet + * @see SeShapeFilter + * @see SeQuery + */ + public ResultSet executeQuery(String pLayername, String pSpatialColumnName, double[][] pPoints, String[] pReturnFields) throws TechnicalException { + sLogger.debug("executeQuery()"); + Connection con = null; + try { + con = getConnection(); + return null; + + } catch (TechnicalException e) { + sLogger.error(e.getMessage(), e); + throw e; + } catch (Exception e) { + sLogger.error(e.getMessage(), e); + throw new TechnicalException("Error accessing Spatial Database: " + e.getMessage(), e); + } finally { + returnConnection(con); + } + } + /** + * Execute a spatial query against a sde datasource. + * This Query method is responsible for getting and returning a valid {@link SeConnection}-Object. + * // todo: there is work needed to fully implement the wanted functionality. + * + * @param pLayername + * @param pSpatialColumnName + * @param pPoints + * @param pReturnFields + * @return a ResultSet + * @see SeShapeFilter + * @see SeQuery + */ + public ResultSet executeQuery(String pLayername, String pSpatialColumnName, String pWhere, double[][] pPoints, String[] pReturnFields) throws TechnicalException { + sLogger.debug("executeQuery()"); + Connection con = null; + try { + con = getConnection(); + return null; + + } catch (TechnicalException e) { + sLogger.error(e.getMessage(), e); + throw e; + } catch (Exception e) { + sLogger.error(e.getMessage(), e); + throw new TechnicalException("Error accessing Spatial Database: " + e.getMessage(), e); + } finally { + returnConnection(con); + } + } + private Connection getConnection() throws TechnicalException { + Connection connection = connectionPool.getConnection(connectionID); + return connection; + } + + private void returnConnection(Connection connection) { + try { + this.connectionPool.closeConnection(connection); + } catch (ConnectionException e) { + sLogger.error(e,e); + } + } + + + private ResultSet handleResultSet(SeQuery pSeQuery) throws SeException { + sLogger.debug("handleResultSet()"); + SDEResultSet lSet = new SDEResultSet(); + SeRow row; + int lCount; + for (lCount = 0; (row = pSeQuery.fetch()) != null; lCount++) { + // one time execution + if (lCount == 0) { + // analyze cols of result set + SeColumnDefinition[] lCols = row.getColumns(); + for (SeColumnDefinition lCol : lCols) { + lSet.addCol(new ColDefinition(lCol.getName(), lCol.getType()));// notice: esri-types have been copied into colDefinition class! + } + } + short lNumCols = row.getNumColumns(); + Row lBackingRow = new Row(lNumCols); + for (int i = 0; i < lNumCols; i++) { + lBackingRow.addObject(row.getObject(i), i); + } + lSet.addRow(lBackingRow); + } + pSeQuery.close(); + return lSet; + } + +// private TempFile handleRowToCSVFile(SeQuery pSeQuery) throws SeException { +// sLogger.debug("handleRowToTempFile()"); +// TempFile lImageFile = TemporaryFileDirectory.getInstance().createFile(".csv"); +// sLogger.debug("handleRowToTempFile() erstellen " + lImageFile.getFile().getName()); +// CSVWriter writer = null; +// ColDefinition[] lColDefinitions = null; +// +// SeRow row; +// int lCount; +// try { +// writer = new CSVWriter(new FileWriter(lImageFile.getFile()), ';'); +// for (lCount = 0; (row = pSeQuery.fetch()) != null; lCount++) { +// // one time execution +// if (lCount == 0) { +// lColDefinitions = new ColDefinition[row.getNumColumns()]; +// // analyze cols of result set +// SeColumnDefinition[] lCols = row.getColumns(); +// int j = 0; +// for (SeColumnDefinition lCol : lCols) { +// lColDefinitions[j] = new ColDefinition(lCol.getName(), lCol.getType()); +// j = j + 1; +// } +// } +// short lNumCols = row.getNumColumns(); +// Row lBackingRow = new Row(lNumCols); +// for (int i = 0; i < lNumCols; i++) { +// lBackingRow.addObject(row.getObject(i), i); +// } +// +// writer.writeRow(lBackingRow,lColDefinitions, false); +// } +// } catch (IOException e) { +// sLogger.error(e.getMessage(), e); +// } catch (TechnicalException e) { +// sLogger.error(e.getMessage(), e); +// } +// finally { +// pSeQuery.close(); +// try{ +// writer.close(); +// } +// catch (Exception e){ +// sLogger.error(e.getMessage(), e); +// } +// } +// +// return lImageFile; +// } + +}
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/geo-backend/src/main/java/de/intevation/gnv/geobackend/sde/datasources/SDEResultSet.java Fri Sep 28 12:13:50 2012 +0200 @@ -0,0 +1,1351 @@ +/** + * Title: SDEResultSet, $Header: /share/gdi/SDI-Suite/Repository/projekte/BSH-GDI/genericViewer/src/main/java/de/conterra/bsh/gdi/gnviewer/datasources/sde/SDEResultSet.java,v 1.2 2008/01/30 12:38:34 blume Exp $ + * Source: $Source: /share/gdi/SDI-Suite/Repository/projekte/BSH-GDI/genericViewer/src/main/java/de/conterra/bsh/gdi/gnviewer/datasources/sde/SDEResultSet.java,v $ + * created by: Stefan Blume (blume) + * erstellt am: 21.11.2007 + * Copyright: con terra GmbH, 2005 + * + * modified by: $Author: blume $ + * modified on: $Date: 2008/01/30 12:38:34 $ + * Version: $Revision: 1.2 $ + * TAG: $Name: $ + * locked from: $Locker: $ + * CVS State: $State: Exp $ + * Project: $ProjectName$ + */ +package de.intevation.gnv.geobackend.sde.datasources; + +import java.io.InputStream; +import java.io.Reader; +import java.math.BigDecimal; +import java.net.URL; +import java.sql.Array; +import java.sql.Blob; +import java.sql.Clob; +import java.sql.Date; +import java.sql.NClob; +import java.sql.Ref; +import java.sql.ResultSetMetaData; +import java.sql.RowId; +import java.sql.SQLException; +import java.sql.SQLWarning; +import java.sql.SQLXML; +import java.sql.Statement; +import java.sql.Time; +import java.sql.Timestamp; +import java.util.ArrayList; +import java.util.Calendar; +import java.util.Collections; +import java.util.List; +import java.util.Map; + +import org.apache.log4j.Logger; + +import de.intevation.gnv.geobackend.sde.datasources.exception.TechnicalException; + +/** + * @author blume + * @version 1.0 + * @serial 1.0 + * @see + * @since 21.11.2007 11:01:27 + */ +public class SDEResultSet extends ResultSet { + + /** + * Default Logging instance + */ + private static Logger log = Logger.getLogger(SDEResultSet.class); + + private List<Row> mRows = Collections.synchronizedList(new ArrayList<Row>()); + private List<ColDefinition> mCols = Collections.synchronizedList(new ArrayList<ColDefinition>()); + + private Row currentRow = null; + private int cursor = 0; + + public SDEResultSet() { + } + + public int getCount() { + return mRows.size(); + } + + public int getNumberOfColumns() { + return mCols.size(); + } + + public ColDefinition[] getColumnDefinitions() { + ColDefinition[] lColDefinitions = new ColDefinition[mCols.size()]; + return mCols.toArray(lColDefinitions); + } + + public Row[] getResults() { + Row[] lRows = new Row[mRows.size()]; + return mRows.toArray(lRows); + } + + public void addRow(Row pRow) { + mRows.add(pRow); + } + + public void addCol(ColDefinition pColDefinition) { + mCols.add(pColDefinition); + } + + /** + * @see java.sql.ResultSet#absolute(int) + */ + public boolean absolute(int row) throws SQLException { + + return false; + } + + /** + * @see java.sql.ResultSet#afterLast() + */ + public void afterLast() throws SQLException { + } + + /** + * @see java.sql.ResultSet#beforeFirst() + */ + public void beforeFirst() throws SQLException { + } + + /** + * @see java.sql.ResultSet#cancelRowUpdates() + */ + public void cancelRowUpdates() throws SQLException { + } + + /** + * @see java.sql.ResultSet#clearWarnings() + */ + public void clearWarnings() throws SQLException { + } + + /** + * @see java.sql.ResultSet#close() + */ + public void close() throws SQLException { + } + + /** + * @see java.sql.ResultSet#deleteRow() + */ + public void deleteRow() throws SQLException { + } + + /** + * @see java.sql.ResultSet#findColumn(java.lang.String) + */ + public int findColumn(String columnName) throws SQLException { + + return 0; + } + + /** + * @see java.sql.ResultSet#first() + */ + public boolean first() throws SQLException { + + return false; + } + + /** + * @see java.sql.ResultSet#getArray(int) + */ + public Array getArray(int i) throws SQLException { + + return null; + } + + /** + * @see java.sql.ResultSet#getArray(java.lang.String) + */ + public Array getArray(String colName) throws SQLException { + + return null; + } + + /** + * @see java.sql.ResultSet#getAsciiStream(int) + */ + public InputStream getAsciiStream(int columnIndex) throws SQLException { + + return null; + } + + /** + * @see java.sql.ResultSet#getAsciiStream(java.lang.String) + */ + public InputStream getAsciiStream(String columnName) throws SQLException { + + return null; + } + + /** + * @see java.sql.ResultSet#getBigDecimal(int) + */ + public BigDecimal getBigDecimal(int columnIndex) throws SQLException { + + return null; + } + + /** + * @see java.sql.ResultSet#getBigDecimal(java.lang.String) + */ + public BigDecimal getBigDecimal(String columnName) throws SQLException { + + return null; + } + + /** + * @see java.sql.ResultSet#getBigDecimal(int, int) + */ + public BigDecimal getBigDecimal(int columnIndex, int scale) + throws SQLException { + + return null; + } + + /** + * @see java.sql.ResultSet#getBigDecimal(java.lang.String, int) + */ + public BigDecimal getBigDecimal(String columnName, int scale) + throws SQLException { + + return null; + } + + /** + * @see java.sql.ResultSet#getBinaryStream(int) + */ + public InputStream getBinaryStream(int columnIndex) throws SQLException { + + return null; + } + + /** + * @see java.sql.ResultSet#getBinaryStream(java.lang.String) + */ + public InputStream getBinaryStream(String columnName) throws SQLException { + + return null; + } + + /** + * @see java.sql.ResultSet#getBlob(int) + */ + public Blob getBlob(int i) throws SQLException { + + return null; + } + + /** + * @see java.sql.ResultSet#getBlob(java.lang.String) + */ + public Blob getBlob(String colName) throws SQLException { + + return null; + } + + /** + * @see java.sql.ResultSet#getBoolean(int) + */ + public boolean getBoolean(int columnIndex) throws SQLException { + + return false; + } + + /** + * @see java.sql.ResultSet#getBoolean(java.lang.String) + */ + public boolean getBoolean(String columnName) throws SQLException { + + return false; + } + + /** + * @see java.sql.ResultSet#getByte(int) + */ + public byte getByte(int columnIndex) throws SQLException { + + return 0; + } + + /** + * @see java.sql.ResultSet#getByte(java.lang.String) + */ + public byte getByte(String columnName) throws SQLException { + + return 0; + } + + /** + * @see java.sql.ResultSet#getBytes(int) + */ + public byte[] getBytes(int columnIndex) throws SQLException { + + return null; + } + + /** + * @see java.sql.ResultSet#getBytes(java.lang.String) + */ + public byte[] getBytes(String columnName) throws SQLException { + + return null; + } + + /** + * @see java.sql.ResultSet#getCharacterStream(int) + */ + public Reader getCharacterStream(int columnIndex) throws SQLException { + + return null; + } + + /** + * @see java.sql.ResultSet#getCharacterStream(java.lang.String) + */ + public Reader getCharacterStream(String columnName) throws SQLException { + + return null; + } + + /** + * @see java.sql.ResultSet#getClob(int) + */ + public Clob getClob(int i) throws SQLException { + + return null; + } + + /** + * @see java.sql.ResultSet#getClob(java.lang.String) + */ + public Clob getClob(String colName) throws SQLException { + + return null; + } + + /** + * @see java.sql.ResultSet#getConcurrency() + */ + public int getConcurrency() throws SQLException { + + return 0; + } + + /** + * @see java.sql.ResultSet#getCursorName() + */ + public String getCursorName() throws SQLException { + + return null; + } + + /** + * @see java.sql.ResultSet#getDate(int) + */ + public Date getDate(int columnIndex) throws SQLException { + try { + return new java.sql.Date(this.currentRow.getDateValue(columnIndex-1).getTime()); + } catch (TechnicalException e) { + log.error(e,e); + throw new SQLException(e.getMessage()); + } + } + + /** + * @see java.sql.ResultSet#getDate(java.lang.String) + */ + public Date getDate(String columnName) throws SQLException { + int columnIndex = this.getColumnIndex(columnName); + return this.getDate(columnIndex); + } + + /** + * @see java.sql.ResultSet#getDate(int, java.util.Calendar) + */ + public Date getDate(int columnIndex, Calendar cal) throws SQLException { + + return null; + } + + /** + * @see java.sql.ResultSet#getDate(java.lang.String, java.util.Calendar) + */ + public Date getDate(String columnName, Calendar cal) throws SQLException { + + return null; + } + + /** + * @see java.sql.ResultSet#getDouble(int) + */ + public double getDouble(int columnIndex) throws SQLException { + try { + return this.currentRow.getDoubleValue(columnIndex-1); + } catch (TechnicalException e) { + log.error(e,e); + throw new SQLException(e.getMessage()); + } + } + + /** + * @see java.sql.ResultSet#getDouble(java.lang.String) + */ + public double getDouble(String columnName) throws SQLException { + int columnIndex = this.getColumnIndex(columnName); + return this.getDouble(columnIndex); + } + + /** + * @see java.sql.ResultSet#getFetchDirection() + */ + public int getFetchDirection() throws SQLException { + + return 0; + } + + /** + * @see java.sql.ResultSet#getFetchSize() + */ + public int getFetchSize() throws SQLException { + + return 0; + } + + /** + * @see java.sql.ResultSet#getFloat(int) + */ + public float getFloat(int columnIndex) throws SQLException { + try { + return this.currentRow.getFloatValue(columnIndex-1); + } catch (TechnicalException e) { + log.error(e,e); + throw new SQLException(e.getMessage()); + } + } + + /** + * @see java.sql.ResultSet#getFloat(java.lang.String) + */ + public float getFloat(String columnName) throws SQLException { + int columnIndex = this.getColumnIndex(columnName); + return this.getFloat(columnIndex); + } + + /** + * @see java.sql.ResultSet#getInt(int) + */ + public int getInt(int columnIndex) throws SQLException { + + try { + return this.currentRow.getIntValue(columnIndex-1); + } catch (TechnicalException e) { + log.error(e,e); + throw new SQLException(e.getMessage()); + } + } + + /** + * @see java.sql.ResultSet#getInt(java.lang.String) + */ + public int getInt(String columnName) throws SQLException { + int columnIndex = this.getColumnIndex(columnName); + return this.getInt(columnIndex); + } + + /** + * @see java.sql.ResultSet#getLong(int) + */ + public long getLong(int columnIndex) throws SQLException { + + return 0; + } + + /** + * @see java.sql.ResultSet#getLong(java.lang.String) + */ + public long getLong(String columnName) throws SQLException { + + return 0; + } + + /** + * @see java.sql.ResultSet#getMetaData() + */ + public ResultSetMetaData getMetaData() throws SQLException { + log.debug("SDEREsultSet.getMetaData"); + return new ArcSDEResultSetMetaData(this.mCols); + } + + /** + * @see java.sql.ResultSet#getObject(int) + */ + public Object getObject(int columnIndex) throws SQLException { + try { + return this.currentRow.getValue(columnIndex-1); + } catch (TechnicalException e) { + log.error(e,e); + throw new SQLException(e.getMessage()); + } + } + + /** + * @see java.sql.ResultSet#getObject(java.lang.String) + */ + public Object getObject(String columnName) throws SQLException { + int columnIndex = this.getColumnIndex(columnName); + return this.getObject(columnIndex); + } + + /** + * @see java.sql.ResultSet#getObject(int, java.util.Map) + */ + public Object getObject(int i, Map<String, Class<?>> map) + throws SQLException { + + return null; + } + + /** + * @see java.sql.ResultSet#getObject(java.lang.String, java.util.Map) + */ + public Object getObject(String colName, Map<String, Class<?>> map) + throws SQLException { + + return null; + } + + /** + * @see java.sql.ResultSet#getRef(int) + */ + public Ref getRef(int i) throws SQLException { + + return null; + } + + /** + * @see java.sql.ResultSet#getRef(java.lang.String) + */ + public Ref getRef(String colName) throws SQLException { + + return null; + } + + /** + * @see java.sql.ResultSet#getRow() + */ + public int getRow() throws SQLException { + + return 0; + } + + /** + * @see java.sql.ResultSet#getShort(int) + */ + public short getShort(int columnIndex) throws SQLException { + + return 0; + } + + /** + * @see java.sql.ResultSet#getShort(java.lang.String) + */ + public short getShort(String columnName) throws SQLException { + + return 0; + } + + /** + * @see java.sql.ResultSet#getStatement() + */ + public Statement getStatement() throws SQLException { + + return null; + } + + /** + * @see java.sql.ResultSet#getString(int) + */ + public String getString(int columnIndex) throws SQLException { + + try { + return this.currentRow.getStringValue(columnIndex-1); + } catch (TechnicalException e) { + log.error(e,e); + throw new SQLException(e.getMessage()); + } + } + + /** + * @see java.sql.ResultSet#getString(java.lang.String) + */ + public String getString(String columnName) throws SQLException { + int columnIndex = this.getColumnIndex(columnName); + return this.getString(columnIndex); + } + + /** + * @see java.sql.ResultSet#getTime(int) + */ + public Time getTime(int columnIndex) throws SQLException { + + return null; + } + + /** + * @see java.sql.ResultSet#getTime(java.lang.String) + */ + public Time getTime(String columnName) throws SQLException { + + return null; + } + + /** + * @see java.sql.ResultSet#getTime(int, java.util.Calendar) + */ + public Time getTime(int columnIndex, Calendar cal) throws SQLException { + + return null; + } + + /** + * @see java.sql.ResultSet#getTime(java.lang.String, java.util.Calendar) + */ + public Time getTime(String columnName, Calendar cal) throws SQLException { + + return null; + } + + /** + * @see java.sql.ResultSet#getTimestamp(int) + */ + public Timestamp getTimestamp(int columnIndex) throws SQLException { + + return null; + } + + /** + * @see java.sql.ResultSet#getTimestamp(java.lang.String) + */ + public Timestamp getTimestamp(String columnName) throws SQLException { + + return null; + } + + /** + * @see java.sql.ResultSet#getTimestamp(int, java.util.Calendar) + */ + public Timestamp getTimestamp(int columnIndex, Calendar cal) + throws SQLException { + + return null; + } + + /** + * @see java.sql.ResultSet#getTimestamp(java.lang.String, + * java.util.Calendar) + */ + public Timestamp getTimestamp(String columnName, Calendar cal) + throws SQLException { + + return null; + } + + /** + * @see java.sql.ResultSet#getType() + */ + public int getType() throws SQLException { + + return 0; + } + + /** + * @see java.sql.ResultSet#getURL(int) + */ + public URL getURL(int columnIndex) throws SQLException { + + return null; + } + + /** + * @see java.sql.ResultSet#getURL(java.lang.String) + */ + public URL getURL(String columnName) throws SQLException { + + return null; + } + + /** + * @see java.sql.ResultSet#getUnicodeStream(int) + */ + public InputStream getUnicodeStream(int columnIndex) throws SQLException { + + return null; + } + + /** + * @see java.sql.ResultSet#getUnicodeStream(java.lang.String) + */ + public InputStream getUnicodeStream(String columnName) throws SQLException { + + return null; + } + + /** + * @see java.sql.ResultSet#getWarnings() + */ + public SQLWarning getWarnings() throws SQLException { + + return null; + } + + /** + * @see java.sql.ResultSet#insertRow() + */ + public void insertRow() throws SQLException { + } + + /** + * @see java.sql.ResultSet#isAfterLast() + */ + public boolean isAfterLast() throws SQLException { + + return false; + } + + /** + * @see java.sql.ResultSet#isBeforeFirst() + */ + public boolean isBeforeFirst() throws SQLException { + + return false; + } + + /** + * @see java.sql.ResultSet#isFirst() + */ + public boolean isFirst() throws SQLException { + + return false; + } + + /** + * @see java.sql.ResultSet#isLast() + */ + public boolean isLast() throws SQLException { + + return false; + } + + /** + * @see java.sql.ResultSet#last() + */ + public boolean last() throws SQLException { + + return false; + } + + /** + * @see java.sql.ResultSet#moveToCurrentRow() + */ + public void moveToCurrentRow() throws SQLException { + } + + /** + * @see java.sql.ResultSet#moveToInsertRow() + */ + public void moveToInsertRow() throws SQLException { + } + + /** + * @see java.sql.ResultSet#next() + */ + public boolean next() throws SQLException { + + boolean next = this.mRows.size() > this.cursor; + if (next){ + //log.debug("Zeile "+(cursor+1)+" von "+this.mRows.size()+" wird angesteuert."); + this.currentRow = this.mRows.get(this.cursor); + this.cursor++; + }else{ + this.currentRow = null; + } + return next; + } + + /** + * @see java.sql.ResultSet#previous() + */ + public boolean previous() throws SQLException { + + return false; + } + + /** + * @see java.sql.ResultSet#refreshRow() + */ + public void refreshRow() throws SQLException { + } + + /** + * @see java.sql.ResultSet#relative(int) + */ + public boolean relative(int rows) throws SQLException { + + return false; + } + + /** + * @see java.sql.ResultSet#rowDeleted() + */ + public boolean rowDeleted() throws SQLException { + + return false; + } + + /** + * @see java.sql.ResultSet#rowInserted() + */ + public boolean rowInserted() throws SQLException { + + return false; + } + + /** + * @see java.sql.ResultSet#rowUpdated() + */ + public boolean rowUpdated() throws SQLException { + + return false; + } + + /** + * @see java.sql.ResultSet#setFetchDirection(int) + */ + public void setFetchDirection(int direction) throws SQLException { + } + + /** + * @see java.sql.ResultSet#setFetchSize(int) + */ + public void setFetchSize(int rows) throws SQLException { + } + + /** + * @see java.sql.ResultSet#updateArray(int, java.sql.Array) + */ + public void updateArray(int columnIndex, Array x) throws SQLException { + } + + /** + * @see java.sql.ResultSet#updateArray(java.lang.String, java.sql.Array) + */ + public void updateArray(String columnName, Array x) throws SQLException { + } + + /** + * @see java.sql.ResultSet#updateAsciiStream(int, java.io.InputStream, int) + */ + public void updateAsciiStream(int columnIndex, InputStream x, int length) + throws SQLException { + } + + /** + * @see java.sql.ResultSet#updateAsciiStream(java.lang.String, + * java.io.InputStream, int) + */ + public void updateAsciiStream(String columnName, InputStream x, int length) + throws SQLException { + } + + /** + * @see java.sql.ResultSet#updateBigDecimal(int, java.math.BigDecimal) + */ + public void updateBigDecimal(int columnIndex, BigDecimal x) + throws SQLException { + } + + /** + * @see java.sql.ResultSet#updateBigDecimal(java.lang.String, + * java.math.BigDecimal) + */ + public void updateBigDecimal(String columnName, BigDecimal x) + throws SQLException { + } + + /** + * @see java.sql.ResultSet#updateBinaryStream(int, java.io.InputStream, int) + */ + public void updateBinaryStream(int columnIndex, InputStream x, int length) + throws SQLException { + } + + /** + * @see java.sql.ResultSet#updateBinaryStream(java.lang.String, + * java.io.InputStream, int) + */ + public void updateBinaryStream(String columnName, InputStream x, int length) + throws SQLException { + } + + /** + * @see java.sql.ResultSet#updateBlob(int, java.sql.Blob) + */ + public void updateBlob(int columnIndex, Blob x) throws SQLException { + } + + /** + * @see java.sql.ResultSet#updateBlob(java.lang.String, java.sql.Blob) + */ + public void updateBlob(String columnName, Blob x) throws SQLException { + } + + /** + * @see java.sql.ResultSet#updateBoolean(int, boolean) + */ + public void updateBoolean(int columnIndex, boolean x) throws SQLException { + } + + /** + * @see java.sql.ResultSet#updateBoolean(java.lang.String, boolean) + */ + public void updateBoolean(String columnName, boolean x) throws SQLException { + } + + /** + * @see java.sql.ResultSet#updateByte(int, byte) + */ + public void updateByte(int columnIndex, byte x) throws SQLException { + } + + /** + * @see java.sql.ResultSet#updateByte(java.lang.String, byte) + */ + public void updateByte(String columnName, byte x) throws SQLException { + } + + /** + * @see java.sql.ResultSet#updateBytes(int, byte[]) + */ + public void updateBytes(int columnIndex, byte[] x) throws SQLException { + } + + /** + * @see java.sql.ResultSet#updateBytes(java.lang.String, byte[]) + */ + public void updateBytes(String columnName, byte[] x) throws SQLException { + } + + /** + * @see java.sql.ResultSet#updateCharacterStream(int, java.io.Reader, int) + */ + public void updateCharacterStream(int columnIndex, Reader x, int length) + throws SQLException { + } + + /** + * @see java.sql.ResultSet#updateCharacterStream(java.lang.String, + * java.io.Reader, int) + */ + public void updateCharacterStream(String columnName, Reader reader, + int length) throws SQLException { + } + + /** + * @see java.sql.ResultSet#updateClob(int, java.sql.Clob) + */ + public void updateClob(int columnIndex, Clob x) throws SQLException { + } + + /** + * @see java.sql.ResultSet#updateClob(java.lang.String, java.sql.Clob) + */ + public void updateClob(String columnName, Clob x) throws SQLException { + } + + /** + * @see java.sql.ResultSet#updateDate(int, java.sql.Date) + */ + public void updateDate(int columnIndex, Date x) throws SQLException { + } + + /** + * @see java.sql.ResultSet#updateDate(java.lang.String, java.sql.Date) + */ + public void updateDate(String columnName, Date x) throws SQLException { + } + + /** + * @see java.sql.ResultSet#updateDouble(int, double) + */ + public void updateDouble(int columnIndex, double x) throws SQLException { + } + + /** + * @see java.sql.ResultSet#updateDouble(java.lang.String, double) + */ + public void updateDouble(String columnName, double x) throws SQLException { + } + + /** + * @see java.sql.ResultSet#updateFloat(int, float) + */ + public void updateFloat(int columnIndex, float x) throws SQLException { + } + + /** + * @see java.sql.ResultSet#updateFloat(java.lang.String, float) + */ + public void updateFloat(String columnName, float x) throws SQLException { + } + + /** + * @see java.sql.ResultSet#updateInt(int, int) + */ + public void updateInt(int columnIndex, int x) throws SQLException { + } + + /** + * @see java.sql.ResultSet#updateInt(java.lang.String, int) + */ + public void updateInt(String columnName, int x) throws SQLException { + } + + /** + * @see java.sql.ResultSet#updateLong(int, long) + */ + public void updateLong(int columnIndex, long x) throws SQLException { + } + + /** + * @see java.sql.ResultSet#updateLong(java.lang.String, long) + */ + public void updateLong(String columnName, long x) throws SQLException { + } + + /** + * @see java.sql.ResultSet#updateNull(int) + */ + public void updateNull(int columnIndex) throws SQLException { + } + + /** + * @see java.sql.ResultSet#updateNull(java.lang.String) + */ + public void updateNull(String columnName) throws SQLException { + } + + /** + * @see java.sql.ResultSet#updateObject(int, java.lang.Object) + */ + public void updateObject(int columnIndex, Object x) throws SQLException { + } + + /** + * @see java.sql.ResultSet#updateObject(java.lang.String, java.lang.Object) + */ + public void updateObject(String columnName, Object x) throws SQLException { + } + + /** + * @see java.sql.ResultSet#updateObject(int, java.lang.Object, int) + */ + public void updateObject(int columnIndex, Object x, int scale) + throws SQLException { + } + + /** + * @see java.sql.ResultSet#updateObject(java.lang.String, java.lang.Object, + * int) + */ + public void updateObject(String columnName, Object x, int scale) + throws SQLException { + } + + /** + * @see java.sql.ResultSet#updateRef(int, java.sql.Ref) + */ + public void updateRef(int columnIndex, Ref x) throws SQLException { + } + + /** + * @see java.sql.ResultSet#updateRef(java.lang.String, java.sql.Ref) + */ + public void updateRef(String columnName, Ref x) throws SQLException { + } + + /** + * @see java.sql.ResultSet#updateRow() + */ + public void updateRow() throws SQLException { + } + + /** + * @see java.sql.ResultSet#updateShort(int, short) + */ + public void updateShort(int columnIndex, short x) throws SQLException { + } + + /** + * @see java.sql.ResultSet#updateShort(java.lang.String, short) + */ + public void updateShort(String columnName, short x) throws SQLException { + } + + /** + * @see java.sql.ResultSet#updateString(int, java.lang.String) + */ + public void updateString(int columnIndex, String x) throws SQLException { + } + + /** + * @see java.sql.ResultSet#updateString(java.lang.String, java.lang.String) + */ + public void updateString(String columnName, String x) throws SQLException { + } + + /** + * @see java.sql.ResultSet#updateTime(int, java.sql.Time) + */ + public void updateTime(int columnIndex, Time x) throws SQLException { + } + + /** + * @see java.sql.ResultSet#updateTime(java.lang.String, java.sql.Time) + */ + public void updateTime(String columnName, Time x) throws SQLException { + } + + /** + * @see java.sql.ResultSet#updateTimestamp(int, java.sql.Timestamp) + */ + public void updateTimestamp(int columnIndex, Timestamp x) + throws SQLException { + } + + /** + * @see java.sql.ResultSet#updateTimestamp(java.lang.String, + * java.sql.Timestamp) + */ + public void updateTimestamp(String columnName, Timestamp x) + throws SQLException { + } + + /** + * @see java.sql.ResultSet#wasNull() + */ + public boolean wasNull() throws SQLException { + + return false; + } + + + private int getColumnIndex(String columnName) throws SQLException{ + for (int i = 0 ; i < this.mCols.size();i++){ + if(this.mCols.get(i).getName().equalsIgnoreCase(columnName)){ + return i +1; // PLUS 1 da SQL-Cursor 1 basiert sind + } + } + throw new SQLException("Column "+columnName+" does not exist in ResulSet"); + } + + public int getHoldability() throws SQLException { + return 0; + } + + public Reader getNCharacterStream(int arg0) throws SQLException { + return null; + } + + public Reader getNCharacterStream(String arg0) throws SQLException { + return null; + } + + public NClob getNClob(int arg0) throws SQLException { + return null; + } + + public NClob getNClob(String arg0) throws SQLException { + return null; + } + + public String getNString(int arg0) throws SQLException { + return null; + } + + public String getNString(String arg0) throws SQLException { + return null; + } + + public RowId getRowId(int arg0) throws SQLException { + + return null; + } + + public RowId getRowId(String arg0) throws SQLException { + + return null; + } + + public SQLXML getSQLXML(int arg0) throws SQLException { + + return null; + } + + public SQLXML getSQLXML(String arg0) throws SQLException { + + return null; + } + + public boolean isClosed() throws SQLException { + + return false; + } + + public void updateAsciiStream(int arg0, InputStream arg1) + throws SQLException { + } + + public void updateAsciiStream(String arg0, InputStream arg1) + throws SQLException { + } + + public void updateAsciiStream(int arg0, InputStream arg1, long arg2) + throws SQLException { + } + + public void updateAsciiStream(String arg0, InputStream arg1, long arg2) + throws SQLException { + } + + public void updateBinaryStream(int arg0, InputStream arg1) + throws SQLException { + } + + public void updateBinaryStream(String arg0, InputStream arg1) + throws SQLException { + } + + public void updateBinaryStream(int arg0, InputStream arg1, long arg2) + throws SQLException { + } + + public void updateBinaryStream(String arg0, InputStream arg1, long arg2) + throws SQLException { + } + + public void updateBlob(int arg0, InputStream arg1) throws SQLException { + } + + public void updateBlob(String arg0, InputStream arg1) throws SQLException { + } + + public void updateBlob(int arg0, InputStream arg1, long arg2) + throws SQLException { + } + + public void updateBlob(String arg0, InputStream arg1, long arg2) + throws SQLException { + } + + public void updateCharacterStream(int arg0, Reader arg1) + throws SQLException { + } + + public void updateCharacterStream(String arg0, Reader arg1) + throws SQLException { + } + + public void updateCharacterStream(int arg0, Reader arg1, long arg2) + throws SQLException { + } + + public void updateCharacterStream(String arg0, Reader arg1, long arg2) + throws SQLException { + } + + public void updateClob(int arg0, Reader arg1) throws SQLException { + } + + public void updateClob(String arg0, Reader arg1) throws SQLException { + } + + public void updateClob(int arg0, Reader arg1, long arg2) + throws SQLException { + } + + public void updateClob(String arg0, Reader arg1, long arg2) + throws SQLException { + } + + public void updateNCharacterStream(int arg0, Reader arg1) + throws SQLException { + } + + public void updateNCharacterStream(String arg0, Reader arg1) + throws SQLException { + } + + public void updateNCharacterStream(int arg0, Reader arg1, long arg2) + throws SQLException { + } + + public void updateNCharacterStream(String arg0, Reader arg1, long arg2) + throws SQLException { + } + + public void updateNClob(int arg0, NClob arg1) throws SQLException { + } + + public void updateNClob(String arg0, NClob arg1) throws SQLException { + } + + public void updateNClob(int arg0, Reader arg1) throws SQLException { + } + + public void updateNClob(String arg0, Reader arg1) throws SQLException { + } + + public void updateNClob(int arg0, Reader arg1, long arg2) + throws SQLException { + } + + public void updateNClob(String arg0, Reader arg1, long arg2) + throws SQLException { + } + + public void updateNString(int arg0, String arg1) throws SQLException { + } + + public void updateNString(String arg0, String arg1) throws SQLException { + } + + public void updateRowId(int arg0, RowId arg1) throws SQLException { + } + + public void updateRowId(String arg0, RowId arg1) throws SQLException { + } + + public void updateSQLXML(int arg0, SQLXML arg1) throws SQLException { + } + + public void updateSQLXML(String arg0, SQLXML arg1) throws SQLException { + } + + public boolean isWrapperFor(Class<?> arg0) throws SQLException { + + return false; + } + + public <T> T unwrap(Class<T> arg0) throws SQLException { + + return null; + } + +}
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/geo-backend/src/main/java/de/intevation/gnv/geobackend/sde/datasources/exception/DatasourceException.java Fri Sep 28 12:13:50 2012 +0200 @@ -0,0 +1,37 @@ +/* Copyright (C) 2007 con terra GmbH (http://www.conterra.de) + * All rights reserved + * + * $Id: DatasourceException.java,v 1.2 2008/01/30 12:38:34 blume Exp $ + * + * created by: drewnak + * created at : 21.11.2007 + * created at : 14:56:32 + * + * modified by: $Author: blume $ + * modified at: $Date: 2008/01/30 12:38:34 $ + */ +package de.intevation.gnv.geobackend.sde.datasources.exception; + +/** + * A Class for encapsulating implementation specific datasource exceptions. + * This exception class can be used for all exceptions occuring in connection to "datasources actions". + */ +public class DatasourceException extends Exception { + + public DatasourceException() { + super(); + } + + public DatasourceException(String pMessage) { + super(pMessage); + } + + public DatasourceException(String pMessage, Throwable pCause) { + super(pMessage, pCause); + } + + public DatasourceException(Throwable pCause) { + super(pCause); + } + +} \ No newline at end of file
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/geo-backend/src/main/java/de/intevation/gnv/geobackend/sde/datasources/exception/TechnicalException.java Fri Sep 28 12:13:50 2012 +0200 @@ -0,0 +1,79 @@ +/** + * Title: TechnicalException, $Header: /share/gdi/SDI-Suite/Repository/projekte/BSH-GDI/genericViewer/src/main/java/de/conterra/bsh/gdi/gnviewer/exception/TechnicalException.java,v 1.2 2008/08/18 14:50:33 drewnak Exp $ + * Source: $Source: /share/gdi/SDI-Suite/Repository/projekte/BSH-GDI/genericViewer/src/main/java/de/conterra/bsh/gdi/gnviewer/exception/TechnicalException.java,v $ + * created by: Stefan Blume (blume) + * erstellt am: 21.11.2007 + * Copyright: con terra GmbH, 2005 + * + * modified by: $Author: drewnak $ + * modified on: $Date: 2008/08/18 14:50:33 $ + * Version: $Revision: 1.2 $ + * TAG: $Name: $ + * locked from: $Locker: $ + * CVS State: $State: Exp $ + * Project: $ProjectName$ + */ +package de.intevation.gnv.geobackend.sde.datasources.exception; + +import org.apache.log4j.Logger; + +/** + * The class <code>TechnicalException</code> fulfills the following purposes: + * <ol> + * <li></li> + * </ol> + * + * @author blume + * @version 1.0 + * @serial 1.0 + * @see + * @since 21.11.2007 08:12:08 + */ +public class TechnicalException extends Exception{ + + /** + * Default Logging instance + */ + private static Logger sLogger = Logger.getLogger(TechnicalException.class); + + /** + * Constructs a new exception with <code>null</code> as its detail message. + * The cause is not initialized, and may subsequently be initialized by a + * call to {@link #initCause}. + */ + public TechnicalException() { + } + + /** + * Constructs a new exception with the specified detail message. The + * cause is not initialized, and may subsequently be initialized by + * a call to {@link #initCause}. + * + * @param message the detail message. The detail message is saved for + * later retrieval by the {@link #getMessage()} method. + */ + public TechnicalException(String message) { + super(message); + } + public TechnicalException(Throwable pCause) { + super(pCause); + } + + /** + * Constructs a new exception with the specified detail message and + * cause. <p>Note that the detail message associated with + * <code>cause</code> is <i>not</i> automatically incorporated in + * this exception's detail message. + * + * @param message the detail message (which is saved for later retrieval + * by the {@link #getMessage()} method). + * @param cause the cause (which is saved for later retrieval by the + * {@link #getCause()} method). (A <tt>null</tt> value is + * permitted, and indicates that the cause is nonexistent or + * unknown.) + * @since 1.4 + */ + public TechnicalException(String message, Throwable cause) { + super(message, cause); + } +}
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/geo-backend/src/main/java/de/intevation/gnv/geobackend/util/DateUtils.java Fri Sep 28 12:13:50 2012 +0200 @@ -0,0 +1,128 @@ +/** + * Title: DateUtisl, $Header: /share/gdi/SDI-Suite/Repository/projekte/BSH-GDI/genericViewer/src/main/java/de/conterra/bsh/gdi/gnviewer/util/DateUtils.java,v 1.2 2008/08/18 14:50:33 drewnak Exp $ + * Source: $Source: /share/gdi/SDI-Suite/Repository/projekte/BSH-GDI/genericViewer/src/main/java/de/conterra/bsh/gdi/gnviewer/util/DateUtils.java,v $ + * created by: Stefan Blume (blume) + * erstellt am: 21.12.2007 + * Copyright: con terra GmbH, 2005 + * + * modified by: $Author: drewnak $ + * modified on: $Date: 2008/08/18 14:50:33 $ + * Version: $Revision: 1.2 $ + * TAG: $Name: $ + * locked from: $Locker: $ + * CVS State: $State: Exp $ + * Project: $ProjectName$ + */ +package de.intevation.gnv.geobackend.util; + +import org.apache.log4j.Logger; + +import java.text.SimpleDateFormat; +import java.util.Date; + + +/** + * The class <code>DateUtisl</code> fulfills the following purposes: + * <ol> + * <li></li> + * </ol> + * + * @author blume + * @version 1.0 + * @serial 1.0 + * @see + * @since 21.12.2007 10:34:01 + */ +public class DateUtils { + + /** + * Default Logging instance + */ + private static Logger sLogger = Logger.getLogger(DateUtils.class); + private static boolean sDebug = sLogger.isDebugEnabled(); + + public static final String DATE_PATTERN = "yyyy.MM.dd HH:mm:ss"; + public static final String DATE_PATTERN1 = "dd-MMM-yyyy HH:mm:ss"; + public static final String TimeRangeFilterPattern = "yyyy-MM-dd'T'HH:mm:ssZ"; + public static final String TimeRangeFilterFormPattern = "dd.MM.yyyy HH:mm"; + + public static String getPatternedDateSDF(Date pDate) { + SimpleDateFormat fmt = new SimpleDateFormat(); + fmt.applyPattern(DATE_PATTERN); + return fmt.format(pDate); + } + + public static String getPatternedDate(Date pDate, String sPattern) { +// DateTimeZone.setDefault(DateTimeZone.UTC); +// DateTime dtf = new DateTime (pDate); +// String sStr = dtf.toString(sPattern); +// return sStr; + SimpleDateFormat fmt = new SimpleDateFormat(); + fmt.applyPattern(sPattern); + return fmt.format(pDate); + } + + public static String getPatternedDateAmer(Date pDate) { + + String sStr = getPatternedDate(pDate, DATE_PATTERN); + return sStr; + } + + public static String getPatternedDateGerm(Date pDate) { + + String sStr = getPatternedDate(pDate, DATE_PATTERN1); + return sStr; + } + + public static String getPatternedDateTimeRangeFilter(Date pDate) { + + String sStr = getPatternedDate(pDate, TimeRangeFilterPattern); + return sStr; + } + + public static String getPatternedDateTimeRangeFilterForm(Date pDate) { + + String sStr = getPatternedDate(pDate, TimeRangeFilterFormPattern); + return sStr; + } + + public synchronized static Date getDateFromStringSDF (String sStr)throws Exception { + SimpleDateFormat formatter = new SimpleDateFormat("dd-MMM-yyyy HH:mm:ss"); + Date date = (Date)formatter.parse(sStr); + return date; + } + + public static Date getDateFromString (String sStr)throws Exception { + + Date date = getDateFromString (sStr, DATE_PATTERN1); + return date; + } + + public static Date getDateFromStringTimeRangeFilter (String sStr)throws Exception { + + Date date = getDateFromString (sStr, TimeRangeFilterPattern); + return date; + } + + public static Date getDateFromStringTimeRangeFilterForm (String sStr)throws Exception { + + Date date = getDateFromString (sStr, TimeRangeFilterFormPattern); + return date; + } + + public static Date getDateFromString (String sStr, String sPattern)throws Exception { +// DateTime dtf = null; +// DateTimeZone.setDefault(DateTimeZone.UTC); +// +// dtf = DateTimeFormat.forPattern(sPattern).parseDateTime(sStr); +// +// Date date = dtf.toDate(); +// +// return date; + SimpleDateFormat fmt = new SimpleDateFormat(); + fmt.applyPattern(sPattern); + return fmt.parse(sStr); + } + + +}
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/geo-backend/src/main/java/de/intevation/gnv/geobackend/util/RedundancyRemover.java Fri Sep 28 12:13:50 2012 +0200 @@ -0,0 +1,56 @@ +package de.intevation.gnv.geobackend.util; + +import java.util.LinkedHashMap; +import java.util.Map; + +/** + * @author Sascha L. Teichmann (sascha.teichmann@intevation.de) + */ +public final class RedundancyRemover +extends LinkedHashMap +{ + /** ceil(1029 * 1.75) = 1801, which is prime + * -> suitable for a hash map slot size. + */ + public static final int DEFAULT_LOOKBACK = + Integer.getInteger( + "de.intevation.gnv.geobackend.util.RedundancyRemover.lookback", + 1029); + + private int maxCapacity; + private int removed; + + public RedundancyRemover() { + this(DEFAULT_LOOKBACK); + } + + public RedundancyRemover(int maxCapacity) { + super((int)Math.ceil(maxCapacity * 1.75f)); + this.maxCapacity = maxCapacity; + } + + protected boolean removeEldestEntry(Map.Entry eldest) { + return size() > maxCapacity; + } + + public int numRemoved() { + return removed; + } + + public Object filter(Object object) { + if (object == null) { + return object; + } + Object old = get(object); + + if (old != null) { + if (old != object) { // count only identical + ++removed; + } + return old; + } + put(object, object); + return object; + } +} +// vim:set ts=4 sw=4 si et sta sts=4 fenc=utf8 :
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/geo-backend/src/test/java/de/intevation/gnv/geobackend/base/query/GroupBySample.java Fri Sep 28 12:13:50 2012 +0200 @@ -0,0 +1,123 @@ +package de.intevation.gnv.geobackend.base.query; + +import com.esri.sde.sdk.client.SDEPoint; +import com.esri.sde.sdk.client.SeColumnDefinition; +import com.esri.sde.sdk.client.SeConnection; +import com.esri.sde.sdk.client.SeException; +import com.esri.sde.sdk.client.SeFilter; +import com.esri.sde.sdk.client.SeLayer; +import com.esri.sde.sdk.client.SeQuery; +import com.esri.sde.sdk.client.SeQueryInfo; +import com.esri.sde.sdk.client.SeRow; +import com.esri.sde.sdk.client.SeShape; +import com.esri.sde.sdk.client.SeShapeFilter; +import com.esri.sde.sdk.client.SeSqlConstruct; + +public class GroupBySample { + + + public void executeQuery(SeConnection con, String[] pLayername, + String pSpatialColumnName, String pWhere, + SDEPoint[] g, String[] pReturnFields, String byClause) { + + try { + // get the layer for querying + + SeShapeFilter[] filters = null; + if (g != null){ + SeLayer lLayer = new SeLayer(con, pLayername[0], pSpatialColumnName); + SeShape shape = new SeShape(); + shape.setCoordRef(lLayer.getCoordRef()); + + + + shape.generatePolygon(g.length, 1, null, g); + SeShapeFilter filter = new SeShapeFilter(pLayername[0], + pSpatialColumnName, shape, SeFilter.METHOD_AI); + filters = new SeShapeFilter[1]; + filters[0] = filter; + } + + SeQuery spatialQuery = null; + SeSqlConstruct sqlCons = new SeSqlConstruct(pLayername, pWhere); + spatialQuery = new SeQuery(con); + + SeQueryInfo queryInfo = new SeQueryInfo(); + queryInfo.setColumns(pReturnFields); + + if (byClause != null){ + queryInfo.setByClause(byClause); + } + + queryInfo.setConstruct(sqlCons); + spatialQuery.prepareQueryInfo(queryInfo); + + /* + * Set spatial constraints + */ + if (filters != null){ + spatialQuery.setSpatialConstraints(SeQuery.SE_OPTIMIZE, false, + filters); + } + spatialQuery.execute(); + + SeRow row; + int lCount; + for (lCount = 0; (row =spatialQuery.fetch()) != null; lCount++) { + // one time execution + if (lCount == 0) { + // analyze cols of result set + SeColumnDefinition[] lCols = row.getColumns(); + + } + short lNumCols = row.getNumColumns(); + + for (int i = 0; i < lNumCols; i++) { + System.out.println(row.getObject(i)); + } + + } + spatialQuery.close(); + + } catch (Exception e){ + e.printStackTrace(); + } + + } + + + /** + * @param args + */ + public static void main(String[] args) { + + try { + String[] layerNames = new String[] { "median.meshpoint", "median.mesh" }; + String spatialColumnName = "SHAPE"; + String where = "median.meshpoint.meshid = median.mesh.meshid"; + String[] returnFields = new String[] { "sourceid" }; + String byClause = "group by sourceid"; + SDEPoint[] g = new SDEPoint[5]; + g[0] = new SDEPoint(52, 8); + g[1] = new SDEPoint(52, 9); + g[2] = new SDEPoint(53, 9); + g[3] = new SDEPoint(53, 8); + g[4] = new SDEPoint(52, 8); + + String server = ""; + String port = ""; + String database = ""; + String username = ""; + String credentials = ""; + SeConnection con = new SeConnection(server, port, + database, username, + credentials); + new GroupBySample().executeQuery(con, layerNames, spatialColumnName, + where, g, returnFields, byClause); + + + } catch (SeException e) { + e.printStackTrace(); + } + } +}
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/geo-backend/src/test/java/de/intevation/gnv/geobackend/base/query/QueryExecutorTestCase.java Fri Sep 28 12:13:50 2012 +0200 @@ -0,0 +1,188 @@ +/** + * + */ +package de.intevation.gnv.geobackend.base.query; + +import java.io.FileInputStream; +import java.io.InputStream; +import java.util.Collection; +import java.util.Iterator; +import java.util.Properties; + +import org.apache.log4j.BasicConfigurator; +import org.apache.log4j.Logger; + +import de.intevation.gnv.geobackend.base.Result; +import de.intevation.gnv.geobackend.base.ResultDescriptor; +import de.intevation.gnv.geobackend.base.connectionpool.ConnectionPoolFactory; +import de.intevation.gnv.geobackend.base.query.container.QueryContainerFactory; +import de.intevation.gnv.geobackend.base.query.exception.QueryException; +import junit.framework.TestCase; + +/** + * TestCase for the QueryExecutor interface + * @author Tim Englich <tim.englich@intevation.de> + * + */ +public class QueryExecutorTestCase extends TestCase { + + + /** + * the logger, used to log exceptions and additonaly information + */ + private static Logger log = null; + + static{ + BasicConfigurator.configure(); + log = Logger.getLogger(QueryExecutorTestCase.class); + } + + /** + * Constructor + * @param name + */ + public QueryExecutorTestCase(String name) { + super(name); + } + + + public void testSpatialQuery(){ + try { + + this.testQuery(5, "spatial_query", null); + } catch (QueryException e) { + log.error(e,e); + fail(); + } + } + + public void testSpatialQueryWithoutIntersects(){ + try { +// this.testQuery(286, "spatial_query_without_intersects", null); + this.testQuery(0, "spatial_query_without_intersects2", null); + this.testQuery(2060, "spatial_query_without_intersects3", null); + this.testQuery(0, "spatial_query_without_intersects4", null); + this.testQuery(27031, "spatial_query_without_intersects5", null); + this.testQuery(8464, "spatial_query_without_intersects6", null); + } catch (QueryException e) { + log.error(e,e); + fail(); + } + } + + public void testSpatialQueryWithInnerSelect(){ + try { + this.testQuery(1998, "spatial_query_with_innerselect", null); + } catch (QueryException e) { + log.error(e,e); + fail(); + } + } + + + public void testChooseFis(){ + try { + this.testQuery(1, "choose_fis_query", null); + } catch (QueryException e) { + log.error(e, e); + fail(); + } + } + + + /** + * Tests if the Databaseconnection can be established and + * Data could be read from the Database + */ + public void testQueryExecutor(){ + + try { + + this.testQuery(6, "mesh", null); + + this.testQuery(1, "mesh_id", new String[]{"5"}); + + + + } catch (QueryException e) { + log.error(e,e); + fail(); + } + } + + public void testTimeSeriesQueries(){ + + try{ + // Test zum initialisieren der umgebung + this.testQuery(6, "mesh", null); + + + this.testQuery(11, "timeseries_stations", new String[]{"4"}); + this.testQuery(11, "timeseries_stations_op", new String[]{"4"}); + this.testQuery(1, "timeseries_interval", new String[]{"500042 ", "54"}); + + } catch (QueryException e) { + log.error(e,e); + fail(); + } + + } + + /** + * @param resultsize + * @param queryID + * @param filter + * @throws QueryException + */ + private void testQuery(int resultsize, String queryID, String[] filter) + throws QueryException { + long start = System.currentTimeMillis(); + QueryExecutor queryExecutor = QueryExecutorFactory.getInstance().getQueryExecutor(); + Collection<Result> results = queryExecutor.executeQuery(queryID, filter); +// if (results == null || results.size() == 0){ +// log.error("Keine Daten erhalten"); +// fail(); +// }else { + assertEquals(resultsize, results.size()); + log.debug(resultsize+" Datensätze erhalten"); + Iterator<Result> it = results.iterator(); + while (it.hasNext()){ + Result tmpResult = it.next(); + ResultDescriptor resultDescriptor = tmpResult.getResultDescriptor(); + int columns = resultDescriptor.getColumnCount(); + for (int i = 0; i < columns; i++){ + String columnName = resultDescriptor.getColumnName(i); + Object value = tmpResult.getString(columnName); + log.debug(columnName + " ==> "+value.toString()); + } + } +// } + log.debug("Query dauerte: "+(System.currentTimeMillis()-start) +"ms"); + } + + + + /** + * @see junit.framework.TestCase#setUp() + */ + @Override + protected void setUp() throws Exception { + + super.setUp(); + InputStream inputStream = new FileInputStream("src/test/ressources/ArcSDEConnectionPoolTestCase.properties"); + Properties properties = new Properties(); + properties.load(inputStream); + + ConnectionPoolFactory cpf = ConnectionPoolFactory.getInstance(); + cpf.initializeConnectionPool(properties); + + + inputStream = new FileInputStream("src/test/ressources/QueryExecutorTestCase.properties"); + properties = new Properties(); + properties.load(inputStream); + + QueryContainerFactory qcf = QueryContainerFactory.getInstance(); + qcf.initializeQueryContainer(properties); + } + +}
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/geo-backend/src/test/java/de/intevation/gnv/geobackend/base/query/ToCharSample.java Fri Sep 28 12:13:50 2012 +0200 @@ -0,0 +1,132 @@ +package de.intevation.gnv.geobackend.base.query; + +import com.esri.sde.sdk.client.SDEPoint; +import com.esri.sde.sdk.client.SeColumnDefinition; +import com.esri.sde.sdk.client.SeConnection; +import com.esri.sde.sdk.client.SeException; +import com.esri.sde.sdk.client.SeFilter; +import com.esri.sde.sdk.client.SeLayer; +import com.esri.sde.sdk.client.SeQuery; +import com.esri.sde.sdk.client.SeQueryInfo; +import com.esri.sde.sdk.client.SeRow; +import com.esri.sde.sdk.client.SeShape; +import com.esri.sde.sdk.client.SeShapeFilter; +import com.esri.sde.sdk.client.SeSqlConstruct; + +public class ToCharSample { + + + public boolean executeQuery(SeConnection con, String[] pLayername, + String pSpatialColumnName, String pWhere, + SDEPoint[] g, String[] pReturnFields, String byClause) { + + try { + // get the layer for querying + + SeShapeFilter[] filters = null; + if (g != null){ + SeLayer lLayer = new SeLayer(con, pLayername[0], pSpatialColumnName); + SeShape shape = new SeShape(); + shape.setCoordRef(lLayer.getCoordRef()); + + + + shape.generatePolygon(g.length, 1, null, g); + SeShapeFilter filter = new SeShapeFilter(pLayername[0], + pSpatialColumnName, shape, SeFilter.METHOD_AI); + filters = new SeShapeFilter[1]; + filters[0] = filter; + } + + SeQuery spatialQuery = null; + SeSqlConstruct sqlCons = new SeSqlConstruct(pLayername, pWhere); + spatialQuery = new SeQuery(con); + + SeQueryInfo queryInfo = new SeQueryInfo(); + queryInfo.setColumns(pReturnFields); + + if (byClause != null){ + queryInfo.setByClause(byClause); + } + + queryInfo.setConstruct(sqlCons); + spatialQuery.prepareQueryInfo(queryInfo); + + /* + * Set spatial constraints + */ + if (filters != null){ + spatialQuery.setSpatialConstraints(SeQuery.SE_OPTIMIZE, false, + filters); + } + spatialQuery.execute(); + + SeRow row; + int lCount; + for (lCount = 0; (row =spatialQuery.fetch()) != null; lCount++) { + // one time execution + if (lCount == 0) { + // analyze cols of result set + SeColumnDefinition[] lCols = row.getColumns(); + + } + short lNumCols = row.getNumColumns(); + + for (int i = 0; i < lNumCols; i++) { + System.out.println(row.getObject(i)); + } + + } + spatialQuery.close(); + return true; + } catch (Exception e){ + e.printStackTrace(); + return false; + } + + } + + + /** + * @param args + */ + public static void main(String[] args) { + + try { + + String[] layerNames = new String[] { "MEDIAN.TRACK" }; + String spatialColumnName = "SHAPE"; + String where = "MEDIAN.TRACK.CRUISEID = 47"; + String[] returnFields = new String[] { "MEDIAN.TRACK.TRACKID KEY", + "to_char(STARTDATE,'DD.MM.YYYY HH24:MI') || ' - '|| to_char(ENDDATE,'DD.MM.YYYY HH24:MI') || ' - '|| NAME VALUE", + "NAME" }; + String byClause = "ORDER BY STARTDATE, ENDDATE, NAME"; + SDEPoint[] g = new SDEPoint[5]; + g[0] = new SDEPoint(52, 8); + g[1] = new SDEPoint(52, 9); + g[2] = new SDEPoint(53, 9); + g[3] = new SDEPoint(53, 8); + g[4] = new SDEPoint(52, 8); + + String server = ""; + String port = ""; + String database = ""; + String username = ""; + String credentials = ""; + + SeConnection con = new SeConnection(server, port, + database, username, + credentials); + boolean success = new ToCharSample().executeQuery(con, layerNames, spatialColumnName, + where, g, returnFields, byClause); + + if (success){ + System.out.println("Terminated successful."); + }else{ + System.out.println("Terminated with an Exception."); + } + } catch (SeException e) { + e.printStackTrace(); + } + } +}
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/geo-backend/src/test/java/de/intevation/gnv/geobackend/sde/datasources/ArcSDEConnectionPoolTestCase.java Fri Sep 28 12:13:50 2012 +0200 @@ -0,0 +1,120 @@ +package de.intevation.gnv.geobackend.sde.datasources; + +import java.io.FileInputStream; +import java.io.FileNotFoundException; +import java.io.IOException; +import java.io.InputStream; +import java.sql.Connection; +import java.sql.SQLException; +import java.sql.Statement; +import java.util.Properties; +import java.sql.ResultSet; + +import junit.framework.TestCase; + +import org.apache.log4j.BasicConfigurator; +import org.apache.log4j.Logger; + +import de.intevation.gnv.geobackend.base.connectionpool.ConnectionPool; +import de.intevation.gnv.geobackend.base.connectionpool.ConnectionPoolFactory; +import de.intevation.gnv.geobackend.base.connectionpool.exception.ConnectionException; +import de.intevation.gnv.geobackend.sde.connectionpool.ArcSDEPoolableObjectFactory; + +/** + * TestCase for the usage of the ArcSDEConnectionPool. + * @author Tim Englich <tim.englich@intevation.de> + * + */ +public class ArcSDEConnectionPoolTestCase extends TestCase { + + /** + * the logger, used to log exceptions and additonaly information + */ + private static Logger log = null; + + static { + BasicConfigurator.configure(); + log = Logger.getLogger(ArcSDEPoolableObjectFactory.class); + } + /** + * + */ + public ArcSDEConnectionPoolTestCase() { + super(); + } + + /** + * + * @param name + */ + public ArcSDEConnectionPoolTestCase(String name) { + super(name); + } + + /** + * Test if the ArcSDEConnectionPool can be established + * and if a Request could be done. + */ + public void testArcSDEConnectionPool(){ + log.debug("ArcSDEConnectionPoolTestCase.testArcSDEConnectionPool"); + try { + InputStream inputStream = new FileInputStream("src/test/ressources/ArcSDEConnectionPoolTestCase.properties"); + Properties properties = new Properties(); + properties.load(inputStream); + + ConnectionPoolFactory cpf = ConnectionPoolFactory.getInstance(); + cpf.initializeConnectionPool(properties); + + assertTrue(cpf.isInitialized()); + log.debug("ConnectionPoolFactory ist initialisiert."); + + ConnectionPool cp = cpf.getConnectionPool(); + assertNotNull(cp); + log.debug("ConnectionPool ist initialisiert."); + + Connection connection = null; + try{ + try { + connection = cp.getConnection("N/N"); + assertNotNull(connection); + log.debug("Connection ist initialisiert."); + } catch (ConnectionException e) { + log.error("Es traten Probleme bei der Verbinung zur Datenbank auf."); + fail(); + } + + + try { + Statement stmt = connection.createStatement(); + ResultSet rs = stmt.executeQuery("Select MESHID, NAME from MEDIAN.MESH"); + + while (rs.next()){ + log.debug(rs.getInt(1)); + log.debug(rs.getString(2)); + + log.debug(rs.getInt("MESHID")); + log.debug(rs.getString("NAME")); + } + + } catch (SQLException e) { + log.error(e,e); + } + }finally{ + if (connection != null){ + try { + connection.close(); + } catch (SQLException e) { + log.error(e,e); + fail(); + } + } + } + } catch (FileNotFoundException e) { + log.error(e,e); + fail(); + } catch (IOException e) { + log.error(e,e); + fail(); + } + } +}
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/geo-backend/src/test/ressources/ArcSDEConnectionPoolTestCase.properties Fri Sep 28 12:13:50 2012 +0200 @@ -0,0 +1,10 @@ +maxActive=2 +testOnBorrow=true +testOnReturn=false +testWhileIdle=false +timeBetweenEvictionRunsMillis=360000 +server=localhost +username=gast +credentials=gast +port=22119 +database=esri_sde \ No newline at end of file
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/geo-backend/src/test/ressources/QueryExecutorTestCase.properties Fri Sep 28 12:13:50 2012 +0200 @@ -0,0 +1,98 @@ +mesh=Select MESHID, NAME from MEDIAN.MESH +mesh_id=Select MESHID, NAME from MEDIAN.MESH where MESHID = ? + + + +# Timeseries +timeseries_stations=SELECT tsp.FEATUREID, tsp.NAME FROM MEDIAN.TIMESERIESPOINT tsp WHERE tsp.FEATUREID IN (SELECT DISTINCT mmt.FEATUREID FROM MEDIAN.MEASUREMENT mmt WHERE mmt.SOURCEID IN ( 4 )) order by tsp.name +timeseries_stations_op=SELECT DISTINCT tsp.FEATUREID, tsp.NAME FROM MEDIAN.TIMESERIESPOINT tsp, MEDIAN.MEASUREMENT mmt WHERE tsp.FEATUREID = mmt.FEATUREID AND mmt.SOURCEID IN ( 4 ) order by tsp.name +timeseries_interval=select min(tv.TIMEVALUE) MIN, max(tv.TIMEVALUE) MAX from MEDIAN.TIMESERIES t , MEDIAN.TIMESERIESVALUE tv where tv.TIMESERIESID = t.TIMESERIESID and tv.MEASUREMENTID IN ( ? ) AND t.PARAMETERID IN ( ? ) + + + + spatial_query=SELECT FEATUREID, \ + ST_ASTEXT(SHAPE) \ + FROM MEDIAN.MESHPOINT \ + WHERE MESHID in \ + (SELECT DISTINCT m.MESHID \ + FROM MEDIAN.MESH m \ + WHERE M.OBJECTID = M.OBJECTID) AND \ + KPOSITION = 1 AND \ + INTERSECTS(SHAPE,"POLYGON((6.483333296817733 56.50000002211891,6.483012102287745 56.50981053688071,6.48205002744038 56.51957897448855,6.480451095589132 56.52926359306454,6.478222179894658 56.538822818368885,6.475372835726546 56.54821591435217,6.471915300663454 56.557402480241024,6.46786415921693 56.56634328872852,6.463237181021697 56.57500011833619,6.458053812093169 56.583335585775785,6.452336348293812 56.591314319415915,6.446109264780915 56.59890195345352,6.439399383644684 56.60606613374233,6.432235203355873 56.61277601487856,6.424647569318296 56.61900309839146,6.416668835678138 56.62472056219082,6.408333368238544 56.629903931119344,6.399676538630871 56.63453090931458,6.390735730143376 56.6385820507611,6.381549164254523 56.64203958582419,6.372156068271238 56.644888929992305,6.362596842966894 56.64711784568678,6.352912224390906 56.64871677753803,6.34314378678306 56.64967885238539,6.333333272021264 56.65000004691538,6.323522757259468 56.64967885238539,6.31375431965165 56.64871677753803,6.304069701075662 56.64711784568678,6.29451047577129 56.644888929992305,6.285117379788005 56.64203958582419,6.27593081389918 56.6385820507611,6.266990005411657 56.63453090931458,6.258333343442075 56.629903931119344,6.24999770836439 56.62472056219082,6.24201897472426 56.61900309839146,6.234431340686655 56.61277601487856,6.227267160397872 56.60606613374233,6.220557279261612 56.59890195345352,6.214330195748715 56.591314319415915,6.2086127319493585 56.583335585775785,6.203429363020831 56.57500011833619,6.198802384825626 56.56634328872852,6.194751243379102 56.557402480241024,6.19129370831601 56.54821591435217,6.188444364147898 56.538822818368885,6.186215448453396 56.52926359306454,6.184616516602176 56.51957897448855,6.183654441754783 56.50981053688071,6.183333247224795 56.50000002211891,6.183654441754783 56.490189507357115,6.184616516602176 56.4804210697493,6.186215448453396 56.47073645117331,6.188444364147898 56.46117722586894,6.19129370831601 56.45178412988565,6.194751243379102 56.44259756399683,6.198802384825626 56.433656755509304,6.203429363020831 56.42500009353972,6.2086127319493585 56.41666445846204,6.214330195748715 56.40868572482191,6.220557279261612 56.4010980907843,6.227267160397872 56.39393391049552,6.234431340686655 56.38722402935926,6.24201897472426 56.38099694584636,6.24999770836439 56.375279482047006,6.258333343442075 56.37009611311848,6.266990005411657 56.36546913492327,6.27593081389918 56.36141799347675,6.285117379788005 56.35796045841366,6.29451047577129 56.355111114245545,6.304069701075662 56.35288219855104,6.31375431965165 56.351283266699795,6.323522757259468 56.35032119185243,6.333333272021264 56.34999999732244,6.34314378678306 56.35032119185243,6.352912224390906 56.351283266699795,6.362596842966894 56.35288219855104,6.372156068271238 56.355111114245545,6.381549164254523 56.35796045841366,6.390735730143376 56.36141799347675,6.399676538630871 56.36546913492327,6.408333368238544 56.37009611311848,6.416668835678138 56.375279482047006,6.424647569318296 56.38099694584636,6.432235203355873 56.38722402935926,6.439399383644684 56.39393391049552,6.446109264780915 56.4010980907843,6.452336348293812 56.40868572482191,6.458053812093169 56.41666445846204,6.463237181021697 56.42500009353972,6.46786415921693 56.433656755509304,6.471915300663454 56.44259756399683,6.475372835726546 56.45178412988565,6.478222179894658 56.46117722586894,6.480451095589132 56.47073645117331,6.48205002744038 56.4804210697493,6.483012102287745 56.490189507357115,6.483333296817733 56.50000002211891))") + +spatial_query_without_intersects = SELECT ST_ASTEXT(SHAPE), \ + MSV.DATAVALUE YORDINATE , \ + MSV.PARAMETERID GROUP1 , \ + M.ZLOCATION GROUP2, \ + 1 GROUP3 \ + FROM MEDIAN.INSTANTANEOUSPOINT, \ + MEDIAN.MEASUREMENT M, \ + MEDIAN.MEASUREDSCALARVALUE MSV \ + WHERE MEDIAN.INSTANTANEOUSPOINT.FEATUREID =m.FEATUREID AND \ + m.MEASUREMENTID = MSV.MEASUREMENTID AND \ + MEDIAN.INSTANTANEOUSPOINT.SURVEYID = 1895 AND \ + m.ZLOCATION IN (-16.843 , -15.852) AND \ + MSV.PARAMETERID in (2 , 1) \ + ORDER BY MSV.PARAMETERID, \ + m.ZLOCATION + +spatial_query_without_intersects2 = SELECT ST_ASTEXT(SHAPE), \ + msv.DATAVALUE YORDINATE, \ + msv.PARAMETERID GROUP1, \ + msv.TIMEVALUE GROUP2, \ + MEDIAN.MESHPOINT.KPOSITION GROUP3, \ + MEDIAN.MESHPOINT.JPOSITION , \ + MEDIAN.MESHPOINT.IPOSITION \ + from MEDIAN.MESHLAYER ml, \ + MEDIAN.MESHPOINT , \ + MEDIAN.MESH m, \ + MEDIAN.MESHSCALARVALUE msv \ + where msv.FEATUREID = MEDIAN.MESHPOINT.FEATUREID AND \ + ml.KPOSITION = MEDIAN.MESHPOINT.KPOSITION and \ + ml.MESHID = MEDIAN.MESHPOINT.MESHID and \ + m.MESHID = MEDIAN.MESHPOINT.MESHID AND \ + m.PARTIDMIN <= msv.PARTID AND \ + m.PARTIDMAX >= msv.PARTID AND \ + msv.PARAMETERID in (2,1) AND \ + msv.TIMEVALUE in (to_date('2009.10.15 23:00:00', 'YYYY.MM.DD HH24:MI:SS')) AND \ + m.OBJECTID = 3 AND \ + MEDIAN.MESHPOINT.FEATUREID in \ + ( select FEATUREID \ + from MEDIAN.MESHPOINT mp, \ + MEDIAN.MESH m \ + where m.OBJECTID = 3 AND \ + mp.MESHID = m.MESHID AND \ + KPOSITION in ( 1 ) and \ + JPOSITION = (select JPOSITION from MEDIAN.MESHPOINT where FEATUREID = 2003771)) \ + order by MEDIAN.MESHPOINT.KPOSITION, \ + msv.TIMEVALUE, \ + msv.PARAMETERID, \ + MEDIAN.MESHPOINT.JPOSITION , \ + MEDIAN.MESHPOINT.IPOSITION + + +spatial_query_without_intersects3 = SELECT ST_ASTEXT(SHAPE), MSV.DATAVALUE YORDINATE, MEDIAN.MESHFACE.JPOSITION, MEDIAN.MESHFACE.IPOSITION, MEDIAN.MESHFACE.KPOSITION from MEDIAN.MESHLAYER ML, MEDIAN.MESHFACE, MEDIAN.MESH M, MEDIAN.MESHSCALARVALUE MSV where MSV.FEATUREID = MEDIAN.MESHFACE.FEATUREID AND ML.KPOSITION = MEDIAN.MESHFACE.KPOSITION and ML.MESHID = MEDIAN.MESHFACE.MESHID and M.MESHID = MEDIAN.MESHFACE.MESHID AND M.PARTIDMIN <= MSV.PARTID AND M.PARTIDMAX >= MSV.PARTID AND MSV.PARAMETERID = 1 AND MSV.TIMEVALUE = to_date('2007.01.03 00:00:00', 'YYYY.MM.DD HH24:MI:SS') AND M.OBJECTID = 2 AND MEDIAN.MESHFACE.KPOSITION = 1 order by MEDIAN.MESHFACE.JPOSITION , MEDIAN.MESHFACE.IPOSITION +spatial_query_without_intersects4 = SELECT ST_ASTEXT(SHAPE), MSV.DATAVALUE YORDINATE, MEDIAN.MESHFACE.JPOSITION, MEDIAN.MESHFACE.IPOSITION, MEDIAN.MESHFACE.KPOSITION from MEDIAN.MESHLAYER ML, MEDIAN.MESHFACE, MEDIAN.MESH M, MEDIAN.MESHSCALARVALUE MSV where MSV.FEATUREID = MEDIAN.MESHFACE.FEATUREID AND ML.KPOSITION = MEDIAN.MESHFACE.KPOSITION and ML.MESHID = MEDIAN.MESHFACE.MESHID and M.MESHID = MEDIAN.MESHFACE.MESHID AND M.PARTIDMIN <= MSV.PARTID AND M.PARTIDMAX >= MSV.PARTID AND MSV.PARAMETERID = 1 AND MSV.TIMEVALUE = to_date('2008.01.15 12:00:00', 'YYYY.MM.DD HH24:MI:SS') AND M.OBJECTID = 321 AND MEDIAN.MESHFACE.KPOSITION = 1 order by MEDIAN.MESHFACE.JPOSITION , MEDIAN.MESHFACE.IPOSITION +spatial_query_without_intersects5 = SELECT ST_ASTEXT(SHAPE), MSV.DATAVALUE YORDINATE, MEDIAN.MESHFACE.JPOSITION, MEDIAN.MESHFACE.IPOSITION, MEDIAN.MESHFACE.KPOSITION from MEDIAN.MESHLAYER ML, MEDIAN.MESHFACE, MEDIAN.MESH M, MEDIAN.MESHSCALARVALUE MSV where MSV.FEATUREID = MEDIAN.MESHFACE.FEATUREID AND ML.KPOSITION = MEDIAN.MESHFACE.KPOSITION and ML.MESHID = MEDIAN.MESHFACE.MESHID and M.MESHID = MEDIAN.MESHFACE.MESHID AND M.PARTIDMIN <= MSV.PARTID AND M.PARTIDMAX >= MSV.PARTID AND MSV.PARAMETERID = 2 AND MSV.TIMEVALUE = to_date('2009.10.23 01:00:00', 'YYYY.MM.DD HH24:MI:SS') AND M.OBJECTID = 1 AND MEDIAN.MESHFACE.KPOSITION = 1 order by MEDIAN.MESHFACE.JPOSITION , MEDIAN.MESHFACE.IPOSITION +spatial_query_without_intersects6 = SELECT ST_ASTEXT(SHAPE), MSV.DATAVALUE YORDINATE, MEDIAN.MESHFACE.JPOSITION, MEDIAN.MESHFACE.IPOSITION, MEDIAN.MESHFACE.KPOSITION from MEDIAN.MESHLAYER ML, MEDIAN.MESHFACE, MEDIAN.MESH M, MEDIAN.MESHSCALARVALUE MSV where MSV.FEATUREID = MEDIAN.MESHFACE.FEATUREID AND ML.KPOSITION = MEDIAN.MESHFACE.KPOSITION and ML.MESHID = MEDIAN.MESHFACE.MESHID and M.MESHID = MEDIAN.MESHFACE.MESHID AND M.PARTIDMIN <= MSV.PARTID AND M.PARTIDMAX >= MSV.PARTID AND MSV.PARAMETERID = 2 AND MSV.TIMEVALUE = to_date('2009.10.23 01:00:00', 'YYYY.MM.DD HH24:MI:SS') AND M.OBJECTID = 3 AND MEDIAN.MESHFACE.KPOSITION = 1 order by MEDIAN.MESHFACE.JPOSITION , MEDIAN.MESHFACE.IPOSITION + + + +choose_fis_query = select m.sourceid, \ + from median.meshpoint ,\ + median.mesh \ + where median.meshpoint.meshid = median.mesh.meshid and \ + INTERSECTS(SHAPE,"POLYGON((6.483333296817733 56.50000002211891,6.483012102287745 56.50981053688071,6.48205002744038 56.51957897448855,6.480451095589132 56.52926359306454,6.478222179894658 56.538822818368885,6.475372835726546 56.54821591435217,6.471915300663454 56.557402480241024,6.46786415921693 56.56634328872852,6.463237181021697 56.57500011833619,6.458053812093169 56.583335585775785,6.452336348293812 56.591314319415915,6.446109264780915 56.59890195345352,6.439399383644684 56.60606613374233,6.432235203355873 56.61277601487856,6.424647569318296 56.61900309839146,6.416668835678138 56.62472056219082,6.408333368238544 56.629903931119344,6.399676538630871 56.63453090931458,6.390735730143376 56.6385820507611,6.381549164254523 56.64203958582419,6.372156068271238 56.644888929992305,6.362596842966894 56.64711784568678,6.352912224390906 56.64871677753803,6.34314378678306 56.64967885238539,6.333333272021264 56.65000004691538,6.323522757259468 56.64967885238539,6.31375431965165 56.64871677753803,6.304069701075662 56.64711784568678,6.29451047577129 56.644888929992305,6.285117379788005 56.64203958582419,6.27593081389918 56.6385820507611,6.266990005411657 56.63453090931458,6.258333343442075 56.629903931119344,6.24999770836439 56.62472056219082,6.24201897472426 56.61900309839146,6.234431340686655 56.61277601487856,6.227267160397872 56.60606613374233,6.220557279261612 56.59890195345352,6.214330195748715 56.591314319415915,6.2086127319493585 56.583335585775785,6.203429363020831 56.57500011833619,6.198802384825626 56.56634328872852,6.194751243379102 56.557402480241024,6.19129370831601 56.54821591435217,6.188444364147898 56.538822818368885,6.186215448453396 56.52926359306454,6.184616516602176 56.51957897448855,6.183654441754783 56.50981053688071,6.183333247224795 56.50000002211891,6.183654441754783 56.490189507357115,6.184616516602176 56.4804210697493,6.186215448453396 56.47073645117331,6.188444364147898 56.46117722586894,6.19129370831601 56.45178412988565,6.194751243379102 56.44259756399683,6.198802384825626 56.433656755509304,6.203429363020831 56.42500009353972,6.2086127319493585 56.41666445846204,6.214330195748715 56.40868572482191,6.220557279261612 56.4010980907843,6.227267160397872 56.39393391049552,6.234431340686655 56.38722402935926,6.24201897472426 56.38099694584636,6.24999770836439 56.375279482047006,6.258333343442075 56.37009611311848,6.266990005411657 56.36546913492327,6.27593081389918 56.36141799347675,6.285117379788005 56.35796045841366,6.29451047577129 56.355111114245545,6.304069701075662 56.35288219855104,6.31375431965165 56.351283266699795,6.323522757259468 56.35032119185243,6.333333272021264 56.34999999732244,6.34314378678306 56.35032119185243,6.352912224390906 56.351283266699795,6.362596842966894 56.35288219855104,6.372156068271238 56.355111114245545,6.381549164254523 56.35796045841366,6.390735730143376 56.36141799347675,6.399676538630871 56.36546913492327,6.408333368238544 56.37009611311848,6.416668835678138 56.375279482047006,6.424647569318296 56.38099694584636,6.432235203355873 56.38722402935926,6.439399383644684 56.39393391049552,6.446109264780915 56.4010980907843,6.452336348293812 56.40868572482191,6.458053812093169 56.41666445846204,6.463237181021697 56.42500009353972,6.46786415921693 56.433656755509304,6.471915300663454 56.44259756399683,6.475372835726546 56.45178412988565,6.478222179894658 56.46117722586894,6.480451095589132 56.47073645117331,6.48205002744038 56.4804210697493,6.483012102287745 56.490189507357115,6.483333296817733 56.50000002211891))") \ + group by sourceid + + +spatial_query_with_innerselect = SELECT M.OBJECTID KEY, \ + M.NAME VALUE \ + FROM MEDIAN.MESHPOINT, \ + MEDIAN.MESH M \ + WHERE M.SOURCEID IN (2) AND \ + M.MESHID = MEDIAN.MESHPOINT.MESHID AND \ + INTERSECTS(SHAPE, \ + (SELECT st_astext(SHAPE) FROM MEDIAN.FEATUREAREA WHERE (FEATURETYPE = 7 OR FEATURETYPE = 8) AND FEATURECODE = 41 )\ + ) \ + order by M.NAME