Getting records with offset and orderBy in activeJDBC

Question

I get duplicate records using:

List<Post> postList = Post.findAll().limit(Long.valueOf(request.queryParams("limit"))).offset(Long.valueOf(request.queryParams("offset"))).orderBy("id desc");

When I remove orderBy it works fine.

• First pull: limit is 3 offset is 0
• Second pull: limit is 3 offset is 4 //I get duplicates on second pull

Why is it so?

update:

Table Structure:

CREATE TABLE `post` (
  `id` mediumint(15) NOT NULL AUTO_INCREMENT,
  `title` text,
  `details` text,
  `created_at` text,
  `username` varchar(45) DEFAULT NULL,
  `userImage` text,
  `url` varchar(1000) DEFAULT NULL,
  `article_id` text NOT NULL,
  `postImageUrl` text,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=243 DEFAULT CHARSET=utf8;

INSERT INTO `lt9vgms366ueidoa`.`post`
(`id`,
`title`,
`details`,
`created_at`,
`username`,
`userImage`,
`url`,
`article_id`,
`postImageUrl`)
VALUES
(<{id: }>,
<{title: "Test"}>,
<{details: "Test"}>,
<{created_at: }>,
<{username: "Test"}>,
<{userImage: }>,
<{url: "Test"}>,
<{article_id: "121"}>,
<{postImageUrl: }>);

Request mapping:

get("/get_data_on_scrollEnd", (Request request, Response response) -> {
    System.out.println("ON SCROLL END -- LIMIT: "+ request.queryParams("limit") + " " + "OFFSET: "+ request.queryParams("offset"));
    List<Post> postList = Post.findAll().limit(Long.valueOf(request.queryParams("limit"))).offset(Long.valueOf(request.queryParams("offset")));
    System.out.println("///////////////////////");
    log.info("PAGINATION: " + postList);
    System.out.println("///////////////////////");
    return postList;
}, new JsonTransformer());

The returned List<Post> postList is empty.

Answer 1

Because your offset on the second pull needs to be offset + limit.

Also, you might want to look into a Paginator which was built to page through results on web pages.

On the off-topic, the way you parse long values from the parameters is vulnerable to runtime exceptions if your customers start messing up your URL.