TOMCAT数据源配置-CSDN博客

PostgreSQL

MySQL DBCP ExampMySQL DBCP Example

0. Introduction

Versions of MySQL and JDBCdrivers that have been reported to work:

MySQL 3.23.47, MySQL 3.23.47 using InnoDB,, MySQL 3.23.58, MySQL 4.0.1alpha
Connector/J 3.0.11-stable (the official JDBC Driver)
mm.mysql 2.0.14 (an old 3rd party JDBC Driver)

Before you proceed, don't forget to copy the JDBC Driver's jar into $CATALINA_HOME/lib.

1. MySQL configuration

Ensure that you follow these instructions as variations can cause problems.

Create a new test user, a new database and a single test table.Your MySQL usermust have a password assigned. The driverwill fail if you try to connect with an empty password.
mysql> GRANT ALL PRIVILEGES ON *.* TO javauser@localhost 
    ->   IDENTIFIED BY 'javadude' WITH GRANT OPTION;
mysql> create database javatest;
mysql> use javatest;
mysql> create table testdata (
    ->   id int not null auto_increment primary key,
    ->   foo varchar(25), 
    ->   bar int);
Note: the above user should be removed once testing iscomplete!

Next insert some test data into the testdata table.
mysql> insert into testdata values(null, 'hello', 12345);
Query OK, 1 row affected (0.00 sec)

mysql> select * from testdata;
+----+-------+-------+
| ID | FOO   | BAR   |
+----+-------+-------+
|  1 | hello | 12345 |
+----+-------+-------+
1 row in set (0.00 sec)

mysql>
2. Context configuration

Configure the JNDI DataSource in Tomcat by adding a declaration for yourresource to yourContext.

For example:
<Context>

    

    

    

    

    
    
    

  <Resource name="jdbc/TestDB" auth="Container" type="javax.sql.DataSource"
               maxActive="100" maxIdle="30" maxWait="10000"
               username="javauser" password="javadude" driverClassName="com.mysql.jdbc.Driver"
               url="jdbc:mysql://localhost:3306/javatest"/>

</Context>
3. web.xml configuration

Now create a WEB-INF/web.xml for this test application.
<web-app xmlns="http://java.sun.com/xml/ns/j2ee"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://java.sun.com/xml/ns/j2ee
http://java.sun.com/xml/ns/j2ee/web-app_2_4.xsd"
    version="2.4">
  <description>MySQL Test App</description>
  <resource-ref>
      <description>DB Connection</description>
      <res-ref-name>jdbc/TestDB</res-ref-name>
      <res-type>javax.sql.DataSource</res-type>
      <res-auth>Container</res-auth>
  </resource-ref>
</web-app>
4. Test code

Now create a simple test.jsp page for use later.
<%@ taglib uri="http://java.sun.com/jsp/jstl/sql" prefix="sql" %>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>

<sql:query var="rs" dataSource="jdbc/TestDB">
select id, foo, bar from testdata
</sql:query>

<html>
  <head>
    <title>DB Test</title>
  </head>
  <body>

  <h2>Results</h2>
  
<c:forEach var="row" items="${rs.rows}">
    Foo ${row.foo}<br/>
    Bar ${row.bar}<br/>
</c:forEach>

  </body>
</html>
That JSP page makes use of JSTL'sSQL and Core taglibs. You can get it fromApache Tomcat Taglibs - Standard Tag Libraryproject — just make sure you get a 1.1.x release. Once you have JSTL,copy jstl.jar and standard.jar to your web app'sWEB-INF/lib directory.

Finally deploy your web app into $CATALINA_BASE/webapps eitheras a warfile calledDBTest.war or into a sub-directory calledDBTest

Once deployed, point a browser athttp://localhost:8080/DBTest/test.jsp to view the fruits ofyour hard work.

Oracle 8i, 9i & 10g

0. Introduction

Oracle requires minimal changes from the MySQL configuration except for theusual gotchas :-)

Drivers for older Oracle versions may be distributed as *.zip files ratherthan *.jar files. Tomcat will only use*.jar files installed in$CATALINA_HOME/lib. Therefore classes111.zipor classes12.zip will need to be renamed with a.jarextension. Since jarfiles are zipfiles, there is no need to unzip and jar thesefiles - a simple rename will suffice.

For Oracle 9i onwards you should use oracle.jdbc.OracleDriverrather thanoracle.jdbc.driver.OracleDriver as Oracle have statedthat oracle.jdbc.driver.OracleDriver is deprecated and supportfor this driver class will be discontinued in the next major release.

1. Context configuration

In a similar manner to the mysql config above, you will need to define yourDatasource in yourContext. Here we define aDatasource called myoracle using the thin driver to connect as user scott,password tiger to the sid called mysid. (Note: with the thin driver this sid isnot the same as the tnsname). The schema used will be the default schema for theuser scott.

Use of the OCI driver should simply involve a changing thin to oci in the URL string.
<Resource name="jdbc/myoracle" auth="Container"
              type="javax.sql.DataSource" driverClassName="oracle.jdbc.OracleDriver"
              url="jdbc:oracle:thin:@127.0.0.1:1521:mysid"
              username="scott" password="tiger" maxActive="20" maxIdle="10"
              maxWait="-1"/> 
2. web.xml configuration

You should ensure that you respect the element ordering defined by the DTD when youcreate you applications web.xml file.
<resource-ref>
 <description>Oracle Datasource example</description>
 <res-ref-name>jdbc/myoracle</res-ref-name>
 <res-type>javax.sql.DataSource</res-type>
 <res-auth>Container</res-auth>
</resource-ref>
3. Code example

You can use the same example application as above (asuming you create the required DBinstance, tables etc.) replacing the Datasource code with something like
Context initContext = new InitialContext();
Context envContext  = (Context)initContext.lookup("java:/comp/env");
DataSource ds = (DataSource)envContext.lookup("jdbc/myoracle");
Connection conn = ds.getConnection();
//etc.

Postgre SQL

0. Introduction

PostgreSQL is configured in a similar manner to Oracle.

1. Required files

Copy the Postgres JDBC jar to $CATALINA_HOME/lib. As with Oracle, thejars need to be in this directory in order for DBCP's Classloader to findthem. This has to be done regardless of which configuration step you take next.

2. Resource configuration

You have two choices here: define a datasource that is shared across all Tomcatapplications, or define a datasource specifically for one application.

2a. Shared resource configuration

Use this option if you wish to define a datasource that is shared acrossmultiple Tomcat applications, or if you just prefer defining your datasourcein this file.

This author has not had success here, although others have reported so.Clarification would be appreciated here.
<Resource name="jdbc/postgres" auth="Container"
          type="javax.sql.DataSource" driverClassName="org.postgresql.Driver"
          url="jdbc:postgresql://127.0.0.1:5432/mydb"
          username="myuser" password="mypasswd" maxActive="20" maxIdle="10" maxWait="-1"/>
2b. Application-specific resource configuration

Use this option if you wish to define a datasource specific to your application,not visible to other Tomcat applications. This method is less invasive to yourTomcat installation.

Create a resource definition for your Context.The Context element should look something like the following.
<Context>

<Resource name="jdbc/postgres" auth="Container"
          type="javax.sql.DataSource" driverClassName="org.postgresql.Driver"
          url="jdbc:postgresql://127.0.0.1:5432/mydb"
          username="myuser" password="mypasswd" maxActive="20" maxIdle="10"
maxWait="-1"/>
</Context>
3. web.xml configuration
<resource-ref>
 <description>postgreSQL Datasource example</description>
 <res-ref-name>jdbc/postgres</res-ref-name>
 <res-type>javax.sql.DataSource</res-type>
 <res-auth>Container</res-auth>
</resource-ref>
4. Accessing the datasource

When accessing the datasource programmatically, remember to prependjava:/comp/env to your JNDI lookup, as in the following snippet ofcode. Note also that "jdbc/postgres" can be replaced with any value you prefer, providedyou change it in the above resource definition file as well.
InitialContext cxt = new InitialContext();
if ( cxt == null ) {
   throw new Exception("Uh oh -- no context!");
}

DataSource ds = (DataSource) cxt.lookup( "java:/comp/env/jdbc/postgres" );

if ( ds == null ) {
   throw new Exception("Data source not found!");
}