sortservlet.java

基于JSP的家电连锁超市管理系统 数据库也在文件内

package servlet;

import java.io.*;
import javax.servlet.*;
import javax.servlet.http.*;
import java.sql.*;
import java.util.*;
import electric.dbs.*;
import electric.electricUtils.*;

public class SortServlet extends HttpServlet
{
	public void doPost(HttpServletRequest request,HttpServletResponse response)
	  throws ServletException,java.io.IOException
	{
         String Ayear = ParamUtils.getEscapeHTMLParameter(request, "Ayear","2007");
         System.out.println("== Ayear == "+Ayear);
		 String Amonth = ParamUtils.getEscapeHTMLParameter(request, "Amonth","01");
		 System.out.println("== Amonth == "+Amonth);
		 String Aday = ParamUtils.getEscapeHTMLParameter(request, "Aday","01");
		 System.out.println("== Aday == "+Aday);
		 String Byear = ParamUtils.getEscapeHTMLParameter(request, "Byear","2007");
		 System.out.println("== Byear == "+Byear);
		 String Bmonth = ParamUtils.getEscapeHTMLParameter(request, "Bmonth","01");
		 System.out.println("== Bmonth == "+Bmonth);
		 String Bday = ParamUtils.getEscapeHTMLParameter(request, "Bday","01");
		 System.out.println("== Bday == "+Bday);
		 String Atime=Ayear+Amonth+Aday;
		 String Btime=Byear+Bmonth+Bday;
		 int Proidtemp = ParamUtils.getIntParameter(request, "Proid",-1);
		 System.out.println("== Proid == "+Proidtemp);
		 String sql="";
		 if(Proidtemp==-1)
		 {
		         sql=" SELECT SUM(PRONUMBER) AS SPRONUMBER,Shopid FROM [SELL] WHERE Ptime BETWEEN '"+Atime
		         +"' AND '" +Btime+"'GROUP BY Shopid";
		         System.out.println("@@@ "+sql);
		         //sql=" SELECT SUM(PRONUMBER) AS SPRONUMBER,Shopid FROM [SELL] WHERE Ptime BETWEEN '"+Atime+"' AND '" +Btime+"'GROUP BY Shopid ORDER BY SPRONUMBER DESC";
		 }else
		 {
		         sql=" SELECT SUM(PRONUMBER)AS SPRONUMBER,[SHOPID] FROM SELL WHERE PTIME BETWEEN '"+Atime
		         +"' AND '" +Btime+"' AND PRODUCTID=" + Proidtemp + " GROUP BY SHOPID";
		         //排序出现问题 错误提示：参数不足期待是1
		         //sql=" SELECT SUM(PRONUMBER)AS SPRONUMBER,[SHOPID] FROM SELL WHERE PTIME BETWEEN '"+Atime+"' AND '" +Btime+"' AND PRODUCTID=" + Proid + " GROUP BY SHOPID ORDER BY SPRONUMBER DESC";
		 }
		 
		try
		{
	  		Connection con=null;
			PreparedStatement ps=null;
			ResultSet rs=null;
			Vector v=new Vector();			
			con=DbConnectionManager.getConnection();
			ps=con.prepareStatement(sql);
			rs=ps.executeQuery();
			int count=1;
			while(rs.next())
			{
				String[] str=new String[3];
				str[0]=count+"";
				str[1]=rs.getInt(1)+"";
			    Dbshop dbshop=new Dbshop(rs.getInt(2));			    				
				str[2]=dbshop.getShopname();
				System.out.print("## NO> ## "+str[0]+"   ");
				System.out.print("## count ## "+str[1]+"   ");
				System.out.print("## shopname ## "+str[2]);
				System.out.println(" ");
				v.add(str);
				count++;								
			}
			String Proid=Proidtemp+"";
			request.setAttribute("SortV",v);
			request.setAttribute("Atime",Atime);
			request.setAttribute("Btime",Btime);
			request.setAttribute("Proid",Proid);
			this.getServletConfig().getServletContext().getRequestDispatcher("/sortsell.jsp").forward(request,response);
		}
		catch(SQLException e)
		{
			e.printStackTrace();
		}
		catch(ShopNotFoundException ee)
		{
			ee.printStackTrace();
		}
  		
	}
	
	public void doGet(HttpServletRequest request,HttpServletResponse response)
	  throws ServletException,java.io.IOException
	{
	  	this.doPost(request,response);
	}
	
	public static void main(String args[])
	{
       		try
		{
	  		String sql=" SELECT SUM(PRONUMBER) AS SPRONUMBER,Shopid FROM [SELL] WHERE Ptime BETWEEN '"[phone]
		         +"' AND '"[phone]+"'GROUP BY Shopid";
	  		Connection con=null;
			PreparedStatement ps=null;
			ResultSet rs=null;
			Vector v=new Vector();			
			con=DbConnectionManager.getConnection();
			ps=con.prepareStatement(sql);
			rs=ps.executeQuery();
			int count=1;
			System.out.println("vv");
			while(rs.next())
			{
				System.out.println("tt");
				String[] str=new String[3];
				str[0]=count+"";
				str[1]=rs.getInt(1)+"";
			    Dbshop dbshop=new Dbshop(rs.getInt(2));			    				
				str[2]=dbshop.getShopname();
				System.out.print("## NO> ## "+str[0]+"   ");
				System.out.print("## count ## "+str[1]+"   ");
				System.out.print("## shopname ## "+str[2]);
				System.out.println(" ");
				v.add(str);
				count++;								
			}
		}
		catch(SQLException e)
		{
			e.printStackTrace();
		}
		catch(ShopNotFoundException ee)
		{
			ee.printStackTrace();
		}
	}
}