cbacba100
266
2019-11-06 13:26:33 작성 2019-11-06 13:30:10 수정됨
3
362

jsp 페이징 처리하고 있는데요 파라미터 오류 좀 봐주세요..


페이징 처리를 하고 있는데 오류가 납니다.

front_idx가 0인 데이터를 한페이지에 12개씩만 출력하고 하는데 계속 오류가 나네요..

화면에 출력도 안됩니다.

저 쿼리문을 그대로 mysql에서 실행해봤는데 front_idx가 0인 데이터만 딱 출력이 되는데 왜 쿼리문에선ㄴ 안될까요..ㅜㅜㅜ


오류입니다. 소스는 아래 있습니다.

java.sql.SQLException: Parameter index out of range (1 > number of parameters, which is 0).


at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:965)


at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:898)


at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:887)


at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:861)


at com.mysql.jdbc.PreparedStatement.checkBounds(PreparedStatement.java:3382)


at com.mysql.jdbc.PreparedStatement.setInternal(PreparedStatement.java:3367)


at com.mysql.jdbc.PreparedStatement.setInternal(PreparedStatement.java:3406)


at com.mysql.jdbc.PreparedStatement.setInt(PreparedStatement.java:3357)


at org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.setInt(DelegatingPreparedStatement.java:139)


at org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.setInt(DelegatingPreparedStatement.java:139)


at aa.aa_DAO.nextPage(aa_DAO.java:466)


at org.apache.jsp.novel_jsp._jspService(novel_jsp.java:265)


at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70)


at javax.servlet.http.HttpServlet.service(HttpServlet.java:731)


at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:472)


at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:395)


at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:339)


at javax.servlet.http.HttpServlet.service(HttpServlet.java:731)


at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:303)


at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)


at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)


at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)


at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)


at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:219)


at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:110)


at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:494)


at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:169)


at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:104)


at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:1025)


at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:116)


at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:445)


at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1137)


at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:637)


at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:317)


at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)


at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)


at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)


at java.lang.Thread.run(Unknown Source)


getList(): 오라클 처리 완료


nextPage(): 오라클 처리 완료



jsp파일입니다.


<%

 int pageNumber = 1; //기본 페이지 넘버

  //페이지넘버값이 있을때

  if (request.getParameter("pageNumber") != null) {

   pageNumber = Integer.parseInt(request.getParameter("pageNumber"));

  }

 %>

 <!-- 페이지 넘기기 -->

 <div class="aaStartTotalSection">

   <div class="aaTotalSection" >

 

<table>

     <%

      

      aa_DAO aa_DAO = new aa_DAO();

      ArrayList<aa_Bean> mList = aa_DAO.getList(pageNumber);

      for(int i = 0; i < mList.size(); i++) { 

     %>

<div class="aastartSection1">

            <img src="upload/<%= mList.get(i).getPhoto() %>" width="60px" height="60px" class="aastartImage1"/>

               <div class="startTitleSection1">

                     <div class="startTitle1">

                        <a href="aacontent.jsp?write_num=<%= mList.get(i).getWrite_num() %>"><%= mList.get(i).getWrite_title() %></a>

                     </div>

                  <div class="satrtNameSection1">

                     <img src="img/user(1).png" class="startProfile1">

                        <span class="startName1"><b><%= mList.get(i).getNickname() %></b></span>

                       <span class="view"><%= mList.get(i).getWrite_see() %></span>

                       <span class="write_see">조회수</span>

                  </div>      

                  <div class="startTimeSection1">

                     <div class="startTime"><%= mList.get(i).getWrite_date() %> </div>

                  </div>   

               </div>   

         </div>

     <%

      }

     %>

    </tbody>

</table>


  </div>

 </div>


<div class="pageDiv">

   <%

    if(pageNumber != 1) {  

   %>

    <span class="btnPre"><a href="novel.jsp?pageNumber=<%=pageNumber - 1%>" class="btnPreview">이전</a></span>

   <%

    } if(aa_DAO.nextPage(pageNumber + 1)) {

   %>

    <span class="btnNe"><a href="novel.jsp?pageNumber=<%=pageNumber + 1%>" class="btnNext">다음</a></span>

   <%

    }

   %>

</div>  


dao.java입니다.

   public int getFront_idx() { 


      String SQL = "SELECT FRONT_IDX FROM NOVEL";


      try {

         PreparedStatement pstmt = con.prepareStatement(SQL);

         rs = pstmt.executeQuery();

         if(rs.next()) {

            return rs.getInt(1) + 1;

         }

         return 1;//첫 번째 게시물인 경우

      } catch (Exception e) {

         e.printStackTrace();

      }

      return -1; //데이터베이스 오류

   }

   

   public ArrayList<aa_Bean> getList(int pageNumber){ 


       ArrayList<aa_Bean> mList2 = new ArrayList<>();

       connect_CP();

       String strQuery = "";



      try {

      strQuery = "select ";

          strQuery += "* ";

          strQuery += ", case when DATEDIFF <> 0 THEN CONCAT(DATEDIFF,'일전') WHEN (DATEDIFF = 0 AND TIMEDIFF <> 0) THEN (CASE WHEN HOUR(TIMEDIFF) = 0 THEN CONCAT(MINUTE(TIMEDIFF),'분전') WHEN HOUR(TIMEDIFF) <= 3 AND HOUR(TIMEDIFF) > 0 THEN CONCAT(HOUR(TIMEDIFF),'시간전') ELSE write_date END ) END AS DIFF ";

          strQuery += "from ";

          strQuery += "( ";

          strQuery += "select  ";

          strQuery += "*  ";

          strQuery += ", DATEDIFF(now(), STR_TO_DATE(write_date, '%Y-%m-%d %H:%i:%s')) AS DATEDIFF ";

          strQuery += ", TIMEDIFF(now(), STR_TO_DATE(write_date, '%Y-%m-%d %H:%i:%s')) AS TIMEDIFF ";

          strQuery += "from  ";

          strQuery += "aa.novel  ";

          strQuery += "where front_idx=0 ";

          strQuery += "limit 12 ";

          strQuery += ") as a; ";

          

          psmt = con.prepareStatement(strQuery);

         psmt.setInt(1, getFront_idx() - (pageNumber -1) * 12);


         rs = psmt.executeQuery();


         while (rs.next()) {


             aa_Bean mbean = new aa_Bean();

             mbean.setWrite_num(rs.getInt("write_num"));

             mbean.setNickname(rs.getString("nickname"));

             mbean.setWrite_title(rs.getString("write_title"));

             mbean.setWrite_contents(rs.getString("write_contents"));;

             mbean.setWrite_see(rs.getInt("write_see"));

             mbean.setWrite_date(rs.getString("DIFF"));

             mbean.setGenre(rs.getString("genre"));

             mbean.setPhoto(rs.getString("photo")); 

               

             mList2.add(mbean);


         }


      } catch (SQLException e) {

          e.printStackTrace();

       }

     System.out.println("getList(): 오라클 처리 완료");

    

     disconnect();

     

     return mList2;

  }

   

   public boolean nextPage (int pageNumber) {

       ArrayList<aa_Bean> mList = new ArrayList<>();

       connect_CP();


      try {

         psmt = con.prepareStatement("SELECT * FROM NOVEL WHERE FRONT_IDX=0 LIMIT 12");


         psmt.setInt(1, getFront_idx() - (pageNumber -1) * 12);


         rs = psmt.executeQuery();


         if (rs.next()) {


            return true;


         }


      } catch (SQLException e) {

          e.printStackTrace();

       }

    System.out.println("nextPage(): 오라클 처리 완료");

    

    disconnect();

    

   return false;       


   }




0
  • 답변 3

  • 스우
    148
    2019-11-06 13:48:54

    nextPage의 psmt.setInt(1, getFront_idx() - (pageNumber -1) * 12); 지우면 어떻게 되나요?

  • 아이러니y
    1k
    2019-11-06 14:45:20

    쿼리문에 ? 을 넣어서 맵핑을 맞춰줘보세요



  • spoon
    1k
    2019-11-06 15:28:04 작성 2019-11-06 15:28:43 수정됨

    java.sql.SQLException: Parameter index out of range (1 > number of parameters, which is 0).

    >> 인덱스가 1이상이여야하는데 0이라는것같네요

  • 로그인을 하시면 답변 을 등록할 수 있습니다.