--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/geo-backend/src/main/java/de/intevation/gnv/geobackend/sde/datasources/SDEQuery.java	Wed Sep 02 09:07:03 2009 +0000
@@ -0,0 +1,385 @@
+/**
+ * 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 org.apache.log4j.Logger;
+
+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.SeCoordinateReference;
+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.SeRow;
+import com.esri.sde.sdk.client.SeShape;
+import com.esri.sde.sdk.client.SeShapeFilter;
+import com.esri.sde.sdk.client.SeSqlConstruct;
+
+import de.intevation.gnv.geobackend.sde.datasources.exception.TechnicalException;
+import de.intevation.gnv.geobackend.util.DateUtils;
+
+/**
+ * // 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();
+
+
+    DatasourceConnection mConnection;
+
+    public SDEQuery(DatasourceConnection pConnection) {
+        if (sDebug) sLogger.debug("SDEQuery()");
+        mConnection = pConnection;
+    }
+
+    /**
+     * 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()");
+        SeConnection 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 = 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 (SeException 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);
+        SeConnection con = null;
+        try {
+            con = getConnection();
+
+            SeQuery lSeQuery = new SeQuery(con);
+            long lStart = System.currentTimeMillis();
+            lSeQuery.prepareSql(pSQLStatement);
+            lSeQuery.execute();
+
+            ResultSet lSet = handleResultSet(lSeQuery);
+            
+            //TmpFile erstellen
+            //Row auslesen
+            //Row -> TmpFile
+            //TmpFile close
+
+            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 (SeException 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()");
+        SeConnection con = null;
+        try {
+            con = getConnection();
+            // get the layer for querying
+            SeLayer lLayer = new SeLayer(con, pLayername, pSpatialColumnName);
+            SeCoordinateReference cref = lLayer.getCoordRef();
+
+
+            SeShape shape = new SeShape();
+            shape.setCoordRef(lLayer.getCoordRef());
+            SDEPoint[] lPoints = ArcSDEUtils.createPoints(pPoints);
+
+            /*
+            * int numPts, int numParts, int[] partOffsets,SDEPoint[] ptArray
+            * */
+            shape.generatePolygon(lPoints.length, 1, null, lPoints);
+            SeShapeFilter filter = new SeShapeFilter(pLayername, pSpatialColumnName, shape, SeFilter.METHOD_AI);
+            SeShapeFilter[] filters = new SeShapeFilter[1];
+            filters[0] = filter;
+
+            SeQuery spatialQuery = null;
+            SeSqlConstruct sqlCons = new SeSqlConstruct(pLayername);
+            spatialQuery = new SeQuery(con, pReturnFields, sqlCons);
+            spatialQuery.prepareQuery();
+            /*
+            *   Set spatial constraints
+            */
+            spatialQuery.setSpatialConstraints(SeQuery.SE_OPTIMIZE, false, filters);
+            spatialQuery.execute();
+
+            return handleResultSet(spatialQuery);
+
+        } catch (TechnicalException e) {
+            sLogger.error(e.getMessage(), e);
+            throw e;
+        } catch (SeException 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()");
+        SeConnection con = null;
+        try {
+            con = getConnection();
+            // get the layer for querying
+            SeLayer lLayer = new SeLayer(con, pLayername, pSpatialColumnName);
+            SeCoordinateReference cref = lLayer.getCoordRef();
+
+
+            SeShape shape = new SeShape();
+            shape.setCoordRef(lLayer.getCoordRef());
+            SDEPoint[] lPoints = ArcSDEUtils.createPoints(pPoints);
+
+            /*
+            * int numPts, int numParts, int[] partOffsets,SDEPoint[] ptArray
+            * */
+            shape.generatePolygon(lPoints.length, 1, null, lPoints);
+            SeShapeFilter filter = new SeShapeFilter(pLayername, pSpatialColumnName, shape, SeFilter.METHOD_AI);
+            SeShapeFilter[] filters = new SeShapeFilter[1];
+            filters[0] = filter;
+
+            SeQuery spatialQuery = null;
+            SeSqlConstruct sqlCons = new SeSqlConstruct(pLayername,pWhere);
+            spatialQuery = new SeQuery(con, pReturnFields, sqlCons);
+            spatialQuery.prepareQuery();
+            /*
+            *   Set spatial constraints
+            */
+            spatialQuery.setSpatialConstraints(SeQuery.SE_OPTIMIZE, false, filters);
+            spatialQuery.execute();
+
+            return handleResultSet(spatialQuery);
+
+        } catch (TechnicalException e) {
+            sLogger.error(e.getMessage(), e);
+            throw e;
+        } catch (SeException e) {
+            sLogger.error(e.getMessage(), e);
+            throw new TechnicalException("Error accessing Spatial Database: " + e.getMessage(), e);
+        } finally {
+            returnConnection(con);
+        }
+    }
+    private SeConnection getConnection() throws TechnicalException {
+        SeConnection lConnection = ((SDEConnection) mConnection).getConnection();
+        if (sDebug) try {
+            sLogger.debug("get connection to server: " + lConnection.getServer() + ", ServerTime: " + DateUtils.getPatternedDateAmer(lConnection.getServerTime()) + ", Release: " + lConnection.getRelease().getDesc());
+        } catch (SeException e) {
+            sLogger.error(e.getMessage(), e);
+        }
+        return lConnection;
+    }
+
+    private void returnConnection(SeConnection pSeConnection) {
+        ((SDEConnection) mConnection).returnConnection(pSeConnection);
+    }
+
+
+    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;
+//    }
+
+}