Wednesday, 4 May 2011

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.


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) {
        declareParameter(new SqlOutParameter("l_Return", Types.INTEGER));

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

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

    protected void declareOutParameters() {
        // declare out params

    protected void setSQL() {

     * 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();

                int[] elemInfo = new int[]{1,2,1};
                JGeometry j_geom = new JGeometry(
                    JGeometry.GTYPE_CURVE,8307, elemInfo,ordinateDoubles);        
                STRUCT obj =, 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" 

        <property name="driverClassName">
        <property name="url">
        <property name="username">
        <property name="password">

    <bean id="nativeJdbcExtractor" class="" 

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

    <bean id="createGeometryLineDAO" class="">
        <constructor-arg ref="jdbcTemplate" />

No comments:

Post a Comment

Note: only a member of this blog may post a comment.