waremn.java

jsp mysql 实现网上购物系统

package com.mybusiness.web;


import java.sql.*;
import java.util.Vector;
import com.mybusiness.util.*;
import javax.servlet.http.HttpServletRequest;
import com.mybusiness.web.ware.ware;

/**
 *         商品类别列表
 */

public class waremn extends DataBase {
	private ware awares = new ware();	//新的商品类
	private javax.servlet.http.HttpServletRequest request; //建立页面请求
	private boolean sqlflag = true ;		//对接收到的数据是否正确
	private Vector warelist;				//显示商品列表向量数组
	private int page = 1;					//显示的页码
	private int pageSize=10;				//每页显示的商品数
	private int pageCount =0;				//页面总数
	private long recordCount =0;			//查询的记录总数

	//建立数据库连接
	public waremn() throws Exception{
		super();                          
	}
   //显示商品列表向量数组
	public Vector getwarelist() {
		return warelist;
	}
   
	public boolean getSqlflag() {
		return sqlflag;
	}
   //转换为汉字
	public String getGbk( String str) {
		try
		{
			str =  new String(str.getBytes("ISO-8859-1"),"GBK");
			return str;
		}
		catch (Exception e)
		{
			return str;
		}
	}
	
	//将页面表单传来的资料分解
	public boolean getRequest(javax.servlet.http.HttpServletRequest newrequest) {
		boolean flag = false;
		try
		{	
			request = newrequest;
			String ID = request.getParameter("id");
			long wareid = 0;
			try
			{
				wareid = Long.parseLong(ID);           //id转换为long类型
			}
			catch (Exception e)
			{
			}
			awares.setId(wareid);
			String warename = request.getParameter("warename");  //取得商品名称，下面依次取出各个信息
			if (warename==null || warename.equals(""))
			{
				warename = "";
				sqlflag = false;
			}
			awares.setwareName(getGbk(warename)); 
			String publish = request.getParameter("publish");
			if (publish==null)
			{
				publish = "";
			}
			awares.setPublish(getGbk(publish));
			String wareclass = request.getParameter("wareclass");				
			int bc = Integer.parseInt(wareclass);
			awares.setwareClass(bc);
			String wareno = request.getParameter("wareno");
			if (wareno == null)
			{
				wareno = "";
			}
			awares.setwareNo(getGbk(wareno));
			float price;
			try	{
				price =new Float(request.getParameter("price")).floatValue();
			}
			catch (Exception e)
			{	
				price = 0;
				sqlflag = false;
			}
			awares.setPrince(price);
			int amount;
			try
			{
				amount = new Integer(request.getParameter("amount")).intValue();	
			}
			catch (Exception e)
			{
				sqlflag = false;
				amount = 0;
			} 
			awares.setAmount(amount);
			String content = request.getParameter("content");
			if (content == null)
			{	
				content = "";
			}
			awares.setContent(getGbk(content));	        //添加入商品向量数组
			if (sqlflag)
			{
				flag = true;
			}
			return flag;					
		}
		catch (Exception e)
		{
			return flag;
		}
	}

	public String getSql() {
		sqlStr = "select id,classname from my_ware order by id";  //查询商品的id和名字
		return sqlStr;
	}


	public boolean execute(HttpServletRequest res) throws Exception {
		request = res;
		String PAGE = request.getParameter("page");   //页码
		String classid = request.getParameter("classid");	//分类ID号
		String keyword = request.getParameter("keyword");	//查询关键词
		if (classid==null) classid="";		
		if (keyword==null) keyword = "";
		keyword = getGbk(keyword).toUpperCase();
		try
		{
			page = Integer.parseInt(PAGE);
		}
		catch (NumberFormatException e)
		{
			page = 1;
		}
		 
		//取出记录数
		if (!classid.equals("") && keyword.equals("") ) {		
			sqlStr = "select count(*) from my_ware where wareclass='" + classid + "'";
		} else if (!keyword.equals("")) {
			if (classid.equals(""))
			{
				sqlStr = "select count(*) from my_ware where upper(warename) like '%" +keyword+ "%' or upper(content) like '%" + keyword + "%'";
			} else {
				sqlStr = "select count(*) from my_ware where wareclass='" + classid + "' and  (upper(warename) like '%" +keyword+ "%' or upper(content) like '%" + keyword + "%')";
			}
		} else {
			sqlStr = "select count(*) from my_ware";   
		}

		int rscount = pageSize;
		try
		{
			ResultSet rs1 = stmt.executeQuery(sqlStr);
			if (rs1.next()) recordCount = rs1.getInt(1);				
			rs1.close();
		}
		catch (SQLException e)
		{
			return false;
		}
		//设定有多少pageCount
		if (recordCount < 1)
            pageCount = 0;
        else
            pageCount = (int)(recordCount - 1) / pageSize + 1;
		//检查查看的页面数是否在范围内
		if (page < 1)  
            page = 1;
        else if (page > pageCount)
            page = pageCount;
		
		rscount = (int) recordCount % pageSize;	 // 最后一页记录数        

		//sql为倒序取值
		sqlStr = "select  a.id,a.warename,a.wareclass,b.classname,a.publish,a.wareno,a.content,a.prince,a.amount,a.Leav_number,a.regtime from My_ware a,My_wareclass b where a.wareclass = b.Id ";
		if (!classid.equals("") && keyword.equals("") ){  //如果类别不为空，非查询
			if (page == 1)
			{
				sqlStr = sqlStr + " and a.wareclass='" + classid + "' order by a.Id desc";
			} else {
				sqlStr = sqlStr + " and a.wareclass='" + classid + "' and a.Id not in ( select Id from My_ware order by Id ) and a.Id in " +
				"(select Id from My_ware ORDER BY Id )  order by a.Id desc";
			}		
		} else if (!keyword.equals("")) {  //如果是查询资料
			if (page == 1)
			{
				if (!classid.equals(""))  //查询某一类
				{
					sqlStr = sqlStr + "and a.wareclass='" + classid + "' and (upper(a.warename) like '%" +keyword+ "%' or upper(a.content) like '%" + keyword + "%')  order by a.Id desc";
				} else {		//查询所有类
					sqlStr = sqlStr + " and (upper(a.warename) like '%" +keyword+ "%' or upper(a.content) like '%" + keyword + "%') order by a.Id desc";
				}
			} else { 
				if (!classid.equals(""))
				{
					sqlStr = sqlStr + " and a.wareclass='" + classid + "' and (upper(a.warename) like '%" +keyword+ "%' or upper(a.content) like '%" + keyword + "%') and a.Id not in ( select Id from My_ware ORDER BY Id ) and a.Id in " +
					"(select Id from My_ware ORDER BY Id ) " + " order by a.Id desc";
				} else {
					sqlStr = sqlStr + " and (upper(a.warename) like '%" +keyword+ "%' or upper(a.content) like '%" + keyword + "%') and a.Id not in ( select Id from My_ware ORDER BY Id ) and a.Id in " +
					"(select Id from My_ware ORDER BY Id ) " + " order by a.Id desc";
				}
			}	

		} else {		//非查询，也非分类浏览
			if (page == 1)
			{
				sqlStr = sqlStr + "  order by a.Id desc";
			} else {
				sqlStr = sqlStr + " and a.Id not in ( select Id from My_ware ORDER BY Id ) and a.Id in " +
				"(select Id from My_ware ORDER BY Id) order by a.Id desc";
			}		
		}

		try
		{
			rs = stmt.executeQuery(sqlStr);
			warelist = new Vector(rscount);
			//把查询到的商品添加入商品向量数组里
			while (rs.next())
			{
				ware ware = new ware();
				ware.setId(rs.getLong("id"));
				ware.setwareName(rs.getString("warename"));
				ware.setwareClass(rs.getInt("wareclass"));
				ware.setClassname(rs.getString("classname"));
				ware.setPublish(rs.getString("publish"));
				ware.setwareNo(rs.getString("wareno"));
				ware.setContent(rs.getString("content"));
				ware.setPrince(rs.getFloat("prince"));
				ware.setAmount(rs.getInt("amount"));
				ware.setLeav_number(rs.getInt("Leav_number"));
				ware.setRegTime(rs.getString("regtime"));
				warelist.addElement(ware);
			}
			rs.close();
			return true;
		}
		catch (SQLException e)
		{
			
			System.out.println(e);
			return false;
		}
		

	}
  //添加入新的商品
	public boolean insert() throws Exception {
		sqlStr = "insert into my_ware (warename,wareclass,Publish,wareno,Content,Prince,Amount,Leav_number,Regtime) values ('";
		sqlStr = sqlStr + awares.getwareName() + "','";
		sqlStr = sqlStr + awares.getwareClass() + "','";
		sqlStr = sqlStr + awares.getPublish() + "','";
		sqlStr = sqlStr + awares.getwareNo() + "','";
		sqlStr = sqlStr + awares.getContent() + "','";
		sqlStr = sqlStr + awares.getPrince() + "','";
		sqlStr = sqlStr + awares.getAmount() + "','";
		sqlStr = sqlStr + awares.getAmount() + "',";
		sqlStr = sqlStr + "now())";
		try
		{
			stmt.execute(sqlStr);
			return true;
		}
		catch (SQLException sqle)
		{
			System.out.println(sqle);
			return false;
		}
	}
  //修改商品信息
	public boolean update() throws Exception {
		sqlStr = "update my_ware set ";
		sqlStr = sqlStr + "warename = '" + awares.getwareName() + "',";
		sqlStr = sqlStr + "wareclass = '" + awares.getwareClass() + "',";
		sqlStr = sqlStr + "publish = '" + awares.getPublish() + "',";
		sqlStr = sqlStr + "wareno = '" + awares.getwareNo() + "',";
		sqlStr = sqlStr + "content = '" + awares.getContent() + "',";
		sqlStr = sqlStr + "prince = '" + awares.getPrince() + "',";
		sqlStr = sqlStr + "Amount = '" + awares.getAmount() + "',";
		sqlStr = sqlStr + "leav_number = '" + awares.getAmount() + "' ";
		sqlStr = sqlStr + "where id = '" + awares.getId() + "'";
		try
		{		
			stmt.execute(sqlStr);
			return true;
		}
		catch (SQLException e)
		{
			return false;
		}
	
	}
//删除一个商品
	public boolean delete( int aid ) throws Exception {

		sqlStr = "delete from My_ware where id = "  + aid ;
		try
		{
			stmt.execute(sqlStr);
			return true;
		}
		catch (SQLException e)
		{
			System.out.println(e);
			return false;
		}
	}
 // 查询商品
	public boolean getOneware(int newid ) throws Exception {
		try
		{
			sqlStr="select  a.id,a.warename,a.wareclass,b.classname,a.publish,a.wareno,a.content,a.prince,a.amount,a.Leav_number,a.regtime from My_ware a,My_wareclass b where a.wareclass=b.Id and a.Id = " + newid ;
			rs = stmt.executeQuery(sqlStr);
			
			if (rs.next())
			{	warelist = new Vector(1);
				ware ware = new ware();
				ware.setId(rs.getLong("id"));
				ware.setwareName(rs.getString("warename"));
				ware.setwareClass(rs.getInt("wareclass"));
				ware.setClassname(rs.getString("classname"));
				ware.setPublish(rs.getString("publish"));
				ware.setwareNo(rs.getString("wareno"));
				ware.setContent(rs.getString("content"));
				ware.setPrince(rs.getFloat("prince"));
				ware.setAmount(rs.getInt("amount"));
				ware.setLeav_number(rs.getInt("Leav_number"));
				ware.setRegTime(rs.getString("regtime"));
				warelist.addElement(ware);
			} else {
				rs.close();
				return false;
			}
			rs.close();
			return true;
		}
		catch (SQLException e)
		{
			System.out.println(e);
			return false;
		}
		
	}

	public int getPage() {				//显示的页码
		return page;
	}
	public void setPage(int newpage) {
		page = newpage;
	}

	public int getPageSize(){			//每页显示的图书数
		return pageSize;
	}
	public void setPageSize(int newpsize) {
		pageSize = newpsize;
	}

	public int getPageCount() {				//页面总数
		return pageCount;
	}
	public void setPageCount(int newpcount) {
		pageCount = newpcount;
	}

	public long getRecordCount() {
		return recordCount;
	}
	public void setRecordCount(long newrcount) {
		recordCount= newrcount;
	}


};