10 Replies Latest reply on Feb 8, 2010 1:45 PM by vrxyz

    How do you call a Stored Procedure?

    alringer

      I need to call a stored procedure in MySQL that creates some temp tables that I need to query.


      Is there a way to do this in Seam, or do I need to create my own JDBC utilities?



        • 1. Re: How do you call a Stored Procedure?
          tom_goring

          no seam support for stored proc's.


          you could use some spring lib's and use the spring-seam support.


          If it's just one you maybe better just calling the SP with some JDBC in your session bean.

          • 2. Re: How do you call a Stored Procedure?
            pmuir

            Nothing particularly to do with Seam - this is really up to whatever persistence layer you use e.g. JPA.

            • 3. Re: How do you call a Stored Procedure?
              stells

              Hello, Marc!


              I face the same problem right now. (I must to use legacy stored proc's while I need JPA also).


              What solution have you implemented?


              Thank you in advance!


              Michail

              • 4. Re: How do you call a Stored Procedure?
                alringer
                I just implemented a DatabaseUtility class. Here is an example. of one of the stored procs being called...



                public static BigDecimal calcDistance(String zipcode, BigDecimal lat, BigDecimal lon) throws SQLException
                        {
                                BigDecimal result = null;
                                DataSource datasource = null;
                                try
                                {
                                        InitialContext context = new InitialContext();
                                        datasource = (DataSource)context.lookup("java:/MyDatasource");
                                }
                                catch(NamingException e)
                                {
                                        assert false : "Unable to create directory context!";
                                }
                                       
                                Connection connection = datasource.getConnection();
                                       
                                // Call a procedure with one n-IN/1-OUT parameters
                                CallableStatement statement = connection.prepareCall("{call CalcDistance(?, ?, ?, ?)}");
                               
                                try
                                {
                                               
                                        statement.setString(1, zipcode);
                                        statement.setBigDecimal(2, lat);
                                        statement.setBigDecimal(3, lon);
                       
                                        statement.registerOutParameter(4, Types.DOUBLE);
                       
                                        statement.execute();
                                       
                                        result = statement.getBigDecimal(4);

                                }
                                catch (SQLException e)
                                {
                                        log.error("sql error " + e);
                                }

                                finally
                                {
                                        if(statement != null)
                                        {
                                                statement.close();
                                        }
                                        if(connection != null)
                                        {
                                                connection.close();
                                        }
                                }
                               
                                return result;
                        }
                • 5. Re: How do you call a Stored Procedure?

                  I had posted here before, but my post got deleted (did I say something wrong?):


                  Where I work, we have 2 projects with the same situation, one of them uses Seam+Spring+Ibatis+POJOasDTO and the other Seam+Spring+Spring-Jdbc+MapasDTO.


                  I have found that Seam+Spring+Ibatis works find with big projects, but with small project it becomes cumbersome to maintain the daos and the xml files, for those it si better to use Seam+Spring+Spring-Jdbc+MapasDTO. (Of course it is alwasy better to use Seam+JPA, but sometimes that is not an option)


                  I do not think it is a good idea to use Seam+PlainJDBC, it is just to easy to make mistakes, and you end up wrinting an insane amount of code even for very simple things (like in your previous example)


                  What we do is that we configure the datasource with JNDI, link to it using Spring and then configure a JdbcTemplate bean linked to that datasource, then we inject it to our Seam component:


                  
                  import org.springframework.jdbc.core.simple.SimpleJdbcTemplate;
                  
                  @Name("seamComponent");
                  public SeamComponent{
                  
                     @In("#{jdbcTemplate}")     
                     private transient SimpleJdbcTemplate jdbcTemplate;
                     
                  }
                  
                  



                  Spring works really good as a glue between Seam and Spring-Jdbc or Ibatis.


                  Regards,

                  • 6. Re: How do you call a Stored Procedure?

                    With Seam+Spring+Spring-Jdbc your example would look like this:


                    import org.springframework.jdbc.core.simple.SimpleJdbcTemplate;
                    
                    @Name("seamComponent");
                    public SeamComponent{
                    
                       @In("#{jdbcTemplate}")
                    
                    public static BigDecimal calcDistance(String zipcode, BigDecimal lat, BigDecimal lon) throws
                    {
                         ConnectionCallbak connectionCallBack = new ConnectionCallback() {
                              @Override
                              public Object doInConnection(Connection conexion_db)
                                        throws SQLException, DataAccessException {
                                             CallableStatement statement = connection.prepareCall("{call CalcDistance(?, ?, ?, ?)}");
                                        statement.setString(1, zipcode);
                                        statement.setBigDecimal(2, lat);
                                        statement.setBigDecimal(3, lon);
                                        statement.registerOutParameter(4, Types.DOUBLE);
                                        statement.execute();
                                        result = statement.getBigDecimal(4);
                                        return result;
                                        }
                    
                         });
                         BigDecimal result = (BigDecimal) template.execute(connectionCallBack);
                         return result;               
                    }
                    



                    As you can see the code reduces significantly, and that is not the only advantage: now the Connection is automatically handled by Spring so you do not have to waste time writing try{}catch{}finally{} nor risk forgetting or not writing them if you are in a hurry.


                    Regards,

                    • 7. Re: How do you call a Stored Procedure?

                      Ha! Sorry, forgot to declare the variable in my previous post:


                      This:


                      
                      @Name("seamComponent");
                      public SeamComponent{
                      
                         @In("#{jdbcTemplate}")
                         
                      
                      public static BigDecimal calcDistance(String zipcode, BigDecimal lat, BigDecimal lon) throws
                      {
                      
                      
                      




                      Should be this:


                      @Name("seamComponent");
                      public SeamComponent{
                      
                         @In("#{jdbcTemplate}")
                         private transient SimpleJdbcTemplate jdbcTemplate;
                      
                      
                      public static BigDecimal calcDistance(String zipcode, BigDecimal lat, BigDecimal lon)
                      {
                      
                      



                      Oh, and the throws in calcDistance is no longer necessary, Spring also handles checked exceptions for you.


                      I think Seam and Spring make a really good combination.


                      Regards,

                      • 8. Re: How do you call a Stored Procedure?
                        gonorrhea

                        Pete Muir wrote on Apr 14, 2008 00:23:


                        Nothing particularly to do with Seam - this is really up to whatever persistence layer you use e.g. JPA.


                        I disagree.  See my recent thread: My Link


                        Seam needs to provide a JDBC API just like Spring does...


                        Francisco's Seam/Spring integration example is a good idea but we Seam developers should not have to rely on Spring API for something as basic as JDBC...


                        Is there official reference documentation (or in a Seam book) on how to do the Seam/Spring integration regarding JdbcTemplate usage?

                        • 9. Re: How do you call a Stored Procedure?

                          Arbi Sookazian wrote on Jun 18, 2009 08:05:



                          Pete Muir wrote on Apr 14, 2008 00:23:


                          Nothing particularly to do with Seam - this is really up to whatever persistence layer you use e.g. JPA.


                          I disagree.  See my recent thread: My Link

                          Seam needs to provide a JDBC API just like Spring does...


                          Feel free to reinvent the wheel, but I see no point in doing so...



                          Francisco's Seam/Spring integration example is a good idea but we Seam developers should not have to rely on Spring API for something as basic as JDBC...

                          Is there official reference documentation (or in a Seam book) on how to do the Seam/Spring integration regarding JdbcTemplate usage?


                          There aint much to say, Dan Allen book covers how to create a datasource in Spring that is used by a Seam application, after that all you have to do is connect your jdbcTemplate with your dataSource

                          • 10. Re: How do you call a Stored Procedure?
                            vrxyz

                            Hi,


                            Does that mean I have to download Spring framework and configure everything there? All I just have to do is inject it to Seam?
                            Just to make sure I'm following. Thank you :)