queryservlet.java

JAVA数据库编程实例源码及其一些Java基本介绍

package querydbinapplet;

import java.io.*;
import java.sql.*;
import java.util.*;
import javax.servlet.*;
import javax.servlet.http.*;

/**
 * Title:			Applet与Servlet通讯查询数据库
 * Description:			教学示范
 * Copyright:			Copyright (c) 2003
 * Company:			北京师范大学计算机系
 * @author			孙一林
 * @version			1.0
 */


public class queryServlet extends HttpServlet {
  static final private String CONTENT_TYPE = "text/html; charset=GBK";

  public void init() throws ServletException {
  }

  public void doGet(HttpServletRequest request,HttpServletResponse response) throws ServletException, IOException {
    response.setContentType(CONTENT_TYPE);
    request.setCharacterEncoding("GBK");
    PrintWriter out = response.getWriter();
    String num = request.getParameter("Num");      //获取用户在Applet中输入的学号
    String rsString = getResultString(num);        //通过学号查询学生记录，查询结果保存在字符串中
    out.println(rsString);                         //将查询结果字符串返回给Applet进行处理显示
  }

  public void doPost(HttpServletRequest request,
                       HttpServletResponse response)
        throws ServletException, IOException {
      doGet(request, response);
    }

  public void destroy() {
  }

  public String getResultString(String number)    //通过学号查询学生信息，并将查询结果以字符串形式返回
  {
    Connection connection = null;                //定义与数据库进行连接的Connection对象
    ResultSet rs = null;                         //定义数据库查询的结果集
    Statement statement = null;                  //定义查询数据库的Statement对象
    String resultString = "";                    //定义存放查询结果的字符串
    try
    {
      Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");   //指定与数据库连接使用JDBC-ODBC桥驱动程序
      String url = "jdbc:odbc:student";                //指定数据源名
      connection = DriverManager.getConnection(url);   //与数据源建立连接
      statement = connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);  //创建Statement接口实例
      String sql = "select studentbase.学号,姓名,年龄,性别,系名,电话,EMAIL地址 from studentbase inner join studentaddress on (studentbase.学号=studentaddress.学号) where studentbase.学号="+Integer.parseInt(number);   //创建以学生学号为约束条件联合查询studentbase表和studentaddress表的SQL语句
      rs = statement.executeQuery(sql);                //将数据存入结果集中
      ResultSetMetaData rsData = rs.getMetaData();     //获取查询结果的信息
      int colCount = rsData.getColumnCount();          //获取查询结果的字段数
      resultString+= String.valueOf(colCount);         //将字段数写入查询结果字符串
      resultString+= "#";                              //与下一条查询信息以字符“#”相间隔
      int recordCount = 0;                             //获取查询结果的记录数
      while(rs.next())
      {
        recordCount++;
      }
      resultString+= String.valueOf(recordCount);      //将记录数写入查询结果字符串
      resultString+= "#";                              //与下一条查询信息以字符“#”相间隔
      for(int i=1; i<=colCount; i++)     //将字段名称写入查询结果字符串，每个字段以字符“#”相间隔
      {
        resultString+= new String(rsData.getColumnName(i).getBytes("GB2312"));
        resultString+= "#";
      }
      rs.beforeFirst();
      while(rs.next())                   //将查询结果写入查询结果字符串，每个字段以字符“#”相间隔
      {
        for(int i=1; i<=colCount; i++)
        {
          resultString+= new String(rs.getString(i).getBytes("GB2312"));
          resultString+= "#";
        }
      }
    }
    catch(SQLException ex){                          //捕捉异常
      System.out.println("\nERROR:----- SQLException -----\n");
      while (ex != null) {
        System.out.println("Message:   " + ex.getMessage());
        System.out.println("SQLState:  " + ex.getSQLState());
        System.out.println("ErrorCode: " + ex.getErrorCode());
        ex = ex.getNextException();
      }
    }
    catch(Exception ex ) {
      ex.printStackTrace();
    }
    finally {
      try {
        if(statement != null)
        {
          statement.close();                      //关闭Statement接口实例
        }
        if(connection != null)
        {
          connection.close();                   //关闭Connection接口实例
        }
      }
      catch (SQLException ex)  {
        System.out.println("\nERROR:----- SQLException -----\n");
        System.out.println("Message:   " + ex.getMessage());
        System.out.println("SQLState:  " + ex.getSQLState());
        System.out.println("ErrorCode: " + ex.getErrorCode());
      }
    }
    return resultString;
  }
}