Forum:

Servlets

retreiving all rows from a mysql table to a jsp page

Greenhorn

Posts: 29

posted 14 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

I would like to retreive all 5 rows of mysql table "MemberDetails" to DeleteMember.jsp page.DeleteMember should allows to see the five records.
My MemberDetails table is already created with firstname,lastname,email,phonenum as columns.The jsp page shows the records and allow you to select (with checkboxes)the record(s) for deletion.When I clik submit it return index.jsp.Any thougths ...

I tried to do the DeleteMember.jsp but I'm not sure about using the result set and where to insert the checboxes.

<%@ page language="java" import="java.sql.*"%> 
<html> 
<head>
 
<title>Read from mySQL Database </title>
 
</head> 
<body> 
 
<p align="center"><b>Following records are selected from the 'MemberDetails' table.</b><br> </p> 
 
<div align="center" width="85%"> 
<center> 
<table border="1" borderColor="#ffe9bf" cellPadding="0" cellSpacing="0" width="658" height="63"> 
<tbody> 
<td bgColor="#008080" width="47" align="center" height="19"><font
 
<td bgColor="#008080" width="224" height="19"><font color="#ffffff"><b>Url 
Address</b></font></td> 
<td bgColor="#008080" width="270" height="19"><font color="#ffffff"><b>Description 
of the project</b></font></td> 
 
<% 
String DRIVER = "com.mysql.jdbc.Driver"; 
Class.forName(DRIVER).newInstance(); 
 
 
Connection con=null; 
ResultSet rst=null; 
Statement stmt=null; 
 
try{ 
String url="jdbc:mysql://localhost:3306/myfanclub"; 
 
int i=1; 
con=DriverManager.getConnection(url); 
stmt=con.createStatement(); 
rst=stmt.executeQuery("select * from MemberDetails"); 
while(rst.next()){ 
 
</table> 
</center> 
 
 
</body>

[ November 23, 2008: Message edited by: Bear Bibeault ]

Jeanne Boyarsky

author & internet detective

Posts: 41656

883

I like...

posted 14 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

JC,
A word of advice: it's poor practice to put code (especially JDBC) in a JSP. It's harder to write, harder to debug, harder to maintain and harder to get help on. The preferred approach is to do the JDBC code in a Java Servlet or class.

In any event, there are two next steps:
1) Loop through the result set and print out the values.
2) Create a checkbox with the key as it's value.

Do you know how to do either of these?

[OCP 17 book] | [OCP 11 book] | [OCA 8 book] [OCP 8 book] [Practice tests book] [Blog] [JavaRanch FAQ] [How To Ask Questions] [Book Promos]
Other Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, TOGAF part 1 and part 2

Java 8 (verified skill)
Skill verified by Jeanne Boyarsky

JUnit 5 (verified skill)
Skill verified by Jeanne Boyarsky

JC Bismark

Greenhorn

Posts: 29

posted 14 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

I'm not sure but I have an idea for the first part.Using your advise (Servlet)I retrieve records this way:

package com.example.web;
 
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Connection;
 
import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

/**
 * Servlet implementation class ServletRetrieve
 */
public class ServletRetrieve extends HttpServlet {
private static final long serialVersionUID = 1L;
       
    /**
     * @see HttpServlet#HttpServlet()
     */
    public ServletRetrieve() {
        super();
        // TODO Auto-generated constructor stub
    }
 
/**
 * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
 */
protected void doGet(HttpServletRequest req, HttpServletResponse res) throws ServletException, IOException {
// TODO Auto-generated method stub

res.setContentType("text/html");
    PrintWriter pw = res.getWriter();
    Connection conn = null;
    Statement st=null;

/* Create string of connection url within specified 
   format with machine name, 
    port number and database name. Here machine name id 
    localhost and database name is myfanclub. */

String url = "jdbc:mysql://localhost:3306/myfanclub"; /* myfanclub is my new database that contains 
                                                             my MemberDetails table */
 
   try {
   
// Load JDBC driver "com.mysql.jdbc.Driver"
 Class.forName("com.mysql.jdbc.Driver").newInstance();
/* create a connection to the database by using Connection interface 
     and java.sql.DriverManager's getConnection( ) method *
 
  conn = DriverManager.getConnection(url,"root","root");
        
       
        st = conn.createStatement();
        ResultSet rs = st.executeQuery("Select * from MemberDetails");
      while(rs.next()){
         
        pw.println("First_Name" + "   " + "Last_Name" + "   " + "E_Mail" + "   " + "Phone_num" + "<br>");
        pw.println(rs.getString(2) + "   " + rs.getString(3) +"  "+ rs.getString(4) + "   " + rs.getString(5)+ "<br>");
  
 
    
    }
    catch (Exception e){
      pw.println(e);
    }finally {
            // close all the connections.
    try {
      st.close();
      conn.close();
    } catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
    }

}
    }
}

This servlet deploy should show only all the records ,but no checbox
in front of each record that allow deletion when checked.Do I use :

RequestDispatcher view = req.getRequestDispatcher("Deletemember.jsp");//client then sends it to the JSP file on the server
view.forward(req, res); instead of printing to output the records and forward the ResultSet to a JSP page with a form.I'm thinking about something like this:

Jeanne Boyarsky

author & internet detective

Posts: 41656

883

I like...

posted 14 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

JC,
That is really good! One thing to add to the servlet - storing the data you want to access in the JSP in request.setAttribute("key", value). For example, you could store a List<String[]> or an object of your own creation. I tend to do the later so I can had helper APIs on my object. (To keep even more code out of the JSP.) It is important to note that this object in the request cannot be the resultset itself since that will be closed by the time the JSP executes.

JC Bismark

Greenhorn

Posts: 29

posted 14 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

Hello Jeanne,

Do I need to add the RequestDispacher to my servlet or not? I want to know if the output needs to appear directly in the servlet with pw.println...
or the retrieve of value are fast forward through the object you're talking about. Please,advise...

JC Bismark

Greenhorn

Posts: 29

posted 14 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

Where do you put the object created in your servlet code.After the retreive
of ResustSet? what it looks like, is it something like this:

CreateRecObj cro = new CreateRecObj();

what's helper API? and if the key doesn't come from the ResultSet,how the key will appears in the object? what about the value?

Jeanne Boyarsky

author & internet detective

Posts: 41656

883

I like...

posted 14 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

Originally posted by JC Bismark:
Do I need to add the RequestDispacher to my servlet or not? I want to know if the output needs to appear directly in the servlet with pw.println...
or the retrieve of value are fast forward through the object you're talking about.

Yes, you need the RequestDispatcher. That's what tells the servlet about the JSP. Any printlns you have in the servlet are just for debugging.

The servlet and JSP communicate through this object in the request get/set attribute.

Jeanne Boyarsky

author & internet detective

Posts: 41656

883

I like...

posted 14 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

Originally posted by JC Bismark:
Where do you put the object created in your servlet code....

The typical pattern is:

Then the object is available to the JSP via the request.

I'm going to move this to our Servlets forum since we are now discussing servlet/JSP dynamics rather than JDBC.

JC Bismark

Greenhorn

Posts: 29

posted 14 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

Jeanne or anyone else who want to help.

My Servlet looks like:

package com.record.web;
 
import java.awt.List;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Connection;
import java.util.ArrayList;

import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
 
/**
 * Servlet implementation class ServletRetrieve
 */
public class ServletRetrieve extends HttpServlet {
private static final long serialVersionUID = 1L;
 
    /**
     * Default constructor. 
     */
    public ServletRetrieve() {
        // TODO Auto-generated constructor stub
    }
 
 protected void doGet(HttpServletRequest req, HttpServletResponse res) throws ServletException, IOException {
// TODO Auto-generated method stub
res.setContentType("text/html");
    PrintWriter pw = res.getWriter();
    Connection conn = null;
    Statement st=null;

String url = "jdbc:mysql://localhost:3306/myfanclub"; 
 
   try {
   
// Load JDBC driver "com.mysql.jdbc.Driver"
 Class.forName("com.mysql.jdbc.Driver").newInstance();
     
     /* create a connection to the database 
  conn = DriverManager.getConnection(url,"root","root");
        
        /* createStatement() is used for create statement
        object that is used for 
    sending sql statements to the specified database. */
        st = conn.createStatement();
        java.sql.ResultSet rs = st.executeQuery("Select * from MemberDetails");
        ArrayList result = new ArrayList();
        // JDBC code to get resultset
        while (rs.next()) {  
        CreateRecObj cro = new CreateRecObj(); 
        cro.setFirstName(rs.getString(2)); 
        cro.setLastName(rs.getString(3));
        cro.setEmail(rs.getString(4));
        cro.setPhonenum(rs.getString(5));
                result.add(cro);
            }
         req.setAttribute("records", result);
 
       /* what about the field userId which is the primary key in MemberDetails,with
           rs.getString(1) retrieving the first column */ 
         
        pw.println("First_Name" + "   " + "Last_Name" + "   " + "E_Mail" + "   " + "Phone_num" + "<br>");
        pw.println(rs.getString(2) + "   " + rs.getString(3) +"  "+ rs.getString(4) + "   " + rs.getString(5)+ "<br>");
        
        RequestDispatcher view = req.getRequestDispatcher("Deletemember.jsp");//client then sends it to the JSP file on the server
           view.forward(req, res);
  
 
      }
   catch (Exception e){
      pw.println(e);
    }finally {
            // close all the connections.
    try {
st.close();
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}

}
    }
}

what about the field userId which is the primary key in MemberDetails,with
rs.getString(1) retrieving the first column ?
the result.setAttribute("records",result) allows the object to fast forward the records.
How to see all the records in a JSP table with a checkBox in front of each records allowing deletion of the checked one only,some thoughts?

Jeanne Boyarsky

author & internet detective

Posts: 41656

883

I like...

posted 14 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

Why not put the first column in the object? Or as they key in a map which maps to that object?

JC Bismark

Greenhorn

Posts: 29

posted 14 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

We can put the key in the project,but since the requirement was to make appear the firstname,lastname,email and phone number only, with a checkBox on the left side of each record; the primary key userId has been added by me.
I was thinking that this key should appears in the JSP page as a value to the checkBox.Please,advise...

Also,in the JSP do I have to define a form or not?
How do we define a checkbox with each record?

Please,advise...

Jeanne Boyarsky

author & internet detective

Posts: 41656

883

I like...

posted 14 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

I agree that the primary key should be as a value to the checkbox. That's when you render the JSP page though. It's still ok to store it in the object to transfer it from the servlet to the JSP.

Yes, you need an HTML form defined. You then loop through the list from the request outputting the checkbox as you were originally planning to.

JC Bismark

Greenhorn

Posts: 29

posted 14 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

Jeanne,thank you for your clarifications.
I want to know if I'm in the right track,and also the next step to get the deletion of records with the checkBox checked.Here is my work :

-Does this bring all records with the checkbox for each record?

-For deletion,I have to create a servlet with doPost request and a model class that will do the deletion
Is that right?

-the CreateRecObj class needs to be created to use cro object in the Servlet. Is the following definition right?

Jeanne Boyarsky

author & internet detective

Posts: 41656

883

I like...

posted 14 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

The CreateRecordObject has the right data. Any reason it's missing getters? You need those for the JSP to work.

What do you think this does? (It's not right, but I think trying to explain it will help you see why.) In particular, try to explain where the JSP gets each of the objects from.

JC Bismark

Greenhorn

Posts: 29

posted 14 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

you're right I should add this line :

-What's the best way to delete some records by using the checkBoxes and submit
I can use a another Servlet that control a modele class using DELETE method and the userId? what else is possible?

Jeanne Boyarsky

author & internet detective

Posts: 41656

883

I like...

posted 14 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

Originally posted by JC Bismark:
I can use a another Servlet that control a modele class using DELETE method and the userId? what else is possible?

This is the way to do it. Each checkbox has an id. The second servlet gets those ids through request.getParameterValues() and deletes the records.

JC Bismark

Greenhorn

Posts: 29

posted 14 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

My goal is to display all records from a mysql table (MemberDetails) using Deletemember.jsp page,then allows to check some of them for deletion using one checkBox per records display.

I was able to retrieve mysql table's data to Deletemember.jsp. Also using
a radio button I was able to delete one record at a time.Each record's button having the same name="bt1" with different value="<%=rs.getInt(1)%>".But now,each record have it's own checkBox's name.
From my code of JSP with radio button,the input type="radio" will be replace. I would get
<input type="checkBox" name="bt1">
<input type="checkBox" name="bt2">
<input type="checkBox" name="bt3">
--etc--
<input type="checkBox" name="btn">

How can I write the SQL statement in the HandlereplyModel.java to handle those different checkBoxes with their possible values( checked or not checked )?.

My code for the JSP and HandlereplyModel.java (case of a radio button):

The Deletemember.jsp code:

<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
    pageEncoding="ISO-8859-1"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<%@ page import="java.sql.*" %>
<%@ page import="java.io.*" %> 
 
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>display data from the table using jsp</title>
</head>
<body>
    <form method="post" action="Handlereply.do" >
    <h2>Data from the table 'MemberDetails' of database 'myfanclub'</h2>
<%
      try {
 
          String connectionURL = "jdbc:mysql://localhost:3306/myfanclub";

// declare a connection by using Connection interface
          Connection connection = null;
          /* declare object of Statement interface that is used for executing   sql statements. */
          Statement statement = null;
          // declare a resultset that is uses as a table for output data from that table.
          ResultSet rs = null;
          // Load JBBC driver "com.mysql.jdbc.Driver"
          Class.forName("com.mysql.jdbc.Driver").newInstance();
          
          connection = DriverManager.getConnection(connectionURL, "root", "root");
          /* createStatement() is used for create statement object that is used for sending sql statement to the specified database. */
          statement = connection.createStatement();
 
          // sql query to retrieve values from the specified table.
          String QueryString = "SELECT * from MemberDetails";
          rs = statement.executeQuery(QueryString);
%>
<TABLE cellpadding="15" border="1";">
    <%
    while (rs.next()) {
    %>
    <TR>
        <TD><INPUT TYPE="radio" NAME="bt1" VALUE="<%=rs.getInt(1)%>"></TD>
        <TD><%=rs.getInt(1)%></TD>       
        <TD><%=rs.getString(2)%></TD>   
        <TD><%=rs.getString(3)%></TD>   
        <TD><%=rs.getString(4)%></TD>   
        <TD><%=rs.getString(5)%></TD>  
    </TR>
    <%   }    %>
    
    <%
    // close all the connections.
    rs.close();
    statement.close();
    connection.close();
} catch (Exception ex) {
    %>
 
  
    <%
         out.println("Unable to connect to database.");
       }
    %>
 
</TABLE>
<br><br>
<input type="submit" name="submit" value="Deletemember">
</form>
</body>
</html>

I also have a servlet as controller.
I will use getParameter like I did in the case of radio button :
String useID = req.getParameter("bt1"); to retrieve the info from the JSP .an object of the model class is created
HandlereplyModel hrm = new HandlereplyModel(); and the delete()method is call :
String successOrFailure = hrm.delete(useID);

Seetharaman Venkatasamy

Ranch Hand

Posts: 5575

I like...

posted 14 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

Additionally , you need to know about Transfer Object and DataAccessObject. Google the words you will get enough information

JC Bismark

Greenhorn

Posts: 29

posted 14 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

Thanks seetharaman.

Oh. Hi guys! Look at this tiny ad:

Thread Boost feature

https://coderanch.com/t/674455/Thread-Boost-feature