Improve SQL server compatibility

1 files changed, 20 insertions, 19 deletions

diff --git a/src/main/java/com/juick/service/MessagesServiceImpl.java b/src/main/java/com/juick/service/MessagesServiceImpl.java
index 4247cee5..ef458979 100644
--- a/src/main/java/com/juick/service/MessagesServiceImpl.java
+++ b/src/main/java/com/juick/service/MessagesServiceImpl.java
@@ -279,7 +279,7 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ
     void updateRepliesBy(int mid) {
         List<String> users = getJdbcTemplate().queryForList("SELECT users.nick FROM replies "
                 + "INNER JOIN users ON replies.user_id=users.id WHERE replies.message_id=? "
-                + "GROUP BY replies.user_id, users.nick ORDER BY COUNT(replies.reply_id) DESC LIMIT 5", String.class,
+                + "GROUP BY replies.user_id, users.nick ORDER BY COUNT(replies.reply_id) DESC OFFSET 0 ROWS FETCH NEXT 5 ROWS ONLY", String.class,
                 mid);
         String result = users.stream().map(u -> "@" + u).collect(Collectors.joining(","));
         getJdbcTemplate().update("UPDATE messages_txt SET repliesby=? WHERE message_id=?", result, mid);
@@ -519,7 +519,8 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ
                         + " SELECT 1 FROM bl_users b WHERE b.user_id = :visitorUid AND b.bl_user_id = m.user_id)" : ")")
                 + " AND NOT EXISTS (SELECT 1 FROM bl_tags bt WHERE bt.tag_id IN "
                 + "(SELECT tag_id FROM messages_tags WHERE message_id = m.message_id) and :visitorUid = bt.user_id)"
-                + " AND NOT EXISTS (SELECT 1 from users u WHERE u.banned = 1 and u.id = m.user_id and u.id <> :visitorUid) ORDER BY m.message_id DESC LIMIT 20",
+                + " AND NOT EXISTS (SELECT 1 from users u WHERE u.banned = 1 and u.id = m.user_id and u.id <> :visitorUid)" +
+                        " ORDER BY m.message_id DESC OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY",
                 sqlParameterSource, Integer.class);
     }
 
@@ -536,7 +537,7 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ
                         + (before > 0 ? " messages.message_id < :before AND " : StringUtils.EMPTY)
                         + "(messages.privacy > 0 OR messages.user_id = :visitorUid) "
                         + "AND NOT EXISTS (SELECT 1 FROM bl_users b WHERE b.user_id = :visitorUid and b.bl_user_id = messages.user_id) "
-                        + "ORDER BY messages.message_id DESC LIMIT :cnt", sqlParameterSource, Integer.class);
+                        + "ORDER BY messages.message_id DESC OFFSET 0 ROWS FETCH NEXT :cnt ROWS ONLY", sqlParameterSource, Integer.class);
     }
 
     @Transactional(readOnly = true)
@@ -549,7 +550,7 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ
                 + "INNER JOIN messages USING(message_id) WHERE messages_tags.tag_id IN (" + tids + ") "
                 + (before > 0 ? " AND messages.message_id < :before " : StringUtils.EMPTY)
                 + " AND (messages.privacy > 0 OR messages.user_id = :visitorUid) "
-                + "ORDER BY messages.message_id DESC LIMIT :cnt", sqlParameterSource, Integer.class);
+                + "ORDER BY messages.message_id DESC OFFSET 0 ROWS FETCH NEXT :cnt ROWS ONLY", sqlParameterSource, Integer.class);
     }
 
     @Transactional(readOnly = true)
@@ -561,7 +562,7 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ
         return getNamedParameterJdbcTemplate().queryForList(
                 "SELECT message_id FROM messages WHERE place_id = :placeId "
                         + (before > 0 ? " AND message_id < :before " : StringUtils.EMPTY)
-                        + " AND (privacy > 0 OR user_id = :visitorUid) ORDER BY message_id DESC LIMIT 20",
+                        + " AND (privacy > 0 OR user_id = :visitorUid) ORDER BY message_id DESC OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY",
                 sqlParameterSource, Integer.class);
     }
 
@@ -588,7 +589,7 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ
                         + "AND NOT EXISTS (SELECT 1 FROM bl_users b WHERE b.user_id = :uid and b.bl_user_id = messages.user_id)"
                         : StringUtils.EMPTY)
                 + ") " + (before > 0 ? "AND message_id < :before " : StringUtils.EMPTY)
-                + "ORDER BY message_id DESC LIMIT 20", sqlParameterSource, Integer.class);
+                + "ORDER BY message_id DESC OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY", sqlParameterSource, Integer.class);
     }
 
     @Transactional(readOnly = true)
@@ -600,7 +601,7 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ
         return getNamedParameterJdbcTemplate()
                 .queryForList("SELECT message_id FROM messages WHERE user_id = :uid AND privacy < 0"
                         + (before > 0 ? " AND message_id < :before " : StringUtils.EMPTY)
-                        + "ORDER BY message_id DESC LIMIT 20", sqlParameterSource, Integer.class);
+                        + "ORDER BY message_id DESC OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY", sqlParameterSource, Integer.class);
     }
 
     @Transactional(readOnly = true)
@@ -614,7 +615,7 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ
                     "SELECT message_id FROM messages WHERE " + (to != 0 ? " updated < :to AND" : StringUtils.EMPTY)
                             + " NOT EXISTS (SELECT 1 from users u WHERE u.banned = 1"
                             + " AND u.id = messages.user_id and u.id <> :uid) "
-                            + " ORDER BY updated DESC, message_id DESC LIMIT 20",
+                            + " ORDER BY updated DESC, message_id DESC OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY",
                     sqlParameterSource, (rs, rowNum) -> rs.getInt(1));
         }
         return getNamedParameterJdbcTemplate().query(
@@ -623,7 +624,7 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ
                         + "WHERE suser_id = :uid " + (to != 0 ? "AND updated < :to " : StringUtils.EMPTY)
                         + " AND NOT EXISTS (SELECT 1 from users u WHERE u.banned = 1"
                         + " AND u.id = messages.user_id and u.id <> :uid) "
-                        + "ORDER BY updated DESC, messages.message_id DESC LIMIT 20",
+                        + "ORDER BY updated DESC, messages.message_id DESC OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY",
                 sqlParameterSource, (rs, rowNum) -> rs.getInt(1));
     }
 
@@ -636,7 +637,7 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ
         return getNamedParameterJdbcTemplate().queryForList("SELECT f.message_id FROM favorites f WHERE "
                 + "EXISTS (SELECT 1 FROM subscr_users s WHERE s.suser_id = :uid and f.user_id = s.user_id)"
                 + (before > 0 ? " AND f.message_id < :before " : StringUtils.EMPTY)
-                + "ORDER BY f.message_id DESC LIMIT 20", sqlParameterSource, Integer.class);
+                + "ORDER BY f.message_id DESC OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY", sqlParameterSource, Integer.class);
     }
 
     @Transactional(readOnly = true)
@@ -652,7 +653,7 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ
                         + "(SELECT tag_id FROM messages_tags WHERE message_id = m.message_id) and :vid = bt.user_id)"
                         + " AND NOT EXISTS (SELECT 1 from users u WHERE u.banned = 1 and u.id = m.user_id and u.id <> :vid) "
                         + " AND NOT EXISTS (SELECT 1 FROM bl_users b WHERE b.user_id = :vid and b.bl_user_id = m.user_id) "
-                        + " ORDER BY m.message_id DESC LIMIT 20", sqlParameterSource, Integer.class);
+                        + " ORDER BY m.message_id DESC OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY", sqlParameterSource, Integer.class);
     }
 
     @Transactional(readOnly = true)
@@ -670,7 +671,7 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ
         return getNamedParameterJdbcTemplate().queryForList(
                 "SELECT message_id FROM messages INNER JOIN users" + " ON messages.user_id = users.id"
                         + " WHERE user_id = :uid" + (before > 0 ? " AND message_id < :before" : StringUtils.EMPTY)
-                        + " AND privacy >= :privacy AND users.banned = 0 ORDER BY message_id DESC LIMIT 20",
+                        + " AND privacy >= :privacy AND users.banned = 0 ORDER BY message_id DESC OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY",
                 sqlParameterSource, Integer.class);
     }
 
@@ -687,7 +688,7 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ
                         + " WHERE messages.user_id = :uid AND messages_tags.tag_id = :tid"
                         + (before > 0 ? " AND messages.message_id < :before " : StringUtils.EMPTY)
                         + " AND messages.privacy >= :privacy AND users.banned = 0"
-                        + " ORDER BY messages.message_id DESC LIMIT 20", sqlParameterSource, Integer.class);
+                        + " ORDER BY messages.message_id DESC OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY", sqlParameterSource, Integer.class);
     }
 
     @Transactional(readOnly = true)
@@ -706,7 +707,7 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ
                                 ? " AND ts >= :date"
                                         + " AND ts < :date"
                                 : StringUtils.EMPTY)
-                        + " AND privacy >= :privacy AND users.banned = 0 ORDER BY message_id DESC LIMIT 20",
+                        + " AND privacy >= :privacy AND users.banned = 0 ORDER BY message_id DESC OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY",
                 sqlParameterSource, Integer.class);
     }
 
@@ -728,7 +729,7 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ
                         + " AND NOT EXISTS (SELECT 1 FROM bl_tags bt WHERE bt.tag_id IN "
                         + "(SELECT tag_id FROM messages_tags WHERE message_id = favorites.message_id) and :vid = bt.user_id)"
                         + (before > 0 ? " AND messages.message_id < :before " : StringUtils.EMPTY)
-                        + " ORDER BY messages.message_id DESC LIMIT 20) as r" + " UNION ALL "
+                        + " ORDER BY messages.message_id DESC OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY) as r" + " UNION ALL "
                         + "SELECT message_id FROM "
                         + "(SELECT message_id FROM messages" + " INNER JOIN users" + " ON messages.user_id = users.id"
                         + " WHERE user_id = :uid AND users.banned = 0"
@@ -736,8 +737,8 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ
                         + " AND NOT EXISTS (SELECT 1 FROM bl_tags bt WHERE bt.tag_id IN "
                         + "(SELECT tag_id FROM messages_tags WHERE message_id = messages.message_id) and :vid = bt.user_id)"
                         + (before > 0 ? " AND messages.message_id < :before" : StringUtils.EMPTY)
-                        + " AND privacy >= :privacy ORDER BY messages.message_id DESC LIMIT 20) as m "
-                        + "ORDER BY message_id DESC LIMIT 20", sqlParameterSource, Integer.class);
+                        + " AND privacy >= :privacy ORDER BY messages.message_id DESC OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY) as m "
+                        + "ORDER BY message_id DESC OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY", sqlParameterSource, Integer.class);
     }
 
     @Transactional(readOnly = true)
@@ -749,7 +750,7 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ
         return getNamedParameterJdbcTemplate().queryForList("SELECT message_id FROM favorites" + " INNER JOIN users"
                 + " ON favorites.user_id = users.id" + " WHERE user_id = :uid AND users.banned = 0 "
                 + (before > 0 ? " AND message_id < :before " : StringUtils.EMPTY)
-                + " ORDER BY message_id DESC LIMIT 20", sqlParameterSource, Integer.class);
+                + " ORDER BY message_id DESC OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY", sqlParameterSource, Integer.class);
     }
 
     @Transactional(readOnly = true)
@@ -762,7 +763,7 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ
                 "SELECT message_id FROM messages" + " INNER JOIN users" + " ON messages.user_id = users.id"
                         + " WHERE user_id = :uid and users.banned = 0"
                         + (before > 0 ? " AND message_id < :before " : StringUtils.EMPTY)
-                        + " AND privacy >= :privacy AND attach IS NOT NULL ORDER BY message_id DESC LIMIT 20",
+                        + " AND privacy >= :privacy AND attach IS NOT NULL ORDER BY message_id DESC OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY",
                 sqlParameterSource, Integer.class);
     }