diff options
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); - - } - + } } |