I'm confused as to how to properly interact with my Postgres database throughout the typical user experience on my web app. I'm seeking clarification on the most efficient method of communicating with the database, without over-communicating (spamming).
Currently I use
org.apache.commons.dbcp.BasicDataSource to connect to my database whenever needed, and I always close the connection when finished. It makes total sense to me when I'm doing a one-time query.
But what if the query can be performed multiple times at the discretion of the user?
Here are 2 specific occurrences in my app:
- If a user types in their credentials and then clicks "Login" on the login page, the database is queried to validate the credentials. What if the user clicks the button 100 times? This means they could potentially spam the button, causing multiple queries to be sent to my database. We could impose a manual limit (like 5 clicks per minute) but where do we draw the line?
- Once logged in, their user profile must be filled and they can 'Save' the changes after any field changes are triggered. So it would be very easy to maliciously spam the save button after a new character is typed.
I understand a new connection is not created each time
getConnection() is called but I don't understand if the inner mechanics of
BasicDataSource handle this potential spamming.
Once a user clicks 'Save' it's important that the changes are accessible to all other users. For example, User A could click 'Make Visible to Other Users' and then click 'Save'. User B should now be able to find User A in our app.
Do you recommend I use Connection Pool, Hibernate, Redis, cache2K or some other tool/framework? Or is it sufficient to query the database each and every time since pool will optimize it on the back-end?
Thanks so much. I'm using Java 8 + Vaadin 8.