1

I am using jquery datatable for my project. It's working well when i have less columns. But when it goes above 18 columns,it refuses to apply sorting and colvis functionalities. My current code is as below.

<table id="testTable" class="display nowrap stripe row-border order-column" cellspacing="0" align="center">
<%
    try {
      String a="";
      String acode = "N/A";
      int atp1, id4;
      query = "SELECT * FROM IETMS;";
      stmt = conn.createStatement();
      rs = stmt.executeQuery(query);
      ResultSetMetaData rsmd=rs.getMetaData();
      int columns = rsmd.getColumnCount();
      if(rs.next()) {
%>
  <thead>
    <tr style="background-color: #3f91bd;color:white;">
      <% for (i=1; i<=columns-2; i++) {%>
        <th><%= rsmd.getColumnLabel(i) %></th>
      <%  } %>
    </tr>
  </thead>
  <tfoot>
    <tr class="header">
      <% for (i=1; i<=columns-2; i++) {%>
      <th><%= rsmd.getColumnLabel(i) %></th>
      <%  }%>
    </tr>
  </tfoot>
<%}%>
<tbody stye="border: 2px solid green;">
  <% while(rs.next()){ %>
    <tr class="clickable">
    <%
      for (i=1; i<=columns-2; i++) {
        if(rs.getObject(i) == null)
        {
          a = "N/A";
        } else
          a=""+rs.getObject(i);
    %>
    <td height="15"><%=a%></td>
    <% }%>
    </tr>
  <%}%>
</tbody>
<%
  } catch (Exception ex) {
      out.println(ex);
  }
%>
</table>

I'd like to move the implementation to a new JSP page that fetches data using JSON, but I'm not really all that familiar with JSON. I've looked at many examples, but it seems like JSON has a fixed schema, and I want to dynamically display whatever data is returned. Is there a way to do this with JSON?

0

1 Answer 1

1

You can dynamically display JSON data, but it's not that easy and it's not necessarily tabular in nature. If you are certain that it follows a particular pattern -- say, an array of objects with no nested objects and each object has the same properties, then I suppose you could display it in a table format by iterating over the array and then iterating across each o the properties in the objects, displaying each property in a column:

Here's a piece of sample code using the Jackson API for instance -- it's not a JSP, but it's not hard to use roughly the same logic in JSP:

import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

import com.fasterxml.jackson.core.JsonFactory;
import com.fasterxml.jackson.core.JsonParser;
import com.fasterxml.jackson.core.JsonToken;

public class JsonTable {
    JsonParser parser;

    public JsonTable( String filename ) {
        try {
            JsonFactory f = new JsonFactory();
            InputStream stream = Thread.currentThread().getContextClassLoader().getResourceAsStream( filename );
            parser = f.createParser(stream);
        } catch ( IOException e ) {
            e.printStackTrace();
        }
    }

    public String getTable() throws Exception {
        try {
            StringBuilder builder = new StringBuilder();
            List<String> header = new ArrayList<>();
            if ( parser.nextToken() != JsonToken.START_ARRAY ) {
                throw new RuntimeException( "Expected an array of objects." );
            }
            while ( parser.nextToken().equals(JsonToken.START_OBJECT) ) {
                buildRow(header,builder);
            }
            if ( parser.currentToken() != JsonToken.END_ARRAY ) {
                throw new RuntimeException( "Expected end of JSON array, got: " + parser.currentToken() );
            }
            wrapRowsWithTable(header,builder);
            return builder.toString();
        } finally {
            parser.close();
        }
    }

    private void wrapRowsWithTable( List<String> columns, StringBuilder builder ) {
        StringBuilder header = new StringBuilder();
        header.append("<table><thead><tr>");
        for( String column : columns ) {
            header.append("<th>" + column + "</th>");
        }
        header.append("</tr></thead>");
        builder.insert(0,header.toString());
        builder.append("</table>");
    }

    private void buildRow( List<String> header, StringBuilder builder ) throws IOException {
        int index = 0;
        builder.append("<tr>");
        while(parser.nextToken() != JsonToken.END_OBJECT)
        switch( parser.currentToken() ) {
        case FIELD_NAME:
            if( header.size() > index ) {
                if( !header.get(index).equals( parser.getCurrentName() ) ) {
                    throw new RuntimeException( "Expected field: " + header.get(index) + ", found: " + parser.getCurrentName() );
                }
            } else {
                header.add(parser.getCurrentName());
            }
            index++;
            break;
        case VALUE_NULL:
        case VALUE_TRUE:
        case VALUE_FALSE:
        case VALUE_NUMBER_INT:
        case VALUE_NUMBER_FLOAT:
        case VALUE_STRING:
            builder.append("<td>" + parser.getText() + "</td>");
            break;
        case VALUE_EMBEDDED_OBJECT:
            throw new RuntimeException( "Expecting an array of objects with no nested objects." );
        default:
            throw new RuntimeException( "Unexpected token: " + parser.currentToken() );
        }
    }
}

This expects the fields to follow the same order, which might not work for every scenario -- you could enhance it to allow fields to appear in any order and/or be optional.

If the data is a JSON object tree or some other kind of pattern, it's not going to be easy to represent that as a table.

Sign up to request clarification or add additional context in comments.

1 Comment

Thank you for response Mr.Geoffrey. I have checked many json examples. But all i got are supports only when the person knows the columns to fetched. It would be great if you have any sample code suitable for above situation?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.