attractiondao.java

使用eclipse开发的web程序

package com.wrox.tourism.db;

import com.wrox.tourism.entity.Attraction;

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 AttractionDAO {

  private Connection con;


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


  public void create(Attraction attraction) throws CreateException {

    PreparedStatement ps = null;

    String sql = "INSERT INTO attraction VALUES (?,?,?,?,?,?)";

    try {

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

      ps = con.prepareStatement(sql);

      ps.setString(1,attraction.getUserId());
      ps.setString(2,attraction.getPassword());
      ps.setString(3,attraction.getName());
      ps.setString(4,attraction.getDescription());
      ps.setString(5,attraction.getWebSite());
      ps.setString(6,attraction.getAddress());

      if (ps.executeUpdate() != 1) {
        throw new CreateException("error.create.attraction");
      }
    } catch (SQLException e) {
      try {
        findByPrimaryKey(attraction.getUserId());
      } catch (FinderException fe) {
        fe.printStackTrace();
        throw new RuntimeException("error.unexpected");
      }
      throw new DuplicateKeyException(
        "error.duplicate.attraction");
    } finally {
      try {
        if (ps != null)
          ps.close();
      } catch (SQLException e) {
        e.printStackTrace();
        throw new RuntimeException("error.unexpected");
      }
    }

  }


  public void update(Attraction attraction) {

    PreparedStatement ps = null;

    String sql = "UPDATE attraction SET password = ?," +
        "name = ?,description = ?,web_site = ?,address = ? " +
        "WHERE user_id = ?";

    try {

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

      ps = con.prepareStatement(sql);
      ps.setString(1,attraction.getPassword());
      ps.setString(2,attraction.getName());
      ps.setString(3,attraction.getDescription());
      ps.setString(4,attraction.getWebSite());
      ps.setString(5,attraction.getAddress());
      ps.setString(6,attraction.getUserId());

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

    } 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(String userId) {

    PreparedStatement ps = null;

    String sql = "DELETE FROM attraction WHERE user_id = ?";

    try {

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

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

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

    } 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 Attraction findByPrimaryKey(String userId)
    throws FinderException {

    PreparedStatement ps = null;
    ResultSet rs = null;
    Attraction attraction = null;

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

    try {

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

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

      if (rs.next()) {

        attraction = new Attraction();
        attraction.setUserId(rs.getString(1));
        attraction.setPassword(rs.getString(2));
        attraction.setName(rs.getString(3));
        attraction.setDescription(rs.getString(4));
        attraction.setWebSite(rs.getString(5));
        attraction.setAddress(rs.getString(6));

        return attraction;

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

    } 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() {

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

    String sql = "SELECT * from attraction ";

    try {

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

      ps = con.prepareStatement(sql);
      rs = ps.executeQuery();

      while(rs.next()) {

        Attraction attraction = new Attraction();
        attraction.setUserId(rs.getString(1));
        attraction.setPassword(rs.getString(2));
        attraction.setName(rs.getString(3));
        attraction.setDescription(rs.getString(4));
        attraction.setWebSite(rs.getString(5));
        attraction.setAddress(rs.getString(6));

        list.add(attraction);

      }

      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");
      }
    }

  }

}