Results 1 to 3 of 3
Like Tree1Likes
  • 1 Post By SurfMan

Thread: cannot write to database

  1. #1
    Ana Pan is offline Member
    Join Date
    Jan 2013
    Posts
    2
    Rep Power
    0

    Lightbulb cannot write to database

    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

  2. #2
    SurfMan's Avatar
    SurfMan is online now Godlike
    Join Date
    Nov 2012
    Location
    The Netherlands
    Posts
    992
    Rep Power
    2

    Default Re: cannot write to database

    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.
    Fubarable likes this.

  3. #3
    Ana Pan is offline Member
    Join Date
    Jan 2013
    Posts
    2
    Rep Power
    0

Similar Threads

  1. how to embed combobox,checbox in JTable read write to database
    By scorpion.poison in forum New To Java
    Replies: 4
    Last Post: 08-30-2012, 10:52 AM
  2. Replies: 16
    Last Post: 05-24-2011, 09:29 AM
  3. Android read/write to database on a web service.
    By SeanieSortMeOut in forum Android
    Replies: 1
    Last Post: 02-05-2011, 01:21 PM
  4. How to write database queries in Jtable rows and columns?
    By Vignesh Karthick in forum AWT / Swing
    Replies: 1
    Last Post: 02-02-2011, 03:55 PM
  5. Replies: 2
    Last Post: 10-11-2009, 12:31 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •