JSP页中通过 useBean的方式访问数据库

时间：2008-12-23 来源：lvjinhua

文件:	jspchatroom.zip
大小:	4KB
下载:	下载

搞了好久，终于可以访问了。我的环境是： Java5+Tomcat6.0+Mysql5.1 1）将mysql-connector-java-5.1.7-bin.jar 拷贝到 C:\Tomcat6.0\lib 目录下； 2）将mysql-connector-java-5.1.7-bin.jar 拷贝到 java 安装目录的lib目录下，并在 CLASSPATH环境变量中加入该 jar文件； 3） javaBean要放到一个包里面，自己设定个名字就行； 4）将编译javaBean生成的class文件放到 tomcat 部署目录的 WEB-INF/classes/包名目录下； 5）在mysql中创建数据库 jspchatroom，并且创建表 userlist，并且在表中创建 user和mail两个字段，并且随便插入两行数据；目录结构： C:\Tomcat6.0\webapps\jspchatroom\WEB-INF\classes\conn\opdb.clas C:\Tomcat6.0\webapps\jspchatroom\dbtest.jsp C:\Tomcat6.0\webapps\jspchatroom\opdb.java opdb.java:

package conn;
import java.sql.*;
import java.util.*;
import sun.io.*;

public class opdb {
    Connection conn = null;
    Statement sqlStatement=null;
    ResultSet results = null;
    ResultSet retemp = null;
    ResultSetMetaData resultsMeta = null;
    boolean status;
    String sql;
    int columns;
    long rowcount = 0;

    public opdb() {
    }

    public static void main(String[] args) {
        opdb db = new opdb();
        db.doConnect();
        ResultSet rs = db.doSelect("select * from userlist");
        try{
        while(rs.next()) {
            System.out.println("User name:"+rs.getString("name"));
            System.out.println("User mail:"+rs.getString("mail"));
            System.out.println();
        }

        rs.close();
        } catch( SQLException e) {
            e.printStackTrace();
        }

        db.closeConn();
    }

    public void doConnect() {
        try{
            String driver="com.mysql.jdbc.Driver";
            String url = "jdbc:mysql://localhost:3306/jspchatroom";
            String user="root";
            String pwd ="passwordofroot";

            Class.forName(driver);
            conn = DriverManager.getConnection(url, user, pwd);
            this.status = true;

        }
        catch(Exception e)
        {
            this.status = false;
            System.out.println("Connect to database fail.");
            e.printStackTrace();
        }
    }

    public Connection getConn() {
        return this.conn;
    }

    public void closeConn() {
        try{
            if(this.conn!=null) {
                this.conn.close();
            }
            if(this.sqlStatement!=null) {
                this.sqlStatement.close();
            }
            if(this.results!=null) {
                this.results.close();
            }
            this.status=true;
        } catch (SQLException e) {
            e.printStackTrace();
            this.status=false;
        }
    }

    public synchronized ResultSet doSelect(String sql) {
        this.sql = sql;
        try {
            sqlStatement = this.conn.createStatement();
            retemp = sqlStatement.executeQuery(this.sql);
            this.rowcount = 0;
            while(this.retemp.next())
                this.rowcount++;
            if(retemp!=null) retemp.close();

            results = sqlStatement.executeQuery(this.sql);
            resultsMeta = results.getMetaData();
            this.columns = resultsMeta.getColumnCount();
            this.status = true;
            return results;
        } catch (SQLException e) {
            this.status = false;
            e.printStackTrace();
            return null;
        }
    }

    public int getColumns() {
        return this.columns;
    }

    public long getRowcount() {
        return this.rowcount;
    }

    public synchronized void doUpdate(String sql) {
        try{
            this.sql = sql;
            Statement sqlStatement = this.conn.createStatement();
            sqlStatement.executeUpdate(sql);
            status = true;
        } catch(SQLException e) {
            status = false;
            e.printStackTrace();
        }
    }

    //insert into userlist(name, mail) values("name1", "mail1");

    public synchronized void doInsert(String sql) {
        this.sql = sql;
        try {
            sqlStatement = this.conn.createStatement();
            sqlStatement.executeUpdate(sql);
            this.status = true;
        } catch(SQLException e) {
            this.status = false;
            e.printStackTrace();
        }
    }

    public synchronized void doDelete(String sql) {
        this.sql = sql;
        try{
            sqlStatement = this.conn.createStatement();
            sqlStatement.executeUpdate(sql);
            status = true;
        } catch(SQLException e) {
            status = false;
            e.printStackTrace();
        }
    }

    public boolean getSuccess() {
        return status;
    }
}

dbtest.jsp:

<!DOCTYPE html
PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">

    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=gb2312" />
        <meta http-equiv="Content-Language" content="zh-cn" />
    </head>

    <title>测试数据库访问</title>

    <body>
        <%@ page language="java" import="java.sql.*" %>
        <jsp:useBean id="opdb1" scope="page" class="conn.opdb" />
        <%
        opdb1.doConnect();
        //Connection conn = opdb1.getConn();

        String sql = "select user, mail from userlist;";
        ResultSet rs = opdb1.doSelect("select * from userlist");
        while(rs.next()) {
        out.print("name:"+rs.getString("name")+"<br />");
        out.print("mail:"+rs.getString("mail")+"<br /><br />");
        }
        rs.close();
        %>

    </body>

</html>

mysql script:

CREATE DATABASE `jspchatroom` /*!40100 DEFAULT CHARACTER SET latin1 */;

DROP TABLE IF EXISTS `jspchatroom`.`userlist`;
CREATE TABLE `jspchatroom`.`userlist` (
  `name` varchar(50) NOT NULL,
  `mail` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
insert into `jspchatroom`.`userlist`(name, mail) values("name1", "mail1");
insert into `jspchatroom`.`userlist`(name, mail) values("name2", "mail2");
insert into `jspchatroom`.`userlist`(name, mail) values("name3", "mail3");