@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;
}
@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;
}
@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;
}
@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;
}
}
@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;
}
@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;
}