    001    //$HeadURL$
    002    /*----------------------------------------------------------------------------
    003     This file is part of deegree, http://deegree.org/
    004     Copyright (C) 2001-2009 by:
    005     Department of Geography, University of Bonn
    006     and
    007     lat/lon GmbH
    008
    009     This library is free software; you can redistribute it and/or modify it under
    010     the terms of the GNU Lesser General Public License as published by the Free
    011     Software Foundation; either version 2.1 of the License, or (at your option)
    012     any later version.
    013     This library is distributed in the hope that it will be useful, but WITHOUT
    014     ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
    015     FOR A PARTICULAR PURPOSE. See the GNU Lesser General Public License for more
    016     details.
    017     You should have received a copy of the GNU Lesser General Public License
    018     along with this library; if not, write to the Free Software Foundation, Inc.,
    019     59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
    020
    021     Contact information:
    022
    023     lat/lon GmbH
    024     Aennchenstr. 19, 53177 Bonn
    025     Germany
    026     http://lat-lon.de/
    027
    028     Department of Geography, University of Bonn
    029     Prof. Dr. Klaus Greve
    030     Postfach 1147, 53001 Bonn
    031     Germany
    032     http://www.geographie.uni-bonn.de/deegree/
    033
    034     e-mail: info@deegree.org
    035     ----------------------------------------------------------------------------*/
    036
    037    package org.deegree.io.databaseloader;
    038
    039    import java.net.URI;
    040    import java.sql.Connection;
    041    import java.sql.PreparedStatement;
    042    import java.sql.ResultSet;
    043    import java.sql.ResultSetMetaData;
    044    import java.sql.SQLException;
    045    import java.util.UUID;
    046
    047    import oracle.spatial.geometry.JGeometry;
    048    import oracle.sql.STRUCT;
    049
    050    import org.deegree.datatypes.QualifiedName;
    051    import org.deegree.datatypes.Types;
    052    import org.deegree.framework.log.ILogger;
    053    import org.deegree.framework.log.LoggerFactory;
    054    import org.deegree.io.DBConnectionPool;
    055    import org.deegree.io.DBPoolException;
    056    import org.deegree.io.JDBCConnection;
    057    import org.deegree.io.datastore.sql.oracle.JGeometryAdapter;
    058    import org.deegree.model.crs.CRSTransformationException;
    059    import org.deegree.model.crs.CoordinateSystem;
    060    import org.deegree.model.crs.GeoTransformer;
    061    import org.deegree.model.feature.FeatureCollection;
    062    import org.deegree.model.feature.FeatureFactory;
    063    import org.deegree.model.feature.FeatureProperty;
    064    import org.deegree.model.feature.schema.FeatureType;
    065    import org.deegree.model.feature.schema.PropertyType;
    066    import org.deegree.model.spatialschema.Envelope;
    067    import org.deegree.model.spatialschema.GeometryException;
    068    import org.deegree.model.spatialschema.GeometryFactory;
    069    import org.deegree.model.spatialschema.Surface;
    070    import org.deegree.ogcwebservices.wms.configuration.DatabaseDataSource;
    071
    072    /**
    073     * class for loading data as feature collection from a postgis database
    074     *
    075     * @author <a href="mailto:poth@lat-lon.de">Andreas Poth</a>
    076     * @author last edited by: $Author: poth $
    077     *
    078     * @version $Revision: 6251 $, $Date: 2007-03-19 16:59:28 +0100 (Mo, 19 Mrz 2007) $
    079     */
    080    public class OracleDataLoader {
    081
    082        private static final ILogger LOG = LoggerFactory.getLogger( OracleDataLoader.class );
    083
    084        private static URI namespace;
    085        static {
    086            try {
    087                namespace = new URI( "http://www.deegree.org/database" );
    088            } catch ( Exception e ) {
    089                LOG.logError( e.getMessage(), e );
    090            }
    091        }
    092
    093        /**
    094         * @param datasource
    095         * @param envelope
    096         * @return the feature collection directly from the db
    097         * @throws Exception
    098         */
    099        public static FeatureCollection load( DatabaseDataSource datasource, Envelope envelope )
    100                                throws Exception {
    101            return load( datasource, envelope, null );
    102        }
    103
    104        /**
    105         * @param datasource
    106         * @param envelope
    107         * @param sql
    108         * @return the fc from the db
    109         * @throws Exception
    110         */
    111        public static FeatureCollection load( DatabaseDataSource datasource, Envelope envelope, String sql )
    112                                throws Exception {
    113            return load( datasource, envelope, sql, null );
    114        }
    115
    116        /**
    117         *
    118         * @param datasource
    119         * @param envelope
    120         * @param sql
    121         * @param extraClauses
    122         * @return featurecollection loaded from a postgis database
    123         * @throws Exception
    124         */
    125        public static FeatureCollection load( DatabaseDataSource datasource, Envelope envelope, String sql,
    126                                              String extraClauses )
    127                                throws Exception {
    128            if ( sql == null ) {
    129                sql = datasource.getSqlTemplate();
    130            }
    131
    132            JDBCConnection jdbc = datasource.getJDBCConnection();
    133            PreparedStatement stmt = null;
    134            Connection conn = null;
    135            ResultSet rs = null;
    136            FeatureCollection fc = FeatureFactory.createFeatureCollection( UUID.randomUUID().toString(), 10000 );
    137            try {
    138                CoordinateSystem crs = datasource.getNativeCRS();
    139                conn = acquireConnection( jdbc );
    140                stmt = createPreparedStatement( datasource, envelope, conn, crs, sql, extraClauses );
    141
    142                rs = stmt.executeQuery();
    143
    144                LOG.logDebug( "performing database query: " + sql );
    145                ResultSetMetaData rsmd = rs.getMetaData();
    146                FeatureType featureType = createFeatureType( datasource, datasource.getGeometryFieldName(), rsmd );
    147                int ccnt = rsmd.getColumnCount();
    148                int k = 0;
    149
    150                // read each line from database and create a feature from it
    151                while ( rs.next() ) {
    152                    FeatureProperty[] properties = new FeatureProperty[ccnt];
    153                    for ( int i = 0; i < ccnt; i++ ) {
    154                        String name = rsmd.getColumnName( i + 1 );
    155                        Object value = rs.getObject( i + 1 );
    156                        // if column name equals geometry field name the value read from
    157                        // database must be converted into a deegree geometry
    158                        if ( name.equalsIgnoreCase( datasource.getGeometryFieldName() ) ) {
    159                            JGeometry jGeometry = JGeometry.load( (STRUCT) value );
    160                            value = JGeometryAdapter.wrap( jGeometry, crs );
    161                        }
    162                        properties[i] = FeatureFactory.createFeatureProperty( featureType.getPropertyName( i ), value );
    163                    }
    164                    // because feature IDs are not important in case of database datasource
    165                    // it is just 'ID' as prefix plus a number of current row
    166                    fc.add( FeatureFactory.createFeature( "ID" + k++, featureType, properties ) );
    167                }
    168                LOG.logDebug( k + " features loaded from database" );
    169            } catch ( Exception e ) {
    170                LOG.logError( e.getMessage(), e );
    171                throw e;
    172            } finally {
    173                try {
    174                    if ( rs != null ) {
    175                        rs.close();
    176                    }
    177                } catch ( Exception e ) {
    178                    // what to do here anyway
    179                }
    180                try {
    181                    if ( stmt != null ) {
    182                        stmt.close();
    183                    }
    184                } catch ( SQLException e ) {
    185                    // what to do here anyway
    186                }
    187                releaseConnection( jdbc, conn );
    188            }
    189            return fc;
    190        }
    191
    192        private static PreparedStatement createPreparedStatement( DatabaseDataSource datasource, Envelope envelope,
    193                                                                  Connection conn, CoordinateSystem crs, String sql,
    194                                                                  String extraClauses )
    195                                throws GeometryException, SQLException, IllegalArgumentException,
    196                                CRSTransformationException {
    197            PreparedStatement stmt;
    198
    199            String nativeCRS = crs.getLocalName();
    200            String envCRS = nativeCRS;
    201            if ( envelope.getCoordinateSystem() != null ) {
    202                envCRS = envelope.getCoordinateSystem().getLocalName();
    203            }
    204
    205            // use the bbox operator (&&) to filter using the spatial index
    206            if ( !( nativeCRS.equals( envCRS ) ) ) {
    207                GeoTransformer gt = new GeoTransformer( crs );
    208                envelope = gt.transform( envelope, envelope.getCoordinateSystem() );
    209            }
    210            Surface surface = GeometryFactory.createSurface( envelope, envelope.getCoordinateSystem() );
    211            JGeometry jgeom = JGeometryAdapter.export( surface, Integer.parseInt( nativeCRS ) );
    212            StringBuffer query = new StringBuffer( 1000 );
    213            query.append( " MDSYS.SDO_RELATE(" );
    214            query.append( datasource.getGeometryFieldName() );
    215            query.append( ',' );
    216            query.append( '?' );
    217            query.append( ",'MASK=ANYINTERACT QUERYTYPE=WINDOW')='TRUE'" );
    218
    219            if ( extraClauses != null ) {
    220                query.append( extraClauses );
    221            }
    222
    223            if ( sql.trim().toUpperCase().endsWith( " WHERE" ) ) {
    224                LOG.logDebug( "performed SQL: ", sql );
    225                stmt = conn.prepareStatement( sql + query );
    226            } else {
    227                LOG.logDebug( "performed SQL: ", sql + " AND " + query );
    228                stmt = conn.prepareStatement( sql + " AND " + query );
    229            }
    230
    231            LOG.logDebug( "Converting JGeometry to STRUCT." );
    232            STRUCT struct = JGeometry.store( jgeom, conn );
    233            stmt.setObject( 1, struct, java.sql.Types.STRUCT );
    234            return stmt;
    235        }
    236
    237        /**
    238         *
    239         * @param geometryFiedName
    240         * @param rsmd
    241         * @return {@link FeatureType} created from column names and types
    242         * @throws SQLException
    243         */
    244        private static FeatureType createFeatureType( DatabaseDataSource datasource, String geometryFiedName,
    245                                                      ResultSetMetaData rsmd )
    246                                throws SQLException {
    247            int ccnt = rsmd.getColumnCount();
    248            QualifiedName name = new QualifiedName( datasource.getName().getLocalName(), namespace );
    249            PropertyType[] properties = new PropertyType[ccnt];
    250            for ( int i = 0; i < ccnt; i++ ) {
    251                QualifiedName propName = new QualifiedName( rsmd.getColumnName( i + 1 ), namespace );
    252                LOG.logDebug( "propertyname: ", propName );
    253                int typeCode = getTypeCode( geometryFiedName, rsmd.getColumnName( i + 1 ), rsmd.getColumnType( i + 1 ) );
    254                properties[i] = FeatureFactory.createSimplePropertyType( propName, typeCode, true );
    255            }
    256            return FeatureFactory.createFeatureType( name, false, properties );
    257        }
    258
    259        private static int getTypeCode( String geometryFiedName, String columnName, int columnType ) {
    260            if ( columnName.equalsIgnoreCase( geometryFiedName ) ) {
    261                return Types.GEOMETRY;
    262            }
    263            return columnType;
    264        }
    265
    266        private static void releaseConnection( JDBCConnection jdbc, Connection conn ) {
    267            try {
    268                DBConnectionPool pool = DBConnectionPool.getInstance();
    269                pool.releaseConnection( conn, jdbc.getDriver(), jdbc.getURL(), jdbc.getUser(), jdbc.getPassword() );
    270            } catch ( DBPoolException e ) {
    271                // what to do here anyway
    272            }
    273        }
    274
    275        private static Connection acquireConnection( JDBCConnection jdbc )
    276                                throws DBPoolException {
    277            DBConnectionPool pool = DBConnectionPool.getInstance();
    278            return pool.acquireConnection( jdbc.getDriver(), jdbc.getURL(), jdbc.getUser(), jdbc.getPassword() );
    279        }
    280
    281    }