我应该在每个方法中调用数据库连接设置还是在构造函数中调用一次 [英] Should I call the database connection setup in each method or just once in constructor
问题描述
我正在用Java开发一个小项目，我有DatabaseController类，该类包含许多可从数据库中插入/选择/删除的方法。
I'm working on a small project in Java and I have got DatabaseController class which contains many methods to insert/select/delete from my database.
我的问题是，与建立有关最佳软件工程实践的连接有关。
my question is, related to setting up the connection in regards to best software engineering practices.
- 我应该在DatabaseController构造函数中建立数据库连接，以便将其打开一次并保持打开状态。
- 在插入/删除/选择方法之前，我应该放置一个连接数据库的方法吗？
我了解两者都可能有缺点也有缺点。.但我想知道对于小型和大型项目来说最好的方法是什么？
I understand that both might have cons and pros .. but I'd like to know what's best for small and large scale projects?
推荐答案
DataSource
首先，最好将对数据库的访问权限放在 DataSource 接口。这使您能够更改获取数据库连接的策略，而无需更改代码中使用这些数据库连接的所有位置。请参见 Oracle教程。
您的 JDBC驱动程序应该提供 DataSource 作为外观，您可以在代码中的一个位置之间切换，甚至可以使用第三方连接池解决方案，而无需更改许多您呼叫的地方 DataSource :: getConnection（） 。
Your JDBC driver should provide an implementation of DataSource. For example, consider the Postgres JDBC driver from https://jdbc.postgresql.org. That driver supplies two implementations: One for direct connections to the database, and the other for pooling connections. By using the DataSource as a façade, you could switch between either of these, or even use a 3rd party connection pooling solution, in one place in your code, without changing the many places you call DataSource::getConnection().
您可能想要的第三种实现替换为分布式交易。
A third kind of implementation you might want to substitute in would be for distributed transactions.
在界面后面交换具体类的另一个示例：也许您决定切换JDBC驱动程序。也许对于您的Postgres数据库，您决定从 jdbc.postgresql.org驱动程序切换到 pgjdbc-ng驱动程序。您希望能够在一处更改此配置，而又不破坏所有现有的应用程序代码。
Another example of swapping the concrete class behind the interface: Perhaps you decide to switch JDBC drivers. Perhaps for your Postgres database, you decide to switch from the jdbc.postgresql.org driver to the pgjdbc-ng driver. You want to be able to make this configuration change in one place, without breaking all your existing app code.
这里是在<$后面使用具体实现的示例c $ c> DataSource 接口。
public javax.sql.DataSource obtainDataSource() {
org.postgresql.ds.PGSimpleDataSource dataSource = new PGSimpleDataSource() ;
dataSource("AcmeInvoices database data source") ;
source.setServerName( "localhost" ) ;
source.setDatabaseName( "invoicing" ) ;
source.setUser( "Scott" ) ;
source.setPassword( "tiger" ) ;
return dataSource ; // Returning a reference to the object of this concrete class `PGSimpleDataSource` as an object of the interface `DataSource`.
}
您在上述代码段中看到的调用方法会因数据库和 JDBC驱动程序的功能。例如，如果您使用的是到数据库的加密连接，则将有其他方法可调用以配置加密密钥。
The methods you see being called in that code snippet above will vary dramatically by the capabilities of your database and of your JDBC driver. For example, if you are using encrypted connections to the database, you will have additional methods to call for configuring the encryption keys.
您可能在应用程序启动的早期就设置了此 DataSource 对象。保持参考的地方。或保留几个：对于应用程序的不同部分，您可能具有不同的连接类型（不同的用户名和密码），需要不同的安全性或不同的用户角色。
You set up this DataSource object early on, likely as part of your app launch. Keep a reference someplace. Or keep several: you may have different connection types (different user name & passwords) for different parts of your app where different security or different user roles are necessary.
Pass代码库周围的 DataSource 对象，介绍了许多用于插入/选择/删除数据库的方法。在每种方法中，它们都调用 myDataSource.getConnection（），然后继续执行其SQL。该方法完成工作后，应通过调用 Connection :: close 关闭连接。更好的是，使用try-with-resources自动关闭连接。
Pass the DataSource object around your codebase to the many methods that insert/select/delete with your database. In each method, they call myDataSource.getConnection(), then go on to execute their SQL. When that method finishes its work, it should close the connection by calling Connection::close. Even better, use try-with-resources to automatically close the connection.
如果连接来自连接池，则该连接并没有真正关闭。而是，连接返回到池中。这是使用接口（此处为 Connection ）的强大功能的另一个示例。接口后面的具体类可以选择如何响应 close 方法的调用，并且具体类可以稍后换出而不会破坏调用代码。
If the connection came from a connection pool, the connection is not really closed. Rather, the connection is returned to the pool. This is another example of the power of using an interface, here Connection. The concrete class behind the interface can choose how to respond to invoking the close method — and that concrete class can be swapped out later without breaking the calling code.
我应该在DatabaseController构造函数中建立数据库连接，以便将其打开一次并保持打开状态
Should I establish the database connection in DatabaseController constructor so it's opened once and left open
通常，保持连接打开而不进行进一步工作通常不是一个好主意。
No, it is not generally a good idea to keep a connection open with no further work to be done.
基本上，您将创建自己的连接池。连接池并不是一件容易的事。池中要考虑各种问题，您不太可能认为它们没有能力解决这些问题。我见过的所有连接池工具都有各自的bug，问题和局限性。因此，不要自己承担这项任务。如果您确实选择使用池，请找到一个可靠的，陈旧的现有实现。并仔细研究其文档，以确保您了解如何正确配置和使用该池。
Basically you would be creating your own connection pool. And connection pooling is not a simple matter. There are various issues to consider in pooling, and you are unlikely to think of them much less be competent to solving them. All the connection pooling tools I have seen have each had their share of bugs, issues, and limitations. So do not take on this task yourself. If you do choose to go with pooling, find a robust well-worn existing implementation. And study its documentation thoroughly to be sure you understand how to configure and use the pool properly.
坦率地说，我自己停止使用连接池。在我看来，对于用户/连接频率较低的系统来说，各种风险和复杂性是不值得的。例如，查看各种池解决方案中的默认超时设置，其中在一段时间不使用后关闭池连接。如果您在泳池中的活动大部分都少于该超时时间，那么您实际上将不会从泳池中受益。另外，我发现，关于以昂贵的价格打开与数据库的连接的大多数主张都被夸大了，特别是对于同一台机器上的本地数据库。
Frankly, I stopped using connection pooling myself. The various risks and complications are not worth it in my opinion for systems with a lower number of less frequent number of users/connections. For example, look at the default time-out set in the various pooling solutions where the pooled connections are closed after some period of no use. If your activity in the pool is mostly lower than that time-out, then you effectively won't be benefiting from the pool. Also, I have found that most of the claims about opening a connection to the database as expensive are exaggerated, especially for a local database on the same machine.
我应该在我之前放置一种连接数据库的方法吗？插入/删除/选择方法
Should I place a method to connect to the database before my insert/delete/select methods
以下是一些示例方案，显示了如何在靠近您的位置进行数据库连接使用连接来执行某些特定的数据库任务。
Here are some example scenarios showing how you should get-and-close the database connection close to where you use the connection for some specific database task.
在登录屏幕中，当用户单击<时，通过查找存储在数据库中的用户和组来对用户进行身份验证。 code>登录按钮，您的代码应检索对 DataSource 对象的引用，调用 getConnection ，先进行用户和组的查找，然后立即关闭连接。
In your login screen where you authenticate the user by looking up users and groups stored in your database, when the user clicks the Login button, your code should retrieve a reference to the DataSource object, call getConnection, do the user-and-groups lookups, then immediately close the connection.
当用户继续查找过期的发票时，请执行相同的操作。检索 DataSource ，调用 getConnection ，运行SQL以查找任何过期的发票，并将该发票数据作为缓存，调用 Connection :: close 关闭与数据库的连接，然后继续构建GUI以显示缓存的发票数据。
When the user goes on to lookup overdue invoices, do the same. Retrieve the DataSource, call getConnection, run your SQL to find any overdue invoices, load that invoice data into memory as a cache, call Connection::close to close the connection to the database, and proceed to build your GUI to display the cached invoice data.
当用户单击一个按钮对其中一张发票执行某些操作时，请执行相同的过程。检索 DataSource ，调用 getConnection ，运行SQL来获取特定发票行中所需的主键值，并检索其他发票字段和相关行（例如发票项目），请调用 Connection :: close 关闭与数据库的连接，然后继续执行操作，例如生成包含以下内容的电子邮件：缓存的发票明细及其发票项目行。请注意，您如何在此操作方法中多次使用连接，首先单击发票表，然后再次单击发票项目表。最终所有即将完成的即时工作完成后，关闭连接。
When the user clicks a button to take some action on one of those invoices, do the same process. Retrieve the DataSource, call getConnection, run your SQL to fetch the particular invoice row for the desired primary key value, retrieve additional invoice fields and related rows such as invoice-items, call Connection::close to close the connection to the database, and proceed with your action such as generating an email containing the cached invoice details and its invoice-item rows. Notice how you might use the connection multiple times within this action method, first to hit the invoice table, then again to hit the invoice-item table. When finally all the immediate work at hand is complete, close the connection.
请记住，在这几次连接中，如果有池连接，则关闭连接：
Remember, in each of these several times where you got a connection and closed a connection, if pooled:
- 您获得的连接可能是新连接，也可能不是新连接。
- 关闭将连接返回到池，而不是实际关闭它。
该池可能会在某个时候关闭该连接，并可能在某个时候用一个新的连接代替它。但这对所有用户身份验证代码和发票查询代码都是透明的。
The pool may close the connection at some point, and at some point perhaps replace it with a fresh connection. But that is transparent to all your user-authentication code and invoice lookup code.
这篇关于我应该在每个方法中调用数据库连接设置还是在构造函数中调用一次的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
