Using Prepared Statement to select values for JQuery JTable

Question

I am selecting values based on the search query string for a Jquery JTable handled by Java Servlets. I am receiving the request parameter for filters as well as startPageIndex and recordPageIndex. But there was an Mysql error. kindly view the code / error below.

Selecting columns based on Page

  if(filter != null && !filter.trim().equals("")){
        query = "SELECT C1, C2, C3, C4, C5"
                + "FROM DEPARTMENTS "
                + "WHERE  C2= ? OR C3= ? OR C4= ? OR C5= ? "
                + "ORDER BY department ASC  LIMIT "
                + "? ,  ? ";              

        // the last two values used for table pagination

        pStmt = dbConnection.prepareStatement(query);

        pStmt.setString(1, filter.trim());
        pStmt.setString(2, filter.trim());
        pStmt.setString(3, filter.trim());
        pStmt.setString(4, filter.trim());
        pStmt.setInt(5, startPageIndex);
        pStmt.setInt(6, recordsPerPage);

        ResultSet rs = pStmt.executeQuery(query);
        System.out.println("query executed " + query);

          while (rs.next()) {
                   // Stores in List used to populate in Jquery JTable
          }
    }

MySQL Error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '? OR C3= ? OR C4= ? OR C5= ? ORDER BY C3 ASC ' at line 1

Answer 1

add space before FROM or after C5 in your select clause:

query = "SELECT C1, C2, C3, C4, C5 "
        + "FROM DEPARTMENTS "
        + "WHERE C2 = ? OR C3 = ? OR C4 = ? OR C5 = ? "
        + "ORDER BY department ASC LIMIT ?, ?";