How to save the JSON data to the database

Question

I am trying to save the JSON response of a website to the database. Latter I am accessing this JSON string for processing the data. But I am not able to parse it as JSON object. On analyzing I realize that the characters of strings needs to be escaped. Since I am saving the data as a string the data results which am getting back as a JSON data are not escaped in the database. Is there a way out how I can save the JSON data to the database.

Example:

   {"RULE":[{"replace":{"value":"","type":"text"},"match":{"value":"<a [^>]*><img   src="[^"]*WindowsLiveWriter/IconsfordifferentSocialBookmarkingSites[^>]*>\s*</a>","type":"text"}},{"replace":{"value":"","type":"text"},"match":{"value":"<a [^>]*><img src="[^"]*WindowsLiveWriter/IconsfordifferentSocialBookmarkingSites[^>]*>\s*</a>","type":"text"}}]}

Want to save in database as (also getting the response as) i.e. the " and \ are escaped

   {"RULE":[{"replace":{"value":"","type":"text"},"match":{"value":"<a [^>]*><img src=\"[^\"]*WindowsLiveWriter/IconsfordifferentSocialBookmarkingSites[^>]*>\\s*</a>","type":"text"}},{"replace":{"value":"","type":"text"},"match":{"value":"<a [^>]*><img src=\"[^\"]*WindowsLiveWriter/IconsfordifferentSocialBookmarkingSites[^>]*>\\s*</a>","type":"text"}}]}

Here is the code I have used to save the data in the database

                      // here the raw_data is the data from the website
                            JSONObject jo = new JSONObject(raw_data);

            // Get the JSONObject value associated with the search result key.
            jo = jo.getJSONObject("pipe");
                        jo = jo.getJSONObject("definition");

                            String def=jo.toString();

                JSONArray jo1=jo.getJSONArray("modules");
                JSONArray jo2=jo.getJSONArray("wires");

                    /*
                     * Write the contents in the data base
                     * 
                     *              
                     */
                    def =def.replaceAll( "[']", "\\\\\'" ); //creates problem for strings with '
                    def =def.replaceAll( "&", "%26" ); 

                    String tablename="PipesTable2";
                    System.out.println(def);
                    database d=new database();

Solution 1

Have you considered using a library like Jackson ( http://wiki.fasterxml.com/JacksonHome ) to translate between Json objects and Java objects? Then you can use standard Java persistence tools and libraries to save it out to/read it in from your database, and it will handle all your escaping automatically.

Jackson has two main ways that it translates between Json and Java objects. If you're planning on using direct JDBC calls for your persistence and you don't want to do much other processing, then you can get away with "Simple Data Binding". This just translates between Json and Java's built-in types (List, Map, String, Boolean, Number).

If you're planning on doing Java processing on the data, or you want to use a persistence framework like Hibernate, you'll need "Full Data Binding". This uses POJOs and annotations to provide a more complex structure to your objects.

There is a good, very concise tutorial covering both options here: http://wiki.fasterxml.com/JacksonInFiveMinutes

Solution 2

I suggest you to create an class having properties mapped to json. You can convert object to json or json to object using third party tool.you can use Gson.

Now what you can do when you get the josn you can convert this json to object and save that object's properties to database. when you retrieve the value, set these value to object's properties and then convert this object to json.

You can find Gson here - http://code.google.com/p/google-gson/

This is easy to use.

Comments

[Judy]

Thanks I will try. I haven't looked at these libraries. Can you also direct me to some examples. That will be really helpful.

[Rishav]

Hey, the link is broken can you please re-upload. Thanks! (Yeah I know its 7 years from the answer)