Use upsert queries, convert to named parameter statements

Get rid of ispostedby, move it to tweet table ("userid")

1 files changed, 69 insertions, 0 deletions

diff --git a/src/database/NamedPreparedStatement.java b/src/database/NamedPreparedStatement.java
new file mode 100644
index 0000000..c98edac
--- /dev/null
+++ b/src/database/NamedPreparedStatement.java
@@ -0,0 +1,69 @@
+package database;
+
+import java.sql.Connection;
+import java.sql.PreparedStatement;
+import java.sql.SQLException;
+import java.util.ArrayList;
+import java.util.List;
+import java.util.regex.Matcher;
+import java.util.regex.Pattern;
+
+/**
+ * Allows a prepared statement to contain named parameters instead of a question
+ * mark position marker.
+ *
+ * @author Peter Wu
+ */
+public class NamedPreparedStatement {
+
+    private final List<String> fields;
+    private final PreparedStatement stmt;
+
+    public NamedPreparedStatement(Connection conn, String query) throws SQLException {
+        fields = new ArrayList<>();
+        Pattern pattern = Pattern.compile(":\\w+");
+        Matcher matcher = pattern.matcher(query);
+        while (matcher.find()) {
+            fields.add(matcher.group());
+        }
+        String sql = query.replaceAll(pattern.pattern(), "");
+        stmt = conn.prepareStatement(sql);
+    }
+
+    private List<Integer> getParamIndices(String fieldName) {
+        List<Integer> indices = new ArrayList<>();
+        int index = 0;
+        for (String name : fields) {
+            ++index;
+            if (name.equals(fieldName)) {
+                indices.add(index);
+            }
+        }
+        if (indices.isEmpty()) {
+            throw new RuntimeException("Missing " + fieldName + " in query!");
+        }
+        return indices;
+    }
+
+    public void setLong(String name, long l) throws SQLException {
+        for (int paramIndex : getParamIndices(name)) {
+            stmt.setLong(paramIndex, l);
+        }
+    }
+
+    public void setString(String name, String str) throws SQLException {
+        for (int paramIndex : getParamIndices(name)) {
+            stmt.setString(paramIndex, str);
+        }
+    }
+
+    public void setString(String name, int i) throws SQLException {
+        for (int paramIndex : getParamIndices(name)) {
+            stmt.setInt(paramIndex, i);
+        }
+    }
+
+    public PreparedStatement getStmt() {
+        return stmt;
+    }
+}