Mercurial > dive4elements > gnv-client
view geo-backend/src/main/java/de/intevation/gnv/geobackend/sde/datasources/ArcSDEStatement.java @ 275:9063c5fcebf7
Added the possibility to use Innerselects in SpatialQueries
geo-backend/trunk@286 c6561f87-3c4e-4783-a992-168aeb5c3f6f
author | Tim Englich <tim.englich@intevation.de> |
---|---|
date | Thu, 05 Nov 2009 15:03:51 +0000 |
parents | ff1b7967e6b9 |
children | f304f2f12db4 |
line wrap: on
line source
/** * */ package de.intevation.gnv.geobackend.sde.datasources; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.SQLWarning; import java.sql.Statement; 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.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.SeQueryInfo; 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 com.vividsolutions.jts.geom.Geometry; import com.vividsolutions.jts.io.WKTReader; /** * @author Tim Englich <tim.englich@intevation.de> * */ public class ArcSDEStatement implements Statement { /** * the logger, used to log exceptions and additonaly information */ private static Logger log = Logger.getLogger(ArcSDEStatement.class); private ArcSDEConnection connection = null; /** * Constructor */ public ArcSDEStatement(ArcSDEConnection connection) { this.connection = connection; } /** * @see java.sql.Statement#addBatch(java.lang.String) */ public void addBatch(String arg0) throws SQLException { } /** * @see java.sql.Statement#cancel() */ public void cancel() throws SQLException { } /** * @see java.sql.Statement#clearBatch() */ public void clearBatch() throws SQLException { } /** * @see java.sql.Statement#clearWarnings() */ public void clearWarnings() throws SQLException { } /** * @see java.sql.Statement#close() */ public void close() throws SQLException { } /** * @see java.sql.Statement#execute(java.lang.String) */ public boolean execute(String arg0) throws SQLException { return false; } /** * @see java.sql.Statement#execute(java.lang.String, int) */ public boolean execute(String arg0, int arg1) throws SQLException { return false; } /** * @see java.sql.Statement#execute(java.lang.String, int[]) */ public boolean execute(String arg0, int[] arg1) throws SQLException { return false; } /** * @see java.sql.Statement#execute(java.lang.String, java.lang.String[]) */ public boolean execute(String arg0, String[] arg1) throws SQLException { return false; } /** * @see java.sql.Statement#executeBatch() */ public int[] executeBatch() throws SQLException { return null; } /** * @see java.sql.Statement#executeQuery(java.lang.String) */ public ResultSet executeQuery(String statement) throws SQLException { try { SeQuery query = null; if (statement.toLowerCase().contains("st_astext") || statement.toLowerCase().contains("intersects")){ String[] values = statement.toLowerCase().split("where", 2); String where = values.length > 1 ? values[1].trim() : ""; values = values[0].split("from", 2); String[] tableNames = values[1].toUpperCase().trim().split(","); String[] returnFields = values[0].replaceAll("select", "").trim().split(","); String geometryColumnName = null; String byClause = null; int byClausePos = where.indexOf("group by"); if (byClausePos < 0){ byClausePos = where.indexOf("order by"); } if (byClausePos > 0){ byClause = where.substring(byClausePos); where = where.substring(0,byClausePos); } for (int i = 0; i < returnFields.length; i++){ returnFields[i] = returnFields[i].trim(); if (returnFields[i].startsWith("st_astext(")){ returnFields[i] = returnFields[i].replaceAll("st_astext", ""); returnFields[i] = returnFields[i].substring(1, returnFields[i].length()-1); geometryColumnName = returnFields[i]; } } for (int i = 0; i < tableNames.length; i++){ tableNames[i] = tableNames[i].trim(); } Geometry g = null; int pos = where.indexOf("intersects"); if (pos > 0 ){ String substr = where.substring(pos); where = where.substring(0,where.lastIndexOf("intersects")); // TODO auch or unterst�tzen where = where.substring(0,where.lastIndexOf("and")); // TODO auch or unterst�tzen String intersectsStmt = substr.substring(0, substr.lastIndexOf(")")); // Annahme r�umliches Stmt steht am Ende String wkt = null; if (intersectsStmt.contains("select")){ // Anstelle eines WKT ist ein InnerSelect zum Bestimmen der Comparatorgeometrie gegeben. String stmt = intersectsStmt.substring(intersectsStmt.indexOf("select"),intersectsStmt.lastIndexOf(")")); ResultSet resultSet = this.executeQuery(stmt); if (resultSet != null && resultSet.next()){ // TODO Selektion von mehreren Geometrien unterst�tzen. wkt = resultSet.getString("SHAPE"); } }else{ wkt = intersectsStmt.substring(intersectsStmt.indexOf("\""), intersectsStmt.lastIndexOf("\"")).replace("\"", "").trim(); } g = new WKTReader().read(wkt); if (geometryColumnName == null){ geometryColumnName = "SHAPE"; // TODO dynamisch aus Intersects auslesen. } } return this.executeQuery(this.connection.getSeConnection(), tableNames, geometryColumnName, where, g, returnFields,byClause); }else{ query = new SeQuery(this.connection.getSeConnection()); query.prepareSql(statement); query.execute(); return this.handleResultSet(query); } } catch (Exception e) { log.error(e,e); throw new SQLException(e.getMessage()); } } private ResultSet executeQuery(SeConnection con, String[] pLayername, String pSpatialColumnName, String pWhere, Geometry g, String[] pReturnFields, String byClause) throws SQLException { log.debug("executeQuery()"); try { // get the layer for querying SeShapeFilter[] filters = null; if (g != null){ SeLayer lLayer = new SeLayer(con, pLayername[0], pSpatialColumnName); SeShape shape = new SeShape(); shape.setCoordRef(lLayer.getCoordRef()); SDEPoint[] lPoints = new ArcSDEUtils().createPoints(g); shape.generatePolygon(lPoints.length, 1, null, lPoints); SeShapeFilter filter = new SeShapeFilter(pLayername[0], pSpatialColumnName, shape, SeFilter.METHOD_AI); filters = new SeShapeFilter[1]; filters[0] = filter; } SeQuery spatialQuery = null; SeSqlConstruct sqlCons = new SeSqlConstruct(pLayername, pWhere); spatialQuery = new SeQuery(con);//, pReturnFields, sqlCons); SeQueryInfo queryInfo = new SeQueryInfo(); queryInfo.setColumns(pReturnFields); if (byClause != null){ queryInfo.setByClause(byClause); } queryInfo.setConstruct(sqlCons); spatialQuery.prepareQueryInfo(queryInfo); /* * Set spatial constraints */ if (filters != null){ spatialQuery.setSpatialConstraints(SeQuery.SE_OPTIMIZE, false, filters); } spatialQuery.execute(); return this.handleResultSet(spatialQuery); } catch (Exception e) { if (e instanceof SeException){ ArcSDEUtils.printError((SeException)e); }else{ log.error(e.getMessage(), e); } throw new SQLException(e.getMessage()); } } /** * @see java.sql.Statement#executeUpdate(java.lang.String) */ public int executeUpdate(String arg0) throws SQLException { return 0; } /** * @see java.sql.Statement#executeUpdate(java.lang.String, int) */ public int executeUpdate(String arg0, int arg1) throws SQLException { return 0; } /** * @see java.sql.Statement#executeUpdate(java.lang.String, int[]) */ public int executeUpdate(String arg0, int[] arg1) throws SQLException { return 0; } /** * @see java.sql.Statement#executeUpdate(java.lang.String, java.lang.String[]) */ public int executeUpdate(String arg0, String[] arg1) throws SQLException { return 0; } /** * @see java.sql.Statement#getConnection() */ public Connection getConnection() throws SQLException { return this.connection; } /** * @see java.sql.Statement#getFetchDirection() */ public int getFetchDirection() throws SQLException { return 0; } /** * @see java.sql.Statement#getFetchSize() */ public int getFetchSize() throws SQLException { return 0; } /** * @see java.sql.Statement#getGeneratedKeys() */ public ResultSet getGeneratedKeys() throws SQLException { return null; } /** * @see java.sql.Statement#getMaxFieldSize() */ public int getMaxFieldSize() throws SQLException { return 0; } /** * @see java.sql.Statement#getMaxRows() */ public int getMaxRows() throws SQLException { return 0; } /** * @see java.sql.Statement#getMoreResults() */ public boolean getMoreResults() throws SQLException { return false; } /** * @see java.sql.Statement#getMoreResults(int) */ public boolean getMoreResults(int arg0) throws SQLException { return false; } /** * @see java.sql.Statement#getQueryTimeout() */ public int getQueryTimeout() throws SQLException { return 0; } /** * @see java.sql.Statement#getResultSet() */ public ResultSet getResultSet() throws SQLException { return null; } /** * @see java.sql.Statement#getResultSetConcurrency() */ public int getResultSetConcurrency() throws SQLException { return 0; } /** * @see java.sql.Statement#getResultSetHoldability() */ public int getResultSetHoldability() throws SQLException { return 0; } /** * @see java.sql.Statement#getResultSetType() */ public int getResultSetType() throws SQLException { return 0; } /** * @see java.sql.Statement#getUpdateCount() */ public int getUpdateCount() throws SQLException { return 0; } /** * @see java.sql.Statement#getWarnings() */ public SQLWarning getWarnings() throws SQLException { return null; } /** * @see java.sql.Statement#setCursorName(java.lang.String) */ public void setCursorName(String arg0) throws SQLException { } /** * @see java.sql.Statement#setEscapeProcessing(boolean) */ public void setEscapeProcessing(boolean arg0) throws SQLException { } /** * @see java.sql.Statement#setFetchDirection(int) */ public void setFetchDirection(int arg0) throws SQLException { } /** * @see java.sql.Statement#setFetchSize(int) */ public void setFetchSize(int arg0) throws SQLException { } /** * @see java.sql.Statement#setMaxFieldSize(int) */ public void setMaxFieldSize(int arg0) throws SQLException { } /** * @see java.sql.Statement#setMaxRows(int) */ public void setMaxRows(int arg0) throws SQLException { } /** * @see java.sql.Statement#setQueryTimeout(int) */ public void setQueryTimeout(int arg0) throws SQLException { } /** * Copied from de.intevation.gnv.geobackend.sde.datasources.SDEQuery * @param pSeQuery * @return * @throws SeException */ private ResultSet handleResultSet(SeQuery pSeQuery) throws SeException { log.debug("ArcSDEStatement,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; } }