spotdao.java

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

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.List;
import java.util.Map;

import org.apache.commons.beanutils.BeanUtils;

import util.ObjectRowMapper;
import dao.model.Passage;
import dao.model.Spot;

public class SpotDao extends BaseDao {
	private static final String SQL_FIND_SPOTS_BY_PLAGEID = "SELECT * FROM PUB_SPOT WHERE ID IN(SELECT ID_SPOT FROM SPOT_PLAGE WHERE ID_PLAGE=?)";
	private static final String SQL_FIND_SPOT_BY_ID = "SELECT * FROM PUB_SPOT WHERE ID =?";
	private static final String SQL_INSERT_SPOT_PLAGE = "INSERT INTO SPOT_PLAGE(ID_PLAGE,ID_SPOT,DEBUT) VALUES(?,?,?)";
	private final static String SQL_INSERT_SPOT = "INSERT INTO PUB_SPOT(ID,TITRE,THEME,DUREE) VALUES(?,?,?,?)";
	private static final String SQL_INSERT_PASSAGE = "INSERT INTO PASSAGE(ID,ID_SPOT,CATEGORIE,NOMBRE) VALUES(NEXT VALUE FOR SEQ_PASSAGE,?,?,?)";
	
	public Spot getSpotById(int id) {
		PreparedStatement ps = null;
		try {
			ps = getConnection().prepareStatement(SQL_FIND_SPOT_BY_ID);
			ps.setInt(1, id);
			ResultSet rs = ps.executeQuery();
			rs.next();
			Map map = new ObjectRowMapper().mapRow(rs, 4);
			Spot s = new Spot();
			BeanUtils.populate(s, map);
			return s;
		} 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<Spot> getSpotsByPlageId(int id) {
		List<Spot> list = new ArrayList<Spot>();
		PreparedStatement ps = null;
		try {
			ps = getConnection().prepareStatement(SQL_FIND_SPOTS_BY_PLAGEID);
			ps.setInt(1, id);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				Map map = new ObjectRowMapper().mapRow(rs, 4);
				Spot s = new Spot();
				BeanUtils.populate(s, map);
				list.add(s);
			}
			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 attachSpotToPlage(int spotId, int plageId,Timestamp t) throws DAOException {
		PreparedStatement ps;
		try {
			ps = getConnection().prepareStatement(SQL_INSERT_SPOT_PLAGE);
			ps.setInt(1, plageId);
			ps.setInt(2, spotId);
			ps.setTimestamp(3, t);
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
			throw new DAOException();
		}
	}

	public int generateIdForSpot() {
		try {
			Statement statement = getConnection().createStatement();
			ResultSet rs = statement
					.executeQuery("select next value for seq_pub_spot from dual");
			rs.next();
			return rs.getInt(1);

		} catch (Exception e) {
			e.printStackTrace();
			return -1;
		}
	}

	public void insert(Spot s) {
		Connection con=getConnection();
		PreparedStatement ps;
		try {
			ps = con.prepareStatement(SQL_INSERT_SPOT);
			ps.setInt(1, s.getId());
			ps.setString(2, s.getTitre());
			ps.setString(3, s.getTheme());
			ps.setInt(4, s.getDuree());
			ps.executeUpdate();
			ps = con.prepareStatement(SQL_INSERT_PASSAGE);
			for (Passage p : s.getLesPassages()) {
				ps = con.prepareStatement(SQL_INSERT_PASSAGE);
				ps.setInt(1, s.getId());
				ps.setString(2, p.getCategorie());
				ps.setInt(3, p.getNombre());
				ps.executeUpdate();
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
}