Forum:

JDBC and Relational Databases

cannot write a new customer to the database

Ana Pan

Greenhorn

Posts: 4

I like...

posted 11 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

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

[HTML]

<!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>

<h1>New Customer</h1>

</form>
        </div>

</body>
</html>

CustomerSystem.java (controller)

/*
 * 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)

/*
 * 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 help or advice,
Ana

Paul Clapham

Marshal

Posts: 28293

I like...

posted 11 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

Here's the relevant code from your post:

Your INSERT statement has 8 parameters (those question marks). They are numbered from 1 to 8. So your numbering in the setString calls is off by 1.

Java 8 (verified skill)
Skill verified by Paul Clapham

Ana Pan

Greenhorn

Posts: 4

I like...

posted 11 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

Ok,
I have edited that piece of the code.
Now it looks like this:

public boolean addCustomer() {

boolean saved = false;
        try {

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

PreparedStatement inserter = connection.prepareStatement(template);
            inserter.setString(1, null);
            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;
    }

Unfortunately, now I get

SEVERE: The log message is null.
com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Column 'userName' cannot be null

for some reason it doesn't take userName info...

Happiness is not a goal ... it's a by-product of a life well lived - Eleanor Roosevelt. Tiny ad:

We need your help - Coderanch server fundraiser

https://coderanch.com/wiki/782867/Coderanch-server-fundraiser