From 378b014b82158d06a6ee986cb7522a04bc59f5bc Mon Sep 17 00:00:00 2001 From: Ugnich Anton Date: Tue, 16 Jul 2013 04:07:15 +0700 Subject: getUsersByName, getUsersByJID --- src/com/juick/server/MessagesQueries.java | 6 ++-- src/com/juick/server/UserQueries.java | 53 +++++++++++++++++++++++++++++-- src/com/juick/server/Utils.java | 13 +++++++- 3 files changed, 65 insertions(+), 7 deletions(-) (limited to 'src/com/juick') diff --git a/src/com/juick/server/MessagesQueries.java b/src/com/juick/server/MessagesQueries.java index 55fc3898..7eaaff2e 100644 --- a/src/com/juick/server/MessagesQueries.java +++ b/src/com/juick/server/MessagesQueries.java @@ -558,7 +558,7 @@ public class MessagesQueries { ArrayList mids = new ArrayList(20); if (mids0.size() > 0) { try { - stmt = sql.prepareStatement("SELECT message_id FROM messages WHERE message_id IN (" + Utils.convertArray2String(mids0) + ") AND privacy>0 ORDER BY message_id DESC LIMIT 20"); + stmt = sql.prepareStatement("SELECT message_id FROM messages WHERE message_id IN (" + Utils.convertArrayInt2String(mids0) + ") AND privacy>0 ORDER BY message_id DESC LIMIT 20"); rs = stmt.executeQuery(); rs.beforeFirst(); while (rs.next()) { @@ -714,7 +714,7 @@ public class MessagesQueries { ArrayList mids = new ArrayList(20); if (mids0.size() > 0) { try { - stmt = sql.prepareStatement("SELECT message_id FROM messages WHERE message_id IN (" + Utils.convertArray2String(mids0) + ") AND privacy>=" + privacy + " ORDER BY message_id DESC"); + stmt = sql.prepareStatement("SELECT message_id FROM messages WHERE message_id IN (" + Utils.convertArrayInt2String(mids0) + ") AND privacy>=" + privacy + " ORDER BY message_id DESC"); rs = stmt.executeQuery(); rs.beforeFirst(); while (rs.next()) { @@ -736,7 +736,7 @@ public class MessagesQueries { 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()),messages.ts,messages.replies,messages_txt.repliesby,messages.attach,messages.place_id,places.name,messages.lat,messages.lon FROM ((messages INNER JOIN messages_txt ON messages.message_id=messages_txt.message_id) INNER JOIN users ON messages.user_id=users.id) LEFT JOIN places ON messages.place_id=places.place_id WHERE messages.message_id IN (" + Utils.convertArray2String(mids) + ") ORDER BY messages.message_id DESC"); + 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()),messages.ts,messages.replies,messages_txt.repliesby,messages.attach,messages.place_id,places.name,messages.lat,messages.lon FROM ((messages INNER JOIN messages_txt ON messages.message_id=messages_txt.message_id) INNER JOIN users ON messages.user_id=users.id) LEFT JOIN places ON messages.place_id=places.place_id WHERE messages.message_id IN (" + Utils.convertArrayInt2String(mids) + ") ORDER BY messages.message_id DESC"); rs = stmt.executeQuery(); rs.beforeFirst(); while (rs.next()) { diff --git a/src/com/juick/server/UserQueries.java b/src/com/juick/server/UserQueries.java index f0c5a57f..f1b235af 100644 --- a/src/com/juick/server/UserQueries.java +++ b/src/com/juick/server/UserQueries.java @@ -68,7 +68,7 @@ public class UserQueries { return uid; } - public static com.juick.User getUserByNick(Connection sql, String username) { + public static com.juick.User getUserByName(Connection sql, String username) { com.juick.User user = null; PreparedStatement stmt = null; @@ -90,13 +90,36 @@ public class UserQueries { return user; } + public static ArrayList getUsersByName(Connection sql, ArrayList unames) { + ArrayList 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) { + System.err.println(e); + } finally { + Utils.finishSQL(rs, stmt); + } + return users; + } + public static ArrayList getUsersByID(Connection sql, ArrayList uids) { ArrayList users = new ArrayList(); PreparedStatement stmt = null; ResultSet rs = null; try { - stmt = sql.prepareStatement("SELECT id,nick FROM users WHERE id IN (" + Utils.convertArray2String(uids) + ")"); + stmt = sql.prepareStatement("SELECT id,nick FROM users WHERE id IN (" + Utils.convertArrayInt2String(uids) + ")"); rs = stmt.executeQuery(); rs.beforeFirst(); while (rs.next()) { @@ -114,6 +137,30 @@ public class UserQueries { return users; } + public static ArrayList getUsersByJID(Connection sql, ArrayList jids) { + ArrayList 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) { + System.err.println(e); + } finally { + Utils.finishSQL(rs, stmt); + } + 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); } @@ -307,7 +354,7 @@ public class UserQueries { 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.convertArray2String(uids) + ")"); + 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(); diff --git a/src/com/juick/server/Utils.java b/src/com/juick/server/Utils.java index 997c4f32..d37931d0 100644 --- a/src/com/juick/server/Utils.java +++ b/src/com/juick/server/Utils.java @@ -28,7 +28,7 @@ import java.util.ArrayList; */ public class Utils { - public static String convertArray2String(ArrayList mids) { + public static String convertArrayInt2String(ArrayList mids) { String q = ""; for (int i = 0; i < mids.size(); i++) { if (i > 0) { @@ -39,6 +39,17 @@ public class Utils { return q; } + public static String convertArrayString2String(ArrayList unames) { + String q = ""; + for (int i = 0; i < unames.size(); i++) { + if (i > 0) { + q += ","; + } + q += "\"" + unames.get(i) + "\""; + } + return q; + } + public static void finishSQL(ResultSet rs, Statement stmt) { if (rs != null) { try { -- cgit v1.2.3