aboutsummaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--src/main/java/com/juick/server/MessagesQueries.java151
-rw-r--r--src/main/java/com/juick/server/PMQueries.java204
-rw-r--r--src/main/java/com/juick/server/TagQueries.java63
-rw-r--r--src/main/java/com/juick/server/UserQueries.java129
4 files changed, 191 insertions, 356 deletions
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<Integer> getMyFeed(Connection sql, int uid, int before) {
+ public static List<Integer> getMyFeed(JdbcTemplate sql, int uid, int before) {
List<Integer> 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<? AND (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, 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<? AND (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, 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<? ORDER BY message_id DESC LIMIT 20");
- stmt.setInt(1, uid);
- stmt.setInt(2, before);
- } else {
- stmt = sql.prepareStatement("SELECT message_id FROM messages WHERE user_id=? ORDER BY message_id DESC LIMIT 20");
- stmt.setInt(1, 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.addAll(sql.queryForList("SELECT message_id FROM messages " +
+ "WHERE user_id=? AND message_id<? ORDER BY message_id DESC LIMIT 20", Integer.class,
+ new Object[]{uid, before}));
+ } else {
+ mids.addAll(sql.queryForList("SELECT message_id FROM messages " +
+ "WHERE user_id=? ORDER BY message_id DESC LIMIT 20", Integer.class,
+ new Object[]{uid}));
}
Collections.sort(mids, Collections.reverseOrder());
@@ -945,50 +931,47 @@ public class MessagesQueries {
return mids;
}
- public static List<com.juick.Message> getMessages(Connection sql, List<Integer> mids) {
+ public static List<com.juick.Message> getMessages(JdbcTemplate sql, List<Integer> mids) {
List<com.juick.Message> 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<com.juick.Message> 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<Integer> 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<com.juick.User> getPMLastConversationsUsers(Connection sql, int uid, int cnt) {
- List<User> 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<User> getPMLastConversationsUsers(JdbcTemplate sql, int uid, int cnt) {
+ List<User> 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<User> 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<com.juick.Message> getPMMessages(Connection sql, int uid, int uid_to) {
- List<com.juick.Message> 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<com.juick.Message> getPMMessages(JdbcTemplate sql, int uid, int uid_to) {
+ List<com.juick.Message> 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<com.juick.Tag> getTags(Connection sql, String[] tags, boolean autoCreate) {
+ public static List<com.juick.Tag> getTags(JdbcTemplate sql, String[] tags, boolean autoCreate) {
List<Tag> 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<com.juick.Tag> 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<User> {
@Override
@@ -115,7 +111,7 @@ public class UserQueries {
uids.toArray());
}
- public static boolean fillUsersByID(Connection sql, List<com.juick.User> users) {
+ public static boolean fillUsersByID(JdbcTemplate sql, List<User> 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<com.juick.User> getUsersByJID(Connection sql, List<String> jids) {
- List<com.juick.User> 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<com.juick.User> getUsersByJID(JdbcTemplate sql, List<String> 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<Integer> 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<Integer> checkBL(Connection sql, int visitor, List<Integer> 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<Integer> getUserRead(Connection sql, int uid) {
- return SQLHelpers.getArrayInteger(sql, "SELECT user_id FROM subscr_users WHERE suser_id=?", uid);
+ public static List<Integer> getUserRead(JdbcTemplate sql, int uid) {
+ return sql.queryForList("SELECT user_id FROM subscr_users WHERE suser_id=?", Integer.class, uid);
}
public static List<com.juick.User> 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<Integer> getUserReaders(Connection sql, int uid) {
- return SQLHelpers.getArrayInteger(sql, "SELECT suser_id FROM subscr_users WHERE user_id=?", uid);
+ public static List<Integer> getUserReaders(JdbcTemplate sql, int uid) {
+ return sql.queryForList("SELECT suser_id FROM subscr_users WHERE user_id=?", Integer.class, uid);
}
public static List<com.juick.User> 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;
}
generated by cgit v1.2.3 (git 2.39.1) at 2024-11-22 22:56:25 +0000