在JSP和Servlet中使用JDBC

1 在JSP中使用JDBC访问数据库
本节将介绍在JSP页面中通过JDBC访问数据库，并且介绍在数据库中执行不同操作的方法。在数据库编程中，主要涉及到的操作有：

查询数据；
更新数据；
删除数据；
创建数据表、更改表、获得表本身的信息；

在介绍编程之前，请使用下列脚本在数据库中创建一个表。

use jspdev;
drop table contact;
create table contact(userName varchar(20),mobile int,phone varchar(20),mail varchar(50),lastcontact datetime,mem varchar(100),constraint pk_contact primary key(userName));

1.1 添加数据
insert.jsp

<%@ page language="java" import="java.util.*,java.sql.*,java.io.*" pageEncoding="GB2312"%>
<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>
<%@page contentType="text/html; charset=gb2312"%>
<%@ page import="java.sql.DriverManager" %>
<%@ page import="java.sql.PreparedStatement" %>
<%@ page import="java.sql.Date" %>
<%request.setCharacterEncoding("GB2312");%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
    <base href="<%=basePath%>">

    <title>My JSP 'insert.jsp' starting page</title>

    <meta http-equiv="pragma" content="no-cache">
    <meta http-equiv="cache-control" content="no-cache">
    <meta http-equiv="expires" content="0">
    <meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
    <meta http-equiv="description" content="This is my page">

    
</head>

<body>
<%
    try
    {
       Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver").newInstance();

       Connection con =
           DriverManager.getConnection("jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=jspdev","sa","starxing");
       //使用preparedStatement
       PreparedStatement pstmt = con.prepareStatement("insert into contact values(?,?,?,?,?,?)");

       pstmt.setString(1,"张彦星");
       pstmt.setInt(2,136234243);
       pstmt.setString(3,"010455554");
       pstmt.setString(4,"test@test.com");
       pstmt.setDate(5,new Date(new java.util.Date().getTime()));
       pstmt.setString(6,"啊水电费搭撒");
       pstmt.execute();

       pstmt.setString(1,"aaas");
       pstmt.setInt(2,2343);
       pstmt.setString(3,"123");
       pstmt.setString(4,"tessst@test.com");
       pstmt.setDate(5,new Date(new java.util.Date().getTime()));
       pstmt.setString(6,"嗄撒旦发");
       pstmt.execute();

       pstmt.setString(1,"ssf");
       pstmt.setInt(2,123);
       pstmt.setString(3,"asfwef");
       pstmt.setString(4,"test@test.com");
       pstmt.setDate(5,new Date(new java.util.Date().getTime()));
       pstmt.setString(6,"文风");
       pstmt.execute();

       pstmt.setString(1,"文风嗄撒旦发");
       pstmt.setInt(2,24124);
       pstmt.setString(3,"文人贴");
       pstmt.setString(4,"test@test.com");
       pstmt.setDate(5,new Date(new java.util.Date().getTime()));
       pstmt.setString(6,"奥斯丁");
       pstmt.execute();

       pstmt.setString(1,"案犯问改");
       pstmt.setInt(2,451145);
       pstmt.setString(3,"文人贴统一");
       pstmt.setString(4,"test@test.com");
       pstmt.setDate(5,new Date(new java.util.Date().getTime()));
       pstmt.setString(6,"文风撒肥");
       pstmt.execute();
    }
    catch(Exception e)
    {
       e.printStackTrace();
    }
%>
</body>
</html>

1.2 查询数据库
    查询数据库的操作和执行更新的操作基本相似。不同的是使用的SQL语句不同，并且操作完成后往往需要对查询的结果集进行处理。
query.jsp

<%@ page language="java" import="java.util.*,java.sql.*,java.io.*" pageEncoding="GB2312"%>
<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>
<%! String trans(String chi)
{
   String result = null;
   byte temp[];
   try
   {
     temp = chi.getBytes("iso-8859-1");
     result = new String(temp);
   }catch(Exception e)
   {
     e.printStackTrace();
   }
   return result;
}
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
    <base href="<%=basePath%>">

    <title>My JSP 'query.jsp' starting page</title>

    <meta http-equiv="pragma" content="no-cache">
    <meta http-equiv="cache-control" content="no-cache">
    <meta http-equiv="expires" content="0">
    <meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
    <meta http-equiv="description" content="This is my page">


    <link rel="stylesheet" type="text/css" href="starxing.css">

</head>

<body>
     以下是从Mssql数据库读取的数据：
    <TABLE border=1 cellspacing="0" cellpadding="0">
    <tr>
      <th>姓名</th>
      <th>手机</th>
      <th>电话</th>
      <th>Email</th>
      <th>最后的联系时间</th>
      <th>备注</th>
    </tr>

    <%
     try
     {
       Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver").newInstance();
       Connection con =
           DriverManager.getConnection("jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=jspdev","sa","starxing");
       Statement stmt = con.createStatement();
       ResultSet rst = stmt.executeQuery("select * from contact;");
       while(rst.next())
       {
         out.println("<tr>");
         out.println("<td>"+rst.getString("userName")+"</td>");
         out.println("<td>"+rst.getInt("mobile")+"</td>");
         out.println("<td>"+rst.getString("phone")+"</td>");
         out.println("<td>"+rst.getString("mail")+"</td>");
         out.println("<td>"+rst.getString("lastcontact")+"</td>");
         out.println("<td>"+rst.getString("mem")+"</td>");
         out.println("</tr>");
       }
       rst.close();
       stmt.close();
       con.close();
      }catch(Exception e)
      {
        e.printStackTrace();
      }

    %>
    </TABLE>
</body>
</html>
1.3 更新数据
   更新的操作和前面的操作基本一样，
update.jsp

<%@ page language="java" import="java.util.*,java.sql.*,java.io.*" pageEncoding="GB2312"%>
<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
    <base href="<%=basePath%>">

    <title>My JSP 'update.jsp' starting page</title>

    <meta http-equiv="pragma" content="no-cache">
    <meta http-equiv="cache-control" content="no-cache">
    <meta http-equiv="expires" content="0">
    <meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
    <meta http-equiv="description" content="This is my page">

    
</head>

<body>
    <%
       try
     {
       Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver").newInstance();
       Connection con =
           DriverManager.getConnection("jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=jspdev","sa","starxing");
       Statement stmt = con.createStatement();
       int col = stmt.executeUpdate("update contact set mem ='他在北京' where userName='张彦星'");
       out.println("成功更新"+col+"条数据");
       stmt.close();
       con.close();
     }catch(Exception e)
      {
        e.printStackTrace();
      }
    %>
</body>
</html>
1.4 删除数据
delete.jsp

<%@ page language="java" import="java.util.*,java.sql.*,java.io.*" pageEncoding="GB2312"%>
<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
    <base href="<%=basePath%>">

    <title>My JSP 'delete.jsp' starting page</title>

    <meta http-equiv="pragma" content="no-cache">
    <meta http-equiv="cache-control" content="no-cache">
    <meta http-equiv="expires" content="0">
    <meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
    <meta http-equiv="description" content="This is my page">

    
</head>

<body>
    <%
       try
     {
       Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver").newInstance();
       Connection con =
           DriverManager.getConnection("jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=jspdev","sa","starxing");
       Statement stmt = con.createStatement();
       boolean col= stmt.execute("delete from contact where userName ='aaas'");
       out.println("成功更新"+col+"条数据");
       stmt.close();
       con.close();
     }catch(Exception e)
      {
        e.printStackTrace();
      }
    %>
</body>
</html>
1.5 高级操作

   除了能查询、删除、更新数据表中的信息外，我们还可以获得数据表本身的信息。
   下面我们开发一个JSP，只要提供数据库名和表的名称，就可以把数据表的所有信息（包括字段名，字段的类型和所有记录）在浏览器里显示。
proxy.jsp

<%@ page language="java" import="java.util.*,java.sql.*,java.sql.*" pageEncoding="GB2312"%>
<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>
<%@page contentType="text/html; charset=gb2312"%>
<%request.setCharacterEncoding("GB2312");%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
    <base href="<%=basePath%>">

    <title>My JSP 'proxy.jsp' starting page</title>

    <meta http-equiv="pragma" content="no-cache">
    <meta http-equiv="cache-control" content="no-cache">
    <meta http-equiv="expires" content="0">
    <meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
    <meta http-equiv="description" content="This is my page">


    <link rel="stylesheet" type="text/css" href="starxing.css">

</head>
<%!
String typeToString(int i)
{
     String ret = "";
     switch(i)
     {
        case(1):ret="CHAR";break;
        case(2):ret="NUMERIC";break;
        case(3):ret="DECIMAL";break;
        case(4):ret="INTEGER";break;
        case(5):ret="SMALLINT";break;
        case(8):ret="FLOAT";break;
        case(12):ret="DOUBLE";break;
        case(91):ret="VARCHAR";break;
        default:ret="other";
     }
     return ret;
}
%>
<%!
String getCol(ResultSet rst,int type,int colNum)throws Exception
{
     String ret="";
     switch(type)
     {
       case(1):ret=rst.getString(colNum);break;
       case(4):ret=String.valueOf(rst.getInt(colNum));break;
       case(5):ret=String.valueOf(rst.getInt(colNum));break;
       case(6):ret=String.valueOf(rst.getFloat(colNum));break;
       case(8):ret=String.valueOf(rst.getDouble(colNum));break;
       case(12):ret=rst.getString(colNum);break;
       default:ret="not know";
     }
     return ret;
}
%>
<%!
String trans(String chi)
{
     String result=null;
     byte temp[];
     try
     {
        temp=chi.getBytes("iso-8859-1");
        result = new String(temp);
     }
     catch(Exception e)
     {
       e.printStackTrace();
     }
     return result;
}
%>
<body>
    数据库动态代理v0.5:<hr>
    <FORM action="ch12/proxy.jsp" method=get>
    <TABLE>
     <TR><TH>数据库的名称：<input type=text name=database></TH>
     <TH>表的名称：<input type=text name=table></TH>
     </TR>
     <TR><td colspan=2><input type=submit value="提交"></td></TR>
    </TABLE>
    </FORM>
    <%
      String database=(String)request.getParameter("database");
      String table = (String)request.getParameter("table");
   //   out.println(database+table);
    %>
    <TABLE border=1>
    <%
      try
      {
         Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver").newInstance();
       Connection con =
           DriverManager.getConnection("jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=jspdev","sa","starxing");

       Statement stmt = con.createStatement();
       ResultSet rst = stmt.executeQuery("use "+database+";select * from "+table);
       ResultSetMetaData meta = rst.getMetaData();
       int col = meta.getColumnCount();
       out.println("表"+table+"共有："+col+"个字段，这些字段：<br>");
       out.println("<tr>");
       for(int i=1;i<col+1;i++)
       {
         out.println("<TH>字段名：");
         out.println(meta.getColumnName(i));
         out.println("<p> 类型：");
         out.println(typeToString(meta.getColumnType(i)));
         out.println("</TH>");
       }
       out.println("</TR>");
       while(rst.next())
       {
         out.println("<tr>");
         for(int i=1;i<col+1;i++)
         {
           out.println("<TD>"+getCol(rst,meta.getColumnType(i),i)+"</td>");
         }
         out.println("</tr>");
       }
       rst.close();
       stmt.close();
       con.close();
      }catch(Exception e)
      {
       e.printStackTrace();
      }
    %>
    </TABLE>
</body>
</html>

1.6 其他操作
   除了在数据库中对表进行操作外，在JDBC中还可以执行许多其他的操作，比如创建、删除数据库，增加、删除一个数据库用户，创建、删除表、更新表的结构等。
misc_operation.jsp

<%@ page language="java" import="java.util.*,java.sql.*,java.io.*" pageEncoding="GB2312"%>
<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
    <base href="<%=basePath%>">

    <title>My JSP 'misc_operation.jsp' starting page</title>

    <meta http-equiv="pragma" content="no-cache">
    <meta http-equiv="cache-control" content="no-cache">
    <meta http-equiv="expires" content="0">
    <meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
    <meta http-equiv="description" content="This is my page">

    
</head>

<body>
    <%
       try
     {
       Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver").newInstance();
       Connection con =
           DriverManager.getConnection("jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=jspdev","sa","starxing");
       Statement stmt = con.createStatement();
       stmt.execute("create table test(t int,t2 varchar(20))");
       stmt.execute("alter table add t3 varchar(20) null");
       stmt.execute("insert into test values(12,'starxing','asiapower')");
       stmt.execute("insert into test values(11,'star1xing','aaa1');");
       out.print("执行完成！");
       stmt.close();
       con.close();
     }catch(Exception e)
      {
        e.printStackTrace();
      }
    %>
</body>
</html>

posted on 2007-08-08 10:14 mordecai 阅读(137) 评论(0) 编辑收藏引用所属分类: servlet

常用链接

留言簿

文章分类

搜索

最新评论