Added classes to refactor the database code.

	* DBConnection creates a persistent conn.
	* DBQuery resolves and executes queries.
	* QueryUtils creates sql query text.

4 files changed, 285 insertions, 0 deletions

diff --git a/src/database/DBConnection.java b/src/database/DBConnection.java
new file mode 100644
index 0000000..4227fb3
--- /dev/null
+++ b/src/database/DBConnection.java
@@ -0,0 +1,52 @@
+
+package database;
+
+import java.sql.Connection;
+import java.sql.DriverManager;
+import java.sql.SQLException;
+import java.util.logging.Level;
+import java.util.logging.Logger;
+
+/**
+ * Create a persistent database connection.
+ * @author Maurice Laveaux
+ */
+public class DBConnection {
+    
+    /* The interface to the postgresql database connection. */
+    private Connection m_connection;
+        
+    public DBConnection(final String hostaddress, 
+                        final String port, 
+                        final String databasename,
+                        final String username,
+                        final String password) {
+        
+        String url = "jdbc:postgresql://" + hostaddress + ":" + port + "/" + databasename;
+        
+        try {            
+            m_connection = DriverManager.getConnection(url, username, password);
+        } catch (SQLException ex) {
+            //TODO: retry when db connection fails or something.
+            throw new RuntimeException("cannot connect to host: " + url);
+        }
+    }
+    
+    public void prepare(DBQuery query) {
+        
+    }
+    
+    /**
+     * Closes the connection if it exists.
+     */
+    public void close() {        
+        if (m_connection != null) {
+            try {
+                m_connection.close();
+            } catch (SQLException ex) {
+                /* TODO: what to do here else. */
+                Logger.getLogger(DBConnection.class.getName()).log(Level.SEVERE, null, ex);
+            }
+        }
+    }
+}
diff --git a/src/database/DBQuery.java b/src/database/DBQuery.java
new file mode 100644
index 0000000..602af97
--- /dev/null
+++ b/src/database/DBQuery.java
@@ -0,0 +1,20 @@
+package database;
+
+import java.sql.PreparedStatement;
+
+/**
+ * An interface to known query objects.
+ * @author Maurice Laveaux
+ */
+public class DBQuery {
+    
+    protected PreparedStatement m_prepared; 
+    
+    public DBQuery(final String query) {
+        
+    }
+    
+    public void execute() {
+        
+    }
+}
diff --git a/src/database/Post.java b/src/database/Post.java
new file mode 100644
index 0000000..03ca11e
--- /dev/null
+++ b/src/database/Post.java
@@ -0,0 +1,173 @@
+package database;

+

+

+

+/*

+ * 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<String> 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(Post.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(Post.class.getName());

+                lgr.log(Level.WARNING, ex.getMessage(), ex);

+            }

+        }

+

+    }

+

+    ArrayList<String> getBrands() {

+        ArrayList<String> result = new ArrayList<String>();

+        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;

+    }

+}

diff --git a/src/database/QueryUtils.java b/src/database/QueryUtils.java
new file mode 100644
index 0000000..a120d1c
--- /dev/null
+++ b/src/database/QueryUtils.java
@@ -0,0 +1,40 @@
+package database;
+
+import org.json.JSONObject;
+
+/**
+ * Utilities to create queries.
+ *
+ * @author Maurice Laveaux
+ */
+public class QueryUtils {
+
+    /**
+     * Create an insert tweet.
+     *
+     * @return A valid database query.
+     */
+    public static DBQuery insertProfile() {
+        String query = "INSERT INTO twitteruser (userid,displayname,timezone,tweetcount,"
+                + "followercount,followedcount,location) "
+                + "SELECT  ?, ?, ?, ?, ?, ?, ? "
+                + "WHERE NOT EXISTS "
+                + "(SELECT * FROM twitteruser WHERE userid= ? )";
+        return new DBQuery(query);
+    }
+
+    public static DBQuery insertTweet() {
+        String query = "INSERT INTO tweet (tweetid,createdat,favcount,retweetcount,text) "
+                + "SELECT ?, ?, ?, ?, ? WHERE NOT EXISTS (SELECT * FROM tweet WHERE tweetid= ? )";
+        return new DBQuery(query);
+    }
+
+    public static void setInsertParams(DBQuery query, JSONObject tweet) {
+        /*query.getPrepared().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"));*/
+    }
+}