Application: gvSIG desktop » gvSIG data provider for MS SQLServer

import java.sql.ResultSet;

import java.sql.Statement;

import java.util.ArrayList;

import java.util.List;

import org.gvsig.fmap.dal.store.jdbc.exception.JDBCSQLException;

import org.gvsig.fmap.dal.store.jdbc2.JDBCUtils;

import org.gvsig.fmap.geom.Geometry;

import org.gvsig.fmap.geom.primitive.Envelope;

    public void createOrUpdateSpatialIndex(

            Connection conn,

            String database,

            String schema,

            String table,

            String columnName

        ) throws JDBCSQLException {

        List<String> sqls = this.createOrUpdateSpatialIndexSql(

                conn, database, schema, table, columnName

        );

        Statement st = null;

        try {

            st = conn.createStatement();

            for (String sql : sqls) {

                JDBCUtils.execute(st, sql);

            }

        } catch (SQLException ex) {

            throw new JDBCSQLException(ex);

        } finally {

            JDBCUtils.closeQuietly(st);

        }

    }

    public List<String> createOrUpdateSpatialIndexSql(

            Connection conn,

            String database,

            String schema,

            String table,

            String columnName

        ) throws JDBCSQLException {

        JDBCSQLBuilderBase sqlbuilder = createSQLBuilder();

        MSSQLServerSQLBuilder.MSSQLServerCreateIndexBuilder create_index = (MSSQLServerSQLBuilder.MSSQLServerCreateIndexBuilder) sqlbuilder.create_index();

        sqlbuilder.create_index().spatial();

        create_index.setBoundingBox(

                this.getBoundingBox(

                        conn,

                        database,

                        schema,

                        table,

                        columnName

                )

        );

        create_index.if_not_exist();

        create_index.name("idx_" + table + "_" + columnName);

        create_index.column(columnName);

        create_index.table().database(database).schema(schema).name(table);

        return create_index.toStrings();

    }    

    public Envelope getBoundingBox(

            Connection conn,

            String database,

            String schema,

            String table,

            String columnName

        ) throws JDBCSQLException {

        JDBCSQLBuilderBase sqlbuilder = createSQLBuilder();

        sqlbuilder.select().column().value(

            sqlbuilder.getAsGeometry(

                sqlbuilder.ST_ExtentAggregate(

                        sqlbuilder.column(columnName)

                )

            )

        );

        sqlbuilder.select().from().table().database(database).schema(schema).name(table);

        String sql = sqlbuilder.select().toString();

        Statement st = null;

        ResultSet rs = null;

        try {

            st = conn.createStatement();

            rs = JDBCUtils.executeQuery(st, sql);

            if (!rs.next()) {

                return null;

            }

            Geometry geom = this.getGeometryFromColumn(rs, 1);

            if (geom == null) {

                return null;

            }

            return geom.getEnvelope();

        } catch (Exception ex) {

            throw new JDBCSQLException(ex);

        } finally {

            JDBCUtils.closeQuietly(st);

            JDBCUtils.closeQuietly(rs);

        }        

    }