tpublishdao.java

招标网站

package com.jblack.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.jblack.model.Tpublish;
import com.jblack.tool.DBConnection;


public class TpublishDAO {
	protected DBConnection conn = new DBConnection();
	SimpleDateFormat date = new SimpleDateFormat("yyyy-MM-dd");

	public List<Tpublish> queryAll() {
		List<Tpublish> list = new ArrayList<Tpublish>();
		ResultSet rs = conn.executeQuery("select * from Tpublish");
		try {
			while (rs.next()) {
				list.add(new Tpublish(rs.getInt("TP_id"), rs.getInt("TC_id"),
						rs.getInt("TP_winner"),rs.getString("TP_name"), 
						rs.getDouble("TP_initPrice"), rs.getDouble("TP_modPrice"),
						rs.getString("TP_description"), rs.getDate("TP_pubDate"),
						rs.getDate("TP_deadline"),rs.getDate("TP_gameDate"),
						rs.getDate("TP_gameDeadline"),rs.getInt("TP_status")));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			conn.close();
		}
		return list;
	}

	public int saveOrUpdate(Tpublish s) {	
		int num = conn
			.executeUpdate("insert into Tpublish (TC_id,TP_name," +
						"TP_initPrice,TP_modPrice,TP_description,TP_pubDate," +
						"TP_deadline, TP_gameDate,TP_gameDeadline,TP_status ) values ("
						+ s.getTcid()								
						+ ", '"
						+ s.getTpname()
						+ "', "
						+ s.getTpinitPrice()
						+ ", "
						+ s.getTpmodPrice()
						+ ", '"
						+ s.getTpdescription()
						+ "', date('"
						+ date.format(s.getTppubDate())
						+ "'), date('"
						+ date.format(s.getTpdeadline())
						+ "'), date('"
						+ date.format(s.getTpgameDate())
						+ "'), date('"
						+ date.format(s.getTpgameDeadline())
						+ "'), " 
						+ s.getTpstatus() + ")");
			return num;
			
		
	}
	
	public int Update(int tpwinner, int tpid) {
		int num = 0;
		
		num = conn.executeUpdate("update tpublish set TP_winner="
						+ tpwinner+ ", TP_status=" + Tpublish.DONE + " where TP_id=" + tpid );	
		conn.close();
		return num;
	}


	public int save(Tpublish s) {
		String sql = "insert into tpublish "+ 
				"(TC_id,TP_name,TP_initPrice,TP_modPrice," +
				" TP_description,TP_pubDate,TP_deadline," +
				" TP_gameDate,TP_gameDeadline,TP_status ) values ("
			
			+ s.getTcid()
			+ ", '"
			+ s.getTpname()
			+ "', "
			+ s.getTpinitPrice()
			+ ", "
			+ s.getTpmodPrice()
			+ ", '"
			+ s.getTpdescription()
			+ "', date('"
			+ date.format(s.getTppubDate())
			+ "'), date('"
			+ date.format(s.getTpdeadline())
			+ "'), date('"
			+ date.format(s.getTpgameDate())
			+ "'), date('"
			+ date.format(s.getTpgameDeadline())
			+ "'), "
			+ s.getTpstatus() + ")";

		int num = conn.insertAndGetPKKey(sql);
		conn.close();
		return num;
	}

	public Tpublish get(int no) {
		Tpublish s = null;
		ResultSet rs = conn
				.executeQuery("select * from tpublish where TP_id=" + no);
		try {
			while (rs.next()) {
				s = new Tpublish(rs.getInt("TP_id"), rs.getInt("TC_id"),
						rs.getInt("TP_winner"),rs.getString("TP_name"), 
						rs.getDouble("TP_initPrice"), rs.getDouble("TP_modPrice"),
						rs.getString("TP_description"), rs.getDate("TP_pubDate"),
						rs.getDate("TP_deadline"),rs.getDate("TP_gameDate"),
						rs.getDate("TP_gameDeadline"),rs.getInt("TP_status"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			conn.close();
		}
		return s;
	}
	
	public Tpublish  gettp(int no) {
		Tpublish s = null;
		ResultSet rs = conn
				.executeQuery("select * from tpublish where TC_id=" + no);
		try {
			while (rs.next()) {
				s = new Tpublish(rs.getInt("TP_id"), rs.getInt("TC_id"),
						rs.getInt("TP_winner"),rs.getString("TP_name"), 
						rs.getDouble("TP_initPrice"), rs.getDouble("TP_modPrice"),
						rs.getString("TP_description"), rs.getDate("TP_pubDate"),
						rs.getDate("TP_deadline"),rs.getDate("TP_gameDate"),
						rs.getDate("TP_gameDeadline"),rs.getInt("TP_status"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			conn.close();
		}
		return s;
	}
	
	public List<Tpublish> queryPublish(String sql) {
		List<Tpublish> list = new ArrayList<Tpublish>();
		ResultSet rs = conn.executeQuery(sql);
		try {
			while (rs.next()) {
				list.add(new Tpublish(rs.getInt("TP_id"), rs.getInt("TC_id"),
						rs.getInt("TP_winner"),rs.getString("TP_name"), 
						rs.getDouble("TP_initPrice"), rs.getDouble("TP_modPrice"),
						rs.getString("TP_description"), rs.getDate("TP_pubDate"),
						rs.getDate("TP_deadline"),rs.getDate("TP_gameDate"),
						rs.getDate("TP_gameDeadline"),rs.getInt("TP_status")));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			conn.close();
		}
		return list;
	}
	
	public List<Tpublish> queryEndDate(Date datesql, int tcidpub) {
		List<Tpublish> list = new ArrayList<Tpublish>();
		ResultSet rs = conn.executeQuery("select * from Tpublish where " +
				"TP_gameDeadline < date('" + date.format(datesql) + "') and TC_id ="+tcidpub);
		try {
			while (rs.next()) {
				list.add(new Tpublish(rs.getInt("TP_id"), rs.getInt("TC_id"),
						rs.getInt("TP_winner"),rs.getString("TP_name"), 
						rs.getDouble("TP_initPrice"), rs.getDouble("TP_modPrice"),
						rs.getString("TP_description"), rs.getDate("TP_pubDate"),
						rs.getDate("TP_deadline"),rs.getDate("TP_gameDate"),
						rs.getDate("TP_gameDeadline"),rs.getInt("TP_status")));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			conn.close();
		}
		return list;
	}

	public void delete(int no) {
		conn.executeUpdate("delete from tpublish where tp_id=" + no);
		conn.close();
	}
}