Announcement Announcement Module
Collapse
No announcement yet.
Spring + postgres + stored procedure Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Spring + postgres + stored procedure

    Hi, I have stored procedures on postgres to call with spring
    this is merely function such as

    listerActionsModele (provide a list of Actions as a function !)

    declare curseur refcursor;
    begin
    open curseur for select l.idListeAction,
    l.nomlisteAction,
    a.identLovAction,
    a.fichierPicto,
    a.actionIHM,
    a.codeAction
    from listeAction l natural join lovAction a
    order by l.idListeAction;
    return curseur;


    my test code ......

    import javax.sql.DataSource;


    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.sql.Types;
    import java.util.HashMap;
    import java.util.Map;
    import java.util.Iterator;

    import org.springframework.jdbc.core.JdbcTemplate;
    import org.springframework.jdbc.core.SqlOutParameter;
    import org.springframework.jdbc.object.StoredProcedure;
    import org.springframework.jdbc.support.nativejdbc.Common sDbcpNativeJdbcExtractor;
    import org.springframework.jdbc.core.SqlParameter;
    //import org.springframework.jdbc.core.SqlOutParameter;
    import org.springframework.jdbc.datasource.*;
    import org.springframework.jdbc.core.SqlReturnResultSet;
    import org.springframework.jdbc.core.RowCallbackHandler;
    import org.springframework.jdbc.core.RowMapper;

    import org.springframework.jdbc.datasource.*;


    import org.springframework.dao.DataAccessException;
    import org.springframework.dao.InvalidDataAccessApiUsageE xception;
    import org.springframework.jdbc.BadSqlGrammarException;
    import org.springfram ework.jdbc.core.CallableStatementCreator;
    import org.springframework.jdbc.core.ParameterMapper;
    import org.springframework.jdbc.core.RowCallbackHandler;
    import org.springframework.jdbc.core.RowMapper;
    import org.springframework.jdbc.core.SqlParameter;
    import org.springframework.jdbc.core.SqlReturnResultSet;
    import org.springframework.jdbc.datasource.ConnectionHold er;
    import org.springframework.jdbc.support.SQLExceptionTrans lator;
    import org.springframework.jdbc.support.SQLStateSQLExcept ionTranslator;
    import org.springframework.transaction.support.Transactio nSynchronizationManager;



    public class TestSP {

    public static void main(String[] args) {

    System.out.println("DB TestSP!");
    TestSP t = new TestSP();
    t.test();
    System.out.println("Done!");

    }

    void test() {
    DriverManagerDataSource ds = new DriverManagerDataSource();
    ds.setDriverClassName("org.postgresql.Driver");
    ds.setUrl("jdbcostgresql://10.10.10.130:5432/testdb");
    ds.setUsername("cosma");
    ds.setPassword("");

    MyStoredProcedure sproc = new MyStoredProcedure(ds);
    Map res = sproc.execute();
    printMap(res);


    System.out.println("fin execute" );


    }

    private class MyStoredProcedure extends StoredProcedure {
    public static final String SQL = "\"listerActionsModele\"";
    private int count = 0;

    public MyStoredProcedure(DataSource ds) {
    setDataSource(ds);
    setFunction(true);
    setSql(SQL);
    declareParameter(new SqlOutParameter("rs", Types.OTHER , new RowCallbackHandlerImpl()));


    //declareParameter(new SqlReturnResultSet("rs", new RowCallbackHandlerImpl()));
    /*

    declareParameter(new SqlReturnResultSet("rs", new RowMapper() {
    public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
    Action o = new Action();
    o.setIdentlisteaction(rs.getInt(1));
    o.setIdentlovaction(rs.getInt(1));
    // add more mappings here ????
    return o;
    }
    }));
    */

    compile();
    }

    public int getCount() {
    return count;
    }

    private class RowCallbackHandlerImpl implements RowCallbackHandler {
    public void processRow(ResultSet rs) throws SQLException {
    while (rs.next()) {
    System.out.println(rs.getInt(1));
    }
    count++;
    }
    }

    public Map execute() {
    Map out = execute(new HashMap());
    System.out.println(out);
    return out;
    }
    }

    private static void printMap(Map r) {
    Iterator i = r.entrySet().iterator();
    while (i.hasNext()) {
    System.out.println((String) i.next().toString());
    }
    }
    }

    end;

    and the error ....

    DB TestSP!
    1 sept. 2004 14:32:58 org.springframework.jdbc.datasource.DriverManagerD ataSource setDriverClassName
    INFO: Loaded JDBC driver: org.postgresql.Driver
    1 sept. 2004 14:32:58 org.springframework.beans.factory.xml.XmlBeanDefin itionReader loadBeanDefinitions
    INFO: Loading XML bean definitions from class path resource [org/springframework/jdbc/support/sql-error-codes.xml]
    1 sept. 2004 14:32:58 org.springframework.beans.factory.support.Abstract BeanFactory getBean
    INFO: Creating shared instance of singleton bean 'DB2'
    1 sept. 2004 14:32:59 org.springframework.beans.factory.support.Abstract BeanFactory getBean
    INFO: Creating shared instance of singleton bean 'HSQL'
    1 sept. 2004 14:32:59 org.springframework.beans.factory.support.Abstract BeanFactory getBean
    INFO: Creating shared instance of singleton bean 'MS-SQL'
    1 sept. 2004 14:32:59 org.springframework.beans.factory.support.Abstract BeanFactory getBean
    INFO: Creating shared instance of singleton bean 'MySQL'
    1 sept. 2004 14:32:59 org.springframework.beans.factory.support.Abstract BeanFactory getBean
    INFO: Creating shared instance of singleton bean 'Oracle'
    1 sept. 2004 14:32:59 org.springframework.beans.factory.support.Abstract BeanFactory getBean
    INFO: Creating shared instance of singleton bean 'Informix'
    1 sept. 2004 14:32:59 org.springframework.beans.factory.support.Abstract BeanFactory getBean
    INFO: Creating shared instance of singleton bean 'PostgreSQL'
    1 sept. 2004 14:32:59 org.springframework.beans.factory.support.Abstract BeanFactory getBean
    INFO: Creating shared instance of singleton bean 'Sybase'
    1 sept. 2004 14:32:59 org.springframework.jdbc.support.SQLErrorCodesFact ory <init>
    INFO: SQLErrorCodes loaded: [HSQL Database Engine, Oracle, Sybase SQL Server, Microsoft SQL Server, Informix Dynamic Server, PostgreSQL, MySQL, DB2]
    1 sept. 2004 14:32:59 org.springframework.jdbc.support.SQLErrorCodesFact ory getErrorCodes
    INFO: Looking up default SQLErrorCodes for DataSource
    1 sept. 2004 14:32:59 org.springframework.jdbc.datasource.DriverManagerD ataSource getConnectionFromDriverManager
    INFO: Creating new JDBC connection to [jdbcostgresql://10.10.10.130:5432/testdb]
    1 sept. 2004 14:32:59 org.springframework.jdbc.support.SQLErrorCodesFact ory getErrorCodes
    INFO: Database Product Name is PostgreSQL
    1 sept. 2004 14:32:59 org.springframework.jdbc.support.SQLErrorCodesFact ory getErrorCodes
    INFO: Driver Version is PostgreSQL 7.4.3 JDBC3 with SSL (build 214)
    1 sept. 2004 14:32:59 org.springframework.jdbc.object.SqlCall compileInternal
    INFO: Compiled stored procedure. Call string is [{? = call "listerActionsModele"()}]
    1 sept. 2004 14:32:59 org.springframework.jdbc.object.RdbmsOperation compile
    INFO: RdbmsOperation with SQL ["listerActionsModele"] compiled
    1 sept. 2004 14:32:59 org.springframework.jdbc.datasource.DriverManagerD ataSource getConnectionFromDriverManager
    INFO: Creating new JDBC connection to [jdbcostgresql://10.10.10.130:5432/testdb]
    1 sept. 2004 14:32:59 org.springframework.jdbc.core.JdbcTemplate extractReturnedResultSets
    ATTENTION: ResultSet returned from stored procedure but a corresponding SqlReturnResultSet parameter was not declared
    1 sept. 2004 14:32:59 org.springframework.jdbc.support.SQLErrorCodeSQLEx ceptionTranslator translate
    ATTENTION: Unable to translate SQLException with errorCode '0', will now try the fallback translator
    1 sept. 2004 14:32:59 org.springframework.jdbc.support.SQLStateSQLExcept ionTranslator translate
    INFO: Translating SQLException with SQLState '34000' and errorCode '0' and message [ERROR: cursor "<unnamed portal 1>" does not exist
    ]; SQL was [{? = call "listerActionsModeleBis"()}] for task [executing CallableStatementCallback [CallableStatementCreatorFactory.CallableStatementC reatorImpl: sql=[{? = call "listerActionsModeleBis"()}]: params=[{}]]]
    org.springframework.jdbc.UncategorizedSQLException : (executing CallableStatementCallback [CallableStatementCreatorFactory.CallableStatementC reatorImpl: sql=[{? = call "listerActionsModeleBis"()}]: params=[{}]]): encountered SQLException [ERROR: cursor "<unnamed portal 1>" does not exist
    ]; nested exception is org.postgresql.util.PSQLException: ERROR: cursor "<unnamed portal 1>" does not exist

    org.postgresql.util.PSQLException: ERROR: cursor "<unnamed portal 1>" does not exist

    at org.postgresql.util.PSQLException.parseServerError (PSQLException.java:139)
    at org.postgresql.core.QueryExecutor.executeV3(QueryE xecutor.java:152)
    at org.postgresql.core.QueryExecutor.execute(QueryExe cutor.java:100)
    at org.postgresql.core.QueryExecutor.execute(QueryExe cutor.java:67)
    at org.postgresql.jdbc3.Jdbc3RefCursorResultSet.next( Jdbc3RefCursorResultSet.java:42)
    at org.springframework.jdbc.core.JdbcTemplate$RowCall backHandlerResultSetExtractor.extractData(JdbcTemp late.java:780)
    at org.springframework.jdbc.core.JdbcTemplate.process ResultSet(JdbcTemplate.java:617)
    at org.springframework.jdbc.core.JdbcTemplate.extract OutputParameters(JdbcTemplate.java:581)
    at org.springframework.jdbc.core.JdbcTemplate$7.doInC allableStatement(JdbcTemplate.java:530)
    at org.springframework.jdbc.core.JdbcTemplate.execute (JdbcTemplate.java:500)
    at org.springframework.jdbc.core.JdbcTemplate.call(Jd bcTemplate.java:520)
    at org.springframework.jdbc.object.StoredProcedure.ex ecute(StoredProcedure.java:101)
    at TestSP$MyStoredProcedure.execute(TestSP.java:131)
    at TestSP.test(TestSP.java:77)
    at TestSP.main(TestSP.java:64)


    ??? I do not find standard code in the doc and cannot find solution myself
    with RowMapper or anything else
    please help or propose code, thanks in advance.
    Your dummy user

  • #2
    I spent about 1h trying to resolve this problem :cry:
    1.configuration
    - postgresql 7.2 on windows XP
    - jdbc driver: pg74.215.jdbc3.jar
    - jdk 1.4.2_03
    - Spring: 1.1 from CVS
    2.table users
    Code:
    select id, login from users;
     id | login
    ----+--------
      0 | system
      1 | admin
      2 | guest
      3 | taha
    &#40;4 rows&#41;
    3.stored procedure
    Code:
    CREATE FUNCTION demo&#40;&#41; RETURNS refcursor AS '
    DECLARE
        ref refcursor;
    BEGIN
        OPEN ref FOR SELECT id, login FROM users;
        RETURN ref;
    END;
    ' LANGUAGE plpgsql;
    4.java class
    Code:
    public class Testos &#123;
     public static void main&#40;String&#91;&#93; args&#41; throws Exception &#123;
        new Testos&#40;&#41;.execute&#40;&#41;;
     &#125;
    
      public void execute&#40;&#41; throws Exception &#123;
       //some DI will be welcome here
        BasicDataSource ds = new BasicDataSource&#40;&#41;;
        ds.setDriverClassName&#40;"org.postgresql.Driver"&#41;;
        ds.setUrl&#40;"jdbc&#58;postgresql&#58;//localhost&#58;5432/xdocs"&#41;;
        ds.setUsername&#40;"xdocs"&#41;;
        ds.setPassword&#40;"xdocs"&#41;;
        //do not forget to turn autocommit false,
        //otherwise, the refcursor will be closed before the data is read
        ds.setDefaultAutoCommit &#40;false&#41;;
    
        DemoStoredProcedure proc = new DemoStoredProcedure&#40;ds&#41;;
        Map params = new HashMap&#40;&#41;;
        proc.execute&#40;params&#41;;
    
        ds.close&#40;&#41;;
      &#125;
    
      private class DemoStoredProcedure extends StoredProcedure &#123;
        public static final String SQL = "demo";
        public DemoStoredProcedure&#40;DataSource ds&#41; &#123;
          setDataSource&#40;ds&#41;;
          setSql&#40;SQL&#41;;
          declareParameter&#40;new SqlReturnResultSet&#40;"refcur", new DemoRowMapper&#40;&#41;&#41;&#41;;
          compile&#40;&#41;;
        &#125;
      &#125;
    
      private class DemoRowMapper implements RowCallbackHandler &#123;
        public void processRow&#40;ResultSet rs&#41; throws SQLException &#123;
          //get our ref cursor
          ResultSet cur = &#40;ResultSet&#41; rs.getObject&#40;1&#41;;
          while &#40;cur.next&#40;&#41;&#41; &#123;
            System.out.println&#40;cur.getInt&#40;1&#41; + " - " + cur.getString&#40;2&#41;&#41;;
          &#125;
          cur.close&#40;&#41;;
        &#125;
      &#125;
    &#125;
    5.comments
    - you must turn false autocommit, otherwise, the refcursor will be closed before the data is read by the java class.
    - since the stored procedure returns a refcursor, you should use :
    Resultset cur = (ResultSet) rs.getObject(1);
    - demo is a function, but we can not use setFunction(true)

    Comment


    • #3
      thanks for your time

      It's work ! Many thanks for your time

      just one thing, how can I adapt this code to return a List of Items coming from the rs ?

      Does spring provide tools like jakarta dbutils ?

      Comment


      • #4
        Change the DemoRowMapper to a true RowMapper This should give you a List of User objects.

        Code:
        	  private class DemoRowMapper implements RowMapper &#123;
        		public Object mapRow&#40;ResultSet rs, int rowNum&#41; throws SQLException &#123;
        			User u = new User&#40;&#41;;
        			u.setId = rs.getInt&#40;1&#41;;
        			u.setLogin = rs.getString&#40;2&#41;;
        			return u;
        		&#125;
        	  &#125;
        Thanks for the example, Omar. I never got this to work on Postgres before, but I think I was using an older JDBC driver, at that point, that did not support ref cursors.

        Comment


        • #5
          last stone for the solution

          at the end how do you get the List from the execute method ?

          give full sample code as a reference

          thanks
          your dummy user :lol:
          :oops:

          Comment


          • #6
            The execute method returns a Map with one entry for each out parameter keyed by the name specified when you declare the parameters. So this code would retrive the List returned for the "refcur" parameter declared in DemoStoredProcedure:
            Code:
                DemoStoredProcedure proc = new DemoStoredProcedure&#40;ds&#41;;
                Map params = new HashMap&#40;&#41;;
                Map results = proc.execute&#40;params&#41;;
                List refcurRows = results.get&#40;"refcur"&#41;;

            Comment


            • #7
              postgresql jdbc version

              I did not find the method setDefaultAutoCommit() in pg74.216.jdbc3.jar, but I found it in postgresql 8.* jdbc 3. So I am wondering how you could get around to call this method without upgrading the postgres to version 8.

              The setDefaultAutoCommit() method was found in org.postgresql.ds.PGConnectionPoolDataSource class in postgresql.8.0-315.jdbc3.jar.

              Do you have other way if I prefer not to upgrade postgres to version 8 ?

              Thanks,
              SpringNaive

              Comment

              Working...
              X