Integer parsing in a servlet

Question

This is just a simple query in Employee database using Employee ID where ID is an integer value. I did following operation to parse the value of ID in integer.

     String value = request.getParameter("Employee_ID");
     int id = Integer.parseInt(value);
  // Step 3: Execute a SQL SELECT query
     String sqlStr = "select * from Employee where ID = id ";

but it gives me following error:

Multiple markers at this line
    - Line breakpoint:QueryServlet [line: 45] - doGet(HttpServletRequest, 
     HttpServletResponse)
    - The value of the local variable id is not used

My html file:

<html>
<head>
  <title>Employee Details</title>
</head>
<body>
  <h2>Employee Details</h2>
  <form method="get" action="http://localhost:9999/abcd/query">
    <b>Select Employee ID:</b>
    <input type="text" name="Employee_ID" value="ex101">

    <input type="submit" value="Search">
  </form>
</body>
</html>

What's the error in your question? One is a breakpoint, and the other one an warning. I can't find any error in your question — msrd0, Sep 08 '14 at 15:22
@msrd0 the problem is that OP wants/need to use this `id` variable but it's not able to do it. — Luiggi Mendoza, Sep 08 '14 at 15:29

score 2 · Accepted Answer · edited May 23 '17 at 12:01

The problem is that you're not using the id variable in your code. This is a literal string:

"select * from Employee where ID = id "
                                   ^ here id is part of the string, it's not the id variable

The naive way to make this work would be concatenating the variable to the String

String sqlStr = "select * from Employee where ID = " + id;

But this is not the right way to create dynamic queries. You should use a PreparedStatement and pass the parameters accordingly. This is how the code should look like:

//placeholder for id variable
String sqlStr = "select * from Employee where ID = ?";
//retrieve the connection to database
Connection con = ...;
//prepare the statement from the connection
PreparedStatement pstmt = con.prepareStatement(sqlStr);
//pass the id as parameter to the prepared statement
pstmt.setInt(id);
//execute the statement
ResultSet rs = pstmt.execute();

Also, make sure to split your code into layers. All this code related to the database connectivity and SQL execution belongs to a DAO layer.

More info:

score 1 · Answer 2 · answered Sep 08 '14 at 15:19

1

Change

String sqlStr = "select * from Employee where ID = id ";

by

String sqlStr = "select * from Employee where ID = "+ id ;

However, you should read something about SQL Injection

answered Sep 08 '14 at 15:19

Andres

10,561
4
45
63

2

This is technically true but it's not the right way to do it. – Luiggi Mendoza Sep 08 '14 at 15:20
Not only SQL Injection but code maintainability and SQL statement execution performance. – Luiggi Mendoza Sep 08 '14 at 15:25

ajay.patel · Answer 3 · 2014-09-08T15:28:16.553

0

Below should work:

String sqlStr = "select * from Employee where ID ="+id;

You have to concatenate the id to the query string that you wrote.

Edit as mentioned in the comments, its better to use parameterized query to prevent sql injection.

edited Sep 08 '14 at 15:28

answered Sep 08 '14 at 15:19

ajay.patel

1,957
12
15

1

Similar to @Andres' answer, this is technically true but it's not the right way to do it. – Luiggi Mendoza Sep 08 '14 at 15:21

Integer parsing in a servlet

3 Answers3