From 2635d6187ecd48c732d21e4f510f1df8451ce646 Mon Sep 17 00:00:00 2001 From: Vitaly Takmazov Date: Fri, 15 Jan 2016 14:49:38 +0300 Subject: spring-jdbc WIP --- .../java/com/juick/server/MessagesQueries.java | 230 +++++++-------------- 1 file changed, 79 insertions(+), 151 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 cff8caff..8f658b42 100644 --- a/src/main/java/com/juick/server/MessagesQueries.java +++ b/src/main/java/com/juick/server/MessagesQueries.java @@ -17,6 +17,8 @@ */ package com.juick.server; +import com.juick.Message; +import com.juick.User; import org.springframework.jdbc.core.JdbcTemplate; import java.sql.Connection; @@ -30,6 +32,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; /** * @@ -228,67 +231,46 @@ public class MessagesQueries { return SQLHelpers.getInt(sql, "SELECT privacy FROM messages WHERE message_id=?", mid, -4); } - public static com.juick.Message getMessage(Connection sql, int mid) { - com.juick.Message msg = null; - - PreparedStatement stmt = null; - ResultSet rs = null; - try { - stmt = sql.prepareStatement("SELECT messages.user_id,users.nick,TIMESTAMPDIFF(MINUTE,messages.ts,NOW()),DATE_FORMAT(messages.ts,'%Y-%m-%d %H:%i:%s'),messages.readonly,messages.privacy,messages.replies,messages.attach,messages.place_id,messages.lat,messages.lon,messages.likes,messages.hidden FROM messages INNER JOIN users ON messages.user_id=users.id WHERE messages.message_id=?"); - stmt.setInt(1, mid); - rs = stmt.executeQuery(); - if (rs.first()) { - msg = new com.juick.Message(); - msg.MID = mid; - msg.User = new com.juick.User(); - msg.User.UID = rs.getInt(1); - msg.User.UName = rs.getString(2); - msg.TimeAgo = rs.getInt(3); - msg.TimestampString = rs.getString(4); - msg.ReadOnly = rs.getBoolean(5); - msg.Privacy = rs.getInt(6); - msg.FriendsOnly = msg.Privacy < 0; - msg.Replies = rs.getInt(7); - msg.AttachmentType = rs.getString(8); - //if (rs.getInt(9) > 0) { - // msg.Place = PlacesQueries.getPlace(sql, rs.getInt(9)); - //} - if (rs.getDouble(10) != 0) { - msg.Place = new com.juick.Place(); - msg.Place.lat = rs.getDouble(10); - msg.Place.lon = rs.getDouble(11); - } - msg.Likes = rs.getInt(12); - msg.Hidden = rs.getBoolean(13); - } - } catch (SQLException e) { - LOGGER.log(Level.SEVERE, "sql exception", e); - } finally { - Utils.finishSQL(rs, stmt); - } - - if (msg == null) { - return null; - } - - try { - stmt = sql.prepareStatement("SELECT tags,repliesby,txt FROM messages_txt WHERE message_id=?"); - stmt.setInt(1, mid); - rs = stmt.executeQuery(); - if (rs.first()) { - if (rs.getString(1) != null) { - msg.parseTags(rs.getString(1)); - } - msg.RepliesBy = rs.getString(2); - msg.Text = rs.getString(3); - } - } catch (SQLException e) { - LOGGER.log(Level.SEVERE, "sql exception", e); - } finally { - Utils.finishSQL(rs, stmt); - } - - return msg; + public static com.juick.Message getMessage(JdbcTemplate sql, int mid) { + return sql.queryForObject("SELECT messages.user_id,users.nick," + + "TIMESTAMPDIFF(MINUTE,messages.ts,NOW())," + + "DATE_FORMAT(messages.ts,'%Y-%m-%d %H:%i:%s')," + + "messages.readonly,messages.privacy,messages.replies," + + "messages.attach,messages.place_id,messages.lat," + + "messages.lon,messages.likes,messages.hidden," + + "txt.tags,txt.repliesby,txt.txt FROM messages " + + "INNER JOIN users ON messages.user_id=users.id " + + "INNER JOIN messages_txt " + + "ON messages.message_id = messages_txt.message_id " + + "WHERE messages.message_id=?", new Object[]{mid}, + (rs, rowNum) -> { + Message msg = new Message(); + msg.MID = mid; + msg.User = new com.juick.User(); + msg.User.UID = rs.getInt(1); + msg.User.UName = rs.getString(2); + msg.TimeAgo = rs.getInt(3); + msg.TimestampString = rs.getString(4); + msg.ReadOnly = rs.getBoolean(5); + msg.Privacy = rs.getInt(6); + msg.FriendsOnly = msg.Privacy < 0; + msg.Replies = rs.getInt(7); + msg.AttachmentType = rs.getString(8); + //if (rs.getInt(9) > 0) { + // msg.Place = PlacesQueries.getPlace(sql, rs.getInt(9)); + //} + if (rs.getDouble(10) != 0) { + msg.Place = new com.juick.Place(); + msg.Place.lat = rs.getDouble(10); + msg.Place.lon = rs.getDouble(11); + } + msg.Likes = rs.getInt(12); + msg.Hidden = rs.getBoolean(13); + msg.parseTags(rs.getString(14)); + msg.RepliesBy = rs.getString(15); + msg.Text = rs.getString(16); + return msg; + }); } public static com.juick.Message getReply(Connection sql, int mid, int rid) { @@ -322,26 +304,16 @@ public class MessagesQueries { return msg; } - public static com.juick.User getMessageAuthor(Connection sql, int mid) { - com.juick.User user = null; - - PreparedStatement stmt = null; - ResultSet rs = null; - try { - stmt = sql.prepareStatement("SELECT messages.user_id,users.nick FROM messages INNER JOIN users ON messages.user_id=users.id WHERE messages.message_id=?"); - stmt.setInt(1, mid); - rs = stmt.executeQuery(); - if (rs.first()) { - user = new com.juick.User(); - user.UID = rs.getInt(1); - user.UName = rs.getString(2); - } - } catch (SQLException e) { - LOGGER.log(Level.SEVERE, "sql exception", e); - } finally { - Utils.finishSQL(rs, stmt); - } - return user; + public static User getMessageAuthor(JdbcTemplate sql, int mid) { + return sql.queryForObject("SELECT messages.user_id,users.nick " + + "FROM messages INNER JOIN users " + + "ON messages.user_id=users.id WHERE messages.message_id=?", + new Object[]{mid}, (rs, num) -> { + User res = new com.juick.User(); + res.UID = rs.getInt(1); + res.UName = rs.getString(2); + return res; + }); } public static List getMessageTags(Connection sql, int mid) { @@ -829,31 +801,18 @@ public class MessagesQueries { return mids; } - public static List getUserRecommendations(Connection sql, int UID, int before) { - List mids = new ArrayList<>(20); + public static List getUserRecommendations(JdbcTemplate sql, int UID, int before) { + if (before > 0) { + return sql.queryForList("SELECT message_id FROM favorites " + + "WHERE user_id=? AND message_id 0) { - stmt = sql.prepareStatement("SELECT message_id FROM favorites WHERE user_id=? AND message_id getUserPhotos(Connection sql, int UID, int privacy, int before) { @@ -1007,58 +966,27 @@ public class MessagesQueries { return replies; } - public static boolean setMessagePopular(Connection sql, int mid, int popular) { - boolean ret = false; - - PreparedStatement stmt = null; - try { - if (popular == -2) { - stmt = sql.prepareStatement("UPDATE messages SET hidden=1 WHERE message_id=?"); - stmt.setInt(1, mid); - } else if (popular == -1) { - stmt = sql.prepareStatement("UPDATE messages SET popular=0 WHERE message_id=?"); - stmt.setInt(1, mid); - } else { - stmt = sql.prepareStatement("UPDATE messages SET popular=? WHERE message_id=?"); - stmt.setInt(1, popular); - stmt.setInt(2, mid); - } - ret = stmt.executeUpdate() > 0; - } catch (SQLException e) { - LOGGER.log(Level.SEVERE, "sql exception", e); - } finally { - Utils.finishSQL(null, stmt); + public static boolean setMessagePopular(JdbcTemplate sql, int mid, int popular) { + boolean ret; + switch (popular) { + case -2: + ret = sql.update("UPDATE messages SET hidden=1 WHERE message_id=?", mid) > 0; + break; + case -1: + ret = sql.update("UPDATE messages SET popular=0 WHERE message_id=?", mid) > 0; + break; + default: + ret = sql.update("UPDATE messages SET popular=? WHERE message_id=?", popular, mid) > 0; + break; } - if (popular == -1) { - try { - stmt = sql.prepareStatement("INSERT INTO top_ignore_messages VALUES (?)"); - stmt.setInt(1, mid); - stmt.executeUpdate(); - } catch (SQLException e) { - LOGGER.log(Level.SEVERE, "sql exception", e); - } finally { - Utils.finishSQL(null, stmt); - } + ret = sql.update("INSERT INTO top_ignore_messages VALUES (?)", mid) > 0; } - return ret; } - public static boolean setMessagePrivacy(Connection sql, int mid) { - boolean ret = false; - - PreparedStatement stmt = null; - try { - stmt = sql.prepareStatement("UPDATE messages SET privacy=1 WHERE message_id=?"); - stmt.setInt(1, mid); - ret = stmt.executeUpdate() > 0; - } catch (SQLException e) { - LOGGER.log(Level.SEVERE, "sql exception", e); - } finally { - Utils.finishSQL(null, stmt); - } - - return ret; + public static boolean setMessagePrivacy(JdbcTemplate sql, int mid) { + return sql.update("UPDATE messages SET privacy=1 WHERE message_id=?", + mid) > 0; } } -- cgit v1.2.3