Mercurial > dive4elements > gnv-client

comparison geo-backend/src/main/java/de/intevation/gnv/geobackend/sde/datasources/SDEQuery.java @ 129:110e3ac1b7d2

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
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
comparison
equal deleted inserted replaced
128:9b3f5a067c29 129:110e3ac1b7d2
1 /**
2 * 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 $
3 * Source: $Source: /share/gdi/SDI-Suite/Repository/projekte/BSH-GDI/genericViewer/src/main/java/de/conterra/bsh/gdi/gnviewer/datasources/sde/SDEQuery.java,v $
4 * created by: Stefan Blume (blume)
5 * erstellt am: 21.11.2007
6 * Copyright: con terra GmbH, 2005
7 *
8 * modified by: $Author: blume $
9 * modified on: $Date: 2008/01/30 12:38:34 $
10 * Version: $Revision: 1.3 $
11 * TAG: $Name: $
12 * locked from: $Locker: $
13 * CVS State: $State: Exp $
14 * Project: $ProjectName$
15 */
16 package de.intevation.gnv.geobackend.sde.datasources;
17
18 import org.apache.log4j.Logger;
19
20 import com.esri.sde.sdk.client.SDEPoint;
21 import com.esri.sde.sdk.client.SeColumnDefinition;
22 import com.esri.sde.sdk.client.SeConnection;
23 import com.esri.sde.sdk.client.SeCoordinateReference;
24 import com.esri.sde.sdk.client.SeException;
25 import com.esri.sde.sdk.client.SeFilter;
26 import com.esri.sde.sdk.client.SeLayer;
27 import com.esri.sde.sdk.client.SeQuery;
28 import com.esri.sde.sdk.client.SeRow;
29 import com.esri.sde.sdk.client.SeShape;
30 import com.esri.sde.sdk.client.SeShapeFilter;
31 import com.esri.sde.sdk.client.SeSqlConstruct;
32
33 import de.intevation.gnv.geobackend.sde.datasources.exception.TechnicalException;
34 import de.intevation.gnv.geobackend.util.DateUtils;
35
36 /**
37 * // todo: supply a general interface for Query-Objects (Query = read access to datasource implementation)
38 *
39 * @author blume
40 * @version 1.0
41 * @serial 1.0
42 * @see
43 * @since 21.11.2007 11:32:19
44 */
45 public class SDEQuery {
46
47 /**
48 * Default Logging instance
49 */
50 private static final Logger sLogger = Logger.getLogger(SDEQuery.class);
51 private static boolean sDebug = sLogger.isDebugEnabled();
52
53
54 DatasourceConnection mConnection;
55
56 public SDEQuery(DatasourceConnection pConnection) {
57 if (sDebug) sLogger.debug("SDEQuery()");
58 mConnection = pConnection;
59 }
60
61 /**
62 * Execute a query against a sde datasource.
63 * This Query method is responsible for getting and returning a valid {@link SeConnection}-Object.
64 *
65 * @param pTables
66 * @param pCols
67 * @param pWhere the where-clause to be executed.
68 * @return a ResultSet
69 * @see SeSqlConstruct
70 * @see SeQuery
71 */
72 public ResultSet executeQuery(String pTables[], String pCols[], String pWhere) throws TechnicalException {
73 sLogger.debug("executeQuery()");
74 SeConnection con = null;
75 try {
76 con = getConnection();
77 SeSqlConstruct lSeSqlConstruct = new SeSqlConstruct(pTables, pWhere);
78 SeQuery lSeQuery = new SeQuery(con, pCols, lSeSqlConstruct);
79 long lStart = System.currentTimeMillis();
80 lSeQuery.prepareQuery();
81 ResultSet lSet = handleResultSet(lSeQuery);
82 long lEnd = System.currentTimeMillis();
83 if (sDebug)
84 sLogger.debug((new StringBuilder()).append("lSeQuery lasts ").append((double) (lEnd - lStart) / 1000D).append(" seconds").toString());
85 return lSet;
86 } catch (TechnicalException e) {
87 sLogger.error(e.getMessage(), e);
88 throw e;
89 } catch (SeException e) {
90 sLogger.error(e.getMessage(), e);
91 throw new TechnicalException("Error during executeQuery", e);
92 } finally {
93 returnConnection(con);
94 }
95
96 }
97
98 /**
99 * Execute a query against a sde datasource.
100 * This Query method is responsible for getting and returning a valid {@link SeConnection}-Object.
101 *
102 * @param pSQLStatement the SQL-Statement to be executed.
103 * @return a ResultSet
104 * @see SeQuery
105 */
106 public ResultSet executeQuery(String pSQLStatement) throws TechnicalException {
107 if (sDebug) sLogger.debug("executeQuery():\n" + pSQLStatement);
108 SeConnection con = null;
109 try {
110 con = getConnection();
111
112 SeQuery lSeQuery = new SeQuery(con);
113 long lStart = System.currentTimeMillis();
114 lSeQuery.prepareSql(pSQLStatement);
115 lSeQuery.execute();
116
117 ResultSet lSet = handleResultSet(lSeQuery);
118
119 //TmpFile erstellen
120 //Row auslesen
121 //Row -> TmpFile
122 //TmpFile close
123
124 long lEnd = System.currentTimeMillis();
125 if (sDebug)
126 sLogger.debug((new StringBuilder()).append("lSeQuery lasts ").append((double) (lEnd - lStart) / 1000D).append(" seconds").toString());
127 return lSet;
128 } catch (TechnicalException e) {
129 sLogger.error(e.getMessage(), e);
130 throw e;
131 } catch (SeException e) {
132 sLogger.error(e.getMessage(), e);
133 throw new TechnicalException("Error during executeQuery", e);
134 } finally {
135 returnConnection(con);
136 }
137
138
139 }
140
141 // /**
142 // * Execute a query against a sde datasource.
143 // * This Query method is responsible for getting and returning a valid {@link SeConnection}-Object.
144 // *
145 // * @param pSQLStatement the SQL-Statement to be executed.
146 // * @return File
147 // * @see SeQuery
148 // */
149 // public TempFile executeQueryDiagram (String pSQLStatement) throws TechnicalException {
150 // if (sDebug) sLogger.debug("executeQueryDiagram():\n" + pSQLStatement);
151 // SeConnection con = null;
152 // TempFile lImageFile = null;
153 // try {
154 // con = getConnection();
155 //
156 // SeQuery lSeQuery = new SeQuery(con);
157 // long lStart = System.currentTimeMillis();
158 // lSeQuery.prepareSql(pSQLStatement);
159 // lSeQuery.execute();
160 //
161 // //ResultSet lSet = handleResultSet(lSeQuery);
162 // lImageFile = handleRowToCSVFile(lSeQuery);
163 //
164 //
165 // long lEnd = System.currentTimeMillis();
166 // if (sDebug)
167 // sLogger.debug((new StringBuilder()).append("lSeQuery lasts ").append((double) (lEnd - lStart) / 1000D).append(" seconds").toString());
168 //
169 // } catch (TechnicalException e) {
170 // sLogger.error(e.getMessage(), e);
171 // throw e;
172 // } catch (SeException e) {
173 // sLogger.error(e.getMessage(), e);
174 // throw new TechnicalException("Error during executeQuery", e);
175 // } finally {
176 // returnConnection(con);
177 // }
178 // return lImageFile;
179 //
180 // }
181
182 /**
183 * Execute a spatial query against a sde datasource.
184 * This Query method is responsible for getting and returning a valid {@link SeConnection}-Object.
185 * // todo: there is work needed to fully implement the wanted functionality.
186 *
187 * @param pLayername
188 * @param pSpatialColumnName
189 * @param pPoints
190 * @param pReturnFields
191 * @return a ResultSet
192 * @see SeShapeFilter
193 * @see SeQuery
194 */
195 public ResultSet executeQuery(String pLayername, String pSpatialColumnName, double[][] pPoints, String[] pReturnFields) throws TechnicalException {
196 sLogger.debug("executeQuery()");
197 SeConnection con = null;
198 try {
199 con = getConnection();
200 // get the layer for querying
201 SeLayer lLayer = new SeLayer(con, pLayername, pSpatialColumnName);
202 SeCoordinateReference cref = lLayer.getCoordRef();
203
204
205 SeShape shape = new SeShape();
206 shape.setCoordRef(lLayer.getCoordRef());
207 SDEPoint[] lPoints = ArcSDEUtils.createPoints(pPoints);
208
209 /*
210 * int numPts, int numParts, int[] partOffsets,SDEPoint[] ptArray
211 * */
212 shape.generatePolygon(lPoints.length, 1, null, lPoints);
213 SeShapeFilter filter = new SeShapeFilter(pLayername, pSpatialColumnName, shape, SeFilter.METHOD_AI);
214 SeShapeFilter[] filters = new SeShapeFilter[1];
215 filters[0] = filter;
216
217 SeQuery spatialQuery = null;
218 SeSqlConstruct sqlCons = new SeSqlConstruct(pLayername);
219 spatialQuery = new SeQuery(con, pReturnFields, sqlCons);
220 spatialQuery.prepareQuery();
221 /*
222 * Set spatial constraints
223 */
224 spatialQuery.setSpatialConstraints(SeQuery.SE_OPTIMIZE, false, filters);
225 spatialQuery.execute();
226
227 return handleResultSet(spatialQuery);
228
229 } catch (TechnicalException e) {
230 sLogger.error(e.getMessage(), e);
231 throw e;
232 } catch (SeException e) {
233 sLogger.error(e.getMessage(), e);
234 throw new TechnicalException("Error accessing Spatial Database: " + e.getMessage(), e);
235 } finally {
236 returnConnection(con);
237 }
238 }
239 /**
240 * Execute a spatial query against a sde datasource.
241 * This Query method is responsible for getting and returning a valid {@link SeConnection}-Object.
242 * // todo: there is work needed to fully implement the wanted functionality.
243 *
244 * @param pLayername
245 * @param pSpatialColumnName
246 * @param pPoints
247 * @param pReturnFields
248 * @return a ResultSet
249 * @see SeShapeFilter
250 * @see SeQuery
251 */
252 public ResultSet executeQuery(String pLayername, String pSpatialColumnName, String pWhere, double[][] pPoints, String[] pReturnFields) throws TechnicalException {
253 sLogger.debug("executeQuery()");
254 SeConnection con = null;
255 try {
256 con = getConnection();
257 // get the layer for querying
258 SeLayer lLayer = new SeLayer(con, pLayername, pSpatialColumnName);
259 SeCoordinateReference cref = lLayer.getCoordRef();
260
261
262 SeShape shape = new SeShape();
263 shape.setCoordRef(lLayer.getCoordRef());
264 SDEPoint[] lPoints = ArcSDEUtils.createPoints(pPoints);
265
266 /*
267 * int numPts, int numParts, int[] partOffsets,SDEPoint[] ptArray
268 * */
269 shape.generatePolygon(lPoints.length, 1, null, lPoints);
270 SeShapeFilter filter = new SeShapeFilter(pLayername, pSpatialColumnName, shape, SeFilter.METHOD_AI);
271 SeShapeFilter[] filters = new SeShapeFilter[1];
272 filters[0] = filter;
273
274 SeQuery spatialQuery = null;
275 SeSqlConstruct sqlCons = new SeSqlConstruct(pLayername,pWhere);
276 spatialQuery = new SeQuery(con, pReturnFields, sqlCons);
277 spatialQuery.prepareQuery();
278 /*
279 * Set spatial constraints
280 */
281 spatialQuery.setSpatialConstraints(SeQuery.SE_OPTIMIZE, false, filters);
282 spatialQuery.execute();
283
284 return handleResultSet(spatialQuery);
285
286 } catch (TechnicalException e) {
287 sLogger.error(e.getMessage(), e);
288 throw e;
289 } catch (SeException e) {
290 sLogger.error(e.getMessage(), e);
291 throw new TechnicalException("Error accessing Spatial Database: " + e.getMessage(), e);
292 } finally {
293 returnConnection(con);
294 }
295 }
296 private SeConnection getConnection() throws TechnicalException {
297 SeConnection lConnection = ((SDEConnection) mConnection).getConnection();
298 if (sDebug) try {
299 sLogger.debug("get connection to server: " + lConnection.getServer() + ", ServerTime: " + DateUtils.getPatternedDateAmer(lConnection.getServerTime()) + ", Release: " + lConnection.getRelease().getDesc());
300 } catch (SeException e) {
301 sLogger.error(e.getMessage(), e);
302 }
303 return lConnection;
304 }
305
306 private void returnConnection(SeConnection pSeConnection) {
307 ((SDEConnection) mConnection).returnConnection(pSeConnection);
308 }
309
310
311 private ResultSet handleResultSet(SeQuery pSeQuery) throws SeException {
312 sLogger.debug("handleResultSet()");
313 SDEResultSet lSet = new SDEResultSet();
314 SeRow row;
315 int lCount;
316 for (lCount = 0; (row = pSeQuery.fetch()) != null; lCount++) {
317 // one time execution
318 if (lCount == 0) {
319 // analyze cols of result set
320 SeColumnDefinition[] lCols = row.getColumns();
321 for (SeColumnDefinition lCol : lCols) {
322 lSet.addCol(new ColDefinition(lCol.getName(), lCol.getType()));// notice: esri-types have been copied into colDefinition class!
323 }
324 }
325 short lNumCols = row.getNumColumns();
326 Row lBackingRow = new Row(lNumCols);
327 for (int i = 0; i < lNumCols; i++) {
328 lBackingRow.addObject(row.getObject(i), i);
329 }
330 lSet.addRow(lBackingRow);
331 }
332 pSeQuery.close();
333 return lSet;
334 }
335
336 // private TempFile handleRowToCSVFile(SeQuery pSeQuery) throws SeException {
337 // sLogger.debug("handleRowToTempFile()");
338 // TempFile lImageFile = TemporaryFileDirectory.getInstance().createFile(".csv");
339 // sLogger.debug("handleRowToTempFile() erstellen " + lImageFile.getFile().getName());
340 // CSVWriter writer = null;
341 // ColDefinition[] lColDefinitions = null;
342 //
343 // SeRow row;
344 // int lCount;
345 // try {
346 // writer = new CSVWriter(new FileWriter(lImageFile.getFile()), ';');
347 // for (lCount = 0; (row = pSeQuery.fetch()) != null; lCount++) {
348 // // one time execution
349 // if (lCount == 0) {
350 // lColDefinitions = new ColDefinition[row.getNumColumns()];
351 // // analyze cols of result set
352 // SeColumnDefinition[] lCols = row.getColumns();
353 // int j = 0;
354 // for (SeColumnDefinition lCol : lCols) {
355 // lColDefinitions[j] = new ColDefinition(lCol.getName(), lCol.getType());
356 // j = j + 1;
357 // }
358 // }
359 // short lNumCols = row.getNumColumns();
360 // Row lBackingRow = new Row(lNumCols);
361 // for (int i = 0; i < lNumCols; i++) {
362 // lBackingRow.addObject(row.getObject(i), i);
363 // }
364 //
365 // writer.writeRow(lBackingRow,lColDefinitions, false);
366 // }
367 // } catch (IOException e) {
368 // sLogger.error(e.getMessage(), e);
369 // } catch (TechnicalException e) {
370 // sLogger.error(e.getMessage(), e);
371 // }
372 // finally {
373 // pSeQuery.close();
374 // try{
375 // writer.close();
376 // }
377 // catch (Exception e){
378 // sLogger.error(e.getMessage(), e);
379 // }
380 // }
381 //
382 // return lImageFile;
383 // }
384
385 }
http://dive4elements.wald.intevation.org