Tomcat5.0+MySql配置JDBC，DBCP，SSL

中国IT动力,最新最全的IT技术教程

最新100篇 | 推荐100篇 | 专题100篇 | 排行榜 | 搜索 | 在线API文档 | 网通镜像

当前位置：> 程序开发 > 编程语言 > Java > 数据库

Tomcat5.0+MySql配置JDBC，DBCP，SSL

作者:ycg01 时间:2006-09-26 10:22 出处:Java研究组织责编:月夜寒箫

摘要：Tomcat5.0+MySql配置JDBC，DBCP，SSL

准备环境:

1.j2sdk-1_4_2-windows-i586.exe

//jdk

2.mysql-4.0.20d-win.zip

//mysql数据库

3.mysqlcc-0.9.4-win32.zip

//mysqlGUI控制

4.jakarta-tomcat-5.0.27.exe

//tomcat服务器

5.mysql-connector-java-3.0.14-production.zip

//内含mysql驱动

安装步骤:

1.安装jdk

2.安装tomcat

3.安装mysql

4.安装mysqlcc

5.将驱动包解压,拷贝mysql-connector-java-3.0.14-production-bin.jar

到tomcat/common/lib下

或者下载mm.mysql-2.0.14-you-must-unjar-me.jar，

解压后拷贝其中的mm.mysql-2.0.14-bin.jar

Tomcat5.0配置本例使用安装密码 198277

1.配置manager 管理应用程序

在conf/server.xml 中

添加如下：

<Service name="Catalina">
            ...
            <Context path="/manager"
            debug="0" privileged="true"
            docBase="/usr/local/kinetic
            /tomcat5/server/webapps/manager">
            </Context>
            </Service>

限制IP访问配置

<Context path="/manager"
            debug="0" privileged="true"
            docBase="/usr/local/kinetic
            /tomcat5/server/webapps/manager">
            <Valve className="org.apache.
            catalina.valves.RemoteAddrValve"
            allow="127.0.0.1"/>
            </Context>

测试为:http://localhost:8080/manager/html

2、配置JDBCRealm容器管理安全,以mysql-4.0数据库为例

a.拷贝驱动mm.mysql-2.0.14-bin.jar到common/lib/下

b.在数据库ycg中建表

create table users
            (
            user_name
            varchar(15) not null primary key,
            user_pass
            varchar(15) not null
            );
            create table user_roles
            (
            user_name
            varchar(15) not null,
            role_name
            varchar(15) not null,
            primary key (user_name, role_name)
            );

c.修改server.xml如下(默认数据库为root,无密码,如果有形如:

connectionURL="jdbc:mysql:
            //localhost/authority?
            user=dbuser&password=dbpass")
            <Realm  className="org.apache.catalina.
            realm.JDBCRealm" debug="99"
            driverName="
            org.gjt.mm.mysql.Driver"
            connectionURL="jdbc:mysql:
            //localhost/ycg?user=root"
            connectionName=""
            connectionPassword=""
            userTable="users"
            userNameCol="user_name"
            userCredCol="user_pass"
            userRoleTable="user_roles"
            roleNameCol="role_name" />

d.在数据库中添加入tomcat的默认配置数据:

e.启动mysql,启动tomcat,此后tomcat将从数据库中读用户规则认证.默认的conf/tomcat-users.xml失效

3.DBCP的配置

a.设置

<parameter>
            <name>removeAbandoned</name>
            <value>true</value>
            </parameter>

可使失效的数据连接重新启用.

配套设置

<parameter>
            <name>removeAbandonedTimeout</name>
            <value>60</value>
            </parameter>

失效时间

如果要写入日志

设置

<parameter>
            <name>logAbandoned</name>
            <value>true</value>
            </parameter>

以上三个默认都是false

b.以mysql为例,配置数据连接池

c.配置新的用户与数据库,必须设定密码,空密码将导致连接失败

指定root密码:

mysqladmin -u root -h localhost
            password "198277"

(需修改上面的jdbcrealm设置connectionURL="jdbc:mysql://localhost/ycg?user=root&password=198277")

命令mysql进入匿名连接到服务器

密码访问

shell> mysql -h host -u user -p
            Enter password: ********
            //如果root没有密码,以下是不成功的.(试过了)
            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);
            在conf/server.xml中<host></host>
            中添加
            <Context path="/DBTest" docBase="DBTest"
            debug="5" reloadable="true"
            crossContext="true">
            <Logger className="org.apache.
            catalina.logger.FileLogger"
            prefix="localhost_DBTest_log."
            suffix=".txt"
            timestamp="true"/>
            <Resource name="jdbc/TestDB"
            auth="Container"
            type="javax.sql.DataSource"/>
            <ResourceParams name="jdbc/TestDB">
            <parameter>
            <name>factory</name>
            <value>org.apache.commons.dbcp.
            BasicDataSourceFactory</value>
            </parameter>
            <!-- Maximum number of dB connections
            in pool. Make sure you
            configure your mysqld
            max_connections large enough to handle
            all of your db connections.
            Set to 0 for no limit.
            -->
            <parameter>
            <name>maxActive</name>
            <value>100</value>
            </parameter>
            <!-- Maximum number of idle dB
            connections to retain in pool.
            Set to 0 for no limit.
            -->
            <parameter>
            <name>maxIdle</name>
            <value>30</value>
            </parameter>
            <!-- Maximum time to wait for a
            dB connection to become available
            in ms, in this example 10
            seconds. An Exception is thrown if
            this timeout is exceeded.
            Set to -1 to wait indefinitely.
            -->
            <parameter>
            <name>maxWait</name>
            <value>10000</value>
            </parameter>
            <!-- MySQL dB username and
            password for dB connections  -->
            <parameter>
            <name>username</name>
            <value>javauser</value>
            </parameter>
            <parameter>
            <name>password</name>
            <value>javadude</value>
            </parameter>
            <!-- Class name for the old mm.
            mysql JDBC driver - uncomment
            this entry and comment next
            if you want to use this driver
            - we recommend using Connector/J though
            <parameter>
            <name>driverClassName</name>
            <value>org.gjt.mm.mysql.Driver</value>
            </parameter>
            -->
            <!-- Class name for the official
            MySQL Connector/J driver -->
            <parameter>
            <name>driverClassName</name>
            <value>com.mysql.jdbc.Driver</value>
            </parameter>
            <!-- The JDBC connection url for
            connecting to your MySQL dB.
            The autoReconnect=true argument
            to the url makes sure that the
            mm.mysql JDBC Driver will
            automatically reconnect if mysqld closed the
            connection.  mysqld by default
            closes idle connections after 8 hours.
            -->
            <parameter>
            <name>url</name>
            <value>jdbc:mysql://localhost:3306
            /javatest?autoReconnect=true</value>
            </parameter>
            <parameter>
            <name>removeAbandoned</name>
            <value>true</value>
            </parameter>
            <parameter>
            <name>removeAbandonedTimeout</name>
            <value>60</value>
            </parameter>
            <parameter>
            <name>logAbandoned</name>
            <value>true</value>
            </parameter>
            </ResourceParams>
            </Context>

f.在web服务中调用.配置web.xml 如:

<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>

g.测试用test.jsp

<%@ 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>

h.新建web应用

下载jakarta-taglibs-standard-1.1.0

copy jstl.jar and standard.jar to your web app's WEB-INF/lib

DBTest/
            WEB-INF/
            web.xml
            lib/
            jstl.jar
            standard.jar
            test.jsp

拷贝到webapps/ 下

i.启动mysql,tomcat

访问:

http://localhost:8080/DBTest/test.jsp

显示:

Results
            Foo hello
            Bar 12345

4.ssl的配置,以jdk1.4.2为例

a.进入%JAVA_HOME%\bin

运行命令:keytool -genkey -alias tomcat -keyalg RSA

以tomcat 安装密码为198277,ketool设置密码为198277为例

输入keystore密码： 198277

您的名字与姓氏是什么？

[Unknown]： ycg

您的组织单位名称是什么？

[Unknown]： nju

您的组织名称是什么？

[Unknown]： nju

您所在的城市或区域名称是什么？

[Unknown]： nanjing

您所在的州或省份名称是什么？

[Unknown]： jiangsu

该单位的两字母国家代码是什么

[Unknown]： nd

CN=ycg, OU=nju, O=nju, L=nanjing, ST=jiangsu, C=nd 正确吗？

[否]： y

输入的主密码

（如果和 keystore 密码相同，按回车）： 198277

b.在你的D:\Documents and Settings\的当前用户目录下可以找到.keystore文件.将其拷贝到conf/文件夹下.

c.在server.xml 中找到

<!--
            <Connector port="8443"
            maxThreads="150" minSpareThreads="25"
            maxSpareThreads="75"
            enableLookups="false"
            disableUploadTimeout="true"
            acceptCount="100" debug="0"
            scheme="https" secure="true"
            clientAuth="false" sslProtocol="TLS" />
            -->

去掉注释

添加配置字段:keystoreFile="/conf/.keystore" keystorePass="198277"

如:

<Connector port="8443"
            maxThreads="150" minSpareThreads=
            "25" maxSpareThreads="75"
            enableLookups="false"
            disableUploadTimeout="true"
            acceptCount="100" debug="0"
            scheme="https" secure="true"
            clientAuth="false" sslProtocol="TLS"
            keystoreFile="/conf/.keystore"
            keystorePass="198277"/>

d.测试为:

https://localhost:8443

e.在自己的程序中添加ssl认证方式为:

在web.xml 中添加

<security-constraint>
            <web-resource-collection>
            <web-resource-name>Success
            </web-resource-name>
            <url-pattern>/</url-pattern>
            <http-method>GET</http-method>
            <http-method>POST</http-method>
            </web-resource-collection>
            <user-data-constraint>
            <transport-guarantee>CONFIDENTIAL
            </transport-guarantee>
            </user-data-constraint>
            </security-constraint>

f.用上提为例就是

修改web.xml 为

<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>
            <security-constraint>
            <web-resource-collection>
            <web-resource-name>Success
            </web-resource-name>
            <url-pattern>/</url-pattern>
            <http-method>GET</http-method>
            <http-method>POST</http-method>
            </web-resource-collection>
            <user-data-constraint>
            <transport-guarantee>CONFIDENTIAL
            </transport-guarantee>
            </user-data-constraint>
            </security-constraint>
            <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>

访问:

https://localhost:8443/DBTest/test.jsp

g.如果与2配置的jdbcRealm结合起来进行表单认证

先在user_roles表中添加user_name:ycg role_name:web-user

在users表中添加user_name:ycg user_pass:198277

然后在web.xml中添加

<auth-constraint>
            <role-name>web-user</role-name>
            </auth-constraint>
            <login-config>
            <auth-method>BASIC</auth-method>
            <realm-name>My Member Area</realm-name>
            </login-config>

修改后的web.xml如:

<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>
            <security-constraint>
            <web-resource-collection>
            <web-resource-name>Success
            </web-resource-name>
            <url-pattern>/</url-pattern>
            <http-method>GET</http-method>
            <http-method>POST</http-method>
            </web-resource-collection>
            <auth-constraint>
            <role-name>web-user</role-name>
            </auth-constraint>
            <user-data-constraint>
            <transport-guarantee>CONFIDENTIAL
            </transport-guarantee>
            </user-data-constraint>
            </security-constraint>
            <login-config>
            <auth-method>BASIC</auth-method>
            <realm-name>My Member Area</realm-name>
            </login-config>
            <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>

测试:

http://localhost:8080/DBTest/test.jsp

将通过ssl连接,并进行表单认证.用户密码可在user_roles,和users中添加.

5.中文乱码问题:

mysql 默认编码 iso

tomcat request 传输编码 iso

如果要显示中文

在*.jsp中添加

<head>
            <%@ page
            language="java"
            contentType="text/html;
            charset=GB18030"
            pageEncoding="GB18030"
            %>
            </head>

如果是数据传输中的乱码(如用servlet从mysql数据库读出的数据)用以下两个转码函数转码，如果不清楚由哪种编码转成哪种编码，就多尝试。

//转码GBK转ISO
            public String toISO(String input)
            {
            try
            {
            byte[] bytes = input.getBytes("GBK");
            return new String(bytes,"ISO8859-1");
            }catch(Exception ex)
            {
            }
            return input;
            }
            //转码IS0转GBK
            public String toGBK(String input)
            {
            try {
            byte[] bytes =
            input.getBytes("ISO8859-1");
            return new String(bytes,"GBK");
            }catch(Exception ex) {
            }
            return input;
            }

关闭本页