0

I want to retrieve two columns from database namely system_name and arrival_time and display these in tabular format. Now along with these two columns, I want to display a third column which will represent the number of times system_name appears in the table.

I'm using sql server 2012 and for display I use JSP AND JSTL.

I tried to write code for displaying two columns, but the output shows only the last data entries. The java code is:

public LinkedHashMap < String, String > alarm_Detail()

{
  try {
    con = getConnection();

    stmt = con.createStatement();
    String sql = "select distinct * from i2alarmlog where Ack_status=0 AND Direction='CAME' ";

    stmt.executeQuery(sql);
    rs = stmt.getResultSet();

    while (rs.next()) {

      Map1.put(rs.getString("system_name"), rs.getString("arrival_time"));
    }



  } catch (Exception e) {
    System.out.println("\nException i(String code):" + e);
  } finally {
    closeConnection(stmt, rs, con);
  }

  return Map1;
}

Jsp code is: 

< body >
  < jsp: useBean id = "ab"
class = "alarm.Alarm_Bean" >

  < /jsp:useBean>
<table width = "300px" border = "1" cellspacing="2">
<tr>
<th>system_name</th >
  < th > arrival_time < /th>
</tr >
  < c: forEach
var = "country"
items = "${ab.alarm_Detail()}" >
  < tr >
  < td > $ {
    country.key
  } < /td>
   <td> ${country.value} </td >
  < /tr>  
</c: forEach >

  < /table> 


</body >
David ten Hove
  • 2,748
  • 18
  • 33
SRY_JAVA
  • 323
  • 3
  • 10
  • 21
  • So is your question "how can I get the system_name and arrival_time columns to show all the entries", or "how do I modify my query to show a third column containing the number of times a the particular system_name appears in the query result"? – Tony Dec 09 '14 at 07:25
  • @Tony I have to show both the columns along with a third column containing the number of times a particular system_name appears – SRY_JAVA Dec 09 '14 at 08:20
  • You could try a query like the following to get your three columns: `select distinct * from i2alarmlog inner join (select system_name, count(system_name) as cnt from i2alarmlog group by system_name) as t2 on i2alarmlog.system_name = t2.system_name where Ack_status=0 AND Direction='CAME'`. Your third column will be 'cnt'. I can't help with the second question though. – Tony Dec 09 '14 at 09:23
  • @Tony but how to display it in tabular format..?? – SRY_JAVA Dec 09 '14 at 10:56
  • @Tony to get distinct rows I have to write sql query as select distinct * from i2alarmlog inner join (select system_name, count(system_name) as cnt from i2alarmlog where Ack_status=0 AND Direction='CAME' group by system_name having count(*)>=2 ) as t2 on i2alarmlog.system_name = t2.system_name – SRY_JAVA Dec 09 '14 at 11:46

0 Answers0