本文以目前最流行的MySQL为例，讲解JSP连接MySQL数据库的基本步骤，如果你了解MySQL可跳过第一步。
下载列表：
1.J2SE 5.0：
http://java.sun.com/j2se/1.5.0/download-netbeans.html
2.jakarta-tomcat-5.5.9：
http://mirror.vmmatrix.net/apache/dist/jakarta/tomcat-5/v5.5.9/bin/jakarta-tomcat-5.5.9.exe
3.MySQL 4.1 Downloads：
http://dev.mysql.com/downloads/mysql/4.1.html
4.mysql-connector-java-3.2.0-alpha.zip：
http://dev.mysql.com/get/Downloads/Connector-J/mysql-connector-java-3.2.0-alpha.zip/from/pick
在进行JDBC连接前，先解压缩mysql-connector-java-3.2.0-alpha.zip，将其中的mysql-connector-java-3.2.0-alpha-bin.jar取出，置于<%TOMCAT_HOME%>commonlib中。
接下来，让我们一起进入精彩的JDBC之旅。
一.新建用户及数据库
操作步骤如下：
C:\Documents and Settings\Administrator>d:
D:\>cd mysql\bin
D:\MySQL\bin>mysql -u root -p
Enter password: *******
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 51 to server version: 4.1.12a-nt
Type ';help;'; or ';\h'; for help. Type ';\c'; to clear the buffer.
mysql> GRANT ALL PRIVILEGES ON jcc.* TO jcc@localhost IDENTIFIED BY ';jsp.com.cn';
WITH GRANT OPTION;
Query OK, 0 rows affected (0.01 sec)
mysql> USE mysql;
Database changed
mysql> SELECT Host,User,Password FROM user;
+-----------+------+-------------------------------------------+
| Host | User | Password |
+-----------+------+-------------------------------------------+
| localhost | root | *60D5B730382EC2170CA366DE181767E4C5343DE8 |
| % | jsp | *C22AB0FD8A289C7D337C9998B63B8EA8335E5F35 |
| localhost | jcc | *C22AB0FD8A289C7D337C9998B63B8EA8335E5F35 |
+-----------+------+-------------------------------------------+
3 rows in set (0.01 sec)
mysql> exit
Bye
D:\MySQL\bin>mysql -u jcc -p
Enter password: **********
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 57 to server version: 4.1.12a-nt
Type ';help;'; or ';\h'; for help. Type ';\c'; to clear the buffer.
mysql> CREATE DATABASE jcc;
Query OK, 1 row affected (0.02 sec)
mysql> USE jcc;
Database changed
mysql> CREATE TABLE user(
-> id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
-> name VARCHAR(8) NOT NULL
-> );
Query OK, 0 rows affected (0.08 sec)
mysql> INSERT INTO user (name) VALUES (';Corebit';);
Query OK, 1 row affected (0.03 sec)
mysql> INSERT INTO user (name) VALUES (';Ivan';);
Query OK, 1 row affected (0.03 sec)
mysql> SELECT * FROM user;
+----+---------+
| id | name |
+----+---------+
| 1 | Corebit |
| 2 | Ivan |
+----+---------+
2 rows in set (0.00 sec)
mysql>
二.在<%wwwroot%>/下，新建MySQL数据库连接文件Select.jsp
Select.jsp源码如下：
<%@page contentType="text/html;charset=gb2312"%>
<%@page import="java.sql.*"%>
<%
try{
Class.forName("com.mysql.jdbc.Driver").newInstance();
}
catch(ClassNotFoundException e){
out.print(e);
}
try{
String url = "jdbc:mysql://localhost/jcc";
Connection conn = DriverManager.getConnection(url,"jcc","jsp.com.cn");
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("SELECT * FROM user");
out.println("User-list"+"
");
while(rs.next()){
out.print(rs.getString(1)+" ");
out.print(rs.getString(2)+"
");
}
rs.close();
stmt.close();
conn.close();
}
catch(Exception ex){
out.print(ex);
}
%>
三.运行http://localhost/Select.jsp，显示结果如下：
User-list
1 Corebit
2 Ivan
则表示数据库连接成功！恭喜！恭喜！
否则请检查数据库连接器版本，出错可能性比较高！
＊注：文件名Select.jsp区分大小写！
＊附：文中MySQL指令简介：
mysql -u root -p #以root用户权限登录，-p说明密码不为空，不推荐直接使用mysql -u [user] -p [password]，此时shell会将密码记录在历史记录中。
GRANT ALL PRIVILEGES ON jcc.* TO jcc@localhost IDENTIFIED BY ';jsp.com.cn'; WITH GRANT OPTION; #新建授权用户jcc，密码为jsp.com.cn，只限于本机登录，访问权限只限于jcc数据库，同时对jcc数据库拥有完全控制权。
其余指令如下（注释略）：
CREATE DATABASE jcc;
USE jcc;
CREATE TABLE user(
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(8) NOT NULL
);
INSERT INTO user (name) VALUES (';Corebit';);
INSERT INTO user (name) VALUES (';Ivan';);
希望本文能对你的JSP连接MySQL数据库有所帮助！
|
