如何在基于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应用程序项目，因此是否有放置连接数据的好地方?还是有办法放弃所有这些而只在数据源资源管理器中使用连接?

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?

推荐答案

通常的做法是将其配置为 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 作为servlet容器，则需要根据其 JNDI文档.您会看到有几种方法.最简单的方法是在动态Web项目的Web内容中创建/META-INF/context.xml(很明显，/META-INF与Webapp的/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();
}

..并将所有Class.forName(driver)调用替换为new Database("jdbc/db")，并将所有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?).

或者，通过 @Resource注入DataSource 标记在容器托管的工件中，例如 @WebServlet servlet类:

Alternatively, inject the DataSource via the @Resource annotation inside a container managed artifact, such as a @WebServlet servlet class:

@Resource("jdbc/db")
private DataSource dataSource;

应该的.只需部署具有上述更改的Web应用程序并运行它即可.不要忘记将数据库JDBC驱动程序放在Tomcat/lib中或将其路径添加到Tomcat/conf/catalina.properties的shared.loader属性，因为现在加载JDBC驱动程序的职责已从Web应用程序转移到服务器.有关更多提示和其他基本的JDBC/JNDI示例，您可以找到本文也是有用的.

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.

• 如何无需面对java.lang.ClassNotFoundexception
在Eclipse Web项目中安装JDBC驱动程序
• 我在哪里必须为Tomcat的连接池放置JDBC驱动程序?
• 在多线程系统中使用静态java.sql.Connection实例安全吗?
• 显示JDBC结果集MVC和DAO模式在JSP页面的HTML中使用
• 如何在JSP页面中从数据库检索并显示图像?

• How to install JDBC driver in Eclipse web project without facing java.lang.ClassNotFoundexception
• Where do I have to place the JDBC driver for Tomcat's connection pool?
• Is it safe to use a static java.sql.Connection instance in a multithreaded system?
• Show JDBC ResultSet in HTML in JSP page using MVC and DAO pattern
• How to retrieve and display images from a database in a JSP page?

这篇关于如何在基于servlet的应用程序中连接到JDBC数据库/数据源?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！