Does java have an equivalent of mysql's limit and offset for arraylists?

Question

In MySQL you can do something like this:

select * from sometable order by id desc limit 3 offset 0;

Which would return the first 3 results. How is something like this achievable in java?

If I have an ArrayList of an odd amount of elements:

ArrayList<String> ids = new ArrayList<String>();
ids.add("1");
ids.add("2");
ids.add("3");
ids.add("4");
ids.add("5");
ids.add("6");
ids.add("7");
ids.add("8");
ids.add("9");
ids.add("10");
ids.add("11");

How would one go about getting only 3 results from this ArrayList for each offset (or less than 3 if there are no more elements left)?

for example, say if the limit is always 3 and offset = 0:

it should spit out 1,2,3

if offset = 3:

4,5,6

offset = 6:

7,8,9

offset = 9:

10,11

The way i'm currently doing it is by creating a subList of the list:

int endOf = offset+3;
ArrayList<String> ids2 = new ArrayList<String>(ids.subList(offset, endOf));

But it breaks when the offset is larger than the size of ids...

If this can't be done with arraylists is there a better way to do it?

EDIT:

Based on the two answers, Andy's method seemed to perform better:

long startTime = System.nanoTime();
 //tried each method here
long stopTime = System.nanoTime();
System.out.println(stopTime - startTime);

Without streams:

With streams:

EDIT2:

The benchmark test above by no means should be relied on. For more info why see here: How do I write a correct micro-benchmark in Java?

If you are using Guava, you might alternatively use [`Lists.partition`](https://google.github.io/guava/releases/23.0/api/docs/com/google/common/collect/Lists.html#partition-java.util.List-int-). — Andy Turner, Jun 10 '18 at 18:27
Your benchmark is way too naive. Please see: https://stackoverflow.com/questions/504103/how-do-i-write-a-correct-micro-benchmark-in-java — lexicore, Jun 10 '18 at 19:36

Mureinik · Answer 1 · 2021-06-30T12:51:16.817

9

This can be done with streams:

List<Integer> result = 
    ids.stream()
       .skip(0)  // Equivalent to SQL's offset
       .limit(3) // Equivalent to SQL's limit
       .collect(Collectors.toList());

edited Jun 30 '21 at 12:51

answered Jun 10 '18 at 18:13

Mureinik

297,002
52
306
350

score 7 · Accepted Answer · answered Jun 10 '18 at 18:16

7

Just clamp the parameters of subList:

list.subList(
    Math.min(list.size(), offset),
    Math.min(list.size(), offset + limit))

If you find the syntax inconvenient, write a helper method, e.g.

<T> List<T> clampedSublist(List<T> list, int offset, int limit)

answered Jun 10 '18 at 18:16

Andy Turner

137,514
11
162
243

great solution too, but the stream example seems like the easier way to go in this case. Thanks! – Jonathan Laliberte Jun 10 '18 at 18:24
2

@JonathanLaliberte one thing to bear in mind is that the stream approach creates a new list, whereas this just returns a view of the list, i.e. O(lim) storage Vs O(1). – Andy Turner Jun 10 '18 at 18:45
Would you say this method is more efficient then? – Jonathan Laliberte Jun 10 '18 at 18:46
1

@JonathanLaliberte yes. – Andy Turner Jun 10 '18 at 18:46
Well i guess i'll mark this answer as the correct one considering it's the most efficient and doesn't require changing the pre-existing method. Cheers! – Jonathan Laliberte Jun 10 '18 at 18:54
1

@JonathanLaliberte don't simply take my word for it though. Measure the difference in your application. If there is no significant difference, or the difference doesn't matter, choose the one you find easiest. – Andy Turner Jun 10 '18 at 18:56
The difference should always matter, even if it's almost negligible. I ran a small test on the two methods and this method was indeed the one that performed the fastest. (as shown in the edit of my question) – Jonathan Laliberte Jun 10 '18 at 19:07
2

@JonathanLaliberte See also: https://stackoverflow.com/questions/504103/how-do-i-write-a-correct-micro-benchmark-in-java – lexicore Jun 10 '18 at 19:35
@lexicore thanks, that answer is gold. Edited my question. – Jonathan Laliberte Jun 10 '18 at 19:45
1

One thing you also have to consider is that `subList` gives you a *view* of the original list. From the JavaDoc: *The returned list is backed by this list, so non-structural changes in the returned list are reflected in this list, and vice-versa.* See [this question](https://stackoverflow.com/questions/31540730/why-changes-in-sublist-are-reflected-in-the-original-list). So if you need an independent list, you have to create a copy of the sublist (`new ArrayList<>(...)` or similar). – lexicore Jun 10 '18 at 19:51

score 1 · Answer 3 · edited Sep 20 '19 at 05:24

1

List<Integer> result = 
    ids.stream()
       .skip(0)  // Equivalent to SQL's offset
       .limit(3) // Equivalent to SQL's limit
       .collect(Collectors.toList());

skip should be pageno times limit

edited Sep 20 '19 at 05:24

Simon.S.A.

6,240
7
22
41

answered Sep 20 '19 at 04:54

Motalib Miah

19
3

1

For this answer to be useful, further explanation would greatly assist. – Simon.S.A. Sep 20 '19 at 04:56

Does java have an equivalent of mysql's limit and offset for arraylists?

3 Answers3