offersdao.java

交易撮合系统是一套买卖信息沟通的平台

		}
		return totalPage;
	}

	/*
	 * 根据传来的ID查出该帖子的内容
	 */
	public Offers getOfferContent(int id) {

		Connection conn = null;
		PreparedStatement pstmt = null;
		
		String sql = "SELECT * FROM webpk.offers WHERE offers.offersid=?";
		Offers of = null;
		
		try {
			conn = DBConnection.getConn();
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, id);
			
			ResultSet rs = pstmt.executeQuery();
			
			while(rs.next()){
				of = new Offers();
				of.setOffersid(rs.getInt("offersid"));
				of.setProductsid(rs.getInt("productsid"));
				of.setContent(rs.getString("content"));
				of.setPost_time(rs.getDate("post_time"));
				of.setEnd_time(rs.getDate("end_time"));
				of.setEmail(rs.getString("email"));
				of.setAmount(rs.getInt("amount"));
				of.setDeliver(rs.getString("deliver"));
				of.setPayment(rs.getString("payment"));
				of.setPrice(rs.getDouble("price"));
				of.setHits(rs.getInt("hits"));
				of.setTrade(rs.getInt("trade"));
				of.setState(rs.getString("state"));
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			DBConnection.closeConn();
		}
		return of;

	}
	/*
	 * 修改帖子状态
	 */
	public int changeOffer(String content,String deliver,String payment,String state,String remark,int id) {

		Connection conn = null;
		PreparedStatement pstmt = null;
		
		String sql = "UPDATE webpk.offers o SET o.content=?,o.deliver=?,o.payment=?,o.state=?,o.remark=? WHERE o.offersid=?";
		int count = 0;
		
		try {
			conn = DBConnection.getConn();
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, content);
			pstmt.setString(2, deliver);
			pstmt.setString(3, payment);
			pstmt.setString(4, state);
			pstmt.setString(5, remark);
			pstmt.setInt(6, id);
			count = pstmt.executeUpdate();
			
			
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			DBConnection.closeConn();
		}
		return count;

	}
	/*
	 * 根据用户名查帖子的相关信息
	 * 买的信息
	 */
	public List<Offers> getBuyOffers(String email){
		Connection conn = DBConnection.getConn();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		String sql = "SELECT * FROM webpk.offers o WHERE o.email=? AND o.trade=0";
		List<Offers> olist = new ArrayList<Offers>();
		Offers off = null;
		try{
			pstmt = conn.prepareStatement(sql);	
			
			pstmt.setString(1, email);
			rs = pstmt.executeQuery();
			
			while(rs.next()){
				off = new Offers();
				off.setAmount(rs.getInt("amount"));
				off.setContent(rs.getString("content"));
				off.setDeliver(rs.getString("deliver"));
				off.setEnd_time(rs.getDate("end_time"));
				off.setPayment(rs.getString("payment"));
				off.setPost_time(rs.getDate("post_time"));
				off.setPrice(rs.getInt("price"));
				off.setTitle(rs.getString("title"));
				off.setProductsid(rs.getInt("productsid"));
				off.setRemark(rs.getString("remark"));
				off.setState(rs.getString("state"));
				olist.add(off);
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			DBConnection.closeConn();
		}
		return olist;
	}
	/*
	 * 卖的信息
	 */
	public List<Offers> getSellOffers(String email){
		Connection conn = DBConnection.getConn();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		String sql = "SELECT * FROM webpk.offers o WHERE o.email=? AND o.trade=1";
		List<Offers> olist = new ArrayList<Offers>();
		Offers off = null;
		try{
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, email);
			rs = pstmt.executeQuery();
			
			while(rs.next()){
				off = new Offers();
				off.setAmount(rs.getInt("amount"));
				off.setContent(rs.getString("content"));
				off.setDeliver(rs.getString("deliver"));
				off.setEnd_time(rs.getDate("end_time"));
				off.setPayment(rs.getString("payment"));
				off.setPost_time(rs.getDate("post_time"));
				off.setPrice(rs.getInt("price"));
				off.setTitle(rs.getString("title"));
				off.setProductsid(rs.getInt("productsid"));
				off.setRemark(rs.getString("remark"));
				off.setState(rs.getString("state"));
				olist.add(off);
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			DBConnection.closeConn();
		}
		return olist;
	}
	/*
	 * 买的信息
	 * 等待交易的帖子
	 */
	public List<Offers> getBuyWaitOffers(String email){
		Connection conn = DBConnection.getConn();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		String sql = "SELECT * FROM webpk.offers o WHERE o.email=? AND o.trade=0 AND o.state='发贴中'";
		List<Offers> olist = new ArrayList<Offers>();
		Offers off = null;
		try{
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, email);
			rs = pstmt.executeQuery();
			
			while(rs.next()){
				off = new Offers();
				off.setAmount(rs.getInt("amount"));
				off.setContent(rs.getString("content"));
				off.setDeliver(rs.getString("deliver"));
				off.setEnd_time(rs.getDate("end_time"));
				off.setPayment(rs.getString("payment"));
				off.setPost_time(rs.getDate("post_time"));
				off.setPrice(rs.getInt("price"));
				off.setTitle(rs.getString("title"));
				off.setProductsid(rs.getInt("productsid"));
				off.setRemark(rs.getString("remark"));
				
				olist.add(off);
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			DBConnection.closeConn();
		}
		return olist;
	}
	/*
	 * 买的信息
	 * 交易中的帖子
	 */
	public List<Offers> getBuyingOffers(String email){
		Connection conn = DBConnection.getConn();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		String sql = "SELECT * FROM webpk.offers o WHERE o.email=? AND o.trade=0 AND o.state='交易中'";
		List<Offers> olist = new ArrayList<Offers>();
		Offers off = null;
		try{
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, email);
			rs = pstmt.executeQuery();
			
			while(rs.next()){
				off = new Offers();
				off.setAmount(rs.getInt("amount"));
				off.setContent(rs.getString("content"));
				off.setDeliver(rs.getString("deliver"));
				off.setEnd_time(rs.getDate("end_time"));
				off.setPayment(rs.getString("payment"));
				off.setPost_time(rs.getDate("post_time"));
				off.setPrice(rs.getInt("price"));
				off.setTitle(rs.getString("title"));
				off.setProductsid(rs.getInt("productsid"));
				off.setRemark(rs.getString("remark"));
				
				olist.add(off);
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			DBConnection.closeConn();
		}
		return olist;
	}
	/*
	 * 买的信息
	 * 交易成功的帖子
	 */
	public List<Offers> getBuySuccessOffers(String email){
		Connection conn = DBConnection.getConn();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		String sql = "SELECT * FROM webpk.offers o WHERE o.email=? AND o.trade=0 AND o.state='交易成功'";
		List<Offers> olist = new ArrayList<Offers>();
		Offers off = null;
		try{
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, email);
			rs = pstmt.executeQuery();
			
			while(rs.next()){
				off = new Offers();
				off.setAmount(rs.getInt("amount"));
				off.setContent(rs.getString("content"));
				off.setDeliver(rs.getString("deliver"));
				off.setEnd_time(rs.getDate("end_time"));
				off.setPayment(rs.getString("payment"));
				off.setPost_time(rs.getDate("post_time"));
				off.setPrice(rs.getInt("price"));
				off.setTitle(rs.getString("title"));
				off.setProductsid(rs.getInt("productsid"));
				off.setRemark(rs.getString("remark"));
				
				olist.add(off);
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			DBConnection.closeConn();
		}
		return olist;
	}
	/*
	 * 卖的信息
	 * 等待交易的帖子
	 */
	public List<Offers> getSellWaitOffers(String email){
		Connection conn = DBConnection.getConn();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		String sql = "SELECT * FROM webpk.offers o WHERE o.email=? AND o.trade=1 AND o.state='发贴中'";
		List<Offers> olist = new ArrayList<Offers>();
		Offers off = null;
		try{
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, email);
			rs = pstmt.executeQuery();
			
			while(rs.next()){
				off = new Offers();
				off.setAmount(rs.getInt("amount"));
				off.setContent(rs.getString("content"));
				off.setDeliver(rs.getString("deliver"));
				off.setEnd_time(rs.getDate("end_time"));
				off.setPayment(rs.getString("payment"));
				off.setPost_time(rs.getDate("post_time"));
				off.setPrice(rs.getInt("price"));
				off.setTitle(rs.getString("title"));
				off.setProductsid(rs.getInt("productsid"));
				off.setRemark(rs.getString("remark"));
				
				olist.add(off);
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			DBConnection.closeConn();
		}
		return olist;
	}
	/*
	 * 卖的信息
	 * 交易中的帖子
	 */
	public List<Offers> getSellingOffers(String email){
		Connection conn = DBConnection.getConn();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		String sql = "SELECT * FROM webpk.offers o WHERE o.email=? AND o.trade=1 AND o.state='交易中'";
		List<Offers> olist = new ArrayList<Offers>();
		Offers off = null;
		try{
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, email);
			rs = pstmt.executeQuery();
			
			while(rs.next()){
				off = new Offers();
				off.setAmount(rs.getInt("amount"));
				off.setContent(rs.getString("content"));
				off.setDeliver(rs.getString("deliver"));
				off.setEnd_time(rs.getDate("end_time"));
				off.setPayment(rs.getString("payment"));
				off.setPost_time(rs.getDate("post_time"));
				off.setPrice(rs.getInt("price"));
				off.setTitle(rs.getString("title"));
				off.setProductsid(rs.getInt("productsid"));
				off.setRemark(rs.getString("remark"));
				
				olist.add(off);
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			DBConnection.closeConn();
		}
		return olist;
	}
	/*
	 * 卖的信息
	 * 交易成功的帖子
	 */
	public List<Offers> getSellSuccessOffers(String email){
		Connection conn = DBConnection.getConn();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		String sql = "SELECT * FROM webpk.offers o WHERE o.email=? AND o.trade=1 AND o.state='交易成功'";
		List<Offers> olist = new ArrayList<Offers>();
		Offers off = null;
		try{
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, email);
			rs = pstmt.executeQuery();
			
			while(rs.next()){
				off = new Offers();
				off.setAmount(rs.getInt("amount"));
				off.setContent(rs.getString("content"));
				off.setDeliver(rs.getString("deliver"));
				off.setEnd_time(rs.getDate("end_time"));
				off.setPayment(rs.getString("payment"));
				off.setPost_time(rs.getDate("post_time"));
				off.setPrice(rs.getInt("price"));
				off.setTitle(rs.getString("title"));
				off.setProductsid(rs.getInt("productsid"));
				off.setRemark(rs.getString("remark"));
				
				olist.add(off);
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			DBConnection.closeConn();
		}
		return olist;
	}
	public int delOffer(int id){
		Connection conn = DBConnection.getConn();
		PreparedStatement pstmt = null;
		int count = 0;		
		String sql = "DELETE FROM webpk.offers where offers.offersid=?";
		
		try{
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, id);
			count = pstmt.executeUpdate();
			
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			DBConnection.closeConn();
		}
		
		return count;
	}
}