JDOQL: What is the equivalent of SQL's count(*) in the JDO world?

-

Displaying large datasets

There are often times when you need to display data in a table format and the size of the potential dataset is extremely large and so ends up being represented across many pages and displayed with the usual "previous/next" buttons.

Most UI frameworks need to know upfront what is the total count of items you will be displaying so they know how many pages there are and can display some text above the table that says something like:

Items 31-40 of 397

Avoiding a big database, memory and CPU hit

What you don't want to do is force your JDO ORM (or an ORM using any Java persistence standard e.g. JPA) from instantiating ALL of the objects just so that your UI knows the total count of items to be displayed.

To do this you need to perform some kind of 'count' query. In JDO this is done by setting the result set to 'count' but count needs a parameter.

e.g.

PersistenceManager pm = getPm();

Query q = pm.newQuery(MyClass.class);

        // Set up any filtering here ...
        q.setFilter("value > 300");


q.setResult("count(batch)");

where batch is an attribute of MyClass, the extent of which, you are querying. Now that's all well and good but what if you don't want to perform the 'count' on any specific field.

I just want a total record/object count

In raw SQL you would do something like:

        select count(*) from mytable;

but that '*' is an SQL concept only. However, there is an equivalent in JDOQL and given it is OO based you can guess what the equivalent is in JDOQL - 'this'.

So the following will make sure you get the exact count that you are looking for:

q.setResult("count(this)");

If you were to perform a Collection retrieval with the exact same filter then the above count will always match the number of objects in the returned Collection. However, if you use count(attributeName) this may not always be the case based on uniqueness of the attribute's value etc.,

Conclusion


Until we came across the count(this) construct we were having issues with our counts returning more objects than a query with the exact same filter that returned a collection of the objects. I hope this helps anyone else out there who has this issue.

Extra performance hint

Some JDBC drivers which will always retrieve all records to perform a 'count' operation even if you have specified only a count(this) dataset in JDOQL above.

That is not desirable on large datasets!
 
To overcome this issue with we can, if using the DataNucleus JDO implementation, use its extension that tells it to inform the driver NOT to pull all records to perform a count: 

q.addExtension("datanucleus.query.resultSizeMethod", "count");


Enjoy!

Comments

Post a Comment

Please add your comment:

Popular posts from this blog

Java package name structure and organization - best practice and conventions

-
When it comes to conventions regarding how to structure your packages in Java (or any other language in fact) there is a wide variety of opinion. Making the right decision can promote productivity, understandability, quality, flexibility and the construction of reusable frameworks. I've seen some people promote 'package by feature' over 'package by layer' but I've used quite a few approaches and found 'package by layer' much better than 'package by feature' but further from that I have found that a hybrid: 'package by layer then feature' strategy works extremely well in practice and here's why I think that's so. Package by layer then feature The 'package by feature' people tend to want to put all user interface, model classes, persistence classes (eg., DAO - if you're into self torture ;]) for a particular feature into the same package and according to many examples, the same directory. i.e. they don't us
Read more

Classic software engineering mistakes: To Greenfield or Refactor Legacy code?

-
Oh, the humanity! If we were not in the software engineering game but, rather, in the civil engineering game then the equivalent of this article would be called 'Classic Civil Engineering Mistakes' and contain graphic videos of buildings and bridges collapsing and thousands of people running for their lives. It's hard to get emotional about something you can't see Unfortunately software is a rather intangible asset that doesn't lend itself to dramatic and emotional visualizations. When it comes down to it the life work of a software engineer, when reduced to it's lowest conceptual level, is the specific arrangement of sequences of 1's and 0's on one or more hard disks (or some form of solid state storage) residing on a server or desktop computer. How those 1's and 0's get there and 'which' sequences work and which don't is not really a black art but it definitely requires some intelligence but surprisingly a lot more common sens
Read more

How to reset MySQL 8 root password on CentOS 7 and 8

-
Occasionally you come up with the world's most unbreakable root password for a MySQL 8 installation and you get distracted before you record it somewhere.... and you remain distracted for 3 months. Then you return to the project where this database was needed and ... whoops! What was that totally awesome password? Here's how you can reset your root password. This was based on a page that had a similar instructions except none of them worked - lots of errors and incorrect names - like referring to mysql instead of mysqld when stopping the service. Here we go: All as root user: Create a file in /var/lib/mysql called mysql-pwd with the following contents: ALTER USER 'root'@'localhost' IDENTIFIED BY 'anotherPwd28^$94'; Shutdown mysql   systemctl stop mysqld Run MySQL executing the given init file under the mysql user   mysqld --init-file=/var/lib/mysql/mysql-pwd --user=mysql & Check the MySQL log to make sure no errors occurred:   vi /var/log/mysqld.log
Read more