dbdco.java

来自「海鲜超市管理系统是不个错的超市管理系统」· Java 代码 · 共 1,111 行 · 第 1/2 页

	{
	
		Connection co = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		String vendor_Name = null;
		List list = new ArrayList();
		String sql = " SELECT VENDOR_NAME FROM TABLE_VENDOR WHERE VENDOR_ID = ? ";
			
		try
		{
			co = DBConn.getConn();
			ps = co.prepareStatement(sql);
			ps.setLong(1,vendor_ID);
			rs = ps.executeQuery();
			
			if (rs.next())
			{
				vendor_Name = rs.getString("VENDOR_NAME");
			}
		}
		catch (Exception e)
		{
			e.printStackTrace();
		}
		finally
		{
			try
			{
				rs.close();
				ps.close();
				co.close();
			}
			catch (Exception e1)
			{
				e1.printStackTrace();
			}
		}
		return vendor_Name;
	}

//	往Table_vendor表中插入获取的信息
	public int inseV(VendorDto vd)
	{
		Connection co = null;
		PreparedStatement ps = null;
		int flg = 0;
		String sql = " INSERT INTO TABLE_VENDOR VALUES(VENDOR_SEQ.NEXTVAL,?,?,?,?,?) ";
			
		try
		{
			co = DBConn.getConn();
			co.setAutoCommit(false);
			ps = co.prepareStatement(sql);
			ps.setString(1,vd.getVendor_Name());
			ps.setString(2,vd.getVendor_Address());
			ps.setString(3,vd.getVendor_Phone());
			ps.setString(4,vd.getVendor_Fas());
			ps.setString(5,vd.getVendor_Person());
			flg = ps.executeUpdate();
			co.commit();
		}
		catch (Exception e)
		{
			try
			{
				co.rollback();
			}
			catch (Exception e1)
			{
				e1.printStackTrace();
			}
			e.printStackTrace();
		}
		finally
		{
			try
			{
				ps.close();
				co.close();
			}
			catch (Exception e2)
			{
				e2.printStackTrace();
			}
		}
		return flg;
	}
	
//	跟据所补获信息，修改Table_Vendor表中的信息值
	public int updaV(VendorDto vd)
	{
		Connection co = null;
		PreparedStatement ps = null;
		int flg = 0;
		String sql = " UPDATE TABLE_VENDOR SET VENDOR_NAME=?,VENDOR_ADDRESS=?,VENDOR_PHONE=?,VENDOR_FAS=?,VENDOR_CONTACT_PERSON=? WHERE VENDOR_ID=? ";
			
		try
		{
			co = DBConn.getConn();
			co.setAutoCommit(false);
			ps = co.prepareStatement(sql);
			ps.setString(1,vd.getVendor_Name());
			ps.setString(2,vd.getVendor_Address());
			ps.setString(3,vd.getVendor_Phone());
			ps.setString(4,vd.getVendor_Fas());
			ps.setString(5,vd.getVendor_Person());
			ps.setInt(6,Integer.parseInt(vd.getVendor_Id()));
			flg = ps.executeUpdate();
			co.commit();
		}
		catch (Exception e)
		{
			try
			{
				co.rollback();
			}
			catch (Exception e1)
			{
				e1.printStackTrace();
			}
			e.printStackTrace();
		}
		finally
		{
			try
			{
				ps.close();
				co.close();
			}
			catch (Exception e2)
			{
				e2.printStackTrace();
			}
		}
		return flg;
	}
	
//	从序列中获取bill_id
	public long bill_id()
	{
		Connection co = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		long bill_Id = 0;
		String sql = " SELECT BILL_SEQ.NEXTVAL SEQ FROM DUAL ";
			
		try
		{
			co = DBConn.getConn();
			ps = co.prepareStatement(sql);
					
			rs = ps.executeQuery();
					
			if (rs.next())
			{
				bill_Id = rs.getLong("SEQ");			
			}
		}
		catch (Exception e)
		{
			e.printStackTrace();
		}
		finally
		{
			try
			{
				rs.close();
				ps.close();
				co.close();
			}
			catch (Exception e1)
			{
				e1.printStackTrace();
			}
		}
		return bill_Id;
	}
	
//	往TABLE_BILL表中插入传递的数据
	public int addBill(BillDto bd)
	{
		Connection co = null;
		PreparedStatement ps = null;
		int flg = 0;
				
		String sql = " INSERT INTO TABLE_BILL VALUES(?,?,TO_DATE(?,'YYYY-MM-DD'),TO_DATE(?,'YYYY-MM-DD'),0,?) ";
		
		try
		{
			co = DBConn.getConn();
			co.setAutoCommit(false);
			ps = co.prepareStatement(sql);
			ps.setInt(1,Integer.parseInt(bd.getBill_ID()));
			ps.setString(2,bd.getVerdor_Id());
			ps.setString(3,bd.getBill_Date());
			ps.setString(4,bd.getBill_Due_Date());
			
			ps.setString(5,bd.getBill_Amount());
			flg = ps.executeUpdate();
			co.commit();
		}
		catch (Exception e)
		{
			try
			{
				co.rollback();
			}
			catch (Exception e1)
			{
				e1.printStackTrace();
			}
			e.printStackTrace();
		}
		finally
		{
			try
			{
				ps.close();
				co.close();
			}
			catch (Exception e2)
			{
				e2.printStackTrace();
			}
		}
		return flg;
	}
	
//	往TABLE_BILL_ITEM表中插入传递的数据
	public int addBill_item(BillitemDto bidto)
	{
		Connection co = null;
		PreparedStatement ps = null;
		int flg = 0;
				
		String sql = " INSERT INTO TABLE_BILL_ITEM VALUES(?,?,BILL_ITEM_SEQ.NEXTVAL,?) ";
		
		try
		{
			co = DBConn.getConn();
			co.setAutoCommit(false);
			ps = co.prepareStatement(sql);

			ps.setString(1,bidto.getBill_id());
			ps.setString(2,bidto.getDept_id());
			ps.setString(3,bidto.getBill_item_expense());
			
			flg = ps.executeUpdate();
			co.commit();
		}
		catch (Exception e)
		{
			try
			{
				co.rollback();
			}
			catch (Exception e1)
			{
				e1.printStackTrace();
			}
			e.printStackTrace();
		}
		finally
		{
			try
			{
				ps.close();
				co.close();
			}
			catch (Exception e2)
			{
				e2.printStackTrace();
			}
		}
		return flg;
	}
	
//	用传入的供应商名称参数，查询出供应商ID
	public VendorDto seleBV(String vendor_Name)
	{		
		Connection co = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		VendorDto ia = new VendorDto();
		String sql = " SELECT VENDOR_ID ID,VENDOR_NAME NAME FROM TABLE_VENDOR WHERE VENDOR_NAME = ? ";
			
		try
		{
			co = DBConn.getConn();
			ps = co.prepareStatement(sql);
			ps.setString(1,vendor_Name);
			rs = ps.executeQuery();
			
			if (rs.next())
			{				
				ia.setVendor_Id(Integer.toString(rs.getInt("ID")));
				ia.setVendor_Name(rs.getString("NAME"));				
			}
		}
		catch (Exception e)
		{
			e.printStackTrace();
		}
		finally
		{
			try
			{
				rs.close();
				ps.close();
				co.close();
			}
			catch (Exception e1)
			{
				e1.printStackTrace();
			}
		}
		return ia;
	}
	
//	查询BILL表中未付款的记录
	public List seleB(String Vendor_Id)
	{
		Connection co = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		List list = new ArrayList();

		String sql = "SELECT TA.BILL_ID BILL_ID,TA.VENDOR_ID VENDOR_ID,T.VENDOR_NAME VNAME,TO_CHAR(BILL_DATE,'MM/DD/YYYY') BILL_DATE,TO_CHAR(BILL_DUE_DATE,'MM/DD/YYYY') DUE_DATE,BILL_AMOUNT "+
			"FROM TABLE_BILL TA,TABLE_VENDOR T WHERE TA.VENDOR_ID=T.VENDOR_ID AND BILL_PAID_FLAG = 0 AND TA.VENDOR_ID= ? ";
			
		try
		{
			co = DBConn.getConn();
			ps = co.prepareStatement(sql);
			ps.setInt(1,Integer.parseInt(Vendor_Id));
			rs = ps.executeQuery();
			
			while (rs.next())
			{				
				BillDto bd = new BillDto();
				bd.setBill_ID(Long.toString(rs.getLong("BILL_ID")));
				bd.setVerdor_Name(rs.getString("VNAME"));
				bd.setVerdor_Id(Integer.toString(rs.getInt("VENDOR_ID")));
				bd.setBill_Date(rs.getString("BILL_DATE"));
				bd.setBill_Due_Date(rs.getString("DUE_DATE"));
				bd.setBill_Amount(Integer.toString(rs.getInt("BILL_AMOUNT")));
		
				list.add(bd);		
			}			
		}
		catch (Exception e)
		{
			e.printStackTrace();
		}
		finally
		{
			try
			{
				rs.close();
				ps.close();
				co.close();
			}
			catch (Exception e1)
			{
				e1.printStackTrace();
			}
		}
		return list;
	}
	
//	修改bill表中的支付标记
	public int updaB(String bill_Id)
	{
		Connection co = null;
		PreparedStatement ps = null;
		int flg = 0;
				
		String sql = " UPDATE TABLE_BILL SET BILL_PAID_FLAG=1 WHERE BILL_ID = ? ";
		
		try
		{
			co = DBConn.getConn();
			co.setAutoCommit(false);
			ps = co.prepareStatement(sql);
			ps.setInt(1,Integer.parseInt(bill_Id));
			flg = ps.executeUpdate();
			co.commit();
		}
		catch (Exception e)
		{
			try
			{
				co.rollback();
			}
			catch (Exception e1)
			{
				e1.printStackTrace();
			}
			e.printStackTrace();
		}
		finally
		{
			try
			{
				ps.close();
				co.close();
			}
			catch (Exception e2)
			{
				e2.printStackTrace();
			}
		}
		return flg;
	}
	
//	查序列CHECK_SEQ
	public long check_id()
	{
		Connection co = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		long check_Id = 0;
		String sql = " SELECT CHECK_SEQ.NEXTVAL SEQ FROM DUAL ";
			
		try
		{
			co = DBConn.getConn();
			ps = co.prepareStatement(sql);
			
		
			rs = ps.executeQuery();
		
			
			if (rs.next())
			{
				check_Id = rs.getLong("SEQ");			
			}
		}
		catch (Exception e)
		{
			e.printStackTrace();
		}
		finally
		{
			try
			{
				rs.close();
				ps.close();
				co.close();
			}
			catch (Exception e1)
			{
				e1.printStackTrace();
			}
		}
		return check_Id;
	}
	
	
//	往Table_Check表中插入数据
	public int inseC(long check_Id,String check_Number)
	{
		Connection co = null;
		PreparedStatement ps = null;
		int flg = 0;
		String sql = " INSERT INTO TABLE_CHECK VALUES(?,?) ";
			
		try
		{
			co = DBConn.getConn();
			co.setAutoCommit(false);
			ps = co.prepareStatement(sql);
			ps.setLong(1,check_Id);
			ps.setInt(2,Integer.parseInt(check_Number));
			
			flg = ps.executeUpdate();
			co.commit();
		}
		catch (Exception e)
		{
			try
			{
				co.rollback();
			}
			catch (Exception e1)
			{
				e1.printStackTrace();
			}
			e.printStackTrace();
		}
		finally
		{
			try
			{
				ps.close();
				co.close();
			}
			catch (Exception e2)
			{
				e2.printStackTrace();
			}
		}
		return flg;
	}
	
//	向TABLE_PAYMENT表中插入数据
	public int inseP(String bill_Id,long check_Id)
	{
		Connection co = null;
		PreparedStatement ps = null;
		int flg = 0;
		String sql = " INSERT INTO TABLE_PAYMENT VALUES(?,?) ";
			
		try
		{
			co = DBConn.getConn();
			co.setAutoCommit(false);
			ps = co.prepareStatement(sql);
			ps.setLong(1,Long.parseLong(bill_Id));
			ps.setLong(2,check_Id);
		
			
			flg = ps.executeUpdate();
			co.commit();
		}
		catch (Exception e)
		{
			try
			{
				co.rollback();
			}
			catch (Exception e1)
			{
				e1.printStackTrace();
			}
			e.printStackTrace();
		}
		finally
		{
			try
			{
				ps.close();
				co.close();
			}
			catch (Exception e2)
			{
				e2.printStackTrace();
			}
		}
		return flg;
	}	
}