Create jSON object from SQL HTML data

Question

I'm new to java and javascript. I have started using json objects to return data from our SQL server and it's been nice however, I have run into an issue and I am not sure how to approach it. I'm also not even sure if it is possible but it never hurts to ask.

I have a field in a database that is storing HTML content.

<p>Please verify you have done the following before submitting a trouble-ticket</p>    <ol>   <li>Be sure you are using your correct user name and password (e.e., first initial and last name)</li>   <li>Password are case sensitive</li>   <li>Be sure to select the domain from the drop-down list.</li>   <li>Network cable must be plugged in!</li>  </ol>

I am trying to output it to a JSON object so I can display it on screen with it's current formatting.

I build my json object in a .jsp page like so:

if(rs != null && rs.next()){

  out.print("{\"qteTips\" : [");
  tipList = new StringBuffer();
  int rowCount = 0;

  String HelpfulTipID = null;
  String Title = null;
  String Tip = null;
  String DateCreated = null;
  String Active = null;

  do{
    rowCount++;

    HelpfulTipID = rs.getString("HelpfulTipID");
    Title = rs.getString("Title");
    DateCreated = rs.getString("DateCreated");
    Active = rs.getString("Active");
    Tip = rs.getString("Tip");

    if(rowCount > 1){
      out.print(",");
    }

    out.print("{\"HelpfulTipID\":\""+HelpfulTipID+"\",");
    out.print("\"Title\":\""+Title+"\",");
    out.print("\"DateCreated\":\""+DateCreated+"\",");
    out.print("\"Active\":\""+Active+"\",");
    out.print("\"Tip\":\""+Tip+"\"}");
    if(DEBUG){
      System.out.println("created record #"+rowCount);
    }
  }
  while(rs != null && rs.next());
  out.print("]}");
  try   {rs.close();} catch (SQLException sqle){}
  try{ps.close();} catch (SQLException sqle){}  

}else{
  out.print("{\"error\":\"No tips found.\"}");
}

But when I grab the object it looks like this:

{"qteTips" : [{"HelpfulTipID":"47","Title":"Unable to login to your comptuer?","DateCreated":"06/01/15","Active":"1","Tip":"<p>Please verify you have done the following before submitting a trouble-ticket</p>

<ol>
    <li>Be sure you are using your correct user name and password (e.e., first initial and last name)</li>
    <li>Password are case sensitive</li>
    <li>Be sure to select the domain from the drop-down list.</li>
    <li>Network cable must be plugged in!</li>
</ol>
"}]}

And I get this error on jsonlint.com when attempting to validate it.

Parse error on line 8:
...            "Tip": "<p>Please verify yo
----------------------^
Expecting 'STRING', 'NUMBER', 'NULL', 'TRUE', 'FALSE', '{', '['

Does anyone know a better approach to what I am trying to do?

The JSON spec [does not allow for multi-line strings](http://stackoverflow.com/questions/2392766/multiline-strings-in-json), which is causing parse failures. You can encode your newlines as `\n`, which at least gets your JSON output past some linters I just tried. — rutter, Jun 01 '15 at 23:56
Multiple strings are in the file. I had to use a different approach and build the data from a separate jsp page. Since the data is stored in HTML format, it worked out pretty well. — user3183411, Jun 04 '15 at 22:01

score 0 · Answer 1 · answered Jun 02 '15 at 00:00

0

The problem is the newlines in the returned JSON, you're going to have to escape them, replacing occurrences with \n. I usually do this:

line = line.replaceAll('\r', '').replaceAll('\n', '\\n')

Basically replaces the \r character with nothing and \n with \\n which will render on your JSON as ...string goes here\nNext line

answered Jun 02 '15 at 00:00

Jaime Garcia

6,744
7
49
61

Thanks for the feedback! I did have to use a different approach and build the data directly in a separate jsp page and returned it the main. – user3183411 Jun 04 '15 at 22:02

score 0 · Accepted Answer · answered Jun 04 '15 at 22:10

I've included a jsp page.

<%@ page contentType="text/html"%>
<%@include file="GetAllHelpfulTips.jsp"%> 
<html lang="en">
<head>

The page executes a sql statement that builds the data as it goes through a loop.

ps = conn.prepareStatement(sqlTask.toString());
   rs = ps.executeQuery();
   if(rs != null && rs.next()){

    tipList = new StringBuffer();
    int rowCount = 0;
    
    String HelpfulTipID = null;
    String Title = null;
    String Tip = null;
    String DateCreated = null;
    String Active = null;
    
    do{
     rowCount++;
       
     HelpfulTipID = rs.getString("HelpfulTipID");
     Title = rs.getString("Title");
     DateCreated = rs.getString("DateCreated");
     Active = rs.getString("Active");
     Tip = rs.getString("Tip");
      
     if(tipsPage == "1"){
      System.out.println("from tip page");
      tipList.append("<div id='tip' class='tip'>"+
       "<div class='title row'>"+
        "<div class='col-xs-6'>"+
         "<p class='tipTitle'>"+Title+"</p>"+
        "</div>"+
        "<div class='col-xs-3'>"+
         "<input type='text' id='tipID' name='tipVal' value='"+HelpfulTipID+"'>"+
        "</div>"+
       "</div>"+
       "<div class='tipDetail row'>"+
        "<p>"+Tip+"</p>"+
       "</div>"+
      "</div>"); 
     }else{
     
      tipList.append("<div id='tipMgmt' class='tipMgmt'>"+
       "<div class='row'>");
      if(Active.equals("1")){
       tipList.append("<div class='col-xs-1'><center><i class='fa fa-check active'></i></center></div>");
      }else{
       tipList.append("<div class='col-xs-1'><center><i class='fa fa-times inactive'></i></center></div>");
      }
      
      tipList.append("<div class='col-xs-3'><center>"+DateCreated+"</center></div>"+
        "<div class='col-xs-6'><p class=''>"+Title+"</p></div>"+
        "<div class='col-xs-1'><input type='text' style='display:none' size='1px' id='HelpfulTipID' name='HelpfulTipID' value='"+HelpfulTipID+"'></div>"+
       "</div>"+
      "</div>");
     }    
     ;
     
     
     if(DEBUG){
      System.out.println("created record #"+rowCount);
      System.out.println(tipList);
     }
    }
    while(rs != null && rs.next());

I then just included the new object on the current page.

<div>
 <%=tipList%>
</div>

Since the data that is being stored is HTML it displays exactly as it was entered in the ckeditor!

Create jSON object from SQL HTML data

2 Answers2