tim@554: package de.intevation.gnv.geobackend.base.query;
tim@554: 
tim@554: import com.esri.sde.sdk.client.SDEPoint;
tim@554: import com.esri.sde.sdk.client.SeColumnDefinition;
tim@554: import com.esri.sde.sdk.client.SeConnection;
tim@554: import com.esri.sde.sdk.client.SeException;
tim@554: import com.esri.sde.sdk.client.SeFilter;
tim@554: import com.esri.sde.sdk.client.SeLayer;
tim@554: import com.esri.sde.sdk.client.SeQuery;
tim@554: import com.esri.sde.sdk.client.SeQueryInfo;
tim@554: import com.esri.sde.sdk.client.SeRow;
tim@554: import com.esri.sde.sdk.client.SeShape;
tim@554: import com.esri.sde.sdk.client.SeShapeFilter;
tim@554: import com.esri.sde.sdk.client.SeSqlConstruct;
tim@554: 
tim@554: public class GroupBySample {
tim@554: 
sascha@885: 
tim@554:     public void executeQuery(SeConnection con, String[] pLayername,
tim@554:                                    String pSpatialColumnName, String pWhere,
tim@554:                                    SDEPoint[] g, String[] pReturnFields, String byClause) {
sascha@885: 
tim@554:         try {
tim@554:             // get the layer for querying
sascha@885: 
tim@554:             SeShapeFilter[] filters  = null;
tim@554:             if (g != null){
tim@554:                 SeLayer lLayer = new SeLayer(con, pLayername[0], pSpatialColumnName);
tim@554:                 SeShape shape = new SeShape();
tim@554:                 shape.setCoordRef(lLayer.getCoordRef());
sascha@885: 
sascha@885: 
sascha@885: 
tim@554:                 shape.generatePolygon(g.length, 1, null, g);
tim@554:                 SeShapeFilter filter = new SeShapeFilter(pLayername[0],
tim@554:                         pSpatialColumnName, shape, SeFilter.METHOD_AI);
tim@554:                 filters = new SeShapeFilter[1];
tim@554:                 filters[0] = filter;
tim@554:             }
tim@554: 
tim@554:             SeQuery spatialQuery = null;
tim@554:             SeSqlConstruct sqlCons = new SeSqlConstruct(pLayername, pWhere);
tim@554:             spatialQuery = new SeQuery(con);
sascha@885: 
tim@554:             SeQueryInfo queryInfo = new SeQueryInfo();
tim@554:             queryInfo.setColumns(pReturnFields);
sascha@885: 
tim@554:             if (byClause != null){
tim@554:                 queryInfo.setByClause(byClause);
tim@554:             }
sascha@885: 
tim@554:             queryInfo.setConstruct(sqlCons);
tim@554:             spatialQuery.prepareQueryInfo(queryInfo);
tim@554: 
tim@554:             /*
tim@554:              * Set spatial constraints
tim@554:              */
tim@554:             if (filters != null){
tim@554:                 spatialQuery.setSpatialConstraints(SeQuery.SE_OPTIMIZE, false,
tim@554:                         filters);
tim@554:             }
tim@554:             spatialQuery.execute();
tim@554: 
tim@554:             SeRow row;
tim@554:             int lCount;
tim@554:             for (lCount = 0; (row =spatialQuery.fetch()) != null; lCount++) {
tim@554:                 // one time execution
tim@554:                 if (lCount == 0) {
tim@554:                     // analyze cols of result set
tim@554:                     SeColumnDefinition[] lCols = row.getColumns();
sascha@885: 
tim@554:                 }
tim@554:                 short lNumCols = row.getNumColumns();
sascha@885: 
tim@554:                 for (int i = 0; i < lNumCols; i++) {
tim@554:                     System.out.println(row.getObject(i));
tim@554:                 }
sascha@885: 
tim@554:             }
tim@554:             spatialQuery.close();
tim@554: 
sascha@885:         } catch (Exception e){
tim@554:                 e.printStackTrace();
tim@554:         }
sascha@885: 
tim@554:     }
sascha@885: 
sascha@885: 
tim@554:     /**
tim@554:      * @param args
tim@554:      */
tim@554:     public static void main(String[] args) {
sascha@885: 
tim@554:         try {
tim@554:             String[] layerNames = new String[] { "median.meshpoint", "median.mesh" };
tim@554:             String spatialColumnName = "SHAPE";
tim@554:             String where = "median.meshpoint.meshid = median.mesh.meshid";
tim@554:             String[] returnFields = new String[] { "sourceid" };
tim@554:             String byClause = "group by sourceid";
tim@554:             SDEPoint[] g = new SDEPoint[5];
tim@554:             g[0] = new SDEPoint(52, 8);
tim@554:             g[1] = new SDEPoint(52, 9);
tim@554:             g[2] = new SDEPoint(53, 9);
tim@554:             g[3] = new SDEPoint(53, 8);
tim@554:             g[4] = new SDEPoint(52, 8);
tim@554: 
tim@554:             String server = "";
tim@554:             String port = "";
tim@554:             String database = "";
tim@554:             String username = "";
tim@554:             String credentials = "";
sascha@885:             SeConnection con = new SeConnection(server, port,
tim@554:                                                 database, username,
tim@554:                                                 credentials);
tim@554:             new GroupBySample().executeQuery(con, layerNames, spatialColumnName,
tim@554:                                              where, g, returnFields, byClause);
sascha@885: 
sascha@885: 
tim@554:         } catch (SeException e) {
tim@554:            e.printStackTrace();
tim@554:         }
tim@554:     }
tim@554: }