1

I'm implementing a custom pagination in Java and I'm wondering why does Query's setFirstResult() method takes int as an argument? This means that I won't be able to paginate after the rows count in the DB exceeds 2.147.483.647?

Is there a way to use long instead?

BalusC
  • 1,082,665
  • 372
  • 3,610
  • 3,555
Peter
  • 323
  • 1
  • 16
  • 46
  • 4
    serious? you are going to paginate through 2 billion records? – Scary Wombat Aug 17 '16 at 06:05
  • just in theory, e.g. I think FB has more than 2 billion posts – Peter Aug 17 '16 at 06:07
  • 1
    If you have such a huge result set, I think you need to paginate in the query conditions (which you probably have to do for performance reasons anyway). – Thilo Aug 17 '16 at 06:08
  • @Thilo my knowledge is limited but as far as I understand you can't use LIMIT(x,y) explicitly in JPQL (at least I could not) and Query solves this for you (http://stackoverflow.com/questions/3479128/limit-number-of-results-in-jpql), so basically this IS the query condition. – Peter Aug 17 '16 at 06:12
  • @Peter There's no real use case where you would be doing pagination with such a huge result set. If you think you need it, then you've made a serious design flaw somewhere. – Kayaman Aug 17 '16 at 06:13
  • a) You don't have to use JPQL b) this can be done with pagingTokens ("WHERE sortField1 >= ? AND sortField2>=? AND id > ?" ) – Thilo Aug 17 '16 at 06:14
  • @Kayaman You are quite right but there may be _very_ special cases when it's not caused by a design flaw I think. – Peter Aug 17 '16 at 06:27
  • @Thilo Thank you, I was just wondering why do I have to add unnecessary logic, and why doesn't Query support it by default. – Peter Aug 17 '16 at 06:29

1 Answers1

1

Because int is large enough by quite a margin. Considering that when using LIMIT/OFFSET it's recommended to have an ORDER BY clause to get reliable results (otherwise you might get same rows on multiple pages), with a resultset of 2 billion rows you'll be sorting the results on the server, then skipping OFFSET rows to get your page, it'll be quite inefficient even way before you get to the limits of int.

Kayaman
  • 72,141
  • 5
  • 83
  • 121
  • 1
    ... meaning to paginate huge datasets, you have to put stuff like `WHERE sortField1 >= ? AND sortField2>=? AND id > ?` into your query anyway (i.e. page manually, removing the need for OFFSET) – Thilo Aug 17 '16 at 06:36
  • @Thilo Exactomundo. – Kayaman Aug 17 '16 at 06:39
  • I guess your reasoning about ORDER BY is valid enough. Thank you both! – Peter Aug 17 '16 at 06:43
  • More like `WHERE sortField1 > ? OR (sortField1 = ? AND ( sortField2 > ? OR (sortField2 = ? AND id > ?)))` actually... Not pretty (unless you are sorting on a single and unique key). But necessary for performance. – Thilo Aug 17 '16 at 06:46
  • @Peter That's based on the last line [here](https://www.postgresql.org/docs/9.5/static/queries-limit.html). Even though they say only that it *might* be inefficient, but it's wasted space and power in any case. – Kayaman Aug 17 '16 at 06:47
  • 2
    Also, if you are paging a billion records deep, chances are that there are concurrent updates, messing up the pagination as well. – Thilo Aug 17 '16 at 06:50