Mercurial > dive4elements > gnv-client
comparison 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 |
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 } |