How do I output results of an SQL query using java servlet

Question

I'm trying to implement a search feature to my web application that allows a user to search a database of products. I've attempted to do this using the following:

The .jsp file for the web page which starts by listing all products (productSearch.jsp):

<%@page import="java.sql.Connection"%>
<%@page import="databaseManagement.DBConnection"%>
<%@page import="java.sql.ResultSet" %>
<%@page import="java.sql.SQLException"%>
<%@page import="java.sql.Connection"%>
<%@page import="java.sql.PreparedStatement"%>

<?xml version="1.0" encoding="ISO-8859-1" ?>
<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
    pageEncoding="ISO-8859-1"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1" />
<title>Search Our Products</title>
</head>
<body>
<form method="post">
        Search:<input type="text" name="Search"> 
        <input type="submit" value="Go">

<table border="2">
<tr>
<td>ID</td>
<td>NAME</td>
<td>DESCRIPTION</td>
<td>PRICE</td>
</tr>
<%

try
{
DBConnection db = new DBConnection();
Connection con = db.getConnection();
PreparedStatement ps = con.prepareStatement("select * from products");
ResultSet rs = ps.executeQuery();
while(rs.next())
{

%>
    <tr>
        <td><%=rs.getInt("ID") %></td>
        <td><%=rs.getString("NAME") %></td>
        <td><%=rs.getString("DESCRIPTION") %></td>
        <td><%=rs.getString("PRICE") %></td>
    </tr>
        <%
}
%>
    </table>
    <%
    rs.close();
    con.close();
    }
catch(Exception e)
{
    e.printStackTrace();
    }

%>
</form>
</body>
</html>

The class to take the user's input and run the query (ProductSearch.java):

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.io.PrintWriter;
import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.Cookie;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import databaseManagement.DBConnection;

@WebServlet("/ProductSearch")
public class ProductSearch extends HttpServlet {
    private static final long serialVersionUID = 1L;

    public ProductSearch() {
        super();
        // TODO Auto-generated constructor stub
    }

    protected void doGet(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        // TODO Auto-generated method stub
    }

    protected void doPost(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        // TODO Auto-generated method stub

        String searchTerm = request.getParameter("Search");

        try {
            DBConnection db = new DBConnection();
            Connection con = db.getConnection();
            PreparedStatement ps = con.prepareStatement("select * from products where name like %?%");
            ps.setString(1, searchTerm);
            ResultSet rs = ps.executeQuery();
            return;
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

    }
}

And lastly a class to handle the connection to the database (DBConnection):

package databaseManagement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class DBConnection {

    public Connection getConnection() {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            // TODO: finish
            //CHANGE USERNAME AND PASSWORD WHEN IMPLIMENTING ON VM
            Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/applicationdata", "root", "safepassword");
            return con;
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
            return null;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }
}

The issue I'm now having is that I'm not sure how to take the results I got from the ProductSearch class and display them back in the productSearch.jsp page.

I'm quite new to this, so I apologise if I've made any glaring mistakes. Any help is greatly appreciated :)

Swati · Accepted Answer · 2019-01-20 16:12:21Z

Firstly , create a class where all values which you want return back is declared and also getter/setter of that variable . i.e Suppose variable user.

import java.util.*;

public class Abc{

    private String user;
    public String getUser() {
        return user;
    }

    public void setUser(String user) {
        this.user = user;
    }



    public Abc(String user) {
        this.user = user;

    }
}

Next , In your ProductSearch.java file put this code :

 protected void doPost(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        // TODO Auto-generated method stub

        String searchTerm = request.getParameter("Search");
 ArrayList<Abc> ab= new ArrayList();


try
  {

String sql1 ="select * from products where name like %?%";

PreparedStatement ps = conn.prepareStatement(sql1);

ps.setString(1,searchTerm);

resultSet = ps.executeQuery();
while(resultSet.next())
{

   Abc b=new Abc();
    b.setUser(resultSet.getString("user"));
     ab.add(b);

 }

request.setAttribute("r1", ab);      
request.getRequestDispatcher("productSearch.jsp").forward(request, response);


   }

catch(Exception s2)
{
   s2.printStackTrace();
}

    }
}

Lastly print result back in your productSearch.jsp page using jstl

<!-- this is use because we use jstl tag-->
    <%@taglib uri="http://java.sun.com/jstl/core_rt" prefix="c" %>

    <table align="center"  border="1">


    <tr bgcolor="#d9ac26">

    <td><b>search</b></td>


    </tr>
  <!--Here we are printing result-->
    <c:forEach var="book" items="${r1}">

        <tr bgcolor="">

            <td>${book.user}</td>
        </tr> 
    </c:forEach>
    </table>

Hope this helps you. Make necessary changes as per your requirement . This is not complete code .

Collectives™ on Stack Overflow

How do I output results of an SQL query using java servlet

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related