orderselectbean.java

jspjava实现的购物系统

package com.buy.bean.coin;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import com.buy.bean.comm.database.DBConnect;

public class OrderSelectBean {
	String s_year = "1700", s_month = "1", s_day = "1", e_year = "2099",
			e_month = "1", e_day = "1", status = "0", num=null;

	
	public void setNum(String num) {
		this.num = num;
	}

	public void setStatus(String status) {
		this.status = status;
	}

	public void setS_year(String s_year) {
		this.s_year = s_year;
	}

	public void setS_month(String s_month) {
		this.s_month = s_month;
	}

	public void setS_day(String s_day) {
		this.s_day = s_day;
	}

	public void setE_year(String e_year) {
		this.e_year = e_year;
	}

	public void setE_month(String e_month) {
		this.e_month = e_month;
	}

	public void setE_day(String e_day) {
		this.e_day = e_day;
	}

	public StringBuffer selectOrder(String userID) {
		
			String type1 = null;
			 int j = Integer.parseInt(status);
			 switch (j) {
			 case 0:
			 type1 = "Any";
			 break;
			 case 1:
			 type1 = "正在处理";
			 break;
			 case 2:
			 type1 = "支付成功";
			 break;
			 case 3:
			 type1 = "支付失败";
			 break;
			 }
			 
			String s_date = s_year + "-" + s_month + "-" + s_day;
			String e_date = e_year + "-" + e_month + "-" + e_day;
			String condition;
			
			if(num!=null){
				condition = "select orderid,type,price,datetime,productname from buy.order where USERID ='"+ userID + "' and orderid = "+num+""; 
			}else{
				condition = "select orderid,type,price,datetime,productname from buy.order where USERID ='"+ userID + "' and datetime <= '"+ e_date+ "'and datetime >= '" + s_date +"'";
				
				if(j != 0){
					condition +=  "and type ='"+type1+"'";  
				}
			}						
			System.out.println(condition);
			ResultSet rs = null;
			Statement sql = null;
			String status, coin, date, num,name;
			StringBuffer buffer = new StringBuffer();
			try {
				Connection conn = DBConnect.getConnection();
				System.out.println(conn.isClosed());
				sql = conn.createStatement();
				rs = sql.executeQuery(condition);
				int i = 1;
				buffer.append("<table width=" + "100%" + " border=" + "0"
						+ " cellpadding=" + "0" + " cellspacing=" + "1" + ">");
				buffer.append("<tr align=" + "center" + ">");
				buffer.append("<td width=" + "150" + "height=" + "25"
						+ " class=" + "cal_td12" + ">订单号</td>");
				buffer.append("<td width=" + "180" + "height=" + "25"
						+ " class=" + "cal_td12" + ">订单状态</td>");
				buffer.append("<td width=" + "150" + "height=" + "25"
						+ " class=" + "cal_td12" + ">金额</td>");
				buffer.append("<td width=" + "150" + "height=" + "25"
						+ " class=" + "cal_td12" + ">订单创建时间</td>");
				buffer.append("<td width=" + "150" + "height=" + "25"
						+ " class=" + "cal_td12" + ">订购商品名称</td>");
				buffer.append("</TR>");
				while (rs.next()) {
					buffer.append("<tr align=" + "center" + ">");
					if (i % 2 == 1) {
						//取得结果集中一条记录的第一个字段，付给num
						num = rs.getString(1);
						buffer.append("<TD width=" + 180 + " height=" + "20"
										+ " class=" + "cal_td06" + ">" + num
										+ "</TD>");
//						取得结果集中一条记录的第一个字段，付给num
						status = rs.getString(2);
						buffer.append("<TD width=" + 150 + " height=" + "20"
								+ " class=" + "cal_td06" + ">" + status
								+ "</TD>");
//						取得结果集中一条记录的第一个字段，付给num
						coin = rs.getString(3);
						buffer.append("<TD width=" + 150 + " height=" + "20"
								+ " class=" + "cal_td06" + ">" + coin
								+ "</TD>");
//						取得结果集中一条记录的第一个字段，付给num
						date = rs.getString(4);
						buffer.append("<TD width=" + 150 + " height=" + "20"
								+ " class=" + "cal_td06" + ">" + date
								+ "</TD>");
//						取得结果集中一条记录的第一个字段，付给num
						name = rs.getString(5);
						buffer.append("<TD width=" + 150 + " height=" + "20"
								+ " class=" + "cal_td06" + ">" + name
								+ "</TD>");
					} else {
//						取得结果集中一条记录的第一个字段，付给num
						num = rs.getString(1);
						buffer.append("<TD width=" + 180 + " height=" + "20"
										+ " class=" + "cal_td08" + ">" + num
										+ "</TD>");
//						取得结果集中一条记录的第一个字段，付给num
						status = rs.getString(2);
						buffer.append("<TD width=" + 150 + " height=" + "20"
								+ " class=" + "cal_td08" + ">" + status
								+ "</TD>");
//						取得结果集中一条记录的第一个字段，付给num
						coin = rs.getString(3);
						buffer.append("<TD width=" + 150 + " height=" + "20"
								+ " class=" + "cal_td08" + ">" + coin
								+ "</TD>");
//						取得结果集中一条记录的第一个字段，付给num
						date = rs.getString(4);
						buffer.append("<TD width=" + 150 + " height=" + "20"
								+ " class=" + "cal_td08" + ">" + date
								+ "</TD>");
//						取得结果集中一条记录的第一个字段，付给num
						name = rs.getString(5);
						buffer.append("<TD width=" + 150 + " height=" + "20"
								+ " class=" + "cal_td08" + ">" + name
								+ "</TD>");
					}
					buffer.append("</TR>");
					i++;
				}
				buffer.append("</Table>");
				rs.close();
				sql.close();
				conn.close();
				// }
			} catch (SQLException ex) {
				System.out.println("TradeDB SQLException: " + ex.getMessage());
				return new StringBuffer("查询错误");
			}
			return buffer;
		
	}
}