Mercurial > dive4elements > gnv-client
diff 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 diff
--- /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; +// } + +}