Dimple Dimple - 5 months ago 13
Java Question

Retrieving record from database in Java by using select option tag

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>




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.

Answer

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>