diff options
author | 2013-07-16 04:07:15 +0700 | |
---|---|---|
committer | 2013-07-16 04:07:15 +0700 | |
commit | 378b014b82158d06a6ee986cb7522a04bc59f5bc (patch) | |
tree | cd59383a4281a18d7d4968075eeebbb1fe0bf4d3 | |
parent | 1afa0e255d694840c77c19c1effed6c0d0a92f1f (diff) |
getUsersByName, getUsersByJID
-rw-r--r-- | src/com/juick/server/MessagesQueries.java | 6 | ||||
-rw-r--r-- | src/com/juick/server/UserQueries.java | 53 | ||||
-rw-r--r-- | src/com/juick/server/Utils.java | 13 |
3 files changed, 65 insertions, 7 deletions
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<Integer> mids = new ArrayList<Integer>(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<Integer> mids = new ArrayList<Integer>(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<com.juick.User> getUsersByName(Connection sql, ArrayList<String> unames) { + ArrayList<com.juick.User> users = new ArrayList<com.juick.User>(); + + 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<com.juick.User> getUsersByID(Connection sql, ArrayList<Integer> uids) { ArrayList<com.juick.User> users = new ArrayList<com.juick.User>(); 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<com.juick.User> getUsersByJID(Connection sql, ArrayList<String> jids) { + ArrayList<com.juick.User> users = new ArrayList<com.juick.User>(); + + 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<Integer> mids) { + public static String convertArrayInt2String(ArrayList<Integer> 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<String> 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 { |