So, here is the deal. I'm developing an Android application (although it could just as easily be any other mobile platform) that will occasionally be sending queries to a server (which is written is Java). This server will then search a MySQL database for the query, and send the results back to the Android. Although this sounds fairly generic, here are some specifics:
所以这里是交易。我正在开发一个Android应用程序（虽然它可以像任何其他移动平台一样容易）偶尔会向服务器发送查询（编写的是Java）。然后，该服务器将在MySQL数据库中搜索查询，并将结果发送回Android。虽然这听起来很通用，但这里有一些细节：
-
The Android will make a new TCP connection to the server every time it queries. The server is geographically close, the Android could potentially be moving around a lot, and, since the Android app might run for hours while only sending a few queries, this seemed the best use of resources.
每次查询时，Android都会与服务器建立新的TCP连接。服务器地理位置接近，Android可能会移动很多，而且，由于Android应用程序可能只运行几个小时而只发送一些查询，这似乎是资源的最佳使用。
-
The server could potentially have hundreds (or possibly even thousands) of these queries at once.
服务器可能同时拥有数百个（或甚至数千个）这些查询。
-
Since each query runs in its own Thread, each query will at least need its own Statement (and could have its own Connection).
由于每个查询都在自己的Thread中运行，因此每个查询至少需要自己的Statement（并且可以拥有自己的Connection）。
Right now, the server is set up to make one Connection to the database, and then create a new Statement for each query. My questions for those of you with some database experience (MySQL in particular, since it is a MySQL database) are:
现在，服务器设置为与数据库建立一个连接，然后为每个查询创建一个新的Statement。对于那些具有一定数据库经验的人（特别是MySQL，因为它是MySQL数据库），我的问题是：
a) Is it thread safe to create one Statement per Thread from a single Connection? From what I understand it is, just looking for confirmation.
a）从单个连接创建一个每个线程的语句是否可以安全线程？据我所知，只是寻找确认。
b) Is there any thread safe way for multiple threads to use a single PreparedStatement? These queries will all be pretty much identical, and since each thread will execute only one query and then return, this would be ideal.
b）多线程使用单个PreparedStatement是否有任何线程安全的方法？这些查询将完全相同，并且由于每个线程只执行一个查询然后返回，这将是理想的。
c) Should I be creating a new Connection for each Thread, or is it better to spawn new Statements from a single Connection? I think a single Connection would be better performance-wise, but I have no idea what the overhead for establishing a DB Connection is.
c）我应该为每个线程创建一个新的连接，还是从单个连接中生成新的语句更好？我认为单个Connection在性能方面会更好，但我不知道建立数据库连接的开销是多少。
d) Is it best to use stored SQL procedures for all this?
d）最好是为所有这些使用存储的SQL过程吗？
Any hints / comments / suggestions from your experience in these matters are greatly appreciated.
我们非常感谢您对这些事项的任何提示/意见/建议。
EDIT:
编辑：
Just to clarify, the android sends queries over the network to the server, which then queries the database. The android does not directly communicate with the database. I am mainly wondering about best practices for the server-database connection here.
为了澄清，android通过网络向服务器发送查询，然后服务器查询数据库。 android不直接与数据库通信。我主要想知道服务器数据库连接的最佳实践。
3 个解决方案
#1
5
Just because a Connection object is thread safe does not mean its thread efficient. You should use a Connection pool as a best practice to avoid potential blocking issues. But in answer to your question, yes you can share a Connection object between multiple threads.
仅仅因为Connection对象是线程安全的并不意味着它的线程有效。您应该使用连接池作为最佳做法，以避免潜在的阻塞问题。但是回答你的问题，是的，你可以在多个线程之间共享一个Connection对象。
You do need to create a new Statements/Prepared Statements in each thread that will be accessing the database, they are NOT thread safe. I would highly recommend using Prepared Statements as you will gain efficiency and protection against SQL injection attacks.
您需要在将访问数据库的每个线程中创建新的语句/准备语句，它们不是线程安全的。我强烈建议您使用Prepared Statements，因为您将获得效率并防止SQL注入攻击。
Stored procedures will speed up your database queries since the execution plan is compiled already and saved - highly recommended to use if you can.
存储过程将加速您的数据库查询，因为已经编译并保存了执行计划 - 如果可以，强烈建议使用。
Have you looked at caching your database data? Take a look at spymemcached if you can, its a great product for reducing number of calls to your data store.
您是否考虑过缓存数据库数据？如果可以，请查看spymemcached，它是减少数据存储调用次数的绝佳产品。
#2
2
From my experience, you should devote a little time to wrap the database in a web service. This accomplishes two things:
根据我的经验，您应该花一点时间将数据库包装在Web服务中。这完成了两件事：
- You are forced to examine the data for wider consumption
- 您*检查数据以获得更广泛的消费
- You make it easier for new consumers to consume the data
- 您可以让新消费者更容易使用数据
A bit more development time, but direct connections to a database via an open network (Internet) is more problematic than specifying what can be accessed through a method.
更多的开发时间，但通过开放网络（Internet）直接连接到数据库比指定可通过方法访问的内容更成问题。
#3
1
Use a connection pool such as Commons DBCP. It will handle all the stuff you're worrying about, out of the box.
使用Commons DBCP等连接池。它将处理所有你担心的东西，开箱即用。
#1
5
Just because a Connection object is thread safe does not mean its thread efficient. You should use a Connection pool as a best practice to avoid potential blocking issues. But in answer to your question, yes you can share a Connection object between multiple threads.
仅仅因为Connection对象是线程安全的并不意味着它的线程有效。您应该使用连接池作为最佳做法，以避免潜在的阻塞问题。但是回答你的问题，是的，你可以在多个线程之间共享一个Connection对象。
You do need to create a new Statements/Prepared Statements in each thread that will be accessing the database, they are NOT thread safe. I would highly recommend using Prepared Statements as you will gain efficiency and protection against SQL injection attacks.
您需要在将访问数据库的每个线程中创建新的语句/准备语句，它们不是线程安全的。我强烈建议您使用Prepared Statements，因为您将获得效率并防止SQL注入攻击。
Stored procedures will speed up your database queries since the execution plan is compiled already and saved - highly recommended to use if you can.
存储过程将加速您的数据库查询，因为已经编译并保存了执行计划 - 如果可以，强烈建议使用。
Have you looked at caching your database data? Take a look at spymemcached if you can, its a great product for reducing number of calls to your data store.
您是否考虑过缓存数据库数据？如果可以，请查看spymemcached，它是减少数据存储调用次数的绝佳产品。
#2
2
From my experience, you should devote a little time to wrap the database in a web service. This accomplishes two things:
根据我的经验，您应该花一点时间将数据库包装在Web服务中。这完成了两件事：
- You are forced to examine the data for wider consumption
- 您*检查数据以获得更广泛的消费
- You make it easier for new consumers to consume the data
- 您可以让新消费者更容易使用数据
A bit more development time, but direct connections to a database via an open network (Internet) is more problematic than specifying what can be accessed through a method.
更多的开发时间，但通过开放网络（Internet）直接连接到数据库比指定可通过方法访问的内容更成问题。
#3
1
Use a connection pool such as Commons DBCP. It will handle all the stuff you're worrying about, out of the box.
使用Commons DBCP等连接池。它将处理所有你担心的东西，开箱即用。