Announcement Announcement Module
Collapse
No announcement yet.
Problems trying to call stored procedure through Spring Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Problems trying to call stored procedure through Spring

    Hi,

    I am completely new to Spring, having never used it before until very recently. I am trying to integrate an example Spring application into our current portal application as a test.

    I am currently having a lot of trouble trying to call a stored procedure, I am able to make SQL calls directly.

    This is the error I am getting when I start tomcat:

    Initialization of bean failed; nested exception is org.springframework.beans.factory.BeanCreationExce ption: Error creating bean with name 'exampleTwoController': Injecti
    on of autowired dependencies failed; nested exception is org.springframework.beans.factory.BeanCreationExce ption: Could not autowire field: javax.sql.DataSource ExampleTwoController.ds; nested exception is org.springframework.beans.factory.NoSuchBeanDefini tionException: No unique bean of type [ja
    vax.sql.DataSource] is defined: expected single matching bean but found 2: [oracleDevDataSource, mysqlDataSource]

    I have a class called MyDataSource which just takes a parameter to determine what connection settings to load. Am I only able to define a single datasource in the persistence.xml (this seems extremely unlikely)?

    Any pointers in the right direction would be greatly appreciated.

    Below is my config and classes:

    Code:
    public class ExampleTwoStoredProcedureCall extends StoredProcedure {    
        //private static final Log log = LogFactory.getLog(ExampleTwoStoredProcedureCall.class);
        private static final String SQL = "PKG_TEST.P_GET_DATA";
         
        public ExampleTwoStoredProcedureCall(DataSource dataSource) {
            super(dataSource, SQL);
            declareParameter(new SqlOutParameter("param_out",Types.NUMERIC));
            declareParameter(new SqlParameter("param_in",Types.NUMERIC));
            setFunction(true);//you must set this as it distinguishes it from a sproc
            compile();
        }
    
        public Map execute(Integer id) {
         Map outs = null;
         try{
              Map inputs = new HashMap();          
              inputs.put("param_in", id);
              outs = execute(inputs);
         }catch(Exception e){
           e.printStackTrace();
         }
              return outs;
        }
    }
    Code:
    @Controller
    @RequestMapping(value = "/*")
    public class ExampleTwoController extends AbstractController {    
        
        @Autowired DataSource ds;
        
       
        @RequestMapping(value = "main")
        public ModelAndView main(HttpServletRequest request, HttpServletResponse response) throws Exception {        
            ExampleTwoForm form = new ExampleTwoForm();
            form.fillFromRequest(request);        
            ModelAndView mav = init(request, response, "main", "Main");       
            ExampleTwoStoredProcedureCall mySp = new ExampleTwoStoredProcedureCall(ds);
            Map i = mySp.execute(1);
    
            mav.addObject("subtitle", "Main menu");
            mav.addObject("form", form);        
    
            return mav;
        }
    }
    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:aop="http://www.springframework.org/schema/aop"
        xmlns:context="http://www.springframework.org/schema/context"
        xsi:schemaLocation="
            http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
            http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-2.0.xsd">
    
        <!--
    
            Data source beans.
    
        -->
        
        <!-- oracle data source -->
        <bean id="oracleDevDataSource" class="MyDataSource">
            <constructor-arg value="ORACLE_DEV" />
        </bean>
    
       <bean id="ExampleTwoStoredProcedure" class="ExampleTwoStoredProcedureCall">
            <property name="dataSource" ref="oracleDevDataSource" />        
        </bean>
    
        <!--
    
            Other beans.
    
        -->
        <bean id="mysqlDataSource" class="MyDataSource">
            <constructor-arg value="MSSQL_DEV" />
        </bean>
    
        <!-- InternalDatabaseDetailsDao -->
        <bean id="internalDatabaseDetailsDao" class="InternalDatabaseDetailsDao">
            <property name="dataSource" ref="mysqlDataSource" />
        </bean>
    
    </beans>

  • #2
    Hi Paul,

    I have a class called MyDataSource which just takes a parameter to determine what connection settings to load. Am I only able to define a single datasource in the persistence.xml (this seems extremely unlikely)?
    I wouldn't say it's extremely unlikely, but "it's not quite common".
    This is not the only conflicting case with Spring, but you may face the same when working with DAOs and you have more than one implementation for a specific DAO interface.
    The same could apply to services, and whatever other interface you might have.

    Before autowiring, this was not a problem, as we directly injected beans using the identifiers. But in this case, you'll need to make use of the @Qualifier annotation. For instance, in order to inject the oracle data source into your ExampleTwoController:
    Code:
      @Autowired
      @Qualifier("oracleDevDataSource")
      DataSource ds;
    Another possibility:
    Code:
      @Autowired
      DataSource oracleDevDataSource;
    In the later case, spring will automatically autowire the oracle datasource matching with the attribute name to the bean identifier.

    Despite of I personally don't like to put name references in Java classes, I prefer to use @Qualifier, but that is up to you.

    Best,
    Carlos

    Comment


    • #3
      Hi Carlos,

      Thanks for the reply it was extremely helpful to me. I am now able to call my stored procedure after using:

      Code:
          @Autowired
          @Qualifier("oracleDevDataSource")
          DataSource ds;
      I also had to remove the bean mapping for my stored procedure class from persistence (I am not 100% sure why but I assume it is because of autowiring). I think the examples that I used did not use autowire which is why the bean was defined in persistence.xml. Is this correct?

      In relation but on a separate note, is this the best way to perform a stored procedure call, is it inline with the latest methods used by Spring developers? As always in programming there seems to be many ways to skin the same cat using Spring!

      Code:
          @Autowired
          @Qualifier("oracleDevDataSource")
          DataSource ds;
      
          //The call to the stored procedure implementation
          ExampleTwoStoredProcedureCall mySp = new ExampleTwoStoredProcedureCall(ds);
      Code:
      //Stored procedure implementation class
      public class ExampleTwoStoredProcedureCall extends StoredProcedure {    
          //private static final Log log = LogFactory.getLog(ExampleTwoStoredProcedureCall.class);
          private static final String SQL = "PKG_TEST.P_GET_DATA";
      
          public ExampleTwoStoredProcedureCall(DataSource dataSource) {
              super(dataSource, SQL);
              declareParameter(new SqlOutParameter("param_out",Types.NUMERIC));
              declareParameter(new SqlParameter("param_in",Types.NUMERIC));
              setFunction(true);//you must set this as it distinguishes it from a sproc
              compile();
          }
      
          public Map execute(Integer id) {
           Map outs = null;
           try{
                Map inputs = new HashMap();          
                inputs.put("param_in", id);
                outs = execute(inputs);
           }catch(Exception e){
             e.printStackTrace();
           }
                return outs;
          }
      }
      I am hopeful that Spring will be very beneficial to us once we have overcome the initial learning curve.

      Thanks,

      Paul

      Comment


      • #4
        I also had to remove the bean mapping for my stored procedure class from persistence (I am not 100% sure why but I assume it is because of autowiring). I think the examples that I used did not use autowire which is why the bean was defined in persistence.xml. Is this correct?
        I'd need to see the stacktrace, but I don't think you should stuck on this. Just let's continue with the implementation...that's much more funny

        In relation but on a separate note, is this the best way to perform a stored procedure call, is it inline with the latest methods used by Spring developers? As always in programming there seems to be many ways to skin the same cat using Spring!
        I'll try to answer this from a very neutral point of view, as this is a common start point for a never-ending debate.

        My suggestion is you separate things in layers this way:
        1) Controller class receiving requests from UI
        2) Service class implementing the business logic. You'll need to inject the service class into the controller. I kindly suggest to work with interface and implementation. The attribute type in Controller class should be the interface.
        3) DAO class providing access to the persistence layer. You'll need to inject the DAO into the Service class.
        Same comments as before regarding using interfaces.

        Now we have the backbones, you need to inject your ExampleTwoStoredProcedureCall into the DAO, and put logic to invoke it's "execute" method.

        Best,
        Carlos.

        Comment


        • #5
          Hi carlos thanks again for the reply.

          One final question, when you say 'inject' the service into the controller etc what exactly do you mean? Is it simply a case of just creating an object of the service implementation in the controller, for example:

          Code:
          @RequestMapping(value = "main")
              public ModelAndView main(HttpServletRequest request, HttpServletResponse response) throws Exception {        
                  Service myService = new MyImplementationService();      
                  ModelAndView mav = init(request, response, "main", "Main");       
                  myService.doWork();
                       
                  return mav;
              }
          Thanks,

          Paul

          Comment


          • #6
            Hi Paul,

            I was talking about defining your "MyImplementationService" as a spring bean: put @Component above the class declaration.
            Then, define a "Service" attribute inside your controller and put @Autowired (as same as in the case of the data source), and let Spring to automatically inject the service implementation into your controller class.

            Best,
            Carlos.

            Comment

            Working...
            X