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

import dao.model.Contrat;
import dao.model.Spot;

public class ContratDao extends BaseDao {
	private static final String SQL_INSERT_CONTRACT = "INSERT INTO CONTRACT(ID,ID_SPOT,COUT,DATE,NOMCLIENT) values(?,?,?,?,?)";

	private static final String SQL_SELECT_CONTRAT ="" +
			"select * from contract where id  like ? ";
	private static final String SQL_SELECT_SPOT ="" +
			"SELECT * FROM PUB_spot WHERE ID =?";
	public List<Contrat> rechercher(String like)throws DAOException, SQLException{
		List<Contrat> list = new ArrayList<Contrat>();
		PreparedStatement ps;
		try{
			ps=getConnection().prepareStatement(SQL_SELECT_CONTRAT);
			ps.setString(1, "%"+like+"%");
			ResultSet rs =ps.executeQuery();
			while(rs.next())
			{
				Contrat c = new Contrat();
				c.setId(rs.getString(1));
				c.setCout(rs.getBigDecimal(3));
				c.setDate(rs.getDate(4));
				c.setNomClient(rs.getString(5));
				int id =rs.getInt(2);
				c.setSpot(DaoFactory.getSpotDao().getSpotById(id));
				list.add(c);
				
			}
		}catch (SQLException e) {
			e.printStackTrace();
			throw new DAOException();
		
		}
		return list;
	}
	public void insert(Contrat c) throws DAOException {
		Connection con = getConnection();
		PreparedStatement ps;
		try {
			ps = con.prepareStatement(SQL_INSERT_CONTRACT);
			ps.setString(1, c.getId());
			ps.setInt(2, c.getSpot().getId());
			ps.setBigDecimal(3, c.getCout());
			ps.setDate(4, c.getDate());
			ps.setString(5, c.getNomClient());
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
			throw new DAOException();
		}

	}
	

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

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

}