Application: gvSIG desktop

package org.gvsig.fmap.dal.store.jdbc2.spi;

import java.sql.Connection;

import org.cresques.cts.IProjection;

public interface SRSSolver {

    void add(String databaseCode, String applicationAbbrev);

    String getApplicationAbbrev(Connection connection, String databaseCode);

    String getDatabaseCode(Connection connection, String applicationAbbrev);

    boolean hasApplicationAbbrev(Connection connection, String applicationAbbrev);

    boolean hasDatabaseCode(Connection connection, String databaseCode);

    public IProjection getProjection(Connection connection, String databaseCode);

    public String getDatabaseCode(Connection connection, IProjection projection);

}

package org.gvsig.fmap.dal.store.jdbc2.spi;

import java.sql.Connection;

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

public class SRSSolverDumb extends SRSSolverBase implements SRSSolver {

    public SRSSolverDumb(JDBCHelper helper) {

        super(helper);

    }

    @Override

    protected String searchDatabaseCode(Connection connection, String applicationAbbrev) {

        try {

            String[] s = applicationAbbrev.split(applicationAbbrev);

            return s[1].trim();

        } catch (Throwable ex) {

            throw new RuntimeException("Problems searching database code from '"+applicationAbbrev+"'.",ex);

        }

    }

    @Override

    protected String searchApplicationAbbrev(Connection connection, String databaseCode) {

        try {

            return "EPSG:" + databaseCode.trim();

        } catch (Throwable ex) {

            throw new RuntimeException("Problems searching application abbrev from '"+databaseCode+"'.",ex);

        }

    }    

}

    protected SRSSolver srssolver;

        this.srssolver = new SRSSolverDumb(this);

        Connection connection=null;

        try {

            connection = this.getConnection();

            String databaseCode = this.srssolver.getDatabaseCode(connection, crs.getAbrev());

            return Integer.parseInt(databaseCode);

        } catch(Throwable ex) {

            throw new RuntimeException(ex);

        } finally {

            this.closeConnection(connection);

        }

        Connection connection=null;

        try {

            connection = this.getConnection();

            return this.srssolver.getDatabaseCode(connection, proj);

        } catch(Throwable ex) {

            throw new RuntimeException(ex);

        } finally {

            this.closeConnection(connection);

        }

    public SRSSolver getSRSSolver() {

        return this.srssolver;

    }

    @Override

        Connection connection=null;

        try {

            connection = this.getConnection();

            return this.srssolver.getProjection(connection, databaseCode);

        } catch(Throwable ex) {

            throw new RuntimeException(ex);

        } finally {

            this.closeConnection(connection);

package org.gvsig.fmap.dal.store.jdbc2.spi;

import java.sql.Connection;

import java.sql.ResultSet;

import java.sql.Statement;

import java.util.HashMap;

import java.util.Map;

import org.apache.commons.lang3.StringUtils;

import org.cresques.cts.IProjection;

import org.gvsig.fmap.crs.CRSFactory;

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

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

public class SRSSolverBase implements SRSSolver {

    protected Map<String,String> database2applicationAbbrev;

    protected Map<String,String> applicationAbbrev2database;

    protected JDBCHelper helper;

    public SRSSolverBase(JDBCHelper helper) {

        this.helper = helper;

        this.applicationAbbrev2database = new HashMap<>();

        this.database2applicationAbbrev = new HashMap<>();

    }

    @Override

    public void add(String databaseCode, String applicationAbbrev) {

        this.applicationAbbrev2database.put(applicationAbbrev, databaseCode);

        this.database2applicationAbbrev.put(databaseCode, applicationAbbrev);

    }

    @Override

    public String getDatabaseCode(Connection connection, String applicationAbbrev) {

        if( this.hasApplicationAbbrev(connection, applicationAbbrev) ) {

            return this.applicationAbbrev2database.get(applicationAbbrev);

        }

        String databaseCode = this.searchDatabaseCode(connection, applicationAbbrev);

        if( StringUtils.isEmpty(databaseCode) ) {

            return null;

        }

        this.add(databaseCode, applicationAbbrev);

        return databaseCode;

    }

    @Override

    public String getApplicationAbbrev(Connection connection, String databaseCode) {

        if( this.hasDatabaseCode(connection, databaseCode) ) {

            return this.database2applicationAbbrev.get(databaseCode);

        }

        String applicationAbbrev = this.searchApplicationAbbrev(connection, databaseCode);

        if( StringUtils.isEmpty(applicationAbbrev) ) {

            return null;

        }

        this.add(databaseCode, applicationAbbrev);

        return applicationAbbrev;

    }

    @Override

    public boolean hasDatabaseCode(Connection connection, String databaseCode) {

        return this.database2applicationAbbrev.containsKey(databaseCode);

    }

    @Override

    public boolean hasApplicationAbbrev(Connection connection, String applicationAbbrev) {

        return this.applicationAbbrev2database.containsKey(applicationAbbrev);

    }

    protected String searchDatabaseCode(Connection connection, String applicationAbbrev) {

        // Initialize sql only for debugging purposes

        String sql = "select srid, auth_name, auth_srid from spatial_ref_sys where auth_name/auth_srid is '"+applicationAbbrev+"'.";

        try {

            String[] s = applicationAbbrev.split(applicationAbbrev);

            sql = "select srid, auth_name, auth_srid from spatial_ref_sys where auth_name = '" + s[0] +"' and auth_srid = '"+s[1]+"' ";

            Statement st = connection.createStatement();

            ResultSet rs = JDBCUtils.executeQuery(st, sql);

            if ( rs.next() ) {

                int srid = rs.getInt("srid");

                return String.valueOf(srid);

            }            

            return null;

        } catch (Throwable ex) {

            throw new RuntimeException("Problems with SQL '"+sql+"'.",ex);

        }

    }

    protected String searchApplicationAbbrev(Connection connection, String databaseCode) {

        String sql = "select srid, auth_name, auth_srid from spatial_ref_sys where srid = " + databaseCode;

        try {

            Statement st = connection.createStatement();

            ResultSet rs = JDBCUtils.executeQuery(st, sql);

            if ( rs.next() ) {

                int auth_code = rs.getInt("auth_srid");

                String auth_name = rs.getString("auth_name");

                return auth_name + ":" + auth_code;

            }            

            return null;

        } catch (Throwable ex) {

            throw new RuntimeException("Problems with SQL '"+sql+"'.",ex);

        }

    }

    @Override

    public IProjection getProjection(Connection connection, String databaseCode) {

        if( StringUtils.trimToEmpty(databaseCode).equals("0") ) {

            return null;

        }

        String abbrev = this.getApplicationAbbrev(connection, databaseCode);

        if( StringUtils.isEmpty(abbrev) ) {

            return null;

        }

        IProjection proj = CRSFactory.getCRS(abbrev);

        return proj;

    }

    @Override

    public String getDatabaseCode(Connection connection, IProjection projection) {

        return this.getDatabaseCode(connection, projection.getAbrev());

    }

}

import org.gvsig.fmap.dal.store.jdbc2.spi.SRSSolver;

    public SRSSolver getSRSSolver();

import org.gvsig.fmap.dal.store.jdbc2.spi.SRSSolverBase;

        this.srssolver = new SRSSolverBase(this);