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/PMQueries.java | 204 ++++++++------------------ 1 file changed, 60 insertions(+), 144 deletions(-) (limited to 'src/main/java/com/juick/server/PMQueries.java') diff --git a/src/main/java/com/juick/server/PMQueries.java b/src/main/java/com/juick/server/PMQueries.java index c4b4fa2d..a2d4545c 100644 --- a/src/main/java/com/juick/server/PMQueries.java +++ b/src/main/java/com/juick/server/PMQueries.java @@ -27,6 +27,8 @@ import java.util.ArrayList; import java.util.List; import java.util.logging.Level; import java.util.logging.Logger; +import org.springframework.jdbc.core.JdbcTemplate; +import org.springframework.jdbc.core.RowMapper; /** * @@ -34,54 +36,24 @@ import java.util.logging.Logger; */ public class PMQueries { - private static final Logger logger = Logger.getLogger(PMQueries.class.getName()); + private static final Logger LOGGER = Logger.getLogger(PMQueries.class.getName()); - public static boolean createPM(Connection sql, int uid_from, int uid_to, String body) { - boolean ret = false; - PreparedStatement stmt = null; - try { - stmt = sql.prepareStatement("INSERT INTO pm(user_id,user_id_to,txt) VALUES (?,?,?)"); - stmt.setInt(1, uid_from); - stmt.setInt(2, uid_to); - stmt.setString(3, body); - ret = stmt.executeUpdate() > 0; - } catch (SQLException e) { - logger.log(Level.SEVERE, "sql exception", e); - } finally { - Utils.finishSQL(null, stmt); - } + public static boolean createPM(JdbcTemplate sql, int uid_from, int uid_to, String body) { + boolean ret = sql.update("INSERT INTO pm(user_id,user_id_to,txt) VALUES (?,?,?)", + new Object[] {uid_from, uid_to, body}) > 0; if (ret) { - PreparedStatement stmt2 = null; - try { - stmt2 = sql.prepareStatement("INSERT INTO pm_streams(user_id,user_id_to,lastmessage,unread) VALUES (?,?,NOW(),1) ON DUPLICATE KEY UPDATE lastmessage=NOW(),unread=unread+1"); - stmt2.setInt(1, uid_from); - stmt2.setInt(2, uid_to); - stmt2.executeUpdate(); - } catch (SQLException e) { - logger.log(Level.SEVERE, "sql exception", e); - } finally { - Utils.finishSQL(null, stmt2); - } + sql.update("INSERT INTO pm_streams(user_id,user_id_to,lastmessage,unread) " + + "VALUES (?,?,NOW(),1) " + + "ON DUPLICATE KEY UPDATE lastmessage=NOW(),unread=unread+1", + new Object[] {uid_from, uid_to}); } - return ret; } - public static boolean addPMinRoster(Connection sql, int uid, String jid) { - boolean ret = false; - PreparedStatement stmt = null; - try { - stmt = sql.prepareStatement("INSERT INTO pm_inroster(user_id,jid) VALUES (?,?)"); - stmt.setInt(1, uid); - stmt.setString(2, jid); - ret = stmt.executeUpdate() > 0; - } catch (SQLException e) { - logger.log(Level.SEVERE, "sql exception", e); - } finally { - Utils.finishSQL(null, stmt); - } - return ret; + public static boolean addPMinRoster(JdbcTemplate sql, int uid, String jid) { + return sql.update("INSERT INTO pm_inroster(user_id,jid) VALUES (?,?)", + new Object[] {uid, jid}) > 0; } public static boolean removePMinRoster(Connection sql, int uid, String jid) { @@ -93,32 +65,18 @@ public class PMQueries { stmt.setString(2, jid); ret = stmt.executeUpdate() > 0; } catch (SQLException e) { - logger.log(Level.SEVERE, "sql exception", e); + LOGGER.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(null, stmt); } return ret; } - public static boolean havePMinRoster(Connection sql, int uid, String jid) { - boolean ret = false; - - PreparedStatement stmt = null; - ResultSet rs = null; - try { - stmt = sql.prepareStatement("SELECT 1 FROM pm_inroster WHERE user_id=? AND jid=?"); - stmt.setInt(1, uid); - stmt.setString(2, jid); - rs = stmt.executeQuery(); - if (rs.first()) { - ret = true; - } - } catch (SQLException e) { - logger.log(Level.SEVERE, "sql exception", e); - } finally { - Utils.finishSQL(rs, stmt); - } - return ret; + public static boolean havePMinRoster(JdbcTemplate sql, int uid, String jid) { + List res = sql.queryForList("SELECT 1 FROM pm_inroster " + + "WHERE user_id=? AND jid=?", Integer.class, + new Object[] {uid, jid}); + return res.size() > 0; } public static String getLastView(Connection sql, int uid_from, int uid_to) { @@ -135,61 +93,42 @@ public class PMQueries { ret = rs.getString(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 getPMLastConversationsUsers(Connection sql, int uid, int cnt) { - List users = new ArrayList<>(); - - PreparedStatement stmt = null; - ResultSet rs = null; - try { - stmt = sql.prepareStatement("SELECT user_id,unread FROM pm_streams WHERE user_id_to=? AND unread>0 ORDER BY lastmessage DESC LIMIT " + cnt); - stmt.setInt(1, uid); - rs = stmt.executeQuery(); - rs.beforeFirst(); - while (rs.next()) { - com.juick.User u = new com.juick.User(); - u.UID = rs.getInt(1); - u.MessagesCount = rs.getInt(2); - users.add(u); - } - } catch (SQLException e) { - logger.log(Level.SEVERE, "sql exception", e); - } finally { - Utils.finishSQL(rs, stmt); - } + public static List getPMLastConversationsUsers(JdbcTemplate sql, int uid, int cnt) { + List users = sql.query("SELECT user_id,unread FROM pm_streams " + + "WHERE user_id_to=? AND unread>0 " + + "ORDER BY lastmessage DESC LIMIT " + cnt, (rs, rowNum) -> { + com.juick.User u = new com.juick.User(); + u.UID = rs.getInt(1); + u.MessagesCount = rs.getInt(2); + return u; + }, new Object[]{uid}); if (users.size() < cnt) { - try { - stmt = sql.prepareStatement("SELECT user_id,user_id_to FROM pm_streams WHERE (user_id=? OR user_id_to=?) AND lastmessage>TIMESTAMPADD(MONTH,-1,NOW()) ORDER BY lastmessage DESC LIMIT ?"); - stmt.setInt(1, uid); - stmt.setInt(2, uid); - stmt.setInt(3, cnt * 2); - rs = stmt.executeQuery(); - rs.beforeFirst(); - while (rs.next()) { - int uuid = rs.getInt(1) + rs.getInt(2) - uid; - if (!haveUserInArray(users, uuid)) { - com.juick.User u = new com.juick.User(); + List addUsers = sql.query("SELECT user_id,user_id_to " + + "FROM pm_streams WHERE (user_id=? OR user_id_to=?) " + + "AND lastmessage>TIMESTAMPADD(MONTH,-1,NOW()) " + + "ORDER BY lastmessage DESC LIMIT ?", (rs, num) -> { + User u = new com.juick.User(); + int uuid = rs.getInt(1) + rs.getInt(2) - uid; u.UID = uuid; - users.add(u); - if (users.size() >= cnt) { - break; - } + return u; + }, new Object[]{uid, uid, cnt * 2}); + for (User addUser : addUsers) { + if (!haveUserInArray(users, addUser.UID)) { + users.add(addUser); + if (users.size() >= cnt) { + break; } } - } catch (SQLException e) { - logger.log(Level.SEVERE, "sql exception", e); - } finally { - Utils.finishSQL(rs, stmt); } } - if (!users.isEmpty()) { UserQueries.fillUsersByID(sql, users); } @@ -206,45 +145,22 @@ public class PMQueries { return false; } - public static List getPMMessages(Connection sql, int uid, int uid_to) { - List msgs = new ArrayList<>(20); - - PreparedStatement stmt = null; - ResultSet rs = null; - try { - stmt = sql.prepareStatement("SELECT user_id,txt,ts FROM pm WHERE (user_id=? AND user_id_to=?) OR (user_id_to=? AND user_id=?) ORDER BY ts DESC LIMIT 20"); - stmt.setInt(1, uid); - stmt.setInt(2, uid_to); - stmt.setInt(3, uid); - stmt.setInt(4, uid_to); - rs = stmt.executeQuery(); - rs.beforeFirst(); - while (rs.next()) { - com.juick.Message msg = new com.juick.Message(); - int uuid = rs.getInt(1); - msg.User = new com.juick.User(); - msg.User.UID = uuid; - msg.Text = rs.getString(2); - msg.TimestampString = rs.getString(3); - msgs.add(0, msg); - } - } catch (SQLException e) { - logger.log(Level.SEVERE, "sql exception", e); - } finally { - Utils.finishSQL(rs, stmt); - } - - PreparedStatement stmt2 = null; - try { - stmt2 = sql.prepareStatement("UPDATE pm_streams SET lastview=NOW(),unread=0 WHERE user_id_to=? AND user_id=?"); - stmt2.setInt(1, uid); - stmt2.setInt(2, uid_to); - stmt2.executeUpdate(); - } catch (SQLException e) { - logger.log(Level.SEVERE, "sql exception", e); - } finally { - Utils.finishSQL(null, stmt2); - } + public static List getPMMessages(JdbcTemplate sql, int uid, int uid_to) { + List msgs = sql.query("SELECT user_id,txt,ts FROM pm " + + "WHERE (user_id=? AND user_id_to=?) " + + "OR (user_id_to=? AND user_id=?) ORDER BY ts DESC LIMIT 20", + (rs, rowNum) -> { + com.juick.Message msg = new com.juick.Message(); + int uuid = rs.getInt(1); + msg.User = new com.juick.User(); + msg.User.UID = uuid; + msg.Text = rs.getString(2); + msg.TimestampString = rs.getString(3); + return msg; + }, new Object[]{uid, uid_to, uid, uid_to}); + + sql.update("UPDATE pm_streams SET lastview=NOW(),unread=0 " + + "WHERE user_id_to=? AND user_id=?", new Object[]{uid, uid_to}); return msgs; } @@ -270,7 +186,7 @@ public class PMQueries { msgs.add(0, msg); } } catch (SQLException e) { - logger.log(Level.SEVERE, "sql exception", e); + LOGGER.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } @@ -299,7 +215,7 @@ public class PMQueries { msgs.add(0, msg); } } catch (SQLException e) { - logger.log(Level.SEVERE, "sql exception", e); + LOGGER.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } -- cgit v1.2.3