To display the data of multiple databases on jsp

singh123

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.

rpc1

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:

Your map value have to be array type for example:

HashMap<String, String[]>  hm =  new HashMap<String,String[]>();

The size of String[] have to be equal to dbSizeList.size();

P.S. using generics is not  a good practice, you have to set type of key and value

P.S. Don't use scriplet only JSTL and EL

for example

${column_count} instead of

  <%

  String column_count= (String)request.getAttribute("column_count");

  %>