Mercurial > dive4elements > gnv-client
view geo-backend/src/main/java/de/intevation/gnv/geobackend/sde/datasources/SDEQuery.java @ 129:110e3ac1b7d2
Library Dependencies Added to pom.xml-File
Import of SDE-Datasources
geo-backend/trunk@5 c6561f87-3c4e-4783-a992-168aeb5c3f6f
author | Tim Englich <tim.englich@intevation.de> |
---|---|
date | Wed, 02 Sep 2009 09:07:03 +0000 |
parents | |
children | e4eacd613356 |
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 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; // } }