userroledao.java

Begining JSP Web Development外文书籍源代码

package com.wrox.tourism.db;

import com.wrox.tourism.entity.UserRole;

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

public class UserRoleDAO {

  private Connection con;


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


  public void create(UserRole userRole) throws CreateException {

    PreparedStatement ps = null;

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

    try {

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

      ps = con.prepareStatement(sql);
      ps.setString(1,userRole.getUserId());
      ps.setString(2,userRole.getRoleName());

      if (ps.executeUpdate() != 1) {
        throw new CreateException("error.create.userRole");
      }

    } catch (SQLException e) {
      try {
        findByPrimaryKey(
          userRole.getUserId(),
            userRole.getRoleName());
      } catch (FinderException fe) {
        fe.printStackTrace();
        throw new RuntimeException("error.unexpected");
      }
      throw new DuplicateKeyException(
        "error.duplicate.userRole");
    } finally {
      try {
        if (ps != null)
          ps.close();
      } catch (SQLException e) {
        e.printStackTrace();
        throw new RuntimeException("error.unexpected");
      }
    }

  }


  public void remove(String userId,String roleName) {

    PreparedStatement ps = null;

    String sql = "DELETE FROM user_role " +
        "WHERE user_id = ? AND role_name = ?";

    try {

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

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

      if (ps.executeUpdate() < 1) {
        throw new NoSuchEntityException(
          "error.removed.userRole");
      }

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

    PreparedStatement ps = null;
    ResultSet rs = null;
    UserRole userRole = null;

    String sql = "SELECT * from user_role " +
        "WHERE user_id = ? AND role_name = ?";

    try {

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

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

      if (rs.next()) {

        userRole = new UserRole();
        userRole.setUserId(rs.getString(1));
        userRole.setRoleName(rs.getString(2));

        return userRole;

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

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

  }

}