Announcement Announcement Module
Collapse
No announcement yet.
Calling Stored Procedure Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Calling Stored Procedure

    Invoking a simple stored procedure (more sophisticated stored procedure support is covered later).
    http://static.springsource.org/sprin...ence/jdbc.html

    What is the difference between simple and sophisticated ??

    I want to call a stored procedure from my DAO but not wait for it to complete and move on with my further logic. What is the correct way? - using call/update or SimpleJdbcCall ?

  • #2
    Here is your answer

    Look, I made a mini framework that extends from Spring 2. First at all, we make a Abstract Class from Spring StoredProcedure:
    Code:
    package com.example.storedprocedures;
    
    import java.util.HashMap;
    import java.util.Map;
    
    import javax.sql.DataSource;
    
    import org.springframework.dao.DataAccessException;
    import org.springframework.jdbc.core.JdbcTemplate;
    import org.springframework.jdbc.core.SqlParameter;
    import org.springframework.jdbc.object.StoredProcedure;
    
    import com.example.storedprocedures.exceptions.StoredProcedureException;
    
    public abstract class JDBCStoredProcedure extends StoredProcedure {
    
        protected static final String IN_PARAMETER_ERROR = "Error, los par\u00E1metros de entrada no deben ser nulos.";
    
        protected static final String DECLARED_IO_PARAMETER_ERROR = "Error, debe declarar al menos un par\u00E1metro I/O para el procedimiento almacenado. Adem\u00E1s no deben ser nulos.";
    
        protected static final String CONFIGURATION_ERROR = "Error al configurar procedimiento almacenado ";
    
        protected static final String EXECUTION_ERROR = "No fue posible ejecutar la sentencia ";
    
        public JDBCStoredProcedure(String schema, String query, DataSource dataSource, SqlParameter[] parameters) throws StoredProcedureException {
            this(schema, query, new JdbcTemplate(dataSource), parameters);
        }
    
        public JDBCStoredProcedure(String schema, String query, JdbcTemplate template, SqlParameter[] parameters) throws StoredProcedureException {
            super(template, schema != null && !"".equalsIgnoreCase(schema) ? schema + "." + query : query);
            this.declareParametersAndCompile(parameters);
        }
    
        private void declareParametersAndCompile(SqlParameter[] parameters) throws StoredProcedureException {
            try {
                if (parameters == null) {
                    throw new IllegalArgumentException(DECLARED_IO_PARAMETER_ERROR);
                }
    
                for (int index = 0; index < parameters.length; index++) {
                    if (parameters[index] == null) {
                        throw new IllegalArgumentException(DECLARED_IO_PARAMETER_ERROR + " [Indice: " + index + "]");
                    }
    
                    this.declareParameter(parameters[index]);
                }
    
                this.compile();
            } catch (IllegalArgumentException exception) {
                throw new StoredProcedureException(CONFIGURATION_ERROR + this.getCallString() + ".", exception);
            } catch (DataAccessException exception) {
                throw new StoredProcedureException(CONFIGURATION_ERROR + this.getCallString() + ".", exception);
            }
        }
    
        public Map execute() throws StoredProcedureException {
            return this.execute(new HashMap(0));
        }
    
        public Map execute(Map inParameters) throws StoredProcedureException {
            try {
                if (inParameters == null) {
                    throw new IllegalArgumentException(IN_PARAMETER_ERROR);
                }
    
                return super.execute(inParameters);
            } catch (IllegalArgumentException exception) {
                throw new StoredProcedureException(EXECUTION_ERROR + this.getCallString() + ".", exception);
            } catch (DataAccessException exception) {
                throw new StoredProcedureException(EXECUTION_ERROR + this.getCallString() + ".", exception);
            } catch (Exception exception) {
                throw new StoredProcedureException(EXECUTION_ERROR + this.getCallString() + ".", exception);
            }
        }
    
    }
    Now you can extend your own stored procedures like:
    Code:
    package com.example.storedprocedures.implementations;
    
    import javax.sql.DataSource;
    
    import org.springframework.jdbc.core.SqlParameter;
    
    import com.example.storedprocedures.JDBCStoredProcedure;
    import com.example.storedprocedures.exceptions.StoredProcedureException;
    
    public class SimpleStoredProcedure extends JDBCStoredProcedure {
    
        public SimpleStoredProcedure(String schema, String query, DataSource dataSource, SqlParameter[] parameters) throws StoredProcedureException {
            super(schema, query, dataSource, parameters);
        }
    
        public SimpleStoredProcedure(String query, DataSource dataSource, SqlParameter[] parameters) throws StoredProcedureException {
            super(null, query, dataSource, parameters);
        }
    
    }
    Last edited by Gabejazz; Aug 28th, 2009, 02:07 PM.

    Comment


    • #3
      Here is your answer #2

      For example, if you want a transactional stored procedure you can make it like:
      Code:
      package com.example.storedprocedures.implementations;
      
      import java.util.Map;
      
      import javax.sql.DataSource;
      
      import org.springframework.jdbc.core.JdbcTemplate;
      import org.springframework.jdbc.core.SqlParameter;
      import org.springframework.transaction.PlatformTransactionManager;
      import org.springframework.transaction.TransactionStatus;
      import org.springframework.transaction.support.TransactionCallback;
      import org.springframework.transaction.support.TransactionTemplate;
      
      import com.example.storedprocedures.JDBCStoredProcedure;
      import com.example.storedprocedures.exceptions.StoredProcedureException;
      
      public class TransactionalStoredProcedure extends JDBCStoredProcedure {
      
          protected static final String TRANSACTION_TEMPLATE_ERROR = "Error, tanto transactionTemplate como transactionManager no deben ser nulos.";
      
          private TransactionTemplate transactionTemplate;
      
          public TransactionalStoredProcedure(String schema, String query, DataSource dataSource, PlatformTransactionManager transactionManager, SqlParameter[] parameters) throws StoredProcedureException {
              this(schema, query, new JdbcTemplate(dataSource), new TransactionTemplate(transactionManager), parameters);
          }
      
          public TransactionalStoredProcedure(String schema, String query, DataSource dataSource, TransactionTemplate transactionTemplate, SqlParameter[] parameters) throws StoredProcedureException {
              this(schema, query, new JdbcTemplate(dataSource), transactionTemplate, parameters);
          }
      
          public TransactionalStoredProcedure(String schema, String query, JdbcTemplate template, PlatformTransactionManager transactionManager, SqlParameter[] parameters) throws StoredProcedureException {
              this(schema, query, template, new TransactionTemplate(transactionManager), parameters);
          }
      
          public TransactionalStoredProcedure(String schema, String query, JdbcTemplate template, TransactionTemplate transactionTemplate, SqlParameter[] parameters) throws StoredProcedureException {
              super(schema, query, template, parameters);
              this.setTransactionTemplate(transactionTemplate);
          }
      
          public TransactionTemplate getTransactionTemplate() {
              return this.transactionTemplate;
          }
      
          public void setTransactionTemplate(TransactionTemplate transactionTemplate) {
              try {
                  if (transactionTemplate == null || transactionTemplate.getTransactionManager() == null) {
                      throw new IllegalArgumentException(TRANSACTION_TEMPLATE_ERROR);
                  }
      
                  this.transactionTemplate = transactionTemplate;
              } catch (IllegalArgumentException exception) {
                  throw new StoredProcedureException(CONFIGURATION_ERROR + this.getCallString() + ".", exception);
              }
          }
      
          public Map execute() throws StoredProcedureException {
              return (Map) this.transactionTemplate.execute(new TransactionCallback() {
      
                  public Object doInTransaction(TransactionStatus status) {
                      return executeMe();
                  }
      
              });
          }
      
          public Map execute(final Map inParameters) throws StoredProcedureException {
              return (Map) this.transactionTemplate.execute(new TransactionCallback() {
      
                  public Object doInTransaction(TransactionStatus status) {
                      return executeMe(inParameters);
                  }
      
              });
          }
      
          private Map executeMe() throws StoredProcedureException {
              return super.execute();
          }
      
          private Map executeMe(Map inParameters) throws StoredProcedureException {
              return super.execute(inParameters);
          }
      
      }
      And now, you can configure all your stored procedures you want:
      Code:
      <?xml version="1.0" encoding="UTF-8"?>
      <beans xmlns="http://www.springframework.org/schema/beans" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:util="http://www.springframework.org/schema/util" xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-2.5.xsd http://www.springframework.org/schema/util http://www.springframework.org/schema/util/spring-util-2.5.xsd">
          <bean class="com.example.storedprocedures.implementations.SimpleStoredProcedure">
              <constructor-arg index="0" type="java.lang.String" value="MySPCallName" />
              <constructor-arg index="1" ref="my-ds-reference" />
              <constructor-arg index="2">
                  <list>
                      <bean name="INPARAM01" class="org.springframework.jdbc.core.SqlParameter">
                          <constructor-arg index="0" type="java.lang.String">
                              <util:constant static-field="com.example.storedprocedures.ServiceConstants.INPARAM01" />
                          </constructor-arg>
                          <constructor-arg index="1" type="int">
                              <util:constant static-field="java.sql.Types.INTEGER" />
                          </constructor-arg>
                      </bean>
                      <bean name="INPARAM02" class="org.springframework.jdbc.core.SqlParameter">
                          <constructor-arg index="0" type="java.lang.String">
                              <util:constant static-field="com.example.storedprocedures.ServiceConstants.INPARAM02" />
                          </constructor-arg>
                          <constructor-arg index="1" type="int">
                              <util:constant static-field="java.sql.Types.VARCHAR" />
                          </constructor-arg>
                      </bean>
                      <bean name="RESULTSET" class="org.springframework.jdbc.core.SqlReturnResultSet">
                          <constructor-arg index="0" type="java.lang.String">
                              <util:constant static-field="com.example.storedprocedures.ServiceConstants.RESULT_ID" />
                          </constructor-arg>
                          <constructor-arg index="1" type="org.springframework.jdbc.core.RowMapper">
                              <bean class="com.example.storedprocedures.mappers.MyMapper" />
                          </constructor-arg>
                      </bean>
                  </list>
              </constructor-arg>
          </bean>
      </beans>
      Last edited by Gabejazz; Aug 28th, 2009, 02:42 PM.

      Comment


      • #4
        Here is your answer #3

        For Transactional SP, the xml would be like the above SimpleSP, but whit a few changes:
        Code:
        <bean class="com.example.storedprocedures.implementations.TransactionalStoredProcedure">
                        <constructor-arg index="0" type="java.lang.String" value="" />
                        <constructor-arg index="1" type="java.lang.String" value="mySPInsertOrUpdateCallName" />
                        <constructor-arg index="2" ref="myDataSourceReference" />
                        <constructor-arg index="3" ref="myTransactionTemplateReference" />
                        <constructor-arg index="4">
                            <list>
                                ...
                                ...
                                ...
                                etc, etc, etc
        Finally, in your DAO you have to call to your SP like.
        Code:
        Map inParameters = new HashMap(0);
        inParameters.put(ServiceConstants.INPARAM01, new Integer(1));
        inParameters.put(ServiceConstants.INPARAM02, "somevarchardata");
        Map outParameters = this.storedProcedure.execute(inParameters);
        (List) outParameters.get(ServiceConstants.RESULT_ID);
        And that's it. Hope that I could help you. CYA!!!
        Last edited by Gabejazz; Aug 28th, 2009, 02:25 PM.

        Comment


        • #5
          My question is simple :

          What is the difference between
          Code:
          this.jdbcTemplate.update(
                  "call SUPPORT.REFRESH_ACTORS_SUMMARY(?)", 
                  new Object[]{Long.valueOf(unionId)});
          and

          Code:
          org.springframework.jdbc.object.StoredProcedure;

          Comment


          • #6
            The first one is used for manual programming and involves much maintenance, the second one is used for more automated apps. In fact, StoredProcedure use jdbcTemplate for execute the calls.
            Last edited by Gabejazz; Aug 29th, 2009, 11:54 AM.

            Comment

            Working...
            X