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.

score 1 · Answer 1 · answered Sep 10 '17 at 16:18

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.

answered Sep 10 '17 at 16:18

ipolevoy

5,432
2
31
46

Actually, on the first pull, the limit is 3 and offset is 0 – kittu Sep 10 '17 at 18:04
Setting offset+limit on second pull doesn't fetch data at all. I have about 6 records in DB. Offset on second pull is 3(offset) + 3(limit) – kittu Sep 10 '17 at 18:16
post the structure and contents of your table here, so I could replicate it. – ipolevoy Sep 10 '17 at 18:20
also, turn on logging: http://javalite.io/logging and post the SQL generated when you are running the app – ipolevoy Sep 10 '17 at 22:58
updtated the question. Added log4j as in the docs. How do I generate sql while running the app? – kittu Sep 11 '17 at 05:11
Please, see my earlier comment. Additionally, in earler comment, I asked for content of table. Please, do not include a picture of a table, include a DDL to create a table, and SQL to fill it with data. – ipolevoy Sep 11 '17 at 06:27
There is no sql generated. Added log4j – kittu Sep 11 '17 at 06:28
Updated the question with create and insert sql – kittu Sep 11 '17 at 18:18
Fixed the issue. It was actually the issue with `orderBy` Thanks ipolevoy :) – kittu Sep 11 '17 at 19:44
1

glad it worked out for you. Given that this framework is about 8 years old, I would really be surprized if there are logical errors :) – ipolevoy Sep 11 '17 at 20:25

Getting records with offset and orderBy in activeJDBC

1 Answers1