search data using jsp

Question

I want to search record in sql server database and then show it in the web page using jsp. How can I do this please help me. My code is also given bellow. Thanks

<%
    try {
        Class.forName("com.mysql.jdbc.Driver");
        Connection con = DriverManager.getConnection ("jdbc:mysql://localhost:3306/mydb","root","root");
        Statement stmt = con.createStatement();
        ResultSet rs = stmt.executeQuery("SELECT * FROM test where firstName = ?");
        while (rs.next()){
            %>
          <tr bgcolor="#Abc">
            <td> <%=rs.getString("firstName")%> </td>
             <td><%=rs.getString("lastname")%></td> 
    </tr>
      <%
        }
     }catch (Exception ex){
       System.out.println(ex.getMessage());
    }
    %>

It is showing only the heading of the table like FirstName and Password. Not showing the saved data from database. — Shakir Ali, Jun 25 '14 at 09:05

score 1 · Answer 1 · edited May 23 '17 at 10:26

You need to specify the 'first name' that you want to query on.

Assuming it's passed as a parameter in the URL (http://localhost/myapp/mypage.jsp?firstName=Nick), this should do the trick:

<%

  Connection connection = null;
  PreparedStatement statement = null;
  ResultSet resultSet = null;

  try 
  {      
    connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/mydb","root","root");

    statement = connection.prepareStatement("SELECT firstName, lastName FROM test WHERE firstName = ?");
    statement.setString(1, request.getParameter("firstName"));

    resultSet = statement.executeQuery(); 

    while (resultSet.next())
    {
%>

<tr>
  <td><%= resultSet.getString(1) %></td>
  <td><%= resultSet.getString(2) %></td> 
</tr>

<%
    }
  }
  finally
  {
    if (resultSet != null) result.close();
    if (statement != null) statement.close();
    if (connection != null) connection.close();  
  }
%>

Note:

using a PreparedStatement protects against SQL injection attacks as described here.
you haven't needed to load the driver with a Class.forName since the service provider mechanism was introduced.
it's considerably more efficient to 'get' from a ResultSet by index.
you should always be releasing database resources in a finally block.
you should really use a connection pool rather than connecting directly to the database from a JSP.

Thanks for help. I have add your code in my project but it still not showing the data from the database.my code is given bellow please explain if it have any error. thanks — Shakir Ali, Jun 25 '14 at 09:35
I have add the given code to my project but it is still not working. Means not showing the data from database. Please help me out. — Shakir Ali, Jun 25 '14 at 09:41
Most IDEs will debug JSPs but if you have trouble getting that working, then add some debug logging after the connection is made, after the query is run and in the event of an `Exception`. If you're not seeing errors then you should check your database contains the data you're expecting to see — Nick Holt, Jun 25 '14 at 10:49

score 0 · Answer 2 · answered Jun 25 '14 at 08:39

You don't appear to ever be setting what the parameter should be, denoted by ?. I'd expect a setString call on a preparedStatement. Consider this example on how to use JDBC. This isn't as much as JSP issue, rather the parameter isn't set in the SQL query.

score 0 · Answer 3 · answered Jun 25 '14 at 09:17

First of all you are using java.sql.Statement so automatically this goes wrong

stmt.executeQuery("SELECT * FROM test WHERE firstName = ?");

as you dint set the firstName anywhere you can use something like

stmt.executeQuery("SELECT * FROM test WHERE firstName = '"+name+"'");

or use java.sql.PreparedStatement You can accomplish the task like :

PreparedStatement statement = connectionObject.prepareStatement("SELECT * FROM test WHERE firstName = ?");

statement.setString(parameterIndex, String firstName);

search data using jsp

3 Answers3