Reputation: 31

Why do I keep getting the SQL parameter index out of range exception?

I looked at similar questions and I followed the syntax for writing queries for inserting into a database but I keep getting this exception. I'm clearly not seeing something. It stops running at " insertStatement.setInt(1, schoolID);" . I read that this means that the query is not in its proper syntax. Please show me where I went wrong. I can't see it at all.

package Servlets;

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.Timestamp;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.Persistence;
import javax.servlet.ServletContext;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import jpa.entities.School;

/**
 *
 * @author Timothy
 */
@WebServlet(name = "SchoolFormServlet", urlPatterns = {"/SchoolFormServlet"})
public class SchoolFormServlet extends HttpServlet {

    /**
     * Processes requests for both HTTP <code>GET</code> and <code>POST</code>
     * methods.
     *
     * @param request servlet request
     * @param response servlet response
     * @throws ServletException if a servlet-specific error occurs
     * @throws IOException if an I/O error occurs
     */
    protected void processRequest(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException, ParseException {
        response.setContentType("text/html;charset=UTF-8");
        try (PrintWriter out = response.getWriter()) {
            /* TODO output your page here. You may use following sample code. */
            ServletContext sc = this.getServletContext();
            sc.getAttribute("schoolForm");
            Integer schoolId = Integer.parseInt(request.getParameter("schoolID"));
            String schoolName = request.getParameter("schoolName");
            Integer periods = Integer.parseInt(request.getParameter("periods"));
            Integer repeatDays = Integer.parseInt(request.getParameter("repeatDays"));
            String scheduleBlock = request.getParameter("scheduleBlock");
            Integer semesters = Integer.parseInt(request.getParameter("semesters"));
            String rangeForLunch = request.getParameter("rangeForLunch");
            String schoolYear = request.getParameter("schoolYear");
            initAndExecuteQuery(schoolId,schoolName,semesters,periods,repeatDays,scheduleBlock,rangeForLunch,schoolYear);

            out.println("<!DOCTYPE html>");
            out.println("<html>");
            out.println("<head>");
            out.println("<title>Servlet SchoolFormServlet</title>");            
            out.println("</head>");
            out.println("<body>");
            out.println("<h1>Servlet SchoolFormServlet at " + request.getContextPath() + "</h1>");
            out.println("</body>");
            out.println("</html>");
        }
    }

    // <editor-fold defaultstate="collapsed" desc="HttpServlet methods. Click on the + sign on the left to edit the code.">
    /**
     * Handles the HTTP <code>GET</code> method.
     *
     * @param request servlet request
     * @param response servlet response
     * @throws ServletException if a servlet-specific error occurs
     * @throws IOException if an I/O error occurs
     */
    @Override
    protected void doGet(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        try {
            processRequest(request, response);
        } catch (ParseException ex) {
            Logger.getLogger(SchoolFormServlet.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    /**
     * Handles the HTTP <code>POST</code> method.
     *
     * @param request servlet request
     * @param response servlet response
     * @throws ServletException if a servlet-specific error occurs
     * @throws IOException if an I/O error occurs
     */
    @Override
    protected void doPost(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        try {
            processRequest(request, response);
        } catch (ParseException ex) {
            Logger.getLogger(SchoolFormServlet.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    /**
     * Returns a short description of the servlet.
     *
     * @return a String containing servlet description
     */
    @Override
    public String getServletInfo() {
        return "Short description";
    }// </editor-fold>

    public void initAndExecuteQuery(Integer schoolID,String schoolName, Integer semesters,Integer periods,Integer repeatDays,String scheduleBlock,String rangeForLunch, String schoolYear) {
      // JDBC driver name and database URL
      String jdbcDriver ="com.mysql.jdbc.Driver";  
      String url ="jdbc:mysql://173.194.104.102:3306/hssp_schema?zeroDateTimeBehavior=convertToNull";
      Connection connection = null;
      ResultSet resultSet = null;
      Statement statement = null;
      //  Database credentials
      String userName = "admin_aamir";
      String passWord = "tommybrown"; 


        try {
            Class.forName("com.mysql.jdbc.Driver").newInstance();
            connection = DriverManager.getConnection(url, userName , passWord);

          // Execute SQL query

         String sql;
         sql = "INSERT INTO School VALUES ('"+schoolID+" ','"+schoolName+" ','"+semesters+" ','"+periods+" ','"+repeatDays+" ','"+scheduleBlock+" ','"+rangeForLunch+" ','"+schoolYear+" ')";
         PreparedStatement insertStatement = connection.prepareStatement(sql);
         insertStatement.setInt(1, schoolID);
         insertStatement.setString(2,schoolName);
         insertStatement.setInt(3,periods);
         insertStatement.setInt(4,repeatDays);
         insertStatement.setString(5,scheduleBlock);
         insertStatement.setString(6,rangeForLunch);
         insertStatement.setString(7,schoolYear);
         insertStatement.executeQuery();
         insertStatement.close();
         connection.close();

        }
        catch (Exception e) {
            e.printStackTrace();

        }



   }


}

Upvotes: 0

Answers (4)

Nishit Shah

Reputation: 192

Method 1: Write the following code

String sql;
     sql = "INSERT INTO School VALUES ('"+schoolID+" ','"+schoolName+" ','"+semesters+" ','"+periods+" ','"+repeatDays+" ','"+scheduleBlock+" ','"+rangeForLunch+" ','"+schoolYear+" ')";
     PreparedStatement insertStatement = connection.prepareStatement(sql);
int valid = insertStatement.executeUpdate();
if(valid == 1)
   System.out.println("insertion successfull");
else
   System.out.println("problem while inserting data in database");

Hope it works for you.It works in my case.

Method 2:

Write the following code

String sql;
     sql = "INSERT INTO School VALUES ('"+schoolID+" ','"+schoolName+" ','"+semesters+" ','"+periods+" ','"+repeatDays+" ','"+scheduleBlock+" ','"+rangeForLunch+" ','"+schoolYear+" ')";
    sql = "INSERT INTO School VALUES(?,?,?,?,?,?,?,?)";
    PreparedStatement insertStatement = connection.prepareStatement(sql);
     insertStatement.setInt(1, schoolID);
     insertStatement.setString(2,schoolName);
     insertStatement.setInt(3,semesters)   // you forgot to write this statement assuming the data types are correct
     insertStatement.setInt(4,periods);
     insertStatement.setInt(5,repeatDays);
     insertStatement.setString(6,scheduleBlock);
     insertStatement.setString(7,rangeForLunch);
     insertStatement.setString(8,schoolYear);

     int valid = insertStatement.executeUpdate();
     if(valid == 1)
          System.out.println("insertion successfull");
     else
          System.out.println("problem while inserting data in database");

Hope you understand both the methods

Upvotes: 0

user3835327

Reputation: 1204

inside School( ...... ) put your database table column name, inside values(?,?,?.....) put the question mark based on how many column name you wanted to insert to database, for your current case is 8 column so put 8 question marks.

 String sql;
         sql = "INSERT INTO School(schoolID,schoolName,semesters,periods,repeatDays,scheduleBlock,rangeForLunch,schoolYear) VALUES (?,?,?,?,?,?,?,?)";
         PreparedStatement insertStatement = connection.prepareStatement(sql);
         insertStatement.setInt(1, schoolID);
         insertStatement.setString(2,schoolName);
         insertStatement.setInt(3,periods);
         insertStatement.setInt(4,repeatDays);
         insertStatement.setString(5,scheduleBlock);
         insertStatement.setString(6,rangeForLunch);
         insertStatement.setString(7,schoolYear); 
        // you have missing 1 line for number 8 , since you wanted to insert 8 data into 8 column 
         insertStatement.executeQuery();
         insertStatement.close();
         connection.close();

Upvotes: 0

arcy

Reputation: 13153

Proper syntax is

INSERT INTO TABLENAME (FIELD1, FIELD2, FIELD3) VALUES (?, ?, ?)

so you need the names of the columns within first parens and the same number of ? in the second. Then your setX(1, value) statements fill in where the question marks are before execution.

Upvotes: 1

yshavit

Reputation: 43456

You need to put question marks in the SQL string to tell it that it has parameters -- instead of appending them straight in.

sql = "INSERT INTO School VALUES(?, ?, ? ...)

The MySQL connector implementation will do the hard work of replacing those question marks with the values of the parameters you set (it doesn't just paste them in, as your code does, since that would make it vulnerable to SQL injection).

Upvotes: 1

Why do I keep getting the SQL parameter index out of range exception?

Answers (4)

Related Questions