DB Test

准备环境:
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 中
添加如下

...

??? ?????? docBase="/usr/local/kinetic/tomcat5/server/webapps/manager">
?

限制ip访问配置
???????? docBase="/usr/local/kinetic/tomcat5/server/webapps/manager">
???????? ??????????????? allow="127.0.0.1"/>

测试为: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")
????? ???????????? 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.设置
????????????
????????????? removeAbandoned
????????????? true
???????????

?可使失效的数据连接重新启用.
配套设置
??
????????????
????????????? removeAbandonedTimeout
????????????? 60
???????????
失效时间
如果要写入日志
设置
???????????
????????????? logAbandoned
????????????? true
???????????
以上三个默认都是false
b.以mysql为例,配置数据连接池
c.配置新的用户与数据库,必须设定密码,空密码将导致连接失败
e.
指定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中中添加
??????? debug="5" reloadable="true" crossContext="true">

? ???????????? prefix="localhost_DBTest_log." suffix=".txt"
???????????? timestamp="true"/>

? ?????????????? auth="Container"
?????????????? type="javax.sql.DataSource"/>

?
???
????? factory
????? org.apache.commons.dbcp.BasicDataSourceFactory
???

???
???
????? maxActive
????? 100
???

???
???
????? maxIdle
????? 30
???

???
???
????? maxWait
????? 10000
???

???
???
???? username
???? javauser
???
???
???? password
???? javadude
???

???
???
???
???
?????? driverClassName
?????? com.mysql.jdbc.Driver
???
???
???
???
????? url
????? jdbc:mysql://localhost:3306/javatest?autoReconnect=true
???

????????
????????????? removeAbandoned
????????????? true
???????????

???
????????????? removeAbandonedTimeout
????????????? 60
???????????
?????
????????????? logAbandoned
????????????? true
???????????
?

f.在web服务中调用.配置web.xml 如:
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">
? MySQL Test App
?
????? DB Connection
????? jdbc/TestDB
????? javax.sql.DataSource
????? Container
?

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

select id, foo, bar from testdata

?
??? DB Test
?
?

Results

?

??? Foo ${row.foo}

??? Bar ${row.bar}

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%in
运行命令: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 中找到

???
??? 去掉注释

添加配置字段:keystoreFile="/conf/.keystore" keystorePass="198277"
如:
??
??? ?????????????? 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 中添加

Success
/
GET
POST

CONFIDENTIAL

f.用上提为例就是
修改web.xml 为
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">

?MySQL Test App

Success
/
GET
POST

CONFIDENTIAL

?
????? DB Connection
????? jdbc/TestDB
????? javax.sql.DataSource
????? Container
?

访问:
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中添加

web-user

?BASIC
?My Member Area

修改后的web.xml如:
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">

?MySQL Test App

Success
/
GET
POST

web-user

CONFIDENTIAL

?BASIC
?My Member Area

?
?
????? DB Connection
????? jdbc/TestDB
????? javax.sql.DataSource
????? Container
?

测试:
http://localhost:8080/DBTest/test.jsp
将通过ssl连接,并进行表单认证.用户密码可在user_roles,和users中添加.

5.中文乱码问题:
mysql 默认编码 iso
tomcat request 传输编码 iso
如果要显示中文
在*.jsp中添加

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

如果是数据传输中的乱码(如用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;
?}

例如:
当你发现你在mysql中想搜索中文是遇到编码问题时使用:
String sql = "select sum(number) from studentTable where grade="+toISO("大三");
当你从mysql数据库中读出的数据送到jsp显示为乱码时
rs = st.executeQuery(...);
studentBean.setStudentName(toGBK(rs.getString("studentName")));

以上配置都测试成功.主要参考tomcat5.0的帮助文档.其中如有错误的认识,还望不吝赐教.

责任编辑：H.R.M

DB Test

Results

分类导航