三种不同查询方式的servlet

package servlet；

ervlet;

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import javax.servlet.ServletContext;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

public class ListServlet extends HttpServlet
{
    private String url;
    private String user;
    private String password;

    public void init() throws ServletException
    {
        ServletContext sc=getServletContext();
        String driverClass=sc.getInitParameter("driverClass");
        url=sc.getInitParameter("url");
        user=sc.getInitParameter("user");
        password=sc.getInitParameter("password");
        try
        {
            Class.forName(driverClass);

        }
        catch(ClassNotFoundException ce)
        {
            throw new ServletException("加载数据库驱动失败！");
        }
    }

    public void doGet(HttpServletRequest req, HttpServletResponse resp)
               throws ServletException,IOException
    {
        Connection conn=null;
        Statement stmt=null;
        ResultSet rs=null;
        req.setCharacterEncoding("gb2312");
        String condition=req.getParameter("cond");
        if(null==condition || condition.equals(""))
        {
            resp.sendRedirect("search.html");
            return;
        }
        resp.setContentType("text/html;charset=gb2312");
        PrintWriter out=resp.getWriter();
        try
        {
            conn=DriverManager.getConnection(url,user,password);
            stmt=conn.createStatement();
            if(condition.equals("all"))
            {
                rs=stmt.executeQuery("select * from bookinfo");
                printBookInfo(out,rs);
                out.close();
            }
            else if(condition.equals("precision"))
            {
                String title=req.getParameter("title");
                String author=req.getParameter("author");
                String bookconcern=req.getParameter("bookconcern");

                if((null==title || title.equals("")) &&
                   (null==author || author.equals("")) &&
                   (null==bookconcern || bookconcern.equals("")))
                {
                    resp.sendRedirect("search.html");
                    return;
                }

                StringBuffer sb=new StringBuffer("select * from bookinfo where ");
                boolean bFlag=false;

                if(!title.equals(""))
                {
                    sb.append("title = "+"'"+title+"'");
                    bFlag=true;
                }
                if(!author.equals(""))
                {
                    if(bFlag)
                        sb.append("and author = "+"'"+author+"'");
                    else
                    {
                       sb.append("author = "+"'"+author+"'");
                       bFlag=true;
                    }
                }
                if(!bookconcern.equals(""))
                {
                    if(bFlag)
                        sb.append("and bookconcern = "+"'"+bookconcern+"'");
                    else
                        sb.append("bookconcern = "+"'"+bookconcern+"'");
                }
                rs=stmt.executeQuery(sb.toString());
                printBookInfo(out,rs);
                out.close();
            }
            else if(condition.equals("keyword"))
            {
                String keyword=req.getParameter("keyword");
                if(null==keyword || keyword.equals(""))
                {
                    resp.sendRedirect("search.html");
                    return;
                }
                String strSQL="select * from bookinfo where title like '%"+keyword+"%'";

                rs=stmt.executeQuery(strSQL);
                printBookInfo(out,rs);
                out.close();
            }
            else
            {
                resp.sendRedirect("search.html");
                return;
            }
        }
        catch(SQLException se)
        {
            se.printStackTrace();
        }
        finally
        {
            closeResultSet(rs);
            closeStatement(stmt);
            closeConnection(conn);
        }
    }

    public void doPost(HttpServletRequest req, HttpServletResponse resp)
               throws ServletException,IOException
    {
        doGet(req,resp);
    }

    private void printBookInfo(PrintWriter out,ResultSet rs)
                 throws SQLException
    {
        out.println("");
        out.println("图书信息");
        out.println("");
        out.println("");        out.println("");        while(rs.next())        {            out.println("");            out.println("");            out.println("");            out.println("");            out.println("");            out.println("");            out.println("");        }        out.println("图书信息
书名作者出版社价格发行日期



"+rs.getString("title")+"
"+rs.getString("author")+"
"+rs.getString("bookconcern")+"
"+rs.getFloat("price")+"
"+rs.getDate("publish_date")+"



");
        out.println("
        while(rs.next())
        {
            out.println("
            out.println("
            out.println("
            out.println("
            out.println("
            out.println("
            out.println("
        }
        out.println("
    }

    private void closeResultSet(ResultSet rs)
    {
        if(rs!=null)
        {
            try
            {
                rs.close();
            }
            catch(SQLException se)
            {
                se.printStackTrace();
            }
            rs=null;
        }
    }

    private void closeStatement(Statement stmt)
    {
        if(stmt!=null)
        {
            try
            {
                stmt.close();
            }
            catch(SQLException se)
            {
                se.printStackTrace();
            }
            stmt=null;
        }
    }

    private void closeConnection(Connection conn)
    {
        if(conn!=null)
        {
            try
            {
                conn.close();
            }
            catch(SQLException se)
            {
                se.printStackTrace();
            }
            conn=null;
        }
    }
}

1  函数printBookInfo() 负责以网页表格的相识输出结果集中的数据

 

2  客户端提交的请求参数可能包含中文字符，所以调用请求对象的setCharacter Encoding()方法指定请求正文使用的字符编码为gb2312

 

3 getParameter()方法得到查询条件

 

4 判断条件为空的话，调用响应对象的sendRedirect()方法将客户端重定向到search.html页面，然后调用return语句 结束doGet()方法 这是为了避免后面的语句被执行。

 

5 构造一个StringBuffer对象，用来准备一个SQL语句。定义一个boolean类型的变量，用来判断前面的参数是否作为where子句中的一部分，是否添加"and"

 

6 关键字搜索，模糊查询 通过在查询语句中使用关键字 like