This week's giveaway is in the Android forum.
We're giving away four copies of Android Security Essentials Live Lessons and have Godfrey Nolan on-line!
See this thread for details.
The moose likes Servlets and the fly likes retreiving all rows from a mysql table to a jsp page Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Android Security Essentials Live Lessons this week in the Android forum!
JavaRanch » Java Forums » Java » Servlets
Bookmark "retreiving all rows from a mysql table to a jsp page" Watch "retreiving all rows from a mysql table to a jsp page" New topic
Author

retreiving all rows from a mysql table to a jsp page

JC Bismark
Greenhorn

Joined: Jun 27, 2008
Posts: 29
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.



[ November 23, 2008: Message edited by: Bear Bibeault ]
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30130
    
150

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?


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
JC Bismark
Greenhorn

Joined: Jun 27, 2008
Posts: 29
I'm not sure but I have an idea for the first part.Using your advise (Servlet)I retrieve records this way:





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
internet detective
Marshal

Joined: May 26, 2003
Posts: 30130
    
150

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

Joined: Jun 27, 2008
Posts: 29
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

Joined: Jun 27, 2008
Posts: 29
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
internet detective
Marshal

Joined: May 26, 2003
Posts: 30130
    
150

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
internet detective
Marshal

Joined: May 26, 2003
Posts: 30130
    
150

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

Joined: Jun 27, 2008
Posts: 29
Jeanne or anyone else who want to help.

My Servlet looks like:



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
internet detective
Marshal

Joined: May 26, 2003
Posts: 30130
    
150

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

Joined: Jun 27, 2008
Posts: 29
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
internet detective
Marshal

Joined: May 26, 2003
Posts: 30130
    
150

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

Joined: Jun 27, 2008
Posts: 29
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
internet detective
Marshal

Joined: May 26, 2003
Posts: 30130
    
150

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

Joined: Jun 27, 2008
Posts: 29
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
internet detective
Marshal

Joined: May 26, 2003
Posts: 30130
    
150

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

Joined: Jun 27, 2008
Posts: 29
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:



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

Joined: Jan 28, 2008
Posts: 5575

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

Joined: Jun 27, 2008
Posts: 29
Thanks seetharaman.
 
Consider Paul's rocket mass heater.
 
subject: retreiving all rows from a mysql table to a jsp page
 
Similar Threads
JSP Help Regarding the Radio Button
Display results with next and previous buttons
How to split a Array value
Html to database
Help with JSP!!!