在LINUX中实现JSP联接MYSQL初级教程

一：安装MYSQL:

$sudo apt-get install mysql  mysql-admin mysql-query-browser
===============================================================
mysql-admin - GUI tool for intuitive MySQL administration

mysql-query-browser - Official GUI tool to query MySQL database

把mysql-connector-java-5.0.4-bin.jar驱动程序放到$CATALINA_HOME/common/lib目录下

二：安装TOMCAT：

$sudo  apt-get install  tomcat5.5 tomcat5.5-admin  tomcat5.5-webapp
==================================================================
tomcat5.5 - Java Servlet 2.4 engine with JSP 2.0 support
tomcat5.5-admin - Java Servlet engine -- admin & manager web interfaces
tomcat5.5-webapps - Java Servlet engine -- documentation and example web applications
tomcat 默认安装后在/usr/share/tomcat5.5/目录下，在/var/lib/tomcat5.5生成其对应的文件
启动TOMCAT的命令是： sudo /usr/share/tomcat5.5/bin/startup.sh
关闭TOMCAT命令是： sudo /usr/share/tomcat5.5/bin/shutdown.sh




三：设置其环境变量：
在/etc/profile中加上如下的：

#java environment variable settings

JAVA_HOME=/usr/lib/jvm/java-1.5.0-sun
export JAVA_HOME

JRE_HOME=/usr/lib/jvm/java-1.5.0-sun
export JRE_HOME

CLASSPATH=/usr/lib/jvm/java-1.5.0-sun/lib:/usr/share/tomcat5.5/common/lib:$JAVA_HOME/jre/lib/rt.jar
export CLASSPATH

CATALINA_BASE=/usr/share/tomcat5.5
export CATALINA_BASE

CATALINA_HOME=/usr/share/tomcat5.5
export CATALINA_HOME

TOMCAT_HOME=/usr/share/tomcat5.5
export TOMCAT_HOME

PATH=$PATH:$HOME/bin:$JAVA_HOME/bin:/usr/share/tomcat5.5/bin
export PATH


四：在$TOMCAT_HOME/webapps/ROOT/下新建testmysql.jsp:

   内容如下：
    <%@ page contentType="text/html; charset=gb2312" %>
 
<%@ page language="java" %>
 
<%@ page import="com.mysql.jdbc.Driver" %>
 
<%@ page import="java.sql.*" %>
 
<%
//驱动程序名
 
String driverName="com.mysql.jdbc.Driver";
 
//数据库用户名
 
String userName="your name";
 
//密码
 
String userPasswd="your passwd";
 
//数据库名
 
String dbName="test";
 
//表名
 
String tableName="student";
 
//联结字符串
 
String url="jdbc:mysql://localhost/"+dbName+"?user="+userName+"&password="+userPasswd;
 
Class.forName("com.mysql.jdbc.Driver").newInstance();
 
Connection connection=DriverManager.getConnection(url);
 
Statement statement = connection.createStatement();
 
String sql="SELECT * FROM "+tableName;
 
ResultSet rs = statement.executeQuery(sql); 
 
//获得数据结果集合
 
ResultSetMetaData rmeta = rs.getMetaData();
 
//确定数据集的列数，亦字段数
 
int numColumns=rmeta.getColumnCount();
 
// 输出每一个数据值
 
out.print("sno"); 
 
out.print("|");
 
out.print("sname");
 
out.print("|");


out.print("<br>");
 
while(rs.next()) {
 
out.print(rs.getString(1)); 
 
out.print("|");
 
out.print(rs.getString(2));
 
out.print("|");



} 
 
out.print("<br>");
 
out.print("database access success,congratulation!"); 
 
rs.close(); 
 
statement.close(); 
 
connection.close(); 
%> 




注意出现的问题可如下解决：TOMCAT5.5在UBUNTU中默认访问端口是8180，可在$TOMCAT_HOME下/conf/server.xml中修改端口，。如果出现访问权限请修改 /usr/share/tomcat5.5/conf/catalina.policy这个文件，给予其访问的权限。比如添加：// The permissions granted to the context root directory apply to JSP pages.
        grant codeBase "file:$CATALINA_HOME/webapps/jsp-examples/-" {    
      permission java.net.SocketPermission "jdbc:mysql://localhost:3306", "connect";
      permission java.net.SocketPermission "localhost:8080", "connect";};
在conf/tomcat-usersxml中增加管理员的账户，比如
<?xml version='1.0' encoding='utf-8'?>
<tomcat-users>
  <role rolename="tomcat"/>
  <role rolename="role1"/>
  <role rolename="manager"/>
  <role rolename="admin"/>
  <user username="tomcat" password="tomcat" roles="tomcat"/>
  <user username="aaron" password="aaron" roles="admin,manager"/>
  <user username="role1" password="tomcat" roles="role1"/>
  <user username="both" password="tomcat" roles="tomcat,role1"/>
</tomcat-users>