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 +++++++++------------ 1 file changed, 67 insertions(+), 84 deletions(-) (limited to 'src/main/java/com/juick/server/MessagesQueries.java') 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) { -- cgit v1.2.3