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

mysql-connector-java
mysql-connector-java-8.0.11.jar download
mysql connector version compatibility
mysql connector-java 8.0 19
mysql-connector-java maven
mysql connector-java 8.0 18
java mysql tutorial
mysql-connector-java version compatibility

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:

40960
17167
13854
10240

With streams:

303584
118060
47284
40357

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?

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)

MySQL Java tutorial, Connector/J is a JDBC Type 4 driver. Different versions are available that are compatible with the JDBC 3.0 and JDBC 4. The short is equivalent to MySQL’s small int. The Java short takes 2 bytes that has the range -32768 to 32767 while MySQL smallint also take 2 bytes with same range. Here is the demo code of short in Java −

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());

MySQL :: MySQL Connector/J 5.1 Developer Guide :: 1 Overview of , Connector/J 5.1 is also a Type 4 pure Java JDBC driver that conforms to the Connector/J version, JDBC version, MySQL Server version, JRE Required need a customized JSSE implementation to use TLSv1.3 on Oracle's Java 8 platform. How to append data to a file in Java? Can we have integers as elements of an enum in Java? Does SQL Server have an equivalent to MySQL's ENUM data type?

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

2 Connector/J Versions, and the MySQL and Java Versions They , Connector/J 5.1 is a Type 4 pure Java JDBC driver, which conforms to the JDBC Connector/J version, JDBC version, MySQL Server version, JRE Required using the source distribution, you must have both JDK 8.0 or higher AND JDK 5.0​  Data types of MySQL and Java programming language are not same, its need some mechanism for transferring data between an database using MySQL data types and a application using Java data types.

MySQL and Java JDBC - Tutorial, To connect to MySQL in Java, MySQL provides MySQL Connector/J, a driver that implements the JDBC API. MySQL Connector/J is a JDBC Type 4 driver. The Type 4 designation means that the driver is a pure Java implementation of the MySQL protocol and does not rely on the MySQL client libraries. Create a Java project and a package called de.vogella.mysql.first. Create a lib folder and copy the JDBC driver into this folder. Add the JDBC driver to your classpath.

Get MySQL version with Java, To obtain the database version, there is a much simpler solution. Once you have a Connection to the database, you can get its metadata and  MySQL Connector/J. To connect to MySQL in Java, MySQL provides MySQL Connector/J, a driver that implements the JDBC API. MySQL Connector/J is a JDBC Type 4 driver. The Type 4 designation means that the driver is a pure Java implementation of the MySQL protocol and does not rely on the MySQL client libraries.

Java connect to MySQL database with JDBC, If you have never written Java code to connect MySQL before, But only one file we need is the JAR file mysql-connector-java-VERSION.jar. As with Oracle, MySQL users are maintained by the database. MySQL uses a set of grant tables to keep track of users and the privileges that they can have. MySQL uses these grant tables when performing authentication, authorization and access control for users.

Comments
  • If you are using Guava, you might alternatively use Lists.partition.
  • Your benchmark is way too naive. Please see: stackoverflow.com/questions/504103/…
  • @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).
  • @JonathanLaliberte yes.
  • @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.
  • @JonathanLaliberte See also: stackoverflow.com/questions/504103/…
  • 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. So if you need an independent list, you have to create a copy of the sublist (new ArrayList<>(...) or similar).
  • For this answer to be useful, further explanation would greatly assist.