tim@130: /**
tim@130: 		*
tim@130: 		*/
tim@266: package de.intevation.gnv.geobackend.sde.datasources;
tim@130: 
tim@130: import java.sql.Connection;
tim@130: import java.sql.ResultSet;
tim@130: import java.sql.SQLException;
tim@130: import java.sql.SQLWarning;
tim@130: import java.sql.Statement;
tim@130: 
tim@130: import org.apache.log4j.Logger;
tim@130: 
tim@263: import com.esri.sde.sdk.client.SDEPoint;
tim@130: import com.esri.sde.sdk.client.SeColumnDefinition;
tim@263: import com.esri.sde.sdk.client.SeConnection;
tim@130: import com.esri.sde.sdk.client.SeException;
tim@263: import com.esri.sde.sdk.client.SeFilter;
tim@263: import com.esri.sde.sdk.client.SeLayer;
tim@130: import com.esri.sde.sdk.client.SeQuery;
tim@267: import com.esri.sde.sdk.client.SeQueryInfo;
tim@130: import com.esri.sde.sdk.client.SeRow;
tim@263: import com.esri.sde.sdk.client.SeShape;
tim@263: import com.esri.sde.sdk.client.SeShapeFilter;
tim@263: import com.esri.sde.sdk.client.SeSqlConstruct;
tim@263: import com.vividsolutions.jts.geom.Geometry;
tim@263: import com.vividsolutions.jts.io.WKTReader;
tim@263: 
tim@130: 
tim@130: 		/**
tim@130:  * @author Tim Englich <tim.englich@intevation.de>
tim@130:  *
tim@130:  */
tim@130: public class ArcSDEStatement implements Statement {
tim@130: 
tim@130: 	/**
tim@130:      * the logger, used to log exceptions and additonaly information
tim@130:      */
tim@130:     private static Logger log = Logger.getLogger(ArcSDEStatement.class);
tim@130:     
tim@130: 	private ArcSDEConnection connection = null;
tim@130: 	
tim@130: 	/**
tim@130: 	 * Constructor
tim@130: 	 */
tim@130: 	public ArcSDEStatement(ArcSDEConnection connection) {
tim@130: 		this.connection = connection;
tim@130: 	}
tim@130: 
tim@130: 	/**
tim@130: 	 * @see java.sql.Statement#addBatch(java.lang.String)
tim@130: 	 */
tim@130: 	public void addBatch(String arg0) throws SQLException {
tim@130: 	}
tim@130: 
tim@130: 	/**
tim@130: 	 * @see java.sql.Statement#cancel()
tim@130: 	 */
tim@130: 	public void cancel() throws SQLException {
tim@130: 	}
tim@130: 
tim@130: 	/**
tim@130: 	 * @see java.sql.Statement#clearBatch()
tim@130: 	 */
tim@130: 	public void clearBatch() throws SQLException {
tim@130: 	}
tim@130: 
tim@130: 	/**
tim@130: 	 * @see java.sql.Statement#clearWarnings()
tim@130: 	 */
tim@130: 	public void clearWarnings() throws SQLException {
tim@130: 	}
tim@130: 
tim@130: 	/**
tim@130: 	 * @see java.sql.Statement#close()
tim@130: 	 */
tim@130: 	public void close() throws SQLException {
tim@130: 	}
tim@130: 
tim@130: 	/**
tim@130: 	 * @see java.sql.Statement#execute(java.lang.String)
tim@130: 	 */
tim@130: 	public boolean execute(String arg0) throws SQLException {
tim@130: 
tim@130: 		return false;
tim@130: 	}
tim@130: 
tim@130: 	/**
tim@130: 	 * @see java.sql.Statement#execute(java.lang.String, int)
tim@130: 	 */
tim@130: 	public boolean execute(String arg0, int arg1) throws SQLException {
tim@130: 
tim@130: 		return false;
tim@130: 	}
tim@130: 
tim@130: 	/**
tim@130: 	 * @see java.sql.Statement#execute(java.lang.String, int[])
tim@130: 	 */
tim@130: 	public boolean execute(String arg0, int[] arg1) throws SQLException {
tim@130: 
tim@130: 		return false;
tim@130: 	}
tim@130: 
tim@130: 	/**
tim@130: 	 * @see java.sql.Statement#execute(java.lang.String, java.lang.String[])
tim@130: 	 */
tim@130: 	public boolean execute(String arg0, String[] arg1) throws SQLException {
tim@130: 
tim@130: 		return false;
tim@130: 	}
tim@130: 
tim@130: 	/**
tim@130: 	 * @see java.sql.Statement#executeBatch()
tim@130: 	 */
tim@130: 	public int[] executeBatch() throws SQLException {
tim@130: 
tim@130: 		return null;
tim@130: 	}
tim@130: 
tim@130: 	/**
tim@130: 	 * @see java.sql.Statement#executeQuery(java.lang.String)
tim@130: 	 */
tim@130: 	public ResultSet executeQuery(String statement) throws SQLException {
tim@130: 		try {
tim@263: 		    SeQuery query = null;
tim@275: 	        if (statement.toLowerCase().contains("st_astext")  || statement.toLowerCase().contains("intersects")){
tim@263: 	            
tim@263: 	            String[] values =  statement.toLowerCase().split("where", 2);
tim@263: 	            String where = values.length > 1 ? values[1].trim() : "";
tim@277: 	            String[] tableNames = values[0].substring(values[0].indexOf("from")).replaceFirst("from", "").toUpperCase().trim().split(", ");
tim@277: 	            String columnValueString = values[0].substring(0, values[0].indexOf("from")).trim();
tim@277: 	            columnValueString = columnValueString.replaceFirst("select", "").trim();
tim@377: 	            String[] returnFields  = columnValueString.split(", ");
tim@275: 	            String geometryColumnName = null;
tim@267: 	            String byClause = null;
tim@274: 	            int byClausePos = where.indexOf("group by");
tim@267: 	            if (byClausePos < 0){
tim@267: 	                byClausePos = where.indexOf("order by");
tim@267: 	            }
tim@267: 	            if (byClausePos > 0){
tim@267: 	                byClause = where.substring(byClausePos);
tim@267: 	                where = where.substring(0,byClausePos);
tim@267: 	            }
tim@267: 	            
tim@263: 	            for (int i = 0; i < returnFields.length; i++){
tim@263: 	                returnFields[i] = returnFields[i].trim();
tim@263: 	                if (returnFields[i].startsWith("st_astext(")){
tim@263: 	                    returnFields[i] = returnFields[i].replaceAll("st_astext", "");
tim@263: 	                    returnFields[i] = returnFields[i].substring(1, returnFields[i].length()-1);
tim@263: 	                    geometryColumnName = returnFields[i];
tim@263: 	                }
tim@263: 	            }
tim@267: 	            
tim@267: 	            for (int i = 0; i < tableNames.length; i++){
tim@267: 	                tableNames[i] = tableNames[i].trim();
tim@267: 	            }
tim@267: 	            
tim@263: 	            Geometry g = null;
tim@263: 	            int pos = where.indexOf("intersects");
tim@263: 	            if (pos > 0 ){
tim@263: 	                String substr =  where.substring(pos);
tim@275: 	                where = where.substring(0,where.lastIndexOf("intersects")); // TODO auch or unterstützen
tim@263: 	                where = where.substring(0,where.lastIndexOf("and")); // TODO auch or unterstützen
tim@263: 	                String intersectsStmt = substr.substring(0, substr.lastIndexOf(")")); // Annahme räumliches Stmt steht am Ende
tim@275: 	                String wkt = null;
tim@275: 	                if (intersectsStmt.contains("select")){
tim@275: 	                    // Anstelle eines WKT ist ein InnerSelect zum Bestimmen der Comparatorgeometrie gegeben.
tim@275: 	                    String stmt = intersectsStmt.substring(intersectsStmt.indexOf("select"),intersectsStmt.lastIndexOf(")"));
tim@275: 	                    ResultSet resultSet = this.executeQuery(stmt);
tim@275: 	                    if (resultSet != null && resultSet.next()){
tim@275: 	                        // TODO Selektion von mehreren Geometrien unterstützen.
tim@275: 	                        wkt = resultSet.getString("SHAPE");
tim@275: 	                    }
tim@275: 	                }else{
tim@275: 	                    wkt = intersectsStmt.substring(intersectsStmt.indexOf("\""), intersectsStmt.lastIndexOf("\"")).replace("\"", "").trim();
tim@275: 	                }
tim@263: 	                g = new WKTReader().read(wkt);
tim@263: 	                
tim@275: 	                if (geometryColumnName == null){
tim@275: 	                    geometryColumnName = "SHAPE"; // TODO dynamisch aus Intersects auslesen.
tim@275: 	                }
tim@275: 	                
tim@263: 	            }
tim@267: 	            return this.executeQuery(this.connection.getSeConnection(), tableNames, geometryColumnName, where, g, returnFields,byClause);
tim@263: 	        }else{
tim@263: 	            query = new SeQuery(this.connection.getSeConnection());
tim@263: 	            query.prepareSql(statement);
tim@263: 	            query.execute();
tim@263: 	            return this.handleResultSet(query);
tim@263: 	        }
tim@263: 	       
tim@263: 		} catch (Exception e) {
tim@130: 	        log.error(e,e);
tim@130: 	        throw new SQLException(e.getMessage());
tim@130:         }
tim@130:         
tim@130:         
tim@130: 	}
tim@263: 	
tim@267:     private ResultSet executeQuery(SeConnection con, String[] pLayername,
tim@263:                                    String pSpatialColumnName, String pWhere,
tim@267:                                    Geometry g, String[] pReturnFields, String byClause)
tim@263:                                                                               throws SQLException {
tim@263:         log.debug("executeQuery()");
tim@263:         try {
tim@263:             // get the layer for querying
tim@267:            
tim@266:             SeShapeFilter[] filters  = null;
tim@266:             if (g != null){
tim@267:                 SeLayer lLayer = new SeLayer(con, pLayername[0], pSpatialColumnName);
tim@266:                 SeShape shape = new SeShape();
tim@266:                 shape.setCoordRef(lLayer.getCoordRef());
tim@266:                 
tim@266:                 SDEPoint[] lPoints = new ArcSDEUtils().createPoints(g);
tim@266:     
tim@266:                 shape.generatePolygon(lPoints.length, 1, null, lPoints);
tim@267:                 SeShapeFilter filter = new SeShapeFilter(pLayername[0],
tim@266:                         pSpatialColumnName, shape, SeFilter.METHOD_AI);
tim@266:                 filters = new SeShapeFilter[1];
tim@266:                 filters[0] = filter;
tim@266:             }
tim@263: 
tim@263:             SeQuery spatialQuery = null;
tim@263:             SeSqlConstruct sqlCons = new SeSqlConstruct(pLayername, pWhere);
tim@267:             spatialQuery = new SeQuery(con);//, pReturnFields, sqlCons);
tim@267:             
tim@267:             SeQueryInfo queryInfo = new SeQueryInfo();
tim@267:             queryInfo.setColumns(pReturnFields);
tim@267:             
tim@267:             if (byClause != null){
tim@267:                 queryInfo.setByClause(byClause);
tim@267:             }
tim@267:             
tim@267:             queryInfo.setConstruct(sqlCons);
tim@267:             spatialQuery.prepareQueryInfo(queryInfo);
tim@267: 
tim@263:             /*
tim@263:              * Set spatial constraints
tim@263:              */
tim@266:             if (filters != null){
tim@266:                 spatialQuery.setSpatialConstraints(SeQuery.SE_OPTIMIZE, false,
tim@266:                         filters);
tim@266:             }
tim@263:             spatialQuery.execute();
tim@263: 
tim@263:             return this.handleResultSet(spatialQuery);
tim@263: 
tim@263:         } catch (Exception e) {
tim@267:             if (e instanceof SeException){
tim@267:                 ArcSDEUtils.printError((SeException)e);
tim@267:             }else{
tim@267:                 log.error(e.getMessage(), e);
tim@267:             }
tim@267:             
tim@263:             throw new SQLException(e.getMessage());
tim@263:         }
tim@263:     }
tim@130: 
tim@130: 	/**
tim@130: 	 * @see java.sql.Statement#executeUpdate(java.lang.String)
tim@130: 	 */
tim@130: 	public int executeUpdate(String arg0) throws SQLException {
tim@130: 
tim@130: 		return 0;
tim@130: 	}
tim@130: 
tim@130: 	/**
tim@130: 	 * @see java.sql.Statement#executeUpdate(java.lang.String, int)
tim@130: 	 */
tim@130: 	public int executeUpdate(String arg0, int arg1) throws SQLException {
tim@130: 
tim@130: 		return 0;
tim@130: 	}
tim@130: 
tim@130: 	/**
tim@130: 	 * @see java.sql.Statement#executeUpdate(java.lang.String, int[])
tim@130: 	 */
tim@130: 	public int executeUpdate(String arg0, int[] arg1) throws SQLException {
tim@130: 
tim@130: 		return 0;
tim@130: 	}
tim@130: 
tim@130: 	/**
tim@130: 	 * @see java.sql.Statement#executeUpdate(java.lang.String, java.lang.String[])
tim@130: 	 */
tim@130: 	public int executeUpdate(String arg0, String[] arg1) throws SQLException {
tim@130: 
tim@130: 		return 0;
tim@130: 	}
tim@130: 
tim@130: 	/**
tim@130: 	 * @see java.sql.Statement#getConnection()
tim@130: 	 */
tim@130: 	public Connection getConnection() throws SQLException {
tim@130: 		return this.connection;
tim@130: 	}
tim@130: 
tim@130: 	/**
tim@130: 	 * @see java.sql.Statement#getFetchDirection()
tim@130: 	 */
tim@130: 	public int getFetchDirection() throws SQLException {
tim@130: 
tim@130: 		return 0;
tim@130: 	}
tim@130: 
tim@130: 	/**
tim@130: 	 * @see java.sql.Statement#getFetchSize()
tim@130: 	 */
tim@130: 	public int getFetchSize() throws SQLException {
tim@130: 
tim@130: 		return 0;
tim@130: 	}
tim@130: 
tim@130: 	/**
tim@130: 	 * @see java.sql.Statement#getGeneratedKeys()
tim@130: 	 */
tim@130: 	public ResultSet getGeneratedKeys() throws SQLException {
tim@130: 
tim@130: 		return null;
tim@130: 	}
tim@130: 
tim@130: 	/**
tim@130: 	 * @see java.sql.Statement#getMaxFieldSize()
tim@130: 	 */
tim@130: 	public int getMaxFieldSize() throws SQLException {
tim@130: 
tim@130: 		return 0;
tim@130: 	}
tim@130: 
tim@130: 	/**
tim@130: 	 * @see java.sql.Statement#getMaxRows()
tim@130: 	 */
tim@130: 	public int getMaxRows() throws SQLException {
tim@130: 
tim@130: 		return 0;
tim@130: 	}
tim@130: 
tim@130: 	/**
tim@130: 	 * @see java.sql.Statement#getMoreResults()
tim@130: 	 */
tim@130: 	public boolean getMoreResults() throws SQLException {
tim@130: 
tim@130: 		return false;
tim@130: 	}
tim@130: 
tim@130: 	/**
tim@130: 	 * @see java.sql.Statement#getMoreResults(int)
tim@130: 	 */
tim@130: 	public boolean getMoreResults(int arg0) throws SQLException {
tim@130: 
tim@130: 		return false;
tim@130: 	}
tim@130: 
tim@130: 	/**
tim@130: 	 * @see java.sql.Statement#getQueryTimeout()
tim@130: 	 */
tim@130: 	public int getQueryTimeout() throws SQLException {
tim@130: 
tim@130: 		return 0;
tim@130: 	}
tim@130: 
tim@130: 	/**
tim@130: 	 * @see java.sql.Statement#getResultSet()
tim@130: 	 */
tim@130: 	public ResultSet getResultSet() throws SQLException {
tim@130: 
tim@130: 		return null;
tim@130: 	}
tim@130: 
tim@130: 	/**
tim@130: 	 * @see java.sql.Statement#getResultSetConcurrency()
tim@130: 	 */
tim@130: 	public int getResultSetConcurrency() throws SQLException {
tim@130: 
tim@130: 		return 0;
tim@130: 	}
tim@130: 
tim@130: 	/**
tim@130: 	 * @see java.sql.Statement#getResultSetHoldability()
tim@130: 	 */
tim@130: 	public int getResultSetHoldability() throws SQLException {
tim@130: 
tim@130: 		return 0;
tim@130: 	}
tim@130: 
tim@130: 	/**
tim@130: 	 * @see java.sql.Statement#getResultSetType()
tim@130: 	 */
tim@130: 	public int getResultSetType() throws SQLException {
tim@130: 
tim@130: 		return 0;
tim@130: 	}
tim@130: 
tim@130: 	/**
tim@130: 	 * @see java.sql.Statement#getUpdateCount()
tim@130: 	 */
tim@130: 	public int getUpdateCount() throws SQLException {
tim@130: 
tim@130: 		return 0;
tim@130: 	}
tim@130: 
tim@130: 	/**
tim@130: 	 * @see java.sql.Statement#getWarnings()
tim@130: 	 */
tim@130: 	public SQLWarning getWarnings() throws SQLException {
tim@130: 
tim@130: 		return null;
tim@130: 	}
tim@130: 
tim@130: 	/**
tim@130: 	 * @see java.sql.Statement#setCursorName(java.lang.String)
tim@130: 	 */
tim@130: 	public void setCursorName(String arg0) throws SQLException {
tim@130: 	}
tim@130: 
tim@130: 	/**
tim@130: 	 * @see java.sql.Statement#setEscapeProcessing(boolean)
tim@130: 	 */
tim@130: 	public void setEscapeProcessing(boolean arg0) throws SQLException {
tim@130: 	}
tim@130: 
tim@130: 	/**
tim@130: 	 * @see java.sql.Statement#setFetchDirection(int)
tim@130: 	 */
tim@130: 	public void setFetchDirection(int arg0) throws SQLException {
tim@130: 	}
tim@130: 
tim@130: 	/**
tim@130: 	 * @see java.sql.Statement#setFetchSize(int)
tim@130: 	 */
tim@130: 	public void setFetchSize(int arg0) throws SQLException {
tim@130: 	}
tim@130: 
tim@130: 	/**
tim@130: 	 * @see java.sql.Statement#setMaxFieldSize(int)
tim@130: 	 */
tim@130: 	public void setMaxFieldSize(int arg0) throws SQLException {
tim@130: 	}
tim@130: 
tim@130: 	/**
tim@130: 	 * @see java.sql.Statement#setMaxRows(int)
tim@130: 	 */
tim@130: 	public void setMaxRows(int arg0) throws SQLException {
tim@130: 	}
tim@130: 
tim@130: 	/**
tim@130: 	 * @see java.sql.Statement#setQueryTimeout(int)
tim@130: 	 */
tim@130: 	public void setQueryTimeout(int arg0) throws SQLException {
tim@130: 	}
tim@130: 	
tim@130: 	/**
tim@130: 	 * Copied from de.intevation.gnv.geobackend.sde.datasources.SDEQuery
tim@130: 	 *  @param pSeQuery
tim@130: 	 *  @return
tim@130: 	 * @throws SeException
tim@130: 	 */
tim@130:     private ResultSet handleResultSet(SeQuery pSeQuery) throws SeException {
tim@130:         log.debug("ArcSDEStatement,handleResultSet()");
tim@130:         SDEResultSet lSet = new SDEResultSet();
tim@130:         SeRow row;
tim@130:         int lCount;
tim@130:         for (lCount = 0; (row = pSeQuery.fetch()) != null; lCount++) {
tim@130:             // one time execution
tim@130:             if (lCount == 0) {
tim@130:                 // analyze cols of result set
tim@130:                 SeColumnDefinition[] lCols = row.getColumns();
tim@130:                 for (SeColumnDefinition lCol : lCols) {
tim@130:                     lSet.addCol(new ColDefinition(lCol.getName(), lCol.getType()));// notice: esri-types have been copied into colDefinition class!
tim@130:                 }
tim@130:             }
tim@130:             short lNumCols = row.getNumColumns();
tim@130:             Row lBackingRow = new Row(lNumCols);
tim@130:             for (int i = 0; i < lNumCols; i++) {
tim@130:                 lBackingRow.addObject(row.getObject(i), i);
tim@130:             }
tim@130:             lSet.addRow(lBackingRow);
tim@130:         }
tim@130:         pSeQuery.close();
tim@130:         return lSet;
tim@130:     }
tim@130: 
tim@380:     public boolean isClosed() throws SQLException {
tim@380:         return false;
tim@380:     }
tim@380: 
tim@380:     public boolean isPoolable() throws SQLException {
tim@380:         return false;
tim@380:     }
tim@380: 
tim@380:     public void setPoolable(boolean arg0) throws SQLException {
tim@380:     }
tim@380: 
tim@380:     public boolean isWrapperFor(Class<?> iface) throws SQLException {
tim@380:         return false;
tim@380:     }
tim@380: 
tim@380:     public <T> T unwrap(Class<T> iface) throws SQLException {
tim@380:         return null;
tim@380:     }
tim@380: 
tim@130: }