Announcement Announcement Module
Collapse
No announcement yet.
how to call store procedure in spring+hibernate Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • how to call store procedure in spring+hibernate

    hello all

    i write a store procedure in SQL Server 2005 which take two parameter as an input..
    i am planning to call and map the resultset as an entity..

    i write the following DAO to call the store procedure..

    ---------------------- DAO---------------------------------

    @SuppressWarnings("unchecked")
    public List<DetailQuestionnaireSummary> getDetailQuestionnaireSummaryReport(int surveyId,int courseId) {
    LoggingManager.log("Try to calling Store procedure DetailQuestionnaireSummary ",
    Level.INFO, null);
    List<DetailQuestionnaireSummary> list=null;

    try {
    session = getSession();
    // Transaction tx = session.beginTransaction();
    // Query query = session.createQuery(queryString);
    // list = query.list();
    // tx.commit();
    //

    list=session.getNamedQuery("getQuestionDetailSumma ryNativeSQL")
    .setInteger("SurveyId", 42)
    .setInteger("CourseId", 3)
    .list();

    for(DetailQuestionnaireSummary ss:list)
    {
    System.out.println("^^^^^^^^^^ "+ss.getQuestionId()+" "+ss.getQuestionDesc());

    }
    LoggingManager.log("successfully call Store procedure DetailQuestionnaireSummary and receive result ", Level.INFO, null);
    return list;

    } catch (HibernateException re) {
    //session.getTransaction().rollback();
    LoggingManager.log("failed to calling Store procedure DetailQuestionnaireSummary ", Level.SEVERE, re);
    throw re;
    }
    }
    --------------------------------End DAO------------------------------

    Entity to map the result set of the calling store procedure


    ---------------------- Entity---------------------

    @NamedNativeQueries({
    @NamedNativeQuery(
    name = "getQuestionDetailSummaryNativeSQL",
    query = "call GetQuestionsSummeryReport(?, :SurveyId, :CourseId)",
    resultClass = DetailQuestionnaireSummary.class
    )
    })
    @Entity

    public class DetailQuestionnaireSummary {

    private int questionId;
    private String questionDesc;
    private int totalYes;
    private int percentYes;
    private int totalNo;
    private int percentNo;
    private int totalStudents;

    /**
    *
    * @param questionId
    * @param questionDesc
    * @param totalYes
    * @param percentYes
    * @param totalNo
    * @param percentNo
    * @param totalStudents
    */
    public DetailQuestionnaireSummary(int questionId,String questionDesc,int totalYes, int percentYes,int totalNo
    ,int percentNo,int totalStudents)
    {
    this.questionId=questionId;
    this.questionDesc=questionDesc;
    this.totalYes=totalYes;
    this.percentYes=percentYes;
    this.totalNo=totalNo;
    this.percentNo=percentNo;
    this.totalStudents=totalStudents;
    }
    @Id
    @Column(name = "question_id")
    public int getQuestionId() {
    return questionId;
    }
    public void setQuestionId(int questionId) {
    this.questionId = questionId;
    }
    @Column(name = "question_desc")
    public String getQuestionDesc() {
    return questionDesc;
    }
    public void setQuestionDesc(String questionDesc) {
    this.questionDesc = questionDesc;
    }
    @Column(name = "total_yes")
    public int getTotalYes() {
    return totalYes;
    }
    public void setTotalYes(int totalYes) {
    this.totalYes = totalYes;
    }
    @Column(name = "percent_yes")
    public int getPercentYes() {
    return percentYes;
    }
    public void setPercentYes(int percentYes) {
    this.percentYes = percentYes;
    }
    @Column(name = "total_no")
    public int getTotalNo() {
    return totalNo;
    }
    public void setTotalNo(int totalNo) {
    this.totalNo = totalNo;
    }
    @Column(name = "percent_no")
    public int getPercentNo() {
    return percentNo;
    }
    public void setPercentNo(int percentNo) {
    this.percentNo = percentNo;
    }
    @Column(name = "total_students")
    public int getTotalStudents() {
    return totalStudents;
    }
    public void setTotalStudents(int totalStudents) {
    this.totalStudents = totalStudents;
    }

    }
    ------------------------- End Entity--------------------



    when ever i call the procedure i find that exception

    org.hibernate.QueryException: Expected positional parameter count: 1, actual parameters: [] [call GetQuestionsSummeryReport(?, :SurveyId, :CourseId)]


    i not understand what is the correct way to pass parameter.. i also try with this

    list=session.getNamedQuery("getQuestionDetailSumma ryNativeSQL")
    .setParameter("SurveyId", keyWord)
    .setParameter("CourseId", "OC25")
    .list();

    bt same exception occure......

    please can any One suggest me the coreect way to call procedure..

    waiting for response

  • #2
    It is expecting a binded value for the "?" question mark in your query. Check this post, it might help you to resolve this problem:

    http://forum.springsource.org/showthread.php?t=27287

    Comment


    • #3
      yap i realize it and correct it bt still finding problem


      --------------- Entity--------------------
      @org.hibernate.annotations.NamedNativeQuery(name = "getQuestionDetailSummaryNativeSQL",
      query = "call GetQuestionsSummeryReport(:surveyId,:courseId)",
      callable = true, resultClass = DetailQuestionnaireSummary.class)

      @Entity
      public class DetailQuestionnaireSummary implements java.io.Serializable{

      ..

      ...
      }

      ----------------------------------End



      in DAO

      -------------------------- DAO------------------------------

      Transaction tx = session.beginTransaction();

      list=session.getNamedQuery("getQuestionDetailSumma ryNativeSQL")
      .setParameter("surveyId", "42")
      .setParameter("courseId", "1")
      .list();
      --------------------------------- End DAO ----------------------


      now getting Exception


      ----------------------Exception--------------------------------



      org.hibernate.exception.GenericJDBCException: could not execute query
      at org.hibernate.exception.SQLStateConverter.handledN onSpecificException(SQLStateConverter.java:103)
      at org.hibernate.exception.SQLStateConverter.convert( SQLStateConverter.java:91)
      at org.hibernate.exception.JDBCExceptionHelper.conver t(JDBCExceptionHelper.java:43)
      at org.hibernate.loader.Loader.doList(Loader.java:222 3)
      at org.hibernate.loader.Loader.listIgnoreQueryCache(L oader.java:2104)
      at org.hibernate.loader.Loader.list(Loader.java:2099)
      at org.hibernate.loader.custom.CustomLoader.list(Cust omLoader.java:289)
      at org.hibernate.impl.SessionImpl.listCustomQuery(Ses sionImpl.java:1695)
      at org.hibernate.impl.AbstractSessionImpl.list(Abstra ctSessionImpl.java:142)
      at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl. java:152)
      at com.dps.midcoursesurvey.dataaccess.dao.DetailQuest ionsSummaryReportDAO.getDetailQuestionnaireSummary Report(DetailQuestionsSummaryReportDAO.java:172)
      at com.dps.midcoursesurvey.service.detailquestionssum maryreport.DetailQuestionsSummaryReportServiceImpl .getDetailQuestionnaireSummaryReport(DetailQuestio nsSummaryReportServiceImpl.java:60)
      at com.dps.midcoursesurvey.web.summaryreports.detailq uestionssummaryreport.DetailQuestionsSummaryReport MultiactionController.showQuestionSummaryReportInf o(DetailQuestionsSummaryReportMultiactionControlle r.java:124)
      at sun.reflect.NativeMethodAccessorImpl.invoke0(Nativ e Method)
      at sun.reflect.NativeMethodAccessorImpl.invoke(Unknow n Source)
      at sun.reflect.DelegatingMethodAccessorImpl.invoke(Un known Source)
      at java.lang.reflect.Method.invoke(Unknown Source)
      at org.springframework.web.servlet.mvc.multiaction.Mu ltiActionController.invokeNamedMethod(MultiActionC ontroller.java:473)
      at org.springframework.web.servlet.mvc.multiaction.Mu ltiActionController.handleRequestInternal(MultiAct ionController.java:410)
      at org.springframework.web.servlet.mvc.AbstractContro ller.handleRequest(AbstractController.java:153)
      at org.springframework.web.servlet.mvc.SimpleControll erHandlerAdapter.handle(SimpleControllerHandlerAda pter.java:48)
      at org.springframework.web.servlet.DispatcherServlet. doDispatch(DispatcherServlet.java:875)
      at org.springframework.web.servlet.DispatcherServlet. doService(DispatcherServlet.java:807)
      at org.springframework.web.servlet.FrameworkServlet.p rocessRequest(FrameworkServlet.java:571)
      at org.springframework.web.servlet.FrameworkServlet.d oPost(FrameworkServlet.java:511)
      at javax.servlet.http.HttpServlet.service(HttpServlet .java:710)
      at javax.servlet.http.HttpServlet.service(HttpServlet .java:803)
      at org.apache.catalina.core.ApplicationFilterChain.in ternalDoFilter(ApplicationFilterChain.java:290)
      at org.apache.catalina.core.ApplicationFilterChain.do Filter(ApplicationFilterChain.java:206)
      at com.dps.midcoursesurvey.util.RequestHandlerManager .doFilter(RequestHandlerManager.java:60)
      at org.apache.catalina.core.ApplicationFilterChain.in ternalDoFilter(ApplicationFilterChain.java:235)
      at org.apache.catalina.core.ApplicationFilterChain.do Filter(ApplicationFilterChain.java:206)
      at org.jboss.web.tomcat.filters.ReplyHeaderFilter.doF ilter(ReplyHeaderFilter.java:96)
      at org.apache.catalina.core.ApplicationFilterChain.in ternalDoFilter(ApplicationFilterChain.java:235)
      at org.apache.catalina.core.ApplicationFilterChain.do Filter(ApplicationFilterChain.java:206)
      at org.apache.catalina.core.StandardWrapperValve.invo ke(StandardWrapperValve.java:230)
      at org.apache.catalina.core.StandardContextValve.invo ke(StandardContextValve.java:175)
      at org.jboss.web.tomcat.security.SecurityAssociationV alve.invoke(SecurityAssociationValve.java:179)
      at org.jboss.web.tomcat.security.JaccContextValve.inv oke(JaccContextValve.java:84)
      at org.apache.catalina.core.StandardHostValve.invoke( StandardHostValve.java:128)
      at org.apache.catalina.valves.ErrorReportValve.invoke (ErrorReportValve.java:104)
      at org.jboss.web.tomcat.service.jca.CachedConnectionV alve.invoke(CachedConnectionValve.java:156)
      at org.apache.catalina.core.StandardEngineValve.invok e(StandardEngineValve.java:109)
      at org.apache.catalina.connector.CoyoteAdapter.servic e(CoyoteAdapter.java:241)
      at org.apache.coyote.http11.Http11Processor.process(H ttp11Processor.java:844)
      at org.apache.coyote.http11.Http11Protocol$Http11Conn ectionHandler.process(Http11Protocol.java:580)
      at org.apache.tomcat.util.net.JIoEndpoint$Worker.run( JIoEndpoint.java:447)
      at java.lang.Thread.run(Unknown Source)
      Caused by: java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Incorrect syntax near 'call'.
      at com.microsoft.jdbc.base.BaseExceptions.createExcep tion(Unknown Source)
      at com.microsoft.jdbc.base.BaseExceptions.getExceptio n(Unknown Source)
      at com.microsoft.jdbc.sqlserver.tds.TDSRequest.proces sErrorToken(Unknown Source)
      at com.microsoft.jdbc.sqlserver.tds.TDSRequest.proces sReplyToken(Unknown Source)
      at com.microsoft.jdbc.sqlserver.tds.TDSRPCRequest.pro cessReplyToken(Unknown Source)
      at com.microsoft.jdbc.sqlserver.tds.TDSRequest.proces sReply(Unknown Source)
      at com.microsoft.jdbc.sqlserver.SQLServerImplStatemen t.getNextResultType(Unknown Source)
      at com.microsoft.jdbc.base.BaseStatement.commonTransi tionToState(Unknown Source)
      at com.microsoft.jdbc.base.BaseStatement.postImplExec ute(Unknown Source)
      at com.microsoft.jdbc.base.BasePreparedStatement.post ImplExecute(Unknown Source)
      at com.microsoft.jdbc.base.BaseStatement.commonExecut e(Unknown Source)
      at com.microsoft.jdbc.base.BaseStatement.executeInter nal(Unknown Source)
      at com.microsoft.jdbc.base.BasePreparedStatement.exec ute(Unknown Source)
      at org.hibernate.dialect.SybaseDialect.getResultSet(S ybaseDialect.java:186)
      at org.hibernate.jdbc.AbstractBatcher.getResultSet(Ab stractBatcher.java:193)
      at org.hibernate.loader.Loader.getResultSet(Loader.ja va:1784)
      at org.hibernate.loader.Loader.doQuery(Loader.java:67 4)
      at org.hibernate.loader.Loader.doQueryAndInitializeNo nLazyCollections(Loader.java:236)
      at org.hibernate.loader.Loader.doList(Loader.java:222 0)
      ... 44 more
      14:21:12,353 ERROR [STDERR] org.hibernate.exception.GenericJDBCException: could not execute query
      at org.hibernate.exception.SQLStateConverter.handledN onSpecificException(SQLStateConverter.java:103)
      at org.hibernate.exception.SQLStateConverter.convert( SQLStateConverter.java:91)
      at org.hibernate.exception.JDBCExceptionHelper.conver t(JDBCExceptionHelper.java:43)
      at org.hibernate.loader.Loader.doList(Loader.java:222 3)
      at org.hibernate.loader.Loader.listIgnoreQueryCache(L oader.java:2104)
      at org.hibernate.loader.Loader.list(Loader.java:2099)
      at org.hibernate.loader.custom.CustomLoader.list(Cust omLoader.java:289)
      at org.hibernate.impl.SessionImpl.listCustomQuery(Ses sionImpl.java:1695)
      at org.hibernate.impl.AbstractSessionImpl.list(Abstra ctSessionImpl.java:142)
      at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl. java:152)
      at com.dps.midcoursesurvey.dataaccess.dao.DetailQuest ionsSummaryReportDAO.getDetailQuestionnaireSummary Report(DetailQuestionsSummaryReportDAO.java:172)
      at com.dps.midcoursesurvey.service.detailquestionssum maryreport.DetailQuestionsSummaryReportServiceImpl .getDetailQuestionnaireSummaryReport(DetailQuestio nsSummaryReportServiceImpl.java:60)
      at com.dps.midcoursesurvey.web.summaryreports.detailq uestionssummaryreport.DetailQuestionsSummaryReport MultiactionController.showQuestionSummaryReportInf o(DetailQuestionsSummaryReportMultiactionControlle r.java:124)
      at sun.reflect.NativeMethodAccessorImpl.invoke0(Nativ e Method)
      at sun.reflect.NativeMethodAccessorImpl.invoke(Unknow n Source)
      at sun.reflect.DelegatingMethodAccessorImpl.invoke(Un known Source)
      at java.lang.reflect.Method.invoke(Unknown Source)

      Comment


      • #4
        i resolve all this One but now again strange problem i am facing


        ------------Exception--------------------------------
        Caused by: java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]ResultSet can not re-read row data for column 4.
        ------------------ - ------------------



        my Entity

        ---------------------------------------------
        @org.hibernate.annotations.NamedNativeQuery(name = "getQuestionDetailSummaryNativeSQL",
        query = "{call GetQuestionsSummeryReport(:surveyId,:courseId)}",
        callable = true, resultClass = DetailQuestionnaireSummary.class)

        @Entity
        public class DetailQuestionnaireSummary implements java.io.Serializable{

        private int questionId;
        private String questionDesc;
        private int totalYes;
        private int totalNo;
        private int percentYes;
        private int percentNo;
        private int totalStudents;

        /**
        *
        */
        DetailQuestionnaireSummary()
        {

        }
        /**
        *
        * @param questionId
        * @param questionDesc
        * @param totalYes
        * @param percentYes
        * @param totalNo
        * @param percentNo
        * @param totalStudents
        */
        public DetailQuestionnaireSummary(int questionId,String questionDesc,int totalYes, int percentYes,int totalNo
        ,int percentNo,int totalStudents)
        {
        this.questionId=questionId;
        this.questionDesc=questionDesc;
        this.totalYes=totalYes;
        this.percentYes=percentYes;
        this.totalNo=totalNo;
        this.percentNo=percentNo;
        this.totalStudents=totalStudents;
        }
        @Id
        @Column(name = "question_id")
        public int getQuestionId() {
        return questionId;
        }
        public void setQuestionId(int questionId) {
        this.questionId = questionId;
        }
        @Column(name = "question_desc")
        public String getQuestionDesc() {
        return questionDesc;
        }
        public void setQuestionDesc(String questionDesc) {
        this.questionDesc = questionDesc;
        }
        @Column(name = "total_yes")
        public int getTotalYes() {
        return totalYes;
        }
        public void setTotalYes(int totalYes) {
        this.totalYes = totalYes;
        }

        @Column(name = "total_no")
        public int getTotalNo() {
        return totalNo;
        }
        public void setTotalNo(int totalNo) {
        this.totalNo = totalNo;
        }
        @Column(name = "percent_yes")
        public int getPercentYes() {
        return percentYes;
        }
        public void setPercentYes(int percentYes) {
        this.percentYes = percentYes;
        }
        @Column(name = "percent_no")
        public int getPercentNo() {
        return percentNo;
        }
        public void setPercentNo(int percentNo) {
        this.percentNo = percentNo;
        }
        @Column(name = "total_students")
        public int getTotalStudents() {
        return totalStudents;
        }
        public void setTotalStudents(int totalStudents) {
        this.totalStudents = totalStudents;
        }

        }

        Comment

        Working...
        X