diff options
author | S129778 <S129778@S129778.campus.tue.nl> | 2014-05-14 17:42:47 +0200 |
---|---|---|
committer | S129778 <S129778@S129778.campus.tue.nl> | 2014-05-14 17:42:47 +0200 |
commit | fbcb596f8aff9a91362f8c16ae3d0d3f426e12b6 (patch) | |
tree | 74e22db6f9967ed70c992b3e57c7a4f0aff4c420 /src/database | |
parent | 709c04082ffdcc30c93cd13e7aef06eab1f1f428 (diff) | |
download | Goldfarmer-fbcb596f8aff9a91362f8c16ae3d0d3f426e12b6.tar.gz |
Save rating to database
Diffstat (limited to 'src/database')
-rw-r--r-- | src/database/ConnectionBuilder.java | 2 | ||||
-rw-r--r-- | src/database/QueryUtils.java | 102 |
2 files changed, 103 insertions, 1 deletions
diff --git a/src/database/ConnectionBuilder.java b/src/database/ConnectionBuilder.java index 74e5c33..f209344 100644 --- a/src/database/ConnectionBuilder.java +++ b/src/database/ConnectionBuilder.java @@ -25,7 +25,7 @@ public class ConnectionBuilder { public ConnectionBuilder() { dbms = "postgresql"; serverName = "localhost"; - port = 5432; + port = 2014; } public ConnectionBuilder setDbms(String dbms) { diff --git a/src/database/QueryUtils.java b/src/database/QueryUtils.java new file mode 100644 index 0000000..9aab081 --- /dev/null +++ b/src/database/QueryUtils.java @@ -0,0 +1,102 @@ +package database; + +import java.sql.SQLException; +import java.util.Locale; + +/** + * Utilities to create queries. + * + * @author Maurice Laveaux + */ +public class QueryUtils { + public final static String insertRating + = buildQuery("mentionsbrand", new String[]{"tweetid","brand"},"tweetid","brand", "rating"); + public final static String insertProfile + = buildQuery("twitteruser", new String[]{"userid"}, + "userid", "displayname", "timezone", "tweetcount", "followercount", + "followedcount", "location", "tweetname", "createdat::timestamptz", + "language", "description", "verified"); + + public final static String insertTweet + = buildQuery("tweet", new String[]{"tweetid"}, + "tweetid", "createdat::timestamptz", "favcount", "retweetcount", + "text", "coordinates::point", + "language", "retweetid", "replyid", "place", + "userid"); + + public final static String insertHash + = buildQuery("hashtag", null, "tweetid", "hashtag"); + + public final static String insertUserUrl + = buildQuery("userUrl", null, "userid", "url"); + + public final static String insertTweetUrl + = buildQuery("tweetUrl", null, "tweetid", "url"); + + public final static String insertMentions + = buildQuery("mentionsuser", null, "tweetid", "userid"); + + public final static String insertBrand + = buildQuery("mentionsbrand", null, "tweetid", "brand"); + + /** + * Builds an upsert query for a table and keys. + * + * @param table Table to insert or update the data. + * @param primaryKeys The primary keys for the table or null if all keys + * form the primary key. + * @param keys The keys to be inserted or updated. + * @return A SQL query containing named parameters. + */ + private static String buildQuery(String table, String[] primaryKeys, + String... keys) { + String sep = ""; + String fields = ""; // a, b, c + String values = ""; // :a, :b, :c + String set_values = ""; // a = nv.a, b = nv.b, c = nv.c + String pkey_matches = ""; + for (String field : keys) { + String key = field.replaceFirst("::\\w+", ""); + fields += sep + key; + values += sep + ":" + field; + // "u" is "table that gets updated", "nv" is "new values" + set_values += sep + key + " = nv." + key; + sep = ", "; + } + sep = ""; + // assume that all fields form the primary key if pks are missing + if (primaryKeys == null) { + primaryKeys = keys; + } + for (String pkey : primaryKeys) { + assert !pkey.contains("::") : "Specify primary keys when type cast is given"; + pkey_matches += sep + "u." + pkey + " = nv." + pkey; + sep = " AND "; + } + + String sql; + // CTE that holds new values + sql = "WITH nv (" + fields + ") AS (\n"; + sql += " VALUES (" + values + ")\n"; + sql += ")\n"; + // CTE that tries to update the table with new values (PSQL extension) + sql += ", upsert AS (\n"; + sql += " UPDATE " + table + " u SET " + set_values + " FROM nv\n"; + sql += " WHERE " + pkey_matches + " RETURNING 1\n"; + sql += ")\n"; + // if nothing got updated, insert new entry + sql += "INSERT INTO " + table + " (" + fields + ")\n"; + sql += "SELECT " + fields + " FROM nv\n"; + sql += "WHERE NOT EXISTS (SELECT 1 FROM upsert)"; + return sql; + } + + public static void setInsertParams(NamedPreparedStatement statement, + Long tweetid, String brand, int rating) throws SQLException { + statement.setLong("tweetid", tweetid); + statement.setInt("rating", rating); + statement.setString("brand", brand); + + } + +} |