From bcc1e048cdc9c6fe1009acd662e5a824922fca65 Mon Sep 17 00:00:00 2001 From: daanpeters Date: Thu, 1 May 2014 11:53:46 +0200 Subject: --- src/main/Main.java | 33 +++++++++- src/main/Post.java | 181 +++++++++++++++++++++++++++++++++++++++++++++++++++++ 2 files changed, 213 insertions(+), 1 deletion(-) create mode 100644 src/main/Post.java (limited to 'src') diff --git a/src/main/Main.java b/src/main/Main.java index 8aa0458..4d11951 100644 --- a/src/main/Main.java +++ b/src/main/Main.java @@ -1,5 +1,10 @@ package main; - +import java.io.File; +import java.io.FileNotFoundException; +import java.util.Scanner; +import java.util.logging.Level; +import java.util.logging.Logger; +import org.json.JSONObject; /** * * @author maurice @@ -11,6 +16,32 @@ public class Main { * @param args */ public static void main(String[] args) { + // TODO code application logic here + + Scanner scanner=null; + try { + scanner = new Scanner(new File("tweets-en.txt")); + } catch (FileNotFoundException ex) { + Logger.getLogger(Main.class.getName()).log(Level.SEVERE, null, ex); + } + try { + while(scanner.hasNextLine()){ + + + JSONObject tweet = new JSONObject(scanner.nextLine()); + JSONObject user = (JSONObject) tweet.get("user"); + Post p = new Post(tweet,user); + p.start(); + } + + + + } catch (Exception e){ + e.printStackTrace(); + } + + + } } diff --git a/src/main/Post.java b/src/main/Post.java new file mode 100644 index 0000000..f3597c0 --- /dev/null +++ b/src/main/Post.java @@ -0,0 +1,181 @@ +package main; + +/* + * To change this template, choose Tools | Templates + * and open the template in the editor. + */ + +import java.io.File; +import java.sql.Connection; +import java.sql.DriverManager; +import java.sql.PreparedStatement; +import java.sql.ResultSet; +import java.sql.SQLException; +import java.sql.Statement; +import java.sql.Timestamp; +import java.util.ArrayList; +import java.util.logging.Level; +import java.util.logging.Logger; +import org.json.JSONException; +import org.json.JSONObject; + +/** + * + * @author s129778 + */ +public class Post implements Runnable{ + Thread runner; + JSONObject tweet; + JSONObject twuser; + + + Connection con = null; + Statement st = null; + ResultSet rs = null; + PreparedStatement pst = null; + String url = "jdbc:postgresql://131.155.240.72:5432/Twitter"; + String user = "postgres"; + String password = "2IOC02"; + public Post() { + } + public void start(){ + runner.start(); + } + public Post(JSONObject tweet, JSONObject user) { + //create new tread + + runner = new Thread(this, "post"); + this.twuser = user; + this.tweet = tweet; + } + + + public void run() { + try { + con = DriverManager.getConnection(url, user, password); + st = con.createStatement(); + rs = st.executeQuery("SELECT VERSION()"); + + //st.executeUpdate("CREATE TABLE name (ID INT)"); + //st.executeUpdate("DROP TABLE name"); + //st.executeQuery("SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity WHERE pg_stat_activity.datname = 'name' AND pid <> pg_backend_pid();"); + //st.executeUpdate("DROP DATABASE name"); + //st.executeUpdate("CREATE ROLE joe2 PASSWORD 'ok' SUPERUSER CREATEDB CREATEROLE INHERIT LOGIN;"); + //select query + String query = "SELECT * FROM tweet WHERE tweetid<=9000000 ORDER BY tweetid"; + pst = con.prepareStatement(query); + rs = pst.executeQuery(); + //process select query + while ( rs.next() ) { + int id = rs.getInt("tweetid"); + System.out.println( "ID = " + id ); + System.out.println (rs.getTimestamp("createdat")); + } + + //insert query with check for existance + //for(int i=0;i<900000;i++){ + String update = "INSERT INTO tweet (tweetid,createdat,favcount,retweetcount,text) SELECT ? , ? ,?,?, ? WHERE NOT EXISTS (SELECT * FROM tweet WHERE tweetid= ? )"; + pst = con.prepareStatement(update); + pst.setLong(1, tweet.getLong("id")); + pst.setString(2, tweet.getString("created_at")); + pst.setLong(3, tweet.getLong("favorite_count")); + pst.setLong(4, tweet.getLong("retweet_count")); + pst.setString(5, tweet.getString("text")); + pst.setLong(6, tweet.getLong("id")); + pst.executeUpdate(); + + + + update = "INSERT INTO ispostedby (tweetid,userid) SELECT ? , ? WHERE NOT EXISTS (SELECT * FROM ispostedby WHERE tweetid= ? )"; + pst = con.prepareStatement(update); + pst.setLong(1, tweet.getLong("id")); + pst.setLong(2, twuser.getLong("id")); + pst.setLong(3, tweet.getLong("id")); + pst.executeUpdate(); + + //update = "INSERT INTO twitteruser (userid,displayname,timezone,tweetcount,followercount,followedcount,location,brand) Select 1"; + update = "INSERT INTO twitteruser (userid,displayname,timezone,tweetcount,followercount,followedcount,location) SELECT ? ,? ,? ,? , ?, ? ,? WHERE NOT EXISTS (SELECT * FROM twitteruser WHERE userid= ? )"; + pst = con.prepareStatement(update); + pst.setLong(1, twuser.getLong("id")); + pst.setString(2, twuser.getString("name")); + pst.setString(3, twuser.getString("time_zone")); + pst.setLong(4, twuser.getLong("statuses_count")); + pst.setLong(5, twuser.getLong("followers_count")); + pst.setLong(6, twuser.getLong("friends_count")); + pst.setString(7, twuser.getString("location")); + pst.setLong(8, twuser.getLong("id")); + pst.executeUpdate(); + + ArrayList brands=getBrands(); + for(String brand : brands){ + update = "INSERT INTO likesbrand (userid,brand) SELECT ? , ? WHERE NOT EXISTS (SELECT * FROM likesbrand WHERE userid= ? AND brand = ?)"; + pst = con.prepareStatement(update); + + pst.setLong(1, twuser.getLong("id")); + pst.setString(2, brand); + pst.setLong(3, twuser.getLong("id")); + pst.setString(4, brand); + pst.executeUpdate(); + } + //} + //update + //pst = con.prepareStatement("UPDATE tweet set text = 'j' where tweetid=1;"); + //pst.executeUpdate(); + + //delete + //pst = con.prepareStatement("DELETE from tweet where tweetid=1;"); + //pst.executeUpdate(); + + + + + + + + + } catch (JSONException ex) { + Logger.getLogger(Post.class.getName()).log(Level.SEVERE, null, ex); + } catch (SQLException ex) { + Logger lgr = Logger.getLogger(Main.class.getName()); + lgr.log(Level.SEVERE, ex.getMessage(), ex); + + } finally { + try { + if (rs != null) { + rs.close(); + } + if (st != null) { + st.close(); + } + if (con != null) { + con.close(); + } + + } catch (SQLException ex) { + Logger lgr = Logger.getLogger(Main.class.getName()); + lgr.log(Level.WARNING, ex.getMessage(), ex); + } + } + + + } + + ArrayList getBrands(){ + ArrayList result= new ArrayList(); + String text=null; + try { + text = tweet.getString("text"); + text = text.toLowerCase(); + } catch (JSONException ex) { + Logger.getLogger(Post.class.getName()).log(Level.SEVERE, null, ex); + } + if(text.contains("samsung")||text.contains("galaxy")){ + result.add("Samsung"); + System.out.println("samsung"); + } else { + result.add("geen"); + System.out.println(text); + } + return result; + } +} -- cgit v1.2.1