"The method setInt(int, String) is undefined for the type Statement"

[grendel0]

Hello, I'm trying to insert values from a form to a table (employee) on my database using prepared statements. From what I can tell the connections are fine, am able to query the database (as shown in the code to check the user type of the current logged in user). My code follows all the tutorials ive read on many sites including posts on here. Yet I keep getting this error - "The method setInt(int, String) is undefined for the type Statement", also error occurs for setString. Any help with this would be much appreciated.

JSP Connection Code

<%
String DRIVER = "org.gjt.mm.mysql.Driver";
Class.forName(DRIVER).newInstance();

Connection con=null;
Connection con1=null;
Connection con2=null;
ResultSet rst=null;
ResultSet rst1=null;
Statement stmt=null;
Statement stmt1=null;
Statement stmt2=null;

try{
String url="jdbc:mysql://localhost:3306/hr_data";

String user_id = user.getUsername();
session.putValue("user_id",user_id);

String fname = request.getParameter("fname");
String lname = request.getParameter("lname");
String uname = request.getParameter("username");
String pword = request.getParameter("password");
String uemail = request.getParameter("email");
String qual = request.getParameter("qual");
String dob = request.getParameter("dob");
String age = request.getParameter("age");
String sex = request.getParameter("sex");
String tax = request.getParameter("tax");
String tel = request.getParameter("tel");
String addr = request.getParameter("address");
String posc = request.getParameter("post");
String trvl = request.getParameter("trvl");
String sal = request.getParameter("sal");
String ins = request.getParameter("ins");
String utype = request.getParameter("utype");
String dep = request.getParameter("dep");
String add_emp= "insert into employee (employee_id, username, password, first_name, last_name, email, qualification, dob, age, sex, insurance, travel_method, salary, tax, user_type, phone_number, address, postcode, department_department_id) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
int updateQuery=0;

con=DriverManager.getConnection(url, "tahirs2", "samir");
con1=DriverManager.getConnection(url, "tahirs2", "samir");
stmt=con.createStatement();
stmt1=con1.createStatement();
rst1=stmt1.executeQuery("select employee.user_type from employee where username='"+user_id+"'");
rst=stmt.executeQuery("select * from employee where employee.employee_id = (select max(employee_id) from employee)");
if (rst1.next()&(rst1.getString(1).equals("admin"))){
while(rst.next()){

int num_id= Integer.parseInt((rst.getString(1))) + 1;
String new_id=Integer.toString(num_id);

if(fname!=null && lname!=null && uname!=null&& pword!=null&& uemail!=null&& qual!=null&& dob!=null&& age!=null&& sex!=null&& tax!=null&& tel!=null&& addr!=null&& posc!=null&& trvl!=null&& sal!=null&& ins!=null&& utype!=null&& dep!=null){
                         // check if the text box having only blank spaces
            if(fname!="" && lname!="" && uname!=""&& pword!=""&& uemail!=""&& qual!=""&& dob!=""&& age!=""&& sex!=""&& tax!=""&& tel!=""&& addr!=""&& posc!=""&& trvl!=""&& sal!=""&& ins!=""&& utype!=""&& dep!=""){

	con2=DriverManager.getConnection(url, "tahirs2", "samir");
	stmt2=con2.prepareStatement(add_emp);

	stmt2.setInt(1, new_id);
	stmt2.setString(2, uname);	
	stmt2.setString(3, pword);
	stmt2.setString(4, fname);
	stmt2.setString(5, lname);
	stmt2.setString(6, uemail);
	stmt2.setString(7, qual);	
	stmt2.setString(8, dob);
	stmt2.setString(9, age);
	stmt2.setString(10, sex);
	stmt2.setString(11, ins);
	stmt2.setString(12, trvl);
	stmt2.setString(13, sal);	
	stmt2.setString(14, tax);
	stmt2.setString(15, utype);
	stmt2.setString(16, tel);
	stmt2.setString(17, addr);
	stmt2.setString(18, posc);
	stmt2.setString(19, dep);
	
              updateQuery = stmt2.executeUpdate();
                            if (updateQuery != 0) { 
%>
<br>
<table style="background-color: #E3E4FA;" width="30%" border="1">
<tr><th>Data is inserted successfully in database.</th></tr>
</table>
<%
}}}
else{
out.println("Make sure all fields are filled in correctly");
}
else
{
out.println("Make sure all fields are not empty");
}

%>
<tr>
<td bgColor="#CCFF99" vAlign="top" width="100" align="center" height="19"><%=rst.getString(1)%>.</td>
</tr>
<% 

}}
else
{
out.println("Invalid user privileges, must be administrator");
}

rst.close();
rst1.close();
stmt.close();
stmt1.close();
stmt2.close();
con.close();
con1.close();
con2.close();
}catch(Exception e){
System.out.println(e.getMessage());
}
%>

Full Code:

<%@ page language="java" import="java.sql.*"%>
<%@ page language="java" import="java.io.*" %> 
<jsp:useBean id="user" class="user.UserData" scope="session"/>
  <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
    "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<!-- <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/2002/REC-xhtml1-20020801/DTD/xhtml1-transitional.dtd"> -->
<!-- <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/2002/REC-xhtml1-20020801/DTD/xhtml1-strict.dtd"> -->
<!-- <!doctype html public "-//W3C//DTD HTML 4.01//EN"> -->
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta name="generator" content="HTML Tidy for Linux (vers 1 September 2005), see www.w3.org" />
<title>HRMS: Add Employee</title>
<link rel="stylesheet" type="text/css" media="screen" href="style.css" />
<style type="text/css">
/*<![CDATA[*/
 span.c1 {color:#000;}
/*]]>*/
</style>
</head>
<body>
<div id="container">
<div id="header">
<div id="userbox">

</div>
<h1><strong>Human Resource Management System<span class="c1">.</span></strong></h1>

<ul id="nav">
<li><a href="index.html" title="Home" class="on">Add</a></li>
<li><a href="index2.htm" title="Page 2">Edit</a></li>
<li><a href="#" title="Contact">Delete</a></li>
</ul>

</div>
<div id="sidebar">
<div>
<h3 title="Sidebar">Reports</h3>
<ul>
<li<a href="http://localhost:8080/admin_details_hrms.jsp">
Employee details report</a></li>

<li<a href="http://localhost:8080/admin_training_hrms.jsp">
Training report<a /></li>

<li<a href="http://localhost:8080/admin_skill_hrms.jsp">
Skill report<a /></li>

<li<a href="http://localhost:8080/admin_proj_hrms.jsp">
Project report<a /></li>

<li<a href="http://localhost:8080/admin_job_hrms.jsp">
Job report<a /></li>
<li<a href="http://localhost:8080/admin_dep_hrms.jsp">
Placement report</a></li>
<li<a href="http://localhost:8080/admin_leave_hrms.jsp">
Leave report</a></li>
<li<a href="http://localhost:8080/admin_appraisal_hrms.jsp">
Appraisal report</a></li>
</ul>

<h3 title="Lorem Ipsum">Admin</h3>
<ul>
<li><a href="http://localhost:8080/admin_add_hrms.jsp">Account tools</a></li>
<li<a href="http://localhost:8080/style_logout_hrms.jsp">
Logout</a></li>
</ul>

</div>
</div>

<div id="content">
<h2>Add employee</h2>
<p>Enter the new employee's details in the fields specified below, note all entries must not exceed the limits given.</p>
<p>The username field must be unique, i.e the last name followed by the initial of their first name followed by a number, for instance, "tahirs1".</p>
<div>
</div>

<form>
<table>
<tr>
<td>First name:</td>
<td><input type="text" size= "15" name="fname" /></td>
</tr>
<tr>
<td>Last name:</td>
<td><input type="text" size= "15" name="lname" />
</td>
</tr>
<tr>
<td>Username:</td>
<td><input type="text" size= "15" name="username" /></td>
</tr>
<tr>
<td>Password:</td>
<td><input type="password" size= "15" name="password" /></td>
</tr>
<tr>
<td>Email:</td>
<td><input type="text" size= "15" name="email" /></td>
</tr>
<tr>
<td>Qulification:</td>
<td><input type="text" size= "15" name="qual" /></td>
</tr>
<tr>
<td>Date of birth:</td>
<td><input type="text" size= "15" name="dob" /></td>
</tr>
<tr>
<td>Age:</td>
<td><input type="text" size= "15" name="age" /></td>
</tr>
<tr>
<td>Sex:</td>
<td><input type="text" size= "15" name="sex" /></td>
</tr>
<tr>
<td>Insurance:</td>
<td><input type="text" size= "15" name="ins" /></td>
</tr>
<tr>
<td>Salary:</td>
<td><input type="text" size= "15" name="sal" /></td>
</tr>
<tr>
<td>Travel method:</td>
<td><input type="text" size= "15" name="trvl" /></td>
</tr>
<tr>
<td>User type</td>
<td><select name= utype>
  <option value="user">User</option>
  <option value="admin">Admin</option>
</select></td>
</tr>
<tr>
<td>Tax code:</td>
<td><input type="text" size= "15" name="tax" /></td>
</tr>
<tr>
<td>Telephone:</td>
<td><input type="text" size= "15" name="tel" /></td>
</tr>
<tr>
<td>Address:</td>
<td><input type="text" size= "15" name="address" /></td>
</tr>
<tr>
<td>Post code:</td>
<td><input type="text" size= "15" name="post" /></td>
</tr>
<tr>
<td>Department:</td>
<td><select name= dep>
  <option value="0">Requirements</option>
  <option value="1">Analysis</option>
  <option value="2">Design</option>
  <option value="3">Implementation</option>
  <option value="4">Human resources</option>
</select></td> 
<td><input type="submit" value="Create" /></td>
</tr>
</table>
</form>

<div>
</div>

<table border="1" borderColor="#ffe9bf" cellPadding="0" cellSpacing="0" width="800" height="63">
<tbody>
<td bgColor="#006699" width="100" align="center" height="19"><font color="#ffffff"><b>Target</b></font></td>
<td bgColor="#006699" width="47" height="19"><font color="#ffffff"><b>Start date</b></font></td>
<td bgColor="#006699" width="270" height="19"><font color="#ffffff"><b>Review</b></font></td>


<%
String DRIVER = "org.gjt.mm.mysql.Driver";
Class.forName(DRIVER).newInstance();

Connection con=null;
Connection con1=null;
Connection con2=null;
ResultSet rst=null;
ResultSet rst1=null;
Statement stmt=null;
Statement stmt1=null;
Statement stmt2=null;

try{
String url="jdbc:mysql://localhost:3306/hr_data";

String user_id = user.getUsername();
session.putValue("user_id",user_id);

String fname = request.getParameter("fname");
String lname = request.getParameter("lname");
String uname = request.getParameter("username");
String pword = request.getParameter("password");
String uemail = request.getParameter("email");
String qual = request.getParameter("qual");
String dob = request.getParameter("dob");
String age = request.getParameter("age");
String sex = request.getParameter("sex");
String tax = request.getParameter("tax");
String tel = request.getParameter("tel");
String addr = request.getParameter("address");
String posc = request.getParameter("post");
String trvl = request.getParameter("trvl");
String sal = request.getParameter("sal");
String ins = request.getParameter("ins");
String utype = request.getParameter("utype");
String dep = request.getParameter("dep");
String add_emp= "insert into employee (employee_id, username, password, first_name, last_name, email, qualification, dob, age, sex, insurance, travel_method, salary, tax, user_type, phone_number, address, postcode, department_department_id) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
int updateQuery=0;

con=DriverManager.getConnection(url, "tahirs2", "samir");
con1=DriverManager.getConnection(url, "tahirs2", "samir");
stmt=con.createStatement();
stmt1=con1.createStatement();
rst1=stmt1.executeQuery("select employee.user_type from employee where username='"+user_id+"'");
rst=stmt.executeQuery("select * from employee where employee.employee_id = (select max(employee_id) from employee)");
if (rst1.next()&(rst1.getString(1).equals("admin"))){
while(rst.next()){

int num_id= Integer.parseInt((rst.getString(1))) + 1;
String new_id=Integer.toString(num_id);

if(fname!=null && lname!=null && uname!=null&& pword!=null&& uemail!=null&& qual!=null&& dob!=null&& age!=null&& sex!=null&& tax!=null&& tel!=null&& addr!=null&& posc!=null&& trvl!=null&& sal!=null&& ins!=null&& utype!=null&& dep!=null){
                         // check if the text box having only blank spaces
            if(fname!="" && lname!="" && uname!=""&& pword!=""&& uemail!=""&& qual!=""&& dob!=""&& age!=""&& sex!=""&& tax!=""&& tel!=""&& addr!=""&& posc!=""&& trvl!=""&& sal!=""&& ins!=""&& utype!=""&& dep!=""){

	con2=DriverManager.getConnection(url, "tahirs2", "samir");
	stmt2=con2.prepareStatement(add_emp);

	stmt2.setInt(1, new_id);
	stmt2.setString(2, uname);	
	stmt2.setString(3, pword);
	stmt2.setString(4, fname);
	stmt2.setString(5, lname);
	stmt2.setString(6, uemail);
	stmt2.setString(7, qual);	
	stmt2.setString(8, dob);
	stmt2.setString(9, age);
	stmt2.setString(10, sex);
	stmt2.setString(11, ins);
	stmt2.setString(12, trvl);
	stmt2.setString(13, sal);	
	stmt2.setString(14, tax);
	stmt2.setString(15, utype);
	stmt2.setString(16, tel);
	stmt2.setString(17, addr);
	stmt2.setString(18, posc);
	stmt2.setString(19, dep);
	
              updateQuery = stmt2.executeUpdate();
                            if (updateQuery != 0) { 
%>
<br>
<table style="background-color: #E3E4FA;" width="30%" border="1">
<tr><th>Data is inserted successfully in database.</th></tr>
</table>
<%
}}}
else{
out.println("Make sure all fields are filled in correctly");
}
else
{
out.println("Make sure all fields are not empty");
}

%>
<tr>
<td bgColor="#CCFF99" vAlign="top" width="100" align="center" height="19"><%=rst.getString(1)%>.</td>
</tr>
<% 

}}
else
{
out.println("Invalid user privileges, must be administrator");
}

rst.close();
rst1.close();
stmt.close();
stmt1.close();
stmt2.close();
con.close();
con1.close();
con2.close();
}catch(Exception e){
System.out.println(e.getMessage());
}
%>

</tbody>
</table>

</div>

<div id="footer">
Copyright © All Rights Reserved.

</div>
</body>
</html>

[grendel0]

bump

[peter_budo]

bump
>>You are rude! This is not 24/7 give my codes forum, but voluntary work of people that are willing to help when they have time.

1) First thing first, slap your self for doing database connection from JSP and not from servlet
2) You are handling your connections in bad way. Do NOT create new connection in existing connection and then to try to close it as part of same try block

Therefore move database code to servlet.
Split code in number of methods that you will call once certain conditions rises. Start first with methods to get connection and close connection and then create these 3 methods that will handle different queries. Once you done with that we can talk further

[stultuske]

My code follows all the tutorials ive read on many sites including posts on here. Yet I keep getting this error - "The method setInt(int, String) is undefined for the type Statement"

1. the fact that you get this error, means you're not following the most basic tutorials.
you are trying to give an int a String value. how can this setInt(int, String) not be clear enough?

[grendel0]

bump
>>You are rude! This is not 24/7 give my codes forum, but voluntary work of people that are willing to help when they have time.

1) First thing first, slap your self for doing database connection from JSP and not from servlet
2) You are handling your connections in bad way. Do NOT create new connection in existing connection and then to try to close it as part of same try block

Therefore move database code to servlet.
Split code in number of methods that you will call once certain conditions rises. Start first with methods to get connection and close connection and then create these 3 methods that will handle different queries. Once you done with that we can talk further

Ok. Sorry,I was starting to panic...I will split the connections (did not know it was bad as it works fine on my other pages...), though what is the reason for servlets instead of JSP, is it more efficient instead of duplicating code in JSP pages?

[grendel0]

1. the fact that you get this error, means you're not following the most basic tutorials.
you are trying to give an int a String value. how can this setInt(int, String) not be clear enough?

About that, yeah I copied the wrong code, though as I said the method undefined error still appear with setString(int String)...I tried to see if setInt() would make a difference but forgot to use the string for the parameter.

[stultuske]

and what is the exact error message you get on setString?

putting your programming code in your jsp's is bad practice. there are a lot of benefits of having them separated, not in the least it will be a lot easier to read/maintain the code later on.

servlets are where you want to put your business logic, your jsp's are only supposed to be the UI in which you use(show) the information from the servlet.

[grendel0]

and what is the exact error message you get on setString?

putting your programming code in your jsp's is bad practice. there are a lot of benefits of having them separated, not in the least it will be a lot easier to read/maintain the code later on.

servlets are where you want to put your business logic, your jsp's are only supposed to be the UI in which you use(show) the information from the servlet.

I split the connections and discovered I hadn't even declared stm2 as a prepared statement, rather a redular statement! This fixed the error, I have to agree with you about the amount of code on my JSPs, I'll look at changing the logic to servlets, though my lecturer insisted on everything handled in JSP...Thanks for the insight guys.

[javaAddict]

I split the connections and discovered I hadn't even declared stm2 as a prepared statement, rather a redular statement! This fixed the error, I have to agree with you about the amount of code on my JSPs, I'll look at changing the logic to servlets, though my lecturer insisted on everything handled in JSP...Thanks for the insight guys.

Usually when a teacher tells you to do something that is not the right way, is because they want to keep things simple and not to confuse you by teaching you things you won't understand. Maybe because the right practice could be too advance.

But this is NOT the case here. When your teacher said put everything in the JSP is like saying put everything in the main method.

Imagine having a class with a method inside:

class EmployeeDao {

   public int insert(...arguments...) throws Exception {
     // create connection
     // execute insert query
     updateQuery = stmt2.executeUpdate()
     // close connection
    
     return updateQuery;
   }
}

Then you can do this:

EmployeeDao empDao = new EmployeeDao();
int i = empDao.insert(arguments);
// handle i value

You can call the above 2 lines anywhere you want. In a jsp, in a servlet, in a main method, in a swing application.

Imagine putting the entire code of the insert method in a jsp, and then having to write if-else statements.

Now with this approach you can do this for example in a jsp or better servlet.

EmployeeDao empDao = new EmployeeDao();

String action = request.getParameter("action");

if (action.equals("insert")) {
  int i = empDao.insert(arguments);
  // redirect to the page of your choice
} else if (action.equals("view")) {
   Employee [] employees = empDao.getEmployees();
   // redirect to the page of your choice and display employees
} ....

Now imagine how the code would look like if you replace the calls: empDao.insert(arguments) and empDao.getEmployees() from the above code with the code that you wrote in your jsp.