I want to use multiple threads to execute SQL queries. Allocation of one database connection per thread will cause a large overhead. Can I have multiple threads share the same database connection and execute multiple SQL queries in parallel?
In theory, JDBC connections are required to be thread-safe*, but in practice it is problematic for two reasons:
Not all drivers are actually thread-safe, and even drivers that claim to be thread-safe are likely not robustly tested for this thread-safety, as using a connection concurrently from multiple threads is not recommend and uncommon.
Using a single connection concurrently from multiple threads comes with all kinds of additional coordination problems: one thread committing or rolling back, or switching from auto-commit false to auto-commit true, will break things for all other threads on the same connection: work gets lost, or active result sets are suddenly closed (e.g. due to commit, or the JDBC requirement that execution of a statement in auto-commit will close any previous result sets on the same connection).
Also, if a connection breaks (or is closed by one of the threads), this will impact all your other threads as well.
Additionally, if a driver is thread-safe, this thread-safety is usually achieved with a lot of synchronized blocks or other forms of mutual exclusion. This can seriously impair performance over using separate connections, because the other threads will have to wait on each other.
Using a connection per thread is a lot simpler, as it isolates the work from one thread from all others. In addition, it will usually perform better because of the absence of contended access to a connection. If your concern is about the setup cost of having multiple connection, then you should do two things:
Doing this has a lot of benefits: you can submit individual units of work to the thread pool; the unit of work obtains a connection from the pool, and returns it when it is done (by closing the connection). Using a connection pool in this way also allows you to easily replace broken connections without having to handle this in your application code itself (other than maybe a retry for the task that broke a connection), and finally using both a connection pool and thread pool allows you to experiment with sizing both to see what is a good size for giving your application the best throughput.
*: Originally this was a hard requirement in the JDBC 1.20 specification, but that was later removed. For technical reasons, some form of thread-safety is still advisable though.