how to sum columns of html table generated from database in jsp using javascript?_问答_开发者

I am working on my site and i wa开发者_开发问答nt to sum the values in one column of html table using javascript over a jsp. I have found various codes which can add static data which has been put in already but when i use same thing in my code it doesnt work.

The javascript is as follows:-

<script type="text/javascript">
var debugScript = true;

function computeTableColumnTotal(tableId, colNumber)
{       
  var result = 0;

  try
  {
    var tableElem = window.document.getElementById(tableId);           
    var tableBody = tableElem.getElementsByTagName("tbody").item(0);
    var i;
    var howManyRows = tableBody.rows.length;
    for (i=1; i<(howManyRows-1); i++) // skip first and last row (hence i=1, and howManyRows-1)
    {
       var thisTrElem = tableBody.rows[i];
       var thisTdElem = thisTrElem.cells[colNumber];            
       var thisTextNode = thisTdElem.childNodes.item(0);
       if (debugScript)
       {
          window.alert("text is " + thisTextNode.data);
       } // end if

       // try to convert text to numeric
       var thisNumber = parseFloat(thisTextNode.data);
       // if you didn't get back the value NaN (i.e. not a number), add into result
       if (!isNaN(thisNumber))
         result += thisNumber;
     } // end for

  } // end try
  catch (ex)
  {
     window.alert("Exception in function computeTableColumnTotal()\n" + ex);
     result = 0;
  }
  finally
  {
     return result;
  }

}

function finishTable()
{
  if (debugScript)
    window.alert("Beginning of function finishTable");

    var tableElemName = "hikeTable";
        //idhar column define kar raha hai wo
  var totalMilesPlanned = computeTableColumnTotal("hikeTable",2);
  var totalMilesHiked = computeTableColumnTotal("hikeTable",3);

    try 
  {
    var totalMilesPlannedElem = window.document.getElementById("totalMilesPlanned");
    document.getElementById("total_1").innerHTML = totalMilesPlanned;
    var totalMilesHikedElem = window.document.getElementById("totalMilesHiked");
    document.getElementById("total_2").innerHTML = totalMilesHiked ;

   }
   catch (ex)
   {
     window.alert("Exception in function finishTable()\n" + ex);
   }

   return;
}
</script>

This works when html table is like

<html>
<body onload="finishTable();">
<tbody>
<table  id="hikeTable" align="center" border="1" bordercolor="lightslategray">
   <tr>
      <th scope="col">Locations</th>
      <th scope="col"> Date </th>
      <th >Miles (planned)</th>
      <th>Miles (actual)</th>

    </tr>
    <tr>
      <td>Alapocas Woods </td>
      <td>02/18/06</td>
      <td>1324</td>
      <td>1</td>

    </tr>

<tr>
      <td>Alapocas </td>
      <td>02/18/06</td>
      <td>1176576523</td>
      <td>23</td>

    </tr>

<tr>
      <td>Alapocas </td>
      <td>02/18/06</td>
      <td>67</td>
      <td>98</td>
    </tr>
<tr>
      <td colspan="2">Total </td>   
      <td id="total_1"></td>
      <td id="total_2"></td>
    </tr>
</tbody>
<table>
</html>

But my table is something like this :-

<html>
<body onload="finishTable();">
<tbody>
<table  id="hikeTable" align="center" border="1" bordercolor="lightslategray">
   <tr>
      <th scope="col">Locations</th>
      <th scope="col"> Date </th>
      <th >Miles (planned)</th>
      <th>Miles (actual)</th>

    </tr>
<%Class.forName("oracle.jdbc.driver.OracleDriver");
Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:dir","hr","hr");
java.sql.Statement statement = conn.createStatement();
java.sql.ResultSet rs = statement.executeQuery(searchquery);

while(rs.next()){
int buildarea = rs.getInt("build_area");
int numberoflevels = rs.getInt("no_of_levels");
%>
    <tr>
      <td>Alapocas Woods </td>
      <td>02/18/06</td>
      <td><%=buildarea%></td> //here a value comes from database
      <td>1</td>

    </tr>

<tr>
      <td>Alapocas </td>
      <td>02/18/06</td>
      <td>bumberoflevels</td>
      <td>23</td>

    </tr>


      <td colspan="2">Total </td>   
      <td id="total_1"></td>
      <td id="total_2"></td>
    </tr>
</tbody>
<%}%>
<table>
</html>

Please help!!

Just some them over:

int totalBuildArea = 0;
int totalNoOfLevels = 0;
while(rs.next()){
int buildarea = rs.getInt("build_area");
int numberoflevels = rs.getInt("no_of_levels");
%>
...
...

The total row should be out of the loop.

...
...
totalBuildArea += buildarea ;
totalnumberOfLevels  += numberoflevels  ;
<%}%>
<td colspan="2">Total </td>   
      <td id="total_1"><%=totalBuildArea %></td>
      <td id="total_2"><%=totalnumberOfLevels %></td>

That said,

What you are doing does not sound right at all.
For one, accessing the database from JSP is not a good idea. What you need is a good three tire architecture. You can use frameworks like struts. Or at least have your own Business Logic classes, and call them from your servlet.

Even if this is the only way for whatever reason, your JSP itself does not look correct.

Consider this:

<tr>
      <td>Alapocas Woods </td>
      <td>02/18/06</td>
      <td><%=buildarea%></td> //here a value comes from database
      <td>1</td>

    </tr>

<tr>
      <td>Alapocas </td>
      <td>02/18/06</td>
      <td>bumberoflevels</td>
      <td>23</td>

Part of the data is hardcoded. Part of the data is from the database. The columns are not right. <tr>s don't end. For me this does not look like the actual code. Or it is and you have not put complete effort in giving all the details.

Try working on the JSP. Come up with a structure you want. There can be pieces that you don't know how to do. That is fine. Ask them as questions. We are here to help.

But we can help only when you ask a proper question. One simple rule: Imagine You're Trying To Answer The Question.

I leave it to you on whether you want to do the database calls using a servlet or in the JSP itself. The flow is almost the same.

First you create a class for representing the data on the UI. You have a set of (Location, Date, Planned Miles and Actual miles) and then a total of them.

You may want to create a class to represent this (I have made up the name from the id of the HTML, you may want to give a better name):

class Hike
{
    private String location;
    private Date date;
    private Integer builtArea;
    private Integer numberOfLevels;
    //And their getters and setters
}

And a HikeData class

class HikeData
{
    private List<Hike>;
    private Integer totalBuiltArea;
    private Integer totalNumberOfLevels;
    //And their getters and setters
}

//The database call part is fine:

<%Class.forName("oracle.jdbc.driver.OracleDriver");
Connection conn = DriverManager
                  .getConnection("jdbc:oracle:thin:@localhost:1521:dir","hr","hr");
java.sql.Statement statement = conn.createStatement();
java.sql.ResultSet rs = statement.executeQuery(searchquery);

//Fetch the data and populate the above classes
int totalBuiltArea = 0;
int totalNumberOfLevels = 0;
List<Hike> hikeList = new ArrayList<Hike>();
while(rs.next()){
    Hike hike = new Hike();

    hike.setLocation(rs.getString("location"));
    hike.setDate(rs.getDate("date")));

    int builtArea = rs.getInt("build_area");
    hike.setBuiltArea(builtArea);

    int numberOfLevels = rs.getInt("no_of_levels");
    hike.setNumberOfLevels(numberOfLevels);

    totalBuiltArea+= builtArea;
    totalNumberOfLevels += numberOfLevels;
    hikeList.add(hike);
}

HikeData hikeData = new HikeData();
hikeData.setHikeList(hikeList);
hikeData.setTotalNumberOfLevels(totalNumberOfLevels);
hikeData.setTTotalBuiltArea(totalBuiltArea);

//Add the class to request
request.setAttribute("hikeData", hikeData);

The HTML table part is now simpler:

<table  id="hikeTable" align="center" border="1" bordercolor="lightslategray">
   <tr>
      <th scope="col">Locations</th>
      <th scope="col"> Date </th>
      <th >Miles (planned)</th>
      <th>Miles (actual)</th>

    </tr>
<%HikeData hikeData = request.getAttribute("hikeData");
for(Hike hike : hikeData.getHikeList())
{%>
<tr>
      <td><%=hike.getLocation()%></td>
      <td><%=hike.getDate()%></td>
      <td><%=hike.getBuiltArea()%></td> //here a value comes from database
      <td><%=hike.getnumberOfLevels()%></td>

</tr>
<%}>%>
<tr>

    <td colspan="2">Total </td>   
    <td id="total_1"><%=hikeData.getTotalNumberOfLevels()%></td>
    <td id="total_2"><%=hikeData.getTotalBuiltArea()%></td>
</tr>

If you are using a servlet, you move the database part to the servlet or to a service class that is called from the servlet.

If you are continuing with the JSP, you have all these in the JSP itself.

I know adding to request and then retrieving is kind of redundant but I have done so so that if you decide to move this out of the JSP, it is easier.

On a closing Note: This is definitely not the best way to write web apps. There are frameworks to make your job easier and the application maintainable and scalable. For the controller(servlet) part you can use the well known struts framework, [Spring Web flow][2], or the very simple Apache Wicket. For the business logic part, you can use EJB3 or spring. For database access you can use hibernate.

None of this is mandatory, but it always good to have at least a MVC framework i not the database and business logic stuff. Struts makes your life very easy.