Issue
I want to retrieve record from database by clicking on the option selected from drop down list and it as a table on the web page. But after implementing following code the web page is blank now what should I do? Any type of help will be appreciable. Here is my index.jsp page:
<%@page contentType="text/html" pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>JSP Page</title>
</head>
<body>
<form name="f1" action="portal" method="POST">
<h3>Name of the Book : </h3>
<select name="book" id="book">
<option value="">Select</option>
<option value="1">The Pilgrims Progress</option>
<option value="2">Robinson Crusoe</option>
<option value="3">Gullivers Travels</option>
<option value="4">Clarissa</option>
<option value="5">Tom Jones</option>
<option value="6">The Life and Opinions of Tristram Shandy, Gentleman</option>
<option value="7">Emma</option>
<option value="8">Frankenstein</option>
<option value="9">Nightmare Abbey</option>
<option value="10">The Narrative of Arthur Gordon Pym of Nantucket</option>
<option value="11">Sybil</option>
<option value="12">Jane Eyre</option>
<option value="13">Wuthering Heights</option>
<option value="14">Vanity Fair</option>
<option value="15">David Copperfield</option>
<option value="16">The Scarlet Letter</option>
<option value="17">Moby-Dick</option>
<option value="18">Alices Adventures in Wonderland</option>
<option value="19">The Moonstone</option>
<option value="20">Little Women</option>
<option value="21">Middlemarch</option>
</select>
<input type="submit" value="submit" />
</form>
</body>
</html>
And here is my servlet page for retrieving data from database
package com;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
public class portal extends HttpServlet {
/**
* Processes requests for both HTTP <code>GET</code> and <code>POST</code>
* methods.
*
* @param request
* servlet request
* @param response
* servlet response
* @throws ServletException
* if a servlet-specific error occurs
* @throws IOException
* if an I/O error occurs
*/
protected void processRequest(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
response.setContentType("text/html;charset=UTF-8");
PrintWriter out = response.getWriter();
try {
String book = request.getParameter("book");
Class.forName("com.mysql.jdbc.Driver");
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/mysql", "root", "password");
PreparedStatement pt = conn.prepareStatement("Select * from book where Book_Name = ?");
pt.setString(1, book);
out.print("<table width = 75%>");
out.print("<center><h1>Welcome To The Portal</h1></center>");
ResultSet rs = pt.executeQuery();
ResultSetMetaData rsd = rs.getMetaData();
while (rs.next()) {
out.print("<tr>");
out.print("<td>" + rsmd.getColumnName(1) + "</td>");
out.print("<td>" + rs.getString(1) + "</td></tr>");
out.print("<tr><td>" + rsmd.getColumnName(2) + "</td>");
out.print("<td>" + rs.getString(2) + "</td></tr>");
out.print("<tr><td>" + rsmd.getColumnName(3) + "</td>");
out.print("<td>" + rs.getString(3) + "</td></tr>");
out.print("<tr><td>" + rsmd.getColumnName(4) + "</td>");
out.print("<td>" + rs.getString(4) + "</td></tr>");
RequestDispatcher rd = request.getRequestDispatcher("logout.jsp");
rd.include(request, response);
}
out.println("</table>");
}
catch (Exception e) {
out.println(e);
}
}
// <editor-fold defaultstate="collapsed" desc="HttpServlet methods. Click on the
// + sign on the left to edit the code.">
/**
* Handles the HTTP <code>GET</code> method.
*
* @param request
* servlet request
* @param response
* servlet response
* @throws ServletException
* if a servlet-specific error occurs
* @throws IOException
* if an I/O error occurs
*/
@Override
protected void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
processRequest(request, response);
}
/**
* Handles the HTTP <code>POST</code> method.
*
* @param request
* servlet request
* @param response
* servlet response
* @throws ServletException
* if a servlet-specific error occurs
* @throws IOException
* if an I/O error occurs
*/
@Override
protected void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
processRequest(request, response);
}
/**
* Returns a short description of the servlet.
*
* @return a String containing servlet description
*/
@Override
public String getServletInfo() {
return "Short description";
}// </editor-fold>
}
now I don't know where I miss something.
Solution
I suggest that you use your servlet as a controller that control the data in your application and not as a vue where you write html tags
this example may help you :
Firstly, create a serialisable java class where you put communicate with database :
public class BookData implements Serializable {
private String ISBN;
private String titre;
private String auteur;
private int ID;
private String editeur;
// ADD GETTER AN SETTER METHODS
public BookData(String titre, String auteur, int ID, String editeur, String ISBN) {
this.titre = titre;
this.auteur = auteur;
this.ID = ID;
this.editeur = editeur;
this.ISBN = ISBN;
}
public List<BookData> loadData(String book) {
List<BookData> actorList = new ArrayList<BookData>();
com.mysql.jdbc.PreparedStatement ps = null;
ResultSet rs = null;
String url = "jdbc:mysql://127.0.0.1:3306/DATABASENAME";// CHANGE
String name = "NAME";// CHANGE
String pw = "PWD";// CHANGE
String driver = "com.mysql.jdbc.Driver";
Connection connexion = null;
try {
Class.forName(driver).newInstance();
connexion = DriverManager.getConnection(url, name, pw);
String q = "Select * from book where Book_Name ='" + book + "'";
Statement commande = connexion.createStatement();
rs = commande.executeQuery(q);
while (rs.next()) {
BookData bk = new BookData(rs.getString("Book_Title"), rs.getString("Book_Author"), rs.getInt("ID"),
rs.getString("Publisher"), rs.getString("ISBN"));/* CHANGE COLUMN NAMES */
actorList.add(bk);
}
return actorList;
} catch (Exception e) {
e.printStackTrace();
return null;
} finally {
try {
rs.close();
connexion.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
}
Secondly the servlet :
public class EXAMPLE_SERVLET extends HttpServlet {
@Override
protected void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
String option = request.getParameter("book");
BookData dao = new BookData();
List<BookData> list = dao.loadData(option);
request.setAttribute("booklist", list);
RequestDispatcher view = request.getRequestDispatcher("test.jsp");
view.forward(request, response);
}
@Override
protected void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
}
}
then the JSP ((test.jsp))
<table>
<thead>
<tr>
<th>titre</th> //...COLUMNS
</tr>
</thead>
<tbody>
<c:forEach var="employee" items="${booklist}">
<tr>
<td style="width: 110px; color: #3278b3;">${employee.titre}</td>
//...ROWS
</tr>
</c:forEach>
</tbody>
</table>
Answered By - Taha
