orderservice.java

使用jsp+Servlet,采用MVC模式,实现了一典型小型电子商务网站的全过程.包括前台和后台的全部功能.适合于初学者学习使用.

			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;		
	}
	
	/**
	 * 根据ID获得付款信息
	 * @return
	 */
	public List getPayInfoById(long orderid){		
		ArrayList list=new ArrayList();
		DBManager dbManager= new DBManager();
		Connection conn=dbManager.GetConnection();
		Statement st;
		try {
			st = conn.createStatement();
			ResultSet rs=st.executeQuery("select * from payinfo p inner join creditcardinfo c on p.creditcardinfo=c.id where orderid="+orderid);
			ViewOrderForm viewOrderForm=null;
			while(rs.next()){
				viewOrderForm=new ViewOrderForm();
				//付款信息
				viewOrderForm.setCardtype(rs.getString("cardtype"));
				viewOrderForm.setCreditcard(rs.getString("creditcard"));
				viewOrderForm.setExprdate(rs.getString("exprdate"));
				//帐单送达地址信息
				viewOrderForm.setBilltoname(rs.getString("billtoname"));
				viewOrderForm.setBilladdr1(rs.getString("billaddr1"));
				viewOrderForm.setBilladdr2(rs.getString("billaddr2"));
				viewOrderForm.setBillcity(rs.getString("billcity"));
				viewOrderForm.setBillstate(rs.getString("billstate"));
				viewOrderForm.setBillzip(rs.getString("billzip"));
				viewOrderForm.setBillcountry(rs.getString("billcountry"));
				list.add(viewOrderForm);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;		
	}
	
	
	/**
	 * 获得订购商品信息
	 * @return
	 */
	public List getOrderItemList(){
		DBManager dbManager= new DBManager();
		Connection conn=dbManager.GetConnection();
		ArrayList list=new ArrayList();
		Statement st;
		try {
			st = conn.createStatement();
			long orderid=this.getOrderId();
			ResultSet rs=st.executeQuery("select distinct line.itemid,line.quantity,line.unitprice,os.status,i.productid,i.attr1,i.attr2,i.attr3,i.attr5 from lineitem line inner join item i on line.itemid=i.itemid inner join product p on p.productid=i.productid inner join orderStatus os on line.orderid=os.orderid and line.linenum=os.linenum where line.orderid="+orderid);
			ViewOrderLineItemForm form=null;
			
			while (rs.next()){
				form=new ViewOrderLineItemForm();
				form.setItemid(rs.getString("itemid"));
				form.setProductid(rs.getString("productid"));
				form.setAttr1(rs.getString("attr1"));
				form.setAttr2(rs.getString("attr2"));
				form.setAttr3(rs.getString("attr3"));
				form.setAttr5(rs.getString("attr5"));
				form.setQuantity(rs.getLong("quantity"));
				form.setStatus(rs.getString("status"));
				form.setUnitprice(rs.getFloat("unitprice"));
				list.add(form);
			}
			st.close();
			rs.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return list;
		
	}
	
	/**
	 * 根据定单ID获得订购商品信息
	 * @return
	 */
	public List getOrderItemListById(long orderid){
		DBManager dbManager= new DBManager();
		Connection conn=dbManager.GetConnection();
		ArrayList list=new ArrayList();
		Statement st;
		try {
			st = conn.createStatement();
			ResultSet rs=st.executeQuery("select distinct line.itemid,line.quantity,line.unitprice,os.status,i.productid,i.attr1,i.attr2,i.attr3,i.attr5 from lineitem line inner join item i on line.itemid=i.itemid inner join product p on p.productid=i.productid inner join orderStatus os on line.orderid=os.orderid and line.linenum=os.linenum where line.orderid="+orderid);
			ViewOrderLineItemForm form=null;
			
			while (rs.next()){
				form=new ViewOrderLineItemForm();
				form.setItemid(rs.getString("itemid"));
				form.setProductid(rs.getString("productid"));
				form.setAttr1(rs.getString("attr1"));
				form.setAttr2(rs.getString("attr2"));
				form.setAttr3(rs.getString("attr3"));
				form.setAttr5(rs.getString("attr5"));
				form.setQuantity(rs.getLong("quantity"));
				form.setStatus(rs.getString("status"));
				form.setUnitprice(rs.getFloat("unitprice"));
				list.add(form);
			}
			st.close();
			rs.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return list;
		
	}
	/**
	 * 根据ORDERID获得每一份定单的总价格
	 * @param orderid
	 */
	public float getOrderItemTotalPrice(){
		DBManager dbManager= new DBManager();
		Connection conn=dbManager.GetConnection();
		long orderid=this.getOrderId();
		float totalprice=0;
		Statement st;
		try {
			st = conn.createStatement();
			ResultSet rs=st.executeQuery("select totalprice from orders where orderid="+orderid);
			 
			while(rs.next()){
			 totalprice=rs.getFloat("totalprice");
			}
			st.close();
			rs.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return totalprice;		
	}
	/**
	 * 获得某一用户的所有订单
	 * @return
	 */
	public List getAllOrdersList(String userid){
		DBManager dbManager= new DBManager();
		Connection conn=dbManager.GetConnection();
		Statement st;
		ArrayList list=new ArrayList();
		try {
			st = conn.createStatement();
			ResultSet rs=st.executeQuery("select orderid,userid,orderdate,totalprice from orders where userid='"+userid+"'");
			NewOrderForm form=null;
			
			while(rs.next()){
				form=new NewOrderForm();
				form.setOrderid(rs.getLong("orderid"));
				form.setUserid(rs.getString("userid"));
				form.setDatetime(rs.getString("orderdate").substring(0, 10));
				form.setTotalprice(rs.getFloat("totalprice"));
				list.add(form);
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}
	/**
	 * 更新订单状态
	 * @author jonson
	 *
	 */
	public boolean updateOrderStatus(String userid){
		boolean flag=false;
		DBManager dbManager= new DBManager();
		Connection conn=dbManager.GetConnection();
		ArrayList list=new ArrayList();
		try {
			Statement st=conn.createStatement();
			ResultSet rs=st.executeQuery("select orderid from orders where userid='"+userid+"'");
			Orders orders=null;
			while(rs.next()){
				orders=new Orders();
				orders.setOrderid(rs.getLong("orderid"));
				list.add(orders);
			}
			orders=null;
			for(int i=0;i<list.size();i++){
				orders=(Orders)list.get(i);
				long orderid=orders.getOrderid();
				st.executeUpdate("update orderstatus set status=1 where orderid="+orderid);
			}
			st.close();
            conn.close();
            flag=true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return flag;
}
	/**
	 * 根据orderid删除订单
	 * @author jonson
	 *
	 */
	public boolean deleteOrder(long orderid){
		boolean flag=false;
		DBManager dbManager= new DBManager();
		Connection conn=dbManager.GetConnection();
		try {
			Statement st=conn.createStatement();
			st.executeUpdate("delete from payinfo where orderid="+orderid);
			st.executeUpdate("delete from lineitem where orderid="+orderid);
			st.executeUpdate("delete from orderstatus where orderid="+orderid);
			st.executeUpdate("delete from orders where orderid="+orderid);
			st.close();
            conn.close();
            flag=true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return flag;
		
	}
	public List getAllShopInfo(){
		List list=new ArrayList();
		DBManager dbManager= new DBManager();
		Connection conn=dbManager.GetConnection();
		try {
			PreparedStatement ps=conn.prepareStatement("select userid,count(*) as number from orders group by userid order by number desc");
			ResultSet rs=ps.executeQuery();
			while (rs.next()){
				Orders order=new Orders();
				order.setUserid(rs.getString("userid"));
				order.setNumber(rs.getInt("number"));				
				list.add(order);
			}
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}

	public Orders getShopById(String userid){
		Orders form=new Orders();		
		DBManager db=new DBManager();
		Connection conn=db.GetConnection();
		PreparedStatement ps;
		try {
			ps = conn.prepareStatement("select orderid,userid,orderdate from orders where userid='"+userid+"'");
			ps.setString(1, userid);
			ResultSet q=ps.executeQuery();
			while(q.next()){
				form.setUserid(q.getString("userid"));
				form.setOrderid(q.getLong("orderid"));
				form.setDatetime(q.getString("datetime"));
			}
			ps.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
				
		return form;
	}
	
	}