Cross database pagination problem and how we solved it

Cross database pagination problem and how we solved it

When we are developing a webapp with any type of listing, pagination is a must have feature. From the UI perspective we can use infinite scrolling or pages approach.

If you are familiar with google search this is how google uses the second approach,

Now the problem is how we generate listing for a particular page?
Lets take a hypothetical library application where we need to list down existing books, assume we need to list 10 books per page.

This is how we query books to generate the 1st page,

SELECT * FROM BOOKS LIMIT 10;

to generate the 2nd page,

SELECT * FROM BOOKS LIMIT 10 OFFSET 10;

So easy,  by providing the offset value we can dynamically generate our pages.

BUT can we use the same across database types?

If you know the exact backend DB type you gonna use in production, lucky you!
But if you are developing a product which needs to support multiple DB types (MySQL, Oracle, Postgres, H2 ....) above query will fail in some of them. For example above will work in MySQL, H2 and Postgres but will not work in Oracle.

In Oracle 11g you can run following but not in 12c

SELECT *
FROM (
 SELECT b.*, ROWNUM RN
 FROM (
 SELECT *
 FROM BOOKS
 ORDER BY ID ASC
 ) b
 WHERE ROWNUM <= 10
)
WHERE RN > 10

Now lets discuss how we solved this problem. Following is high-level architecture of the solution.







In this scenario we have exposed all CRUD operations as OSGI services to the outside world. This component may or may not have DB specific logic. But for sure we dont have any DB type specific logic within our main OSGI component. We have delegated all those DB type specific logic to the DB Adapter.

What are the DB type specific things we can have?

1. Above pagination related stuff (LIMIT, OFFSET)
2. Auto generate key usage(getGenaratedKeys)

How to load correct DB Adapter class through reflection

We can maintain a configuration file where we state the DB Adapter class. Then read that class name within our main component and use/call through reflection.

Reference

http://www.jooq.org/doc/3.5/manual/sql-building/sql-statements/select-statement/limit-clause/

{ Read More }

Create and print posters and large sized posters Posters It

Create and print posters and large sized posters Posters It

Tom Clancy�s Splinter Cell Blacklist � PC

The United States has a military presence in two thirds of countries around the world. A group of 12 have had enough and initiate a terror ultimatum called the Blacklist � a deadly countdown of escalating terrorist attacks on U.S. interests. Sam Fisher is the leader of the newly formed 4th Echelon unit: a clandestine unit that answers solely to the President of the United States. Sam and his team must hunt down these terrorists by any means necessary, and stop the Blacklist countdown before it reaches zero.

Unleash the power of the most lethal agent to ever exist. You�ve been given the ultimate freedom to protect innocents against the Blacklist terror attacks known as � the freedom to use limitless force, to break every law, and to become the globe�s deadliest operative. If you succeed, the President of the United States will deny you exist. If you fail, millions will die.

 
Download .torrent
You need uTorrent for downloading .torrent files.

{ Read More }

Cover for new book Quick Start Guide to Making Choices makes it real

Cover for new book Quick Start Guide to Making Choices makes it real

Seeing the cover for our book makes it real. We have just completed the first big edit. We can now see see the publishing date coming closer by. 

Many thanks to everyone who has participated in the first feedback group. We have worked all your constructive feedback and suggestions into this big edit. 

Posted via email from colby pre-posterous

{ Read More }