Пример #1
0
 @Override
 public boolean blockSub(final boolean blocked, final int subId) throws SQLException {
   Connection connection = null;
   String sql = "update subscribers " + "set blocked = ? where id = ?";
   try {
     connection = MysqlDaoFactory.getConnection();
     connection.setAutoCommit(false);
     PreparedStatement stmt = connection.prepareStatement(sql);
     stmt.setBoolean(1, blocked);
     stmt.setInt(2, subId);
     int res = stmt.executeUpdate();
     if (res > 0) {
       connection.commit();
       return true;
     }
   } catch (Exception e) {
     try {
       if (connection != null) {
         connection.rollback();
         throw new EJBException("Transaction failed: " + e.getMessage());
       } else {
         throw new EJBException(
             "Transaction failed due " + "connection problem: " + e.getMessage());
       }
     } catch (SQLException ex) {
       throw new EJBException("Rollback failed due SQLException " + ex.getMessage());
     }
   } finally {
     if (connection != null) {
       connection.setAutoCommit(true);
       MysqlDaoFactory.putBackConnection(connection);
     }
   }
   return false;
 }
Пример #2
0
 @Override
 public boolean delSub(int id) throws SQLException {
   Connection connection = null;
   String sql = "delete from subscribers " + "where id = " + id;
   try {
     connection = MysqlDaoFactory.getConnection();
     connection.setAutoCommit(false);
     Statement stmt = connection.createStatement();
     stmt.executeUpdate(sql);
     if (stmt.executeUpdate(sql) > 0) {
       connection.commit();
       return true;
     }
   } catch (Exception e) {
     try {
       if (connection != null) {
         connection.rollback();
         throw new EJBException("Transaction failed: " + e.getMessage());
       } else {
         throw new EJBException(
             "Transaction failed due " + "connection problem: " + e.getMessage());
       }
     } catch (SQLException ex) {
       throw new EJBException("Rollback failed due SQLException " + ex.getMessage());
     }
   } finally {
     if (connection != null) {
       connection.setAutoCommit(true);
       MysqlDaoFactory.putBackConnection(connection);
     }
   }
   return false;
 }
Пример #3
0
 @Override
 public Subscriber getSubById(int id) throws SQLException {
   Connection connection = null;
   try {
     connection = MysqlDaoFactory.getConnection();
     String sql = "select * from subscribers where id = " + id;
     Statement stmt = connection.createStatement();
     ResultSet resSet = stmt.executeQuery(sql);
     if (null != resSet && resSet.next()) {
       Subscriber sub = new Subscriber();
       sub.setId(id);
       sub.setUserId(resSet.getInt(2));
       sub.setScore(resSet.getFloat(3));
       sub.setFname(resSet.getString(4));
       sub.setLname(resSet.getString(5));
       sub.setMname(resSet.getString(6));
       sub.setAddress(resSet.getString(7));
       sub.setBlocked(resSet.getBoolean(8));
       return sub;
     }
   } catch (SQLException e) {
     throw new EJBException("Select failed due " + "SQLException: " + e.getMessage());
   } finally {
     if (connection != null) {
       MysqlDaoFactory.putBackConnection(connection);
     }
   }
   return null;
 }
Пример #4
0
 @Override
 public List<Subscriber> getSubs() throws SQLException {
   Connection connection = null;
   List<Subscriber> resList = null;
   String sql = "select * from subscribers";
   try {
     connection = MysqlDaoFactory.getConnection();
     Statement stmt = connection.createStatement();
     ResultSet resSet = stmt.executeQuery(sql);
     resList = new ArrayList<Subscriber>();
     while (resSet.next()) {
       Subscriber sub = new Subscriber();
       sub.setId(resSet.getInt(1));
       sub.setUserId(resSet.getInt(2));
       sub.setScore(resSet.getFloat(3));
       sub.setFname(resSet.getString(4));
       sub.setLname(resSet.getString(5));
       sub.setMname(resSet.getString(6));
       sub.setAddress(resSet.getString(7));
       sub.setBlocked(resSet.getBoolean(8));
       resList.add(sub);
     }
   } catch (SQLException e) {
     throw new EJBException("Select failed due " + "SQLException: " + e.getMessage());
   } finally {
     if (connection != null) {
       MysqlDaoFactory.putBackConnection(connection);
     }
   }
   if (resList.size() == 0) {
     return null;
   } else {
     return resList;
   }
 }
Пример #5
0
  @Override
  public float updateSubScore(int subId) throws SQLException {
    Connection connection = null;
    String sql = "update subscribers " + "set score = ?, blocked = ? where id = ?";
    try {
      // -----
      // Bad solution. Duplicate connection call.
      Subscriber sub = getSubById(subId);
      // -----
      if ((null == sub) || sub.isBlocked()) {
        return -1;
      }
      TariffDao tariffDao = MysqlDaoFactory.getTariffDao();
      List<ServiceTariffsJoin> subTariffs = tariffDao.getSubscriberTariffs(subId, true);
      float currentScore = sub.getScore();
      boolean blocked = false;
      for (ServiceTariffsJoin sTariff : subTariffs) {
        float tariffCost = sTariff.getTariffs().get(0).getCost();
        if ((currentScore - tariffCost) >= 0) {
          currentScore -= tariffCost;
        } else {
          blocked = true;
        }
      }

      connection = MysqlDaoFactory.getConnection();
      connection.setAutoCommit(false);
      PreparedStatement stmt = connection.prepareStatement(sql);
      stmt.setFloat(1, currentScore);
      stmt.setBoolean(2, blocked);
      stmt.setInt(3, subId);
      int res = stmt.executeUpdate();
      if (res > 0) {
        connection.commit();
        return currentScore;
      }
    } catch (Exception e) {
      try {
        if (connection != null) {
          connection.rollback();
          throw new EJBException("Transaction failed: " + e.getMessage());
        } else {
          throw new EJBException("Transaction failed: " + e.getMessage());
        }
      } catch (SQLException ex) {
        throw new EJBException("Rollback failed due SQLException " + ex.getMessage());
      }
    } finally {
      if (connection != null) {
        connection.setAutoCommit(true);
        MysqlDaoFactory.putBackConnection(connection);
      }
    }
    return -1;
  }
Пример #6
0
 @Override
 public int insertSub(Subscriber sub) throws SQLException {
   Connection connection = null;
   String sql = "insert into subscribers values(0,?,?,?,?,?,?,?, 0)";
   try {
     connection = MysqlDaoFactory.getConnection();
     connection.setAutoCommit(false);
     PreparedStatement stmt =
         connection.prepareStatement(sql, java.sql.Statement.RETURN_GENERATED_KEYS);
     stmt.setInt(1, sub.getUserId());
     stmt.setFloat(2, sub.getScore());
     stmt.setString(3, sub.getFname());
     stmt.setString(4, sub.getLname());
     stmt.setString(5, sub.getMname());
     stmt.setString(6, sub.getAddress());
     stmt.setBoolean(7, sub.isBlocked());
     if (stmt.executeUpdate() > 0) {
       ResultSet gkeys = stmt.getGeneratedKeys();
       if (gkeys.next()) {
         connection.commit();
         return gkeys.getInt(1);
       }
     }
   } catch (Exception e) {
     try {
       if (connection != null) {
         connection.rollback();
         throw new EJBException("Transaction failed: " + e.getMessage());
       } else {
         throw new EJBException(
             "Transaction failed due " + "connection problem: " + e.getMessage());
       }
     } catch (SQLException ex) {
       throw new EJBException("Rollback failed due SQLException " + ex.getMessage());
     }
   } finally {
     if (connection != null) {
       connection.setAutoCommit(true);
       MysqlDaoFactory.putBackConnection(connection);
     }
   }
   return -1;
 }