tim@130: /**
tim@130: 		*
tim@130: 		*/
tim@263: 	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@263: import com.esri.sde.sdk.client.SeCoordinateReference;
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@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.geom.GeometryFactory;
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@263: 	        if (statement.toLowerCase().contains("st_astext")){
tim@263: 	            
tim@263: 	            String[] values =  statement.toLowerCase().split("where", 2);
tim@263: 	            String where = values.length > 1 ? values[1].trim() : "";
tim@263: 	            values = values[0].split("from", 2);
tim@263: 	            String layerName = values[1].toUpperCase().trim();
tim@263: 	            String[] returnFields  = values[0].replaceAll("select", "").trim().split(",");
tim@263: 	            String geometryColumnName = "N/N";
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@263: 	            Geometry g = null;
tim@263: 	            int pos = where.indexOf("intersects");
tim@263: 	            if (pos > 0 ){
tim@263: 	                String substr =  where.substring(pos);
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@263: 	                String wkt = intersectsStmt.substring(intersectsStmt.indexOf("\""), intersectsStmt.lastIndexOf("\"")).replace("\"", "").trim();
tim@263: 	                g = new WKTReader().read(wkt);
tim@263: 	                
tim@263: 	            }
tim@263: 	            return this.executeQuery(this.connection.getSeConnection(), layerName, geometryColumnName, where, g, returnFields);
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@263:     private ResultSet executeQuery(SeConnection con, String pLayername,
tim@263:                                    String pSpatialColumnName, String pWhere,
tim@263:                                    Geometry g, String[] pReturnFields)
tim@263:                                                                               throws SQLException {
tim@263:         log.debug("executeQuery()");
tim@263:         try {
tim@263:             // get the layer for querying
tim@263:             SeLayer lLayer = new SeLayer(con, pLayername, pSpatialColumnName);
tim@263:             SeCoordinateReference cref = lLayer.getCoordRef();
tim@263: 
tim@263:             SeShape shape = new SeShape();
tim@263:             shape.setCoordRef(lLayer.getCoordRef());
tim@263:             SDEPoint[] lPoints = new ArcSDEUtils().createPoints(g);
tim@263: 
tim@263:             shape.generatePolygon(lPoints.length, 1, null, lPoints);
tim@263:             SeShapeFilter filter = new SeShapeFilter(pLayername,
tim@263:                     pSpatialColumnName, shape, SeFilter.METHOD_AI);
tim@263:             SeShapeFilter[] filters = new SeShapeFilter[1];
tim@263:             filters[0] = filter;
tim@263: 
tim@263:             SeQuery spatialQuery = null;
tim@263:             SeSqlConstruct sqlCons = new SeSqlConstruct(pLayername, pWhere);
tim@263:             spatialQuery = new SeQuery(con, pReturnFields, sqlCons);
tim@263:             spatialQuery.prepareQuery();
tim@263:             /*
tim@263:              * Set spatial constraints
tim@263:              */
tim@263:             spatialQuery.setSpatialConstraints(SeQuery.SE_OPTIMIZE, false,
tim@263:                     filters);
tim@263:             spatialQuery.execute();
tim@263: 
tim@263:             return this.handleResultSet(spatialQuery);
tim@263: 
tim@263:         } catch (Exception e) {
tim@263:             log.error(e.getMessage(), e);
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@130: }