java.lang.NumberFormatException: with JSP App and mySQL

Question

Hi i'm getting an the above error when trying insert data into my database from a jsp application

here is the JSP code

    <%@page contentType="text/html" pageEncoding="UTF-8"%>

<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Books database</title>
<link rel="stylesheet" href="style.css" type="text/css">
</head>
<body>

<br>

<div class="navigator">
<a id="currenttab" href="index.jsp">Add</a>
<a href="delete.jsp">Delete</a>
</div>

<%
  String empfirstname = request.getParameter("empfirstname");
  String empsurname = request.getParameter("empsurname");
  String dpddept = request.getParameter("dpddept");
  String extensionno = request.getParameter("extensionno");
  String mobileno = request.getParameter("mobileno");
  String emailaddress = request.getParameter("emailaddress");
  String username = request.getParameter("username");
  String password = request.getParameter("password");
  if (empsurname != null && empfirstname != null 
          && username != null && password != null) {
      Users.Worker.Insert(empfirstname,empsurname,dpddept,extensionno,
              mobileno,emailaddress,username,password);
  }
%>

<br> <br> <br>

    <form method='post' action='index.jsp'>
                    <table>
                        <tr><td>Please Enter your first name.</td>
                            <td><input type="text" id='empfirstname'></td></tr>
                        <tr><td>Please Enter your surname.</td>
                            <td><input type="text" id='empsurname'></td></tr>
                        <tr><td>Please Enter your Department.</td>
                            <td><input type="text" id='dpddept'></td></tr>
                        <tr><td>Please Enter your Extension Number.</td>
                            <td><input type="text" id='extensionno'></td></tr>
                        <tr><td>Please Enter your mobile Number.</td>
                            <td><input type="text" id='mobileno'></td></tr>
                        <tr><td>Please Enter your email Address.</td>
                            <td><input type="text" id='emailaddress'></td></tr>
                        <tr><td>Please Enter your email username.</td>
                            <td><input type="text" id='username'></td></tr>
                        <tr><td>Please Enter your email password.</td>
                            <td><input type="text" id='password'></td></tr>
                        <tr><td><input type="submit" name="submit"/></td></tr>
                    </table></form>
</body>
</html>

This is the java source code

    package Users;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;


public class Worker {

  static final String url = "jdbc:mysql://localhost:3306/users";

  public static void Insert(String empfirstname,String empsurname,
          String dpddept,String extensionno,String mobileno,
          String emailaddress,String username,String password) {
      try {

          String insert = "INSERT INTO users(empfirstname,empsurname,dpddept,extensionno,"
                  + "mobileno,emailaddress,username,password)" +
                  "VALUES (?, ?, ?, ?, ?, ?, ?, ?)";

          Class.forName("com.mysql.jdbc.Driver");
          Connection con = DriverManager.getConnection(url, "root", "dpd2014");

          PreparedStatement ps = con.prepareStatement(insert);


          ps.setString(1, empfirstname);
          ps.setString(2, empsurname);
          ps.setString(3, dpddept);
          ps.setInt(4, Integer.parseInt(extensionno));
          ps.setString(5, mobileno);
          ps.setString(6, emailaddress);
          ps.setString(7, username);
          ps.setString(8, password);
          ps.executeUpdate();
          con.close();

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

  public static List GetUsers() {

      List<String> list = new ArrayList<String>();

      try {

          Class.forName("com.mysql.jdbc.Driver");
          Connection con = DriverManager.getConnection(url, "root", "dpd2014");

          Statement stmt = con.createStatement();

          ResultSet result = stmt.executeQuery("SELECT * FROM users");



          while(result.next())
          {

             list.add(result.getString("empfirstname"));
             list.add(result.getString("empsurname"));
             list.add(result.getString("dpddept"));
             list.add(result.getString("extensionno"));
             list.add(result.getString("mobileno"));
             list.add(result.getString("emailaddress"));
             list.add(result.getString("username"));
             list.add(result.getString("password"));
          } 

          con.close();

      } catch (Exception ex) {
          Logger.getLogger(Worker.class.getName()).log( 
                           Level.SEVERE, null, ex);
      }
          return list;
  }

  public static void Delete(String employeeno) {
      try {

          String delete = "DELETE from users WHERE employeeno = ?";

          Class.forName("com.mysql.jdbc.Driver");
          Connection con = DriverManager.getConnection(url, "root", "dpd2014");
          PreparedStatement ps = con.prepareStatement(delete);

          ps.setString(1, employeeno);
          ps.executeUpdate();
          con.close();

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

and finally this is the mysql table

 users
 (Employeeno int(11) AI PK
 empfirstname varchar(30)
 empsurname varchar(40)
 dpddept varchar(30)
 extensionno int(11)
 mobileno     varchar(30)
 emailaddress varchar(30)
 username varchar(30)
 password varchar(30))

I hope this is enough info, any help would be appreciated

post only relevant codes and which line is showing the error — SpringLearner
– SpringLearner, Commented Apr 14, 2014 at 16:01
At which line of code you are getting NumberFormatException. — Gundamaiah
– Gundamaiah, Commented Apr 14, 2014 at 16:03
Well you have only two fields that are numbers (on the database that is): extensionno and mobileno, you could try omitting these and see if it works then. If it does you might have to convert them to integers before setting the parameters. — Patru
– Patru, Commented Apr 14, 2014 at 16:06

Ravinder Reddy · Accepted Answer · 2014-04-14 17:33:04Z

1

The exception java.lang.NumberFormatException might have thrown for the value input for the extensionno field.

Your table structure says that

extensionno int(11) -- column position 5 in the table

And you tried to set a value to it as

ps.setString( 4, dpddept ); // this caused the error   
ps.setString( 5, extensionno ); // but not this

But setString( 4... caused the NumberFormatException.
The 4th placeholder is for extensionno which is of type int(11).
But you are setting a value from dpddept variable, which is a varchar String. When you try inserting String into an int numeric type, the database will throw an error, like:

ERROR 1366 (HY000): 
  Incorrect integer value: 'sales' for column 'extensionno' at row 1

How to resolve this:

We have set values based on position of a query parameter in the sql statement but not based on column position in the table.

String insert = 
    "INSERT INTO users( empfirstname, empsurname, dpddept, extensionno, "
  + "mobileno, emailaddress, username, password )" 
  + "VALUES ( ?, ?, ?, ?, ?, ?, ?, ? )";

In the above query there are 8 placeholders (?).
That means you should set... 8 values starting from 1st to 8th parameter.
These are placeholder position numbers in the insert query but not column positions in the table.

Hence the statement

ps.setString( 2, empfirstname );

is wrong. It should be

ps.setString( 1, empfirstname );

See what the documentation says:

setString(int parameterIndex, String x) throws SQLException
- parameterIndex - the first parameter is 1, the second is 2, ...
- x - the parameter value

Change your value setters as below:

ps.setString( 1, empfirstname ); // varchar string
ps.setString( 2, empsurname ); // varchar string
ps.setString( 3, dpddept ); // varchar string

// you can use setString on int type columns,
// but only when they are valid numbers
// ps.setString( 4, extensionno ); // int int
// or
ps.setInt( 4, Integer.parseInt( extensionno ) ); // int int

// ps.setString( 5, mobileno ); // int int
// or
ps.setInt( 5, Integer.parseInt( mobileno ) ); // int int

ps.setString( 6, emailaddress ); // varchar string
ps.setString( 7, username ); // varchar string
ps.setString( 8, password ); // varchar string

ps.executeUpdate();

answered Apr 14, 2014 at 17:33

Ravinder Reddy

24.1k6 gold badges54 silver badges86 bronze badges

Sign up to request clarification or add additional context in comments.

12 Comments

David McGuinness Over a year ago

It still doesnt seem to work for me, Every time i add a person on the form it doesnt enter a record in the table. and when i go to the delete tab i get the "java.lang.NumberFormatException: For input string: "nick" " warning, I have entered a record in the table manually it doesnt display and i get the warning (getUsers method). I have removed the first column (employeeno Int) and tried it again along with the changes that ravinder had suggested and it still didnt update the table.

Ravinder Reddy Over a year ago

How was your delete statement framed like? delete from table_name where id = 'nick' or what? If yes, you have not framed your html objects correctly. That should be ... id = ? and value of Employeeno should fill the placeholder using a prepared statement.

David McGuinness Over a year ago

here is my delete statement String delete = "DELETE from users WHERE empfirstname = ?";

David McGuinness Over a year ago

Ive dropped the employeeno column just for now

Ravinder Reddy Over a year ago

And what was your input for the ? placeholder? How did you frame the set...( method and value?

|

Mike B · Accepted Answer · 2014-04-14 18:04:39Z

1

From the PreparedStatement docs:

Note: The setter methods (setShort, setString, and so on) for setting IN parameter values must specify types that are compatible with the defined SQL type of the input parameter. For instance, if the IN parameter has SQL type INTEGER, then the method setInt should be used.

Your extensionno and mobileno are int, not varchar. But you're setting them like this:

ps.setString(5, extensionno);
ps.setString(6, mobileno);

They should be:

ps.setInt(5, extensionno);
ps.setInt(6, mobileno);

EDIT: Looks like Ravinder got the complete solution, check his answer.

edited Apr 14, 2014 at 18:04

answered Apr 14, 2014 at 16:04

Mike B

5,4612 gold badges26 silver badges45 bronze badges

2 Comments

Patru Over a year ago

Of course you will have to convert it first, use Integer.parseInt as described in this question.

Ravinder Reddy Over a year ago

@MikeB: This answer won't help. Error was not on those fields as you specified. And use of setInt/Long/Float/Double/... is optional so long as the value is in a valid number format.

Collectives™ on Stack Overflow

java.lang.NumberFormatException: with JSP App and mySQL

2 Answers 2

12 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

12 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related