dbsell.java

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

package electric.dbs;

import java.sql.*;
import javax.servlet.http.*;
import java.text.SimpleDateFormat;
import electric.*;
import electric.electricUtils.*;

public class Dbsell
{
	//定义静态的私有的sql语句
	private static final String LOAD_SELL_BY_ID="SELECT * FROM [SELL] WHERE [Id]=?";
	private static final String INSERT_SELL="INSERT INTO [SELL] ([Id],[Shopid],[Productid],[Procolor],[Protype],"
	    +"[Companytype],[Pronumber],[Ptime],[Createdate],[Status]) "+"VALUES(?,?,?,?,?,?,?,?,?,?)";
	private static final String UPDATE_SELL="UPDATE [SELL] SET [Shopid]=?,[Productid]=?,[Procolor]=?,[Protype]=?,"
	    +"[Companytype]=?,[Pronumber]=?,[Ptime]=?,[Status]=? WHERE [Id]=?";
	private static final String CLEAR_SELL="DELETE FROM [SELL] WHERE STATUS="+FinalConstants.STATUS_DELETE;
	//定义私有成员变量
	private int Id;
	private int Shopid;	
	private int Productid;
	private String Procolor;
	private String Protype;
	private String Companytype;
	private int Pronumber;
	private String Ptime;
	private String Createdate;
	private int Status;
	
	//getXXX()
	public int getId()
	{
		return this.Id;
	}
	public int getShopid()
	{
		return this.Shopid;
	}	
	public int getProductid()
	{
		return this.Productid;
	}
	public String getProcolor()
	{
		return this.Procolor;
	}
	public String getProtype()
	{
		return this.Protype;
	}
	public String getCompanytype()
	{
		return this.Companytype;
	}
	public int getPronumber()
	{
		return this.Pronumber;
	}
	public String getPtime()
	{
		return this.Ptime;
	}
	public String getCreatedate()
	{
		return this.Createdate;
	}
	public int getStatus()
	{
		return this.Status;
	}
	
	//setXXX()
	public void setId(int Id)
	{
		this.Id=Id;
		saveToDb();
	}
	public void setShopid(int Shopid)
	{
		this.Shopid=Shopid;
		saveToDb();
	}
	public void setProductid(int Productid)
	{
		this.Productid=Productid;
		saveToDb();
	}
	public void setProcolor(String Procolor)
	{
		this.Procolor=Procolor;
		saveToDb();
	}
	public void setProtype(String Protype)
	{
		this.Protype=Protype;
		saveToDb();
	}
	public void setCompanytype(String Companytype)
	{
		this.Companytype=Companytype;
		saveToDb();
	}
	public void setPronumber(int Pronumber)
	{
		this.Pronumber=Pronumber;
		saveToDb();
	}
	public void setPtime(String Ptime)
	{
		this.Ptime=Ptime;
		saveToDb();
	}
	public void setCreatdate(String Createdate)
	{
		this.Createdate=Createdate;
		saveToDb();
	}
	public void setStatus(int Status)
	{
		this.Status=Status;
		saveToDb();
	}
	
	//定义构造函数
	public Dbsell()
	{
	}
	public Dbsell(int Id) throws SellNotFoundException
	{
		this.Id=Id;
		if(Id<=0)
		{
			return;
		}
		loadFromDb();
	}
	
	public Dbsell(HttpServletRequest request)
	{
		this.Id=DbSequenceManager.nextID(FinalConstants.T_SELL);
		this.Shopid=ParamUtils.getIntParameter(request,"shopid");
		this.Productid=ParamUtils.getIntParameter(request,"productid");
		this.Procolor=ParamUtils.getEscapeHTMLParameter(request,"procolor");
		this.Protype=ParamUtils.getEscapeHTMLParameter(request,"protype");
		this.Companytype=ParamUtils.getEscapeHTMLParameter(request,"companytype");
		this.Pronumber=ParamUtils.getIntParameter(request,"pronumber");
		SimpleDateFormat simpledate=new SimpleDateFormat("yyyyMMdd");
		String shiftdate=simpledate.format(new java.util.Date());
		this.Ptime=shiftdate;
		this.Createdate=shiftdate;
		this.Status=ParamUtils.getIntParameter(request,"status");
		insertIntoDb();
	}
	
	//modify()
	public void modify(HttpServletRequest request)
	{
		this.Id=ParamUtils.getIntParameter(request,"id");
		this.Shopid=ParamUtils.getIntParameter(request,"shopid");
		this.Productid=ParamUtils.getIntParameter(request,"productid");
		this.Procolor=ParamUtils.getEscapeHTMLParameter(request,"procolor");
		this.Protype=ParamUtils.getEscapeHTMLParameter(request,"protype");
		this.Companytype=ParamUtils.getEscapeHTMLParameter(request,"companytype");
		this.Pronumber=ParamUtils.getIntParameter(request,"pronumber");
		String ayear = ParamUtils.getEscapeHTMLParameter(request, "Ayear");
		String amonth = ParamUtils.getEscapeHTMLParameter(request, "Amonth");
		String aday = ParamUtils.getEscapeHTMLParameter(request, "Aday");
		this.Ptime = ayear+amonth+aday;
		this.Status=ParamUtils.getIntParameter(request,"status");
		saveToDb();		
	}
	
	//loadFromDb()方法，用于提取特定Id的记录
	private void loadFromDb() throws SellNotFoundException
	{
		Connection con=null;
		PreparedStatement pstmt=null;
		ResultSet rs=null;
		try
		{
		   con=DbConnectionManager.getConnection();
		   pstmt=con.prepareStatement(LOAD_SELL_BY_ID);
		   pstmt.setInt(1,this.Id);
		   rs=pstmt.executeQuery();
		   if(!rs.next())
		   {
			throw new SellNotFoundException("从数据表[SELL]中读取用户数据失败,欲读取的用户ID:[ " +
                                    Id + "]!");		   	
		   }
		   this.Id=rs.getInt(1);
		   this.Shopid=rs.getInt(2);
		   this.Productid=rs.getInt(3);
		   this.Procolor=rs.getString(4);
		   this.Protype=rs.getString(5);
		   this.Companytype=rs.getString(6);
		   this.Pronumber=rs.getInt(7);
		   this.Ptime=rs.getString(8);
		   this.Createdate=rs.getString(9);
		   this.Status=rs.getInt(10);
		   rs.close();
		   pstmt.close();
		   con.close();
		}
		catch(SQLException sqle)
		{
			throw new SellNotFoundException("从数据表[SELL]中读取用户数据失败,欲读取的用户ID:[ " +
                                    Id + "]!");
		}
	}
	
	//saveToDb(),保存从前台提交的销售信息
	private void saveToDb()
	{
	    Connection con=null;
		PreparedStatement pstmt=null;
		try
		{
		   con=DbConnectionManager.getConnection();
		   pstmt=con.prepareStatement(UPDATE_SELL);
		   pstmt.setInt(1,this.Shopid);
		   pstmt.setInt(2,this.Productid);
		   pstmt.setString(3,StringUtils.toChinese(this.Procolor));
		   pstmt.setString(4,StringUtils.toChinese(this.Protype));
		   pstmt.setString(5,StringUtils.toChinese(this.Companytype));
		   pstmt.setInt(6,this.Pronumber);
		   pstmt.setString(7,StringUtils.toChinese(this.Ptime));
		   pstmt.setInt(8,this.Status);
		   pstmt.setInt(9,this.Id);
		   pstmt.executeUpdate();

		}
		catch(SQLException sqle)
		{
			    System.err.println("错误位置: DbSell.java:saveToDb(): " + sqle);
                sqle.printStackTrace();
		}
		finally
		{
			try
			{
				pstmt.close();
				con.close();
			}
			catch(Exception e)
			{
				e.printStackTrace();
			}
		}	
	}
	
	//insertIntoDb(),添加新的记录
	private void insertIntoDb()
	{
	    Connection con=null;
		PreparedStatement pstmt=null;
		ResultSet rs=null;
		try
		{
		   con=DbConnectionManager.getConnection();
		   pstmt=con.prepareStatement(INSERT_SELL);
		   pstmt.setInt(1,this.Id);
		   pstmt.setInt(2,this.Shopid);
		   pstmt.setInt(3,this.Productid);
		   pstmt.setString(4,StringUtils.toChinese(this.Procolor));
		   pstmt.setString(5,StringUtils.toChinese(this.Protype));
		   pstmt.setString(6,StringUtils.toChinese(this.Companytype));
		   pstmt.setInt(7,this.Pronumber);
		   pstmt.setString(8,StringUtils.toChinese(this.Ptime));
		   pstmt.setString(9,StringUtils.toChinese(this.Createdate));
		   pstmt.setInt(10,this.Status);
		   pstmt.executeUpdate();

		}
		catch(SQLException sqle)
		{
			    System.err.println("错误位置: DbSell.java:insertIntoDb(): " + sqle);
                sqle.printStackTrace();
		}
		finally
		{
			try
			{
				pstmt.close();
				con.close();
			}
			catch(Exception e)
			{
				e.printStackTrace();
			}
		}	
	}
	
	//delete()
	protected void delete()
	{
		setStatus(FinalConstants.STATUS_DELETE);
	}
	
	//clear()
	protected static void clear()
	{
		Connection con=null;
		PreparedStatement pstmt=null;
		ResultSet rs=null;
		try
		{
		   con=DbConnectionManager.getConnection();
		   pstmt=con.prepareStatement(CLEAR_SELL);		   
		   pstmt.executeUpdate();

		}
		catch(SQLException sqle)
		{
			    System.err.println("错误位置: DbSell.java:clear(): " + sqle);
                sqle.printStackTrace();
		}
		finally
		{
			try
			{
				rs.close();
				pstmt.close();
				con.close();
			}
			catch(Exception e)
			{
				e.printStackTrace();
			}
		}	
	}
}