consumeselectbean.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 ConsumeSelectBean {
	String year,month,day=null,s_year= "1700", s_month = "1", s_day= "1", e_year= "2099" ,e_month= "1" ,e_day= "1",type="0",num=null;
	
	public ConsumeSelectBean(){		
	}	
	public void setNum(String num) {
		this.num = num;
	}
	
	public void setType(String type){
		this.type = type;
	}
	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 selectConsume(String userID) {
		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 consumeid,productname,price,datetime from buy.consume where USERID ='"+ userID + "' and consumeid = "+num+""; 
		}else{
			condition = "select consumeid,productname,price,datetime from buy.consume where USERID ='"+ userID + "' and datetime <= '"+ e_date+ "'and datetime >= '" + s_date +"'";
		}	
		ResultSet rs = null;
		String name, coin, date;
		Statement sql = null;
		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=" + "236" + "height=" + "25" + " class="
					+ "cal_td12" + ">消费记录编号</td>");
			buffer.append("<td width=" + "280" + "height=" + "25" + " class="
					+ "cal_td12" + ">商品名称</td>");
			buffer.append("<td width=" + "264" + "height=" + "25" + " class="
					+ "cal_td12" + ">商品价格</td>");
			buffer.append("<td width=" + "264" + "height=" + "25" + " class="
					+ "cal_td12" + ">购买日期</td>");
			buffer.append("</TR>");
			while (rs.next()) {
				buffer.append("<tr align=" + "center" + ">");
				num = rs.getString(1);
				if(i%2==1){
				buffer.append("<TD height=" + "20"
						+ " class=" + "cal_td06" + ">" + num + "</TD>");
				name = rs.getString(2);
				buffer.append("<TD height=" + "20"
						+ " class=" + "cal_td06" + ">" + name + "</TD>");
				coin = rs.getString(3);
				buffer.append("<TD height=" + "20"
						+ " class=" + "cal_td06" + ">" + coin + "</TD>");
				date = rs.getString(4);
				buffer.append("<TD height=" + "20"
						+ " class=" + "cal_td06" + ">" + date + "</TD>");
				}else{
					buffer.append("<TD height=" + "20"
							+ " class=" + "cal_td08" + ">" + num + "</TD>");
					name = rs.getString(2);
					buffer.append("<TD height=" + "20"
							+ " class=" + "cal_td08" + ">" + name + "</TD>");
					coin = rs.getString(3);
					buffer.append("<TD height=" + "20"
							+ " class=" + "cal_td08" + ">" + coin + "</TD>");
					date = rs.getString(4);
					buffer.append("<TD height=" + "20"
							+ " class=" + "cal_td08" + ">" + date + "</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;
	}
}