diff options
Diffstat (limited to 'src/main')
| -rw-r--r-- | src/main/java/com/juick/service/BaseJdbcService.java | 17 | ||||
| -rw-r--r-- | src/main/java/com/juick/service/MessagesServiceImpl.java | 6 | ||||
| -rw-r--r-- | src/main/java/com/juick/service/UserServiceImpl.java | 2 |
3 files changed, 15 insertions, 10 deletions
diff --git a/src/main/java/com/juick/service/BaseJdbcService.java b/src/main/java/com/juick/service/BaseJdbcService.java index ab2e3fc8..d16dadd2 100644 --- a/src/main/java/com/juick/service/BaseJdbcService.java +++ b/src/main/java/com/juick/service/BaseJdbcService.java @@ -39,18 +39,18 @@ public class BaseJdbcService { @Inject NamedParameterJdbcTemplate namedParameterJdbcTemplate; @Value("#{new Boolean('${spring.sql.init.platform}' == 'sqlserver')}") - protected boolean omitRecursiveKeyword; + private boolean omitRecursiveKeyword; @Value("#{('${spring.sql.init.platform}' == 'sqlite') or ('${spring.sql.init.platform}' == 'mysql')}") // Added in MariaDB 10.6 - protected boolean haveNoANSIFetch; + private boolean haveNoANSIFetch; @Value("#{new Boolean('${spring.sql.init.platform}' == 'sqlite')}") - protected boolean haveNoDates; + private boolean haveNoDates; @Value("#{new Boolean('${spring.sql.init.platform}' == 'sqlite')}") - protected boolean haveNoGreatest; + private boolean haveNoGreatest; @Value("#{new Boolean('${spring.sql.init.platform}' == 'sqlite')}") - protected boolean haveNoForUpdate; + private boolean haveNoForUpdate; @Value("#{new Boolean('${spring.sql.init.platform}' == 'mysql')}") - protected boolean haveNoOffsetDateTime; + private boolean haveNoOffsetDateTime; public NamedParameterJdbcTemplate getNamedParameterJdbcTemplate() { return namedParameterJdbcTemplate; @@ -59,6 +59,11 @@ public class BaseJdbcService { public JdbcTemplate getJdbcTemplate() { return jdbcTemplate; } + + public String withRecursive() { + return omitRecursiveKeyword ? "WITH" : "WITH RECURSIVE"; + } + protected String limit(int rows) { if (haveNoANSIFetch) { return "LIMIT " + rows; diff --git a/src/main/java/com/juick/service/MessagesServiceImpl.java b/src/main/java/com/juick/service/MessagesServiceImpl.java index eb1acd59..c5b29fd5 100644 --- a/src/main/java/com/juick/service/MessagesServiceImpl.java +++ b/src/main/java/com/juick/service/MessagesServiceImpl.java @@ -789,8 +789,8 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ @Override public List<Message> getMessages(int uid, final List<Integer> mids) { if (CollectionUtils.isNotEmpty(mids)) { - var query = (omitRecursiveKeyword ? "WITH " : "WITH RECURSIVE ") + """ - banned(message_id, reply_id) + var query = withRecursive() + """ + banned(message_id, reply_id) AS (SELECT message_id, reply_id FROM replies WHERE replies.message_id IN (:ids) AND (EXISTS (SELECT 1 FROM bl_users b WHERE b.user_id = :uid AND b.bl_user_id = replies.user_id) OR EXISTS (SELECT 1 from users u WHERE u.banned = 1 and u.id = replies.user_id and u.id <> :uid)) @@ -882,7 +882,7 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ @Transactional @Override public List<Message> getReplies(final User user, final int mid) { - List<Message> replies = getNamedParameterJdbcTemplate().query((omitRecursiveKeyword ? "WITH " : "WITH RECURSIVE ") + "banned(reply_id, user_id) AS (" + List<Message> replies = getNamedParameterJdbcTemplate().query(withRecursive() + " banned(reply_id, user_id) AS (" + "SELECT reply_id, user_id FROM replies " + "WHERE replies.message_id = :mid " + "AND EXISTS (SELECT 1 FROM bl_users b WHERE b.user_id = :uid AND b.bl_user_id = replies.user_id) " + "UNION ALL SELECT replies.reply_id, replies.user_id FROM replies " diff --git a/src/main/java/com/juick/service/UserServiceImpl.java b/src/main/java/com/juick/service/UserServiceImpl.java index 8ed8fd96..57fcd399 100644 --- a/src/main/java/com/juick/service/UserServiceImpl.java +++ b/src/main/java/com/juick/service/UserServiceImpl.java @@ -391,7 +391,7 @@ public class UserServiceImpl extends BaseJdbcService implements UserService { @Transactional(readOnly = true) @Override public boolean isReplyToBL(final User user, final Message reply) { - var replies = getNamedParameterJdbcTemplate().queryForList((omitRecursiveKeyword ? "WITH " : "WITH RECURSIVE ") + "banned(reply_id, user_id) AS (" + + var replies = getNamedParameterJdbcTemplate().queryForList(withRecursive() + " banned(reply_id, user_id) AS (" + "SELECT reply_id, user_id FROM replies " + "WHERE replies.message_id = :mid " + "AND EXISTS (SELECT 1 FROM bl_users b WHERE b.user_id = :uid AND b.bl_user_id = replies.user_id) " + |