Using Spring's StoredProcedure class with Oracle Spatial JGeometry

The Spring framework provides a neat wrapper class you can extend when you want to call a stored procedure. Sometimes though you need to extend to use a native connection because, for example, you need to pass an ORACLE Geometry type to the stored procedure.

The below code wraps a Create_Geometry_Line stored procedure which takes a geometry object. The calling code will use the executeMap method to pass in a Map of in parameters. It converts the list of doubles (which represent the points of the line) to an object of type STRUCT.

package uk.co.city81.persistence.dao.geometry;

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Types;
import java.util.Map;

import oracle.jdbc.OracleTypes;
import oracle.spatial.geometry.JGeometry;
import oracle.sql.STRUCT;

import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.SqlOutParameter;
import org.springframework.jdbc.core.SqlParameter;
import org.springframework.jdbc.object.StoredProcedure;


public class CreateGeometryLineDAO extends StoredProcedure {

    private Connection nativeConn = null;

    public static final String CREATE_GEOMETRY_LINE = "geometry_pkg.Create_Geometry_Line";

    // Constructor
    public CreateGeometryLineDAO(JdbcTemplate jdbcTemplate) {
        setJdbcTemplate(jdbcTemplate);
        setFunction(true);
        setSQL();
        declareParameter(new SqlOutParameter("l_Return", Types.INTEGER));
        declareInParameters();
        declareOutParameters();
        compile();
    }

    // Get the native connection
    protected Connection getNativeConn() throws SQLException {  
        if ((nativeConn == null) || nativeConn.isClosed()) {
            nativeConn = this.getJdbcTemplate().getNativeJdbcExtractor()
                .getNativeConnection(this.getJdbcTemplate()
                .getDataSource().getConnection());
        }
        return nativeConn;   
    }

    protected void declareInParameters() {
        declareParameter(new SqlParameter("p_geom_data",OracleTypes.STRUCT)); 
    }

    protected void declareOutParameters() {
        // declare out params
    }

    protected void setSQL() {
        setSql(CREATE_GEOMETRY_LINE);
    }

    /**
     * Execute the stored procedure
     * 
     * @param inParamMap a map of the stored procedure parameters
     */
    public Map executeMap(Map inParamMap) {
        Map outParamMap = null;
        try {  
            if (inParamMap.get("p_geom_data") != null) {
                java.util.List<Double> ordinates 
                    = (java.util.List<Double>) inParamMap.get("p_geom_data");
                double[] ordinateDoubles = new double[ordinates.size()];
                int count = 0;
                for (Double ordinate : ordinates) {
                    ordinateDoubles[count] = ordinate.doubleValue();
                    count++;
                }

                int[] elemInfo = new int[]{1,2,1};
                JGeometry j_geom = new JGeometry(
                    JGeometry.GTYPE_CURVE,8307, elemInfo,ordinateDoubles);        
                STRUCT obj = JGeometry.store(j_geom, getNativeConn());
                inParamMap.put("p_geom_data", obj);
            }

            outParamMap = super.execute(inParamMap);

        } catch (DataAccessException daex) {
            // throw exception
        } catch (SQLException e) {
            // throw exception
        }

        return outParamMap;
    }

}

The jdbcTemplate can be injected into the DAO's constructor. The extracts from the context xml are below:

    <bean id="datasource" class="org.springframework.jdbc.datasource.DriverManagerDataSource" 
        destroy-method="close">

        <property name="driverClassName">
            <value>oracle.jdbc.OracleDriver</value>
        </property>
        <property name="url">
            <value>jdbc:oracle:thin:@server:port:db</value>
        </property>
        <property name="username">
            <value>username</value>
        </property>
        <property name="password">
            <value>password</value>
        </property>
    </bean>

    <bean id="nativeJdbcExtractor" class="org.springframework.jdbc.support.nativejdbc.SimpleNativeJdbcExtractor" 
      lazy-init="true"/>

    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource">
            <ref bean="datasource" />
        </property>
        <property name="nativeJdbcExtractor">
            <ref bean="nativeJdbcExtractor" />
        </property>
    </bean>

    <bean id="createGeometryLineDAO" class="uk.co.city81.persistence.dao.geometry.CreateGeometryLineDAO">
        <constructor-arg ref="jdbcTemplate" />
    </bean>