This week's book giveaway is in the Testing forum.
We're giving away four copies of The Way of the Web Tester: A Beginner's Guide to Automating Tests and have Jonathan Rasmusson on-line!
See this thread for details.
Win a copy of The Way of the Web Tester: A Beginner's Guide to Automating Tests this week in the Testing forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

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

naveen gandham
Posts: 8
  • Mark post as helpful
  • send pies
  • 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());%>




Albareto McKenzie
Ranch Hand
Posts: 299
  • Mark post as helpful
  • send pies
  • 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: 299
  • Mark post as helpful
  • send pies
  • 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.
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic