I have a user table & a child table Post containing user's posts. A user has a large number of posts just like twitter posts.
I need to fetch user posts on the UI, load more posts as the user scrolls down the page just like facebook/twitter do. I am using hibernate as the ORM framework & MySql as db. I looked into pagination found two primary ways of achieving that
I have two questions
A. Which way of pagination would be more appropriate & efficient to achieve this? I've read that ScrollableResults is more efficient than setFirstResult but it keeps the connection open for the entire pagination process.
B. As the user's profile page is loaded ajax call is fired to display the user's posts everytime, it's like a certain default content in the page. So do I need to implement a second level cache in order to avoid db hits every time the page loads?
I wouldn't use ScrollableResults in a web application. We do not want long lived database connections in a web app. ScrollableResults would make more sense in a swing application for example, where the JVM runs on the desktop, not on the server.
for question B: Configure your Mysql server cache first before thinking about a second level cache. There is much more to gain there. DB hits aren't that bad when the results are already in mysql cache.