Win a copy of Head First Android this week in the Android forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
  • Tim Cooke
  • Campbell Ritchie
  • Paul Clapham
  • Ron McLeod
  • Liutauras Vilda
  • Jeanne Boyarsky
  • Rob Spoor
  • Bear Bibeault
Saloon Keepers:
  • Jesse Silverman
  • Tim Moores
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Piet Souris
  • Al Hobbs
  • salvin francis

How to retrive the data using foreach tag or sql data tag by using jstl

Posts: 8
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

My requirement is to retrive data from the database and show it in atabular format in a jsp page i have done that in this fashion,can any one help me how to use the tags .

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

public class TestCase {

public ArrayList test() throws SQLException{
ArrayList al = new ArrayList();
//getting connection--------------
Connection connection =DBConnection.getConnection();
System.out.println("Connection in testcase"+connection);
String query ="select name ,description,id,status,testsuiteid,testprocedureid, to_char(cdate,'mm/dd/yyyy') ,complete,revision,environment,to_char(start_date,'mm/dd/yyyy'),to_char(end_date,'mm/dd/yyyy'),planned_testcases,completed_testcases,failed,sucess,defects from testcase";
ResultSet rs = null;
Statement st =null;
st =connection.createStatement();
rs = st.executeQuery(query);

while ({
TestCaseBean tc = new TestCaseBean();

double comp;
comp = complete(rs.getInt(13),rs.getInt(14));
String cpl = comp+"%";
double sr;
if(rs.getInt(14) == 0){
sr = 0;
sr = sucessratio(rs.getInt(13), rs.getInt(14),rs.getInt(15));
String sur = sr + "%";
catch(Exception e)
return al;

public double sucessratio(int Completed_testcases,int Planned_testcases ,int failed){
double percent = ((double)(Planned_testcases-failed)/(double)(Completed_testcases))*100;
System.out.println("PERCENT int "+percent);
return percent;

public double complete(int Completed_testcases,int Planned_testcases){
System.out.println("PT : " + Planned_testcases);
System.out.println("CT : " + Completed_testcases);
double value = (((double)(Planned_testcases)*100)/(double)(Completed_testcases));
System.out.println("Value of Completed Percent"+ value);
return value;

-----------------------Bean class for the above --------------------

package com.osius.beans;
public class TestCaseBean {

private String Name;
private String description;
private int id;
private String status;
private int complete;
private int testsuiteid;
private int testprocedureid;
private String cdate;
private String revision;
private String environment;
private int planned_testcases;
private String start_date;
private String end_date;
private int completed_testcases;
private int failed;
private double percent;
public double getValue() {
return value;
public void setValue(double value) {
this.value = value;
private double value;
private int defects;

public int getDefects() {
return defects;
public void setDefects(int defects) {
this.defects = defects;
public double getPercent() {
return percent;
public void setPercent(double percent) {
this.percent = percent;
public int getFailed() {
return failed;
public void setFailed(int failed) {
this.failed = failed;
public String getEnvironment() {
return environment;
public void setEnvironment(String environment) {
this.environment = environment;
public int getComplete() {
return complete;
public void setComplete(int complete) {
this.complete = complete;

public int getPlanned_testcases() {
return planned_testcases;
public void setPlanned_testcases(int planned_testcases) {
this.planned_testcases = planned_testcases;
public int getCompleted_testcases() {
return completed_testcases;
public void setCompleted_testcases(int completed_testcases) {
this.completed_testcases = completed_testcases;
public String getName() {
return Name;
public void setName(String name) {
Name = name;
public String getDescription() {
return description;
public void setDescription(String description) {
this.description = description;
public int getId() {
return id;
public void setId(int id) { = id;
public String getStatus() {
return status;
public void setStatus(String status) {
this.status = status;
public int getTestsuiteid() {
return testsuiteid;
public void setTestsuiteid(int testsuiteid) {
this.testsuiteid = testsuiteid;
public int getTestprocedureid() {
return testprocedureid;
public void setTestprocedureid(int testprocedureid) {
this.testprocedureid = testprocedureid;
public String getCdate() {
return cdate;
public void setCdate(String cdate) {
this.cdate = cdate;
public String getRevision() {
return revision;
public void setRevision(String revision) {
this.revision = revision;

public String getStart_date() {
return start_date;
public void setStart_date(String start_date) {
this.start_date = start_date;
public String getEnd_date() {
return end_date;
public void setEnd_date(String end_date) {
this.end_date = end_date;


--------------------------------here comes my jsp-------------------------

<%@ page language="java" import="java.util.*" pageEncoding="ISO-8859-1"%>
<%@ page language="java" import="com.osius.beans.TestCaseBean"%>
<%@ page language="java" import="com.osius.control.TestCase"%>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<input value =" Home" type="button" class="button" />
<input value ="Test Cases" type="button" class="button"/>
<input value ="Reports" type="button" class="button"/>
<input value ="Settings" type="button" class="button"/><br>

<LINK href="../css/web.css" rel="stylesheet" type="text/css">


<!-- Here creating object of the class -->

<% TestCase tc = new TestCase();
TestCaseBean tsb = new TestCaseBean();
ArrayList al = new ArrayList();
al= tc.test();


<input value ="Plan New Test Cycle" type="button" class="btn"/> // these are buttons
<input value ="Start Test Cycle" type="button" size ="10" class="btn"/>
<input value ="Stop Test Cycle" type="button" size ="10" class="btn"/>

<!-- here starts my requirement -->

<table border="1" cellpadding="0" cellspacing="0" bordercolor="#0AA4C5">

<tr align="right" bgcolor="#0AA4C5" >
<th align ="center"> Date</th>
<th> Description</th>
<th > %Completed</th>
<th> Status</th>
<th> Revision</th>
<th> Environment</th>
<th> Start Date</th>
<th> End Date</th>
<th> Planned Test Cases</th>
<th> Completed Test Cases</th>
<th> Failed</th>
<th> % Success</th>
<th> # Defects</th>


//I have retrived the data and trying to dipaly it in a tabular format , where i am uisng <td <%...%>scriplet.
Instead of the below code i can use foreach tag i think can any one help me please!...

<% for(int i=0; i<al.size(); i++)
tsb =(TestCaseBean)al.get(i);%>
<td width="500"><% out.println(tsb.getCdate()); %> </td>
<td width="500"><% out.println(tsb.getDescription()); %> </td>
<td width="200"><%out.println(tsb.getValue()); %> </td>
<td><% out.println(tsb.getStatus()); %> </td>
<td><% out.println(tsb.getRevision()); %> </td>
<td><% out.println(tsb.getEnvironment()); %> </td>
<td><% out.println(tsb.getStart_date()); %> </td>
<td><% out.println(tsb.getEnd_date()); %> </td>
<td><% out.println(tsb.getPlanned_testcases()); %> </td>
<td><% out.println(tsb.getCompleted_testcases()); %> </td>
<td><% out.println(tsb.getFailed());%></td>
<td><% out.println(tsb.getPercent());%></td>
<td><% out.println(tsb.getDefects());%></td>
<%} %>
<% System.out.println(tsb.getPercent());%>
<% System.out.println(tsb.getValue());%>




Ranch Hand
Posts: 312
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Really difficult to read that code, please use code tags: [code] [/code] and insert the text inside.

Do you mean to use <sql:query... tags?
Albareto McKenzie
Ranch Hand
Posts: 312
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Sorry I didn't read properly the title of your topic,.

Here you have information about creating easy db queries but I read somewhere (I think in this website, I will look for it) that this is not recommended for a real enviroment, just for develop and make test.
    Bookmark Topic Watch Topic
  • New Topic