findRowCount doesn't work when bean has property with @Formula annotation

Question

I have following class:

@Entity
@Table(name = "clients")
public class Client extends Model {
    @Id
    public int id;

    @Formula(select = "inv.some_data", 
            join = "left join (select 1 as some_data) as inv")
    public int someData;

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

    public static int countClientsWithData() {
        return Client.find.where().gt("someData", 0).findRowCount();
    }
}

It has someData field (play framework will generate getters and setters automatically). And also countClientsWithData uses this field in where clause. Now if I do

int count = Client.countClientsWithData();

It will throw NullPointerException while trying to execute query

select count(*) from clients t0 where inv.some_data > ?

Looks like findRowCount doesn't recognize join in @Formula annotation. Any thoughts on how to work around this problem?

Updated question: narrowed down problem to findRowCount call.

Not an answer to your question and just my opinion, but if you really want to use SQL then you should go with iBatis/MyBatis. If you want to use JPA, then use JPA and avoid SQL, avoid SQL, avoid SQL. — JoeG, Sep 24 '12 at 14:28

Khaled.K · Answer 1 · 2016-03-13T22:22:38.870

So you want the count without using findRowCount() method and without fetching all the data ..

Solution: Copy the same query, and make it on the form select count(*) from .. and use it to find the count

Example:

If your query is on the form ..

Query: SELECT * FROM clients

Then this line of code Client.find.where().gt("some_data", 0).findRowCount(); will be equivlant to ..

Count Query: SELECT COUNT(*) FROM clients WHERE some_data > 0

score 0 · Answer 2 · answered Sep 24 '12 at 12:29

0

One possible way could be use findlist() method and use its size() method to get number of rows instead of findRowCount() method

return Client.find.where().gt("totalOrdersAmount", 0).findList().size();

answered Sep 24 '12 at 12:29

zaffargachal

802
7
21

Does that mean that it will fetch the whole table from database? – Poma Sep 24 '12 at 13:00
yep, it will load everything in the Java heap :-( – ndeverge Sep 24 '12 at 20:09
No it will get the group by rows after applying aggregation functions – zaffargachal Sep 25 '12 at 11:20

score 0 · Answer 3 · answered Oct 17 '12 at 16:55

0

You should debug this and see what you're missing. It's either a bug as described, or you somehow are not at all referencing what you think you are. We've all been there though, but if you can breakpoint at that line and see your object, just make sure everything is what you expected to look like. As soon as you add trying to deal with database fields and referencing them, misspellings for instance cause many headaches.

answered Oct 17 '12 at 16:55

user1701047

737
5
7

Yes, everything else works as expected because zaffargachal's answer works (except that it fetches the whole table) – Poma Oct 18 '12 at 10:33

findRowCount doesn't work when bean has property with @Formula annotation

3 Answers3