Hello Folks,
I need your help to display the data of multiple databases on jsp.
My requirements is:
To Display total error count of error description of each database server (column wise) on jsp.
like this:
Description DBServer1 DBServer2
Error1 10 22
Error2 20 30
Error3 - 32
...
In the table of each database having two columns: description and error. but data is dynamic. one database having 2 rows and another database could have 10 rows. so database should be mapped.(error count with description)
My program read a properties file where database server ip is defined and fill a array list with database server ip's. and using a loop over this list size to create db connection and all the tasks.
Java Class code:
HashMap hm=null;
public HashMap getHm() {
return hm;
}
public void setHm(HashMap hm) {
this.hm = hm;
}
public String countErrorData(){
//String[][] tempArray = null;
// ReadDBFile();
hm=new HashMap();
for (int j=0; j<dbSizeList.size();j++){
//mdList= new ArrayList();
//ArrayList errorCountList1= new ArrayList();
serverurl=String.valueOf(dbSizeList.get(j));
try {
String connectionUrl=null;
Class.forName("com.mysql.jdbc.Driver");
connectionUrl = "jdbc:mysql://"+serverurl+":"+Global.myport+"/"+Global.database+"?" +
"user="+Global.myuser2+"&password="+Global.mypasswod+"&autoReconnect=true&useUnicode=true&characterEncoding=utf8";
conn = DriverManager.getConnection(connectionUrl);
stmt=conn.createStatement();
query="select distinct err as Description,count(error) as TotalErrors from tablename where err!='NA' GROUP by ERR ORDER BY TotalErrors desc";
try {
ArrayList errorList= new ArrayList();
rs=stmt.executeQuery(query);
rs.last();
row_count=rs.getRow();
rs.beforeFirst();
column_count=dbSizeList.size();
//tempArray = new String[row_count][column_count];
while (rs.next())
{
hm.put(rs.getString(1), rs.getString(2));
//errorCountList1.add(rs.getString(2));
}
dbURLList.add(serverurl);
/*for(int row=0; row < row_count; row++){
for (int i = z; i <= z; i++) {
tempArray[row][i] =(String)errorCountList1.get(row);
System.out.println("tempArray["+row+"]["+i+"]: "+tempArray[row][i]);
mdList.add(tempArray[row][i]);
}
}*/
}
catch(Exception e){
e.printStackTrace();
}
}catch(Exception e)
{
e.printStackTrace();
}
}
request.setAttribute("row_count", String.valueOf(row_count));
request.setAttribute("column_count", String.valueOf(column_count));
request.setAttribute("dbURLList", dbURLList);
return "success";
}
jsp code:
<form id="myForm" method="post">
<%
String column_count= (String)request.getAttribute("column_count");
List dbURLList= (List)request.getAttribute("dbURLList");
%>
<H2 align="center">Error Details</H2>
<table border="1" align="center">
<tr><td>Description</td>
<% for (int i = 0; i < Integer.parseInt(column_count); i++) { %>
<td> <%=dbURLList.get(i)%> </td>
<% } %>
</tr>
<c:forEach items="${hm}" var="entry">
<tr><td> ${entry.key}</td><td> ${entry.value}</td></tr>
</c:forEach>
</table>
</form>
It display the data of last database server and it show in first column of database server column.
suppose i have 2 database. it is showing like this:
Description DBServer1 DBServer2
Error1 1380
Error2 1500
Error3 99
How i deal with this situtaion. should i set the data in a pojo and display the data in row wise. but database server ip is not fix. there may be 2 database or may be 5 database.
Mapping (description with error count of each database) on jsp is also a issue .
Please advice.
Thanks.