Mercurial > dive4elements > gnv-client

view geo-backend/src/main/java/de/intevation/gnv/geobackend/sde/datasources/SDEQuery.java @ 143:122fdc9da5f0

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Unified changelog, removed some needless imports. xmllint-ed pom.xml geo-backend/trunk@196 c6561f87-3c4e-4783-a992-168aeb5c3f6f
author Sascha L. Teichmann <sascha.teichmann@intevation.de>
date Tue, 06 Oct 2009 14:45:50 +0000
parents e4eacd613356
children 210716612c30
line wrap: on
line source
/**
 * 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 java.sql.Connection;
import java.sql.Statement;

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.base.connectionpool.ConnectionPool;
import de.intevation.gnv.geobackend.base.connectionpool.exception.ConnectionException;
import de.intevation.gnv.geobackend.sde.datasources.exception.TechnicalException;

/**
 * // 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();
//            // 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 null;//handleResultSet(spatialQuery);

        } 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();
//            // 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 null;//handleResultSet(spatialQuery);

        } 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;
//    }

}
http://dive4elements.wald.intevation.org