plagedao.java

用xml+swing+jdbc(hsqldb)写的电视广告管理软件 客户定义好广告的具体信息

package dao;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.beanutils.BeanUtils;

import util.ObjectRowMapper;
import dao.model.Plage;

public class PlageDao extends BaseDao {
	private static final String SQL_INSERT_PLAGE = "INSERT INTO PUB_PLAGE(ID,DEBUT,CATEGORIE,DUREE,TEMPREST) VALUES(next value for seq_pub_plage,?,?,?,?)";
	private static final String SQL_FIND_PLAGES_BY_JOUR = "SELECT * FROM PUB_PLAGE WHERE dayofyear(debut)=dayofyear(?)";
	private static final String SQL_FIND_PLAGES_BY_CRITERE = "SELECT * FROM PUB_PLAGE WHERE CATEGORIE=? AND dayofyear(debut)>dayofyear(?)";
	private static final String SQL_UPDATE_PLAGE = "UPDATE PUB_PLAGE SET TEMPREST=? where id=?";

	private static final String SQL_MAX_DATE = "SELECT MAX(debut) from pub_plage;";

	private static final String SQL_SELECT_PLAGE = ""
			+ "select id,debut,duree,categorie from pub_plage where id =?";
	private static final String SQL_SELECT_PLAGEBYSPOTID = "select * from "
			+ "spot_plage where id_spot=?";
	private static final String SQL_FIND_PLAGES_SPEC = "SELECT * FROM PUB_PLAGE WHERE temprest>? and dayofyear(debut)>=dayofyear(?) AND dayofyear(debut)<=dayofyear(?)";

	public void insert(Plage p) {
		Connection con = getConnection();
		try {
			PreparedStatement ps = con.prepareStatement(SQL_INSERT_PLAGE);
			ps.setTimestamp(1, p.getDebut());
			ps.setString(2, p.getCategorie());
			ps.setInt(3, p.getDuree());
			ps.setInt(4, p.getDuree());
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}

	}

	public List<Plage> getListPlageByJour(Date date) {
		List<Plage> list = new ArrayList<Plage>();
		PreparedStatement ps = null;
		try {
			ps = getConnection().prepareStatement(SQL_FIND_PLAGES_BY_JOUR);
			ps.setDate(1, date);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				Map map = new ObjectRowMapper().mapRow(rs, 5);
				Plage p = new Plage();
				BeanUtils.populate(p, map);
				list.add(p);
			}
			return list;
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		} finally {
			try {
				ps.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}

	public List<Plage> getPlagesByCritere(String categorie, Date miniDate) {
		List<Plage> list = new ArrayList<Plage>();
		PreparedStatement ps = null;
		try {
			ps = getConnection().prepareStatement(SQL_FIND_PLAGES_BY_CRITERE);
			ps.setString(1, categorie);
			ps.setDate(2, miniDate);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				Map map = new ObjectRowMapper().mapRow(rs, 5);
				Plage p = new Plage();
				BeanUtils.populate(p, map);
				list.add(p);
			}
			return list;
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		} finally {
			try {
				ps.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}

	}

	public List<Plage> getPlagesParticulier(int duree, Date start, Date end) {
		List<Plage> list = new ArrayList<Plage>();
		PreparedStatement ps = null;
		try {
			ps = getConnection().prepareStatement(SQL_FIND_PLAGES_SPEC);
			ps.setInt(1, duree);
			ps.setDate(2, start);
			ps.setDate(3, end);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				Map map = new ObjectRowMapper().mapRow(rs, 5);
				Plage p = new Plage();
				BeanUtils.populate(p, map);
				list.add(p);
			}
			return list;
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		} finally {
			try {
				ps.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}

	}

	public void update(Plage p) {
		PreparedStatement ps;
		try {
			ps = getConnection().prepareStatement(SQL_UPDATE_PLAGE);
			ps.setInt(1, p.getTemprest());
			ps.setInt(2, p.getId());
			ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

	public Map<Plage,Timestamp> rechercher(int idSpot) {
		Connection con = getConnection();
		Map<Plage,Timestamp> map = new HashMap<Plage,Timestamp>();
		try {
			PreparedStatement ps = con.prepareStatement(SQL_SELECT_PLAGEBYSPOTID);
			ps.setInt(1, idSpot);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				int idPlage=rs.getInt(1);
				Plage p=findById(idPlage);
				
				map.put(p,rs.getTimestamp(3));
			}
		} catch (SQLException d) {
			d.printStackTrace();
		}
		return map;

	}

	public Plage findById(int idPlage) {
		Connection con = getConnection();
		Plage p = new Plage();
		try {
			PreparedStatement ps = con.prepareStatement(SQL_SELECT_PLAGE);
			ps.setInt(1, idPlage);
			ResultSet rs = ps.executeQuery();
			rs.next();
			p.setId(rs.getInt(1));
			p.setDebut(rs.getTimestamp(2));
			p.setDuree(rs.getInt(3));
			p.setCategorie(rs.getString(4));

		} catch (SQLException e) {
			e.printStackTrace();
		}
		return p;
	}

	public Timestamp getMaxDate() {
		Connection con = getConnection();
		try {
			PreparedStatement ps = con.prepareStatement(SQL_MAX_DATE);
			ResultSet rs = ps.executeQuery();
			rs.next();
			return rs.getTimestamp(1);
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}

	}

}