PreparedStatment - Search Page

[arunkumar subramanian]

Hi

I have a search page with some text boxes (empno, ename, designation, deptno) where the user can enter some values to search. How to create a prepared statement for this search?

// Sample
String url = "jdbc:derby:Fred";
Connection con = DriverManager.getConnection(url, "Fernanda", "J8");
// How to form this preparedstatement and substitute the values?
PreparedStatement preparedStatement =  connection.prepareStatement("select empno, ename, 
designation, joindate, deptno from employee where ...");
ResultSet resultSet = preparedStatement.executeQuery();

[edited to add line break]

[Jeanne Boyarsky]

You would need to use some if statements to append just the entered fields in the query.

[Ryan Webb]

try this.

 
String selectSql = "SELECT * FROM employee WHERE empno=?, ename=?, designation=?, joindate=?, deptno=?";
PrepareStatement pstmt = conn.prepareStatement(selectSql);

pstmt.setInt(1, empnoValue);
pstmt.setString(2, enameValue);
pstmt.setString(3, designationValue);
pstmt.setDate(4, joindateValue);
pstmt.setLong(5, deptnoValue);

ResultSet rs = pstmt.executeQuery();

while (rs.next()) {
    //display values on page/UI
}

hope this helps!
Ryan Webb

[Jeanne Boyarsky]

Ryan,
That won't work. SQL requires "and" rather than "," to separate commas.

.. FROM employee WHERE empno=? and ename=? and designation=? and joindate=? and deptno=?

The reason I didn't suggest this is it implies all of the fields were entered. If any are optional, Arunkumar needs to write logic to concatenate the relevant parts of the where clause.