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, Apr 14 '14 at 16:01
At which line of code you are getting NumberFormatException. — Gundamaiah, Apr 14 '14 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, Apr 14 '14 at 16:06

Mike B · Answer 1 · 2014-04-14T18:04:39.853

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 '14 at 18:04

answered Apr 14 '14 at 16:04

Mike B

5,390
2
23
45

1

Of course you will have to convert it first, use `Integer.parseInt` as described in [this question](http://stackoverflow.com/questions/5585779/how-to-convert-string-to-int-in-java). – Patru Apr 14 '14 at 16:09
@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. – Ravinder Reddy Apr 14 '14 at 17:36

score 1 · Answer 2 · answered Apr 14 '14 at 17:33

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 '14 at 17:33

Ravinder Reddy

23,692
6
52
82

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. – David McGuinness Apr 15 '14 at 09:14
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. – Ravinder Reddy Apr 15 '14 at 09:19
here is my delete statement String delete = "DELETE from users WHERE empfirstname = ?"; – David McGuinness Apr 15 '14 at 09:52
Ive dropped the employeeno column just for now – David McGuinness Apr 15 '14 at 09:52
And what was your input for the `?` placeholder? How did you frame the `set...(` method and value? – Ravinder Reddy Apr 15 '14 at 09:54
I changed it like you said ravinder ps.setInt(4, Integer.parseInt(extensionno)); ps.setInt(5, Integer.parseInt(mobileno)); – David McGuinness Apr 15 '14 at 09:58
In the delete tab the records in the table are to displayed first but they wont display and i get the error when i try to move onto the delete jsp page. i think the issue is in the getUsers method. The table wont update either when i try to updat it but i dont get a warning about that – David McGuinness Apr 15 '14 at 10:17
Mapping of java variables to html objects seems to be not correct. I have corrected you on `insert` but you have not posted anything on how `delete` form is set? Your java code says, you are deleting by `employeeno` but you are passing `nick` as `employeeno`. That was causing NFE. Many things as errors ... – Ravinder Reddy Apr 15 '14 at 10:22
I will repost the the relevant revised code Ravinder – David McGuinness Apr 15 '14 at 10:29
Yes. Remember, it is revised and *Relevant*. – Ravinder Reddy Apr 15 '14 at 10:31
Ravinder, i have now edited the code and the revised code is posted. I have fixed the numeric error. the table is still not being updated though. – David McGuinness Apr 15 '14 at 10:52
Not updated means? not inserted or what? Any exceptions? In the catch block add `ex.printStacktrace();`. Post the exception if raised? – Ravinder Reddy Apr 15 '14 at 11:00

java.lang.NumberFormatException: with JSP App and mySQL

2 Answers2