data querries into database

1 files changed, 103 insertions, 13 deletions

diff --git a/src/database/QueryUtils.java b/src/database/QueryUtils.java
index 5b08655..88a9b6d 100644
--- a/src/database/QueryUtils.java
+++ b/src/database/QueryUtils.java
@@ -19,30 +19,120 @@ public class QueryUtils {
      */
     public static String insertProfile() {
         return "INSERT INTO twitteruser (userid,displayname,timezone,tweetcount,"
-                + "followercount,followedcount,location) "
-                + "SELECT  ?, ?, ?, ?, ?, ?, ? "
+                + "followercount,followedcount,location,tweetname,createdat,language) "
+                + "SELECT  ?, ?, ?, ?, ?, ?, ?, ?, ?, ? "
                 + "WHERE NOT EXISTS "
                 + "(SELECT * FROM twitteruser WHERE userid= ? )";
     }
 
     public static String insertTweet() {
-        return "INSERT INTO tweet (tweetid,createdat,favcount,retweetcount,text) "
-                + "SELECT ?, ?, ?, ?, ? "
+        return "INSERT INTO tweet (tweetid,createdat,favcount,retweetcount,text,"
+                + "coordinates,language,retweetid,replyid,place) "
+                + "SELECT ?, ?, ?, ?, ?, ?, ?, ?, ?, ? "
                 + "WHERE NOT EXISTS "
                 + "(SELECT * FROM tweet WHERE tweetid=? )";
     }
     
-    public static String insetHash() {
+    public static String insertHash() {
         return "INSERT INTO hashtag (tweetid, hashtag) "
-                + "SELECT ?, ? ";
+                + "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 ?, ? "
+                + "WHERE NOT EXISTS "
+                + "(SELECT * FROM mentionsuser WHERE tweetid=? and userid =? )";
+    }
+
+    public static String insertPosted(){
+        return "INSERT INTO ispostedby (tweetid,userid) "
+                + "SELECT ? , ? "
+                + "WHERE NOT EXISTS "
+                + "(SELECT * FROM ispostedby WHERE tweetid= ? )";
+    }
+    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");
 
-    public static void setInsertParams(PreparedStatement statement, JSONObject tweet) throws JSONException, SQLException {
-        statement.setLong(  1, tweet.getLong(  "id"));
-        statement.setString(2, tweet.getString("created_at"));
-        statement.setLong(  3, tweet.getLong(  "favorite_count"));
-        statement.setLong(  4, tweet.getLong(  "retweet_count"));
-        statement.setString(5, tweet.getString("text"));
-        statement.setLong(  6, tweet.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 setInsertBrandParams(PreparedStatement brandStatement, Long id, String brand) throws JSONException, 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 {
+        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 {
+        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 {
+        UrlStatement.setLong(1, id);
+        UrlStatement.setLong(2, userid);
+        UrlStatement.setLong(3, id);
+        UrlStatement.setLong(4, userid);
+        
+        }
+        
 }