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 +++++++++--------------- 1 file changed, 42 insertions(+), 66 deletions(-) (limited to 'src/main/java/com/juick/server/PushQueries.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); + } + } -- cgit v1.2.3