How to parse json to a database?

I have a complex json written in a string. I know java and a little of mysql. I need to make a database out of the json.

I'm using some twitter data so the tweets contain the description of the user who tweeted it and in case it's been retweeted, it contains the description of the user who tweeted it before this user.

My objective is to create a user table ( or array or any other data structure ) which contains all the tweets this user tweeted, and all his tweets which have been retweeted.

The tweet object contains around 50-80 objects so giving an example here will make this post really long.


StatusJSONImpl{createdAt=Wed Sep 28 12:04:55 IST 2011, id=118936707830775808, text='RT @nytimesbits: Google's Biggest Threat Is Google', source='web', isTruncated=false, inReplyToStatusId=-1, inReplyToUserId=-1, isFavorited=false, inReplyToScreenName='null', geoLocation=null, place=null, retweetCount=6, wasRetweetedByMe=false, contributors=null, annotations=null, retweetedStatus=StatusJSONImpl{createdAt=Wed Sep 28 05:35:26 IST 2011, id=118838689248985088, text='Google's Biggest Threat Is Google', source='<a href="" rel="nofollow">The New York Times</a>', isTruncated=false, inReplyToStatusId=-1, inReplyToUserId=-1, isFavorited=false, inReplyToScreenName='null', geoLocation=null, place=null, retweetCount=6, wasRetweetedByMe=false, contributors=null, annotations=null, retweetedStatus=null, userMentionEntities=[], urlEntities=[URLEntityJSONImpl{start=34, end=54, url=, expandedURL=,}], hashtagEntities=[], user=UserJSONImpl{id=14434070, name='NYTimes Bits Blog', screenName='nytimesbits', location='The Cloud', description='News and analysis on tech and business. Also here: select retweets from NYT tech writers and friends. Account maintained by David F. Gallagher (@davidfg).', isContributorsEnabled=true, profileImageUrl='', profileImageUrlHttps='', url='', isProtected=false, followersCount=53180, status=null, profileBackgroundColor='9ae4e8', profileTextColor='000000', profileLinkColor='0000ff', profileSidebarFillColor='e0ff92', profileSidebarBorderColor='87bc44', profileUseBackgroundImage=true, showAllInlineMedia=false, friendsCount=139, createdAt=Fri Apr 18 20:49:26 IST 2008, favouritesCount=5, utcOffset=-18000, timeZone='Eastern Time (US & Canada)', profileBackgroundImageUrl='', profileBackgroundImageUrlHttps='', profileBackgroundTiled=true, lang='en', statusesCount=6360, isGeoEnabled=false, isVerified=true, translator=false, listedCount=4671, isFollowRequestSent=false}}, userMentionEntities=[UserMentionEntityJSONImpl{start=3, end=15, name='NYTimes Bits Blog', screenName='nytimesbits', id=14434070}], urlEntities=[URLEntityJSONImpl{start=51, end=71, url=, expandedURL=,}], hashtagEntities=[], user=UserJSONImpl{id=17989546, name='Wolfgang Fasching-K.', screenName='wwwof', location='Vienna', description='Digital ist besser. Fokus: IT & Internet, World News & US Politik, Medien & Pop/Kultur.', isContributorsEnabled=false, profileImageUrl='', profileImageUrlHttps='', url='null', isProtected=false, followersCount=59, status=null, profileBackgroundColor='C0DEED', profileTextColor='333333', profileLinkColor='0084B4', profileSidebarFillColor='DDEEF6', profileSidebarBorderColor='C0DEED', profileUseBackgroundImage=true, showAllInlineMedia=false, friendsCount=64, createdAt=Tue Dec 09 17:09:35 IST 2008, favouritesCount=0, utcOffset=3600, timeZone='Vienna', profileBackgroundImageUrl='', profileBackgroundImageUrlHttps='', profileBackgroundTiled=true, lang='en', statusesCount=269, isGeoEnabled=false, isVerified=false, translator=false, listedCount=4, isFollowRequestSent=false}}


For JSON parsing, I recommend Jackson. Also, in order to validate your input, you should have a look at JSON Schema (for which I have an implementation if you want).

Here is how to parse a JSON in a string using Jackson:

final ObjectMapper mapper = new ObjectMapper();

final JsonNode node = mapper.readTree(yourInput);

// Access members:

node.get(0); // access node 0 of an array

for (final JsonNode entry: node) {
    ... // cycle through array nodes

node.get("foo"); // access property "foo" of an object

node.get("foo").getTextValue(); // access as a text

// etc etc

It also has a s*load of options to serialize to POJOs if that's what you want.

Your first step will be to parse the JSON to get an object graph, using a library like gson or any of several others.

Then (and this seems really general, but it's a pretty open question) it's a matter of determining what the schema should be, creating the tables, and looping through the object graph populating them.

You might look at "document databases" (so-called NoSQL) rather than SQL ones if you're allowed to, as they usually allow the schema to be more fluid.

If your problem is just with Twitter, you can look for dedicated APIs like Twiiter4J or Spring Social, that should provides ready java beans for tweets. If you're realizing a small project Gson is the best solution for parsing. But if your making something more sophisticated, I suggest you to use Jackson for parsing and Hibernate as a middleware between application and sql database.

Need Your Help

Struts 2 : Passing array of String as static param

arrays string struts2 static param

I wanted to declare an action in such a way that I could pass String array of static parameter. I tried the below code:

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.