我应该如何在基于 servlet 的应用程序中连接到 JDBC 数据库/数据源? [英] How should I connect to JDBC database / datasource in a servlet based application?
问题描述
我有一个外部 MySQL 服务器,它已经设置好并且运行良好.我在 Eclipse 中创建了一个数据库连接,可以在数据源资源管理器选项卡中查看数据库.
I have an external MySQL server that's set up and working fine. I created a database connection in Eclipse and can view the database in the Data Source Explorer tab.
现在,我有一个需要访问该数据库的 servlet.我该怎么做?有没有办法引用在数据源资源管理器中创建的数据库连接,或者我必须定义两次?
Now, I have a servlet that needs to access that database. How do I do it? Is there a way to reference that database connection created in the data source explorer, or do I have to define everything twice?
另外,打开连接的最佳方式是什么?我已经包含了 mysql-connector-java-5.1.11-bin.jar
文件,并且我发现了两种有效的方法:
Also, what's the best way to open the connection? I've got the mysql-connector-java-5.1.11-bin.jar
file included, and I've found two methods that work:
MysqlDataSource d = new MysqlDataSource();
d.setUser("user");
d.setPassword("pass");
d.setServerName("hostname.com");
d.setDatabaseName("db");
Connection c = d.getConnection();
和
Connection c = DriverManager.getConnection("jdbc:mysql://hostname.com/db","user","pass");
两者都不是最优的,因为首先,它们都使用硬编码的字符串来处理所有事情.这是一个Java EE web app项目,那么有没有好放连接数据的地方呢?或者有没有办法放弃所有这些而只使用数据源浏览器中的连接?
Neither is optimal, because first of all, they both use hard-coded strings for everything. This is a Java EE web app project, so is there a good place to put connection data? Or is there a way to forgo all that and just use the connection in the data source explorer?
推荐答案
一种常见的做法是将其配置为 DataSource
在 servlet 容器 有问题.它将为您提供连接池设施,这将大大提高性能.另外一个常见的做法是将一些配置文件中的原始设置外部化,这些文件放在类路径中.
A common practice is to configure this as a DataSource
in the servlet container in question. It will provide you connection pooling facilities which will greatly improve performance. Also a common practice is to externalize the raw settings in some configuration file which is been placed in the classpath.
如果您使用 Tomcat 作为 servletcontainer,您需要根据其 JNDI 文档.你会看到有几种方法.最简单的方法是在你的动态 web 项目的 webcontent 中创建一个 /META-INF/context.xml
(要清楚,/META-INF
是相同的级别作为 web 应用程序的 /WEB-INF
)并用以下内容填充它:
In case you're using Tomcat as servletcontainer, you need to configure the datasource as per its JNDI documentation. You'll see that there are several ways. Easiest way is to create a /META-INF/context.xml
in the webcontent of your dynamic web project (to be clear, the /META-INF
is at the same level as the /WEB-INF
of the webapp) and fill it with something like:
<?xml version="1.0" encoding="UTF-8"?>
<Context>
<Resource
name="jdbc/db" type="javax.sql.DataSource"
maxActive="100" maxIdle="30" maxWait="10000"
url="jdbc:mysql://hostname.com/db"
driverClassName="com.mysql.jdbc.Driver"
username="user" password="pass"
/>
</Context>
这大致意味着Tomcat服务器应该创建一个JNDI名称为jdbc/db
的数据源,最多有100个活动连接,最多30个空闲连接和一个在从应用程序返回连接之前的最长等待时间为 10000 毫秒(实际上:由应用程序关闭,因此应用程序在获取连接和关闭连接之间有 10 秒的时间).设置的剩余部分对您来说应该是熟悉且不言自明的;这些是 JDBC 设置.
This roughly means that Tomcat server should create a datasource with the JNDI name jdbc/db
with a maximum of 100 active connections, a maximum of 30 idle connections and a maximum wait time of 10000 milliseconds before a connection should be returned from your application (actually: closed by your application, so your application has 10 seconds time between acquiring the connection and closing the connection). The remnant of the settings should be familiar and self-explaining enough to you; those are the JDBC settings.
最后在您的 web 项目中,编辑文件 /WEB-INF/web.xml
以添加以下条目:
Finally in your web project, edit the file /WEB-INF/web.xml
to add the following entry:
<resource-env-ref>
<resource-env-ref-name>jdbc/db</resource-env-ref-name>
<resource-env-ref-type>javax.sql.DataSource</resource-env-ref-type>
</resource-env-ref>
这大致意味着 Web 应用程序应该使用名为 jdbc/db
的服务器提供的数据源.
This roughly means that the webapplication should use the server-provided datasource with the name jdbc/db
.
然后将您的连接管理器更改为如下所示:
Then change your connection manager to something like this:
private DataSource dataSource;
public Database(String jndiname) {
try {
dataSource = (DataSource) new InitialContext().lookup("java:comp/env/" + jndiname);
} catch (NamingException e) {
// Handle error that it's not configured in JNDI.
throw new IllegalStateException(jndiname + " is missing in JNDI!", e);
}
}
public Connection getConnection() {
return dataSource.getConnection();
}
..并用 new Database("jdbc/db")
替换所有 Class.forName(driver)
调用并替换所有 DriverManager.getConnection()
由 database.getConnection()
调用.如有必要,您可以从一些配置文件(属性文件?).
..and replace all Class.forName(driver)
calls by new Database("jdbc/db")
and replace all DriverManager.getConnection()
calls by database.getConnection()
. You can if necessary obtain the value jdbc/db
from some config file (Properties file?).
或者,通过 DataSource">@Resource
容器管理工件内的注释,例如 @WebServlet
servlet 类:
Alternatively, inject the DataSource
via the @Resource
annotation inside a container managed artifact, such as a @WebServlet
servlet class:
@Resource(name="jdbc/db")
private DataSource dataSource;
应该是这样.只需使用上述更改部署您的 web 应用程序并运行它.不要忘记将数据库 JDBC 驱动程序放在 Tomcat/lib
或将其路径添加到 Tomcat/conf/catalina 的
,因为加载 JDBC 驱动程序的责任现在从 web 应用程序转移到服务器.有关更多提示和其他基本 JDBC/JNDI 示例,您可以找到 这篇文章 也很有用.shared.loader
属性.properties
That should be it. Just deploy your webapplication with the above changes and run it. Don't forget to place the database JDBC driver in the Tomcat/lib
or to add its path to the shared.loader
property of Tomcat/conf/catalina.properties
, because the responsibility of loading the JDBC driver is now moved from the webapplication to the server. For more hints and other basic JDBC/JNDI examples you may find this article useful as well.
- 如何在Eclipse Web项目中安装JDBC驱动程序而无需面对java.lang.ClassNotFoundException
- 我在哪里必须要为Tomcat的连接池放置JDBC驱动吗?
- 在多线程系统中使用静态 java.sql.Connection 实例是否安全?
- 显示 JDBC 结果集在 JSP 页面的 HTML 中使用 MVC 和 DAO 模式
- 如何在 JSP 页面中从数据库中检索和显示图像?
这篇关于我应该如何在基于 servlet 的应用程序中连接到 JDBC 数据库/数据源?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!