offersdao.java

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

/**
 * 有关OFFERS的，获取属性的方法
 */
package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.List;

import conn.DBConnection;
import domain.Offers;
import domain.Products;
import domain.TitleLei;

public class OffersDao {
	/*
	 * 一个帖子里，所有买进的数量
	 */

	public List<Offers> getAmountin() {

		Connection conn = DBConnection.getConn();
		String sql = "SELECT offers.amount FROM webpk.offers,webpk.products WHERE trade=0 AND offers.productsid=products.productsid ORDER BY products.productsid";
		List<Offers> olist1 = new ArrayList<Offers>();
		try {
			Statement stmt = (Statement) conn.createStatement();
			ResultSet rs = stmt.executeQuery(sql);

			Offers pro = null;
			while (rs.next()) {
				pro = new Offers();
				pro.setInamount(rs.getInt("inamount"));
				olist1.add(pro);
			}

		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			DBConnection.closeConn();
		}
		
		return olist1;
	}

	/*
	 * 一个帖子里，所有卖出的数量
	 */
	public List<Offers> getAmountout() {

		Connection conn = DBConnection.getConn();
		String sql = "SELECT offers.amount FROM webpk.offers,webpk.products WHERE trade=1 AND offers.productsid=products.productsid ORDER BY products.productsid";
		List<Offers> olist2 = new ArrayList<Offers>();
		try {
			Statement stmt = (Statement) conn.createStatement();
			ResultSet rs = stmt.executeQuery(sql);

			Offers pro = null;
			while (rs.next()) {
				pro = new Offers();
				pro.setOutamount(rs.getInt("outamount"));
				olist2.add(pro);
			}

		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			DBConnection.closeConn();
		}
		
		return olist2;
	}

	/*
	 * 有关帖子里的，时间获取方法
	 */
	public List<Offers> getTime() {

		Connection conn = DBConnection.getConn();
		String sql = "SELECT offers.post_time,TO_DAYS(offers.end_time)-TO_DAYS(NOW()) time FROM webpk.offers WHERE trade=0";
		List<Offers> olist3 = new ArrayList<Offers>();
		try {
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(sql);

			Offers pro = null;
			while (rs.next()) {
				pro = new Offers();
				pro.setPost_time(rs.getDate("post_time"));
				pro.setTime(rs.getInt("time"));
				olist3.add(pro);
			}

		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			DBConnection.closeConn();
		}
		
		return olist3;
	}

	public List<TitleLei> chaCha(int productsid) {
		List<TitleLei> list = new ArrayList<TitleLei>();
		List<TitleLei> tl = new ArrayList<TitleLei>();
		List<TitleLei> lt = new ArrayList<TitleLei>();
		Connection conn = DBConnection.getConn();
		PreparedStatement pstat = null;
		ResultSet rs = null;
		String sql1 = "select * from webpk.offers o where o.productsid = ?";
		String sql2 = "select users.credit from webpk.users where users.email = ?";
		String sql3 = "select * from webpk.products where products.productsid = ?";
		try {
			pstat = conn.prepareStatement(sql1);
			pstat.setInt(1, productsid);
			rs = pstat.executeQuery();
			while (rs.next()) {
				TitleLei of = new TitleLei();

				of.setProductsid(productsid);
				of.setBaoJiaTime(rs.getDate("post_time"));
				of.setShuLiang(rs.getInt("amount"));
				of.setEmail(rs.getString("email"));
				of.setTrade(rs.getInt("trade"));
				list.add(of);

			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		Iterator<TitleLei> it = list.iterator();
		
		while (it.hasNext()) {
			TitleLei ttt = it.next();
			TitleLei tll = new TitleLei();
			try {
				pstat = conn.prepareStatement(sql2);
				pstat.setString(1, ttt.getEmail());
				rs = pstat.executeQuery();
				if (rs.next()) {
					tll.setEmail(ttt.getEmail());
					tll.setProductsid(productsid);
					tll.setShuLiang(ttt.getShuLiang());
					tll.setBaoJiaTime(ttt.getBaoJiaTime());
					tll.setXinyu(rs.getInt("credit"));
					tll.setTrade(ttt.getTrade());

					tl.add(tll);
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		Iterator<TitleLei> itt = tl.iterator();
		while (itt.hasNext()) {
			TitleLei titl = itt.next();
			TitleLei tll = new TitleLei();
			try {
				pstat = conn.prepareStatement(sql3);
				pstat.setInt(1, titl.getProductsid());
				rs = pstat.executeQuery();
				if (rs.next()) {
					tll.setChanPinName(rs.getString("title"));
					tll.setProductsid(productsid);
					tll.setEmail(titl.getEmail());
					tll.setShuLiang(titl.getShuLiang());
					tll.setBaoJiaTime(titl.getBaoJiaTime());
					tll.setXinyu(titl.getXinyu());
					tll.setTrade(titl.getTrade());
					lt.add(tll);
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}

		}
		DBConnection.closeConn();
		return lt;

	}

	public boolean addOffer(Offers offer) {
		boolean b = false;
		Connection conn = DBConnection.getConn();
		PreparedStatement pstat = null;
		Date date = null;
		Date enddate = null;
		try {
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt
					.executeQuery("SELECT NOW(),INTERVAL 30 DAY + NOW()");
			while (rs.next()) {
				date = rs.getDate("NOW()");
				enddate = rs.getDate("INTERVAL 30 DAY + NOW()");
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		Timestamp t = new Timestamp(date.getTime());
		Timestamp tt = new Timestamp(enddate.getTime());

		String sql = "insert into webpk.offers value(null,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
		try {
			pstat = conn.prepareStatement(sql);
			pstat.setInt(1, offer.getProductsid());
			pstat.setString(2, offer.getContent());
			pstat.setTimestamp(3, t);
			pstat.setTimestamp(4, tt);
			pstat.setString(5, offer.getEmail());
			pstat.setInt(6, offer.getAmount());
			pstat.setString(7, offer.getDeliver());
			pstat.setString(8, offer.getPayment());
			pstat.setDouble(9, offer.getPrice());
			pstat.setInt(10, offer.getHits());
			pstat.setString(11, offer.getRemark());
			pstat.setInt(12, offer.getTrade());
			pstat.setString(13, offer.getTitle());
			pstat.setString(14, offer.getState());
			pstat.setString(15, offer.getPhoto());
			pstat.executeUpdate();
			System.out.println("恭喜你 ----- 发帖子成功啦!");
			b = true;
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			DBConnection.closeConn();
		}
		
		return b;
	}

	public List<Offers> getElements(String productname) {

		Connection conn = DBConnection.getConn();
		PreparedStatement pstmt = null;

		String sql1 = "SELECT sum(o.amount) inamount,p.title,p.productsid FROM webpk.offers o inner join webpk.products p WHERE o.productsid = p.productsid and p.title = ? AND o.trade=0 GROUP BY o.productsid";
		String sql2 = "SELECT sum(o.amount) outamount FROM webpk.offers o inner join webpk.products p WHERE o.productsid = p.productsid AND P.title = ? AND o.trade=1  GROUP BY o.productsid";
		String sql3 = "SELECT o.post_time,o.end_time,o.hits,TO_DAYS(o.end_time)-TO_DAYS(NOW()) time FROM webpk.offers o inner join webpk.products p WHERE o.productsid = p.productsid and p.title=? AND o.trade=0";

		List<Offers> olist = new ArrayList<Offers>();

		try {

			pstmt = conn.prepareStatement(sql1);
			pstmt.setString(1, productname);
			ResultSet rs = pstmt.executeQuery();

			Offers of = null;
			if (rs.next()) {
				of = new Offers();
				of.setInamount(rs.getInt("inamount"));
				of.setTitle(rs.getString("title"));
				of.setProductsid(rs.getInt("productsid"));
				olist.add(of);
			}

			pstmt = conn.prepareStatement(sql2);
			pstmt.setString(1, productname);
			ResultSet rst = pstmt.executeQuery();

			Offers offer = null;
			if (rst.next()) {
				offer = new Offers();
				offer.setOutamount(rst.getInt("outamount"));
				olist.add(offer);
			} else {
				offer = new Offers();
				offer.setOutamount(0);
				olist.add(offer);
			}

			pstmt = conn.prepareStatement(sql3);
			pstmt.setString(1, productname);
			ResultSet rss = pstmt.executeQuery();

			Offers off = null;
			if (rss.next()) {
				off = new Offers();
				off.setPost_time(rss.getDate("post_time"));
				off.setEnd_time(rss.getDate("end_time"));
				off.setHits(rss.getInt("hits"));
				off.setTime(rss.getInt("time"));
				olist.add(off);
			}

			System.out.println(olist.size());

		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			DBConnection.closeConn();
		}
		
		return olist;

	}

	public void changeHits(String name) {
		Connection conn = DBConnection.getConn();
		PreparedStatement pstmt = null;
		String sql = "UPDATE webpk.offers SET offers.hits = offers.hits + 1 WHERE offers.productsid=(SELECT productsid FROM webpk.products WHERE products.title=?)";

		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, name);
			int count = pstmt.executeUpdate();
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		DBConnection.closeConn();
	}

	public List<Offers> getOffersMessage(String name) {
		Connection conn = DBConnection.getConn();
		Statement stmt = null;
		List<Offers> olist = new ArrayList<Offers>();
		Offers p = null;
		String sql = "SELECT * FROM webpk.offers";
		try {
			stmt = conn.createStatement();

			ResultSet rs = stmt.executeQuery(sql);

			while (rs.next()) {
				p = new Offers();
				p.setOffersid(rs.getInt("offersid"));
				p.setProductsid(rs.getInt("productsid"));
				p.setContent(rs.getString("content"));
				p.setPost_time(rs.getDate("post_time"));
				p.setEnd_time(rs.getDate("end_time"));
				p.setEmail(rs.getString("email"));
				p.setAmount(rs.getInt("amount"));
				p.setDeliver(rs.getString("deliver"));
				p.setPayment(rs.getString("payment"));
				p.setPrice(rs.getInt("price"));
				p.setHits(rs.getInt("hits"));
				p.setRemark(rs.getString("remark"));
				p.setTrade(rs.getInt("trade"));
				p.setTitle(rs.getString("title"));
				olist.add(p);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBConnection.closeConn();
		}
		return olist;
	}

	/**
	 * 获得表中数据记录的个数
	 * 
	 * @return totalRecord 表中的总记录数
	 */
	public static int getRowNumber() {
		Connection conn = DBConnection.getConn();
		int totalRecord = 0;
		try {
			Statement stmt = conn.createStatement();
			String tsql = "SELECT count(*) FROM webpk.offers";
			ResultSet rs = stmt.executeQuery(tsql);
			rs.next();
			totalRecord = rs.getInt(1);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (conn != null) {
				try {
					DBConnection.closeConn();
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
		}

		return totalRecord;
	}

	/**
	 * 获得总的页数
	 * 
	 * @param pageSize
	 *            每页显示的条数
	 * @return 返回总页数
	 */
	public static int getTotalPage(int pageSize) {
		int totalPage = 1;
		int tmpPage = 0;
		int rowNum = getRowNumber();
		tmpPage = rowNum % pageSize;
		if (tmpPage == 0) {
			totalPage = rowNum / pageSize;
		} else {
			totalPage = (int) (Math.floor(rowNum / pageSize) + 1);
		}
		if (totalPage == 0) {
			totalPage = 1;