java.lang.NumberFormatException: with JSP App and mySQL -
hi i'm getting above error when trying insert data database jsp application
here 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 first name.</td> <td><input type="text" id='empfirstname'></td></tr> <tr><td>please enter surname.</td> <td><input type="text" id='empsurname'></td></tr> <tr><td>please enter department.</td> <td><input type="text" id='dpddept'></td></tr> <tr><td>please enter extension number.</td> <td><input type="text" id='extensionno'></td></tr> <tr><td>please enter mobile number.</td> <td><input type="text" id='mobileno'></td></tr> <tr><td>please enter email address.</td> <td><input type="text" id='emailaddress'></td></tr> <tr><td>please enter email username.</td> <td><input type="text" id='username'></td></tr> <tr><td>please enter 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 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 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 * 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 users 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 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 enough info, appreciated
the exception java.lang.numberformatexception might have thrown value input extensionno field.
your table structure says
extensionno int(11) -- column position 5 in table and tried set value
ps.setstring( 4, dpddept ); // caused error ps.setstring( 5, extensionno ); // not but setstring( 4... caused numberformatexception.
4th placeholder extensionno of type int(11).
setting value dpddept variable, varchar string. when try inserting string int numeric type, database throw error, like:
error 1366 (hy000): incorrect integer value: 'sales' column 'extensionno' @ row 1 how resolve this:
we have set values based on position of query parameter in sql statement not based on column position in table.
string insert = "insert users( empfirstname, empsurname, dpddept, extensionno, " + "mobileno, emailaddress, username, password )" + "values ( ?, ?, ?, ?, ?, ?, ?, ? )"; in above query there 8 placeholders (?).
means should set... 8 values starting 1st 8th parameter.
these placeholder position numbers in insert query not column positions in table.
hence statement
ps.setstring( 2, empfirstname ); is wrong. should
ps.setstring( 1, empfirstname ); see documentation says:
setstring(int parameterindex, string x) throws sqlexception
- parameterindex - first parameter 1, second 2, ...
- x - parameter value
change value setters below:
ps.setstring( 1, empfirstname ); // varchar string ps.setstring( 2, empsurname ); // varchar string ps.setstring( 3, dpddept ); // varchar string // can use setstring on int type columns, // when 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();
Comments
Post a Comment