tim@129: /**
tim@129:  * 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 $
tim@129:  * Source:          $Source: /share/gdi/SDI-Suite/Repository/projekte/BSH-GDI/genericViewer/src/main/java/de/conterra/bsh/gdi/gnviewer/datasources/sde/SDEQuery.java,v $
tim@129:  * created by:      Stefan Blume (blume)
tim@129:  * erstellt am:     21.11.2007
tim@129:  * Copyright:       con terra GmbH, 2005
tim@129:  *
tim@129:  * modified by:     $Author: blume $
tim@129:  * modified on:     $Date: 2008/01/30 12:38:34 $
tim@129:  * Version:         $Revision: 1.3 $
tim@129:  * TAG:             $Name:  $
tim@129:  * locked from:     $Locker:  $
tim@129:  * CVS State:       $State: Exp $
tim@129:  * Project:         $ProjectName$
tim@129:  */
tim@129: package de.intevation.gnv.geobackend.sde.datasources;
tim@129: 
tim@130: import java.sql.Connection;
tim@130: import java.sql.Statement;
tim@130: 
tim@129: import org.apache.log4j.Logger;
tim@129: 
tim@129: import com.esri.sde.sdk.client.SDEPoint;
tim@129: import com.esri.sde.sdk.client.SeColumnDefinition;
tim@129: import com.esri.sde.sdk.client.SeConnection;
tim@129: import com.esri.sde.sdk.client.SeCoordinateReference;
tim@129: import com.esri.sde.sdk.client.SeException;
tim@129: import com.esri.sde.sdk.client.SeFilter;
tim@129: import com.esri.sde.sdk.client.SeLayer;
tim@129: import com.esri.sde.sdk.client.SeQuery;
tim@129: import com.esri.sde.sdk.client.SeRow;
tim@129: import com.esri.sde.sdk.client.SeShape;
tim@129: import com.esri.sde.sdk.client.SeShapeFilter;
tim@129: import com.esri.sde.sdk.client.SeSqlConstruct;
tim@129: 
tim@130: import de.intevation.gnv.geobackend.base.connectionpool.ConnectionPool;
tim@130: import de.intevation.gnv.geobackend.base.connectionpool.exception.ConnectionException;
tim@129: import de.intevation.gnv.geobackend.sde.datasources.exception.TechnicalException;
tim@129: import de.intevation.gnv.geobackend.util.DateUtils;
tim@129: 
tim@129: /**
tim@129:  * // todo: supply a general interface for Query-Objects (Query = read access to datasource implementation)
tim@129:  *
tim@129:  * @author blume
tim@129:  * @version 1.0
tim@129:  * @serial 1.0
tim@129:  * @see
tim@129:  * @since 21.11.2007 11:32:19
tim@129:  */
tim@129: public class SDEQuery {
tim@129: 
tim@129:     /**
tim@129:      * Default Logging instance
tim@129:      */
tim@129:     private static final Logger sLogger = Logger.getLogger(SDEQuery.class);
tim@129:     private static boolean sDebug = sLogger.isDebugEnabled();
tim@129: 
tim@129: 
tim@130:     private ConnectionPool connectionPool = null;
tim@130:     private String connectionID = "N/N";
tim@129: 
tim@130:     public SDEQuery(ConnectionPool connectionPool) {
tim@129:         if (sDebug) sLogger.debug("SDEQuery()");
tim@130:         this.connectionPool = connectionPool;
tim@129:     }
tim@129: 
tim@129:     /**
tim@129:      * Execute a query against a sde datasource.
tim@129:      * This Query method is responsible for getting and returning a valid {@link SeConnection}-Object.
tim@129:      *
tim@129:      * @param pTables
tim@129:      * @param pCols
tim@129:      * @param pWhere  the where-clause to be executed.
tim@129:      * @return a ResultSet
tim@129:      * @see SeSqlConstruct
tim@129:      * @see SeQuery
tim@129:      */
tim@129:     public ResultSet executeQuery(String pTables[], String pCols[], String pWhere) throws TechnicalException {
tim@129:         sLogger.debug("executeQuery()");
tim@130:         Connection con = null;
tim@129:         try {
tim@129:             con = getConnection();
tim@130: //            SeSqlConstruct lSeSqlConstruct = new SeSqlConstruct(pTables, pWhere);
tim@130: //            SeQuery lSeQuery = new SeQuery(con, pCols, lSeSqlConstruct);
tim@130: //            long lStart = System.currentTimeMillis();
tim@130: //            lSeQuery.prepareQuery();
tim@130:             ResultSet lSet = null;//handleResultSet(lSeQuery);
tim@130: //            long lEnd = System.currentTimeMillis();
tim@130: //            if (sDebug)
tim@130: //                sLogger.debug((new StringBuilder()).append("lSeQuery lasts ").append((double) (lEnd - lStart) / 1000D).append(" seconds").toString());
tim@129:             return lSet;
tim@129:         } catch (TechnicalException e) {
tim@129:             sLogger.error(e.getMessage(), e);
tim@129:             throw e;
tim@130:         } catch (Exception e) {
tim@129:             sLogger.error(e.getMessage(), e);
tim@129:             throw new TechnicalException("Error during executeQuery", e);
tim@129:         } finally {
tim@129:             returnConnection(con);
tim@129:         }
tim@129: 
tim@129:     }
tim@129: 
tim@129:     /**
tim@129:      * Execute a query against a sde datasource.
tim@129:      * This Query method is responsible for getting and returning a valid {@link SeConnection}-Object.
tim@129:      *
tim@129:      * @param pSQLStatement the SQL-Statement to be executed.
tim@129:      * @return a ResultSet
tim@129:      * @see SeQuery
tim@129:      */
tim@129:     public ResultSet executeQuery(String pSQLStatement) throws TechnicalException {
tim@129:         if (sDebug) sLogger.debug("executeQuery():\n" + pSQLStatement);
tim@130:         Connection con = null;
tim@129:         try {
tim@129:             con = getConnection();
tim@129: 
tim@129: 
tim@130:             long lStart = System.currentTimeMillis();
tim@129:             
tim@130:             Statement stmt = con.createStatement();
tim@130:             java.sql.ResultSet rs = stmt.executeQuery(pSQLStatement);
tim@130:             
tim@129:             long lEnd = System.currentTimeMillis();
tim@129:             if (sDebug)
tim@129:                 sLogger.debug((new StringBuilder()).append("lSeQuery lasts ").append((double) (lEnd - lStart) / 1000D).append(" seconds").toString());
tim@130:             return (ResultSet)rs;
tim@129:         } catch (TechnicalException e) {
tim@129:             sLogger.error(e.getMessage(), e);
tim@129:             throw e;
tim@130:         } catch (Exception e) {
tim@129:             sLogger.error(e.getMessage(), e);
tim@129:             throw new TechnicalException("Error during executeQuery", e);
tim@129:         } finally {
tim@129:             returnConnection(con);
tim@129:         }
tim@129: 
tim@129: 
tim@129:     }
tim@129:     
tim@129: //    /**
tim@129: //     * Execute a query against a sde datasource.
tim@129: //     * This Query method is responsible for getting and returning a valid {@link SeConnection}-Object.
tim@129: //     *
tim@129: //     * @param pSQLStatement the SQL-Statement to be executed.
tim@129: //     * @return File
tim@129: //     * @see SeQuery
tim@129: //     */
tim@129: //    public TempFile  executeQueryDiagram (String pSQLStatement) throws TechnicalException {
tim@129: //        if (sDebug) sLogger.debug("executeQueryDiagram():\n" + pSQLStatement);
tim@129: //        SeConnection con = null;
tim@129: //        TempFile lImageFile = null;
tim@129: //        try {
tim@129: //            con = getConnection();
tim@129: //
tim@129: //            SeQuery lSeQuery = new SeQuery(con);
tim@129: //            long lStart = System.currentTimeMillis();
tim@129: //            lSeQuery.prepareSql(pSQLStatement);
tim@129: //            lSeQuery.execute();
tim@129: //
tim@129: //            //ResultSet lSet = handleResultSet(lSeQuery);
tim@129: //            lImageFile = handleRowToCSVFile(lSeQuery);
tim@129: //            
tim@129: //
tim@129: //            long lEnd = System.currentTimeMillis();
tim@129: //            if (sDebug)
tim@129: //                sLogger.debug((new StringBuilder()).append("lSeQuery lasts ").append((double) (lEnd - lStart) / 1000D).append(" seconds").toString());
tim@129: //            
tim@129: //        } catch (TechnicalException e) {
tim@129: //            sLogger.error(e.getMessage(), e);
tim@129: //            throw e;
tim@129: //        } catch (SeException e) {
tim@129: //            sLogger.error(e.getMessage(), e);
tim@129: //            throw new TechnicalException("Error during executeQuery", e);
tim@129: //        } finally {
tim@129: //            returnConnection(con);
tim@129: //        }
tim@129: //        return lImageFile;
tim@129: //
tim@129: //    }
tim@129: 
tim@129:     /**
tim@129:      * Execute a spatial query against a sde datasource.
tim@129:      * This Query method is responsible for getting and returning a valid {@link SeConnection}-Object.
tim@129:      * // todo: there is work needed to fully implement the wanted functionality.
tim@129:      *
tim@129:      * @param pLayername
tim@129:      * @param pSpatialColumnName
tim@129:      * @param pPoints
tim@129:      * @param pReturnFields
tim@129:      * @return a ResultSet
tim@129:      * @see SeShapeFilter
tim@129:      * @see SeQuery
tim@129:      */
tim@129:     public ResultSet executeQuery(String pLayername, String pSpatialColumnName, double[][] pPoints, String[] pReturnFields) throws TechnicalException {
tim@129:         sLogger.debug("executeQuery()");
tim@130:         Connection con = null;
tim@129:         try {
tim@129:             con = getConnection();
tim@130: //            // get the layer for querying
tim@130: //            SeLayer lLayer = new SeLayer(con, pLayername, pSpatialColumnName);
tim@130: //            SeCoordinateReference cref = lLayer.getCoordRef();
tim@130: //
tim@130: //
tim@130: //            SeShape shape = new SeShape();
tim@130: //            shape.setCoordRef(lLayer.getCoordRef());
tim@130: //            SDEPoint[] lPoints = ArcSDEUtils.createPoints(pPoints);
tim@130: //
tim@130: //            /*
tim@130: //            * int numPts, int numParts, int[] partOffsets,SDEPoint[] ptArray
tim@130: //            * */
tim@130: //            shape.generatePolygon(lPoints.length, 1, null, lPoints);
tim@130: //            SeShapeFilter filter = new SeShapeFilter(pLayername, pSpatialColumnName, shape, SeFilter.METHOD_AI);
tim@130: //            SeShapeFilter[] filters = new SeShapeFilter[1];
tim@130: //            filters[0] = filter;
tim@130: //
tim@130: //            SeQuery spatialQuery = null;
tim@130: //            SeSqlConstruct sqlCons = new SeSqlConstruct(pLayername);
tim@130: //            spatialQuery = new SeQuery(con, pReturnFields, sqlCons);
tim@130: //            spatialQuery.prepareQuery();
tim@130: //            /*
tim@130: //            *   Set spatial constraints
tim@130: //            */
tim@130: //            spatialQuery.setSpatialConstraints(SeQuery.SE_OPTIMIZE, false, filters);
tim@130: //            spatialQuery.execute();
tim@129: 
tim@130:             return null;//handleResultSet(spatialQuery);
tim@129: 
tim@129:         } catch (TechnicalException e) {
tim@129:             sLogger.error(e.getMessage(), e);
tim@129:             throw e;
tim@130:         } catch (Exception e) {
tim@129:             sLogger.error(e.getMessage(), e);
tim@129:             throw new TechnicalException("Error accessing Spatial Database: " + e.getMessage(), e);
tim@129:         } finally {
tim@129:             returnConnection(con);
tim@129:         }
tim@129:     }
tim@129:     /**
tim@129:      * Execute a spatial query against a sde datasource.
tim@129:      * This Query method is responsible for getting and returning a valid {@link SeConnection}-Object.
tim@129:      * // todo: there is work needed to fully implement the wanted functionality.
tim@129:      *
tim@129:      * @param pLayername
tim@129:      * @param pSpatialColumnName
tim@129:      * @param pPoints
tim@129:      * @param pReturnFields
tim@129:      * @return a ResultSet
tim@129:      * @see SeShapeFilter
tim@129:      * @see SeQuery
tim@129:      */
tim@129:     public ResultSet executeQuery(String pLayername, String pSpatialColumnName, String pWhere, double[][] pPoints, String[] pReturnFields) throws TechnicalException {
tim@129:         sLogger.debug("executeQuery()");
tim@130:         Connection con = null;
tim@129:         try {
tim@129:             con = getConnection();
tim@130: //            // get the layer for querying
tim@130: //            SeLayer lLayer = new SeLayer(con, pLayername, pSpatialColumnName);
tim@130: //            SeCoordinateReference cref = lLayer.getCoordRef();
tim@130: //
tim@130: //
tim@130: //            SeShape shape = new SeShape();
tim@130: //            shape.setCoordRef(lLayer.getCoordRef());
tim@130: //            SDEPoint[] lPoints = ArcSDEUtils.createPoints(pPoints);
tim@130: //
tim@130: //            /*
tim@130: //            * int numPts, int numParts, int[] partOffsets,SDEPoint[] ptArray
tim@130: //            * */
tim@130: //            shape.generatePolygon(lPoints.length, 1, null, lPoints);
tim@130: //            SeShapeFilter filter = new SeShapeFilter(pLayername, pSpatialColumnName, shape, SeFilter.METHOD_AI);
tim@130: //            SeShapeFilter[] filters = new SeShapeFilter[1];
tim@130: //            filters[0] = filter;
tim@130: //
tim@130: //            SeQuery spatialQuery = null;
tim@130: //            SeSqlConstruct sqlCons = new SeSqlConstruct(pLayername,pWhere);
tim@130: //            spatialQuery = new SeQuery(con, pReturnFields, sqlCons);
tim@130: //            spatialQuery.prepareQuery();
tim@130: //            /*
tim@130: //            *   Set spatial constraints
tim@130: //            */
tim@130: //            spatialQuery.setSpatialConstraints(SeQuery.SE_OPTIMIZE, false, filters);
tim@130: //            spatialQuery.execute();
tim@129: 
tim@130:             return null;//handleResultSet(spatialQuery);
tim@129: 
tim@129:         } catch (TechnicalException e) {
tim@129:             sLogger.error(e.getMessage(), e);
tim@129:             throw e;
tim@130:         } catch (Exception e) {
tim@129:             sLogger.error(e.getMessage(), e);
tim@129:             throw new TechnicalException("Error accessing Spatial Database: " + e.getMessage(), e);
tim@129:         } finally {
tim@129:             returnConnection(con);
tim@129:         }
tim@129:     }
tim@130:     private Connection getConnection() throws TechnicalException {
tim@130:     	Connection  connection = connectionPool.getConnection(connectionID);
tim@130:         return connection;
tim@129:     }
tim@129: 
tim@130:     private void returnConnection(Connection connection) {
tim@130:         try {
tim@130: 	        this.connectionPool.closeConnection(connection);
tim@130:         } catch (ConnectionException e) {
tim@130:         	sLogger.error(e,e);
tim@130:         }
tim@129:     }
tim@129: 
tim@129: 
tim@129:     private ResultSet handleResultSet(SeQuery pSeQuery) throws SeException {
tim@129:         sLogger.debug("handleResultSet()");
tim@129:         SDEResultSet lSet = new SDEResultSet();
tim@129:         SeRow row;
tim@129:         int lCount;
tim@129:         for (lCount = 0; (row = pSeQuery.fetch()) != null; lCount++) {
tim@129:             // one time execution
tim@129:             if (lCount == 0) {
tim@129:                 // analyze cols of result set
tim@129:                 SeColumnDefinition[] lCols = row.getColumns();
tim@129:                 for (SeColumnDefinition lCol : lCols) {
tim@129:                     lSet.addCol(new ColDefinition(lCol.getName(), lCol.getType()));// notice: esri-types have been copied into colDefinition class!
tim@129:                 }
tim@129:             }
tim@129:             short lNumCols = row.getNumColumns();
tim@129:             Row lBackingRow = new Row(lNumCols);
tim@129:             for (int i = 0; i < lNumCols; i++) {
tim@129:                 lBackingRow.addObject(row.getObject(i), i);
tim@129:             }
tim@129:             lSet.addRow(lBackingRow);
tim@129:         }
tim@129:         pSeQuery.close();
tim@129:         return lSet;
tim@129:     }
tim@129:  
tim@129: //    private TempFile handleRowToCSVFile(SeQuery pSeQuery) throws SeException {
tim@129: //        sLogger.debug("handleRowToTempFile()");
tim@129: //        TempFile lImageFile = TemporaryFileDirectory.getInstance().createFile(".csv");
tim@129: //        sLogger.debug("handleRowToTempFile() erstellen " + lImageFile.getFile().getName());
tim@129: //        CSVWriter writer = null;
tim@129: //        ColDefinition[] lColDefinitions = null;
tim@129: //        
tim@129: //        SeRow row;
tim@129: //        int lCount;
tim@129: //        try {
tim@129: //	        writer = new CSVWriter(new FileWriter(lImageFile.getFile()), ';');
tim@129: //	        for (lCount = 0; (row = pSeQuery.fetch()) != null; lCount++) {
tim@129: //	            // one time execution
tim@129: //	            if (lCount == 0) {
tim@129: //	            	lColDefinitions = new ColDefinition[row.getNumColumns()];
tim@129: //	                // analyze cols of result set
tim@129: //	                SeColumnDefinition[] lCols = row.getColumns();
tim@129: //	                int j = 0;
tim@129: //	                for (SeColumnDefinition lCol : lCols) {
tim@129: //	                	lColDefinitions[j] = new ColDefinition(lCol.getName(), lCol.getType());
tim@129: //	                	j = j + 1;
tim@129: //	                }
tim@129: //	            }
tim@129: //	            short lNumCols = row.getNumColumns();
tim@129: //	            Row lBackingRow = new Row(lNumCols);
tim@129: //	            for (int i = 0; i < lNumCols; i++) {
tim@129: //	                lBackingRow.addObject(row.getObject(i), i);
tim@129: //	            }
tim@129: //	          
tim@129: //	            writer.writeRow(lBackingRow,lColDefinitions, false);
tim@129: //	        }
tim@129: //        } catch (IOException e) {
tim@129: //        	 sLogger.error(e.getMessage(), e);
tim@129: //        } catch (TechnicalException e) {
tim@129: //        	 sLogger.error(e.getMessage(), e);     
tim@129: //        }
tim@129: //        finally {
tim@129: //        	pSeQuery.close();
tim@129: //        	try{
tim@129: //        	writer.close();
tim@129: //        	}
tim@129: //        	catch (Exception e){
tim@129: //        		sLogger.error(e.getMessage(), e);
tim@129: //        	}
tim@129: //        }
tim@129: //        
tim@129: //        return lImageFile;
tim@129: //    }
tim@129: 
tim@129: }