Mercurial > dive4elements > gnv-client

comparison geo-backend/src/main/java/de/intevation/gnv/geobackend/sde/datasources/SDEQuery.java @ 130:e4eacd613356

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Implementierung Datenzugriff auf die ArcSDE über java.sql. Methodiken ChangeLog wird nachgereicht da SubversionClientincompatiblitäten vorhanden sind. geo-backend/trunk@7 c6561f87-3c4e-4783-a992-168aeb5c3f6f
author Tim Englich <tim.englich@intevation.de>
date Wed, 02 Sep 2009 15:15:52 +0000
parents 110e3ac1b7d2
children 122fdc9da5f0
comparison
equal deleted inserted replaced
129:110e3ac1b7d2 130:e4eacd613356
13 * CVS State: $State: Exp $ 13 * CVS State: $State: Exp $
14 * Project: $ProjectName$ 14 * Project: $ProjectName$
15 */ 15 */
16 package de.intevation.gnv.geobackend.sde.datasources; 16 package de.intevation.gnv.geobackend.sde.datasources;
17 17
18 import java.sql.Connection;
19 import java.sql.Statement;
20
18 import org.apache.log4j.Logger; 21 import org.apache.log4j.Logger;
19 22
20 import com.esri.sde.sdk.client.SDEPoint; 23 import com.esri.sde.sdk.client.SDEPoint;
21 import com.esri.sde.sdk.client.SeColumnDefinition; 24 import com.esri.sde.sdk.client.SeColumnDefinition;
22 import com.esri.sde.sdk.client.SeConnection; 25 import com.esri.sde.sdk.client.SeConnection;
28 import com.esri.sde.sdk.client.SeRow; 31 import com.esri.sde.sdk.client.SeRow;
29 import com.esri.sde.sdk.client.SeShape; 32 import com.esri.sde.sdk.client.SeShape;
30 import com.esri.sde.sdk.client.SeShapeFilter; 33 import com.esri.sde.sdk.client.SeShapeFilter;
31 import com.esri.sde.sdk.client.SeSqlConstruct; 34 import com.esri.sde.sdk.client.SeSqlConstruct;
32 35
36 import de.intevation.gnv.geobackend.base.connectionpool.ConnectionPool;
37 import de.intevation.gnv.geobackend.base.connectionpool.exception.ConnectionException;
33 import de.intevation.gnv.geobackend.sde.datasources.exception.TechnicalException; 38 import de.intevation.gnv.geobackend.sde.datasources.exception.TechnicalException;
34 import de.intevation.gnv.geobackend.util.DateUtils; 39 import de.intevation.gnv.geobackend.util.DateUtils;
35 40
36 /** 41 /**
37 * // todo: supply a general interface for Query-Objects (Query = read access to datasource implementation) 42 * // todo: supply a general interface for Query-Objects (Query = read access to datasource implementation)
49 */ 54 */
50 private static final Logger sLogger = Logger.getLogger(SDEQuery.class); 55 private static final Logger sLogger = Logger.getLogger(SDEQuery.class);
51 private static boolean sDebug = sLogger.isDebugEnabled(); 56 private static boolean sDebug = sLogger.isDebugEnabled();
52 57
53 58
54 DatasourceConnection mConnection; 59 private ConnectionPool connectionPool = null;
55 60 private String connectionID = "N/N";
56 public SDEQuery(DatasourceConnection pConnection) { 61
62 public SDEQuery(ConnectionPool connectionPool) {
57 if (sDebug) sLogger.debug("SDEQuery()"); 63 if (sDebug) sLogger.debug("SDEQuery()");
58 mConnection = pConnection; 64 this.connectionPool = connectionPool;
59 } 65 }
60 66
61 /** 67 /**
62 * Execute a query against a sde datasource. 68 * Execute a query against a sde datasource.
63 * This Query method is responsible for getting and returning a valid {@link SeConnection}-Object. 69 * This Query method is responsible for getting and returning a valid {@link SeConnection}-Object.
69 * @see SeSqlConstruct 75 * @see SeSqlConstruct
70 * @see SeQuery 76 * @see SeQuery
71 */ 77 */
72 public ResultSet executeQuery(String pTables[], String pCols[], String pWhere) throws TechnicalException { 78 public ResultSet executeQuery(String pTables[], String pCols[], String pWhere) throws TechnicalException {
73 sLogger.debug("executeQuery()"); 79 sLogger.debug("executeQuery()");
74 SeConnection con = null; 80 Connection con = null;
75 try { 81 try {
76 con = getConnection(); 82 con = getConnection();
77 SeSqlConstruct lSeSqlConstruct = new SeSqlConstruct(pTables, pWhere); 83 // SeSqlConstruct lSeSqlConstruct = new SeSqlConstruct(pTables, pWhere);
78 SeQuery lSeQuery = new SeQuery(con, pCols, lSeSqlConstruct); 84 // SeQuery lSeQuery = new SeQuery(con, pCols, lSeSqlConstruct);
79 long lStart = System.currentTimeMillis(); 85 // long lStart = System.currentTimeMillis();
80 lSeQuery.prepareQuery(); 86 // lSeQuery.prepareQuery();
81 ResultSet lSet = handleResultSet(lSeQuery); 87 ResultSet lSet = null;//handleResultSet(lSeQuery);
82 long lEnd = System.currentTimeMillis(); 88 // long lEnd = System.currentTimeMillis();
83 if (sDebug) 89 // if (sDebug)
84 sLogger.debug((new StringBuilder()).append("lSeQuery lasts ").append((double) (lEnd - lStart) / 1000D).append(" seconds").toString()); 90 // sLogger.debug((new StringBuilder()).append("lSeQuery lasts ").append((double) (lEnd - lStart) / 1000D).append(" seconds").toString());
85 return lSet; 91 return lSet;
86 } catch (TechnicalException e) { 92 } catch (TechnicalException e) {
87 sLogger.error(e.getMessage(), e); 93 sLogger.error(e.getMessage(), e);
88 throw e; 94 throw e;
89 } catch (SeException e) { 95 } catch (Exception e) {
90 sLogger.error(e.getMessage(), e); 96 sLogger.error(e.getMessage(), e);
91 throw new TechnicalException("Error during executeQuery", e); 97 throw new TechnicalException("Error during executeQuery", e);
92 } finally { 98 } finally {
93 returnConnection(con); 99 returnConnection(con);
94 } 100 }
103 * @return a ResultSet 109 * @return a ResultSet
104 * @see SeQuery 110 * @see SeQuery
105 */ 111 */
106 public ResultSet executeQuery(String pSQLStatement) throws TechnicalException { 112 public ResultSet executeQuery(String pSQLStatement) throws TechnicalException {
107 if (sDebug) sLogger.debug("executeQuery():\n" + pSQLStatement); 113 if (sDebug) sLogger.debug("executeQuery():\n" + pSQLStatement);
108 SeConnection con = null; 114 Connection con = null;
109 try { 115 try {
110 con = getConnection(); 116 con = getConnection();
111 117
112 SeQuery lSeQuery = new SeQuery(con); 118
113 long lStart = System.currentTimeMillis(); 119 long lStart = System.currentTimeMillis();
114 lSeQuery.prepareSql(pSQLStatement);
115 lSeQuery.execute();
116
117 ResultSet lSet = handleResultSet(lSeQuery);
118 120
119 //TmpFile erstellen 121 Statement stmt = con.createStatement();
120 //Row auslesen 122 java.sql.ResultSet rs = stmt.executeQuery(pSQLStatement);
121 //Row -> TmpFile 123
122 //TmpFile close
123
124 long lEnd = System.currentTimeMillis(); 124 long lEnd = System.currentTimeMillis();
125 if (sDebug) 125 if (sDebug)
126 sLogger.debug((new StringBuilder()).append("lSeQuery lasts ").append((double) (lEnd - lStart) / 1000D).append(" seconds").toString()); 126 sLogger.debug((new StringBuilder()).append("lSeQuery lasts ").append((double) (lEnd - lStart) / 1000D).append(" seconds").toString());
127 return lSet; 127 return (ResultSet)rs;
128 } catch (TechnicalException e) { 128 } catch (TechnicalException e) {
129 sLogger.error(e.getMessage(), e); 129 sLogger.error(e.getMessage(), e);
130 throw e; 130 throw e;
131 } catch (SeException e) { 131 } catch (Exception e) {
132 sLogger.error(e.getMessage(), e); 132 sLogger.error(e.getMessage(), e);
133 throw new TechnicalException("Error during executeQuery", e); 133 throw new TechnicalException("Error during executeQuery", e);
134 } finally { 134 } finally {
135 returnConnection(con); 135 returnConnection(con);
136 } 136 }
192 * @see SeShapeFilter 192 * @see SeShapeFilter
193 * @see SeQuery 193 * @see SeQuery
194 */ 194 */
195 public ResultSet executeQuery(String pLayername, String pSpatialColumnName, double[][] pPoints, String[] pReturnFields) throws TechnicalException { 195 public ResultSet executeQuery(String pLayername, String pSpatialColumnName, double[][] pPoints, String[] pReturnFields) throws TechnicalException {
196 sLogger.debug("executeQuery()"); 196 sLogger.debug("executeQuery()");
197 SeConnection con = null; 197 Connection con = null;
198 try { 198 try {
199 con = getConnection(); 199 con = getConnection();
200 // get the layer for querying 200 // // get the layer for querying
201 SeLayer lLayer = new SeLayer(con, pLayername, pSpatialColumnName); 201 // SeLayer lLayer = new SeLayer(con, pLayername, pSpatialColumnName);
202 SeCoordinateReference cref = lLayer.getCoordRef(); 202 // SeCoordinateReference cref = lLayer.getCoordRef();
203 203 //
204 204 //
205 SeShape shape = new SeShape(); 205 // SeShape shape = new SeShape();
206 shape.setCoordRef(lLayer.getCoordRef()); 206 // shape.setCoordRef(lLayer.getCoordRef());
207 SDEPoint[] lPoints = ArcSDEUtils.createPoints(pPoints); 207 // SDEPoint[] lPoints = ArcSDEUtils.createPoints(pPoints);
208 208 //
209 /* 209 // /*
210 * int numPts, int numParts, int[] partOffsets,SDEPoint[] ptArray 210 // * int numPts, int numParts, int[] partOffsets,SDEPoint[] ptArray
211 * */ 211 // * */
212 shape.generatePolygon(lPoints.length, 1, null, lPoints); 212 // shape.generatePolygon(lPoints.length, 1, null, lPoints);
213 SeShapeFilter filter = new SeShapeFilter(pLayername, pSpatialColumnName, shape, SeFilter.METHOD_AI); 213 // SeShapeFilter filter = new SeShapeFilter(pLayername, pSpatialColumnName, shape, SeFilter.METHOD_AI);
214 SeShapeFilter[] filters = new SeShapeFilter[1]; 214 // SeShapeFilter[] filters = new SeShapeFilter[1];
215 filters[0] = filter; 215 // filters[0] = filter;
216 216 //
217 SeQuery spatialQuery = null; 217 // SeQuery spatialQuery = null;
218 SeSqlConstruct sqlCons = new SeSqlConstruct(pLayername); 218 // SeSqlConstruct sqlCons = new SeSqlConstruct(pLayername);
219 spatialQuery = new SeQuery(con, pReturnFields, sqlCons); 219 // spatialQuery = new SeQuery(con, pReturnFields, sqlCons);
220 spatialQuery.prepareQuery(); 220 // spatialQuery.prepareQuery();
221 /* 221 // /*
222 * Set spatial constraints 222 // * Set spatial constraints
223 */ 223 // */
224 spatialQuery.setSpatialConstraints(SeQuery.SE_OPTIMIZE, false, filters); 224 // spatialQuery.setSpatialConstraints(SeQuery.SE_OPTIMIZE, false, filters);
225 spatialQuery.execute(); 225 // spatialQuery.execute();
226 226
227 return handleResultSet(spatialQuery); 227 return null;//handleResultSet(spatialQuery);
228 228
229 } catch (TechnicalException e) { 229 } catch (TechnicalException e) {
230 sLogger.error(e.getMessage(), e); 230 sLogger.error(e.getMessage(), e);
231 throw e; 231 throw e;
232 } catch (SeException e) { 232 } catch (Exception e) {
233 sLogger.error(e.getMessage(), e); 233 sLogger.error(e.getMessage(), e);
234 throw new TechnicalException("Error accessing Spatial Database: " + e.getMessage(), e); 234 throw new TechnicalException("Error accessing Spatial Database: " + e.getMessage(), e);
235 } finally { 235 } finally {
236 returnConnection(con); 236 returnConnection(con);
237 } 237 }
249 * @see SeShapeFilter 249 * @see SeShapeFilter
250 * @see SeQuery 250 * @see SeQuery
251 */ 251 */
252 public ResultSet executeQuery(String pLayername, String pSpatialColumnName, String pWhere, double[][] pPoints, String[] pReturnFields) throws TechnicalException { 252 public ResultSet executeQuery(String pLayername, String pSpatialColumnName, String pWhere, double[][] pPoints, String[] pReturnFields) throws TechnicalException {
253 sLogger.debug("executeQuery()"); 253 sLogger.debug("executeQuery()");
254 SeConnection con = null; 254 Connection con = null;
255 try { 255 try {
256 con = getConnection(); 256 con = getConnection();
257 // get the layer for querying 257 // // get the layer for querying
258 SeLayer lLayer = new SeLayer(con, pLayername, pSpatialColumnName); 258 // SeLayer lLayer = new SeLayer(con, pLayername, pSpatialColumnName);
259 SeCoordinateReference cref = lLayer.getCoordRef(); 259 // SeCoordinateReference cref = lLayer.getCoordRef();
260 260 //
261 261 //
262 SeShape shape = new SeShape(); 262 // SeShape shape = new SeShape();
263 shape.setCoordRef(lLayer.getCoordRef()); 263 // shape.setCoordRef(lLayer.getCoordRef());
264 SDEPoint[] lPoints = ArcSDEUtils.createPoints(pPoints); 264 // SDEPoint[] lPoints = ArcSDEUtils.createPoints(pPoints);
265 265 //
266 /* 266 // /*
267 * int numPts, int numParts, int[] partOffsets,SDEPoint[] ptArray 267 // * int numPts, int numParts, int[] partOffsets,SDEPoint[] ptArray
268 * */ 268 // * */
269 shape.generatePolygon(lPoints.length, 1, null, lPoints); 269 // shape.generatePolygon(lPoints.length, 1, null, lPoints);
270 SeShapeFilter filter = new SeShapeFilter(pLayername, pSpatialColumnName, shape, SeFilter.METHOD_AI); 270 // SeShapeFilter filter = new SeShapeFilter(pLayername, pSpatialColumnName, shape, SeFilter.METHOD_AI);
271 SeShapeFilter[] filters = new SeShapeFilter[1]; 271 // SeShapeFilter[] filters = new SeShapeFilter[1];
272 filters[0] = filter; 272 // filters[0] = filter;
273 273 //
274 SeQuery spatialQuery = null; 274 // SeQuery spatialQuery = null;
275 SeSqlConstruct sqlCons = new SeSqlConstruct(pLayername,pWhere); 275 // SeSqlConstruct sqlCons = new SeSqlConstruct(pLayername,pWhere);
276 spatialQuery = new SeQuery(con, pReturnFields, sqlCons); 276 // spatialQuery = new SeQuery(con, pReturnFields, sqlCons);
277 spatialQuery.prepareQuery(); 277 // spatialQuery.prepareQuery();
278 /* 278 // /*
279 * Set spatial constraints 279 // * Set spatial constraints
280 */ 280 // */
281 spatialQuery.setSpatialConstraints(SeQuery.SE_OPTIMIZE, false, filters); 281 // spatialQuery.setSpatialConstraints(SeQuery.SE_OPTIMIZE, false, filters);
282 spatialQuery.execute(); 282 // spatialQuery.execute();
283 283
284 return handleResultSet(spatialQuery); 284 return null;//handleResultSet(spatialQuery);
285 285
286 } catch (TechnicalException e) { 286 } catch (TechnicalException e) {
287 sLogger.error(e.getMessage(), e); 287 sLogger.error(e.getMessage(), e);
288 throw e; 288 throw e;
289 } catch (SeException e) { 289 } catch (Exception e) {
290 sLogger.error(e.getMessage(), e); 290 sLogger.error(e.getMessage(), e);
291 throw new TechnicalException("Error accessing Spatial Database: " + e.getMessage(), e); 291 throw new TechnicalException("Error accessing Spatial Database: " + e.getMessage(), e);
292 } finally { 292 } finally {
293 returnConnection(con); 293 returnConnection(con);
294 } 294 }
295 } 295 }
296 private SeConnection getConnection() throws TechnicalException { 296 private Connection getConnection() throws TechnicalException {
297 SeConnection lConnection = ((SDEConnection) mConnection).getConnection(); 297 Connection connection = connectionPool.getConnection(connectionID);
298 if (sDebug) try { 298 return connection;
299 sLogger.debug("get connection to server: " + lConnection.getServer() + ", ServerTime: " + DateUtils.getPatternedDateAmer(lConnection.getServerTime()) + ", Release: " + lConnection.getRelease().getDesc()); 299 }
300 } catch (SeException e) { 300
301 sLogger.error(e.getMessage(), e); 301 private void returnConnection(Connection connection) {
302 } 302 try {
303 return lConnection; 303 this.connectionPool.closeConnection(connection);
304 } 304 } catch (ConnectionException e) {
305 305 sLogger.error(e,e);
306 private void returnConnection(SeConnection pSeConnection) { 306 }
307 ((SDEConnection) mConnection).returnConnection(pSeConnection);
308 } 307 }
309 308
310 309
311 private ResultSet handleResultSet(SeQuery pSeQuery) throws SeException { 310 private ResultSet handleResultSet(SeQuery pSeQuery) throws SeException {
312 sLogger.debug("handleResultSet()"); 311 sLogger.debug("handleResultSet()");
http://dive4elements.wald.intevation.org