Split url to tweetUrl and userUrl, misc fixes

* Insert User URLs.
* Fix hash insertion query (copy/paste error...).
* Split url to tweetUrl and userUrl as there is no "url" table anymore.
* Do not execute queries directly via getStmt(), but execute them via
  NamedPreparedStatement such that faulty queries can be printed.
* Fix buildQuery for more than two primary keys.
* Drop comments from prepared statements members in DataFiller, there is
  nothing that you cannot learn from the variable name. Besides there
  was a copy/paste error for mentions.
* Change order of insertion to ensure consistency.
* Inline setting parameters for queries, it is now more transparant.

3 files changed, 45 insertions, 56 deletions

diff --git a/src/database/NamedPreparedStatement.java b/src/database/NamedPreparedStatement.java
index 4c4485a..0569db7 100644
--- a/src/database/NamedPreparedStatement.java
+++ b/src/database/NamedPreparedStatement.java
@@ -66,4 +66,14 @@ public class NamedPreparedStatement {
     public PreparedStatement getStmt() {
         return stmt;
     }
+
+    public void executeUpdate() throws SQLException {
+        try {
+            getStmt().executeUpdate();
+        } catch (SQLException ex) {
+            System.err.println("Query error: " + ex.getMessage());
+            System.err.println(stmt);
+            throw ex;
+        }
+    }
 }
diff --git a/src/database/QueryUtils.java b/src/database/QueryUtils.java
index 66edd1c..c6b8304 100644
--- a/src/database/QueryUtils.java
+++ b/src/database/QueryUtils.java
@@ -26,8 +26,10 @@ public class QueryUtils {
     public final static String insertHash
             = buildQuery("hashtag", null, "tweetid", "hashtag");
 
-    // TODO: split url to userUrl and tweetUrl
-    public final static String insertUrl
+    public final static String insertUserUrl
+            = buildQuery("url", null, "userid", "url");
+
+    public final static String insertTweetUrl
             = buildQuery("url", null, "tweetid", "url");
 
     public final static String insertMentions
@@ -65,7 +67,7 @@ public class QueryUtils {
             primaryKeys = keys;
         }
         for (String pkey : primaryKeys) {
-            pkey_matches = sep + "u." + pkey + " = nv." + pkey;
+            pkey_matches += sep + "u." + pkey + " = nv." + pkey;
             sep = " AND ";
         }
 
@@ -135,22 +137,4 @@ public class QueryUtils {
         brandStmt.setString("brand", brand);
         // TODO: rating (positive)
     }
-
-    public static void setInsertHashParams(NamedPreparedStatement hashStmt,
-            long id, String text) throws SQLException {
-        hashStmt.setLong("tweetid", id);
-        hashStmt.setString("hashtag", text);
-    }
-
-    public static void setInsertUrlParams(NamedPreparedStatement urlStmt,
-            long id, String text) throws SQLException {
-        urlStmt.setLong("tweetid", id);
-        urlStmt.setString("url", text);
-    }
-
-    public static void setInsertMentionsParams(NamedPreparedStatement mentStmt,
-            long id, long userid) throws SQLException {
-        mentStmt.setLong("tweetid", id);
-        mentStmt.setLong("userid", userid);
-    }
 }
diff --git a/src/main/DataFiller.java b/src/main/DataFiller.java
index 10ed774..04d44db 100644
--- a/src/main/DataFiller.java
+++ b/src/main/DataFiller.java
@@ -1,6 +1,7 @@
 package main;
 
 import data.Tweet;
+import data.User;
 import database.NamedPreparedStatement;
 import database.QueryUtils;
 import java.sql.Connection;
@@ -22,34 +23,12 @@ public class DataFiller {
      */
     private final Connection m_connection;
 
-    /**
-     * A single insert tweet that can be used.
-     */
     private final NamedPreparedStatement m_insertTweet;
-
-    /**
-     * A single insert profiles that can be used.
-     */
     private final NamedPreparedStatement m_insertProfile;
-
-    /**
-     * A single insert brand that can be used.
-     */
     private final NamedPreparedStatement m_insertBrand;
-
-    /**
-     * A single insert hashtag that can be used.
-     */
     private final NamedPreparedStatement m_insertHash;
-
-    /**
-     * A single insert url that can be used.
-     */
-    private final NamedPreparedStatement m_insertUrl;
-
-    /**
-     * A single insert url that can be used.
-     */
+    private final NamedPreparedStatement m_insertTweetUrl;
+    private final NamedPreparedStatement m_insertUserUrl;
     private final NamedPreparedStatement m_insertMentions;
 
     /**
@@ -64,7 +43,8 @@ public class DataFiller {
             m_insertProfile = new NamedPreparedStatement(m_connection, QueryUtils.insertProfile);
             m_insertBrand = new NamedPreparedStatement(m_connection, QueryUtils.insertBrand);
             m_insertHash = new NamedPreparedStatement(m_connection, QueryUtils.insertHash);
-            m_insertUrl = new NamedPreparedStatement(m_connection, QueryUtils.insertUrl);
+            m_insertTweetUrl = new NamedPreparedStatement(m_connection, QueryUtils.insertTweetUrl);
+            m_insertUserUrl = new NamedPreparedStatement(m_connection, QueryUtils.insertUserUrl);
             m_insertMentions = new NamedPreparedStatement(m_connection, QueryUtils.insertMentions);
         } catch (SQLException ex) {
             throw new RuntimeException(ex.getMessage());
@@ -73,26 +53,41 @@ public class DataFiller {
 
     public void processTweet(Tweet tweet) {
         try {
+            // ensure that the user and tweet are known before adding relations
+            QueryUtils.setInsertParams(m_insertTweet, m_insertProfile, tweet);
+            m_insertProfile.executeUpdate();
+            m_insertTweet.executeUpdate();
+
             for (Tweet.Hashtag hashtag : tweet.entities.hashtags) {
-                QueryUtils.setInsertHashParams(m_insertHash, tweet.id, hashtag.text);
-                m_insertHash.getStmt().executeUpdate();
+                m_insertHash.setLong("tweetid", tweet.id);
+                m_insertHash.setString("hashtag", hashtag.text);
+                m_insertHash.executeUpdate();
             }
             for (Tweet.Url url : tweet.entities.urls) {
-                QueryUtils.setInsertHashParams(m_insertUrl, tweet.id, url.expanded_url);
-                m_insertUrl.getStmt().executeUpdate();
+                m_insertTweetUrl.setLong("tweetid", tweet.id);
+                m_insertTweetUrl.setString("url", url.expanded_url);
+                m_insertTweetUrl.executeUpdate();
             }
             for (Tweet.Mention mention : tweet.entities.user_mentions) {
-                QueryUtils.setInsertMentionsParams(m_insertMentions, tweet.id, mention.id);
-                m_insertMentions.getStmt().executeUpdate();
+                m_insertMentions.setLong("tweetid", tweet.id);
+                m_insertMentions.setLong("userid", mention.id);
+                m_insertMentions.executeUpdate();
             }
 
-            QueryUtils.setInsertParams(m_insertTweet, m_insertProfile, tweet);
-            m_insertTweet.getStmt().executeUpdate();
-            m_insertProfile.getStmt().executeUpdate();
+            User user = tweet.user;
+            if (user.entities != null) {
+                for (Tweet.Url url : tweet.entities.urls) {
+                    m_insertUserUrl.setLong("userid", user.id);
+                    m_insertUserUrl.setString("url", url.expanded_url);
+                    m_insertUserUrl.executeUpdate();
+                }
+            }
+
+            // determine the user's perception of the brand
             List<String> brands = getBrands(tweet);
             for (String brand : brands) {
                 QueryUtils.setInsertBrandParams(m_insertBrand, tweet.id, brand);
-                m_insertBrand.getStmt().executeUpdate();
+                m_insertBrand.executeUpdate();
             }
         } catch (SQLException ex) {
             Logger.getLogger(DataFiller.class.getName()).log(Level.SEVERE, null, ex);