prejdbcdao.java

本套系统采用了业界当前最为流行的beanAction组件

				resultList.add(findPre);
								for (int k = 0; k < rs.getMetaData().getColumnCount(); k++){
									
						 			
						 			sheet.addCell(new Label(k,j+i+1,rs.getString(k+1)));
						 		}  
						 		i++;
			}
						book.write();
					    book.close();   
					    outf.close();
		} catch (Exception e) {
			dealException(e);
		} finally {
			close(rs, pstm);
		}

		return resultList;
	}

	public void deletePre(String preid) {
		String sql = "delete from Preincome where preid = ?";
		PreparedStatement pstm = null;

		try {
			pstm = getConnection().prepareStatement(sql);
			pstm.setString(1, preid);
			pstm.executeUpdate();
		} catch (SQLException e) {
			dealException(e);
		} finally {
			close(pstm);
		}
		return;
	}

	public PreInfo updateAllPre(PreInfo preInfo) {
		String sql = "update Preincome set cityId=?,prodId=?,chargeTypeId=?,money = ? where preid= ?";
		String sqlResult = "select  preid,sale_time,city.cityName as cityName, "
				+ "product.prodName as prodName,charge_type.typeofcharge as typeofcharge, "
				+ "money,state,checkerId,checktime from Preincome,city,product,charge_type "
				+ "where Preincome. preid= ? and city.cityId = Preincome.cityId "
				+ "and product.prodId = Preincome.prodId and charge_type.chargeTypeId=Preincome.chargeTypeId";

		PreparedStatement pstm = null;
		ResultSet rs = null;
		PreInfo findPre = new PreInfo();

		try {
			pstm = getConnection().prepareStatement(sql);
			pstm.setString(1, preInfo.getCityId());
			pstm.setString(2, preInfo.getProdId());
			pstm.setString(3, preInfo.getChargeTypeId());
			pstm.setString(4, preInfo.getMoney());
			pstm.setString(5, preInfo.getPreid());
			pstm.executeUpdate();

			pstm = getConnection().prepareStatement(sqlResult);
			pstm.setString(1, preInfo.getPreid());
			rs = pstm.executeQuery();

			while (rs.next()) {
				findPre.setPreid(rs.getString("preid"));
				findPre.setSale_time(rs.getDate("sale_time").toString());
				findPre.setMoney(rs.getString("money"));
				findPre.setState(rs.getString("state"));
				findPre.setCheckerId(rs.getString("checkerId"));
				findPre.setChecktime(rs.getString("checktime"));
				findPre.setCityName(rs.getString("cityName"));
				findPre.setTypeofcharge(rs.getString("typeofcharge"));
				findPre.setProdName(rs.getString("prodName"));
			}

		} catch (SQLException e) {
			dealException(e);
		} finally {
			close(rs, pstm);
		}

		return findPre;
	}

	public PreInfo updatePreState(PreInfo preInfo) {
		String sql = "update Preincome set checkerId=?,checktime=?,state=? where  preid=?";
		String sqlResult = "select  preid,sale_time,city.cityName as cityName, "
				+ "product.prodName as prodName,charge_type.typeofcharge as typeofcharge, "
				+ "money,state,checkerId,checktime from Preincome,city,product,charge_type "
				+ "where Preincome.preid= ? and city.cityId = Preincome.cityId "
				+ "and product.prodId = Preincome.prodId and charge_type.chargeTypeId=Preincome.chargeTypeId";
		PreparedStatement pstm = null;
		ResultSet rs = null;
		PreInfo findPre = new PreInfo();

		try {
			pstm = getConnection().prepareStatement(sql);
			pstm.setString(1, preInfo.getCheckerId());
			pstm.setDate(2, Date.valueOf(preInfo.getChecktime()));
			pstm.setString(3, preInfo.getState());
			pstm.setString(4, preInfo.getPreid());
			pstm.executeUpdate();

			pstm = getConnection().prepareStatement(sqlResult);
			pstm.setString(1, preInfo.getPreid());
			rs = pstm.executeQuery();

			while (rs.next()) {

				findPre.setPreid(rs.getString("preid"));
				findPre.setSale_time(rs.getString("sale_time"));
				findPre.setMoney(rs.getString("money"));
				findPre.setState(rs.getString("state"));
				findPre.setCheckerId(rs.getString("checkerId"));
				findPre.setChecktime(rs.getString("checktime"));
				findPre.setCityName(rs.getString("cityName"));
				findPre.setTypeofcharge(rs.getString("typeofcharge"));
				findPre.setProdName(rs.getString("prodName"));
			}
		} catch (SQLException e) {
			dealException(e);
		} finally {
			close(pstm);
		}
		return findPre;
	}

	public List<ChargeType> getChargeType() {
		String sql = "select chargeTypeId,typeofcharge from charge_type";
		PreparedStatement pstm = null;
		ResultSet rs = null;
		List<ChargeType> resultList = new ArrayList<ChargeType>();

		try {
			pstm = getConnection().prepareStatement(sql);
			rs = pstm.executeQuery();

			while (rs.next()) {
				ChargeType chargeType = new ChargeType();
				chargeType.setChargeTypeId(rs.getString("chargeTypeId"));
				chargeType.setTypeofcharge(rs.getString("typeofcharge"));

				resultList.add(chargeType);
			}

		} catch (SQLException e) {
			dealException(e);
		} finally {
			close(rs, pstm);
		}

		return resultList;
	}

	public List<Product> getProduct() {
		String sql = "select prodId,prodName from product where ISUSED = 1 AND isChecked = 1";
		PreparedStatement pstm = null;
		ResultSet rs = null;
		List<Product> resultList = new ArrayList<Product>();

		try {
			pstm = getConnection().prepareStatement(sql);
			rs = pstm.executeQuery();

			while (rs.next()) {
				Product product = new Product();
				product.setProdId(rs.getString("prodId"));
				product.setProdName(rs.getString("prodName"));

				resultList.add(product);
			}

		} catch (SQLException e) {
			dealException(e);
		} finally {
			close(rs, pstm);
		}

		return resultList;
	}

	public List<City> getCity() {

		String sql = "select cityId,cityName from city";
		PreparedStatement pstm = null;
		ResultSet rs = null;
		List<City> resultList = new ArrayList<City>();

		try {
			pstm = getConnection().prepareStatement(sql);
			rs = pstm.executeQuery();

			while (rs.next()) {
				City city = new City();
				city.setCityId(rs.getString("cityId"));
				city.setCityName(rs.getString("cityName"));

				resultList.add(city);
			}

		} catch (SQLException e) {
			dealException(e);
		} finally {
			close(rs, pstm);
		}

		return resultList;
	}

	public PreInfo getPreById(String preid) {
		String sql = "select * from preincome where preid = " + preid;
		PreparedStatement pstm = null;
		ResultSet rs = null;
		PreInfo findPre = new PreInfo();
		try{
			pstm = getConnection().prepareStatement(sql);
			rs = pstm.executeQuery();
			while(rs.next()){				
				findPre.setMoney(rs.getString("money"));			
				findPre.setCityId(rs.getString("cityId"));
				findPre.setChargeTypeId(rs.getString("chargeTypeId"));
				findPre.setProdId(rs.getString("prodId"));
				findPre.setPreid(rs.getString("preid"));
			}
		}catch(SQLException e){
			dealException(e);
		}finally{
			close(rs,pstm);
		}
		return findPre;
	}
}