From 9bd8a41c0db5a1027a184facfca8d0152945c078 Mon Sep 17 00:00:00 2001 From: Vitaly Takmazov Date: Fri, 15 Jan 2016 12:50:52 +0300 Subject: spring-jdbc WIP --- src/main/java/com/juick/server/UserQueries.java | 129 ++++++++---------------- 1 file changed, 41 insertions(+), 88 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 bf56e974..27ae2870 100644 --- a/src/main/java/com/juick/server/UserQueries.java +++ b/src/main/java/com/juick/server/UserQueries.java @@ -19,7 +19,6 @@ 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; @@ -29,10 +28,7 @@ import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; -import java.util.ArrayList; -import java.util.List; -import java.util.Random; -import java.util.UUID; +import java.util.*; import java.util.logging.Level; import java.util.logging.Logger; @@ -44,7 +40,7 @@ public class UserQueries { static final String ABCDEF = "0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ"; - private static Logger logger = Logger.getLogger(UserQueries.class.getName()); + private static final Logger LOGGER = Logger.getLogger(UserQueries.class.getName()); static class UserMapper implements RowMapper { @Override @@ -115,7 +111,7 @@ public class UserQueries { uids.toArray()); } - public static boolean fillUsersByID(Connection sql, List users) { + public static boolean fillUsersByID(JdbcTemplate sql, List users) { boolean ret = false; String uids = ""; @@ -127,54 +123,27 @@ public class UserQueries { uids += users.get(i).UID; } - PreparedStatement stmt = null; - ResultSet rs = null; - try { - stmt = sql.prepareStatement("SELECT id,nick FROM users WHERE id IN (" + uids + ")"); - rs = stmt.executeQuery(); - rs.beforeFirst(); - while (rs.next()) { - int uid = rs.getInt(1); - for (int i = 0; i < usersSize; i++) { - if (users.get(i).UID == uid) { - users.get(i).UName = rs.getString(2); - ret = true; - } - } - } - } catch (SQLException e) { - logger.log(Level.SEVERE, "sql exception", e); - } finally { - Utils.finishSQL(rs, stmt); - } + sql.query("SELECT id,nick,banned FROM users WHERE id IN (" + uids + ")", + (rs, num) -> { + User u = users.get(num); + u.UName = rs.getString(2); + return u; + }); - return ret; + return true; } - public static List getUsersByJID(Connection sql, List jids) { - List users = new ArrayList<>(); - - 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) + ")"); - 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); - user.JID = rs.getString(3); - users.add(user); - } - } catch (SQLException e) { - logger.log(Level.SEVERE, "sql exception", e); - } finally { - Utils.finishSQL(rs, stmt); - } - return users; + public static List getUsersByJID(JdbcTemplate sql, List jids) { + return sql.query("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, rowNum) -> { + com.juick.User user = new com.juick.User(); + user.UID = rs.getInt(1); + user.UName = rs.getString(2); + user.JID = rs.getString(3); + return user; + }); } public static String getJIDbyUID(JdbcTemplate sql, int uid) { @@ -246,7 +215,7 @@ public class UserQueries { } } } catch (SQLException e) { - logger.log(Level.SEVERE, "sql exception", e); + LOGGER.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } @@ -266,7 +235,7 @@ public class UserQueries { ret = rs.getInt(1); } } catch (SQLException e) { - logger.log(Level.SEVERE, "sql exception", e); + LOGGER.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } @@ -281,7 +250,7 @@ public class UserQueries { stmt.setInt(2, uid); stmt.executeUpdate(); } catch (SQLException e) { - logger.log(Level.SEVERE, "sql exception", e); + LOGGER.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(null, stmt); } @@ -300,7 +269,7 @@ public class UserQueries { ret = rs.getInt(1) < 3600; } } catch (SQLException e) { - logger.log(Level.SEVERE, "sql exception", e); + LOGGER.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } @@ -321,7 +290,7 @@ public class UserQueries { ret = rs.getInt(1) == 1; } } catch (SQLException e) { - logger.log(Level.SEVERE, "sql exception", e); + LOGGER.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } @@ -342,34 +311,18 @@ public class UserQueries { ret = rs.getInt(1) == 1; } } catch (SQLException e) { - logger.log(Level.SEVERE, "sql exception", e); + LOGGER.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } return ret; } - public static boolean isInBLAny(Connection sql, int uid, int uid2) { - boolean ret = false; - - PreparedStatement stmt = null; - ResultSet rs = null; - try { - stmt = sql.prepareStatement("SELECT 1 FROM bl_users WHERE (user_id=? AND bl_user_id=?) OR (user_id=? AND bl_user_id=?)"); - stmt.setInt(1, uid); - stmt.setInt(2, uid2); - stmt.setInt(3, uid2); - stmt.setInt(4, uid); - rs = stmt.executeQuery(); - if (rs.first()) { - ret = rs.getInt(1) == 1; - } - } catch (SQLException e) { - logger.log(Level.SEVERE, "sql exception", e); - } finally { - Utils.finishSQL(rs, stmt); - } - return ret; + public static boolean isInBLAny(JdbcTemplate sql, int uid, int uid2) { + List res = sql.queryForList("SELECT 1 FROM bl_users " + + "WHERE (user_id=? AND bl_user_id=?) " + + "OR (user_id=? AND bl_user_id=?)", Integer.class, new Object[] {uid, uid2, uid2, uid}); + return res.get(0) == 1; } public static List checkBL(Connection sql, int visitor, List uids) { @@ -386,7 +339,7 @@ public class UserQueries { ret.add(rs.getInt(1)); } } catch (SQLException e) { - logger.log(Level.SEVERE, "sql exception", e); + LOGGER.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } @@ -408,15 +361,15 @@ public class UserQueries { ret = rs.getInt(1) == 1; } } catch (SQLException e) { - logger.log(Level.SEVERE, "sql exception", e); + LOGGER.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } return ret; } - public static List getUserRead(Connection sql, int uid) { - return SQLHelpers.getArrayInteger(sql, "SELECT user_id FROM subscr_users WHERE suser_id=?", uid); + public static List getUserRead(JdbcTemplate sql, int uid) { + return sql.queryForList("SELECT user_id FROM subscr_users WHERE suser_id=?", Integer.class, uid); } public static List getUserReadLeastPopular(Connection sql, int uid, int cnt) { @@ -437,7 +390,7 @@ public class UserQueries { users.add(u); } } catch (SQLException e) { - logger.log(Level.SEVERE, "sql exception", e); + LOGGER.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } @@ -445,8 +398,8 @@ public class UserQueries { return users; } - public static List getUserReaders(Connection sql, int uid) { - return SQLHelpers.getArrayInteger(sql, "SELECT suser_id FROM subscr_users WHERE user_id=?", uid); + public static List getUserReaders(JdbcTemplate sql, int uid) { + return sql.queryForList("SELECT suser_id FROM subscr_users WHERE user_id=?", Integer.class, uid); } public static List getUserBLUsers(Connection sql, int uid) { @@ -466,7 +419,7 @@ public class UserQueries { users.add(u); } } catch (SQLException e) { - logger.log(Level.SEVERE, "sql exception", e); + LOGGER.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } @@ -496,7 +449,7 @@ public class UserQueries { return stmt2.execute(); } } catch (SQLException e) { - logger.log(Level.SEVERE, "sql exception", e); + LOGGER.log(Level.SEVERE, "sql exception", e); } return false; } -- cgit v1.2.3