indexdao.java

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

package dao;

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

import conn.DBConnection;
import domain.*;

public class IndexDao {
	public List<Index> getCredit() {
		Connection conn = DBConnection.getConn();
		PreparedStatement pstat = null;
		ResultSet rs = null;

		String sql1 = "SELECT products.title, products.productsid FROM webpk.products,webpk.offers WHERE products.productsid=offers.productsid GROUP BY products.productsid";
		String sql2 = "SELECT sum(o.amount) inamount FROM webpk.offers o,webpk.products p WHERE o.trade=0 AND o.productsid=? AND o.productsid = p.productsid ORDER BY p.productsid";
		String sql3 = "SELECT sum(o.amount) outamount FROM webpk.offers o,webpk.products p WHERE o.trade=1 AND o.productsid=? AND o.productsid = p.productsid ORDER BY p.productsid";
		String sql4 = "SELECT offers.post_time,offers.email,TO_DAYS(offers.end_time)-TO_DAYS(NOW()) time FROM webpk.offers WHERE trade=0 AND offers.productsid=?";
		String sql5 = "SELECT users.credit FROM webpk.users,webpk.offers WHERE users.email=?";

		List<Index> plist = new ArrayList<Index>();
		List<Index> olist = new ArrayList<Index>();
		List<Index> oflist = new ArrayList<Index>();
		List<Index> offlist = new ArrayList<Index>();
		List<Index> ulist = new ArrayList<Index>();

		try {
			Statement stmt = conn.createStatement();
			rs = stmt.executeQuery(sql1);
			Index ine = null;
			while (rs.next()) {
				ine = new Index();
				ine.setTitle(rs.getString("title"));
				ine.setProductsid(rs.getInt("productsid"));
				plist.add(ine);
			}

			Iterator<Index> it = plist.iterator();
			while (it.hasNext()) {
				Index ins = it.next();
				pstat = conn.prepareStatement(sql2);
				pstat.setInt(1, ins.getProductsid());
				rs = pstat.executeQuery();
				if (rs.next()) {
					Index oin = new Index();
					oin.setTitle(ins.getTitle());
					oin.setProductsid(ins.getProductsid());
					oin.setInamount(rs.getInt("inamount"));
					olist.add(oin);
				}
			}

			Iterator<Index> its = olist.iterator();
			while (its.hasNext()) {
				Index ins = its.next();
				pstat = conn.prepareStatement(sql3);
				pstat.setInt(1, ins.getProductsid());
				rs = pstat.executeQuery();
				if (rs.next()) {
					Index oin = new Index();
					oin.setTitle(ins.getTitle());
					oin.setProductsid(ins.getProductsid());
					oin.setInamount(ins.getInamount());
					oin.setOutamount(rs.getInt("outamount"));
					oflist.add(oin);
				}
			}

			Iterator<Index> ist = oflist.iterator();
			while (ist.hasNext()) {
				Index ins = ist.next();
				pstat = conn.prepareStatement(sql4);
				pstat.setInt(1, ins.getProductsid());
				rs = pstat.executeQuery();
				if (rs.next()) {
					Index oin = new Index();
					oin.setTitle(ins.getTitle());
					oin.setProductsid(ins.getProductsid());
					oin.setInamount(ins.getInamount());
					oin.setOutamount(ins.getOutamount());
					oin.setTime(rs.getInt("time"));
					oin.setPost_time(rs.getDate("post_time"));
					oin.setEmail(rs.getString("email"));
					offlist.add(oin);
				}
			}
			System.out.println(offlist.size());
			Iterator<Index> ito = offlist.iterator();
			while (ito.hasNext()) {
				Index ins = ito.next();
				pstat = conn.prepareStatement(sql5);
				pstat.setString(1, ins.getEmail());
				rs = pstat.executeQuery();
				if (rs.next()) {
					Index oin = new Index();
					oin.setTitle(ins.getTitle());
					oin.setProductsid(ins.getProductsid());
					oin.setInamount(ins.getInamount());
					oin.setOutamount(ins.getOutamount());
					oin.setTime(ins.getTime());
					oin.setPost_time(ins.getPost_time());
					oin.setEmail(ins.getEmail());
					oin.setCredit(rs.getInt("credit"));
					ulist.add(oin);

				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}

		finally {
			DBConnection.closeConn();
		}
		return ulist;
	}

	public List<Index> getAllSeller() {

		Connection conn = DBConnection.getConn();
		Statement stmt = null;
		ResultSet rs = null;

		List<Index> ilist = new ArrayList<Index>();
		String sql1 = "select  p.title,o.amount outamount,o.post_time,TO_DAYS(o.end_time)-TO_DAYS(NOW()) time,u.credit  from webpk.offers o,webpk.products p,webpk.users u  where o.trade=1 and p.productsid=o.productsid and o.email=u.email";

		try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql1);
			Index oin = null;
			while (rs.next()) {
				oin = new Index();
				oin.setTitle(rs.getString("title"));
				oin.setOutamount(rs.getInt("outamount"));				
				oin.setTime(rs.getInt("time"));
				oin.setPost_time(rs.getDate("post_time"));				
				oin.setCredit(rs.getInt("credit"));
				ilist.add(oin);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBConnection.closeConn();
		}
		return ilist;
	}
	
	public List<Index> getSinceritySeller() {

		Connection conn = DBConnection.getConn();
		Statement stmt = null;
		ResultSet rs = null;

		List<Index> ilist = new ArrayList<Index>();
		String sql1 = "select  p.title,o.amount outamount,o.post_time,TO_DAYS(o.end_time)-TO_DAYS(NOW()) time,u.credit  from webpk.offers o,webpk.products p,webpk.users u  where o.trade=1 and u.credit>=2000 and p.productsid=o.productsid and o.email=u.email";

		try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql1);
			Index oin = null;
			while (rs.next()) {
				oin = new Index();
				oin.setTitle(rs.getString("title"));
				oin.setOutamount(rs.getInt("outamount"));				
				oin.setTime(rs.getInt("time"));
				oin.setPost_time(rs.getDate("post_time"));				
				oin.setCredit(rs.getInt("credit"));
				ilist.add(oin);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBConnection.closeConn();
		}
		return ilist;
	}
	
	public List<Index> getFinishTrade() {

		Connection conn = DBConnection.getConn();
		Statement stmt = null;
		ResultSet rs = null;

		List<Index> ilist = new ArrayList<Index>();
		String sql1 = "SELECT p.title,o.amount outamount,o.post_time,TO_DAYS(o.end_time)-TO_DAYS(NOW()) time,u.credit FROM webpk.offers o,webpk.products p,webpk.users u  WHERE o.trade=1 and o.state='交易成功' and p.productsid=o.productsid and o.email=u.email";

		try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql1);
			Index oin = null;
			while (rs.next()) {
				oin = new Index();
				oin.setTitle(rs.getString("title"));
				oin.setOutamount(rs.getInt("outamount"));				
				oin.setTime(rs.getInt("time"));
				oin.setPost_time(rs.getDate("post_time"));				
				oin.setCredit(rs.getInt("credit"));
				ilist.add(oin);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBConnection.closeConn();
		}
		return ilist;
	}
	
	public List<Index> getQiChe() {

		Connection conn = DBConnection.getConn();
		Statement stmt = null;
		ResultSet rs = null;

		List<Index> ilist = new ArrayList<Index>();
		String sql1 = "SELECT p.title,o.amount inamount,o.post_time,TO_DAYS(o.end_time)-TO_DAYS(NOW()) time,u.credit FROM webpk.offers o,webpk.products p,webpk.users u  WHERE o.trade=0 and p.title like '%轿车%' and p.productsid=o.productsid and o.email=u.email";

		try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql1);
			Index oin = null;
			while (rs.next()) {
				oin = new Index();
				oin.setTitle(rs.getString("title"));
				oin.setInamount(rs.getInt("Inamount"));				
				oin.setTime(rs.getInt("time"));
				oin.setPost_time(rs.getDate("post_time"));				
				oin.setCredit(rs.getInt("credit"));
				ilist.add(oin);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBConnection.closeConn();
		}
		return ilist;
	}
	
	public List<Index> getBick() {

		Connection conn = DBConnection.getConn();
		Statement stmt = null;
		ResultSet rs = null;

		List<Index> ilist = new ArrayList<Index>();
		String sql1 = "SELECT p.title,o.amount inamount,o.post_time,TO_DAYS(o.end_time)-TO_DAYS(NOW()) time,u.credit FROM webpk.offers o,webpk.products p,webpk.users u  WHERE o.trade=0 and p.title like '%自行车%' and p.productsid=o.productsid and o.email=u.email";

		try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql1);
			Index oin = null;
			while (rs.next()) {
				oin = new Index();
				oin.setTitle(rs.getString("title"));
				oin.setInamount(rs.getInt("Inamount"));				
				oin.setTime(rs.getInt("time"));
				oin.setPost_time(rs.getDate("post_time"));				
				oin.setCredit(rs.getInt("credit"));
				ilist.add(oin);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBConnection.closeConn();
		}
		return ilist;
	}
	
	public List<Index> getBiJiBen() {

		Connection conn = DBConnection.getConn();
		Statement stmt = null;
		ResultSet rs = null;

		List<Index> ilist = new ArrayList<Index>();
		String sql1 = "SELECT p.title,o.amount inamount,o.post_time,TO_DAYS(o.end_time)-TO_DAYS(NOW()) time,u.credit FROM webpk.offers o,webpk.products p,webpk.users u  WHERE o.trade=0 and p.title like '%笔记本电脑%' and p.productsid=o.productsid and o.email=u.email";

		try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql1);
			Index oin = null;
			while (rs.next()) {
				oin = new Index();
				oin.setTitle(rs.getString("title"));
				oin.setInamount(rs.getInt("Inamount"));				
				oin.setTime(rs.getInt("time"));
				oin.setPost_time(rs.getDate("post_time"));				
				oin.setCredit(rs.getInt("credit"));
				ilist.add(oin);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBConnection.closeConn();
		}
		return ilist;
	}
	
	public List<Index> getTaiShi() {

		Connection conn = DBConnection.getConn();
		Statement stmt = null;
		ResultSet rs = null;

		List<Index> ilist = new ArrayList<Index>();
		String sql1 = "SELECT p.title,o.amount inamount,o.post_time,TO_DAYS(o.end_time)-TO_DAYS(NOW()) time,u.credit FROM webpk.offers o,webpk.products p,webpk.users u  WHERE o.trade=0 and p.title like '%台式电脑%' and p.productsid=o.productsid and o.email=u.email";

		try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql1);
			Index oin = null;
			while (rs.next()) {
				oin = new Index();
				oin.setTitle(rs.getString("title"));
				oin.setInamount(rs.getInt("Inamount"));				
				oin.setTime(rs.getInt("time"));
				oin.setPost_time(rs.getDate("post_time"));				
				oin.setCredit(rs.getInt("credit"));
				ilist.add(oin);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBConnection.closeConn();
		}
		return ilist;
	}
	
	public List<Index> getShouJi() {

		Connection conn = DBConnection.getConn();
		Statement stmt = null;
		ResultSet rs = null;

		List<Index> ilist = new ArrayList<Index>();
		String sql1 = "SELECT p.title,o.amount inamount,o.post_time,TO_DAYS(o.end_time)-TO_DAYS(NOW()) time,u.credit FROM webpk.offers o,webpk.products p,webpk.users u  WHERE o.trade=0 and p.title like '%手机%' and p.productsid=o.productsid and o.email=u.email";

		try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql1);
			Index oin = null;
			while (rs.next()) {
				oin = new Index();
				oin.setTitle(rs.getString("title"));
				oin.setInamount(rs.getInt("Inamount"));				
				oin.setTime(rs.getInt("time"));
				oin.setPost_time(rs.getDate("post_time"));				
				oin.setCredit(rs.getInt("credit"));
				ilist.add(oin);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBConnection.closeConn();
		}
		return ilist;
	}
	
	public List<Index> getMP() {

		Connection conn = DBConnection.getConn();
		Statement stmt = null;
		ResultSet rs = null;

		List<Index> ilist = new ArrayList<Index>();
		String sql1 = "SELECT p.title,o.amount inamount,o.post_time,TO_DAYS(o.end_time)-TO_DAYS(NOW()) time,u.credit FROM webpk.offers o,webpk.products p,webpk.users u  WHERE o.trade=0 and p.title like '%MP%' and p.productsid=o.productsid and o.email=u.email";

		try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql1);
			Index oin = null;
			while (rs.next()) {
				oin = new Index();
				oin.setTitle(rs.getString("title"));
				oin.setInamount(rs.getInt("Inamount"));				
				oin.setTime(rs.getInt("time"));
				oin.setPost_time(rs.getDate("post_time"));				
				oin.setCredit(rs.getInt("credit"));
				ilist.add(oin);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBConnection.closeConn();
		}
		return ilist;
	}
	
	public List<Index> getPSP() {

		Connection conn = DBConnection.getConn();
		Statement stmt = null;
		ResultSet rs = null;

		List<Index> ilist = new ArrayList<Index>();
		String sql1 = "SELECT p.title,o.amount inamount,o.post_time,TO_DAYS(o.end_time)-TO_DAYS(NOW()) time,u.credit FROM webpk.offers o,webpk.products p,webpk.users u  WHERE o.trade=0 and p.title like '%PSP%'||'%PS2%'||'%iPOD%' and p.productsid=o.productsid and o.email=u.email";
		
		

		try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql1);
			Index oin = null;
			while (rs.next()) {
				oin = new Index();
				oin.setTitle(rs.getString("title"));
				oin.setInamount(rs.getInt("Inamount"));				
				oin.setTime(rs.getInt("time"));
				oin.setPost_time(rs.getDate("post_time"));				
				oin.setCredit(rs.getInt("credit"));
				ilist.add(oin);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBConnection.closeConn();
		}
		return ilist;
	}
	
	public List<Index> getPBTV() {

		Connection conn = DBConnection.getConn();
		Statement stmt = null;
		ResultSet rs = null;

		List<Index> ilist = new ArrayList<Index>();
		String sql1 = "SELECT p.title,o.amount inamount,o.post_time,TO_DAYS(o.end_time)-TO_DAYS(NOW()) time,u.credit FROM webpk.offers o,webpk.products p,webpk.users u  WHERE o.trade=0 and p.title like '%平板电视%' and p.productsid=o.productsid and o.email=u.email";
		
		

		try {