spring-jdbc WIP

2 files changed, 89 insertions, 193 deletions

diff --git a/build.gradle b/build.gradle
index 405a81af..b82c3d5c 100644
--- a/build.gradle
+++ b/build.gradle
@@ -1 +1,9 @@
 apply plugin: "java"
+
+repositories {
+    mavenCentral()
+}
+
+dependencies {
+    compile "org.springframework:spring-jdbc:4.2.4.RELEASE"
+}
\ No newline at end of file
diff --git a/src/main/java/com/juick/server/UserQueries.java b/src/main/java/com/juick/server/UserQueries.java
index 98573bfb..bf56e974 100644
--- a/src/main/java/com/juick/server/UserQueries.java
+++ b/src/main/java/com/juick/server/UserQueries.java
@@ -18,6 +18,11 @@
 package com.juick.server;
 
 import com.juick.User;
+import org.springframework.jdbc.core.JdbcTemplate;
+import org.springframework.jdbc.core.PreparedStatementCreator;
+import org.springframework.jdbc.core.RowMapper;
+import org.springframework.jdbc.support.GeneratedKeyHolder;
+import org.springframework.jdbc.support.KeyHolder;
 
 import java.sql.Connection;
 import java.sql.PreparedStatement;
@@ -41,167 +46,73 @@ public class UserQueries {
 
     private static Logger logger = Logger.getLogger(UserQueries.class.getName());
 
-    public static String getSignUpHashByJID(Connection sql, String jid) {
-        String hash = SQLHelpers.getString(sql, "SELECT loginhash FROM jids WHERE jid=? AND user_id IS NULL", jid);
+    static class UserMapper implements RowMapper<User> {
+        @Override
+        public User mapRow(ResultSet rs, int rowNum) throws SQLException {
+            User user = new User();
+            user.UID = rs.getInt("id");
+            user.UName = rs.getString("nick");
+            user.Banned = rs.getBoolean("banned");
+            return user;
+        }
+    }
 
+    public static String getSignUpHashByJID(JdbcTemplate sql, String jid) {
+        String hash = sql.queryForObject("SELECT loginhash FROM jids WHERE jid=? AND user_id IS NULL",
+                String.class, jid);
         if (hash == null) {
             hash = UUID.randomUUID().toString();
-            PreparedStatement stmt = null;
-            try {
-                stmt = sql.prepareStatement("INSERT INTO jids(jid,loginhash) VALUES (?,?)");
-                stmt.setString(1, jid);
-                stmt.setString(2, hash);
-                stmt.executeUpdate();
-            } catch (SQLException e) {
-                logger.log(Level.SEVERE, "sql exception", e);
-            } finally {
-                Utils.finishSQL(null, stmt);
-            }
+            sql.update("INSERT INTO jids(jid,loginhash) VALUES (?,?)", jid, hash);
         }
-
         return hash;
     }
 
-    public static int createUser(Connection sql, String username, String password) {
-        int uid = 0;
+    public static int createUser(JdbcTemplate sql, String username, String password) {
+        KeyHolder holder = new GeneratedKeyHolder();
+        sql.update(con -> {
+            PreparedStatement stmt = con.prepareStatement("INSERT INTO users(nick,passw) VALUES (?,?)",
+                    Statement.RETURN_GENERATED_KEYS);
+                stmt.setString(1, username);
+                stmt.setString(2, password);
+                return stmt;
+        }, holder);
 
-        PreparedStatement stmt = null;
-        ResultSet rs = null;
-        try {
-            stmt = sql.prepareStatement("INSERT INTO users(nick,passw) VALUES (?,?)", Statement.RETURN_GENERATED_KEYS);
-            stmt.setString(1, username);
-            stmt.setString(2, password);
-            stmt.executeUpdate();
-            rs = stmt.getGeneratedKeys();
-            if (rs.first()) {
-                uid = rs.getInt(1);
-            }
-        } catch (SQLException e) {
-            logger.log(Level.SEVERE, "sql exception", e);
-        } finally {
-            Utils.finishSQL(rs, stmt);
-        }
+        int uid = holder.getKey().intValue();
 
-        SQLHelpers.executeInt(sql, "INSERT INTO useroptions(user_id) VALUES (?)", uid);
-        SQLHelpers.executeInt(sql, "INSERT INTO subscr_users(user_id,suser_id) VALUES (2,?)", uid);
+        sql.update("INSERT INTO useroptions(user_id) VALUES (?)", uid);
+        sql.update("INSERT INTO subscr_users(user_id,suser_id) VALUES (2,?)", uid);
 
         return uid;
     }
 
-    public static com.juick.User getUserByUID(Connection sql, int uid) {
-        com.juick.User user = null;
-
-        PreparedStatement stmt = null;
-        ResultSet rs = null;
-        try {
-            stmt = sql.prepareStatement("SELECT nick,banned FROM users WHERE id=?");
-            stmt.setInt(1, uid);
-            rs = stmt.executeQuery();
-            if (rs.first()) {
-                user = new com.juick.User();
-                user.UID = uid;
-                user.UName = rs.getString(1);
-                user.Banned = rs.getBoolean(2);
-            }
-        } catch (SQLException e) {
-            logger.log(Level.SEVERE, "sql exception", e);
-        } finally {
-            Utils.finishSQL(rs, stmt);
-        }
-        return user;
+    public static User getUserByUID(JdbcTemplate sql, int uid) {
+        return sql.queryForObject("SELECT id, nick,banned FROM users WHERE id=?",
+                new Object[] {uid},
+                new UserMapper());
     }
 
-    public static com.juick.User getUserByName(Connection sql, String username) {
-        com.juick.User user = null;
-
-        PreparedStatement stmt = null;
-        ResultSet rs = null;
-        try {
-            stmt = sql.prepareStatement("SELECT id,nick,banned FROM users WHERE nick=?");
-            stmt.setString(1, username);
-            rs = stmt.executeQuery();
-            if (rs.first()) {
-                user = new com.juick.User();
-                user.UID = rs.getInt(1);
-                user.UName = rs.getString(2);
-                user.Banned = rs.getBoolean(3);
-            }
-        } catch (SQLException e) {
-            logger.log(Level.SEVERE, "sql exception", e);
-        } finally {
-            Utils.finishSQL(rs, stmt);
-        }
-        return user;
+    public static User getUserByName(JdbcTemplate sql, String username) {
+        return sql.queryForObject("SELECT id,nick,banned FROM users WHERE nick=?",
+                new Object[] {username},
+                new UserMapper());
     }
 
-    public static com.juick.User getUserByJID(Connection sql, String jid) {
-        com.juick.User user = null;
-
-        PreparedStatement stmt = null;
-        ResultSet rs = null;
-        try {
-            stmt = sql.prepareStatement("SELECT id,nick FROM users WHERE id=(SELECT user_id FROM jids WHERE jid=?)");
-            stmt.setString(1, jid);
-            rs = stmt.executeQuery();
-            if (rs.first()) {
-                user = new com.juick.User();
-                user.UID = rs.getInt(1);
-                user.UName = rs.getString(2);
-                user.JID = jid;
-            }
-        } catch (SQLException e) {
-            logger.log(Level.SEVERE, "sql exception", e);
-        } finally {
-            Utils.finishSQL(rs, stmt);
-        }
-        return user;
+    public static User getUserByJID(JdbcTemplate sql, String jid) {
+        return sql.queryForObject("SELECT id,nick,banned FROM users WHERE id=(SELECT user_id FROM jids WHERE jid=?)",
+                new Object[] {jid},
+                new UserMapper());
     }
 
-    public static List<com.juick.User> getUsersByName(Connection sql, List<String> unames) {
-        List<com.juick.User> users = new ArrayList<>();
-
-        PreparedStatement stmt = null;
-        ResultSet rs = null;
-        try {
-            stmt = sql.prepareStatement("SELECT id,nick FROM users WHERE nick IN (" + Utils.convertArrayString2String(unames) + ")");
-            rs = stmt.executeQuery();
-            rs.beforeFirst();
-            while (rs.next()) {
-                com.juick.User user = new com.juick.User();
-                user.UID = rs.getInt(1);
-                user.UName = rs.getString(2);
-                users.add(user);
-            }
-        } catch (SQLException e) {
-            logger.log(Level.SEVERE, "sql exception", e);
-        } finally {
-            Utils.finishSQL(rs, stmt);
-        }
-        return users;
+    public static List<User> getUsersByName(JdbcTemplate sql, List<String> unames) {
+        return sql.query("SELECT id,nick,banned FROM users WHERE id=(SELECT user_id FROM jids WHERE jid=?)",
+                new UserMapper(),
+                unames.toArray());
     }
 
-    public static List<com.juick.User> getUsersByID(Connection sql, List<Integer> uids) {
-        List<com.juick.User> users = new ArrayList<>();
-
-        PreparedStatement stmt = null;
-        ResultSet rs = null;
-        try {
-            stmt = sql.prepareStatement("SELECT id,nick FROM users WHERE id IN (" + Utils.convertArrayInt2String(uids) + ")");
-            rs = stmt.executeQuery();
-            rs.beforeFirst();
-            while (rs.next()) {
-                com.juick.User u = new com.juick.User();
-                u.UID = rs.getInt(1);
-                u.UName = rs.getString(2);
-                users.add(u);
-            }
-        } catch (SQLException e) {
-            logger.log(Level.SEVERE, "sql exception", e);
-        } finally {
-            Utils.finishSQL(rs, stmt);
-        }
-
-        return users;
+    public static List<User> getUsersByID(JdbcTemplate sql, List<Integer> uids) {
+        return sql.query("SELECT id,nick,banned FROM users WHERE id IN (" + Utils.convertArrayInt2String(uids) + ")",
+                new UserMapper(),
+                uids.toArray());
     }
 
     public static boolean fillUsersByID(Connection sql, List<com.juick.User> users) {
@@ -246,7 +157,9 @@ public class UserQueries {
         PreparedStatement stmt = null;
         ResultSet rs = null;
         try {
-            stmt = sql.prepareStatement("SELECT users.id,users.nick,jids.jid FROM users INNER JOIN jids ON jids.user_id=users.id WHERE jids.jid IN (" + Utils.convertArrayString2String(jids) + ")");
+            stmt = sql.prepareStatement("SELECT users.id,users.nick,jids.jid FROM users " +
+                    "INNER JOIN jids ON jids.user_id=users.id " +
+                    "WHERE jids.jid IN (" + Utils.convertArrayString2String(jids) + ")");
             rs = stmt.executeQuery();
             rs.beforeFirst();
             while (rs.next()) {
@@ -264,64 +177,43 @@ public class UserQueries {
         return users;
     }
 
-    public static String getJIDbyUID(Connection sql, int uid) {
-        return SQLHelpers.getString(sql, "SELECT jid FROM jids WHERE user_id=? AND active=1", uid);
+    public static String getJIDbyUID(JdbcTemplate sql, int uid) {
+        return sql.queryForObject("SELECT jid FROM jids WHERE user_id=? AND active=1", String.class, uid);
     }
 
-    public static int getUIDbyJID(Connection sql, String jid) {
-        return SQLHelpers.getInt(sql, "SELECT user_id FROM jids WHERE jid=?", jid, 0);
+    public static int getUIDbyJID(JdbcTemplate sql, String jid) {
+        return sql.queryForObject("SELECT user_id FROM jids WHERE jid=?", Integer.class, jid);
     }
 
-    public static int getUIDbyName(Connection sql, String uname) {
-        return SQLHelpers.getInt(sql, "SELECT id FROM users WHERE nick=?", uname, 0);
+    public static int getUIDbyName(JdbcTemplate sql, String uname) {
+        return sql.queryForObject("SELECT id FROM users WHERE nick=?", Integer.class, uname);
     }
 
-    public static int getUIDbyHash(Connection sql, String hash) {
-        return SQLHelpers.getInt(sql, "SELECT user_id FROM logins WHERE hash=?", hash, 0);
+    public static int getUIDbyHash(JdbcTemplate sql, String hash) {
+        return sql.queryForObject("SELECT user_id FROM logins WHERE hash=?", Integer.class, hash);
     }
 
-    public static com.juick.User getUserByHash(Connection sql, String hash) {
-        com.juick.User user = null;
-
-        PreparedStatement stmt = null;
-        ResultSet rs = null;
-        try {
-            stmt = sql.prepareStatement("SELECT logins.user_id,users.nick, users.banned FROM logins INNER JOIN users ON logins.user_id=users.id WHERE logins.hash=?");
-            stmt.setString(1, hash);
-            rs = stmt.executeQuery();
-            if (rs.first()) {
-                user = new com.juick.User();
-                user.UID = rs.getInt(1);
-                user.UName = rs.getString(2);
-                user.Banned = rs.getBoolean(3);
-                user.AuthHash = hash;
-            }
-        } catch (SQLException e) {
-            logger.log(Level.SEVERE, "sql exception", e);
-        } finally {
-            Utils.finishSQL(rs, stmt);
-        }
+    public static com.juick.User getUserByHash(JdbcTemplate sql, String hash) {
+        User user = sql.queryForObject("SELECT logins.user_id,users.nick, users.banned FROM logins " +
+                "INNER JOIN users ON logins.user_id=users.id WHERE logins.hash=?",
+                new UserMapper(), hash);
+        user.AuthHash = hash;
         return user;
     }
 
-    public static String getHashByUID(Connection sql, int uid) {
-        String hash = SQLHelpers.getString(sql, "SELECT hash FROM logins WHERE user_id=?", uid);
+    public static String getHashByUID(JdbcTemplate sql, int uid) {
+        String hash = sql.queryForObject("SELECT hash FROM logins WHERE user_id=?", String.class, uid);
 
         if (hash == null) {
             hash = generateHash(16);
-            PreparedStatement stmt = null;
-            try {
-                stmt = sql.prepareStatement("INSERT INTO logins(user_id,hash) VALUES (?,?)");
+            final String finalHash = hash;
+            sql.update(con -> {
+                PreparedStatement stmt = con.prepareStatement("INSERT INTO logins(user_id,hash) VALUES (?,?)");
                 stmt.setInt(1, uid);
-                stmt.setString(2, hash);
-                stmt.executeUpdate();
-            } catch (SQLException e) {
-                logger.log(Level.SEVERE, "sql exception", e);
-            } finally {
-                Utils.finishSQL(null, stmt);
-            }
+                stmt.setString(2, finalHash);
+                return stmt;
+            });
         }
-
         return hash;
     }
 
@@ -630,23 +522,19 @@ public class UserQueries {
         Active
     }
 
-    public static boolean setActiveStatusForJID(Connection sql, String JID, ActiveStatus jidStatus) {
+    public static boolean setActiveStatusForJID(JdbcTemplate sql, String JID, ActiveStatus jidStatus) {
         User user = getUserByJID(sql, JID);
         if (user != null) {
-            PreparedStatement preparedStatement = null;
-            try {
-                preparedStatement = sql.prepareStatement(
+            return sql.update(con -> {
+                PreparedStatement preparedStatement = con.prepareStatement(
                         "UPDATE jids SET active=? WHERE user_id=? AND jid=?");
                 int newStatus = jidStatus == ActiveStatus.Active ? 1 : 0;
                 preparedStatement.setInt(1, newStatus);
                 preparedStatement.setInt(2, user.UID);
                 preparedStatement.setString(3, JID);
-                return preparedStatement.executeUpdate() >= 0;
-            } catch (SQLException e) {
-                logger.log(Level.SEVERE, "sql exception", e);
-            } finally {
-                Utils.finishSQL(null, preparedStatement);
-            }
+                return preparedStatement;
+
+            }) >= 0;
         }
         return false;
     }