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 --- .../java/com/juick/server/MessagesQueries.java | 151 +++++++-------- src/main/java/com/juick/server/PMQueries.java | 204 ++++++--------------- src/main/java/com/juick/server/TagQueries.java | 63 +++---- src/main/java/com/juick/server/UserQueries.java | 129 +++++-------- 4 files changed, 191 insertions(+), 356 deletions(-) (limited to 'src/main/java/com/juick/server') diff --git a/src/main/java/com/juick/server/MessagesQueries.java b/src/main/java/com/juick/server/MessagesQueries.java index 4d5683bd..a8605d3a 100644 --- a/src/main/java/com/juick/server/MessagesQueries.java +++ b/src/main/java/com/juick/server/MessagesQueries.java @@ -17,6 +17,9 @@ */ package com.juick.server; +import com.juick.Message; +import org.springframework.jdbc.core.JdbcTemplate; + import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; @@ -28,6 +31,7 @@ import java.util.Collections; import java.util.List; import java.util.logging.Level; import java.util.logging.Logger; +import org.springframework.jdbc.core.RowMapper; /** * @@ -553,50 +557,32 @@ public class MessagesQueries { return mids; } - public static List getMyFeed(Connection sql, int uid, int before) { + public static List getMyFeed(JdbcTemplate sql, int uid, int before) { List mids = new ArrayList<>(40); - PreparedStatement stmt = null; - ResultSet rs = null; - try { - if (before > 0) { - stmt = sql.prepareStatement("SELECT message_id FROM messages INNER JOIN subscr_users ON (subscr_users.suser_id=? AND subscr_users.user_id=messages.user_id) WHERE message_id=0 OR (privacy>=-2 AND privacy<=-1 AND messages.user_id IN (SELECT user_id FROM wl_users WHERE wl_user_id=?))) ORDER BY message_id DESC LIMIT 20"); - stmt.setInt(1, uid); - stmt.setInt(2, before); - stmt.setInt(3, uid); - } else { - stmt = sql.prepareStatement("SELECT message_id FROM messages INNER JOIN subscr_users ON (subscr_users.suser_id=? AND subscr_users.user_id=messages.user_id) WHERE (privacy>=0 OR (privacy>=-2 AND privacy<=-1 AND messages.user_id IN (SELECT user_id FROM wl_users WHERE wl_user_id=?))) ORDER BY message_id DESC LIMIT 20"); - stmt.setInt(1, uid); - stmt.setInt(2, uid); - } - rs = stmt.executeQuery(); - rs.beforeFirst(); - while (rs.next()) { - mids.add(rs.getInt(1)); - } - } catch (SQLException e) { - logger.log(Level.SEVERE, "sql exception", e); - } finally { - Utils.finishSQL(rs, stmt); + if (before > 0) { + mids = sql.queryForList("SELECT message_id FROM messages " + + "INNER JOIN subscr_users ON (subscr_users.suser_id=? AND subscr_users.user_id=messages.user_id) " + + "WHERE message_id=0 OR (privacy>=-2 AND privacy<=-1 AND messages.user_id " + + "IN (SELECT user_id FROM wl_users WHERE wl_user_id=?))) ORDER BY message_id DESC LIMIT 20", + Integer.class, new Object[]{uid, before, uid}); + } else { + mids = sql.queryForList("SELECT message_id FROM messages " + + "INNER JOIN subscr_users ON (subscr_users.suser_id=? " + + "AND subscr_users.user_id=messages.user_id) " + + "WHERE (privacy>=0 OR (privacy>=-2 AND privacy<=-1 AND messages.user_id " + + "IN (SELECT user_id FROM wl_users WHERE wl_user_id=?))) ORDER BY message_id DESC LIMIT 20", + Integer.class, new Object[]{uid, uid}); } - try { - if (before > 0) { - stmt = sql.prepareStatement("SELECT message_id FROM messages WHERE user_id=? AND message_id 0) { + mids.addAll(sql.queryForList("SELECT message_id FROM messages " + + "WHERE user_id=? AND message_id getMessages(Connection sql, List mids) { + public static List getMessages(JdbcTemplate sql, List mids) { List msgs = new ArrayList<>(20); - - PreparedStatement stmt = null; - ResultSet rs = null; - try { - stmt = sql.prepareStatement("SELECT STRAIGHT_JOIN messages.message_id,messages.user_id,users.nick,messages_txt.tags,messages.readonly,messages.privacy,messages_txt.txt,TIMESTAMPDIFF(MINUTE,messages.ts,NOW()),DATE_FORMAT(messages.ts,'%Y-%m-%d %H:%i:%s'),messages.replies,messages_txt.repliesby,messages.attach,messages.lat,messages.lon,messages.likes FROM (messages INNER JOIN messages_txt ON messages.message_id=messages_txt.message_id) INNER JOIN users ON messages.user_id=users.id WHERE messages.message_id IN (" + Utils.convertArrayInt2String(mids) + ") ORDER BY messages.message_id DESC"); - rs = stmt.executeQuery(); - rs.beforeFirst(); - while (rs.next()) { - com.juick.Message msg = new com.juick.Message(); - msg.User = new com.juick.User(); - - msg.MID = rs.getInt(1); - msg.User.UID = rs.getInt(2); - msg.User.UName = 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.TimeAgo = rs.getInt(8); - msg.TimestampString = rs.getString(9); - msg.Replies = rs.getInt(10); - msg.RepliesBy = rs.getString(11); - msg.AttachmentType = rs.getString(12); - if (rs.getDouble(13) != 0) { - msg.Place = new com.juick.Place(); - msg.Place.lat = rs.getDouble(13); - msg.Place.lon = rs.getDouble(14); - } - msg.Likes = rs.getInt(15); - - msgs.add(msg); - } - } catch (SQLException e) { - logger.log(Level.SEVERE, "sql exception", e); - } finally { - Utils.finishSQL(rs, stmt); - } - - return msgs; + + return sql.query("SELECT messages.message_id,messages.user_id,users.nick," + + "messages_txt.tags,messages.readonly,messages.privacy,messages_txt.txt," + + "TIMESTAMPDIFF(MINUTE,messages.ts,NOW())," + + "DATE_FORMAT(messages.ts,'%Y-%m-%d %H:%i:%s'),messages.replies," + + "messages_txt.repliesby,messages.attach,messages.lat," + + "messages.lon,messages.likes " + + "FROM (messages INNER JOIN messages_txt " + + "ON messages.message_id=messages_txt.message_id) " + + "INNER JOIN users ON messages.user_id=users.id " + + "WHERE messages.message_id " + + "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.MID = rs.getInt(1); + msg.User.UID = rs.getInt(2); + msg.User.UName = 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.TimeAgo = rs.getInt(8); + msg.TimestampString = rs.getString(9); + msg.Replies = rs.getInt(10); + msg.RepliesBy = rs.getString(11); + msg.AttachmentType = rs.getString(12); + if (rs.getDouble(13) != 0) { + msg.Place = new com.juick.Place(); + msg.Place.lat = rs.getDouble(13); + msg.Place.lon = rs.getDouble(14); + } + msg.Likes = rs.getInt(15); + return msg; + }); } public static List getReplies(Connection sql, int mid) { 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); } diff --git a/src/main/java/com/juick/server/TagQueries.java b/src/main/java/com/juick/server/TagQueries.java index 1c5e27d1..076019ff 100644 --- a/src/main/java/com/juick/server/TagQueries.java +++ b/src/main/java/com/juick/server/TagQueries.java @@ -18,6 +18,8 @@ package com.juick.server; import com.juick.Tag; +import org.springframework.jdbc.core.JdbcTemplate; +import org.springframework.jdbc.core.RowMapper; import java.sql.Connection; import java.sql.PreparedStatement; @@ -28,6 +30,8 @@ import java.util.ArrayList; import java.util.List; import java.util.logging.Level; import java.util.logging.Logger; +import org.springframework.jdbc.support.GeneratedKeyHolder; +import org.springframework.jdbc.support.KeyHolder; /** * @@ -61,26 +65,15 @@ public class TagQueries { return ret; } - public static com.juick.Tag getTag(Connection sql, String tag, boolean autoCreate) { - com.juick.Tag ret = null; - - PreparedStatement stmt = null; - ResultSet rs = null; - try { - stmt = sql.prepareStatement("SELECT tag_id,synonym_id,name FROM tags WHERE name=?"); - stmt.setString(1, tag); - rs = stmt.executeQuery(); - if (rs.first()) { - ret = new com.juick.Tag(); - ret.TID = rs.getInt(1); - ret.SynonymID = rs.getInt(2); - ret.Name = rs.getString(3); - } - } catch (SQLException e) { - logger.log(Level.SEVERE, "sql exception", e); - } finally { - Utils.finishSQL(rs, stmt); - } + public static com.juick.Tag getTag(JdbcTemplate sql, String tag, boolean autoCreate) { + Tag ret = sql.queryForObject("SELECT tag_id,synonym_id,name FROM tags WHERE name=?", + new Object[]{tag}, (rs, rowNum) -> { + Tag ret1 = new Tag(); + ret1.TID = rs.getInt(1); + ret1.SynonymID = rs.getInt(2); + ret1.Name = rs.getString(3); + return ret1; + }); if (ret == null && autoCreate) { ret = new com.juick.Tag(); @@ -91,7 +84,7 @@ public class TagQueries { return ret; } - public static List getTags(Connection sql, String[] tags, boolean autoCreate) { + public static List getTags(JdbcTemplate sql, String[] tags, boolean autoCreate) { List ret = new ArrayList<>(); for (String tag : tags) { @@ -127,26 +120,16 @@ public class TagQueries { return ret; } - public static int createTag(Connection sql, String name) { - int ret = 0; + public static int createTag(JdbcTemplate sql, String name) { + KeyHolder holder = new GeneratedKeyHolder(); + sql.update(con -> { + PreparedStatement stmt = con.prepareStatement("INSERT INTO tags(name) VALUES (?)", + Statement.RETURN_GENERATED_KEYS); + stmt.setString(1, name); + return stmt; + }, holder); - PreparedStatement stmt = null; - ResultSet rs = null; - try { - stmt = sql.prepareStatement("INSERT INTO tags(name) VALUES (?)", Statement.RETURN_GENERATED_KEYS); - stmt.setString(1, name); - stmt.executeUpdate(); - rs = stmt.getGeneratedKeys(); - if (rs.first()) { - ret = rs.getInt(1); - } - } catch (SQLException e) { - logger.log(Level.SEVERE, "sql exception", e); - } finally { - Utils.finishSQL(rs, stmt); - } - - return ret; + return holder.getKey().intValue(); } public static List getUserTagsAll(Connection sql, int uid) { 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