From c47ee5696665af5bdaf85e30fe3514009cde2a7c Mon Sep 17 00:00:00 2001 From: Peter Wu Date: Sat, 10 May 2014 16:14:37 +0200 Subject: 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. --- src/database/NamedPreparedStatement.java | 10 +++++ src/database/QueryUtils.java | 26 +++---------- src/main/DataFiller.java | 65 +++++++++++++++----------------- 3 files changed, 45 insertions(+), 56 deletions(-) (limited to 'src') 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 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); -- cgit v1.2.1