OracleSpatialUtils.java

package liquibase.ext.spatial.sqlgenerator;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Collections;
import java.util.HashMap;
import java.util.Map;

import liquibase.database.Database;
import liquibase.database.jvm.JdbcConnection;
import liquibase.exception.UnexpectedLiquibaseException;
import liquibase.util.StringUtils;

/**
 * <code>OracleSpatialUtils</code> provides utility methods for Oracle Spatial.
 */
public class OracleSpatialUtils {

   /** The Oracle function that converts an EPSG SRID to the corresponding Oracle SRID. */
   public static final String EPSG_TO_ORACLE_FUNCTION = "SDO_CS.MAP_EPSG_SRID_TO_ORACLE";

   /** The mapping of EPSG SRID to Oracle SRID. */
   private final static Map<String, String> EPSG_TO_ORACLE_MAP = Collections
         .synchronizedMap(new HashMap<String, String>());

   /** Hide the default constructor. */
   private OracleSpatialUtils() {
   }

   /**
    * Converts the given Well-Known Text string to one that will work in Oracle. If the string is
    * greater than 4000 characters, the string is broken into pieces where each piece is converted
    * to a CLOB. The CLOB handling assumes that the result will be wrapped in single quotes so it
    * wraps the result in "<code>' || TO_CLOB(...) || '</code>".
    *
    * @param wkt
    *           the Well-Known Text string to convert.
    * @return the original WKT or a <code>TO_CLOB</code> concatenation of the WKT.
    */
   public static String getOracleWkt(final String wkt) {
      final String oracleWkt;
      // Strings longer than 4000 characters need to be converted to CLOBs.
      if (wkt.length() > 4000) {
         oracleWkt = "' || " + convertToClob(wkt) + " || '";
      } else {
         oracleWkt = wkt;
      }
      return oracleWkt;
   }

   /**
    * Generates the SQL to convert the given string to a CLOB.
    *
    * @param varchar
    *           the value to convert.
    * @return the SQL to convert the string to a CLOB.
    */
   public static String convertToClob(final String varchar) {
      int startIndex = 0;
      int endIndex = Math.min(startIndex + 4000, varchar.length());
      final StringBuilder clobs = new StringBuilder("TO_CLOB('").append(
            varchar.substring(startIndex, endIndex)).append("')");
      while (endIndex < varchar.length()) {
         startIndex = endIndex;
         endIndex = Math.min(startIndex + 4000, varchar.length());
         clobs.append(" || TO_CLOB('").append(varchar.substring(startIndex, endIndex)).append("')");
      }
      return clobs.toString();
   }

   /**
    * Converts the given EPSG SRID to the corresponding Oracle SRID.
    *
    * @param srid
    *           the EPSG SRID.
    * @param database
    *           the database instance.
    * @return the corresponding Oracle SRID.
    */
   public static String getOracleSrid(final String srid, final Database database) {
      final String oracleSrid;
      if (StringUtils.trimToNull(srid) == null) {
         oracleSrid = null;
      } else if (EPSG_TO_ORACLE_MAP.containsKey(srid)) {
         oracleSrid = EPSG_TO_ORACLE_MAP.get(srid);
      } else {
         oracleSrid = loadOracleSrid(srid, database);
         EPSG_TO_ORACLE_MAP.put(srid, oracleSrid);
      }
      return oracleSrid;
   }

   /**
    * Queries to the database to convert the given EPSG SRID to the corresponding Oracle SRID.
    *
    * @param srid
    *           the EPSG SRID.
    * @param database
    *           the database instance.
    * @return the corresponding Oracle SRID.
    */
   public static String loadOracleSrid(final String srid, final Database database) {
      final String oracleSrid;
      final JdbcConnection jdbcConnection = (JdbcConnection) database.getConnection();
      final Connection connection = jdbcConnection.getUnderlyingConnection();
      Statement statement = null;
      try {
         statement = connection.createStatement();
         final ResultSet resultSet = statement.executeQuery("SELECT " + EPSG_TO_ORACLE_FUNCTION
               + "(" + srid + ") FROM dual");
         resultSet.next();
         oracleSrid = resultSet.getString(1);
      } catch (final SQLException e) {
         throw new UnexpectedLiquibaseException("Failed to find the Oracle SRID for EPSG:" + srid,
               e);
      } finally {
         try {
            statement.close();
         } catch (final SQLException ignore) {
         }
      }
      return oracleSrid;
   }
}