cannot write to database

**Ana Pan** · 01-20-2013, 08:05 PM

Hello,

I have a problem with my code and hope that somebody can point me what am I doing wrong.
My program uses MVC structure. Part of it is the customer login (working), add(not working) and update(not working).

Right now, I am trying to get my addCustomer part working. When I deploy it, it gives me

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

I cannot find where I did that mistake, please help me find it.

addCustomer.html

XML Code:

<!--
To change this template, choose Tools | Templates
and open the template in the editor.
-->

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />

    </head>

    <body>
        <div class="addC">
            <form id="form1" name="form1" method="post" action="UserSystem" enctype="multipart/form-data">

                <h1>New Customer</h1>

                <input type="text" name="username" placeholder="username" id="userName"/>
                <input type="password" name="password" placeholder="password" id="password"/>
                <input type="text" name="firstName" placeholder="first name" id="firstName"/>
                <input type="text" name="lastName" placeholder="last name" id="lastName"/>
                <input type="text" name="address" placeholder="address" id="address"/>
                <input type="text" name="mobile" placeholder="mobile" id="mobile"/>
                <input type="text" name="email" placeholder="email" id="email"/>

                <input type="file" name="image"/>

                <input type="hidden" name="option" value="add"/>
                <input type="submit" name="button" id="button" value="Save" />

            </form>
        </div>

    </body>
</html>

CustomerSystem.java (controller)

Java Code:

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.mm4;

import java.io.File;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Hashtable;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.servlet.RequestDispatcher;
import javax.servlet.ServletContext;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import javazoom.upload.*;

/**
 *
 * 
 */
public class CustomerSystem 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 {
        String address = null;
        String option = null;

        HttpSession session = request.getSession(true);

        String contextPath = (String) session.getAttribute("contextPath");

        ServletContext servletContext = getServletContext();
        contextPath = servletContext.getRealPath(File.separator);
        session.setAttribute("contextPath", contextPath);
       
        String test = contextPath + "images\\uploads\\";
        session.setAttribute("imageLocation",test);

        System.out.println("test " + test);


        MultipartFormDataRequest mrequest = null;
        Customer c = null;
        if (MultipartFormDataRequest.isMultipartFormData(request)) {
            // Uses MultipartFormDataRequest to parse the HTTP request.

            try {
                mrequest = new MultipartFormDataRequest(request);
                option = mrequest.getParameter("option");
            } catch (UploadException ex) {
                Logger.getLogger(CustomerSystem.class.getName()).log(Level.SEVERE, null, ex);
            }

        } else {
            option = request.getParameter("option");
        }
        System.out.println("option=" + option);

        if (option != null) {
            if (option.equalsIgnoreCase("login")) {
                c = new Customer();
                String username = request.getParameter("username");
                String password = request.getParameter("password");
                c = c.findCustomerByCustomerNamePassword(username, password);
                if (c.getCustomerID() != null) {
                    session.setAttribute("customer", c);
                    address = "/home.html";
                } else {
                    address = "/invalid.jsp";
                }
                
            } else if (option.equalsIgnoreCase("add")) {
                String userName = mrequest.getParameter("userName");
                String password = mrequest.getParameter("password");
                String firstName = mrequest.getParameter("firstName");
                String lastName = mrequest.getParameter("lastName");
                String add = mrequest.getParameter("address");
                String mobile = mrequest.getParameter("mobile");
                String email = mrequest.getParameter("email");
                String filename = null;



                //upload code
                try {
                    Hashtable files = mrequest.getFiles();
                    if ((files != null) && (!files.isEmpty())) {
                        UploadFile file = (UploadFile) files.get("image");
                        filename = file.getFileName();
                        if (file != null) {
                            System.out.println("Uploaded file : " + file.getFileName() + " (" + file.getFileSize() + " bytes)" + "Content Type : " + file.getContentType());
                        }
                        // Uses the bean now to store specified by jsp:setProperty at the top.
                        UploadBean upBean = new UploadBean();
                        upBean.setFolderstore(contextPath + "../../web/images/uploads");
                        upBean.store(mrequest, "image");
                    }


                } catch (Exception e) {
                    System.out.println("error on upload " + e);
                }


                // Uses the bean now to store specified by jsp:setProperty at the top.

                c = new Customer(userName, password, firstName, lastName, add, mobile, email, filename);
                boolean saved = c.addCustomer();
                session.setAttribute("customer", c);
                address = "/home.html";

            } else if (option.equalsIgnoreCase("save")) {
                c = (Customer) session.getAttribute("customer");
                String username = request.getParameter("username");
                c.setUserName(username);
                String password = request.getParameter("password");
                c.setPassword(password);
                String firstName = request.getParameter("firstName");
                c.setFirstName(firstName);
                String lastName = request.getParameter("lastName");
                c.setLastName(lastName);
                String add = request.getParameter("address");
                c.setAddress(add);
                String mobile = request.getParameter("mobile");
                c.setMobile(mobile);
                String email = request.getParameter("email");
                c.setEmail(email);
                boolean saved = c.updateCustomer();
                System.out.println("saved=" + saved);
                session.setAttribute("customer", c);
                System.out.println(c.getAddress());
                address = "/home.html";

            }
                else if(option.equalsIgnoreCase("findAllPhones")){
                System.out.println("in  find all");
                Phone phone = new Phone();
                Collection<Phone> phones = new ArrayList<Phone>();
                
                phones = phone.findAllPhones();
                session.setAttribute("phoneList", phones);
                address = "findAllPhones.jsp";
            }
            
            else if(option.equalsIgnoreCase("findAll")){
                System.out.println("in  find all");
                Customer customer = new Customer();
                Collection<Customer> customers = new ArrayList<Customer>();
                
                customers = customer.findAllCustomers();
                session.setAttribute("customerList", customers);
                address = "findAllCustomers.jsp";
            }
            RequestDispatcher dispatcher =
                    request.getRequestDispatcher(address);
            dispatcher.forward(request, response);


        }


    }

    // <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 {
        processRequest(request, response);
    }

    /** 
     * 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 {
        processRequest(request, response);
    }

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

Customer.java (customer class)

Java Code:

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.mm4;

import java.sql.PreparedStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collection;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * 
 */
public class Customer {

    private String customerID;
    private String userName;
    private String password;
    private String firstName;
    private String lastName;
    private String address;
    private String mobile;
    private String email;
    private String image;

    public Customer() {
    }

    public Customer(String customerID, String userName, String password, String firstName, String lastName, String address, String mobile, String email,String image) {
        this.customerID=customerID;
        this.userName = userName;
        this.password = password;
        this.firstName = firstName;
        this.lastName = lastName;
        this.address=address;
        this.mobile = mobile;
        this.email=email;
        this.image = image;
    }
    
        public Customer(String userName, String password, String firstName, String lastName, String address, String mobile, String email,String image) {
        this.userName = userName;
        this.password = password;
        this.firstName = firstName;
        this.lastName = lastName;
        this.address=address;
        this.mobile = mobile;
        this.email=email;
        this.image = image;
    }


    public Customer findCustomerByCustomerNamePassword(String uname, String pass) {


        try {

            Connection connection =
                    DBHelperClass.getConnection();
            Statement statement = connection.createStatement();
            String query = "SELECT * FROM customer where userName='" + uname + "' AND password ='" + pass + "'";
            // Send query to database and store results. 

            ResultSet resultSet = statement.executeQuery(query);

            while (resultSet.next()) {
                this.customerID = resultSet.getString("customerId");
                this.userName = resultSet.getString("userName");
                this.password = resultSet.getString("password");
                this.firstName = resultSet.getString("firstName");
                this.lastName = resultSet.getString("lastName");
                this.address = resultSet.getString("address");
                this.mobile = resultSet.getString("mobile");
                this.email = resultSet.getString("email");
                this.image = resultSet.getString("image");

            }

            connection.close();

        } catch (SQLException ex) {
            Logger.getLogger(Customer.class.getName()).log(Level.SEVERE, null, ex);
        }



        return this;
    }

    public boolean addCustomer() {

        boolean saved = false;
        try {

            Connection connection = DBHelperClass.getConnection();
            Statement statement = connection.createStatement();
            String template = "INSERT INTO customer('userName', 'password', 'firstName', 'lastName', 'address', 'mobile', 'email','image') VALUES (?,?,?,?,?,?,?,?)";

            PreparedStatement inserter = connection.prepareStatement(template);
            inserter.setString(2, this.userName);
            inserter.setString(3, this.password);
            inserter.setString(4, this.firstName);
            inserter.setString(5, this.lastName);
            inserter.setString(6, this.address);
            inserter.setString(7, this.mobile);
            inserter.setString(8, this.email);
            inserter.setString(9, this.image);
            inserter.executeUpdate();

            ResultSet rs = statement.executeQuery("select max(customerID) from customer");
            while (rs.next()) {
                System.out.println("max row " + rs.getString(1));
                this.customerID = rs.getString(1);
            }


            saved = true;
            inserter.close();
            connection.close();


        } catch (SQLException ex) {
            Logger.getLogger(Customer.class.getName()).log(Level.SEVERE, null, ex);
        }



        return saved;
    }

    public Collection<Customer> findAllCustomers() {
        Collection<Customer> customers = new ArrayList<Customer>();
        try {
            System.out.println("in find all");
            Connection connection = DBHelperClass.getConnection();
            Statement statement = connection.createStatement();
            String query = "Select * from customer";
            ResultSet rs = statement.executeQuery(query);

            while (rs.next()) {
                System.out.println("found id " + rs.getString("customerID"));
                Customer c = new Customer(rs.getString("customerID"),rs.getString("userName"),rs.getString("password"),rs.getString("firstName"),rs.getString("lastName"),rs.getString("address"),rs.getString("mobile"),rs.getString("email"),rs.getString("image"));
                customers.add(c);
            }
                                  
            
          
            connection.close();


        } catch (Exception ex) {
            Logger.getLogger(Customer.class.getName()).log(Level.SEVERE, null, ex);
            System.out.println("ex" + ex);
        }

        return customers;

    }

    public boolean updateCustomer() {

        boolean saved = false;
        try {

            Connection connection = DBHelperClass.getConnection();
            Statement statement = connection.createStatement();
            String template = "UPDATE customer SET userName=?,password=?,firstName=?,lastName=?,address=?,mobile=?,email=?, image=?, WHERE customerID = ?";
            System.out.println("in save " + this.customerID);
            PreparedStatement inserter = connection.prepareStatement(template);
            inserter.setString(1, this.userName);
            inserter.setString(2, this.password);
            inserter.setString(3, this.firstName);
            inserter.setString(4, this.lastName);
            inserter.setString(5, this.address);
            inserter.setString(6, this.mobile);
            inserter.setString(5, this.email);
            inserter.setString(6, this.image);
            System.out.println("inserter" + inserter.toString());
            inserter.executeUpdate();

            saved = true;
            inserter.close();
            connection.close();


        } catch (Exception ex) {
            Logger.getLogger(Customer.class.getName()).log(Level.SEVERE, null, ex);
            System.out.println("ex" + ex);
        }
        return saved;
    }

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }

    public String getCustomerID() {
        return customerID;
    }

    public void setCustomerID(String customerID) {
        this.customerID = customerID;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public String getFirstName() {
        return firstName;
    }

    public void setFirstName(String firstName) {
        this.firstName = firstName;
    }

    public String getImage() {
        return image;
    }

    public void setImage(String image) {
        this.image = image;
    }

    public String getLastName() {
        return lastName;
    }

    public void setLastName(String lastName) {
        this.lastName = lastName;
    }

    public String getMobile() {
        return mobile;
    }

    public void setMobile(String mobile) {
        this.mobile = mobile;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public String getUserName() {
        return userName;
    }

    public void setUserName(String userName) {
        this.userName = userName;
    }


}

Thank you very much in advance for any advice possible,
Ana

**SurfMan** · 01-21-2013, 09:39 AM

You should check the number of questionmarks you have in your SQL statement, and compare that against the number of PreparedStatment.set* you have.
First example: line 107 of Customer.java inserter.setString(2, this.userName); but you don't have a inserter.set*(1,...)
Second example: line 182: you have used 6 twice: inserter.setString(6, ...) (Bad copy paste?)

Note that parameters are a 1-based index.

**Ana Pan** · 01-26-2013, 07:34 PM

Thank you! It helped!

Thread: cannot write to database

LinkBack

Thread Tools

Search Thread

Display

cannot write to database

Re: cannot write to database

Re: cannot write to database

Similar Threads

how to embed combobox,checbox in JTable read write to database

Using jbutton to write to jtextfield values to database

Android read/write to database on a web service.

How to write database queries in Jtable rows and columns?

How do you write a Java object to MSAccess database?

Bookmarks

Bookmarks

Posting Permissions

Size Reduced for Images in PDF &...

myBill 13.05.2013

Bugzero 6.6

Marvin 1.4.3

NetXMS 1.2.7

AppScale 1.7.0

Aspose.Slides for Java 7.3.0

DBeaver 2.2.0

TripleA 1.6.1.4

Remote Launcher Server 0.2.15

J'ai fait des erreurs de distance

Lionel Messi est

Par contre.

Manaudou dï¿½ï¿½vore tout sur son...

AJOUTER UN COMMENTAIREait Astrid...

Images won't clear

Programming a media streamer for...

what to insert to add the rows and...

BlackBerry Java SDK questions.

Arguments in Main