From 439d42ef6d60f0a535fdfa2457da8417b70d966a Mon Sep 17 00:00:00 2001 From: Vitaly Takmazov Date: Sun, 10 Jan 2016 16:19:30 +0300 Subject: ShowQueries --- src/main/java/com/juick/server/ShowQueries.java | 72 +++++++++++++++++++++++++ 1 file changed, 72 insertions(+) create mode 100644 src/main/java/com/juick/server/ShowQueries.java (limited to 'src/main/java/com/juick/server/ShowQueries.java') diff --git a/src/main/java/com/juick/server/ShowQueries.java b/src/main/java/com/juick/server/ShowQueries.java new file mode 100644 index 00000000..06aafb2d --- /dev/null +++ b/src/main/java/com/juick/server/ShowQueries.java @@ -0,0 +1,72 @@ +package com.juick.server; + +import com.juick.User; + +import java.sql.Connection; +import java.sql.PreparedStatement; +import java.sql.ResultSet; +import java.sql.SQLException; +import java.util.ArrayList; +import java.util.List; +import java.util.logging.Level; +import java.util.logging.Logger; + +/** + * Created by vt on 10/01/16. + */ +public class ShowQueries { + + private static final Logger logger = Logger.getLogger(ShowQueries.class.getName()); + + public static List getRecommendedUsers(Connection sql, User forUser) { + List result = new ArrayList<>(); + PreparedStatement preparedStatement = null; + ResultSet rs = null; + try { + preparedStatement = sql.prepareStatement("SELECT users.nick FROM subscr_users INNER JOIN users " + + "ON subscr_users.user_id=users.id " + + "WHERE subscr_users.user_id NOT IN (SELECT user_id FROM subscr_users WHERE suser_id=?) " + + "AND subscr_users.suser_id IN (SELECT user_id FROM subscr_users WHERE suser_id=?) " + + "AND subscr_users.user_id NOT IN (SELECT bl_user_id FROM bl_users WHERE user_id=?) " + + "AND subscr_users.user_id!=? AND users.lastmessage>UNIX_TIMESTAMP()-259200 " + + "GROUP BY subscr_users.user_id ORDER BY count(*) DESC LIMIT 10"); + preparedStatement.setInt(1, forUser.UID); + preparedStatement.setInt(2, forUser.UID); + preparedStatement.setInt(3, forUser.UID); + preparedStatement.setInt(4, forUser.UID); + rs = preparedStatement.executeQuery(); + rs.beforeFirst(); + while (rs.next()) { + result.add(rs.getString(1)); + } + } catch (SQLException e) { + logger.log(Level.SEVERE, "sql error", e); + } finally { + Utils.finishSQL(rs, preparedStatement); + } + return result; + } + + public static List getTopUsers(Connection sql) { + List result = new ArrayList<>(); + PreparedStatement preparedStatement = null; + ResultSet rs = null; + try { + preparedStatement = sql.prepareStatement("SELECT users.nick,COUNT(subscr_users.suser_id) AS cnt " + + "FROM (subscr_users INNER JOIN users ON subscr_users.user_id=users.id) " + + "INNER JOIN useroptions ON users.id=useroptions.user_id " + + "WHERE useroptions.privacy_view>0 AND users.lastmessage>UNIX_TIMESTAMP()-259200 " + + "AND users.id!=2 GROUP BY subscr_users.user_id ORDER BY cnt DESC LIMIT 10"); + rs = preparedStatement.executeQuery(); + rs.beforeFirst(); + while (rs.next()) { + result.add(rs.getString(1)); + } + } catch (SQLException e) { + logger.log(Level.SEVERE, "sql error", e); + } finally { + Utils.finishSQL(rs, preparedStatement); + } + return result; + } +} -- cgit v1.2.3 From f14eea2cfc80dac5e9b304a57457e7f82842a8ed Mon Sep 17 00:00:00 2001 From: Vitaly Takmazov Date: Fri, 15 Jan 2016 15:49:29 +0300 Subject: spring-jdbc WIP --- src/main/java/com/juick/server/ShowQueries.java | 58 ++++--------------------- 1 file changed, 9 insertions(+), 49 deletions(-) (limited to 'src/main/java/com/juick/server/ShowQueries.java') diff --git a/src/main/java/com/juick/server/ShowQueries.java b/src/main/java/com/juick/server/ShowQueries.java index 06aafb2d..05a58e9a 100644 --- a/src/main/java/com/juick/server/ShowQueries.java +++ b/src/main/java/com/juick/server/ShowQueries.java @@ -2,71 +2,31 @@ package com.juick.server; import com.juick.User; -import java.sql.Connection; -import java.sql.PreparedStatement; -import java.sql.ResultSet; -import java.sql.SQLException; -import java.util.ArrayList; import java.util.List; -import java.util.logging.Level; -import java.util.logging.Logger; +import org.springframework.jdbc.core.JdbcTemplate; /** * Created by vt on 10/01/16. */ public class ShowQueries { - private static final Logger logger = Logger.getLogger(ShowQueries.class.getName()); - - public static List getRecommendedUsers(Connection sql, User forUser) { - List result = new ArrayList<>(); - PreparedStatement preparedStatement = null; - ResultSet rs = null; - try { - preparedStatement = sql.prepareStatement("SELECT users.nick FROM subscr_users INNER JOIN users " + + public static List getRecommendedUsers(JdbcTemplate sql, User forUser) { + return sql.queryForList("SELECT users.nick FROM subscr_users INNER JOIN users " + "ON subscr_users.user_id=users.id " + "WHERE subscr_users.user_id NOT IN (SELECT user_id FROM subscr_users WHERE suser_id=?) " + "AND subscr_users.suser_id IN (SELECT user_id FROM subscr_users WHERE suser_id=?) " + "AND subscr_users.user_id NOT IN (SELECT bl_user_id FROM bl_users WHERE user_id=?) " + "AND subscr_users.user_id!=? AND users.lastmessage>UNIX_TIMESTAMP()-259200 " + - "GROUP BY subscr_users.user_id ORDER BY count(*) DESC LIMIT 10"); - preparedStatement.setInt(1, forUser.UID); - preparedStatement.setInt(2, forUser.UID); - preparedStatement.setInt(3, forUser.UID); - preparedStatement.setInt(4, forUser.UID); - rs = preparedStatement.executeQuery(); - rs.beforeFirst(); - while (rs.next()) { - result.add(rs.getString(1)); - } - } catch (SQLException e) { - logger.log(Level.SEVERE, "sql error", e); - } finally { - Utils.finishSQL(rs, preparedStatement); - } - return result; + "GROUP BY subscr_users.user_id ORDER BY count(*) DESC LIMIT 10", + String.class, new Object[] {forUser.UID, forUser.UID, forUser.UID, forUser.UID}); } - public static List getTopUsers(Connection sql) { - List result = new ArrayList<>(); - PreparedStatement preparedStatement = null; - ResultSet rs = null; - try { - preparedStatement = sql.prepareStatement("SELECT users.nick,COUNT(subscr_users.suser_id) AS cnt " + + public static List getTopUsers(JdbcTemplate sql) { + return sql.queryForList("SELECT users.nick,COUNT(subscr_users.suser_id) AS cnt " + "FROM (subscr_users INNER JOIN users ON subscr_users.user_id=users.id) " + "INNER JOIN useroptions ON users.id=useroptions.user_id " + "WHERE useroptions.privacy_view>0 AND users.lastmessage>UNIX_TIMESTAMP()-259200 " + - "AND users.id!=2 GROUP BY subscr_users.user_id ORDER BY cnt DESC LIMIT 10"); - rs = preparedStatement.executeQuery(); - rs.beforeFirst(); - while (rs.next()) { - result.add(rs.getString(1)); - } - } catch (SQLException e) { - logger.log(Level.SEVERE, "sql error", e); - } finally { - Utils.finishSQL(rs, preparedStatement); - } - return result; + "AND users.id!=2 GROUP BY subscr_users.user_id ORDER BY cnt DESC LIMIT 10", + String.class); } } -- cgit v1.2.3 From 09fd6b8ee40802eceff9746d3c23aa8446ccbd64 Mon Sep 17 00:00:00 2001 From: Vitaly Takmazov Date: Fri, 15 Jan 2016 22:56:33 +0300 Subject: update core --- .../java/com/juick/server/MessagesQueries.java | 50 +++++++++++----------- src/main/java/com/juick/server/PMQueries.java | 30 ++++++------- src/main/java/com/juick/server/ShowQueries.java | 2 +- src/main/java/com/juick/server/UserQueries.java | 30 ++++++------- 4 files changed, 56 insertions(+), 56 deletions(-) (limited to 'src/main/java/com/juick/server/ShowQueries.java') diff --git a/src/main/java/com/juick/server/MessagesQueries.java b/src/main/java/com/juick/server/MessagesQueries.java index 4086cb13..9cbf4240 100644 --- a/src/main/java/com/juick/server/MessagesQueries.java +++ b/src/main/java/com/juick/server/MessagesQueries.java @@ -244,10 +244,10 @@ public class MessagesQueries { + "WHERE messages.message_id=?", new Object[]{mid}, (rs, rowNum) -> { Message msg = new Message(); - msg.MID = mid; - msg.User = new com.juick.User(); - msg.User.UID = rs.getInt(1); - msg.User.UName = rs.getString(2); + msg.setMID(mid); + msg.setUser(new User()); + msg.getUser().setUID(rs.getInt(1)); + msg.getUser().setUName(rs.getString(2)); msg.TimeAgo = rs.getInt(3); msg.TimestampString = rs.getString(4); msg.ReadOnly = rs.getBoolean(5); @@ -267,7 +267,7 @@ public class MessagesQueries { msg.Hidden = rs.getBoolean(13); msg.parseTags(rs.getString(14)); msg.RepliesBy = rs.getString(15); - msg.Text = rs.getString(16); + msg.setText(rs.getString(16)); return msg; }); } @@ -280,15 +280,15 @@ public class MessagesQueries { + "WHERE replies.message_id=? AND replies.reply_id=?", new Object[]{mid, rid}, (rs, num) -> { Message msg = new Message(); - msg.MID = mid; - msg.RID = rid; - msg.User = new com.juick.User(); - msg.User.UID = rs.getInt(1); - msg.User.UName = rs.getString(2); + msg.setMID(mid); + msg.setRID(rid); + msg.setUser(new User()); + msg.getUser().setUID(rs.getInt(1)); + msg.getUser().setUName(rs.getString(2)); msg.ReplyTo = rs.getInt(3); msg.TimestampString = rs.getString(4); msg.AttachmentType = rs.getString(5); - msg.Text = rs.getString(6); + msg.setText(rs.getString(6)); return msg; }); } @@ -299,8 +299,8 @@ public class MessagesQueries { + "ON messages.user_id=users.id WHERE messages.message_id=?", new Object[]{mid}, (rs, num) -> { User res = new com.juick.User(); - res.UID = rs.getInt(1); - res.UName = rs.getString(2); + res.setUID(rs.getInt(1)); + res.setUName(rs.getString(2)); return res; }); } @@ -893,18 +893,18 @@ public class MessagesQueries { + "IN (" + Utils.convertArrayInt2String(mids) + ") " + "ORDER BY messages.message_id DESC", (rs, rowNum) -> { com.juick.Message msg = new com.juick.Message(); - msg.User = new com.juick.User(); + msg.setUser(new User()); - msg.MID = rs.getInt(1); - msg.User.UID = rs.getInt(2); - msg.User.UName = rs.getString(3); + msg.setMID(rs.getInt(1)); + msg.getUser().setUID(rs.getInt(2)); + msg.getUser().setUName(rs.getString(3)); if (rs.getString(4) != null) { msg.parseTags(rs.getString(4)); } msg.ReadOnly = rs.getInt(5) == 1; msg.Privacy = rs.getInt(6); msg.FriendsOnly = msg.Privacy < 0; - msg.Text = rs.getString(7); + msg.setText(rs.getString(7)); msg.TimeAgo = rs.getInt(8); msg.TimestampString = rs.getString(9); msg.Replies = rs.getInt(10); @@ -932,14 +932,14 @@ public class MessagesQueries { rs.beforeFirst(); while (rs.next()) { com.juick.Message msg = new com.juick.Message(); - msg.MID = mid; - msg.RID = rs.getInt(1); + msg.setMID(mid); + msg.setRID(rs.getInt(1)); msg.ReplyTo = rs.getInt(2); - msg.User = new com.juick.User(); - msg.User.UID = rs.getInt(3); - msg.User.UName = rs.getString(4); - msg.User.Banned = rs.getBoolean(5); - msg.Text = rs.getString(6); + msg.setUser(new User()); + msg.getUser().setUID(rs.getInt(3)); + msg.getUser().setUName(rs.getString(4)); + msg.getUser().Banned = rs.getBoolean(5); + msg.setText(rs.getString(6)); msg.TimeAgo = rs.getInt(7); msg.TimestampString = rs.getString(8); msg.AttachmentType = rs.getString(9); diff --git a/src/main/java/com/juick/server/PMQueries.java b/src/main/java/com/juick/server/PMQueries.java index d49bb527..7984844a 100644 --- a/src/main/java/com/juick/server/PMQueries.java +++ b/src/main/java/com/juick/server/PMQueries.java @@ -104,7 +104,7 @@ public class PMQueries { + "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.setUID(rs.getInt(1)); u.MessagesCount = rs.getInt(2); return u; }, new Object[]{uid}); @@ -116,11 +116,11 @@ public class PMQueries { + "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; + u.setUID(uuid); return u; }, new Object[]{uid, uid, cnt * 2}); for (User addUser : addUsers) { - if (!haveUserInArray(users, addUser.UID)) { + if (!haveUserInArray(users, addUser.getUID())) { users.add(addUser); if (users.size() >= cnt) { break; @@ -137,7 +137,7 @@ public class PMQueries { public static boolean haveUserInArray(List arr, int uid) { for (User user : arr) { - if (user.UID == uid) { + if (user.getUID() == uid) { return true; } } @@ -151,9 +151,9 @@ public class PMQueries { (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.setUser(new User()); + msg.getUser().setUID(uuid); + msg.setText(rs.getString(2)); msg.TimestampString = rs.getString(3); return msg; }, new Object[]{uid, uid_to, uid, uid_to}); @@ -176,10 +176,10 @@ public class PMQueries { rs.beforeFirst(); while (rs.next()) { com.juick.Message msg = new com.juick.Message(); - msg.User = new com.juick.User(); - msg.User.UID = rs.getInt(1); - msg.User.UName = rs.getString(2); - msg.Text = rs.getString(3); + msg.setUser(new User()); + msg.getUser().setUID(rs.getInt(1)); + msg.getUser().setUName(rs.getString(2)); + msg.setText(rs.getString(3)); msg.TimeAgo = rs.getInt(4); msg.TimestampString = rs.getString(5); msgs.add(0, msg); @@ -205,10 +205,10 @@ public class PMQueries { rs.beforeFirst(); while (rs.next()) { com.juick.Message msg = new com.juick.Message(); - msg.User = new com.juick.User(); - msg.User.UID = rs.getInt(1); - msg.User.UName = rs.getString(2); - msg.Text = rs.getString(3); + msg.setUser(new User()); + msg.getUser().setUID(rs.getInt(1)); + msg.getUser().setUName(rs.getString(2)); + msg.setText(rs.getString(3)); msg.TimeAgo = rs.getInt(4); msg.TimestampString = rs.getString(5); msgs.add(0, msg); diff --git a/src/main/java/com/juick/server/ShowQueries.java b/src/main/java/com/juick/server/ShowQueries.java index 05a58e9a..3ddff412 100644 --- a/src/main/java/com/juick/server/ShowQueries.java +++ b/src/main/java/com/juick/server/ShowQueries.java @@ -18,7 +18,7 @@ public class ShowQueries { "AND subscr_users.user_id NOT IN (SELECT bl_user_id FROM bl_users WHERE user_id=?) " + "AND subscr_users.user_id!=? AND users.lastmessage>UNIX_TIMESTAMP()-259200 " + "GROUP BY subscr_users.user_id ORDER BY count(*) DESC LIMIT 10", - String.class, new Object[] {forUser.UID, forUser.UID, forUser.UID, forUser.UID}); + String.class, new Object[] {forUser.getUID(), forUser.getUID(), forUser.getUID(), forUser.getUID()}); } public static List getTopUsers(JdbcTemplate sql) { diff --git a/src/main/java/com/juick/server/UserQueries.java b/src/main/java/com/juick/server/UserQueries.java index 27ae2870..2cd557c6 100644 --- a/src/main/java/com/juick/server/UserQueries.java +++ b/src/main/java/com/juick/server/UserQueries.java @@ -46,8 +46,8 @@ public class UserQueries { @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.setUID(rs.getInt("id")); + user.setUName(rs.getString("nick")); user.Banned = rs.getBoolean("banned"); return user; } @@ -120,13 +120,13 @@ public class UserQueries { if (i > 0) { uids += ","; } - uids += users.get(i).UID; + uids += users.get(i).getUID(); } sql.query("SELECT id,nick,banned FROM users WHERE id IN (" + uids + ")", (rs, num) -> { User u = users.get(num); - u.UName = rs.getString(2); + u.setUName(rs.getString(2)); return u; }); @@ -139,9 +139,9 @@ public class UserQueries { + "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); + user.setUID(rs.getInt(1)); + user.setUName(rs.getString(2)); + user.setJID(rs.getString(3)); return user; }); } @@ -166,7 +166,7 @@ public class UserQueries { 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; + user.setAuthHash(hash); return user; } @@ -385,8 +385,8 @@ public class UserQueries { rs.beforeFirst(); while (rs.next()) { com.juick.User u = new com.juick.User(); - u.UID = rs.getInt(1); - u.UName = rs.getString(2); + u.setUID(rs.getInt(1)); + u.setUName(rs.getString(2)); users.add(u); } } catch (SQLException e) { @@ -414,8 +414,8 @@ public class UserQueries { rs.beforeFirst(); while (rs.next()) { com.juick.User u = new com.juick.User(); - u.UID = rs.getInt(1); - u.UName = rs.getString(2); + u.setUID(rs.getInt(1)); + u.setUName(rs.getString(2)); users.add(u); } } catch (SQLException e) { @@ -434,7 +434,7 @@ public class UserQueries { sql.prepareStatement("INSERT INTO twitter(user_id,access_token,access_token_secret,uname) " + "VALUES (?,?,?,?)" + " ON DUPLICATE KEY UPDATE access_token=?,access_token_secret=?,uname=?"); - stmt.setInt(1, user.UID); + stmt.setInt(1, user.getUID()); stmt.setString(2, accessToken); stmt.setString(3, accessTokenSecret); stmt.setString(4, screenName); @@ -445,7 +445,7 @@ public class UserQueries { PreparedStatement stmt2 = sql.prepareStatement("INSERT INTO subscr_users(user_id,suser_id,jid) " + "VALUES (?,1741,'juick\\@twitter.juick.com')"); - stmt2.setInt(1, user.UID); + stmt2.setInt(1, user.getUID()); return stmt2.execute(); } } catch (SQLException e) { @@ -483,7 +483,7 @@ public class UserQueries { "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.setInt(2, user.getUID()); preparedStatement.setString(3, JID); return preparedStatement; -- cgit v1.2.3