CODEWITHSUNDEEP
javasqljspservletssql-like
developer033
developer033

Reputation: 24894

JSP + SQL (Using Like)

JSP File:

<form action="PatientsController?cmd=search" method="post">
   <input type="text" name="search" placeholder="Search here.."
                        class="form-control text-box single-line">


   <select name="select">
        <option value="id">Id</option>
        <option value="name">Name</option>
        <option value="address">Address</option>
        <option value="cpf">Cpf</option>
        <option value="phone">Phone</option>
        <option value="birthDate">Birth Date</option>
        <option value="gender">Gender</option>
    </select>
</form>

Servlet:

List<Patient> list = new PatientDao().indexFilter(
        request.getParameter("select"),
        request.getParameter("search"));
request.setAttribute("new", list);
request.getRequestDispatcher("Index.jsp")
        .forward(request, response);

PatientDAO:

public List<Patient> indexFilter(String attribute, String condition)
        throws Exception {
    open();

    ps = con.prepareStatement("SELECT * FROM patients WHERE ? like ? ORDER BY id");
    ps.setString(1, attribute);
    ps.setString(2, "%" + condition + "%");
    rs = ps.executeQuery();

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

    while (rs.next())
        list.add(newPatientSetted());

    ps.close();

    close();
    return list;
}

private Patient newPatientSetted() throws Exception {
    return new Patient(rs.getInt(1), rs.getString(2), rs.getString(3),
            rs.getString(4), rs.getString(5), rs.getDate(6), rs
                    .getString(7).equals("M") ? Gender.M : Gender.F);
}

So, I have a problem, I don't know but query is returning nothing (I debugged in DAO and it doesn't join in while loop - in other words, Result Set doesn't have next()), what am I doing wrong? If I remove the "?" after "Where" and put an attribute manually the LIKE operator works.

Upvotes: 0

Views: 2428

Answers (2)

karim mohsen
karim mohsen

Reputation: 2254

When you prepare a statement, the database constructs an execution plan, which it cannot do if the table or column are not there. In other words, placehodlers can only be used for values not for object names or reserved words. You'd have to rely on Java to construct your string in such a case

String sql = "SELECT * FROM patients WHERE "+attribute+" like ? ORDER BY id ";
ps = con.prepareStatement(sql);
ps.setString(1, "%" + condition + "%");
rs = ps.executeQuery();

Upvotes: 0

Jacek Cz
Jacek Cz

Reputation: 1906

SQL parameter is only allowed in places having sense 'value'. Is denied in place of table or column.

 "SELECT * FROM patients WHERE ? like ? ORDER BY id"  -- bad, field 'substitution' is not alowed

 "SELECT * FROM ? WHERE id like ? ORDER BY id"  -- bad, column is not alowed

 "SELECT * FROM patients WHERE Name like ? ORDER BY id"  -- OK,

Background. Lets imagine sql server prepare query tu achieve higher speed. Optimization with unknown column or field cannot be done. Its only image to better understand.

Strict: is denied by standard.

EDIT: extended aswer, how to achieve your target:

 String qry = "SELECT * FROM patients WHERE "+attribute +" like ? ORDER BY id";
 ps = con.prepareStatement(qry);
    ps.setString(1, "%" + condition + "%");
    rs = ps.executeQuery();

Upvotes: 1

Related Questions