From 1afa0e255d694840c77c19c1effed6c0d0a92f1f Mon Sep 17 00:00:00 2001 From: Ugnich Anton Date: Fri, 21 Jun 2013 00:48:01 +0700 Subject: SQLHelpers --- src/com/juick/server/SQLHelpers.java | 57 ++++++++++++++++++++++++++++++++ src/com/juick/server/UserQueries.java | 62 +++++++---------------------------- 2 files changed, 69 insertions(+), 50 deletions(-) (limited to 'src') diff --git a/src/com/juick/server/SQLHelpers.java b/src/com/juick/server/SQLHelpers.java index 7879c244..dd3c4bc3 100644 --- a/src/com/juick/server/SQLHelpers.java +++ b/src/com/juick/server/SQLHelpers.java @@ -35,6 +35,63 @@ public class SQLHelpers { return ret; } + public static int getInt(Connection sql, String query, String param, int defvalue) { + int ret = defvalue; + PreparedStatement stmt = null; + ResultSet rs = null; + try { + stmt = sql.prepareStatement(query); + stmt.setString(1, param); + rs = stmt.executeQuery(); + if (rs.first()) { + ret = rs.getInt(1); + } + } catch (SQLException e) { + System.err.println(e); + } finally { + Utils.finishSQL(rs, stmt); + } + return ret; + } + + public static String getString(Connection sql, String query, int param) { + String ret = null; + PreparedStatement stmt = null; + ResultSet rs = null; + try { + stmt = sql.prepareStatement(query); + stmt.setInt(1, param); + rs = stmt.executeQuery(); + if (rs.first()) { + ret = rs.getString(1); + } + } catch (SQLException e) { + System.err.println(e); + } finally { + Utils.finishSQL(rs, stmt); + } + return ret; + } + + public static String getString(Connection sql, String query, String param) { + String ret = null; + PreparedStatement stmt = null; + ResultSet rs = null; + try { + stmt = sql.prepareStatement(query); + stmt.setString(1, param); + rs = stmt.executeQuery(); + if (rs.first()) { + ret = rs.getString(1); + } + } catch (SQLException e) { + System.err.println(e); + } finally { + Utils.finishSQL(rs, stmt); + } + return ret; + } + public static ArrayList getArrayInteger(Connection sql, String query, int param) { ArrayList ret = new ArrayList(); diff --git a/src/com/juick/server/UserQueries.java b/src/com/juick/server/UserQueries.java index d6f6045c..f0c5a57f 100644 --- a/src/com/juick/server/UserQueries.java +++ b/src/com/juick/server/UserQueries.java @@ -114,45 +114,16 @@ public class UserQueries { return users; } - public static String getJIDbyUID(Connection sql, int UID) { - String jid = null; - - PreparedStatement stmt = null; - ResultSet rs = null; - try { - stmt = sql.prepareStatement("SELECT jid FROM jids WHERE user_id=? AND active=1"); - stmt.setInt(1, UID); - rs = stmt.executeQuery(); - if (rs.first()) { - jid = rs.getString(1); - } - } catch (SQLException e) { - System.err.println(e); - } finally { - Utils.finishSQL(rs, stmt); - } + public static String getJIDbyUID(Connection sql, int uid) { + return SQLHelpers.getString(sql, "SELECT jid FROM jids WHERE user_id=? AND active=1", uid); + } - return jid; + public static int getUIDbyJID(Connection sql, String jid) { + return SQLHelpers.getInt(sql, "SELECT user_id FROM jids WHERE jid=?", jid, 0); } public static int getUIDbyHash(Connection sql, String hash) { - int UID = 0; - - PreparedStatement stmt = null; - ResultSet rs = null; - try { - stmt = sql.prepareStatement("SELECT user_id FROM logins WHERE hash=?"); - stmt.setString(1, hash); - rs = stmt.executeQuery(); - if (rs.first()) { - UID = rs.getInt(1); - } - } catch (SQLException e) { - System.err.println(e); - } finally { - Utils.finishSQL(rs, stmt); - } - return UID; + return SQLHelpers.getInt(sql, "SELECT user_id FROM logins WHERE hash=?", hash, 0); } public static com.juick.User getUserByHash(Connection sql, String hash) { @@ -178,24 +149,11 @@ public class UserQueries { } public static String getHashByUID(Connection sql, int uid) { - String hash = null; - PreparedStatement stmt = null; - ResultSet rs = null; - try { - stmt = sql.prepareStatement("SELECT hash FROM logins WHERE user_id=?"); - stmt.setInt(1, uid); - rs = stmt.executeQuery(); - if (rs.first()) { - hash = rs.getString(1); - } - } catch (SQLException e) { - System.err.println(e); - } finally { - Utils.finishSQL(rs, stmt); - } + String hash = SQLHelpers.getString(sql, "SELECT hash FROM logins WHERE user_id=?", uid); if (hash == null) { hash = generateHash(16); + PreparedStatement stmt = null; try { stmt = sql.prepareStatement("INSERT INTO logins(user_id,hash) VALUES (?,?)"); stmt.setInt(1, uid); @@ -220,6 +178,10 @@ public class UserQueries { return sb.toString(); } + public static boolean checkUserNameValid(String uname) { + return uname != null && uname.length() > 1 && uname.length() < 16 && uname.matches("[a-zA-Z0-9\\-\\.\\@]+"); + } + public static int checkPassword(Connection sql, String username, String password) { int uid = 0; PreparedStatement stmt = null; -- cgit v1.2.3