eventdao.java

使用eclipse开发的web程序

package com.wrox.tourism.db;

import com.wrox.tourism.entity.Event;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import java.util.Collection;
import java.util.ArrayList;

public class EventDAO {

  private Connection con;


  public EventDAO(Connection con) {
    this.con = con;
  }


  public void create(Event event) throws CreateException {

    PreparedStatement ps = null;

    String sql = "INSERT INTO event " +
        "(user_id,name,description,ticket_price) VALUES (?,?,?,?)";

    try {

      if (con.isClosed()) {
        throw new IllegalStateException("error.unexpected");
      }

      ps = con.prepareStatement(sql);
      ps.setString(1,event.getUserId());
      ps.setString(2,event.getName());
      ps.setString(3,event.getDescription());
      ps.setDouble(4,event.getTicketPrice());

      if (ps.executeUpdate() != 1) {
        throw new CreateException("error.create.event");
      }
    } catch (SQLException e) {
      e.printStackTrace();
      throw new RuntimeException("error.unexpected");
    } finally {
      try {
        if (ps != null)
          ps.close();
      } catch (SQLException e) {
        e.printStackTrace();
        throw new RuntimeException("error.unexpected");
      }
    }

  }


  public void update(Event event) {

    PreparedStatement ps = null;

    String sql = "UPDATE event SET name = ?,description = ?," +
        "ticket_price = ? WHERE event_id = ?";

    try {

      if (con.isClosed()) {
        throw new IllegalStateException("error.unexpected");
      }

      System.out.println("event id:" + event.getEventId());

      ps = con.prepareStatement(sql);
      ps.setString(1,event.getName());
      ps.setString(2,event.getDescription());
      ps.setDouble(3,event.getTicketPrice());
      ps.setInt(4,event.getEventId());

      if (ps.executeUpdate() != 1) {
        throw new NoSuchEntityException(
          "error.removed.event");
      }

    } catch (SQLException e) {
      e.printStackTrace();
      throw new RuntimeException("error.unexpected");
    } finally {
      try {
        if (ps != null)
          ps.close();
      } catch (SQLException e) {
        e.printStackTrace();
        throw new RuntimeException("error.unexpected");
      }
    }

  }


  public void remove(int eventId) {

    PreparedStatement ps = null;

    String sql = "DELETE FROM event WHERE event_id = ?";

    try {

      if (con.isClosed()) {
        throw new IllegalStateException("error.unexpected");
      }

      ps = con.prepareStatement(sql);
      ps.setInt(1,eventId);

      if (ps.executeUpdate() != 1) {
        throw new NoSuchEntityException("error.removed.event");
      }

    } catch (SQLException e) {
      e.printStackTrace();
      throw new RuntimeException("error.unexpected");
    } finally {
      try {
        if (ps != null)
          ps.close();
      } catch (SQLException e) {
        e.printStackTrace();
        throw new RuntimeException("error.unexpected");
      }
    }

  }


  public Event findByPrimaryKey(int eventId)
    throws FinderException {

    PreparedStatement ps = null;
    ResultSet rs = null;
    Event event = null;

    String sql = "SELECT * from event WHERE event_id = ? ";

    try {

      if (con.isClosed()) {
        throw new IllegalStateException("error.unexpected");
      }

      ps = con.prepareStatement(sql);
      ps.setInt(1,eventId);
      rs = ps.executeQuery();

      if (rs.next()) {

        event = new Event();
        event.setEventId(rs.getInt(1));
        event.setUserId(rs.getString(2));
        event.setName(rs.getString(3));
        event.setDescription(rs.getString(4));
        event.setTicketPrice(rs.getDouble(5));

        return event;

      } else {
        throw new ObjectNotFoundException("error.removed.event");
      }

    } catch (SQLException e) {
      e.printStackTrace();
      throw new RuntimeException("error.unexpected");
    } finally {
      try {
        if (ps != null)
          ps.close();
        if (rs != null)
          rs.close();
      } catch (SQLException e) {
        e.printStackTrace();
        throw new RuntimeException("error.unexpected");
      }
    }

  }


  public Collection findAll(String userId) {

    PreparedStatement ps = null;
    ResultSet rs = null;
    ArrayList list = new ArrayList();

    String sql = "SELECT * from event WHERE user_id = ? ";

    try {

      if (con.isClosed()) {
        throw new IllegalStateException("error.unexpected");
      }

      ps = con.prepareStatement(sql);
      ps.setString(1,userId);
      rs = ps.executeQuery();

      while(rs.next()) {

        Event event = new Event();
        event.setEventId(rs.getInt(1));
        event.setUserId(rs.getString(2));
        event.setName(rs.getString(3));
        event.setDescription(rs.getString(4));
        event.setTicketPrice(rs.getDouble(5));

        list.add(event);

      }

      return list;

    } catch (SQLException e) {
      e.printStackTrace();
      throw new RuntimeException("error.unexpected");
    } finally {
      try {
        if (ps != null)
          ps.close();
        if (rs != null)
          rs.close();
      } catch (SQLException e) {
        e.printStackTrace();
        throw new RuntimeException("error.unexpected");
      }
    }

  }

}