From d00fd7705cb1a4b085eed7f34df2f1c6d9a69f76 Mon Sep 17 00:00:00 2001 From: Vitaly Takmazov Date: Sat, 24 Oct 2015 18:09:15 +0300 Subject: moving to Gradle --- src/main/java/com/juick/server/TagQueries.java | 189 +++++++++++++++++++++++++ 1 file changed, 189 insertions(+) create mode 100644 src/main/java/com/juick/server/TagQueries.java (limited to 'src/main/java/com/juick/server/TagQueries.java') diff --git a/src/main/java/com/juick/server/TagQueries.java b/src/main/java/com/juick/server/TagQueries.java new file mode 100644 index 00000000..493e8675 --- /dev/null +++ b/src/main/java/com/juick/server/TagQueries.java @@ -0,0 +1,189 @@ +/* + * Juick + * Copyright (C) 2008-2011, Ugnich Anton + * + * This program is free software: you can redistribute it and/or modify + * it under the terms of the GNU Affero General Public License as + * published by the Free Software Foundation, either version 3 of the + * License, or (at your option) any later version. + * + * This program is distributed in the hope that it will be useful, + * but WITHOUT ANY WARRANTY; without even the implied warranty of + * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the + * GNU Affero General Public License for more details. + * + * You should have received a copy of the GNU Affero General Public License + * along with this program. If not, see . + */ +package com.juick.server; + +import java.sql.Connection; +import java.sql.PreparedStatement; +import java.sql.ResultSet; +import java.sql.SQLException; +import java.sql.Statement; +import java.util.ArrayList; + +/** + * + * @author Ugnich Anton + */ +public class TagQueries { + + public static com.juick.Tag getTag(Connection sql, int tid) { + com.juick.Tag ret = null; + + PreparedStatement stmt = null; + ResultSet rs = null; + try { + stmt = sql.prepareStatement("SELECT synonym_id,name FROM tags WHERE tag_id=?"); + stmt.setInt(1, tid); + rs = stmt.executeQuery(); + if (rs.first()) { + ret = new com.juick.Tag(); + ret.TID = tid; + ret.SynonymID = rs.getInt(1); + ret.Name = rs.getString(2); + } + } catch (SQLException e) { + System.err.println(e); + } finally { + Utils.finishSQL(rs, stmt); + } + + 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) { + System.err.println(e); + } finally { + Utils.finishSQL(rs, stmt); + } + + if (ret == null && autoCreate) { + ret = new com.juick.Tag(); + ret.TID = createTag(sql, tag); + ret.Name = tag; + } + + return ret; + } + + public static ArrayList getTags(Connection sql, String[] tags, boolean autoCreate) { + ArrayList ret = new ArrayList(); + + for (int i = 0; i < tags.length; i++) { + if (!tags[i].isEmpty()) { + com.juick.Tag t = getTag(sql, tags[i], autoCreate); + if (t != null) { + ret.add(t); + } + } + } + + return ret; + } + + public static boolean getTagNoIndex(Connection sql, int tag_id) { + boolean ret = false; + + PreparedStatement stmt = null; + ResultSet rs = null; + try { + stmt = sql.prepareStatement("SELECT noindex FROM tags WHERE tag_id=?"); + stmt.setInt(1, tag_id); + rs = stmt.executeQuery(); + if (rs.first()) { + ret = rs.getInt(1) == 1; + } + } catch (SQLException e) { + System.err.println(e); + } finally { + Utils.finishSQL(rs, stmt); + } + + return ret; + } + + public static int createTag(Connection sql, String name) { + int ret = 0; + + 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) { + System.err.println(e); + } finally { + Utils.finishSQL(rs, stmt); + } + + return ret; + } + + public static ArrayList getUserTagsAll(Connection sql, int uid) { + ArrayList tags = new ArrayList(); + + PreparedStatement stmt = null; + ResultSet rs = null; + try { + stmt = sql.prepareStatement("SELECT tags.name,COUNT(messages.message_id) FROM (messages INNER JOIN messages_tags ON (messages.user_id=? AND messages.message_id=messages_tags.message_id)) INNER JOIN tags ON messages_tags.tag_id=tags.tag_id GROUP BY tags.tag_id ORDER BY tags.name ASC"); + stmt.setInt(1, uid); + rs = stmt.executeQuery(); + rs.beforeFirst(); + while (rs.next()) { + com.juick.Tag t = new com.juick.Tag(); + t.Name = rs.getString(1); + t.UsageCnt = rs.getInt(2); + tags.add(t); + } + } catch (SQLException e) { + System.err.println(e); + } finally { + Utils.finishSQL(rs, stmt); + } + return tags; + } + + public static ArrayList getUserBLTags(Connection sql, int uid) { + ArrayList tags = new ArrayList(); + + PreparedStatement stmt = null; + ResultSet rs = null; + try { + stmt = sql.prepareStatement("SELECT tags.name FROM tags INNER JOIN bl_tags ON (bl_tags.user_id=? AND bl_tags.tag_id=tags.tag_id) ORDER BY tags.name"); + stmt.setInt(1, uid); + rs = stmt.executeQuery(); + rs.beforeFirst(); + while (rs.next()) { + tags.add(rs.getString(1)); + } + } catch (SQLException e) { + System.err.println(e); + } finally { + Utils.finishSQL(rs, stmt); + } + return tags; + } +} -- cgit v1.2.3 From c37ed56f884661666b964e47efd23a36be9b24d4 Mon Sep 17 00:00:00 2001 From: Vitaly Takmazov Date: Sun, 10 Jan 2016 16:56:51 +0300 Subject: logger, diamond and arraylist refactoring --- src/main/java/com/juick/server/AdsQueries.java | 6 +- .../java/com/juick/server/CrosspostQueries.java | 8 +- .../java/com/juick/server/MessagesQueries.java | 173 +++++++++++---------- src/main/java/com/juick/server/PMQueries.java | 54 ++++--- src/main/java/com/juick/server/PushQueries.java | 20 ++- src/main/java/com/juick/server/SQLHelpers.java | 25 +-- .../com/juick/server/SubscriptionsQueries.java | 39 +++-- src/main/java/com/juick/server/TagQueries.java | 37 +++-- src/main/java/com/juick/server/UserQueries.java | 79 +++++----- src/main/java/com/juick/server/Utils.java | 6 +- 10 files changed, 244 insertions(+), 203 deletions(-) (limited to 'src/main/java/com/juick/server/TagQueries.java') diff --git a/src/main/java/com/juick/server/AdsQueries.java b/src/main/java/com/juick/server/AdsQueries.java index 0fb4c0a6..ad9b7149 100644 --- a/src/main/java/com/juick/server/AdsQueries.java +++ b/src/main/java/com/juick/server/AdsQueries.java @@ -20,6 +20,8 @@ package com.juick.server; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.SQLException; +import java.util.logging.Level; +import java.util.logging.Logger; /** * @@ -27,6 +29,8 @@ import java.sql.SQLException; */ public class AdsQueries { + private static final Logger logger = Logger.getLogger(AdsQueries.class.getName()); + public static int getAdMID(Connection sql, int uid) { if (uid > 0) { return SQLHelpers.getInt(sql, "SELECT message_id FROM ads_messages WHERE message_id NOT IN (SELECT message_id FROM ads_messages_log WHERE user_id=? AND ts>UNIX_TIMESTAMP()-60*60*24 GROUP BY message_id HAVING COUNT(*)>2) ORDER BY RAND() LIMIT 1", uid, 0); @@ -43,7 +47,7 @@ public class AdsQueries { stmt.setInt(2, mid); stmt.executeUpdate(); } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(null, stmt); } diff --git a/src/main/java/com/juick/server/CrosspostQueries.java b/src/main/java/com/juick/server/CrosspostQueries.java index fd677048..b44b6b59 100644 --- a/src/main/java/com/juick/server/CrosspostQueries.java +++ b/src/main/java/com/juick/server/CrosspostQueries.java @@ -21,6 +21,8 @@ import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; +import java.util.logging.Level; +import java.util.logging.Logger; /** * @@ -28,6 +30,8 @@ import java.sql.SQLException; */ public class CrosspostQueries { + private static final Logger logger = Logger.getLogger(CrosspostQueries.class.getName()); + public static String[] getTwitterTokens(Connection sql, int uid) { String tokens[] = null; @@ -43,7 +47,7 @@ public class CrosspostQueries { tokens[1] = rs.getString(2); } } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } @@ -70,7 +74,7 @@ public class CrosspostQueries { tokens[1] = rs.getString(2); } } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } diff --git a/src/main/java/com/juick/server/MessagesQueries.java b/src/main/java/com/juick/server/MessagesQueries.java index 82fef672..4d5683bd 100644 --- a/src/main/java/com/juick/server/MessagesQueries.java +++ b/src/main/java/com/juick/server/MessagesQueries.java @@ -25,6 +25,9 @@ import java.sql.Statement; import java.sql.Types; import java.util.ArrayList; import java.util.Collections; +import java.util.List; +import java.util.logging.Level; +import java.util.logging.Logger; /** * @@ -32,11 +35,13 @@ import java.util.Collections; */ public class MessagesQueries { - public static int createMessage(Connection sql, int uid, String txt, String attachment, ArrayList tags) { + private static final Logger logger = Logger.getLogger(MessagesQueries.class.getName()); + + public static int createMessage(Connection sql, int uid, String txt, String attachment, List tags) { int mid = 0; PreparedStatement stmt = null; - ResultSet rs = null; + ResultSet rs; try { stmt = sql.prepareStatement("INSERT INTO messages(user_id,attach) VALUES (?,?)", Statement.RETURN_GENERATED_KEYS); stmt.setInt(1, uid); @@ -51,7 +56,7 @@ public class MessagesQueries { mid = rs.getInt(1); } } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(null, stmt); } @@ -73,7 +78,7 @@ public class MessagesQueries { stmt = sql.prepareStatement("INSERT INTO messages_tags(message_id,tag_id) VALUES " + tagsIDs); stmt.executeUpdate(); } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(null, stmt); } @@ -81,7 +86,7 @@ public class MessagesQueries { try { stmt = sql.prepareStatement("INSERT INTO messages_txt(message_id,tags,txt) VALUES (?,?,?)"); stmt.setInt(1, mid); - if (tagsNames == "") { + if (tagsNames.isEmpty()) { stmt.setNull(2, Types.VARCHAR); } else { stmt.setString(2, tagsNames); @@ -89,7 +94,7 @@ public class MessagesQueries { stmt.setString(3, txt); stmt.executeUpdate(); } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(null, stmt); } @@ -118,7 +123,7 @@ public class MessagesQueries { ridnew = 0; } } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(null, stmt); } @@ -166,7 +171,7 @@ public class MessagesQueries { privacy = rs.getInt(2); } } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } @@ -189,7 +194,7 @@ public class MessagesQueries { ret = rs.getInt(1) == 1; } } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } @@ -210,7 +215,7 @@ public class MessagesQueries { ret = rs.getInt(1) == 1; } } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } @@ -255,7 +260,7 @@ public class MessagesQueries { msg.Hidden = rs.getBoolean(13); } } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } @@ -276,7 +281,7 @@ public class MessagesQueries { msg.Text = rs.getString(3); } } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } @@ -307,7 +312,7 @@ public class MessagesQueries { msg.Text = rs.getString(6); } } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } @@ -330,15 +335,15 @@ public class MessagesQueries { user.UName = rs.getString(2); } } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } return user; } - public static ArrayList getMessageTags(Connection sql, int mid) { - ArrayList tags = new ArrayList(); + public static List getMessageTags(Connection sql, int mid) { + List tags = new ArrayList<>(); PreparedStatement stmt = null; ResultSet rs = null; @@ -356,7 +361,7 @@ public class MessagesQueries { tags.add(t); } } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } @@ -364,8 +369,8 @@ public class MessagesQueries { return tags; } - public static ArrayList getMessageTagsIDs(Connection sql, int mid) { - ArrayList tids = new ArrayList(); + public static List getMessageTagsIDs(Connection sql, int mid) { + List tids = new ArrayList<>(); PreparedStatement stmt = null; ResultSet rs = null; @@ -378,7 +383,7 @@ public class MessagesQueries { tids.add(rs.getInt(1)); } } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } @@ -387,7 +392,7 @@ public class MessagesQueries { } public static ArrayList getMessageRecommendations(Connection sql, int mid) { - ArrayList users = new ArrayList(); + ArrayList users = new ArrayList<>(); PreparedStatement stmt = null; ResultSet rs = null; @@ -400,7 +405,7 @@ public class MessagesQueries { users.add(rs.getString(1)); } } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } @@ -408,8 +413,8 @@ public class MessagesQueries { return users; } - public static ArrayList getAll(Connection sql, int visitor_uid, int before) { - ArrayList mids = new ArrayList(20); + public static List getAll(Connection sql, int visitor_uid, int before) { + List mids = new ArrayList<>(20); PreparedStatement stmt = null; ResultSet rs = null; @@ -448,15 +453,15 @@ public class MessagesQueries { mids.add(rs.getInt(1)); } } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } return mids; } - public static ArrayList getTag(Connection sql, int tid, int visitor_uid, int before, int cnt) { - ArrayList mids = new ArrayList(20); + public static List getTag(Connection sql, int tid, int visitor_uid, int before, int cnt) { + List mids = new ArrayList<>(20); PreparedStatement stmt = null; ResultSet rs = null; @@ -483,15 +488,15 @@ public class MessagesQueries { mids.add(rs.getInt(1)); } } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } return mids; } - public static ArrayList getTags(Connection sql, String tids, int visitor_uid, int before, int cnt) { - ArrayList mids = new ArrayList(20); + public static List getTags(Connection sql, String tids, int visitor_uid, int before, int cnt) { + List mids = new ArrayList<>(20); PreparedStatement stmt = null; ResultSet rs = null; @@ -512,15 +517,15 @@ public class MessagesQueries { mids.add(rs.getInt(1)); } } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } return mids; } - public static ArrayList getPlace(Connection sql, int place_id, int visitor_uid, int before) { - ArrayList mids = new ArrayList(20); + public static List getPlace(Connection sql, int place_id, int visitor_uid, int before) { + List mids = new ArrayList<>(20); PreparedStatement stmt = null; ResultSet rs = null; @@ -541,15 +546,15 @@ public class MessagesQueries { mids.add(rs.getInt(1)); } } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } return mids; } - public static ArrayList getMyFeed(Connection sql, int uid, int before) { - ArrayList mids = new ArrayList(40); + public static List getMyFeed(Connection sql, int uid, int before) { + List mids = new ArrayList<>(40); PreparedStatement stmt = null; ResultSet rs = null; try { @@ -569,7 +574,7 @@ public class MessagesQueries { mids.add(rs.getInt(1)); } } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } @@ -589,7 +594,7 @@ public class MessagesQueries { mids.add(rs.getInt(1)); } } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } @@ -603,8 +608,8 @@ public class MessagesQueries { return mids; } - public static ArrayList getPrivate(Connection sql, int uid, int before) { - ArrayList mids = new ArrayList(20); + public static List getPrivate(Connection sql, int uid, int before) { + List mids = new ArrayList<>(20); PreparedStatement stmt = null; ResultSet rs = null; @@ -623,15 +628,15 @@ public class MessagesQueries { mids.add(rs.getInt(1)); } } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } return mids; } - public static ArrayList getDiscussions(Connection sql, int uid, int before) { - ArrayList mids = new ArrayList(20); + public static List getDiscussions(Connection sql, int uid, int before) { + List mids = new ArrayList<>(20); PreparedStatement stmt = null; ResultSet rs = null; @@ -650,7 +655,7 @@ public class MessagesQueries { mids.add(rs.getInt(1)); } } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } @@ -658,8 +663,8 @@ public class MessagesQueries { return mids; } - public static ArrayList getRecommended(Connection sql, int uid, int before) { - ArrayList mids = new ArrayList(20); + public static List getRecommended(Connection sql, int uid, int before) { + List mids = new ArrayList<>(20); PreparedStatement stmt = null; ResultSet rs = null; @@ -678,7 +683,7 @@ public class MessagesQueries { mids.add(rs.getInt(1)); } } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } @@ -687,8 +692,8 @@ public class MessagesQueries { return mids; } - public static ArrayList getPopular(Connection sql, int before) { - ArrayList mids = new ArrayList(20); + public static List getPopular(Connection sql, int before) { + List mids = new ArrayList<>(20); PreparedStatement stmt = null; ResultSet rs = null; @@ -705,15 +710,15 @@ public class MessagesQueries { mids.add(rs.getInt(1)); } } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } return mids; } - public static ArrayList getPhotos(Connection sql, int visitor_uid, int before) { - ArrayList mids = new ArrayList(20); + public static List getPhotos(Connection sql, int visitor_uid, int before) { + List mids = new ArrayList<>(20); PreparedStatement stmt = null; ResultSet rs = null; @@ -732,15 +737,15 @@ public class MessagesQueries { mids.add(rs.getInt(1)); } } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } return mids; } - public static ArrayList getSearch(Connection sql, Connection sqlSearch, String search, int before) { - ArrayList mids0 = new ArrayList(20); + public static List getSearch(Connection sql, Connection sqlSearch, String search, int before) { + List mids0 = new ArrayList<>(20); PreparedStatement stmt = null; ResultSet rs = null; @@ -760,12 +765,12 @@ public class MessagesQueries { mids0.add(rs.getInt(1)); } } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } - ArrayList mids = new ArrayList(20); + List mids = new ArrayList<>(20); if (mids0.size() > 0) { try { 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"); @@ -775,7 +780,7 @@ public class MessagesQueries { mids.add(rs.getInt(1)); } } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } @@ -784,8 +789,8 @@ public class MessagesQueries { return mids; } - public static ArrayList getUserBlog(Connection sql, int UID, int privacy, int before) { - ArrayList mids = new ArrayList(20); + public static List getUserBlog(Connection sql, int UID, int privacy, int before) { + List mids = new ArrayList<>(20); PreparedStatement stmt = null; ResultSet rs = null; @@ -804,15 +809,15 @@ public class MessagesQueries { mids.add(rs.getInt(1)); } } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } return mids; } - public static ArrayList getUserTag(Connection sql, int UID, int TID, int privacy, int before) { - ArrayList mids = new ArrayList(20); + public static List getUserTag(Connection sql, int UID, int TID, int privacy, int before) { + List mids = new ArrayList<>(20); PreparedStatement stmt = null; ResultSet rs = null; @@ -833,15 +838,15 @@ public class MessagesQueries { mids.add(rs.getInt(1)); } } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } return mids; } - public static ArrayList getUserRecommendations(Connection sql, int UID, int before) { - ArrayList mids = new ArrayList(20); + public static List getUserRecommendations(Connection sql, int UID, int before) { + List mids = new ArrayList<>(20); PreparedStatement stmt = null; ResultSet rs = null; @@ -860,15 +865,15 @@ public class MessagesQueries { mids.add(rs.getInt(1)); } } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } return mids; } - public static ArrayList getUserPhotos(Connection sql, int UID, int privacy, int before) { - ArrayList mids = new ArrayList(20); + public static List getUserPhotos(Connection sql, int UID, int privacy, int before) { + List mids = new ArrayList<>(20); PreparedStatement stmt = null; ResultSet rs = null; @@ -887,15 +892,15 @@ public class MessagesQueries { mids.add(rs.getInt(1)); } } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } return mids; } - public static ArrayList getUserSearch(Connection sql, Connection sqlSearch, int UID, String search, int privacy, int before) { - ArrayList mids0 = new ArrayList(20); + public static List getUserSearch(Connection sql, Connection sqlSearch, int UID, String search, int privacy, int before) { + List mids0 = new ArrayList<>(20); PreparedStatement stmt = null; ResultSet rs = null; @@ -916,12 +921,12 @@ public class MessagesQueries { mids0.add(rs.getInt(1)); } } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } - ArrayList mids = new ArrayList(20); + List mids = new ArrayList<>(20); if (mids0.size() > 0) { try { stmt = sql.prepareStatement("SELECT message_id FROM messages WHERE message_id IN (" + Utils.convertArrayInt2String(mids0) + ") AND privacy>=" + privacy + " ORDER BY message_id DESC"); @@ -931,7 +936,7 @@ public class MessagesQueries { mids.add(rs.getInt(1)); } } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } @@ -940,8 +945,8 @@ public class MessagesQueries { return mids; } - public static ArrayList getMessages(Connection sql, ArrayList mids) { - ArrayList msgs = new ArrayList(20); + public static List getMessages(Connection sql, List mids) { + List msgs = new ArrayList<>(20); PreparedStatement stmt = null; ResultSet rs = null; @@ -978,7 +983,7 @@ public class MessagesQueries { msgs.add(msg); } } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } @@ -986,8 +991,8 @@ public class MessagesQueries { return msgs; } - public static ArrayList getReplies(Connection sql, int mid) { - ArrayList replies = new ArrayList(); + public static List getReplies(Connection sql, int mid) { + List replies = new ArrayList<>(); PreparedStatement stmt = null; ResultSet rs = null; @@ -1013,7 +1018,7 @@ public class MessagesQueries { replies.add(msg); } } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } @@ -1039,7 +1044,7 @@ public class MessagesQueries { } ret = stmt.executeUpdate() > 0; } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(null, stmt); } @@ -1050,7 +1055,7 @@ public class MessagesQueries { stmt.setInt(1, mid); stmt.executeUpdate(); } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(null, stmt); } @@ -1068,7 +1073,7 @@ public class MessagesQueries { stmt.setInt(1, mid); ret = stmt.executeUpdate() > 0; } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(null, stmt); } diff --git a/src/main/java/com/juick/server/PMQueries.java b/src/main/java/com/juick/server/PMQueries.java index 56b91abf..c4b4fa2d 100644 --- a/src/main/java/com/juick/server/PMQueries.java +++ b/src/main/java/com/juick/server/PMQueries.java @@ -17,11 +17,16 @@ */ package com.juick.server; +import com.juick.User; + import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; +import java.util.List; +import java.util.logging.Level; +import java.util.logging.Logger; /** * @@ -29,6 +34,8 @@ import java.util.ArrayList; */ public class PMQueries { + 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; @@ -39,7 +46,7 @@ public class PMQueries { stmt.setString(3, body); ret = stmt.executeUpdate() > 0; } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(null, stmt); } @@ -52,7 +59,7 @@ public class PMQueries { stmt2.setInt(2, uid_to); stmt2.executeUpdate(); } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(null, stmt2); } @@ -70,7 +77,7 @@ public class PMQueries { stmt.setString(2, jid); ret = stmt.executeUpdate() > 0; } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(null, stmt); } @@ -86,7 +93,7 @@ public class PMQueries { stmt.setString(2, jid); ret = stmt.executeUpdate() > 0; } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(null, stmt); } @@ -107,7 +114,7 @@ public class PMQueries { ret = true; } } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } @@ -128,15 +135,15 @@ public class PMQueries { ret = rs.getString(1); } } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } return ret; } - public static ArrayList getPMLastConversationsUsers(Connection sql, int uid, int cnt) { - ArrayList users = new ArrayList(); + public static List getPMLastConversationsUsers(Connection sql, int uid, int cnt) { + List users = new ArrayList<>(); PreparedStatement stmt = null; ResultSet rs = null; @@ -152,7 +159,7 @@ public class PMQueries { users.add(u); } } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } @@ -177,7 +184,7 @@ public class PMQueries { } } } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } @@ -190,18 +197,17 @@ public class PMQueries { return users; } - public static boolean haveUserInArray(ArrayList arr, int uid) { - int s = arr.size(); - for (int i = 0; i < s; i++) { - if (arr.get(i).UID == uid) { + public static boolean haveUserInArray(List arr, int uid) { + for (User user : arr) { + if (user.UID == uid) { return true; } } return false; } - public static ArrayList getPMMessages(Connection sql, int uid, int uid_to) { - ArrayList msgs = new ArrayList(20); + public static List getPMMessages(Connection sql, int uid, int uid_to) { + List msgs = new ArrayList<>(20); PreparedStatement stmt = null; ResultSet rs = null; @@ -223,7 +229,7 @@ public class PMQueries { msgs.add(0, msg); } } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } @@ -235,7 +241,7 @@ public class PMQueries { stmt2.setInt(2, uid_to); stmt2.executeUpdate(); } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(null, stmt2); } @@ -243,8 +249,8 @@ public class PMQueries { return msgs; } - public static ArrayList getLastPMInbox(Connection sql, int uid) { - ArrayList msgs = new ArrayList(20); + public static List getLastPMInbox(Connection sql, int uid) { + List msgs = new ArrayList<>(20); PreparedStatement stmt = null; ResultSet rs = null; @@ -264,7 +270,7 @@ public class PMQueries { msgs.add(0, msg); } } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } @@ -272,8 +278,8 @@ public class PMQueries { return msgs; } - public static ArrayList getLastPMSent(Connection sql, int uid) { - ArrayList msgs = new ArrayList(20); + public static List getLastPMSent(Connection sql, int uid) { + List msgs = new ArrayList<>(20); PreparedStatement stmt = null; ResultSet rs = null; @@ -293,7 +299,7 @@ public class PMQueries { msgs.add(0, msg); } } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } diff --git a/src/main/java/com/juick/server/PushQueries.java b/src/main/java/com/juick/server/PushQueries.java index bf7505ca..c17972b0 100644 --- a/src/main/java/com/juick/server/PushQueries.java +++ b/src/main/java/com/juick/server/PushQueries.java @@ -10,6 +10,8 @@ import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import java.util.List; +import java.util.logging.Level; +import java.util.logging.Logger; /** * @@ -17,12 +19,14 @@ import java.util.List; */ public class PushQueries { + private static final Logger logger = Logger.getLogger(PushQueries.class.getName()); + public static String getAndroidRegID(Connection sql, int uid) { return SQLHelpers.getString(sql, "SELECT regid FROM android WHERE user_id=?", uid); } - public static ArrayList getAndroidSubscribers(Connection sql, int uid) { - ArrayList regids = new ArrayList(); + public static List getAndroidSubscribers(Connection sql, int uid) { + List regids = new ArrayList<>(); PreparedStatement stmt = null; ResultSet rs = null; @@ -35,7 +39,7 @@ public class PushQueries { regids.add(rs.getString(1)); } } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } @@ -46,8 +50,8 @@ public class PushQueries { return SQLHelpers.getString(sql, "SELECT url FROM winphone WHERE user_id=?", uid); } - public static ArrayList getWinPhoneSubscribers(Connection sql, int uid) { - ArrayList urls = new ArrayList(); + public static List getWinPhoneSubscribers(Connection sql, int uid) { + List urls = new ArrayList<>(); PreparedStatement stmt = null; ResultSet rs = null; @@ -60,7 +64,7 @@ public class PushQueries { urls.add(rs.getString(1)); } } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } @@ -72,7 +76,7 @@ public class PushQueries { } public static List getAPNSSubscribers(Connection sql, int uid) { - List urls = new ArrayList(); + List urls = new ArrayList<>(); PreparedStatement stmt = null; ResultSet rs = null; @@ -85,7 +89,7 @@ public class PushQueries { urls.add(rs.getString(1)); } } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } diff --git a/src/main/java/com/juick/server/SQLHelpers.java b/src/main/java/com/juick/server/SQLHelpers.java index f5569993..13ebe98a 100644 --- a/src/main/java/com/juick/server/SQLHelpers.java +++ b/src/main/java/com/juick/server/SQLHelpers.java @@ -9,6 +9,9 @@ import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; +import java.util.List; +import java.util.logging.Level; +import java.util.logging.Logger; /** * @@ -16,6 +19,8 @@ import java.util.ArrayList; */ public class SQLHelpers { + private static final Logger logger = Logger.getLogger(SQLHelpers.class.getName()); + public static int execute(Connection sql, String query) { int ret = -1; PreparedStatement stmt = null; @@ -23,7 +28,7 @@ public class SQLHelpers { stmt = sql.prepareStatement(query); ret = stmt.executeUpdate(); } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(null, stmt); } @@ -38,7 +43,7 @@ public class SQLHelpers { stmt.setInt(1, param); ret = stmt.executeUpdate(); } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(null, stmt); } @@ -56,7 +61,7 @@ public class SQLHelpers { ret = rs.getInt(1); } } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } @@ -75,7 +80,7 @@ public class SQLHelpers { ret = rs.getInt(1); } } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } @@ -94,7 +99,7 @@ public class SQLHelpers { ret = rs.getInt(1); } } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } @@ -113,7 +118,7 @@ public class SQLHelpers { ret = rs.getString(1); } } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } @@ -132,15 +137,15 @@ public class SQLHelpers { ret = rs.getString(1); } } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } return ret; } - public static ArrayList getArrayInteger(Connection sql, String query, int param) { - ArrayList ret = new ArrayList(); + public static List getArrayInteger(Connection sql, String query, int param) { + List ret = new ArrayList<>(); PreparedStatement stmt = null; ResultSet rs = null; @@ -153,7 +158,7 @@ public class SQLHelpers { ret.add(rs.getInt(1)); } } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } diff --git a/src/main/java/com/juick/server/SubscriptionsQueries.java b/src/main/java/com/juick/server/SubscriptionsQueries.java index d0f5f308..637c2bcf 100644 --- a/src/main/java/com/juick/server/SubscriptionsQueries.java +++ b/src/main/java/com/juick/server/SubscriptionsQueries.java @@ -9,6 +9,9 @@ import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; +import java.util.List; +import java.util.logging.Level; +import java.util.logging.Logger; /** * @@ -16,8 +19,10 @@ import java.util.ArrayList; */ public class SubscriptionsQueries { - public static ArrayList getJIDSubscribedToUser(Connection sql, int uid, boolean friendsonly) { - ArrayList jids = new ArrayList(); + private static final Logger logger = Logger.getLogger(SubscriptionsQueries.class.getName()); + + public static List getJIDSubscribedToUser(Connection sql, int uid, boolean friendsonly) { + List jids = new ArrayList<>(); PreparedStatement stmt = null; ResultSet rs = null; @@ -36,27 +41,27 @@ public class SubscriptionsQueries { jids.add(rs.getString(1)); } } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } return jids; } - public static ArrayList getJIDSubscribedToUserAndTags(Connection sql, int uid, int mid) { - ArrayList jids = new ArrayList(); + public static List getJIDSubscribedToUserAndTags(Connection sql, int uid, int mid) { + List jids = new ArrayList<>(); PreparedStatement stmt = null; ResultSet rs = null; String tbl = "subscr_jids_" + mid; - ArrayList tags = MessagesQueries.getMessageTagsIDs(sql, mid); + List tags = MessagesQueries.getMessageTagsIDs(sql, mid); try { stmt = sql.prepareStatement("CREATE TEMPORARY TABLE " + tbl + "(user_id INT UNSIGNED NOT NULL) ENGINE=MEMORY"); stmt.executeUpdate(); } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(null, stmt); } @@ -69,7 +74,7 @@ public class SubscriptionsQueries { stmt = sql.prepareStatement(query); stmt.executeUpdate(); } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(null, stmt); } @@ -87,7 +92,7 @@ public class SubscriptionsQueries { jids.add(rs.getString(1)); } } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } @@ -96,7 +101,7 @@ public class SubscriptionsQueries { stmt = sql.prepareStatement("DROP TABLE " + tbl); stmt.executeUpdate(); } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(null, stmt); } @@ -104,8 +109,8 @@ public class SubscriptionsQueries { return jids; } - public static ArrayList getJIDSubscribedToComments(Connection sql, int mid, int ignore_uid) { - ArrayList jids = new ArrayList(); + public static List getJIDSubscribedToComments(Connection sql, int mid, int ignore_uid) { + List jids = new ArrayList<>(); PreparedStatement stmt = null; ResultSet rs = null; @@ -119,17 +124,17 @@ public class SubscriptionsQueries { jids.add(rs.getString(1)); } } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } return jids; } - public static ArrayList getJIDSubscribedToUserRecommendations(Connection sql, int uid, int mid, int muid) { - ArrayList jids = new ArrayList(); + public static List getJIDSubscribedToUserRecommendations(Connection sql, int uid, int mid, int muid) { + List jids = new ArrayList<>(); - ArrayList tags = MessagesQueries.getMessageTagsIDs(sql, mid); + List tags = MessagesQueries.getMessageTagsIDs(sql, mid); PreparedStatement stmt = null; ResultSet rs = null; @@ -154,7 +159,7 @@ public class SubscriptionsQueries { jids.add(rs.getString(1)); } } catch (SQLException e) { - System.err.println(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 493e8675..1c5e27d1 100644 --- a/src/main/java/com/juick/server/TagQueries.java +++ b/src/main/java/com/juick/server/TagQueries.java @@ -17,12 +17,17 @@ */ package com.juick.server; +import com.juick.Tag; + import java.sql.Connection; 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.logging.Level; +import java.util.logging.Logger; /** * @@ -30,6 +35,8 @@ import java.util.ArrayList; */ public class TagQueries { + private static final Logger logger = Logger.getLogger(TagQueries.class.getName()); + public static com.juick.Tag getTag(Connection sql, int tid) { com.juick.Tag ret = null; @@ -46,7 +53,7 @@ public class TagQueries { ret.Name = rs.getString(2); } } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } @@ -70,7 +77,7 @@ public class TagQueries { ret.Name = rs.getString(3); } } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } @@ -84,12 +91,12 @@ public class TagQueries { return ret; } - public static ArrayList getTags(Connection sql, String[] tags, boolean autoCreate) { - ArrayList ret = new ArrayList(); + public static List getTags(Connection sql, String[] tags, boolean autoCreate) { + List ret = new ArrayList<>(); - for (int i = 0; i < tags.length; i++) { - if (!tags[i].isEmpty()) { - com.juick.Tag t = getTag(sql, tags[i], autoCreate); + for (String tag : tags) { + if (!tag.isEmpty()) { + Tag t = getTag(sql, tag, autoCreate); if (t != null) { ret.add(t); } @@ -112,7 +119,7 @@ public class TagQueries { ret = rs.getInt(1) == 1; } } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } @@ -134,7 +141,7 @@ public class TagQueries { ret = rs.getInt(1); } } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } @@ -142,8 +149,8 @@ public class TagQueries { return ret; } - public static ArrayList getUserTagsAll(Connection sql, int uid) { - ArrayList tags = new ArrayList(); + public static List getUserTagsAll(Connection sql, int uid) { + List tags = new ArrayList<>(); PreparedStatement stmt = null; ResultSet rs = null; @@ -159,15 +166,15 @@ public class TagQueries { tags.add(t); } } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } return tags; } - public static ArrayList getUserBLTags(Connection sql, int uid) { - ArrayList tags = new ArrayList(); + public static List getUserBLTags(Connection sql, int uid) { + List tags = new ArrayList<>(); PreparedStatement stmt = null; ResultSet rs = null; @@ -180,7 +187,7 @@ public class TagQueries { tags.add(rs.getString(1)); } } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } diff --git a/src/main/java/com/juick/server/UserQueries.java b/src/main/java/com/juick/server/UserQueries.java index 767a68d2..98573bfb 100644 --- a/src/main/java/com/juick/server/UserQueries.java +++ b/src/main/java/com/juick/server/UserQueries.java @@ -25,6 +25,7 @@ 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.logging.Level; @@ -52,7 +53,7 @@ public class UserQueries { stmt.setString(2, hash); stmt.executeUpdate(); } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(null, stmt); } @@ -76,7 +77,7 @@ public class UserQueries { uid = rs.getInt(1); } } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } @@ -103,7 +104,7 @@ public class UserQueries { user.Banned = rs.getBoolean(2); } } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } @@ -126,7 +127,7 @@ public class UserQueries { user.Banned = rs.getBoolean(3); } } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } @@ -149,15 +150,15 @@ public class UserQueries { user.JID = jid; } } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } return user; } - public static ArrayList getUsersByName(Connection sql, ArrayList unames) { - ArrayList users = new ArrayList(); + public static List getUsersByName(Connection sql, List unames) { + List users = new ArrayList<>(); PreparedStatement stmt = null; ResultSet rs = null; @@ -172,15 +173,15 @@ public class UserQueries { users.add(user); } } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } return users; } - public static ArrayList getUsersByID(Connection sql, ArrayList uids) { - ArrayList users = new ArrayList(); + public static List getUsersByID(Connection sql, List uids) { + List users = new ArrayList<>(); PreparedStatement stmt = null; ResultSet rs = null; @@ -195,7 +196,7 @@ public class UserQueries { users.add(u); } } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } @@ -203,7 +204,7 @@ public class UserQueries { return users; } - public static boolean fillUsersByID(Connection sql, ArrayList users) { + public static boolean fillUsersByID(Connection sql, List users) { boolean ret = false; String uids = ""; @@ -231,7 +232,7 @@ public class UserQueries { } } } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } @@ -239,8 +240,8 @@ public class UserQueries { return ret; } - public static ArrayList getUsersByJID(Connection sql, ArrayList jids) { - ArrayList users = new ArrayList(); + public static List getUsersByJID(Connection sql, List jids) { + List users = new ArrayList<>(); PreparedStatement stmt = null; ResultSet rs = null; @@ -256,7 +257,7 @@ public class UserQueries { users.add(user); } } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } @@ -296,7 +297,7 @@ public class UserQueries { user.AuthHash = hash; } } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } @@ -315,7 +316,7 @@ public class UserQueries { stmt.setString(2, hash); stmt.executeUpdate(); } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(null, stmt); } @@ -353,7 +354,7 @@ public class UserQueries { } } } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } @@ -373,7 +374,7 @@ public class UserQueries { ret = rs.getInt(1); } } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } @@ -388,7 +389,7 @@ public class UserQueries { stmt.setInt(2, uid); stmt.executeUpdate(); } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(null, stmt); } @@ -407,7 +408,7 @@ public class UserQueries { ret = rs.getInt(1) < 3600; } } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } @@ -428,7 +429,7 @@ public class UserQueries { ret = rs.getInt(1) == 1; } } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } @@ -449,7 +450,7 @@ public class UserQueries { ret = rs.getInt(1) == 1; } } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } @@ -472,15 +473,15 @@ public class UserQueries { ret = rs.getInt(1) == 1; } } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } return ret; } - public static ArrayList checkBL(Connection sql, int visitor, ArrayList uids) { - ArrayList ret = new ArrayList(); + public static List checkBL(Connection sql, int visitor, List uids) { + List ret = new ArrayList<>(); PreparedStatement stmt = null; ResultSet rs = null; @@ -493,7 +494,7 @@ public class UserQueries { ret.add(rs.getInt(1)); } } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } @@ -515,19 +516,19 @@ public class UserQueries { ret = rs.getInt(1) == 1; } } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } return ret; } - public static ArrayList getUserRead(Connection sql, int uid) { + public static List getUserRead(Connection sql, int uid) { return SQLHelpers.getArrayInteger(sql, "SELECT user_id FROM subscr_users WHERE suser_id=?", uid); } - public static ArrayList getUserReadLeastPopular(Connection sql, int uid, int cnt) { - ArrayList users = new ArrayList(cnt); + public static List getUserReadLeastPopular(Connection sql, int uid, int cnt) { + List users = new ArrayList<>(cnt); PreparedStatement stmt = null; ResultSet rs = null; @@ -544,7 +545,7 @@ public class UserQueries { users.add(u); } } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } @@ -552,12 +553,12 @@ public class UserQueries { return users; } - public static ArrayList getUserReaders(Connection sql, int uid) { + public static List getUserReaders(Connection sql, int uid) { return SQLHelpers.getArrayInteger(sql, "SELECT suser_id FROM subscr_users WHERE user_id=?", uid); } - public static ArrayList getUserBLUsers(Connection sql, int uid) { - ArrayList users = new ArrayList(); + public static List getUserBLUsers(Connection sql, int uid) { + List users = new ArrayList<>(); PreparedStatement stmt = null; ResultSet rs = null; @@ -573,7 +574,7 @@ public class UserQueries { users.add(u); } } catch (SQLException e) { - System.err.println(e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } @@ -603,7 +604,7 @@ public class UserQueries { return stmt2.execute(); } } catch (SQLException e) { - logger.throwing(UserQueries.class.getName(), "linkTwitterAccount", e); + logger.log(Level.SEVERE, "sql exception", e); } return false; } @@ -642,7 +643,7 @@ public class UserQueries { preparedStatement.setString(3, JID); return preparedStatement.executeUpdate() >= 0; } catch (SQLException e) { - logger.log(Level.SEVERE, "sql error", e); + logger.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(null, preparedStatement); } diff --git a/src/main/java/com/juick/server/Utils.java b/src/main/java/com/juick/server/Utils.java index f97797d7..1968150e 100644 --- a/src/main/java/com/juick/server/Utils.java +++ b/src/main/java/com/juick/server/Utils.java @@ -21,7 +21,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; /** * @@ -29,7 +29,7 @@ import java.util.ArrayList; */ public class Utils { - public static String convertArrayInt2String(ArrayList mids) { + public static String convertArrayInt2String(List mids) { String q = ""; for (int i = 0; i < mids.size(); i++) { if (i > 0) { @@ -40,7 +40,7 @@ public class Utils { return q; } - public static String convertArrayString2String(ArrayList unames) { + public static String convertArrayString2String(List unames) { String q = ""; for (int i = 0; i < unames.size(); i++) { if (i > 0) { -- cgit v1.2.3 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 +++++++-------- src/main/java/com/juick/server/PMQueries.java | 204 ++++++--------------- src/main/java/com/juick/server/TagQueries.java | 63 +++---- src/main/java/com/juick/server/UserQueries.java | 129 +++++-------- 4 files changed, 191 insertions(+), 356 deletions(-) (limited to 'src/main/java/com/juick/server/TagQueries.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) { 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 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 getPMLastConversationsUsers(Connection sql, int uid, int cnt) { - List 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 getPMLastConversationsUsers(JdbcTemplate sql, int uid, int cnt) { + List 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 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 getPMMessages(Connection sql, int uid, int uid_to) { - List 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 getPMMessages(JdbcTemplate sql, int uid, int uid_to) { + List 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 getTags(Connection sql, String[] tags, boolean autoCreate) { + public static List getTags(JdbcTemplate sql, String[] tags, boolean autoCreate) { List 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 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 { @Override @@ -115,7 +111,7 @@ public class UserQueries { uids.toArray()); } - public static boolean fillUsersByID(Connection sql, List users) { + public static boolean fillUsersByID(JdbcTemplate sql, List 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 getUsersByJID(Connection sql, List jids) { - List 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 getUsersByJID(JdbcTemplate sql, List 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 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 checkBL(Connection sql, int visitor, List 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 getUserRead(Connection sql, int uid) { - return SQLHelpers.getArrayInteger(sql, "SELECT user_id FROM subscr_users WHERE suser_id=?", uid); + public static List getUserRead(JdbcTemplate sql, int uid) { + return sql.queryForList("SELECT user_id FROM subscr_users WHERE suser_id=?", Integer.class, uid); } public static List 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 getUserReaders(Connection sql, int uid) { - return SQLHelpers.getArrayInteger(sql, "SELECT suser_id FROM subscr_users WHERE user_id=?", uid); + public static List getUserReaders(JdbcTemplate sql, int uid) { + return sql.queryForList("SELECT suser_id FROM subscr_users WHERE user_id=?", Integer.class, uid); } public static List 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; } -- cgit v1.2.3 From 1ef890d21db2ed9aeefc37b71ff82d0f59df8516 Mon Sep 17 00:00:00 2001 From: Vitaly Takmazov Date: Fri, 15 Jan 2016 13:14:11 +0300 Subject: housekeeping --- .../java/com/juick/server/MessagesQueries.java | 74 +++++++++++----------- src/main/java/com/juick/server/PMQueries.java | 1 - src/main/java/com/juick/server/TagQueries.java | 11 ++-- 3 files changed, 41 insertions(+), 45 deletions(-) (limited to 'src/main/java/com/juick/server/TagQueries.java') diff --git a/src/main/java/com/juick/server/MessagesQueries.java b/src/main/java/com/juick/server/MessagesQueries.java index a8605d3a..cff8caff 100644 --- a/src/main/java/com/juick/server/MessagesQueries.java +++ b/src/main/java/com/juick/server/MessagesQueries.java @@ -17,7 +17,6 @@ */ package com.juick.server; -import com.juick.Message; import org.springframework.jdbc.core.JdbcTemplate; import java.sql.Connection; @@ -31,7 +30,6 @@ import java.util.Collections; import java.util.List; import java.util.logging.Level; import java.util.logging.Logger; -import org.springframework.jdbc.core.RowMapper; /** * @@ -39,7 +37,7 @@ import org.springframework.jdbc.core.RowMapper; */ public class MessagesQueries { - private static final Logger logger = Logger.getLogger(MessagesQueries.class.getName()); + private static final Logger LOGGER = Logger.getLogger(MessagesQueries.class.getName()); public static int createMessage(Connection sql, int uid, String txt, String attachment, List tags) { int mid = 0; @@ -60,7 +58,7 @@ public class MessagesQueries { mid = rs.getInt(1); } } catch (SQLException e) { - logger.log(Level.SEVERE, "sql exception", e); + LOGGER.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(null, stmt); } @@ -82,7 +80,7 @@ public class MessagesQueries { stmt = sql.prepareStatement("INSERT INTO messages_tags(message_id,tag_id) VALUES " + tagsIDs); stmt.executeUpdate(); } catch (SQLException e) { - logger.log(Level.SEVERE, "sql exception", e); + LOGGER.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(null, stmt); } @@ -98,7 +96,7 @@ public class MessagesQueries { stmt.setString(3, txt); stmt.executeUpdate(); } catch (SQLException e) { - logger.log(Level.SEVERE, "sql exception", e); + LOGGER.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(null, stmt); } @@ -127,7 +125,7 @@ public class MessagesQueries { ridnew = 0; } } catch (SQLException e) { - logger.log(Level.SEVERE, "sql exception", e); + LOGGER.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(null, stmt); } @@ -175,7 +173,7 @@ public class MessagesQueries { privacy = rs.getInt(2); } } catch (SQLException e) { - logger.log(Level.SEVERE, "sql exception", e); + LOGGER.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } @@ -198,7 +196,7 @@ public class MessagesQueries { 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); } @@ -219,7 +217,7 @@ public class MessagesQueries { 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); } @@ -264,7 +262,7 @@ public class MessagesQueries { msg.Hidden = rs.getBoolean(13); } } catch (SQLException e) { - logger.log(Level.SEVERE, "sql exception", e); + LOGGER.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } @@ -285,7 +283,7 @@ public class MessagesQueries { msg.Text = rs.getString(3); } } catch (SQLException e) { - logger.log(Level.SEVERE, "sql exception", e); + LOGGER.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } @@ -316,7 +314,7 @@ public class MessagesQueries { msg.Text = rs.getString(6); } } catch (SQLException e) { - logger.log(Level.SEVERE, "sql exception", e); + LOGGER.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } @@ -339,7 +337,7 @@ public class MessagesQueries { user.UName = rs.getString(2); } } catch (SQLException e) { - logger.log(Level.SEVERE, "sql exception", e); + LOGGER.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } @@ -365,7 +363,7 @@ public class MessagesQueries { tags.add(t); } } catch (SQLException e) { - logger.log(Level.SEVERE, "sql exception", e); + LOGGER.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } @@ -387,7 +385,7 @@ public class MessagesQueries { tids.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); } @@ -409,7 +407,7 @@ public class MessagesQueries { users.add(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); } @@ -457,7 +455,7 @@ public class MessagesQueries { mids.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); } @@ -492,7 +490,7 @@ public class MessagesQueries { mids.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); } @@ -521,7 +519,7 @@ public class MessagesQueries { mids.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); } @@ -550,7 +548,7 @@ public class MessagesQueries { mids.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); } @@ -614,7 +612,7 @@ public class MessagesQueries { mids.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); } @@ -641,7 +639,7 @@ public class MessagesQueries { mids.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); } @@ -669,7 +667,7 @@ public class MessagesQueries { mids.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); } @@ -696,7 +694,7 @@ public class MessagesQueries { mids.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); } @@ -723,7 +721,7 @@ public class MessagesQueries { mids.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); } @@ -751,7 +749,7 @@ public class MessagesQueries { mids0.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); } @@ -766,7 +764,7 @@ public class MessagesQueries { mids.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); } @@ -795,7 +793,7 @@ public class MessagesQueries { mids.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); } @@ -824,7 +822,7 @@ public class MessagesQueries { mids.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); } @@ -851,7 +849,7 @@ public class MessagesQueries { mids.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); } @@ -878,7 +876,7 @@ public class MessagesQueries { mids.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); } @@ -907,7 +905,7 @@ public class MessagesQueries { mids0.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); } @@ -922,7 +920,7 @@ public class MessagesQueries { mids.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); } @@ -1001,7 +999,7 @@ public class MessagesQueries { replies.add(msg); } } catch (SQLException e) { - logger.log(Level.SEVERE, "sql exception", e); + LOGGER.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } @@ -1027,7 +1025,7 @@ public class MessagesQueries { } 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); } @@ -1038,7 +1036,7 @@ public class MessagesQueries { stmt.setInt(1, mid); stmt.executeUpdate(); } catch (SQLException e) { - logger.log(Level.SEVERE, "sql exception", e); + LOGGER.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(null, stmt); } @@ -1056,7 +1054,7 @@ public class MessagesQueries { stmt.setInt(1, mid); 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); } diff --git a/src/main/java/com/juick/server/PMQueries.java b/src/main/java/com/juick/server/PMQueries.java index a2d4545c..d49bb527 100644 --- a/src/main/java/com/juick/server/PMQueries.java +++ b/src/main/java/com/juick/server/PMQueries.java @@ -28,7 +28,6 @@ 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; /** * diff --git a/src/main/java/com/juick/server/TagQueries.java b/src/main/java/com/juick/server/TagQueries.java index 076019ff..5bc87e28 100644 --- a/src/main/java/com/juick/server/TagQueries.java +++ b/src/main/java/com/juick/server/TagQueries.java @@ -19,7 +19,6 @@ 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; @@ -39,7 +38,7 @@ import org.springframework.jdbc.support.KeyHolder; */ public class TagQueries { - private static final Logger logger = Logger.getLogger(TagQueries.class.getName()); + private static final Logger LOGGER = Logger.getLogger(TagQueries.class.getName()); public static com.juick.Tag getTag(Connection sql, int tid) { com.juick.Tag ret = null; @@ -57,7 +56,7 @@ public class TagQueries { ret.Name = rs.getString(2); } } catch (SQLException e) { - logger.log(Level.SEVERE, "sql exception", e); + LOGGER.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } @@ -112,7 +111,7 @@ public class TagQueries { 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); } @@ -149,7 +148,7 @@ public class TagQueries { tags.add(t); } } catch (SQLException e) { - logger.log(Level.SEVERE, "sql exception", e); + LOGGER.log(Level.SEVERE, "sql exception", e); } finally { Utils.finishSQL(rs, stmt); } @@ -170,7 +169,7 @@ public class TagQueries { tags.add(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); } -- cgit v1.2.3 From 714a267ae35354e86512288e0ac91990937f95ab Mon Sep 17 00:00:00 2001 From: Vitaly Takmazov Date: Sat, 16 Jan 2016 18:23:21 +0300 Subject: almost working api project --- .../java/com/juick/server/MessagesQueries.java | 187 +++++++++------------ src/main/java/com/juick/server/PMQueries.java | 10 +- src/main/java/com/juick/server/TagQueries.java | 31 +--- src/main/java/com/juick/server/UserQueries.java | 26 +-- 4 files changed, 103 insertions(+), 151 deletions(-) (limited to 'src/main/java/com/juick/server/TagQueries.java') diff --git a/src/main/java/com/juick/server/MessagesQueries.java b/src/main/java/com/juick/server/MessagesQueries.java index 9cbf4240..fd664909 100644 --- a/src/main/java/com/juick/server/MessagesQueries.java +++ b/src/main/java/com/juick/server/MessagesQueries.java @@ -19,7 +19,10 @@ package com.juick.server; import com.juick.Message; import com.juick.User; +import org.springframework.dao.EmptyResultDataAccessException; import org.springframework.jdbc.core.JdbcTemplate; +import org.springframework.jdbc.support.GeneratedKeyHolder; +import org.springframework.jdbc.support.KeyHolder; import java.sql.Connection; import java.sql.PreparedStatement; @@ -41,31 +44,25 @@ public class MessagesQueries { private static final Logger LOGGER = Logger.getLogger(MessagesQueries.class.getName()); - public static int createMessage(Connection sql, int uid, String txt, String attachment, List tags) { - int mid = 0; - - PreparedStatement stmt = null; - ResultSet rs; - try { - stmt = sql.prepareStatement("INSERT INTO messages(user_id,attach) VALUES (?,?)", Statement.RETURN_GENERATED_KEYS); + public static int createMessage(JdbcTemplate sql, int uid, String txt, String attachment, List tags) { + KeyHolder holder = new GeneratedKeyHolder(); + sql.update(con -> { + PreparedStatement stmt = con.prepareStatement("INSERT INTO messages(user_id,attach) VALUES (?,?)", + Statement.RETURN_GENERATED_KEYS); stmt.setInt(1, uid); if (attachment != null) { stmt.setString(2, attachment); } else { stmt.setNull(2, Types.VARCHAR); } - stmt.executeUpdate(); - rs = stmt.getGeneratedKeys(); - if (rs.first()) { - mid = rs.getInt(1); - } - } catch (SQLException e) { - LOGGER.log(Level.SEVERE, "sql exception", e); - } finally { - Utils.finishSQL(null, stmt); - } + return stmt; + }, holder); + + int mid = holder.getKey().intValue(); + if (mid > 0) { + String tagsNames = ""; String tagsIDs = ""; @@ -77,42 +74,33 @@ public class MessagesQueries { tagsNames += tags.get(i).Name; tagsIDs += "(" + mid + "," + tags.get(i).TID + ")"; } - - try { - stmt = sql.prepareStatement("INSERT INTO messages_tags(message_id,tag_id) VALUES " + tagsIDs); - stmt.executeUpdate(); - } catch (SQLException e) { - LOGGER.log(Level.SEVERE, "sql exception", e); - } finally { - Utils.finishSQL(null, stmt); + if (tags.size() > 0) { + sql.execute("INSERT INTO messages_tags(message_id,tag_id) VALUES " + tagsIDs); } - - try { - stmt = sql.prepareStatement("INSERT INTO messages_txt(message_id,tags,txt) VALUES (?,?,?)"); + final String finalTagsNames = tagsNames; + sql.update(con -> { + PreparedStatement stmt = con.prepareStatement("INSERT INTO messages_txt(message_id,tags,txt) " + + "VALUES (?,?,?)", Statement.NO_GENERATED_KEYS); stmt.setInt(1, mid); - if (tagsNames.isEmpty()) { + if (finalTagsNames.isEmpty()) { stmt.setNull(2, Types.VARCHAR); } else { - stmt.setString(2, tagsNames); + stmt.setString(2, finalTagsNames); } stmt.setString(3, txt); - stmt.executeUpdate(); - } catch (SQLException e) { - LOGGER.log(Level.SEVERE, "sql exception", e); - } finally { - Utils.finishSQL(null, stmt); - } + return stmt; + }); } return mid; } - public static int createReply(Connection sql, int mid, int rid, int uid, String txt, String attachment) { + public static int createReply(JdbcTemplate sql, int mid, int rid, int uid, String txt, String attachment) { int ridnew = getReplyIDIncrement(sql, mid); - PreparedStatement stmt = null; - try { - stmt = sql.prepareStatement("INSERT INTO replies(message_id,reply_id,user_id,replyto,attach,txt) VALUES (?,?,?,?,?,?)"); + sql.update( con -> { + PreparedStatement stmt = con.prepareStatement("INSERT INTO replies(message_id,reply_id,user_id," + + "replyto,attach,txt) VALUES (?,?,?,?,?,?)", Statement.NO_GENERATED_KEYS); stmt.setInt(1, mid); stmt.setInt(2, ridnew); stmt.setInt(3, uid); @@ -123,32 +111,18 @@ public class MessagesQueries { stmt.setNull(5, Types.VARCHAR); } stmt.setString(6, txt); - if (stmt.executeUpdate() == 0) { - ridnew = 0; - } - } catch (SQLException e) { - LOGGER.log(Level.SEVERE, "sql exception", e); - } finally { - Utils.finishSQL(null, stmt); - } + return stmt; + }); if (ridnew > 0) { - SQLHelpers.executeInt(sql, "UPDATE messages SET replies=replies+1 WHERE message_id=?", mid); + sql.update("UPDATE messages SET replies=replies+1 WHERE message_id=?", mid); } return ridnew; } - public static int getReplyIDIncrement(Connection sql, int mid) { - int rid = 0; - - SQLHelpers.execute(sql, "LOCK TABLES messages WRITE"); - if (SQLHelpers.executeInt(sql, "UPDATE messages SET maxreplyid=maxreplyid+1 WHERE message_id=?", mid) == 1) { - rid = SQLHelpers.getInt(sql, "SELECT maxreplyid FROM messages WHERE message_id=?", mid, 0); - } - SQLHelpers.execute(sql, "UNLOCK TABLES"); - - return rid; + public static int getReplyIDIncrement(JdbcTemplate sql, int mid) { + return sql.update("UPDATE messages SET maxreplyid=maxreplyid+1 WHERE message_id=?", mid); } public static boolean recommendMessage(Connection sql, int mid, int vuid) { @@ -230,51 +204,56 @@ public class MessagesQueries { return SQLHelpers.getInt(sql, "SELECT privacy FROM messages WHERE message_id=?", mid, -4); } + 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 AS txt " - + "ON messages.message_id=txt.message_id " - + "WHERE messages.message_id=?", new Object[]{mid}, - (rs, rowNum) -> { - Message msg = new Message(); - msg.setMID(mid); - msg.setUser(new User()); - msg.getUser().setUID(rs.getInt(1)); - msg.getUser().setUName(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.setText(rs.getString(16)); - return msg; - }); + try { + return sql.queryForObject("SELECT messages.user_id,users.nick," + + "TIMESTAMPDIFF(MINUTE,messages.ts,NOW())," + + "messages.ts," + + "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 AS txt " + + "ON messages.message_id=txt.message_id " + + "WHERE messages.message_id=?", new Object[]{mid}, + (rs, rowNum) -> { + Message msg = new Message(); + msg.setMID(mid); + msg.setUser(new User()); + msg.getUser().setUID(rs.getInt(1)); + msg.getUser().setUName(rs.getString(2)); + msg.TimeAgo = rs.getInt(3); + msg.setDate(rs.getDate(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.setText(rs.getString(16)); + return msg; + }); + } catch (EmptyResultDataAccessException e) { + return null; + } } public static com.juick.Message getReply(JdbcTemplate sql, int mid, int rid) { return sql.queryForObject("SELECT replies.user_id,users.nick," - + "replies.replyto,DATE_FORMAT(replies.ts,'%Y-%m-%d %H:%i:%s')," + + "replies.replyto,replies.ts," + "replies.attach,replies.txt FROM replies INNER JOIN users " + "ON replies.user_id=users.id " + "WHERE replies.message_id=? AND replies.reply_id=?", @@ -286,7 +265,7 @@ public class MessagesQueries { msg.getUser().setUID(rs.getInt(1)); msg.getUser().setUName(rs.getString(2)); msg.ReplyTo = rs.getInt(3); - msg.TimestampString = rs.getString(4); + msg.setDate(rs.getDate(4)); msg.AttachmentType = rs.getString(5); msg.setText(rs.getString(6)); return msg; @@ -883,7 +862,7 @@ public class MessagesQueries { 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.ts,messages.replies," + "messages_txt.repliesby,messages.attach,messages.lat," + "messages.lon,messages.likes " + "FROM (messages INNER JOIN messages_txt " @@ -906,7 +885,7 @@ public class MessagesQueries { msg.FriendsOnly = msg.Privacy < 0; msg.setText(rs.getString(7)); msg.TimeAgo = rs.getInt(8); - msg.TimestampString = rs.getString(9); + msg.setDate(rs.getDate(9)); msg.Replies = rs.getInt(10); msg.RepliesBy = rs.getString(11); msg.AttachmentType = rs.getString(12); @@ -926,7 +905,7 @@ public class MessagesQueries { PreparedStatement stmt = null; ResultSet rs = null; try { - stmt = sql.prepareStatement("SELECT replies.reply_id,replies.replyto,replies.user_id,users.nick,users.banned,replies.txt,TIMESTAMPDIFF(MINUTE,replies.ts,NOW()),DATE_FORMAT(replies.ts,'%Y-%m-%d %H:%i:%s'),replies.attach FROM replies INNER JOIN users ON replies.user_id=users.id WHERE replies.message_id=? ORDER BY replies.reply_id ASC"); + stmt = sql.prepareStatement("SELECT replies.reply_id,replies.replyto,replies.user_id,users.nick,users.banned,replies.txt,TIMESTAMPDIFF(MINUTE,replies.ts,NOW()),replies.ts,replies.attach FROM replies INNER JOIN users ON replies.user_id=users.id WHERE replies.message_id=? ORDER BY replies.reply_id ASC"); stmt.setInt(1, mid); rs = stmt.executeQuery(); rs.beforeFirst(); @@ -941,7 +920,7 @@ public class MessagesQueries { msg.getUser().Banned = rs.getBoolean(5); msg.setText(rs.getString(6)); msg.TimeAgo = rs.getInt(7); - msg.TimestampString = rs.getString(8); + msg.setDate(rs.getDate(8)); msg.AttachmentType = rs.getString(9); replies.add(msg); diff --git a/src/main/java/com/juick/server/PMQueries.java b/src/main/java/com/juick/server/PMQueries.java index 7984844a..345f641f 100644 --- a/src/main/java/com/juick/server/PMQueries.java +++ b/src/main/java/com/juick/server/PMQueries.java @@ -154,7 +154,7 @@ public class PMQueries { msg.setUser(new User()); msg.getUser().setUID(uuid); msg.setText(rs.getString(2)); - msg.TimestampString = rs.getString(3); + msg.setDate(rs.getDate(3)); return msg; }, new Object[]{uid, uid_to, uid, uid_to}); @@ -170,7 +170,7 @@ public class PMQueries { PreparedStatement stmt = null; ResultSet rs = null; try { - stmt = sql.prepareStatement("SELECT pm.user_id,users.nick,pm.txt,TIMESTAMPDIFF(MINUTE,pm.ts,NOW()),DATE_FORMAT(pm.ts,'%Y-%m-%d %H:%i:%s') FROM pm INNER JOIN users ON pm.user_id=users.id WHERE pm.user_id_to=? ORDER BY pm.ts DESC LIMIT 20"); + stmt = sql.prepareStatement("SELECT pm.user_id,users.nick,pm.txt,TIMESTAMPDIFF(MINUTE,pm.ts,NOW()),pm.ts FROM pm INNER JOIN users ON pm.user_id=users.id WHERE pm.user_id_to=? ORDER BY pm.ts DESC LIMIT 20"); stmt.setInt(1, uid); rs = stmt.executeQuery(); rs.beforeFirst(); @@ -181,7 +181,7 @@ public class PMQueries { msg.getUser().setUName(rs.getString(2)); msg.setText(rs.getString(3)); msg.TimeAgo = rs.getInt(4); - msg.TimestampString = rs.getString(5); + msg.setDate(rs.getDate(5)); msgs.add(0, msg); } } catch (SQLException e) { @@ -199,7 +199,7 @@ public class PMQueries { PreparedStatement stmt = null; ResultSet rs = null; try { - stmt = sql.prepareStatement("SELECT pm.user_id_to,users.nick,pm.txt,TIMESTAMPDIFF(MINUTE,pm.ts,NOW()),DATE_FORMAT(pm.ts,'%Y-%m-%d %H:%i:%s') FROM pm INNER JOIN users ON pm.user_id_to=users.id WHERE pm.user_id=? ORDER BY pm.ts DESC LIMIT 20"); + stmt = sql.prepareStatement("SELECT pm.user_id_to,users.nick,pm.txt,TIMESTAMPDIFF(MINUTE,pm.ts,NOW()),pm.ts FROM pm INNER JOIN users ON pm.user_id_to=users.id WHERE pm.user_id=? ORDER BY pm.ts DESC LIMIT 20"); stmt.setInt(1, uid); rs = stmt.executeQuery(); rs.beforeFirst(); @@ -210,7 +210,7 @@ public class PMQueries { msg.getUser().setUName(rs.getString(2)); msg.setText(rs.getString(3)); msg.TimeAgo = rs.getInt(4); - msg.TimestampString = rs.getString(5); + msg.setDate(rs.getDate(5)); msgs.add(0, msg); } } catch (SQLException e) { diff --git a/src/main/java/com/juick/server/TagQueries.java b/src/main/java/com/juick/server/TagQueries.java index 5bc87e28..22839ad0 100644 --- a/src/main/java/com/juick/server/TagQueries.java +++ b/src/main/java/com/juick/server/TagQueries.java @@ -40,28 +40,15 @@ public class TagQueries { private static final Logger LOGGER = Logger.getLogger(TagQueries.class.getName()); - public static com.juick.Tag getTag(Connection sql, int tid) { - com.juick.Tag ret = null; - - PreparedStatement stmt = null; - ResultSet rs = null; - try { - stmt = sql.prepareStatement("SELECT synonym_id,name FROM tags WHERE tag_id=?"); - stmt.setInt(1, tid); - rs = stmt.executeQuery(); - if (rs.first()) { - ret = new com.juick.Tag(); - ret.TID = tid; - ret.SynonymID = rs.getInt(1); - ret.Name = rs.getString(2); - } - } catch (SQLException e) { - LOGGER.log(Level.SEVERE, "sql exception", e); - } finally { - Utils.finishSQL(rs, stmt); - } - - return ret; + public static com.juick.Tag getTag(JdbcTemplate sql, int tid) { + return sql.queryForObject("SELECT synonym_id,name FROM tags WHERE tag_id=?", + new Object[]{tid}, (rs, num) -> { + Tag ret = new Tag(); + ret.TID = tid; + ret.SynonymID = rs.getInt(1); + ret.Name = rs.getString(2); + return ret; + }); } public static com.juick.Tag getTag(JdbcTemplate sql, String tag, boolean autoCreate) { diff --git a/src/main/java/com/juick/server/UserQueries.java b/src/main/java/com/juick/server/UserQueries.java index 2cd557c6..5588d8e4 100644 --- a/src/main/java/com/juick/server/UserQueries.java +++ b/src/main/java/com/juick/server/UserQueries.java @@ -199,27 +199,13 @@ public class UserQueries { return uname != null && uname.length() >= 2 && uname.length() <= 16 && uname.matches("[a-zA-Z0-9\\-]+"); } - public static int checkPassword(Connection sql, String username, String password) { - int uid = 0; - PreparedStatement stmt = null; - ResultSet rs = null; - try { - stmt = sql.prepareStatement("SELECT id,passw FROM users WHERE nick=?"); - stmt.setString(1, username); - rs = stmt.executeQuery(); - if (rs.first()) { - if (password.equals(rs.getString(2))) { - uid = rs.getInt(1); - } else { - uid = -1; - } - } - } catch (SQLException e) { - LOGGER.log(Level.SEVERE, "sql exception", e); - } finally { - Utils.finishSQL(rs, stmt); + public static int checkPassword(JdbcTemplate sql, String username, String password) { + String realPassword = sql.queryForObject("SELECT passw FROM users WHERE nick=?", String.class, username); + if (realPassword.equals(password)) { + return UserQueries.getUserByName(sql, username).getUID(); + } else { + return -1; } - return uid; } public static int getUserOptionInt(Connection sql, int uid, String option, int defaultValue) { -- cgit v1.2.3 From 9232bdcb144bc3b94f4b825b3a4cb588078bd72d Mon Sep 17 00:00:00 2001 From: Vitaly Takmazov Date: Sun, 17 Jan 2016 00:15:07 +0300 Subject: fix getTag --- src/main/java/com/juick/server/TagQueries.java | 23 ++++++++++++++--------- 1 file changed, 14 insertions(+), 9 deletions(-) (limited to 'src/main/java/com/juick/server/TagQueries.java') diff --git a/src/main/java/com/juick/server/TagQueries.java b/src/main/java/com/juick/server/TagQueries.java index 22839ad0..f3ab480e 100644 --- a/src/main/java/com/juick/server/TagQueries.java +++ b/src/main/java/com/juick/server/TagQueries.java @@ -18,6 +18,7 @@ package com.juick.server; import com.juick.Tag; +import org.springframework.dao.EmptyResultDataAccessException; import org.springframework.jdbc.core.JdbcTemplate; import java.sql.Connection; @@ -52,15 +53,19 @@ public class TagQueries { } 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; - }); - + Tag ret = null; + try { + 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; + }); + } catch (EmptyResultDataAccessException e) { + // tag not found + } if (ret == null && autoCreate) { ret = new com.juick.Tag(); ret.TID = createTag(sql, tag); -- cgit v1.2.3 From 9e740f48cca7332c67a165c6cf60e09bc549e686 Mon Sep 17 00:00:00 2001 From: Vitaly Takmazov Date: Mon, 27 Jun 2016 10:33:04 +0300 Subject: more spring-jdbc --- build.gradle | 3 +- src/main/java/com/juick/server/AdsQueries.java | 40 +++-- .../java/com/juick/server/CrosspostQueries.java | 74 ++++----- src/main/java/com/juick/server/PMQueries.java | 173 ++++++++------------- src/main/java/com/juick/server/TagQueries.java | 58 +++---- src/main/java/com/juick/server/UserQueries.java | 73 +++++---- 6 files changed, 169 insertions(+), 252 deletions(-) (limited to 'src/main/java/com/juick/server/TagQueries.java') diff --git a/build.gradle b/build.gradle index b82c3d5c..9f5bc86d 100644 --- a/build.gradle +++ b/build.gradle @@ -5,5 +5,6 @@ repositories { } dependencies { - compile "org.springframework:spring-jdbc:4.2.4.RELEASE" + compile "org.springframework:spring-jdbc:4.3.0.RELEASE" + compile "org.apache.commons:commons-lang3:3.4" } \ No newline at end of file diff --git a/src/main/java/com/juick/server/AdsQueries.java b/src/main/java/com/juick/server/AdsQueries.java index ad9b7149..06590817 100644 --- a/src/main/java/com/juick/server/AdsQueries.java +++ b/src/main/java/com/juick/server/AdsQueries.java @@ -17,11 +17,8 @@ */ package com.juick.server; -import java.sql.Connection; -import java.sql.PreparedStatement; -import java.sql.SQLException; -import java.util.logging.Level; -import java.util.logging.Logger; +import org.springframework.dao.EmptyResultDataAccessException; +import org.springframework.jdbc.core.JdbcTemplate; /** * @@ -29,27 +26,26 @@ import java.util.logging.Logger; */ public class AdsQueries { - private static final Logger logger = Logger.getLogger(AdsQueries.class.getName()); - - public static int getAdMID(Connection sql, int uid) { + public static int getAdMID(JdbcTemplate sql, int uid) { if (uid > 0) { - return SQLHelpers.getInt(sql, "SELECT message_id FROM ads_messages WHERE message_id NOT IN (SELECT message_id FROM ads_messages_log WHERE user_id=? AND ts>UNIX_TIMESTAMP()-60*60*24 GROUP BY message_id HAVING COUNT(*)>2) ORDER BY RAND() LIMIT 1", uid, 0); + try { + return sql.queryForObject("SELECT message_id FROM ads_messages " + + "WHERE message_id NOT IN (SELECT message_id FROM ads_messages_log WHERE user_id=? " + + "AND ts>UNIX_TIMESTAMP()-60*60*24 GROUP BY message_id HAVING COUNT(*)>2) ORDER BY RAND() LIMIT 1", + Integer.class, uid); + } catch (EmptyResultDataAccessException e) { + return 0; + } } else { - return SQLHelpers.getInt(sql, "SELECT message_id FROM ads_messages ORDER BY RAND() LIMIT 1", 0); + try { + return sql.queryForObject("SELECT message_id FROM ads_messages ORDER BY RAND() LIMIT 1", Integer.class); + } catch (EmptyResultDataAccessException e) { + return 0; + } } } - public static void logAdMID(Connection sql, int uid, int mid) { - PreparedStatement stmt = null; - try { - stmt = sql.prepareStatement("INSERT INTO ads_messages_log(user_id,message_id,ts) VALUES (?,?,UNIX_TIMESTAMP())"); - stmt.setInt(1, uid); - stmt.setInt(2, mid); - stmt.executeUpdate(); - } catch (SQLException e) { - logger.log(Level.SEVERE, "sql exception", e); - } finally { - Utils.finishSQL(null, stmt); - } + public static void logAdMID(JdbcTemplate sql, int uid, int mid) { + sql.update("INSERT INTO ads_messages_log(user_id,message_id,ts) VALUES (?,?,UNIX_TIMESTAMP())", uid, mid); } } diff --git a/src/main/java/com/juick/server/CrosspostQueries.java b/src/main/java/com/juick/server/CrosspostQueries.java index b44b6b59..04898d0a 100644 --- a/src/main/java/com/juick/server/CrosspostQueries.java +++ b/src/main/java/com/juick/server/CrosspostQueries.java @@ -17,12 +17,11 @@ */ package com.juick.server; -import java.sql.Connection; -import java.sql.PreparedStatement; -import java.sql.ResultSet; -import java.sql.SQLException; -import java.util.logging.Level; -import java.util.logging.Logger; +import org.apache.commons.lang3.tuple.Pair; +import org.springframework.dao.EmptyResultDataAccessException; +import org.springframework.jdbc.core.JdbcTemplate; + +import java.util.Optional; /** * @@ -30,55 +29,34 @@ import java.util.logging.Logger; */ public class CrosspostQueries { - private static final Logger logger = Logger.getLogger(CrosspostQueries.class.getName()); - - public static String[] getTwitterTokens(Connection sql, int uid) { - String tokens[] = null; - - PreparedStatement stmt = null; - ResultSet rs = null; + public static Optional> getTwitterTokens(JdbcTemplate sql, int uid) { try { - stmt = sql.prepareStatement("SELECT access_token,access_token_secret FROM twitter WHERE user_id=? AND crosspost=1"); - stmt.setInt(1, uid); - rs = stmt.executeQuery(); - if (rs.first()) { - tokens = new String[2]; - tokens[0] = rs.getString(1); - tokens[1] = rs.getString(2); - } - } catch (SQLException e) { - logger.log(Level.SEVERE, "sql exception", e); - } finally { - Utils.finishSQL(rs, stmt); + return sql.queryForObject("SELECT access_token,access_token_secret FROM twitter WHERE user_id=? AND crosspost=1", + (rs, num) -> { + return Optional.of(Pair.of(rs.getString(1), rs.getString(2))); + }, uid); + } catch (EmptyResultDataAccessException e) { + return Optional.empty(); } - - return tokens; } - public static String getFacebookToken(Connection sql, int uid) { - return SQLHelpers.getString(sql, "SELECT access_token FROM facebook WHERE user_id=? AND access_token IS NOT NULL AND crosspost=1", uid); + public static Optional getFacebookToken(JdbcTemplate sql, int uid) { + try { + return Optional.of(sql.queryForObject("SELECT access_token FROM facebook WHERE user_id=? AND access_token IS NOT NULL " + + "AND crosspost=1", String.class, uid)); + } catch (EmptyResultDataAccessException e) { + return Optional.empty(); + } } - public static String[] getVKTokens(Connection sql, int uid) { - String tokens[] = null; - - PreparedStatement stmt = null; - ResultSet rs = null; + public static Optional> getVKTokens(JdbcTemplate sql, int uid) { try { - stmt = sql.prepareStatement("SELECT vk_id,access_token FROM vk WHERE user_id=? AND crosspost=1"); - stmt.setInt(1, uid); - rs = stmt.executeQuery(); - if (rs.first()) { - tokens = new String[2]; - tokens[0] = rs.getString(1); - tokens[1] = rs.getString(2); - } - } catch (SQLException e) { - logger.log(Level.SEVERE, "sql exception", e); - } finally { - Utils.finishSQL(rs, stmt); + return sql.queryForObject("SELECT vk_id,access_token FROM vk WHERE user_id=? AND crosspost=1", + (rs, num) -> { + return Optional.of(Pair.of(rs.getString(1), rs.getString(2))); + }, uid); + } catch (EmptyResultDataAccessException e) { + return Optional.empty(); } - - return tokens; } } diff --git a/src/main/java/com/juick/server/PMQueries.java b/src/main/java/com/juick/server/PMQueries.java index 6de30995..772a1c28 100644 --- a/src/main/java/com/juick/server/PMQueries.java +++ b/src/main/java/com/juick/server/PMQueries.java @@ -18,16 +18,10 @@ package com.juick.server; import com.juick.User; +import org.springframework.dao.EmptyResultDataAccessException; +import org.springframework.jdbc.core.JdbcTemplate; -import java.sql.Connection; -import java.sql.PreparedStatement; -import java.sql.ResultSet; -import java.sql.SQLException; -import java.util.ArrayList; import java.util.List; -import java.util.logging.Level; -import java.util.logging.Logger; -import org.springframework.jdbc.core.JdbcTemplate; /** * @@ -35,8 +29,6 @@ import org.springframework.jdbc.core.JdbcTemplate; */ public class PMQueries { - private static final Logger LOGGER = Logger.getLogger(PMQueries.class.getName()); - 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 (?,?,?)", uid_from, uid_to, body) > 0; @@ -55,20 +47,8 @@ public class PMQueries { uid, jid) > 0; } - public static boolean removePMinRoster(Connection sql, int uid, String jid) { - boolean ret = false; - PreparedStatement stmt = null; - try { - stmt = sql.prepareStatement("DELETE FROM pm_inroster WHERE user_id=? AND jid=?"); - 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 removePMinRoster(JdbcTemplate sql, int uid, String jid) { + return sql.update("DELETE FROM pm_inroster WHERE user_id=? AND jid=?", uid, jid) > 0; } public static boolean havePMinRoster(JdbcTemplate sql, int uid, String jid) { @@ -78,37 +58,49 @@ public class PMQueries { return res.size() > 0; } - public static String getLastView(Connection sql, int uid_from, int uid_to) { - String ret = null; - - PreparedStatement stmt = null; - ResultSet rs = null; + public static String getLastView(JdbcTemplate sql, int uid_from, int uid_to) { try { - stmt = sql.prepareStatement("SELECT lastview FROM pm_streams WHERE user_id=? AND user_id_to=?"); - stmt.setInt(1, uid_from); - stmt.setInt(2, uid_to); - rs = stmt.executeQuery(); - if (rs.first()) { - ret = rs.getString(1); - } - } catch (SQLException e) { - LOGGER.log(Level.SEVERE, "sql exception", e); - } finally { - Utils.finishSQL(rs, stmt); + return sql.queryForObject("SELECT lastview FROM pm_streams WHERE user_id=? AND user_id_to=?", String.class, + uid_from, uid_to); + } catch (EmptyResultDataAccessException e) { + return null; } - return ret; } public static List getPMLastConversationsUsers(JdbcTemplate sql, int uid, int cnt) { - return sql.query("SELECT users.id, users.nick,pm_streams.unread FROM pm_streams " + - "INNER JOIN users ON users.id = pm_streams.user_id WHERE user_id_to=? " + - "ORDER BY pm_streams.lastmessage DESC LIMIT ?", (rs, rowNum) -> { - com.juick.User u = new com.juick.User(); - u.setUID(rs.getInt(1)); - u.setUName(rs.getString(2)); - u.MessagesCount = rs.getInt(3); - return u; - }, uid, cnt); + List qusers = 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.setUID(rs.getInt(1)); + u.MessagesCount = rs.getInt(2); + return u; + }, uid); + + if (qusers.size() < cnt) { + List 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.setUID(uuid); + return u; + }, uid, uid, cnt * 2); + for (User addUser : addUsers) { + if (!haveUserInArray(qusers, addUser.getUID())) { + qusers.add(addUser); + if (qusers.size() >= cnt) { + break; + } + } + } + } + if (!qusers.isEmpty()) { + UserQueries.fillUsersByID(sql, qusers); + } + + return qusers; } public static boolean haveUserInArray(List arr, int uid) { @@ -140,61 +132,34 @@ public class PMQueries { return msgs; } - public static List getLastPMInbox(Connection sql, int uid) { - List msgs = new ArrayList<>(20); - - PreparedStatement stmt = null; - ResultSet rs = null; - try { - stmt = sql.prepareStatement("SELECT pm.user_id,users.nick,pm.txt,TIMESTAMPDIFF(MINUTE,pm.ts,NOW()),pm.ts FROM pm INNER JOIN users ON pm.user_id=users.id WHERE pm.user_id_to=? ORDER BY pm.ts DESC LIMIT 20"); - stmt.setInt(1, uid); - rs = stmt.executeQuery(); - rs.beforeFirst(); - while (rs.next()) { - com.juick.Message msg = new com.juick.Message(); - msg.setUser(new User()); - msg.getUser().setUID(rs.getInt(1)); - msg.getUser().setUName(rs.getString(2)); - msg.setText(rs.getString(3)); - msg.TimeAgo = rs.getInt(4); - msg.setDate(rs.getTimestamp(5)); - msgs.add(0, msg); - } - } catch (SQLException e) { - LOGGER.log(Level.SEVERE, "sql exception", e); - } finally { - Utils.finishSQL(rs, stmt); - } - - return msgs; + public static List getLastPMInbox(JdbcTemplate sql, int uid) { + return sql.query("SELECT pm.user_id,users.nick,pm.txt,TIMESTAMPDIFF(MINUTE,pm.ts,NOW()),pm.ts " + + "FROM pm INNER JOIN users ON pm.user_id=users.id WHERE pm.user_id_to=? ORDER BY pm.ts DESC LIMIT 20", + (rs, num) -> { + com.juick.Message msg = new com.juick.Message(); + msg.setUser(new User()); + msg.getUser().setUID(rs.getInt(1)); + msg.getUser().setUName(rs.getString(2)); + msg.setText(rs.getString(3)); + msg.TimeAgo = rs.getInt(4); + msg.setDate(rs.getTimestamp(5)); + return msg; + }, uid); } - public static List getLastPMSent(Connection sql, int uid) { - List msgs = new ArrayList<>(20); - - PreparedStatement stmt = null; - ResultSet rs = null; - try { - stmt = sql.prepareStatement("SELECT pm.user_id_to,users.nick,pm.txt,TIMESTAMPDIFF(MINUTE,pm.ts,NOW()),pm.ts FROM pm INNER JOIN users ON pm.user_id_to=users.id WHERE pm.user_id=? ORDER BY pm.ts DESC LIMIT 20"); - stmt.setInt(1, uid); - rs = stmt.executeQuery(); - rs.beforeFirst(); - while (rs.next()) { - com.juick.Message msg = new com.juick.Message(); - msg.setUser(new User()); - msg.getUser().setUID(rs.getInt(1)); - msg.getUser().setUName(rs.getString(2)); - msg.setText(rs.getString(3)); - msg.TimeAgo = rs.getInt(4); - msg.setDate(rs.getTimestamp(5)); - msgs.add(0, msg); - } - } catch (SQLException e) { - LOGGER.log(Level.SEVERE, "sql exception", e); - } finally { - Utils.finishSQL(rs, stmt); - } - - return msgs; + public static List getLastPMSent(JdbcTemplate sql, int uid) { + return sql.query("SELECT pm.user_id_to,users.nick,pm.txt,TIMESTAMPDIFF(MINUTE,pm.ts,NOW())," + + "pm.ts FROM pm INNER JOIN users ON pm.user_id_to=users.id " + + "WHERE pm.user_id=? ORDER BY pm.ts DESC LIMIT 20", + (rs, num) -> { + com.juick.Message msg = new com.juick.Message(); + msg.setUser(new User()); + msg.getUser().setUID(rs.getInt(1)); + msg.getUser().setUName(rs.getString(2)); + msg.setText(rs.getString(3)); + msg.TimeAgo = rs.getInt(4); + msg.setDate(rs.getTimestamp(5)); + return msg; + }, uid); } } diff --git a/src/main/java/com/juick/server/TagQueries.java b/src/main/java/com/juick/server/TagQueries.java index f3ab480e..d2159b5a 100644 --- a/src/main/java/com/juick/server/TagQueries.java +++ b/src/main/java/com/juick/server/TagQueries.java @@ -90,25 +90,12 @@ public class TagQueries { return ret; } - public static boolean getTagNoIndex(Connection sql, int tag_id) { - boolean ret = false; - - PreparedStatement stmt = null; - ResultSet rs = null; + public static boolean getTagNoIndex(JdbcTemplate sql, int tag_id) { try { - stmt = sql.prepareStatement("SELECT noindex FROM tags WHERE tag_id=?"); - stmt.setInt(1, tag_id); - 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 sql.queryForObject("SELECT noindex FROM tags WHERE tag_id=?", Integer.class, tag_id) == 1; + } catch (EmptyResultDataAccessException e) { + return false; } - - return ret; } public static int createTag(JdbcTemplate sql, String name) { @@ -123,28 +110,17 @@ public class TagQueries { return holder.getKey().intValue(); } - public static List getUserTagsAll(Connection sql, int uid) { - List tags = new ArrayList<>(); - - PreparedStatement stmt = null; - ResultSet rs = null; - try { - stmt = sql.prepareStatement("SELECT tags.name,COUNT(messages.message_id) FROM (messages INNER JOIN messages_tags ON (messages.user_id=? AND messages.message_id=messages_tags.message_id)) INNER JOIN tags ON messages_tags.tag_id=tags.tag_id GROUP BY tags.tag_id ORDER BY tags.name ASC"); - stmt.setInt(1, uid); - rs = stmt.executeQuery(); - rs.beforeFirst(); - while (rs.next()) { - com.juick.Tag t = new com.juick.Tag(); - t.Name = rs.getString(1); - t.UsageCnt = rs.getInt(2); - tags.add(t); - } - } catch (SQLException e) { - LOGGER.log(Level.SEVERE, "sql exception", e); - } finally { - Utils.finishSQL(rs, stmt); - } - return tags; + public static List getUserTagsAll(JdbcTemplate sql, int uid) { + return sql.query("SELECT tags.name,COUNT(messages.message_id) " + + "FROM (messages INNER JOIN messages_tags ON (messages.user_id=? " + + "AND messages.message_id=messages_tags.message_id)) " + + "INNER JOIN tags ON messages_tags.tag_id=tags.tag_id GROUP BY tags.tag_id ORDER BY tags.name ASC", + (rs, rowNum) -> { + Tag t = new Tag(); + t.Name = rs.getString(1); + t.UsageCnt = rs.getInt(2); + return t; + }, uid); } public static List getUserBLTags(Connection sql, int uid) { @@ -167,4 +143,8 @@ public class TagQueries { } return tags; } + + public static List getPopularTags(JdbcTemplate sql) { + return sql.queryForList("SELECT name FROM tags WHERE top=1 ORDER BY name ASC", String.class); + } } diff --git a/src/main/java/com/juick/server/UserQueries.java b/src/main/java/com/juick/server/UserQueries.java index 817ae5d3..0d6a3485 100644 --- a/src/main/java/com/juick/server/UserQueries.java +++ b/src/main/java/com/juick/server/UserQueries.java @@ -23,6 +23,7 @@ import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.jdbc.core.RowMapper; import org.springframework.jdbc.support.GeneratedKeyHolder; import org.springframework.jdbc.support.KeyHolder; +import org.springframework.util.StringUtils; import java.sql.Connection; import java.sql.PreparedStatement; @@ -330,47 +331,22 @@ public class UserQueries { } } - public static List checkBL(Connection sql, int visitor, List uids) { - List ret = new ArrayList<>(); - - 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.convertArrayInt2String(uids) + ")"); - stmt.setInt(1, visitor); - rs = stmt.executeQuery(); - rs.beforeFirst(); - while (rs.next()) { - ret.add(rs.getInt(1)); - } - } catch (SQLException e) { - LOGGER.log(Level.SEVERE, "sql exception", e); - } finally { - Utils.finishSQL(rs, stmt); + public static List checkBL(JdbcTemplate sql, int visitor, List uids) { + if (!uids.isEmpty()) { + return sql.queryForList("SELECT user_id FROM bl_users WHERE bl_user_id=? and user_id IN (" + + StringUtils.collectionToCommaDelimitedString(uids) + ")", Integer.class, visitor); + } else { + return new ArrayList<>(); } - - return ret; } - public static boolean isSubscribed(Connection sql, int uid, int check) { - boolean ret = false; - - PreparedStatement stmt = null; - ResultSet rs = null; + public static boolean isSubscribed(JdbcTemplate sql, int uid, int check) { try { - stmt = sql.prepareStatement("SELECT 1 FROM subscr_users WHERE suser_id=? AND user_id=?"); - stmt.setInt(1, uid); - stmt.setInt(2, check); - 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 sql.queryForObject("SELECT 1 FROM subscr_users WHERE suser_id=? AND user_id=?", + Integer.class, uid, check) == 1; + } catch (EmptyResultDataAccessException e) { + return false; } - return ret; } public static List getUserRead(JdbcTemplate sql, int uid) { @@ -403,8 +379,29 @@ public class UserQueries { return users; } - public static List getUserReaders(JdbcTemplate sql, int uid) { - return sql.queryForList("SELECT suser_id FROM subscr_users WHERE user_id=?", Integer.class, uid); + public List getUserReaders(JdbcTemplate sql, int uid) { + return sql.query("SELECT users.id, users.nick FROM subscr_users " + + "INNER JOIN users ON subscr_users.suser_id=users.id " + + "WHERE subscr_users.user_id=? ORDER BY users.nick", + (rs, num) -> { + com.juick.User u = new com.juick.User(); + u.setUID(rs.getInt(1)); + u.setUName(rs.getString(2)); + return u; + }, uid); + } + + public List getUserFriends(JdbcTemplate sql, int uid) { + return sql.query("SELECT users.id,users.nick FROM subscr_users " + + "INNER JOIN users ON subscr_users.user_id=users.id " + + "WHERE subscr_users.suser_id=? AND users.id!=? " + + "ORDER BY users.nick", + (rs, num) -> { + com.juick.User u = new com.juick.User(); + u.setUID(rs.getInt(1)); + u.setUName(rs.getString(2)); + return u; + }, uid, uid); } public static List getUserBLUsers(Connection sql, int uid) { -- cgit v1.2.3 From 6362eab34390b187944c7e3c12eb444e2fb2c455 Mon Sep 17 00:00:00 2001 From: Vitaly Takmazov Date: Mon, 27 Jun 2016 13:15:57 +0300 Subject: more spring-jdbc --- src/main/java/com/juick/server/PushQueries.java | 108 ++++------ .../com/juick/server/SubscriptionsQueries.java | 38 +--- src/main/java/com/juick/server/TagQueries.java | 23 +-- src/main/java/com/juick/server/UserQueries.java | 220 +++++++-------------- 4 files changed, 128 insertions(+), 261 deletions(-) (limited to 'src/main/java/com/juick/server/TagQueries.java') diff --git a/src/main/java/com/juick/server/PushQueries.java b/src/main/java/com/juick/server/PushQueries.java index c17972b0..54528993 100644 --- a/src/main/java/com/juick/server/PushQueries.java +++ b/src/main/java/com/juick/server/PushQueries.java @@ -4,14 +4,12 @@ */ package com.juick.server; -import java.sql.Connection; -import java.sql.PreparedStatement; -import java.sql.ResultSet; -import java.sql.SQLException; -import java.util.ArrayList; +import org.springframework.dao.EmptyResultDataAccessException; +import org.springframework.jdbc.core.JdbcTemplate; +import org.springframework.util.StringUtils; + import java.util.List; -import java.util.logging.Level; -import java.util.logging.Logger; +import java.util.Optional; /** * @@ -19,80 +17,58 @@ import java.util.logging.Logger; */ public class PushQueries { - private static final Logger logger = Logger.getLogger(PushQueries.class.getName()); - - public static String getAndroidRegID(Connection sql, int uid) { - return SQLHelpers.getString(sql, "SELECT regid FROM android WHERE user_id=?", uid); - } - - public static List getAndroidSubscribers(Connection sql, int uid) { - List regids = new ArrayList<>(); - - PreparedStatement stmt = null; - ResultSet rs = null; + public static Optional getAndroidRegID(JdbcTemplate sql, int uid) { try { - stmt = sql.prepareStatement("SELECT regid FROM android INNER JOIN subscr_users ON (subscr_users.user_id=? AND android.user_id=subscr_users.suser_id)"); - stmt.setInt(1, uid); - rs = stmt.executeQuery(); - rs.beforeFirst(); - while (rs.next()) { - regids.add(rs.getString(1)); - } - } catch (SQLException e) { - logger.log(Level.SEVERE, "sql exception", e); - } finally { - Utils.finishSQL(rs, stmt); + return Optional.of(sql.queryForObject("SELECT regid FROM android WHERE user_id=?", String.class, uid)); + } catch (EmptyResultDataAccessException e) { + return Optional.empty(); } - return regids; + } - public static String getWinPhoneURL(Connection sql, int uid) { - return SQLHelpers.getString(sql, "SELECT url FROM winphone WHERE user_id=?", uid); + public static List getAndroidSubscribers(JdbcTemplate sql, int uid) { + return sql.queryForList("SELECT regid FROM android INNER JOIN subscr_users " + + "ON (subscr_users.user_id=? AND android.user_id=subscr_users.suser_id)", String.class, uid); } - public static List getWinPhoneSubscribers(Connection sql, int uid) { - List urls = new ArrayList<>(); + public static List getAndroidTokens(JdbcTemplate sql, List uids) { + return sql.queryForList("SELECT regid FROM android INNER JOIN users " + + "ON (users.id=android.user_id) WHERE users.id IN (" + StringUtils.collectionToCommaDelimitedString(uids) + ")", String.class); + } - PreparedStatement stmt = null; - ResultSet rs = null; + public static Optional getWinPhoneURL(JdbcTemplate sql, int uid) { try { - stmt = sql.prepareStatement("SELECT url FROM winphone INNER JOIN subscr_users ON (subscr_users.user_id=? AND winphone.user_id=subscr_users.suser_id)"); - stmt.setInt(1, uid); - rs = stmt.executeQuery(); - rs.beforeFirst(); - while (rs.next()) { - urls.add(rs.getString(1)); - } - } catch (SQLException e) { - logger.log(Level.SEVERE, "sql exception", e); - } finally { - Utils.finishSQL(rs, stmt); + return Optional.of(sql.queryForObject("SELECT url FROM winphone WHERE user_id=?", String.class, uid)); + } catch (EmptyResultDataAccessException e) { + return Optional.empty(); } - return urls; } - public static String getAPNSToken(Connection sql, int uid) { - return SQLHelpers.getString(sql, "SELECT token from ios WHERE user_id=?", uid); + public static List getWinPhoneSubscribers(JdbcTemplate sql, int uid) { + return sql.queryForList("SELECT url FROM winphone INNER JOIN subscr_users " + + "ON (subscr_users.user_id=? AND winphone.user_id=subscr_users.suser_id)", String.class, uid); } - public static List getAPNSSubscribers(Connection sql, int uid) { - List urls = new ArrayList<>(); + public static List getWindowsTokens(JdbcTemplate sql, List uids) { + return sql.queryForList("SELECT url FROM winphone INNER JOIN users " + + "ON (users.id=winphone.user_id) WHERE users.id IN (" + StringUtils.collectionToCommaDelimitedString(uids) + ")", String.class); + } - PreparedStatement stmt = null; - ResultSet rs = null; + public static Optional getAPNSToken(JdbcTemplate sql, int uid) { try { - stmt = sql.prepareStatement("SELECT token FROM ios INNER JOIN subscr_users ON (subscr_users.user_id=? AND ios.user_id=subscr_users.suser_id)"); - stmt.setInt(1, uid); - rs = stmt.executeQuery(); - rs.beforeFirst(); - while (rs.next()) { - urls.add(rs.getString(1)); - } - } catch (SQLException e) { - logger.log(Level.SEVERE, "sql exception", e); - } finally { - Utils.finishSQL(rs, stmt); + return Optional.of(sql.queryForObject("SELECT token from ios WHERE user_id=?", String.class, uid)); + } catch (EmptyResultDataAccessException e) { + return Optional.empty(); } - return urls; } + + public static List getAPNSSubscribers(JdbcTemplate sql, int uid) { + return sql.queryForList("SELECT token FROM ios INNER JOIN subscr_users " + + "ON (subscr_users.user_id=? AND ios.user_id=subscr_users.suser_id)", String.class, uid); + } + public static List getAPNSTokens(JdbcTemplate sql, List uids) { + return sql.queryForList("SELECT token FROM ios INNER JOIN users " + + "ON (users.id=ios.user_id) WHERE users.id IN (" + StringUtils.collectionToCommaDelimitedString(uids) + ")", String.class); + } + } diff --git a/src/main/java/com/juick/server/SubscriptionsQueries.java b/src/main/java/com/juick/server/SubscriptionsQueries.java index b271435b..feebfb22 100644 --- a/src/main/java/com/juick/server/SubscriptionsQueries.java +++ b/src/main/java/com/juick/server/SubscriptionsQueries.java @@ -8,15 +8,10 @@ import com.juick.User; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.util.StringUtils; -import java.sql.Connection; -import java.sql.PreparedStatement; -import java.sql.ResultSet; -import java.sql.SQLException; import java.util.ArrayList; import java.util.HashSet; import java.util.List; import java.util.Set; -import java.util.logging.Level; import java.util.logging.Logger; /** @@ -27,31 +22,16 @@ public class SubscriptionsQueries { private static final Logger logger = Logger.getLogger(SubscriptionsQueries.class.getName()); - public static List getJIDSubscribedToUser(Connection sql, int uid, boolean friendsonly) { - List jids = new ArrayList<>(); - - PreparedStatement stmt = null; - ResultSet rs = null; - try { - if (friendsonly == false) { - stmt = sql.prepareStatement("SELECT jids.jid FROM subscr_users INNER JOIN jids ON (subscr_users.user_id=? AND subscr_users.suser_id=jids.user_id) WHERE jids.active=1"); - stmt.setInt(1, uid); - } else { - stmt = sql.prepareStatement("SELECT jids.jid FROM subscr_users INNER JOIN jids ON (subscr_users.user_id=? AND subscr_users.suser_id=jids.user_id) WHERE jids.active=1 AND jids.user_id IN (SELECT wl_user_id FROM wl_users WHERE user_id=?)"); - stmt.setInt(1, uid); - stmt.setInt(2, uid); - } - rs = stmt.executeQuery(); - rs.beforeFirst(); - while (rs.next()) { - jids.add(rs.getString(1)); - } - } catch (SQLException e) { - logger.log(Level.SEVERE, "sql exception", e); - } finally { - Utils.finishSQL(rs, stmt); + public static List getJIDSubscribedToUser(JdbcTemplate sql, int uid, boolean friendsonly) { + if (friendsonly == false) { + return sql.queryForList("SELECT jids.jid FROM subscr_users INNER JOIN jids " + + "ON (subscr_users.user_id=? AND subscr_users.suser_id=jids.user_id) WHERE jids.active=1", + String.class, uid); + } else { + return sql.queryForList("SELECT jids.jid FROM subscr_users INNER JOIN jids " + + "ON (subscr_users.user_id=? AND subscr_users.suser_id=jids.user_id) WHERE jids.active=1 " + + "AND jids.user_id IN (SELECT wl_user_id FROM wl_users WHERE user_id=?)", String.class, uid, uid); } - return jids; } public static List getSubscribedUsers(JdbcTemplate sql, int uid, int mid) { diff --git a/src/main/java/com/juick/server/TagQueries.java b/src/main/java/com/juick/server/TagQueries.java index d2159b5a..0094fc22 100644 --- a/src/main/java/com/juick/server/TagQueries.java +++ b/src/main/java/com/juick/server/TagQueries.java @@ -123,25 +123,10 @@ public class TagQueries { }, uid); } - public static List getUserBLTags(Connection sql, int uid) { - List tags = new ArrayList<>(); - - PreparedStatement stmt = null; - ResultSet rs = null; - try { - stmt = sql.prepareStatement("SELECT tags.name FROM tags INNER JOIN bl_tags ON (bl_tags.user_id=? AND bl_tags.tag_id=tags.tag_id) ORDER BY tags.name"); - stmt.setInt(1, uid); - rs = stmt.executeQuery(); - rs.beforeFirst(); - while (rs.next()) { - tags.add(rs.getString(1)); - } - } catch (SQLException e) { - LOGGER.log(Level.SEVERE, "sql exception", e); - } finally { - Utils.finishSQL(rs, stmt); - } - return tags; + public static List getUserBLTags(JdbcTemplate sql, int uid) { + return sql.queryForList("SELECT tags.name FROM tags INNER JOIN bl_tags " + + "ON (bl_tags.user_id=? AND bl_tags.tag_id=tags.tag_id) ORDER BY tags.name", + String.class, uid); } public static List getPopularTags(JdbcTemplate sql) { diff --git a/src/main/java/com/juick/server/UserQueries.java b/src/main/java/com/juick/server/UserQueries.java index 0d6a3485..e7aee0f5 100644 --- a/src/main/java/com/juick/server/UserQueries.java +++ b/src/main/java/com/juick/server/UserQueries.java @@ -25,14 +25,11 @@ import org.springframework.jdbc.support.GeneratedKeyHolder; import org.springframework.jdbc.support.KeyHolder; import org.springframework.util.StringUtils; -import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.*; -import java.util.logging.Level; -import java.util.logging.Logger; /** * @@ -42,8 +39,6 @@ public class UserQueries { static final String ABCDEF = "0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ"; - private static final Logger LOGGER = Logger.getLogger(UserQueries.class.getName()); - public static class UserMapper implements RowMapper { @Override public User mapRow(ResultSet rs, int rowNum) throws SQLException { @@ -163,8 +158,8 @@ public class UserQueries { }); } - public static String getJIDbyUID(JdbcTemplate sql, int uid) { - return sql.queryForObject("SELECT jid FROM jids WHERE user_id=? AND active=1", String.class, uid); + public static List getJIDsbyUID(JdbcTemplate sql, int uid) { + return sql.queryForList("SELECT jid FROM jids WHERE user_id=? AND active=1", String.class, uid); } public static int getUIDbyJID(JdbcTemplate sql, String jid) { @@ -241,58 +236,26 @@ public class UserQueries { } } - public static int getUserOptionInt(Connection sql, int uid, String option, int defaultValue) { - int ret = defaultValue; - - PreparedStatement stmt = null; - ResultSet rs = null; + public static int getUserOptionInt(JdbcTemplate sql, int uid, String option, int defaultValue) { try { - stmt = sql.prepareStatement("SELECT " + option + " FROM useroptions WHERE user_id=?"); - stmt.setInt(1, uid); - rs = stmt.executeQuery(); - if (rs.first()) { - ret = rs.getInt(1); - } - } catch (SQLException e) { - LOGGER.log(Level.SEVERE, "sql exception", e); - } finally { - Utils.finishSQL(rs, stmt); + return sql.queryForObject("SELECT " + option + " FROM useroptions WHERE user_id=?", Integer.class, uid); + } catch (EmptyResultDataAccessException e) { + return defaultValue; } - return ret; } - public static void setUserOptionInt(Connection sql, int uid, String option, int value) { - PreparedStatement stmt = null; - try { - stmt = sql.prepareStatement("UPDATE useroptions SET " + option + "=? WHERE user_id=?"); - stmt.setInt(1, value); - stmt.setInt(2, uid); - stmt.executeUpdate(); - } catch (SQLException e) { - LOGGER.log(Level.SEVERE, "sql exception", e); - } finally { - Utils.finishSQL(null, stmt); - } + public static void setUserOptionInt(JdbcTemplate sql, int uid, String option, int value) { + sql.update("UPDATE useroptions SET " + option + "=? WHERE user_id=?", value, uid); } - public static boolean getCanMedia(Connection sql, int uid) { - boolean ret = false; - - PreparedStatement stmt = null; - ResultSet rs = null; + public static boolean getCanMedia(JdbcTemplate sql, int uid) { try { - stmt = sql.prepareStatement("SELECT users.lastphoto-UNIX_TIMESTAMP() FROM users WHERE id=?"); - stmt.setInt(1, uid); - rs = stmt.executeQuery(); - if (rs.first()) { - ret = rs.getInt(1) < 3600; - } - } catch (SQLException e) { - LOGGER.log(Level.SEVERE, "sql exception", e); - } finally { - Utils.finishSQL(rs, stmt); + int res = sql.queryForObject("SELECT users.lastphoto-UNIX_TIMESTAMP() FROM users WHERE id=?", + Integer.class, uid); + return res < 3600; + } catch (EmptyResultDataAccessException e) { + return false; } - return ret; } public static boolean isInWL(JdbcTemplate sql, int uid, int check) { @@ -300,25 +263,13 @@ public class UserQueries { new Object[] {uid, check}, Integer.class) == 1; } - public static boolean isInBL(Connection sql, int uid, int check) { - boolean ret = false; - - PreparedStatement stmt = null; - ResultSet rs = null; + public static boolean isInBL(JdbcTemplate sql, int uid, int check) { try { - stmt = sql.prepareStatement("SELECT 1 FROM bl_users WHERE user_id=? AND bl_user_id=?"); - stmt.setInt(1, uid); - stmt.setInt(2, check); - 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 sql.queryForObject("SELECT 1 FROM bl_users WHERE user_id=? AND bl_user_id=?", + Integer.class, uid, check) == 1; + } catch (EmptyResultDataAccessException e) { + return false; } - return ret; } public static boolean isInBLAny(JdbcTemplate sql, int uid, int uid2) { @@ -353,33 +304,20 @@ public class UserQueries { return sql.queryForList("SELECT user_id FROM subscr_users WHERE suser_id=?", Integer.class, uid); } - public static List getUserReadLeastPopular(Connection sql, int uid, int cnt) { - List users = new ArrayList<>(cnt); - - PreparedStatement stmt = null; - ResultSet rs = null; - try { - stmt = sql.prepareStatement("SELECT users.id,users.nick FROM (subscr_users INNER JOIN users_subscr ON (subscr_users.suser_id=? AND subscr_users.user_id=users_subscr.user_id)) INNER JOIN users ON subscr_users.user_id=users.id ORDER BY cnt LIMIT ?"); - stmt.setInt(1, uid); - stmt.setInt(2, cnt); - rs = stmt.executeQuery(); - rs.beforeFirst(); - while (rs.next()) { - com.juick.User u = new com.juick.User(); - u.setUID(rs.getInt(1)); - u.setUName(rs.getString(2)); - users.add(u); - } - } catch (SQLException e) { - LOGGER.log(Level.SEVERE, "sql exception", e); - } finally { - Utils.finishSQL(rs, stmt); - } - - return users; + public static List getUserReadLeastPopular(JdbcTemplate sql, int uid, int cnt) { + return sql.query("SELECT users.id,users.nick FROM (subscr_users " + + "INNER JOIN users_subscr ON (subscr_users.suser_id=? " + + "AND subscr_users.user_id=users_subscr.user_id)) INNER JOIN users " + + "ON subscr_users.user_id=users.id ORDER BY cnt LIMIT ?", + (rs, num) -> { + com.juick.User u = new com.juick.User(); + u.setUID(rs.getInt(1)); + u.setUName(rs.getString(2)); + return u; + }, uid, cnt); } - public List getUserReaders(JdbcTemplate sql, int uid) { + public static List getUserReaders(JdbcTemplate sql, int uid) { return sql.query("SELECT users.id, users.nick FROM subscr_users " + "INNER JOIN users ON subscr_users.suser_id=users.id " + "WHERE subscr_users.user_id=? ORDER BY users.nick", @@ -391,7 +329,7 @@ public class UserQueries { }, uid); } - public List getUserFriends(JdbcTemplate sql, int uid) { + public static List getUserFriends(JdbcTemplate sql, int uid) { return sql.query("SELECT users.id,users.nick FROM subscr_users " + "INNER JOIN users ON subscr_users.user_id=users.id " + "WHERE subscr_users.suser_id=? AND users.id!=? " + @@ -404,72 +342,60 @@ public class UserQueries { }, uid, uid); } - public static List getUserBLUsers(Connection sql, int uid) { - List users = new ArrayList<>(); - - PreparedStatement stmt = null; - ResultSet rs = null; - try { - stmt = sql.prepareStatement("SELECT users.id,users.nick FROM users INNER JOIN bl_users ON(bl_users.bl_user_id=users.id) WHERE bl_users.user_id=? ORDER BY users.nick"); - stmt.setInt(1, uid); - rs = stmt.executeQuery(); - rs.beforeFirst(); - while (rs.next()) { - com.juick.User u = new com.juick.User(); - u.setUID(rs.getInt(1)); - u.setUName(rs.getString(2)); - users.add(u); - } - } catch (SQLException e) { - LOGGER.log(Level.SEVERE, "sql exception", e); - } finally { - Utils.finishSQL(rs, stmt); - } - - return users; + public static List getUserBLUsers(JdbcTemplate sql, int uid) { + return sql.query("SELECT users.id,users.nick FROM users INNER JOIN bl_users " + + "ON(bl_users.bl_user_id=users.id) WHERE bl_users.user_id=? ORDER BY users.nick", + (rs, num) -> { + com.juick.User u = new com.juick.User(); + u.setUID(rs.getInt(1)); + u.setUName(rs.getString(2)); + return u; + }, uid); } - public static boolean linkTwitterAccount(Connection sql, User user, String accessToken, + public static boolean linkTwitterAccount(JdbcTemplate sql, User user, String accessToken, String accessTokenSecret, String screenName) { - try { - PreparedStatement stmt = - sql.prepareStatement("INSERT INTO twitter(user_id,access_token,access_token_secret,uname) " + - "VALUES (?,?,?,?)" + - " ON DUPLICATE KEY UPDATE access_token=?,access_token_secret=?,uname=?"); - stmt.setInt(1, user.getUID()); - stmt.setString(2, accessToken); - stmt.setString(3, accessTokenSecret); - stmt.setString(4, screenName); - stmt.setString(5, accessToken); - stmt.setString(6, accessTokenSecret); - stmt.setString(7, screenName); - if (stmt.execute()) { - PreparedStatement stmt2 = - sql.prepareStatement("INSERT INTO subscr_users(user_id,suser_id,jid) " + - "VALUES (?,1741,'juick\\@twitter.juick.com')"); - stmt2.setInt(1, user.getUID()); - return stmt2.execute(); - } - } catch (SQLException e) { - LOGGER.log(Level.SEVERE, "sql exception", e); + if (sql.update("INSERT INTO twitter(user_id,access_token,access_token_secret,uname) " + + "VALUES (?,?,?,?)" + + " ON DUPLICATE KEY UPDATE access_token=?,access_token_secret=?,uname=?", + user.getUID(), accessToken, accessTokenSecret, screenName, accessToken, accessTokenSecret, screenName) > 0) { + return sql.update("INSERT INTO subscr_users(user_id,suser_id,jid) " + + "VALUES (?,1741,'juick\\@twitter.juick.com')", user.getUID()) > 0; } return false; + } - public static int getStatsIRead(Connection sql, int uid) { - return SQLHelpers.getInt(sql, "SELECT COUNT(*) FROM subscr_users WHERE suser_id=?", uid, 0); + public static int getStatsIRead(JdbcTemplate sql, int uid) { + try { + return sql.queryForObject("SELECT COUNT(*) FROM subscr_users WHERE suser_id=?", Integer.class, uid); + } catch (EmptyResultDataAccessException e) { + return 0; + } } - public static int getStatsMyReaders(Connection sql, int uid) { - return SQLHelpers.getInt(sql, "SELECT COUNT(*) FROM subscr_users WHERE user_id=?", uid, 0); + public static int getStatsMyReaders(JdbcTemplate sql, int uid) { + try { + return sql.queryForObject("SELECT COUNT(*) FROM subscr_users WHERE user_id=?", Integer.class, uid); + } catch (EmptyResultDataAccessException e) { + return 0; + } } - public static int getStatsMessages(Connection sql, int uid) { - return SQLHelpers.getInt(sql, "SELECT COUNT(*) FROM messages WHERE user_id=?", uid, 0); + public static int getStatsMessages(JdbcTemplate sql, int uid) { + try { + return sql.queryForObject("SELECT COUNT(*) FROM messages WHERE user_id=?", Integer.class, uid); + } catch (EmptyResultDataAccessException e) { + return 0; + } } - public static int getStatsReplies(Connection sql, int uid) { - return SQLHelpers.getInt(sql, "SELECT COUNT(*) FROM replies WHERE user_id=?", uid, 0); + public static int getStatsReplies(JdbcTemplate sql, int uid) { + try { + return sql.queryForObject("SELECT COUNT(*) FROM replies WHERE user_id=?", Integer.class, uid); + } catch (EmptyResultDataAccessException e) { + return 0; + } } public enum ActiveStatus { -- cgit v1.2.3 From 797942249b0d5e38285e806bc0e73e755e6ffede Mon Sep 17 00:00:00 2001 From: Vitaly Takmazov Date: Wed, 29 Jun 2016 15:09:47 +0300 Subject: add more bot queries --- .../java/com/juick/server/MessagesQueries.java | 11 ++++ .../com/juick/server/SubscriptionsQueries.java | 22 ++++++++ src/main/java/com/juick/server/TagQueries.java | 65 ++++++++++++++++------ 3 files changed, 81 insertions(+), 17 deletions(-) (limited to 'src/main/java/com/juick/server/TagQueries.java') diff --git a/src/main/java/com/juick/server/MessagesQueries.java b/src/main/java/com/juick/server/MessagesQueries.java index cd098ec6..880e94e2 100644 --- a/src/main/java/com/juick/server/MessagesQueries.java +++ b/src/main/java/com/juick/server/MessagesQueries.java @@ -645,4 +645,15 @@ public class MessagesQueries { return sql.update("UPDATE messages SET privacy=1 WHERE message_id=?", mid) > 0; } + public static boolean deleteMessage(JdbcTemplate sql, int uid, int mid) { + if (sql.update("DELETE FROM messages WHERE message_id=? AND user_id=?", mid, uid) > 0) { + sql.update("DELETE FROM messages_txt WHERE message_id=?", mid); + sql.update("DELETE FROM replies WHERE message_id=?", mid); + sql.update("DELETE FROM subscr_messages WHERE message_id=?", mid); + sql.update("DELETE FROM messages_tags WHERE message_id=?", mid); + return true; + } + return false; + } + } diff --git a/src/main/java/com/juick/server/SubscriptionsQueries.java b/src/main/java/com/juick/server/SubscriptionsQueries.java index feebfb22..9f0372ff 100644 --- a/src/main/java/com/juick/server/SubscriptionsQueries.java +++ b/src/main/java/com/juick/server/SubscriptionsQueries.java @@ -4,6 +4,7 @@ */ package com.juick.server; +import com.juick.Tag; import com.juick.User; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.util.StringUtils; @@ -78,4 +79,25 @@ public class SubscriptionsQueries { public static boolean subscribeMessage(JdbcTemplate sql, int mid, int vuid) { return sql.update("INSERT IGNORE INTO subscr_messages(suser_id,message_id) VALUES (" + vuid + "," + mid + ")") == 1; } + public static boolean unSubscribeMessage(JdbcTemplate sql, int mid, int vuid) { + return sql.update("DELETE FROM subscr_messages WHERE message_id=? AND suser_id=?", + mid, vuid) > 0; + } + public static boolean subscribeUser(JdbcTemplate sql, User user, User toUser) { + return sql.update("INSERT IGNORE INTO subscr_users(user_id,suser_id) VALUES (?,?)", + user.getUID(), toUser.getUID()) == 1; + } + public static boolean unSubscribeUser(JdbcTemplate sql, User user, User fromUser) { + return sql.update("DELETE FROM subscr_users WHERE suser_id=? AND user_id=?", + user.getUID(), fromUser.getUID()) > 0; + } + public static boolean subscribeTag(JdbcTemplate sql, User user, Tag toTag) { + return sql.update("INSERT IGNORE INTO subscr_tags(tag_id,suser_id) VALUES (?,?)", + toTag.TID, user.getUID()) == 1; + } + public static boolean unSubscribeTag(JdbcTemplate sql, User user, Tag toTag) { + return sql.update("DELETE FROM subscr_tags WHERE tag_id=? AND suser_id=?", + toTag.TID, user.getUID()) > 0; + } + } diff --git a/src/main/java/com/juick/server/TagQueries.java b/src/main/java/com/juick/server/TagQueries.java index 0094fc22..2258a8ea 100644 --- a/src/main/java/com/juick/server/TagQueries.java +++ b/src/main/java/com/juick/server/TagQueries.java @@ -20,18 +20,17 @@ package com.juick.server; import com.juick.Tag; import org.springframework.dao.EmptyResultDataAccessException; import org.springframework.jdbc.core.JdbcTemplate; +import org.springframework.jdbc.support.GeneratedKeyHolder; +import org.springframework.jdbc.support.KeyHolder; -import java.sql.Connection; import java.sql.PreparedStatement; -import java.sql.ResultSet; -import java.sql.SQLException; import java.sql.Statement; import java.util.ArrayList; +import java.util.Collections; 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; +import java.util.regex.Matcher; +import java.util.regex.Pattern; +import java.util.stream.Collectors; /** * @@ -39,17 +38,19 @@ import org.springframework.jdbc.support.KeyHolder; */ public class TagQueries { - private static final Logger LOGGER = Logger.getLogger(TagQueries.class.getName()); - public static com.juick.Tag getTag(JdbcTemplate sql, int tid) { - return sql.queryForObject("SELECT synonym_id,name FROM tags WHERE tag_id=?", - new Object[]{tid}, (rs, num) -> { - Tag ret = new Tag(); - ret.TID = tid; - ret.SynonymID = rs.getInt(1); - ret.Name = rs.getString(2); - return ret; - }); + try { + return sql.queryForObject("SELECT synonym_id,name FROM tags WHERE tag_id=?", + new Object[]{tid}, (rs, num) -> { + Tag ret = new Tag(); + ret.TID = tid; + ret.SynonymID = rs.getInt(1); + ret.Name = rs.getString(2); + return ret; + }); + } catch (EmptyResultDataAccessException e) { + return null; + } } public static com.juick.Tag getTag(JdbcTemplate sql, String tag, boolean autoCreate) { @@ -132,4 +133,34 @@ public class TagQueries { public static List getPopularTags(JdbcTemplate sql) { return sql.queryForList("SELECT name FROM tags WHERE top=1 ORDER BY name ASC", String.class); } + public static List updateTags(JdbcTemplate sql, int mid, List newTags) { + List currentTags = MessagesQueries.getMessageTags(sql, mid); + newTags.stream().filter(currentTags::contains) + .forEach(t -> sql.update("DELETE FROM messages_tags WHERE message_id=? AND tag_id=?", mid, t.TID)); + newTags.stream().filter(t -> !currentTags.contains(t)) + .forEach(t -> sql.update("INSERT INTO messages_tags(message_id,tag_id) VALUES (?,?)", mid, t.TID)); + return MessagesQueries.getMessageTags(sql, mid); + } + + public static List fromString(JdbcTemplate sql, String txt, boolean tagsOnly) { + String patternString = tagsOnly ? "^(?:(?:\\*[^ \\r\\n\\t]+)|\\s)+$" : "^\\*([^ \\r\\n\\t]+)\\s+([\\s\\S]+)"; + Pattern tagsPattern = Pattern.compile(patternString); + if (tagsPattern.matcher(txt).matches()) { + Pattern tagPattern = Pattern.compile("\\*([^ \\r\\n\\t]+)"); + Matcher tagMatcher = tagPattern.matcher(txt); + List tags = new ArrayList<>(); + // TODO: process readonly, private, friends, public + while (tagMatcher.find()) { + for (int i = 1; i <= tagMatcher.groupCount(); i++) { + tags.add(getTag(sql, tagMatcher.group(i), true)); + } + } + return tags; + } + return Collections.emptyList(); + } + public static String toString(List tags) { + return tags.stream().map(t -> " *" + t.Name) + .collect(Collectors.joining()); + } } -- cgit v1.2.3