JSP连接access数据库

一个用jsp连接Access数据库的代码。
要正确的使用这段代码，你需要首先在Access数据库里创建一username表，表里面创建两个字符型的字段，字段名分别为：uid,pwd，然后插入几条测试数据。
欢迎各位提出改进的意见。
以下用两种方式来实现jsp连接access数据库。

第一种JSP形式。

<span id="articlecontent"><<span style="color:#00ff;">%</span>@ page <span style="color:#0800;">contentType</span><span style="color:#00ff;">=</span><span style="color:#ff0ff;">"text/html; charset=gb2312"</span> <span style="color:#0800;">language</span><span style="color:#00ff;">=</span><span style="color:#ff0ff;">"java"</span>
import<span style="color:#00ff;">=</span><span style="color:#ff0ff;">"java.sql.*"</span><span style="color:#00ff;">%</span><span style="color:#00ff;">></span>
<span style="color:#00ff;"><</span>meta http<span style="color:#00ff;">-</span>equiv<span style="color:#00ff;">=</span><span style="color:#ff0ff;">"Content-Type"</span> content<span style="color:#00ff;">=</span><span style="color:#ff0ff;">"text/html; charset=gb2312"</span><span style="color:#00ff;">></span>
<span style="color:#00ff;"><</span><span style="color:#00ff;">%</span>
    <span style="color:#08080;">/*********************************
     *********  JDBC_ODBC连接Access数据库，不需要设置数据源
     *********  Date: 2005.8
     *********  Email:fanwsp@126.com
     *********  Author: DreamTime [<a target=_blank style="color: rgb(0, 0, 0); " href="http://www.freeage.cn/" target="_blank">梦想年华</a>]
     *********  有任何欢迎提出指正   
     **********************************/</span>
     
    
    <span style="color:#08080;">// ******* 数据库连接代码　开始  *****</span>
    <span style="color:#08080;">//异常处理语句</span>
    try
    <span style="color:#00ff;">{</span>
        <span style="color:#08080;">//以下几项请自行修改</span>
        <span style="color:#00a0;"><strong>String</strong></span> spath <span style="color:#00ff;">=</span> <span style="color:#ff0ff;">"data/test.mdb"</span><span style="color:#00ff;">;</span><span style="color:#08080;">//Access 数据库路径</span>
        <span style="color:#00a0;"><strong>String</strong></span> dbpath <span style="color:#00ff;">=</span> application<span style="color:#00ff;">.</span>getRealPath<span style="color:#00ff;">(</span>spath<span style="color:#00ff;">)</span><span style="color:#00ff;">;</span><span style="color:#08080;">//转化成物理路径</span>
        <span style="color:#00a0;"><strong>String</strong></span> dbname <span style="color:#00ff;">=</span> <span style="color:#ff0ff;">""</span><span style="color:#00ff;">;</span>        <span style="color:#08080;">//Acvess 数据库用户名，没有则为空</span>
        <span style="color:#00a0;"><strong>String</strong></span> user <span style="color:#00ff;">=</span> <span style="color:#ff0ff;">""</span><span style="color:#00ff;">;</span>        <span style="color:#08080;">//Access 数据库密码，没有则为空</span>
<span style="color:#08080;">//数据库连接字符串 </span>
        <span style="color:#00a0;"><strong>String</strong></span> <span style="color:#0800;">url</span> <span style="color:#00ff;">=</span><span style="color:#ff0ff;">"jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ="</span><span style="color:#00ff;">+</span>dbpath<span style="color:#00ff;">;</span> 
        <span style="color:#08080;">//加载驱动程序</span>
        Class<span style="color:#00ff;">.</span>forName<span style="color:#00ff;">(</span><span style="color:#ff0ff;">"sun.jdbc.odbc.JdbcOdbcDriver"</span><span style="color:#00ff;">)</span><span style="color:#00ff;">;</span>
        <span style="color:#08080;">//建立连接</span>
        Connection conn<span style="color:#00ff;">=</span> DriverManager<span style="color:#00ff;">.</span>getConnection<span style="color:#00ff;">(</span><span style="color:#0800;">url</span><span style="color:#00ff;">)</span><span style="color:#00ff;">;</span> 
        <span style="color:#08080;">//创建语句对象</span>
        Statement stmt<span style="color:#00ff;">=</span>conn<span style="color:#00ff;">.</span>createStatement<span style="color:#00ff;">(</span>ResultSet<span style="color:#00ff;">.</span>TYPE_SCROLL_SENSITIVE<span style="color:#00ff;">,</span>ResultSet<span style="color:#00ff;">.</span>CONCUR_UPDATABLE<span style="color:#00ff;">)</span><span style="color:#00ff;">;</span>
        <span style="color:#08080;">// ****   数据库连接代码　结束 ******</span>
        
        <span style="color:#08080;">//*********  测试数据代码开始  ******</span>
        <span style="color:#08080;">//请在数据库中建立username表，表中建立两个字段uid和pwd,类型为文本型</span>
        <span style="color:#00a0;"><strong>String</strong></span> sql <span style="color:#00ff;">=</span> <span style="color:#ff0ff;">"select * from username"</span><span style="color:#00ff;">;</span>
        ResultSet rs <span style="color:#00ff;">=</span> stmt<span style="color:#00ff;">.</span>executeQuery<span style="color:#00ff;">(</span>sql<span style="color:#00ff;">)</span><span style="color:#00ff;">;</span>
        <span style="color:#8000;"><strong>while</strong></span><span style="color:#00ff;">(</span>rs<span style="color:#00ff;">.</span>next<span style="color:#00ff;">(</span><span style="color:#00ff;">)</span><span style="color:#00ff;">)</span>
        <span style="color:#00ff;">{</span>
            out<span style="color:#00ff;">.</span><span style="color:#500a0;">print</span><span style="color:#00ff;">(</span><span style="color:#ff0ff;">"用户名:"</span> <span style="color:#00ff;">+</span> rs<span style="color:#00ff;">.</span>getString<span style="color:#00ff;">(</span><span style="color:#ff0ff;">"uid"</span><span style="color:#00ff;">)</span><span style="color:#00ff;">)</span><span style="color:#00ff;">;</span>
            out<span style="color:#00ff;">.</span><span style="color:#500a0;">print</span><span style="color:#00ff;">(</span><span style="color:#ff0ff;">"  密码:"</span> <span style="color:#00ff;">+</span> rs<span style="color:#00ff;">.</span>getString<span style="color:#00ff;">(</span><span style="color:#ff0ff;">"pwd"</span><span style="color:#00ff;">)</span> <span style="color:#00ff;">+</span> <span style="color:#ff0ff;">"<br>"</span><span style="color:#00ff;">)</span><span style="color:#00ff;">;</span>
        <span style="color:#00ff;">}</span>
        out<span style="color:#00ff;">.</span><span style="color:#500a0;">print</span><span style="color:#00ff;">(</span><span style="color:#ff0ff;">"<br>恭喜你！数据库连接成功！"</span><span style="color:#00ff;">)</span><span style="color:#00ff;">;</span>
        rs<span style="color:#00ff;">.</span>close<span style="color:#00ff;">(</span><span style="color:#00ff;">)</span><span style="color:#00ff;">;</span>                                    <span style="color:#08080;">//关闭记录集对象</span>
        stmt<span style="color:#00ff;">.</span>close<span style="color:#00ff;">(</span><span style="color:#00ff;">)</span><span style="color:#00ff;">;</span>                                <span style="color:#08080;">//关闭语句对象</span>
        conn<span style="color:#00ff;">.</span>close<span style="color:#00ff;">(</span><span style="color:#00ff;">)</span><span style="color:#00ff;">;</span>                                <span style="color:#08080;">//关闭连接对象</span>
    <span style="color:#00ff;">}</span>catch<span style="color:#00ff;">(</span>Exception e<span style="color:#00ff;">)</span><span style="color:#00ff;">{</span>
        out<span style="color:#00ff;">.</span><span style="color:#500a0;">print</span><span style="color:#00ff;">(</span><span style="color:#ff0ff;">"数据库连接错误！,错误信息如下：<br>"</span><span style="color:#00ff;">)</span><span style="color:#00ff;">;</span>
        out<span style="color:#00ff;">.</span><span style="color:#500a0;">print</span><span style="color:#00ff;">(</span>e<span style="color:#00ff;">.</span>getMessage<span style="color:#00ff;">(</span><span style="color:#00ff;">)</span><span style="color:#00ff;">)</span><span style="color:#00ff;">;</span>
        <span style="color:#00ff;">}</span>
    <span style="color:#08080;">//*******   测试数据代码结束  *******</span>
<span style="color:#00ff;">%</span><span style="color:#00ff;">></span></span>

<span id="articlecontent">第二种，JavaBean的形式。</span>
<span id="articlecontent"><span style="color:#08080;">/*
***************************************
  *            作用： java连接Access数据库代码  
*    作者：<a target=_blank style="color: rgb(0, 0, 0); " href="http://www.freeage.cn/" target="_blank">梦想年华</a>
*    Email:fanwsp@126.com
*    Author:<a target=_blank style="color: rgb(0, 0, 0); " href="http://www.freeage.cn/" target="_blank">梦想年华</a>
*     CopyRight(c)2005-2006 by DreamTime 
******** *******************************
*/</span>
[/color]

package conn<span style="color:#00ff;">;</span>                            <span style="color:#08080;">//导入包</span>
import java<span style="color:#00ff;">.</span>sql<span style="color:#00ff;">.</span><span style="color:#00ff;">*</span><span style="color:#00ff;">;</span>                        <span style="color:#08080;">//导入数据库操作的类</span>

<span style="color:#0a0c0;"><strong>public</strong></span> class DBConnAccess                <span style="color:#08080;">//构造方法，初始化</span>
<span style="color:#00ff;">{</span>

    <span style="color:#0a0c0;"><strong>private</strong></span> Connection conn<span style="color:#00ff;">;</span>            <span style="color:#08080;">//连接对象</span>
    <span style="color:#0a0c0;"><strong>private</strong></span> Statement stmt<span style="color:#00ff;">;</span>                <span style="color:#08080;">//语句对象</span>
    <span style="color:#0a0c0;"><strong>private</strong></span> ResultSet rs<span style="color:#00ff;">;</span>                <span style="color:#08080;">//结果集对象</span>
    <span style="color:#0a0c0;"><strong>private</strong></span> <span style="color:#00a0;"><strong>String</strong></span> accessdriver<span style="color:#00ff;">;</span>        <span style="color:#08080;">//保存Access驱动程序字符串</span>
    <span style="color:#0a0c0;"><strong>private</strong></span> <span style="color:#00a0;"><strong>String</strong></span> accessURL<span style="color:#00ff;">;</span>             <span style="color:#08080;">//保存Access连接字符串</span>

    <span style="color:#0a0c0;"><strong>public</strong></span> DBConnAccess<span style="color:#00ff;">(</span><span style="color:#00ff;">)</span>
    <span style="color:#00ff;">{</span>
        <span style="color:#08080;">//Access驱动程序</span>
        accessdriver <span style="color:#00ff;">=</span> <span style="color:#ff0ff;">"sun.jdbc.odbc.JdbcOdbcDriver"</span><span style="color:#00ff;">;</span>        
        <span style="color:#08080;">//连接字符串</span>
        accessURL <span style="color:#00ff;">=</span> <span style="color:#ff0ff;">"jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ="</span><span style="color:#00ff;">;</span>
        conn <span style="color:#00ff;">=</span> <span style="color:#00a0;"><strong>null</strong></span><span style="color:#00ff;">;</span>
    <span style="color:#00ff;">}</span>

    <span style="color:#08080;">//该方法从参数得到数据库路径，并加到连接字符串后面,然后再建立连接</span>
    <span style="color:#0a0c0;"><strong>public</strong></span> Connection getConnToAccess<span style="color:#00ff;">(</span><span style="color:#00a0;"><strong>String</strong></span> dbpath<span style="color:#00ff;">)</span><span style="color:#00ff;">{</span>
        try<span style="color:#00ff;">{</span>
            accessURL<span style="color:#00ff;">=</span>accessURL<span style="color:#00ff;">+</span>dbpath<span style="color:#00ff;">;</span>
            Class<span style="color:#00ff;">.</span>forName<span style="color:#00ff;">(</span>accessdriver<span style="color:#00ff;">)</span><span style="color:#00ff;">;</span>
            conn <span style="color:#00ff;">=</span> DriverManager<span style="color:#00ff;">.</span>getConnection<span style="color:#00ff;">(</span>accessURL<span style="color:#00ff;">)</span><span style="color:#00ff;">;</span>
            <span style="color:#00ff;">}</span>catch<span style="color:#00ff;">(</span>Exception e<span style="color:#00ff;">)</span><span style="color:#00ff;">{</span>
                <span style="color:#00a0;"><strong>System</strong></span><span style="color:#00ff;">.</span>out<span style="color:#00ff;">.</span>println<span style="color:#00ff;">(</span><span style="color:#ff0ff;">"操作数据库出错，请仔细检查"</span><span style="color:#00ff;">)</span><span style="color:#00ff;">;</span>
                <span style="color:#00a0;"><strong>System</strong></span><span style="color:#00ff;">.</span>err<span style="color:#00ff;">.</span>println<span style="color:#00ff;">(</span>e<span style="color:#00ff;">.</span>getMessage<span style="color:#00ff;">(</span><span style="color:#00ff;">)</span><span style="color:#00ff;">)</span><span style="color:#00ff;">;</span>
            <span style="color:#00ff;">}</span>
        <span style="color:#8000;"><strong>return</strong></span> conn<span style="color:#00ff;">;</span>
    <span style="color:#00ff;">}</span>
    
    
        
   <span style="color:#08080;">//关闭数据库连接</span>
    <span style="color:#0a0c0;"><strong>public</strong></span> <span style="color:#00ff;">void</span> close<span style="color:#00ff;">(</span><span style="color:#00ff;">)</span>
    <span style="color:#00ff;">{</span>
        try<span style="color:#00ff;">{</span>
            <span style="color:#08080;">//rs.close();</span>
            <span style="color:#08080;">//stmt.close();</span>
            conn<span style="color:#00ff;">.</span>close<span style="color:#00ff;">(</span><span style="color:#00ff;">)</span><span style="color:#00ff;">;</span> 
        <span style="color:#00ff;">}</span>catch<span style="color:#00ff;">(</span>SQLException sqlexception<span style="color:#00ff;">)</span><span style="color:#00ff;">{</span>
            sqlexception<span style="color:#00ff;">.</span>printStackTrace<span style="color:#00ff;">(</span><span style="color:#00ff;">)</span><span style="color:#00ff;">;</span>
        <span style="color:#00ff;">}</span>
    <span style="color:#00ff;">}</span>
<span style="color:#00ff;">}</span></span>
调用方法如下：
<meta http-equiv="Content-Type" content="text/html; charset=gb2312">
<%@ page contentType="text/html; charset=gb2312" language="java" 
import="java.sql.*"  %>
<jsp:useBean id="DBConn" scope="page" class="conn.DBConnAccess"/>
<% 
    //连接Access 数据库
    String dbpath="data/test.mdb";            //数据库的路径，请自行修改
    Connection conn=DBConn.getConnToAccess(application.getRealPath(dbpath));
    Statement stmt=conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
         String sql="select * from username order by id";
    //String sql1="insert into username (uid,pwd) values('wsp','wsp')";
    //stmt.executeUpdate(sql1);
    ResultSet rs=stmt.executeQuery(sql);
    
    while(rs.next()){
    out.print("用户名:");
    out.print(rs.getString("uid")+" 密码:");
    out.println(rs.getString("pwd")+"<br>");
    }
    DBConn.close();
%>

posted on 2016-03-18 12:32  木鱼哥  阅读(615)  评论(0)  编辑  收藏  举报

导航