diff options
author | Peter Wu <peter@lekensteyn.nl> | 2014-05-08 19:05:36 +0200 |
---|---|---|
committer | Peter Wu <peter@lekensteyn.nl> | 2014-05-08 19:05:36 +0200 |
commit | 9fe9d1b848156928a0d3efde4fff80a78f1bf9e1 (patch) | |
tree | e1e987f71ecb90d513e994b9cc4f3aa6c3656bdb /src/database/QueryUtils.java | |
parent | 5f892ac1e0fe6a21a00808cfb0afae2a2c2071dc (diff) | |
download | Datafiller-9fe9d1b848156928a0d3efde4fff80a78f1bf9e1.tar.gz |
Replace JSON by GSON, adding extra validations
Also change reader method, tweets are not received via an observed but
by submitting from the caller.
Added TODO WTF here and there, formatted with Alt + Shift + F.
Diffstat (limited to 'src/database/QueryUtils.java')
-rw-r--r-- | src/database/QueryUtils.java | 117 |
1 files changed, 59 insertions, 58 deletions
diff --git a/src/database/QueryUtils.java b/src/database/QueryUtils.java index 88a9b6d..d0a424d 100644 --- a/src/database/QueryUtils.java +++ b/src/database/QueryUtils.java @@ -1,9 +1,9 @@ package database; +import data.Tweet; +import data.User; import java.sql.PreparedStatement; import java.sql.SQLException; -import org.json.JSONException; -import org.json.JSONObject; /** * Utilities to create queries. @@ -32,19 +32,21 @@ public class QueryUtils { + "WHERE NOT EXISTS " + "(SELECT * FROM tweet WHERE tweetid=? )"; } - + public static String insertHash() { return "INSERT INTO hashtag (tweetid, hashtag) " + "SELECT ?, ? " + "WHERE NOT EXISTS " + "(SELECT * FROM hashtag WHERE tweetid=? and hashtag =? )"; } + public static String insertUrl() { return "INSERT INTO url (tweetid, url) " + "SELECT ?, ? " + "WHERE NOT EXISTS " + "(SELECT * FROM url WHERE tweetid=? and url =? )"; } + public static String insertMentions() { return "INSERT INTO mentionsuser (tweetid, userid) " + "SELECT ?, ? " @@ -52,87 +54,86 @@ public class QueryUtils { + "(SELECT * FROM mentionsuser WHERE tweetid=? and userid =? )"; } - public static String insertPosted(){ + public static String insertPosted() { return "INSERT INTO ispostedby (tweetid,userid) " + "SELECT ? , ? " + "WHERE NOT EXISTS " + "(SELECT * FROM ispostedby WHERE tweetid= ? )"; } - public static String insertBrand(){ + + public static String insertBrand() { return "INSERT INTO mentionsbrand (tweetid,brand) " + "SELECT ? , ? " + "WHERE NOT EXISTS " + "(SELECT * FROM mentionsbrand WHERE tweetid = ? AND brand = ?)"; } - - - public static void setInsertParams(PreparedStatement tweetStatement, PreparedStatement profileStatement, PreparedStatement postedStatement, JSONObject tweet) throws JSONException, SQLException { - tweetStatement.setLong( 1, tweet.getLong( "id")); - tweetStatement.setString(2, tweet.getString("created_at")); - tweetStatement.setLong( 3, tweet.getLong( "favorite_count")); - tweetStatement.setLong( 4, tweet.getLong( "retweet_count")); - tweetStatement.setString(5, tweet.getString("text")); - tweetStatement.setString(6, tweet.getString("coordinates")); - tweetStatement.setString(7, tweet.getString("lang")); - Long id= 0l; - try{ - id=tweet.getJSONObject("retweeted_status").getLong("id"); - } catch(Exception e){} - tweetStatement.setLong(8,id); - id= 0l; - try{ - id=tweet.getJSONObject("in_reply_to_user_id").getLong("id"); - } catch(Exception e){} - tweetStatement.setLong(9,id); - tweetStatement.setString(10,tweet.getString("place")); - tweetStatement.setLong(11, tweet.getLong("id")); - JSONObject twuser= tweet.getJSONObject("user"); - profileStatement.setLong(1, twuser.getLong("id")); - profileStatement.setString(2, twuser.getString("name")); - profileStatement.setString(3, twuser.getString("time_zone")); - profileStatement.setLong(4, twuser.getLong("statuses_count")); - profileStatement.setLong(5, twuser.getLong("followers_count")); - profileStatement.setLong(6, twuser.getLong("friends_count")); - profileStatement.setString(7, twuser.getString("location")); - profileStatement.setString(8, twuser.getString("screen_name")); - profileStatement.setString(9, twuser.getString("created_at")); - profileStatement.setString(10, twuser.getString("lang")); - profileStatement.setLong(11, twuser.getLong("id")); - - postedStatement.setLong(1, tweet.getLong("id")); - postedStatement.setLong(2, twuser.getLong("id")); - postedStatement.setLong(3, tweet.getLong("id")); - + public static void setInsertParams(PreparedStatement tweetStatement, + PreparedStatement profileStatement, + PreparedStatement postedStatement, + Tweet tweet) throws SQLException { + tweetStatement.setLong(1, tweet.id); + tweetStatement.setString(2, tweet.created_at); + tweetStatement.setLong(3, tweet.favorite_count); + tweetStatement.setLong(4, tweet.retweet_count); + tweetStatement.setString(5, tweet.text); + tweetStatement.setString(6, tweet.coordinates); + tweetStatement.setString(7, tweet.lang); + if (tweet.retweeted_status != null) { + tweetStatement.setLong(8, tweet.retweeted_status.id); + } else { + tweetStatement.setLong(8, 0); + } + tweetStatement.setLong(9, tweet.in_reply_to_user_id); + tweetStatement.setString(10, tweet.place); + tweetStatement.setLong(11, tweet.id); + + User twuser = tweet.user; + profileStatement.setLong(1, twuser.id); + profileStatement.setString(2, twuser.name); + profileStatement.setString(3, twuser.time_zone); + profileStatement.setLong(4, twuser.statuses_count); + profileStatement.setLong(5, twuser.followers_count); + profileStatement.setLong(6, twuser.friends_count); + profileStatement.setString(7, twuser.location); + profileStatement.setString(8, twuser.screen_name); + profileStatement.setString(9, twuser.created_at); + profileStatement.setString(10, twuser.lang); + profileStatement.setLong(11, twuser.id); + postedStatement.setLong(1, tweet.id); + postedStatement.setLong(2, twuser.id); + postedStatement.setLong(3, tweet.id); } - - public static void setInsertBrandParams(PreparedStatement brandStatement, Long id, String brand) throws JSONException, SQLException { + + public static void setInsertBrandParams(PreparedStatement brandStatement, + long id, String brand) throws SQLException { brandStatement.setLong(1, id); brandStatement.setString(2, brand); brandStatement.setLong(3, id); brandStatement.setString(4, brand); - - } - public static void setInsertHashParams(PreparedStatement hashStatement, Long id, String text) throws JSONException, SQLException { + } + + public static void setInsertHashParams(PreparedStatement hashStatement, + long id, String text) throws SQLException { hashStatement.setLong(1, id); hashStatement.setString(2, text); hashStatement.setLong(3, id); hashStatement.setString(4, text); - - } - public static void setInsertUrlParams(PreparedStatement UrlStatement, Long id, String text) throws JSONException, SQLException { + } + + public static void setInsertUrlParams(PreparedStatement UrlStatement, + long id, String text) throws SQLException { UrlStatement.setLong(1, id); UrlStatement.setString(2, text); UrlStatement.setLong(3, id); UrlStatement.setString(4, text); - - } - public static void setInsertMentionsParams(PreparedStatement UrlStatement, Long id, Long userid) throws JSONException, SQLException { + } + + public static void setInsertMentionsParams(PreparedStatement UrlStatement, + long id, long userid) throws SQLException { UrlStatement.setLong(1, id); UrlStatement.setLong(2, userid); UrlStatement.setLong(3, id); UrlStatement.setLong(4, userid); - - } - + } } |