From d00fd7705cb1a4b085eed7f34df2f1c6d9a69f76 Mon Sep 17 00:00:00 2001 From: Vitaly Takmazov Date: Sat, 24 Oct 2015 18:09:15 +0300 Subject: moving to Gradle --- build.gradle | 1 + 1 file changed, 1 insertion(+) create mode 100644 build.gradle (limited to 'build.gradle') diff --git a/build.gradle b/build.gradle new file mode 100644 index 00000000..405a81af --- /dev/null +++ b/build.gradle @@ -0,0 +1 @@ +apply plugin: "java" -- cgit v1.2.3 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 --- build.gradle | 8 + src/main/java/com/juick/server/UserQueries.java | 274 +++++++----------------- 2 files changed, 89 insertions(+), 193 deletions(-) (limited to 'build.gradle') diff --git a/build.gradle b/build.gradle index 405a81af..b82c3d5c 100644 --- a/build.gradle +++ b/build.gradle @@ -1 +1,9 @@ apply plugin: "java" + +repositories { + mavenCentral() +} + +dependencies { + compile "org.springframework:spring-jdbc:4.2.4.RELEASE" +} \ No newline at end of file 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 From 9e740f48cca7332c67a165c6cf60e09bc549e686 Mon Sep 17 00:00:00 2001 From: Vitaly Takmazov Date: Mon, 27 Jun 2016 10:33:04 +0300 Subject: more spring-jdbc --- build.gradle | 3 +- src/main/java/com/juick/server/AdsQueries.java | 40 +++-- .../java/com/juick/server/CrosspostQueries.java | 74 ++++----- src/main/java/com/juick/server/PMQueries.java | 173 ++++++++------------- src/main/java/com/juick/server/TagQueries.java | 58 +++---- src/main/java/com/juick/server/UserQueries.java | 73 +++++---- 6 files changed, 169 insertions(+), 252 deletions(-) (limited to 'build.gradle') diff --git a/build.gradle b/build.gradle index b82c3d5c..9f5bc86d 100644 --- a/build.gradle +++ b/build.gradle @@ -5,5 +5,6 @@ repositories { } dependencies { - compile "org.springframework:spring-jdbc:4.2.4.RELEASE" + compile "org.springframework:spring-jdbc:4.3.0.RELEASE" + compile "org.apache.commons:commons-lang3:3.4" } \ No newline at end of file diff --git a/src/main/java/com/juick/server/AdsQueries.java b/src/main/java/com/juick/server/AdsQueries.java index ad9b7149..06590817 100644 --- a/src/main/java/com/juick/server/AdsQueries.java +++ b/src/main/java/com/juick/server/AdsQueries.java @@ -17,11 +17,8 @@ */ package com.juick.server; -import java.sql.Connection; -import java.sql.PreparedStatement; -import java.sql.SQLException; -import java.util.logging.Level; -import java.util.logging.Logger; +import org.springframework.dao.EmptyResultDataAccessException; +import org.springframework.jdbc.core.JdbcTemplate; /** * @@ -29,27 +26,26 @@ import java.util.logging.Logger; */ public class AdsQueries { - private static final Logger logger = Logger.getLogger(AdsQueries.class.getName()); - - public static int getAdMID(Connection sql, int uid) { + public static int getAdMID(JdbcTemplate sql, int uid) { if (uid > 0) { - return SQLHelpers.getInt(sql, "SELECT message_id FROM ads_messages WHERE message_id NOT IN (SELECT message_id FROM ads_messages_log WHERE user_id=? AND ts>UNIX_TIMESTAMP()-60*60*24 GROUP BY message_id HAVING COUNT(*)>2) ORDER BY RAND() LIMIT 1", uid, 0); + try { + return sql.queryForObject("SELECT message_id FROM ads_messages " + + "WHERE message_id NOT IN (SELECT message_id FROM ads_messages_log WHERE user_id=? " + + "AND ts>UNIX_TIMESTAMP()-60*60*24 GROUP BY message_id HAVING COUNT(*)>2) ORDER BY RAND() LIMIT 1", + Integer.class, uid); + } catch (EmptyResultDataAccessException e) { + return 0; + } } else { - return SQLHelpers.getInt(sql, "SELECT message_id FROM ads_messages ORDER BY RAND() LIMIT 1", 0); + try { + return sql.queryForObject("SELECT message_id FROM ads_messages ORDER BY RAND() LIMIT 1", Integer.class); + } catch (EmptyResultDataAccessException e) { + return 0; + } } } - public static void logAdMID(Connection sql, int uid, int mid) { - PreparedStatement stmt = null; - try { - stmt = sql.prepareStatement("INSERT INTO ads_messages_log(user_id,message_id,ts) VALUES (?,?,UNIX_TIMESTAMP())"); - stmt.setInt(1, uid); - stmt.setInt(2, mid); - stmt.executeUpdate(); - } catch (SQLException e) { - logger.log(Level.SEVERE, "sql exception", e); - } finally { - Utils.finishSQL(null, stmt); - } + public static void logAdMID(JdbcTemplate sql, int uid, int mid) { + sql.update("INSERT INTO ads_messages_log(user_id,message_id,ts) VALUES (?,?,UNIX_TIMESTAMP())", uid, mid); } } diff --git a/src/main/java/com/juick/server/CrosspostQueries.java b/src/main/java/com/juick/server/CrosspostQueries.java index b44b6b59..04898d0a 100644 --- a/src/main/java/com/juick/server/CrosspostQueries.java +++ b/src/main/java/com/juick/server/CrosspostQueries.java @@ -17,12 +17,11 @@ */ package com.juick.server; -import java.sql.Connection; -import java.sql.PreparedStatement; -import java.sql.ResultSet; -import java.sql.SQLException; -import java.util.logging.Level; -import java.util.logging.Logger; +import org.apache.commons.lang3.tuple.Pair; +import org.springframework.dao.EmptyResultDataAccessException; +import org.springframework.jdbc.core.JdbcTemplate; + +import java.util.Optional; /** * @@ -30,55 +29,34 @@ import java.util.logging.Logger; */ public class CrosspostQueries { - private static final Logger logger = Logger.getLogger(CrosspostQueries.class.getName()); - - public static String[] getTwitterTokens(Connection sql, int uid) { - String tokens[] = null; - - PreparedStatement stmt = null; - ResultSet rs = null; + public static Optional> getTwitterTokens(JdbcTemplate sql, int uid) { try { - stmt = sql.prepareStatement("SELECT access_token,access_token_secret FROM twitter WHERE user_id=? AND crosspost=1"); - stmt.setInt(1, uid); - rs = stmt.executeQuery(); - if (rs.first()) { - tokens = new String[2]; - tokens[0] = rs.getString(1); - tokens[1] = rs.getString(2); - } - } catch (SQLException e) { - logger.log(Level.SEVERE, "sql exception", e); - } finally { - Utils.finishSQL(rs, stmt); + return sql.queryForObject("SELECT access_token,access_token_secret FROM twitter WHERE user_id=? AND crosspost=1", + (rs, num) -> { + return Optional.of(Pair.of(rs.getString(1), rs.getString(2))); + }, uid); + } catch (EmptyResultDataAccessException e) { + return Optional.empty(); } - - return tokens; } - public static String getFacebookToken(Connection sql, int uid) { - return SQLHelpers.getString(sql, "SELECT access_token FROM facebook WHERE user_id=? AND access_token IS NOT NULL AND crosspost=1", uid); + public static Optional getFacebookToken(JdbcTemplate sql, int uid) { + try { + return Optional.of(sql.queryForObject("SELECT access_token FROM facebook WHERE user_id=? AND access_token IS NOT NULL " + + "AND crosspost=1", String.class, uid)); + } catch (EmptyResultDataAccessException e) { + return Optional.empty(); + } } - public static String[] getVKTokens(Connection sql, int uid) { - String tokens[] = null; - - PreparedStatement stmt = null; - ResultSet rs = null; + public static Optional> getVKTokens(JdbcTemplate sql, int uid) { try { - stmt = sql.prepareStatement("SELECT vk_id,access_token FROM vk WHERE user_id=? AND crosspost=1"); - stmt.setInt(1, uid); - rs = stmt.executeQuery(); - if (rs.first()) { - tokens = new String[2]; - tokens[0] = rs.getString(1); - tokens[1] = rs.getString(2); - } - } catch (SQLException e) { - logger.log(Level.SEVERE, "sql exception", e); - } finally { - Utils.finishSQL(rs, stmt); + return sql.queryForObject("SELECT vk_id,access_token FROM vk WHERE user_id=? AND crosspost=1", + (rs, num) -> { + return Optional.of(Pair.of(rs.getString(1), rs.getString(2))); + }, uid); + } catch (EmptyResultDataAccessException e) { + return Optional.empty(); } - - return tokens; } } diff --git a/src/main/java/com/juick/server/PMQueries.java b/src/main/java/com/juick/server/PMQueries.java index 6de30995..772a1c28 100644 --- a/src/main/java/com/juick/server/PMQueries.java +++ b/src/main/java/com/juick/server/PMQueries.java @@ -18,16 +18,10 @@ package com.juick.server; import com.juick.User; +import org.springframework.dao.EmptyResultDataAccessException; +import org.springframework.jdbc.core.JdbcTemplate; -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; /** * @@ -35,8 +29,6 @@ import org.springframework.jdbc.core.JdbcTemplate; */ public class PMQueries { - private static final Logger LOGGER = Logger.getLogger(PMQueries.class.getName()); - 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 (?,?,?)", uid_from, uid_to, body) > 0; @@ -55,20 +47,8 @@ public class PMQueries { uid, jid) > 0; } - public static boolean removePMinRoster(Connection sql, int uid, String jid) { - boolean ret = false; - PreparedStatement stmt = null; - try { - stmt = sql.prepareStatement("DELETE FROM pm_inroster WHERE user_id=? AND jid=?"); - 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 removePMinRoster(JdbcTemplate sql, int uid, String jid) { + return sql.update("DELETE FROM pm_inroster WHERE user_id=? AND jid=?", uid, jid) > 0; } public static boolean havePMinRoster(JdbcTemplate sql, int uid, String jid) { @@ -78,37 +58,49 @@ public class PMQueries { return res.size() > 0; } - public static String getLastView(Connection sql, int uid_from, int uid_to) { - String ret = null; - - PreparedStatement stmt = null; - ResultSet rs = null; + public static String getLastView(JdbcTemplate sql, int uid_from, int uid_to) { try { - stmt = sql.prepareStatement("SELECT lastview FROM pm_streams WHERE user_id=? AND user_id_to=?"); - stmt.setInt(1, uid_from); - stmt.setInt(2, uid_to); - rs = stmt.executeQuery(); - if (rs.first()) { - ret = rs.getString(1); - } - } catch (SQLException e) { - LOGGER.log(Level.SEVERE, "sql exception", e); - } finally { - Utils.finishSQL(rs, stmt); + return sql.queryForObject("SELECT lastview FROM pm_streams WHERE user_id=? AND user_id_to=?", String.class, + uid_from, uid_to); + } catch (EmptyResultDataAccessException e) { + return null; } - return ret; } public static List getPMLastConversationsUsers(JdbcTemplate sql, int uid, int cnt) { - return sql.query("SELECT users.id, users.nick,pm_streams.unread FROM pm_streams " + - "INNER JOIN users ON users.id = pm_streams.user_id WHERE user_id_to=? " + - "ORDER BY pm_streams.lastmessage DESC LIMIT ?", (rs, rowNum) -> { - com.juick.User u = new com.juick.User(); - u.setUID(rs.getInt(1)); - u.setUName(rs.getString(2)); - u.MessagesCount = rs.getInt(3); - return u; - }, uid, cnt); + List qusers = 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.setUID(rs.getInt(1)); + u.MessagesCount = rs.getInt(2); + return u; + }, uid); + + if (qusers.size() < cnt) { + 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.setUID(uuid); + return u; + }, uid, uid, cnt * 2); + for (User addUser : addUsers) { + if (!haveUserInArray(qusers, addUser.getUID())) { + qusers.add(addUser); + if (qusers.size() >= cnt) { + break; + } + } + } + } + if (!qusers.isEmpty()) { + UserQueries.fillUsersByID(sql, qusers); + } + + return qusers; } public static boolean haveUserInArray(List arr, int uid) { @@ -140,61 +132,34 @@ public class PMQueries { return msgs; } - public static List getLastPMInbox(Connection sql, int uid) { - List msgs = new ArrayList<>(20); - - PreparedStatement stmt = null; - ResultSet rs = null; - try { - stmt = sql.prepareStatement("SELECT pm.user_id,users.nick,pm.txt,TIMESTAMPDIFF(MINUTE,pm.ts,NOW()),pm.ts FROM pm INNER JOIN users ON pm.user_id=users.id WHERE pm.user_id_to=? ORDER BY pm.ts DESC LIMIT 20"); - stmt.setInt(1, uid); - rs = stmt.executeQuery(); - rs.beforeFirst(); - while (rs.next()) { - com.juick.Message msg = new com.juick.Message(); - 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.setDate(rs.getTimestamp(5)); - msgs.add(0, msg); - } - } catch (SQLException e) { - LOGGER.log(Level.SEVERE, "sql exception", e); - } finally { - Utils.finishSQL(rs, stmt); - } - - return msgs; + public static List getLastPMInbox(JdbcTemplate sql, int uid) { + return sql.query("SELECT pm.user_id,users.nick,pm.txt,TIMESTAMPDIFF(MINUTE,pm.ts,NOW()),pm.ts " + + "FROM pm INNER JOIN users ON pm.user_id=users.id WHERE pm.user_id_to=? ORDER BY pm.ts DESC LIMIT 20", + (rs, num) -> { + com.juick.Message msg = new com.juick.Message(); + 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.setDate(rs.getTimestamp(5)); + return msg; + }, uid); } - public static List getLastPMSent(Connection sql, int uid) { - List msgs = new ArrayList<>(20); - - PreparedStatement stmt = null; - ResultSet rs = null; - try { - stmt = sql.prepareStatement("SELECT pm.user_id_to,users.nick,pm.txt,TIMESTAMPDIFF(MINUTE,pm.ts,NOW()),pm.ts FROM pm INNER JOIN users ON pm.user_id_to=users.id WHERE pm.user_id=? ORDER BY pm.ts DESC LIMIT 20"); - stmt.setInt(1, uid); - rs = stmt.executeQuery(); - rs.beforeFirst(); - while (rs.next()) { - com.juick.Message msg = new com.juick.Message(); - 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.setDate(rs.getTimestamp(5)); - msgs.add(0, msg); - } - } catch (SQLException e) { - LOGGER.log(Level.SEVERE, "sql exception", e); - } finally { - Utils.finishSQL(rs, stmt); - } - - return msgs; + public static List getLastPMSent(JdbcTemplate sql, int uid) { + return sql.query("SELECT pm.user_id_to,users.nick,pm.txt,TIMESTAMPDIFF(MINUTE,pm.ts,NOW())," + + "pm.ts FROM pm INNER JOIN users ON pm.user_id_to=users.id " + + "WHERE pm.user_id=? ORDER BY pm.ts DESC LIMIT 20", + (rs, num) -> { + com.juick.Message msg = new com.juick.Message(); + 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.setDate(rs.getTimestamp(5)); + return msg; + }, uid); } } diff --git a/src/main/java/com/juick/server/TagQueries.java b/src/main/java/com/juick/server/TagQueries.java index f3ab480e..d2159b5a 100644 --- a/src/main/java/com/juick/server/TagQueries.java +++ b/src/main/java/com/juick/server/TagQueries.java @@ -90,25 +90,12 @@ public class TagQueries { return ret; } - public static boolean getTagNoIndex(Connection sql, int tag_id) { - boolean ret = false; - - PreparedStatement stmt = null; - ResultSet rs = null; + public static boolean getTagNoIndex(JdbcTemplate sql, int tag_id) { try { - stmt = sql.prepareStatement("SELECT noindex FROM tags WHERE tag_id=?"); - stmt.setInt(1, tag_id); - 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 sql.queryForObject("SELECT noindex FROM tags WHERE tag_id=?", Integer.class, tag_id) == 1; + } catch (EmptyResultDataAccessException e) { + return false; } - - return ret; } public static int createTag(JdbcTemplate sql, String name) { @@ -123,28 +110,17 @@ public class TagQueries { return holder.getKey().intValue(); } - public static List getUserTagsAll(Connection sql, int uid) { - List tags = new ArrayList<>(); - - PreparedStatement stmt = null; - ResultSet rs = null; - try { - stmt = sql.prepareStatement("SELECT tags.name,COUNT(messages.message_id) FROM (messages INNER JOIN messages_tags ON (messages.user_id=? AND messages.message_id=messages_tags.message_id)) INNER JOIN tags ON messages_tags.tag_id=tags.tag_id GROUP BY tags.tag_id ORDER BY tags.name ASC"); - stmt.setInt(1, uid); - rs = stmt.executeQuery(); - rs.beforeFirst(); - while (rs.next()) { - com.juick.Tag t = new com.juick.Tag(); - t.Name = rs.getString(1); - t.UsageCnt = rs.getInt(2); - tags.add(t); - } - } catch (SQLException e) { - LOGGER.log(Level.SEVERE, "sql exception", e); - } finally { - Utils.finishSQL(rs, stmt); - } - return tags; + public static List getUserTagsAll(JdbcTemplate sql, int uid) { + return sql.query("SELECT tags.name,COUNT(messages.message_id) " + + "FROM (messages INNER JOIN messages_tags ON (messages.user_id=? " + + "AND messages.message_id=messages_tags.message_id)) " + + "INNER JOIN tags ON messages_tags.tag_id=tags.tag_id GROUP BY tags.tag_id ORDER BY tags.name ASC", + (rs, rowNum) -> { + Tag t = new Tag(); + t.Name = rs.getString(1); + t.UsageCnt = rs.getInt(2); + return t; + }, uid); } public static List getUserBLTags(Connection sql, int uid) { @@ -167,4 +143,8 @@ public class TagQueries { } return tags; } + + public static List getPopularTags(JdbcTemplate sql) { + return sql.queryForList("SELECT name FROM tags WHERE top=1 ORDER BY name ASC", String.class); + } } diff --git a/src/main/java/com/juick/server/UserQueries.java b/src/main/java/com/juick/server/UserQueries.java index 817ae5d3..0d6a3485 100644 --- a/src/main/java/com/juick/server/UserQueries.java +++ b/src/main/java/com/juick/server/UserQueries.java @@ -23,6 +23,7 @@ import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.jdbc.core.RowMapper; import org.springframework.jdbc.support.GeneratedKeyHolder; import org.springframework.jdbc.support.KeyHolder; +import org.springframework.util.StringUtils; import java.sql.Connection; import java.sql.PreparedStatement; @@ -330,47 +331,22 @@ public class UserQueries { } } - public static List checkBL(Connection sql, int visitor, List uids) { - List ret = new ArrayList<>(); - - PreparedStatement stmt = null; - ResultSet rs = null; - try { - stmt = sql.prepareStatement("SELECT user_id FROM bl_users WHERE bl_user_id=? and user_id IN (" + Utils.convertArrayInt2String(uids) + ")"); - stmt.setInt(1, visitor); - rs = stmt.executeQuery(); - rs.beforeFirst(); - while (rs.next()) { - ret.add(rs.getInt(1)); - } - } catch (SQLException e) { - LOGGER.log(Level.SEVERE, "sql exception", e); - } finally { - Utils.finishSQL(rs, stmt); + public static List checkBL(JdbcTemplate sql, int visitor, List uids) { + if (!uids.isEmpty()) { + return sql.queryForList("SELECT user_id FROM bl_users WHERE bl_user_id=? and user_id IN (" + + StringUtils.collectionToCommaDelimitedString(uids) + ")", Integer.class, visitor); + } else { + return new ArrayList<>(); } - - return ret; } - public static boolean isSubscribed(Connection sql, int uid, int check) { - boolean ret = false; - - PreparedStatement stmt = null; - ResultSet rs = null; + public static boolean isSubscribed(JdbcTemplate sql, int uid, int check) { try { - stmt = sql.prepareStatement("SELECT 1 FROM subscr_users WHERE suser_id=? AND user_id=?"); - stmt.setInt(1, uid); - stmt.setInt(2, check); - 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 sql.queryForObject("SELECT 1 FROM subscr_users WHERE suser_id=? AND user_id=?", + Integer.class, uid, check) == 1; + } catch (EmptyResultDataAccessException e) { + return false; } - return ret; } public static List getUserRead(JdbcTemplate sql, int uid) { @@ -403,8 +379,29 @@ public class UserQueries { return users; } - public static List getUserReaders(JdbcTemplate sql, int uid) { - return sql.queryForList("SELECT suser_id FROM subscr_users WHERE user_id=?", Integer.class, uid); + public List getUserReaders(JdbcTemplate sql, int uid) { + return sql.query("SELECT users.id, users.nick FROM subscr_users " + + "INNER JOIN users ON subscr_users.suser_id=users.id " + + "WHERE subscr_users.user_id=? ORDER BY users.nick", + (rs, num) -> { + com.juick.User u = new com.juick.User(); + u.setUID(rs.getInt(1)); + u.setUName(rs.getString(2)); + return u; + }, uid); + } + + public List getUserFriends(JdbcTemplate sql, int uid) { + return sql.query("SELECT users.id,users.nick FROM subscr_users " + + "INNER JOIN users ON subscr_users.user_id=users.id " + + "WHERE subscr_users.suser_id=? AND users.id!=? " + + "ORDER BY users.nick", + (rs, num) -> { + com.juick.User u = new com.juick.User(); + u.setUID(rs.getInt(1)); + u.setUName(rs.getString(2)); + return u; + }, uid, uid); } public static List getUserBLUsers(Connection sql, int uid) { -- cgit v1.2.3