Efficient Database Connecting

I’m confused about the most efficient approach of saving user-inputted form data into my database.

  • Do I save to the database each time they click the ‘Save’ button in the form? What if they spam the button, that’ll create a new connection with each click.

  • Do I open a single-threaded connection pool when the form view opens, and then use that connection when needed?

  • Use caching like cache2k or similar, and then persist to DB later?

Thanks

Those are good concerns for a website, and not necessarily dependent on vaadin.

  1. Saving data each time a user clicks save is typically what a user expects to happen. There are quite a few UI design options on how to prevent the user from endlessly clicking save:

    a. disable the save button right after the user clicks on it. Vaadin has a button method to support this feature, just be sure that you re-enable the button after a save.

    this.saveActionButton.setDisableOnClick(true);
    

    b. Display the form in read-only mode until the user clicks an edit button. Once the user clicks cancel/save, reset the form to read-only.

    c. Only invoke the db update command if the data has changed and passed validation. You can even disable the save button until these two conditions are true.

  2. Your application should have a database connection pool, and only grab the connection when you are ready to commit the data. So no, I would not recommend holding onto a database connection when the user first displays the page. Here is one simple design:

    a. Get a database connection from the pool, use the connection to query the user’s info from the database. Return the database connection to the pool.
    b. Populate your vaadin form with the user’s info
    c. When user clicks save:
    i. validate data
    ii. if good, grab a connection from the pool, save the data, return connection to the pool

    Another design:

    a. instead of directly using database connections, use jpa and repositories.
    b. change above “get connection” and “return connection” to invoke get/save methods on repository.

  3. Whether or not you delay saving directly to the database or not is up to your website requirements. I would always immediately persist the user changes; otherwise, they will be lost in the case of a system failure. Losing customer data is not an expected user experience.

Solid reply! Very helpful stuff, thank you.

I plan on going to Approach #1 as you described and is exactly what I’m doing now believe it or not. Sometimes I doubt myself so thanks for clarifying. Cheers.