package org.sunxin.lesson.jsp.bookstore;

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

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 UnavailableException("加載數據庫驅動失敗！");
        }
    }
    
    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
        {
            if(rs!=null)
            {
                try
                {
                    rs.close();
                }
                catch(SQLException se)
                {
                    se.printStackTrace();
                }
                rs=null;
            }
            if(stmt!=null)
            {
                try
                {
                    stmt.close();
                }
                catch(SQLException se)
                {
                    se.printStackTrace();
                }
                stmt=null;
            }
            if(conn!=null)
            {
                try
                {
                    conn.close();
                }
                catch(SQLException se)
                {
                    se.printStackTrace();
                }
                conn=null;
            }
        }
    }
    
    public void doPost(HttpServletRequest req, HttpServletResponse resp)
               throws ServletException,IOException
    {
        doGet(req,resp);
    }
    
    private void printBookInfo(PrintWriter out,ResultSet rs)
                 throws SQLException
    {
        out.println("<html><head>");
        out.println("<title>圖書信息</title>");
        out.println("</head><body>");
        out.println("<table border=1><caption>圖書信息</caption>");
        out.println("<tr><th>書名</th><th>作者</th><th>出版社</th><th>價格</th><th>發行日期</th></tr>");
        while(rs.next())
        {
            out.println("<tr>");
            out.println("<td>"+rs.getString("title")+"</td>");
            out.println("<td>"+rs.getString("author")+"</td>");
            out.println("<td>"+rs.getString("bookconcern")+"</td>");
            out.println("<td>"+rs.getFloat("price")+"</td>");
            out.println("<td>"+rs.getDate("publish_date")+"</td>");
            out.println("</tr>");
        }
        out.println("</table></body></html>");
    }
}