How to limit the MySQL Query with Ebean in Play Framework

Question

Heys guys, i have a Problem using Play Framework. I am trying to display a large amount of Data (from this Database). When i am using the "find.all()" the Play Framework Server crashes, since it takes to much memory.

I got a DB Model named:

@Entity
public class dblp_pub_new extends Model {
[...]
    public dblp_pub_new() {}

    public static List<dblp_pub_new> all() {
        return find.all();
    }

    public String getDoi() {
        return doi;
    }

    public void setMdate() {
        this.mdate = new Date();
    }

    public static Finder<String,dblp_pub_new> find = new Finder<String, dblp_pub_new>(String.class, dblp_pub_new.class);

}

My rendering function is, which is contained in Application.java:

public static Result dois(){
    return ok(views.html.index.render(dblp_pub_new.all(), DoiForm));
}

I am trying to limit the all() Query to 50 (best would be per page). But i cant seem to figure it out. I think i need a List returned to display it on the webpage. But I can't get it to work. I would be really relieved if one of you guys (and girls) could help me figuring out this problem. I've tried it with "fetch" and "setMaxRows()" but I only get errors i can't seem to solve. If there is something unclear, please just ask and i will try to provide as much information as i can. Thank you.

have you tried using query.setFirstRow((value));? – Anirudh Oct 20 '16 at 10:40 — Anirudh, Oct 20 '16 at 10:40

score 13 · Answer 1 · answered Apr 03 '14 at 10:15

13

Using setMaxRows() should work. Try this:

dblp_pub_new.find.setMaxRows(50).findList()

By the way, you should name your class according to the Java convention : DblpPubNew. It will make your code easier to read.

answered Apr 03 '14 at 10:15

mguillermin

4,143
19
25

I have not tried this yet, but will keep an eye on it. I could not name my class according to the Java convention, since this database is already up and working (not under my "lead") and I am just trying to get access to it. But anyway, thanks for your help too! – user3493116 Apr 03 '14 at 10:57
`setMaxRows` doesn't work for `findPagingList`. Why? Are the pages lazy loaded? I don't think so..!? – myborobudur Apr 10 '14 at 14:27

score 5 · Accepted Answer · edited May 23 '17 at 11:53

5

Ebean has a helper for making pagination of data easier. It's called a PagingList there. Play's Finder helper class for Ebeans allows you to get such a PagingList for your query.

Say you want to display 50 items per page, and want to retrieve the items for the first page. Then you'd write something like this

public static List<dblp_pub_new> getpageItems(int page) {
    int pageSize = 50;
    return find.findPagingList(pageSize).getPage(page).getList();
}

Also, please note that your class name dblp_pub_new is highly unusual. Refer to this question for more information.

edited May 23 '17 at 11:53

Community

1
1

answered Apr 03 '14 at 10:18

Carsten

17,991
4
48
53

Thank you very much. That completely solves the issue. So effectiv and yet so easy. I don't know how i could not see that solution. I hope I get everything else to work ;) – user3493116 Apr 03 '14 at 10:55
1

As of July 2017, there's no findPagingList method in Finder, closest being findPagedList on which you can't set page size – Luke Jul 19 '17 at 22:18

score 0 · Answer 3 · answered Apr 03 '14 at 11:50

in addition to Carsten's answer I only mention that Page itself contains several useful methods which help i.e. building pagination menu in the view, so it's easier to use whole Page object:

public static com.avaje.ebean.Page<dblp_pub_new> getPage(int pageSize, int page) {
    return find.findPagingList(pageSize).getPage(page);
}

and then you can use it in the view like:

<ul>
  @for(item <- myPage.getList){
    <li>@item.name</li>
  }
</ul>

<b>Total page count is: @myPage.getTotalPageCount()</b>

score 0 · Answer 4 · answered May 26 '21 at 20:31

The currently accepted answer suggesting findPagingList is not valid anymore. See https://github.com/ebean-orm/ebean/issues/96

Now using setMaxRows() should work with any Query but you have to use findPagedList on that (vs. findPagingList). Something like this:

List<dblp_pub_new> list = dblp_pub_new.find.query()
   .where().eq("some_column", some_value)
   .orderBy("some_column desc")
   .setMaxRows(50)
   .findPagedList()
   .findList()

How to limit the MySQL Query with Ebean in Play Framework

4 Answers4