How to organize multi-threaded interaction with the database in Java?

Question

Java

Good afternoon, comrades!
When designing an application, I wondered which architecture is better to choose ....
There is one thread that adds data to the database, there are many other threads that read this data.
You can create a class that will provide readRecord() and writeRecord() methods, pass a reference to this object to all threads. You can independently establish a connection to the database in each thread and perform the required operations.
Are there any accepted implementation patterns? Is there any literature that addresses this issue?
Thank you all in advance!

Answer 1

An approach from the JEE world (and beyond):
For the entire multi-threaded application, a "connection pool" implementation is used that maintains connections to the DBMS (one or more).
The application code of each thread accesses the pool, obtains a connection to the DBMS from it, performs certain work (request or requests), closes the transaction and closes the connection (as a result, the connection is returned to the pool).
In this case, it is assumed that the application code holds the connection for the minimum time necessary.
This approach makes it possible to serve many threads with a relatively small number of connections to the DBMS and save time on establishing a connection, since under normal conditions, there is a percentage of free and ready connections in the pool.
Plus, the application code does not contain settings for a specific DBMS, all settings are stored at the level of connection pool configuration files.
The pool management logic can independently establish new connections (if all existing ones are busy), terminate connections (if there are a lot of free ones), “take away” connections from the application code if there has been no activity on the connection for a long time (they simply forgot to close the connection), check for idle connections for "aliveness", executing diagnostic SQL queries, etc.
Connection pooling examples:
Apache Commons DBCP
c3p0:JDBC DataSources/Resource Pools
The Tomcat JDBC Connection Pool

Answer 2

In the Java world, it is customary to use Hibernate to work with the database . But, as usual, there are problems .

Answer 3

Well, it is desirable for each thread to issue its connection, because. operations are performed in a transaction, and if different threads push requests into one connection, then when you commit to the database, everything will be committed, with rollback everything will be rolled back. Usually they use a pool of connections to the database and, in principle, no problems, they ask the pool for a getConnection () connection, and the pool either issues an existing one, if there is a free one, or creates a new one.