From 7248c94dfd3fecda1c854e09731592a03a79ca31 Mon Sep 17 00:00:00 2001 From: Vitaly Takmazov Date: Thu, 14 Jan 2016 17:01:28 +0300 Subject: spring-jdbc WIP --- src/main/java/com/juick/server/UserQueries.java | 274 +++++++----------------- 1 file changed, 81 insertions(+), 193 deletions(-) (limited to 'src/main/java/com/juick/server/UserQueries.java') 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 { + @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 getUsersByName(Connection sql, List unames) { - List 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 getUsersByName(JdbcTemplate sql, List 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 getUsersByID(Connection sql, List uids) { - List 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 getUsersByID(JdbcTemplate sql, List 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 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; } -- cgit v1.2.3