File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JSP and the fly likes How to retrive the data using foreach tag or sql data tag  by using jstl Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login

Win a copy of Elasticsearch in Action this week in the Big Data forum!
JavaRanch » Java Forums » Java » JSP
Bookmark "How to retrive the data using foreach tag or sql data tag  by using jstl" Watch "How to retrive the data using foreach tag or sql data tag  by using jstl" New topic

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

naveen gandham

Joined: Oct 15, 2008
Posts: 8

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

Joined: Apr 08, 2009
Posts: 298
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

Joined: Apr 08, 2009
Posts: 298
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.
I agree. Here's the link:
subject: How to retrive the data using foreach tag or sql data tag by using jstl